Mysql, how do I loathe thee? Let me count the ways...
Contents
mysql CLI client
MySQL has a fairly straightforward argument-passing syntax, but it's a bit stupid in some ways, as documented below.
-h hostname -u username -p password -P portnumber
password - If you want to specify the password, you have to leave no space between the -p and the password. Why this is the case is anybody's guess; it's inconsistent with every other command line tool in existence. You need to use it as -pPutThePasswordHere. The alternative is long GNU-style options, --password=ThePassword
hostname - By default, localhost is assumed. When the hostname is localhost, explicit or implied, the client will assume you want to use a file-socket connection, ignoring any other options like the port number. This retardedness is apparently by design, see http://bugs.mysql.com/bug.php?id=16855 for more details. If you really, really want a TCP connections, you have to either specify --protocol=TCP or use something like -h 127.0.0.1. Anything that isn't localhost.
Permissions for creating databases
In RT:133666 there was a bit of confusion over who can create databases, when, and where. Here's the skinny (as of MySQL 5.0.51a, anyway; I make no warranties that the MySQL devs won't go and completely screw with this in any other version)
There is a global CREATE privilege and a per-database CREATE privilege. You grant the global privilege by giving an object_type of *.* while you grant the per-database privilege by giving an object type of database.*
If you specify a particular table or routine name in the grant, no global or database CREATE privilege is provided.
A user with a global CREATE privilege is easy to understand: they are able to create a database with any name that they like. They do not, however, have the ability to drop a database (for that, you need to grant the DROP privilege).
In contrast, a user with a database-specific CREATE privilege is able to create a database named whatever they've got the privilege for, and also to create any table they like in that database. Again, if you want them to be able to remove the database (or tables within it) they need to have the DROP privilege.
Incorrect information in file
Symptoms
From /var/log/mysqld.log:
[ERROR] /usr/libexec/mysqld: Incorrect information in file: './<DATABASE>/<TABLE>.frm'
where <TABLE> is an InnoDB table.
Likely cause
The InnoDB MySQL storage engine has been disabled. Check with:
mysql> SHOW STATUS like '%innodb%';
Fix
Remove the skip-innodb directive from /etc/my.cnf
Repairing FUBAR InnoDB Tables
Start reading, and get some cans of your favorite caffeinated beverage. You might not be going anywhere for a while.
Triggers
This stems from RT:125359. In short, MySQL is stupid (big surprise) and triggers aren't handled sanely like they are in Postgres. Prior to 5.1.6, you have to define triggers as a user with the SUPER privilege (ie. root). Prior to 5.0.17, the trigger runs as the user that fires it (almost acceptable). After 5.0.17, it runs as the defined DEFINER. Which has to be root until 5.1.6. Retards.
Dealing with logfiles
Most of us are familiar with sending a SIGHUP to apache when we want to rotate logs. MySQL doesn't seem to do this, so what do you do to shift a log? Thankfully, there's a way.
Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:
shell> mv host_name.log host_name-old.log shell> mysqladmin flush-logs shell> cp host_name-old.log backup-directory shell> rm host_name-old.log # this should do that same thing mysql> FLUSH LOGS;
Rename it, delete it, do what you want. Flush the logs, mysqld will reopen the logfiles. In that particular ticket I commented out the query-logging line. Whether mysql re-read the config file I'm not sure, but the problem is gone, in any case.
Gotchas
MySQL can't use more than one index on each table in a query. Example, SELECT name,phone FROM users WHERE name='john' and phone > 5555555 will use either an index on name or phone, but not both. You can create a multi-column index on name,phone, though. -_-
You can apparently use a self-join to get around this retardedness. SELECT a.name,b.phone FROM users AS a,users AS b WHERE a.name='john' and b.phone > 5555555
- MyISAM is kinda shite in general
Replication
Not bad in theory, but we find it breaks far too often for us. Monitoring it is kinda hard, too (the slave's SecondsBehindMaster value is actually meaningless).
No, wait. Replication is absolutely fucked in the head. The whole statement-based replication thing is arse, and trying to do any filtering (anything except "sync every single database in the system") is dangerous at best.
You can setup circular replication (aka. multimaster):
- Pros:
- Your friends will think you are 1337
- You will be able to claim that your are certified (as being fucking insane).
- Cons:
- Eman will kick you in the nuts
- Your database will die a horrible, horrible death some day soon.