My Top MySQL Gripes

Due to some (legitimate) criticism I received for complaining about MySQL in another forum without bothering to say why, here is my list of my top MySQL complaints:

Yes, triggers should be avoided, but if you can't avoid them, don't cripple 'em.

Many of these issues force devs to push logic into the application layer where they're far more likely to be buggy.

Actually, if I could just have three of those fixed, I'd want custom data types (with operators, please), sane default values and check constraints.

10 Comments

You think that's bad? In SQLLite, even the type of a column is silently parsed and then ignored!

"So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer. If it can, it inserts the integer instead. If not, it inserts the string."

Oh great, do something random, just what I want in a database engine.

Like MySQL, it is slowly improving: "As of version 3.6.19, SQLite supports foreign key constraints. Prior versions of SQLite parsed foreign key constraints, but did not enforce them."

Also like (older versions of) MySQL, the documentation tends to treat missing data integrity checks as features rather than bugs.

Out of curiosity, why should triggers be avoided?

The SQLite behavior works great for databases where you don't care how long a string is or how big a number or what format the date is in but you just want it in the database.

I did get bit one time by trying to insert "$!" into the database and it inserted as a string rather than the number I expected from the schema, but that was easily fixed even after-the-fact.

In fairness, SQLite doesn't bill itself as a full-featured database. It's a lightweight, no-daemon-required, just-enough-to-get-by database. The BDB of the SQL world.

MySQL over-promises and under-delivers; SQLite (despite providing fewer features) does the inverse.

One I discovered this weekend: you can't say:

created_date TIMESTAMP DEFAULT NOW()

Because functions can't be used as defaults. The above will work using 'CURRENT_TIMESTAMP' instead of 'NOW()', but in other cases, you have to use a trigger if you want a function as a default value.

Given the ownership of MySql I wouldn't invest much hope in seeing it improve.

I think that having a general-purpose string type in the database is a wonderful idea. It is less good to pretend to parse definitions like 'int' or 'char(10)' but silently ignore them. If SQLLite required all columns to be declared with 'string' type, I'd be fine with that; it would also allow support for datatypes such as 'int' to be added in a later release.

The SQL standard defines data integrity checks which must happen when a column is declared with a certain type, or a foreign key constraint, or check constraint; programmers have a reasonable expectation that if they use these features they will work. If you don't support those parts of the SQL language, fine; just don't bogusly parse them and throw them away.

SQLite does assign some significance to the data type of a column – it picks different type coercion affinities depending on the data type. And it supports types in the same way Perl does: if you ask it for a string it will do its best to try and give you a string, and if you ask for an integer it will do its best to give you that. Works for me in Perl, I’m not sure why it’s suddenly horrible when it happens in SQL instead.

If you think MySQL is better than SQLite for supporting data types, I want to see your face next time you INSERT a 200-character string into a CHAR(20) and MySQL smiles, nods and complies… by throwing away 90% of the data. Hooray! SQLite at least doesn’t mangle your data when it, uh, fails to reject it. If you need to constrain it you still can by doing so retrospectively. With MySQL you never get to find out.

And really, SQLite is aimed at uses where you’d never pick MySQL. MySQL, in contrast, is aimed at uses where… you’d never pick MySQL.

Leave a comment

About Ovid

user-pic Have Perl; Will Travel. Freelance Perl/Testing/Agile consultant. Photo by http://www.circle23.com/. Warning: that site is not safe for work. The photographer is a good friend of mine, though, and it's appropriate to credit his work.