Top 5 lists are super-good link bait, so I’ll make my own sad one.
In no particular order, and not stopping at 5:
- your table names are case-sensitive on some platforms (and, on some platforms, dependent on file system type!) but not others
- data definitions (DDL) are not in transactions
- you don’t have a transaction-safe memory table (so create them temporarily for each connection, and clear them manually on rollback)
- you can mix transaction-safe and non-transaction safe tables in a transaction and, if it rolls back, the non-transaction safe tables are not rolled back. And this is silently accepted
- the default character set is latin1. Well, its not even latin1 apparently, its just called
that. Its actually cp1252 with eight characters randomly shifted around. You have to configure the
server / database / column to be something else and configure your client to get
back what you put into character columns; in Python, configuring the client is a few switches scattered about and not
just one. And I have a hazy memory of having to slap “BINARY” keywords on my
VARCHAR columns to get out what I put in or something. From the docs:
The description in the following sections may appear complex, but it has been found in practice that multiple-level defaulting leads to natural and obvious results.
Yeah right! - and the UTF-8 isn’t actually proper full UTF-8, it has UCS2 internally somewhere so no Klingon code-points apparently. You need to specify “utf8mb4” if you’re storing music
- InnoDB is shockingly slow if you don’t have nice sequential primary keys
- well, its shockingly slow any which way
- it defaults to non-transaction-safe table types (on some platforms and packaging)
- sometimes on some clients and some platforms at some strictness levels it will create tables
in the default storage engine if it doesn’t recognise your storage engine
CREATE TABLE silly (id INTEGER) ENGINE=MyIASM;
Query OK, 0 rows affected, 2 warnings (0.12 sec)
+---------+------+-----------------------------------------------+
| Level | Code | Message
+---------+------+-----------------------------------------------+
| Warning | 1286 | Unknown storage engine 'MyIASM'
| Warning | 1266 | Using storage engine InnoDB for table 'silly' - and I haven’t even started on the MySQL dialect of SQL!
What have I missed? What’s your favourite - eh, least favourite - feature of MySQL?
Hate On!
(You may also like: Google: moreSQL is real and how I got massively faster DB with async batching)