BenV's notes

Another reason to avoid MySQL

Today MySQL has managed to piss me off enough to get rid of it for something I was working on.
The case:
I just created a brand new database. In that database I want to store items, and those items should be linked to users.
So as a first stab I had this CREATE TABLE statement:

CREATE TABLE user (user_id SERIAL, username varchar(255) UNIQUE NOT NULL, password char(32) NOT NULL, PRIMARY KEY (user_id));
CREATE TABLE item (item_id SERIAL, name varchar(255) NOT NULL UNIQUE, user_id bigint unsigned REFERENCES user(user_id),PRIMARY KEY (item_id)) ENGINE = InnoDB;

Guess what MySQL said? Of course, it said “Sure thing dude!”. And to make sure you believe me, here’s the verbatim:

mysql> CREATE TABLE user (user_id SERIAL, username varchar(255) UNIQUE NOT NULL, password char(32) NOT NULL, PRIMARY KEY (user_id));
Query OK, 0 rows affected (0.36 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> CREATE TABLE item (item_id SERIAL, name varchar(255) NOT NULL UNIQUE, user_id bigint unsigned REFERENCES user(user_id),PRIMARY KEY (item_id)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.32 sec)
mysql> show warnings;
Empty set (0.00 sec)

Well guess what….
IT LIED!
Those references to the user table? What references? (continue reading…)

Today I was scripting up some commandline tools and found myself in need of doing some MySQL commands on the commandline.
Or to be more specific: I wanted to create a database and grant some permissions. So I figured “Admin stuff… let’s try mysqladmin“. (continue reading…)

Slackware recently upgraded the MySQL version from 5.0 to 5.1, from the Changelog:

Sun Oct 4 00:17:50 CDT 2009
ap/mysql-5.1.39-x86_64-1.txz: Upgraded.
This bumps the version of the shared libraries to .so.16.0.0.

Yeah, that’s all great, but for me this broke on several of my machines. (continue reading…)