AaronCrane.co.uk

Articles tagged “MySQL”

Insanity with dumping MySQL stored procedures

Suppose you have a MySQL database containing stored procedures (which were added in MySQL 5.0.3). Now you dump that database using mysqldump, take the dump, and load it into a different server.

Oops, the dump didn’t contain the stored-procedure definitions, so your code fails. So you read the mysqldump manual, and discover that you need to add a -R (--routines) unbreak-me option. You regenerate the dump with -R, and load it into your other server. That worked; phew. (Assuming the user you dumped as has read permissions on the mysql.proc table, anyway.)

Then you load the dump into a third server. But you weren’t thinking very clearly at this point, and this third server is running 5.0.2, or 4.1, or some other version N−k. What should happen now?

More…

Numbers and strings in MySQL

Here’s a MySQL query issued by some Perl code:

$db->do(q{
    CREATE TEMPORARY TABLE byline_age (
        byline_id int PRIMARY KEY,
        last_used int not null
    )
    SELECT byline_id, MAX(IfNull(published_at, ?)) AS last_used
    FROM article
    GROUP BY byline_id
}, undef, time());

Spot the bug? No, nor did I.

More…

MySQL whitespace insanity

Here’s some astonishingly bad MySQL insanity. (I know I rant about MySQL lossage a lot, but that’s because there’s just so much of it to rant about.)

More…

MySQL and foreign-key support

[Previously published here.]

(This started out as a polite response on a mailing list to someone saying “You guys are out of date, MySQL is a pretty decent db now”. But it got a bit ranty, so I thought I’d put it here instead. Enjoy!)

Current releases of MySQL certainly have many of the checklist features of a real DBMS. However, this does not constitute a defensible claim that MySQL is a real DBMS.

More…