Mysql Limitations
Workarounds
MySQL has numerous limitations which are biting me in the rear...thought I'd share some workarounds here. This is not a complete list by any means, and was written when using MySQL 5.0 after several years of using other database systems. There's a number of other limitations that I encountered, but didn't get around to writing about (i.e. inability to create multiple triggers on the same type of action on a table), but I probably won't end up writing about them unless I end up using MySQL again and feel less lazy the next time around... ;) Hopefully this is of use to some folks anyways.
Queries
ORDER BY with no FROM
Adding something like 'order by 1' to a query which consists only of subselects (no from clause) generates an error in MySQL. You can rewrite the query as a union between the subselects instead to attain the desired result. So instead of:
select (select blah from blah) as 'foo', (select blah2 from blah2) as 'bar' order by 1;
You must instead do:
select 'foo' as name, blah as value from blah union select 'bar' as name, blah2 as value from blah2 order by 2;
The caveat of this is that your results are then in the reverse order you might have wanted, so you'll have to sort that out with a pivot table or at the application level.
Lack of Time Zone support
Time zones in PostgreSQL are handled beautifully, you can insert times in any zone you prefer into a timestamp with time zone column, and it is then displayed in whatever your client time zone setting (which can be overridden with SET timezone = 'whatever';) is when you select the data, allowing time zone conversion to happen automatically. Unfortunately, there's nothing in MySQL that accomplishes this that I can find. There is a session variable for time zone, but it only affects the output of functions like now(), not data from tables.
This means you have to convert the time zone manually every single time you select a timestamp. This is accomplished with the convert_tz function (documented at http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html), however even that is not quite as simple as it seems...
You may try passing a nice argument like 'America/Los Angeles' to convert_tz, but that doesn't work if the time zone tables haven't been populated. This is documented here: http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html
...and can be done with the following command:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p'whatever' mysql
Without time zone table population, your only resort is to use a numeric hourly offset, which should be avoided since it will break when Daylight Savings Time changes.
Table Structure
Indexes have short length limits
This means you cannot create a unique constraint on a text column or even a varchar(256). You either have to make your column shorter (not always possible), or index only a limited-length prefix (meaning a unique constraint won't allow anything with the same prefix - ugh). In addition, the text type is not unlimited length. The blob type is, but that doesn't solve the index problem, which you just have to live with.
Crazy mix of textual datatypes
text, tinytext, longtext, mediumtext, varchar, varbinary, blob, mediumblob, longblob, tinyblob. And none, with the exception of varchar, are really what you want. BLOB == "Binary Large Object" and doesn't seem appropriate for text, but the MySQL folks recommend using it for text when you need to workaround limitations of all the other types. I think it best to stick to varchar and text for some semblance of consistency.
Text columns cannot be unique
Index lengths are limited in MySQL and cannot be used on a text or blob column without a length constraint. This effectively makes a unique constraint impossible, as you can only guarantee uniqueness of the first couple hundred characters. Apparently the indexes use blank padding to make each entry an equal length as well, so they're not very efficient. No real workaround. Best thing I can come up with is to create a redundant varchar column with a shorter codename and make that unique. Guaranteed uniqueness of long text is simply not possible.
"auto_increment" versus sequences
MySQL doesn't support sequences - instead it has an "auto_increment" option for columns. This isn't nearly as flexible as it cannot be used independently of the insert statement (i.e. you cannot first reserve a new number, then use it in a later insert when you want to retain the value - you must instead query for the data you just inserted to discover this).
Silently accepts and doesn't actually create foreign key constraints
Consider the following syntax:
create table hardware ( id int auto_increment not null primary key, type int not null references hardware_types (id), hostname varchar(64) not null unique, domain int not null references domains (id), rack int not null references racks (id), rack_position smallint not null, rack_units smallint not null, asset_tag varchar(16) not null unique, in_use boolean not null default false, primary_ip int not null, client int not null references clients (id) );
Funny, all of those foreign key references are ignored:
mysql> show create table hardware; CREATE TABLE `hardware` ( `id` int(11) NOT NULL auto_increment, `type` int(11) NOT NULL, `hostname` varchar(64) NOT NULL, `domain` int(11) NOT NULL, `rack` int(11) NOT NULL, `rack_position` smallint(6) NOT NULL, `rack_units` smallint(6) NOT NULL, `asset_tag` varchar(16) NOT NULL, `in_use` tinyint(1) NOT NULL default '0', `primary_ip` int(11) NOT NULL, `client` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hostname` (`hostname`), UNIQUE KEY `asset_tag` (`asset_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Not accepting the syntax is one thing, but this is /yet another/ case of MySQL silently accepting input and throwing it away, which it seems to do an awful lot. The statement must be rewritten as follows to work:
create table hardware ( id int auto_increment not null primary key, type int not null, hostname varchar(64) not null unique, domain int not null, rack int not null, rack_position smallint not null, rack_units smallint not null, asset_tag varchar(16) not null unique, in_use boolean not null default false, primary_ip int not null, client int not null, foreign key (type) references hardware_types (id), foreign key (domain) references domains (id), foreign key (rack) references racks (id), foreign key (client) references clients (id) );
Silently accepts and doesn't actually create CHECK constraints
"For other storage engines [than InnoDB], MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines."
Apparently MySQL does not support CHECK constraints, but rather than telling you this, it silently acts like it does, but actually ignores them.
Cannot drop unique index or change/rename columns when foreign key exists
mysql> describe query_conditions_wtf;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| query | int(11) | NO | MUL | | |
| name | varchar(32) | NO | | | |
| type | int(11) | NO | MUL | | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show indexes from query_conditions_wtf;
+----------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| query_conditions_wtf | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | |
| query_conditions_wtf | 0 | query_condition | 1 | query | A | 1 | NULL | NULL | | BTREE | |
| query_conditions_wtf | 0 | query_condition | 2 | type | A | 1 | NULL | NULL | | BTREE | |
| query_conditions_wtf | 1 | type | 1 | type | A | 1 | NULL | NULL | | BTREE | |
+----------------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.01 sec)
mysql> show create table query_conditions_wtf;
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| query_conditions_wtf | CREATE TABLE "query_conditions_wtf" (
"id" int(11) NOT NULL auto_increment,
"query" int(11) NOT NULL,
"name" varchar(32) NOT NULL,
"type" int(11) NOT NULL,
PRIMARY KEY ("id"),
UNIQUE KEY "query_condition" ("query","type"),
KEY "type" ("type"),
CONSTRAINT "query_conditions_wtf_ibfk_1" FOREIGN KEY ("query") REFERENCES "queries" ("id"),
CONSTRAINT "query_conditions_wtf_ibfk_2" FOREIGN KEY ("type") REFERENCES "query_condition_types" ("id")
) |
+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop index query_condition on query_conditions_wtf;
ERROR 1025 (HY000): Error on rename of './gabrielle/#sql-6401_fc' to './gabrielle/query_conditions_wtf' (errno: 150)
The mysqld.err log shows the following:
061204 19:58:02 InnoDB: Error: in ALTER TABLE "gabrielle/query_conditions_wtf" InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition. 061204 19:58:02 InnoDB: Error: table "gabrielle/query_conditions_wtf" does not exist in the InnoDB internal InnoDB: data dictionary though MySQL is trying to drop it. InnoDB: Have you copied the .frm file of the table to the InnoDB: MySQL database directory from another database? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
It also happens if you try to rename a column that is a foreign key or try to alter it in any other way.
Solution is to drop the foreign key constraint (with ALTER TABLE, DROP INDEX produces the same error even though it should be doing the same thing), then drop the key with another ALTER TABLE, then change the column or drop the index that you were trying to, then recreate the foreign key constraint.
Example:
alter table hardware drop column rack; ERROR 1025 (HY000): Error on rename of './rosetta/#sql-1f23_5d' to './rosetta/hardware' (errno: 150) show create table hardware; -- Shows foreign key and key name. alter table hardware drop foreign key hardware_ibfk_3; alter table hardware drop key rack; alter table hardware drop column rack;
Strange timestamp/datetime behavior
To start with, the timestamp datatype, usually a dependable standard thing, varies in MySQL based on the version and sql mode. To quote their docs from here:
"The TIMESTAMP data type has varying properties, depending on the MySQL version and the SQL mode the server is running in."
"For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column."
Ummmmmm, yeah....moving right along...
Consider the following example, attempting to use two timestamp columns:
mysql> CREATE TABLE "hardware_historical" (
-> "id" int(11) NOT NULL,
-> "type" int(11) NOT NULL,
-> "hostname" int(11) NOT NULL,
-> "domain" int(11) NOT NULL,
-> "asset_tag" int(11) NOT NULL,
-> "in_use" tinyint(1) NOT NULL,
-> "client" int(11) NOT NULL,
-> "created" timestamp not null,
-> "replaced" timestamp not null default current_timestamp,
-> PRIMARY KEY ("id"),
-> UNIQUE ("id", "created"),
-> UNIQUE ("id", "replaced"),
-> FOREIGN KEY ("type") REFERENCES "hardware_types" ("id"),
-> FOREIGN KEY ("domain") REFERENCES "domains" ("id"),
-> FOREIGN KEY ("client") REFERENCES "clients" ("id"),
-> FOREIGN KEY ("hostname") REFERENCES "hostnames" ("id"),
-> FOREIGN KEY (asset_tag) references asset_tags (id)
-> );
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
As usual in MySQL, this error doesn't make sense, as there is indeed only one timestamp column with current_timestamp in the default clause. But apparently you can't have another timestamp column without any default set, either.
Okay, so let's try to use datetime instead:
mysql> CREATE TABLE "hardware_historical" (
-> "id" int(11) NOT NULL,
-> "type" int(11) NOT NULL,
-> "hostname" int(11) NOT NULL,
-> "domain" int(11) NOT NULL,
-> "asset_tag" int(11) NOT NULL,
-> "in_use" tinyint(1) NOT NULL,
-> "client" int(11) NOT NULL,
-> "created" datetime not null,
-> "replaced" datetime not null default current_timestamp,
-> PRIMARY KEY ("id"),
-> UNIQUE ("id", "created"),
-> UNIQUE ("id", "replaced"),
-> FOREIGN KEY ("type") REFERENCES "hardware_types" ("id"),
-> FOREIGN KEY ("domain") REFERENCES "domains" ("id"),
-> FOREIGN KEY ("client") REFERENCES "clients" ("id"),
-> FOREIGN KEY ("hostname") REFERENCES "hostnames" ("id"),
-> FOREIGN KEY (asset_tag) references asset_tags (id)
-> );
ERROR 1067 (42000): Invalid default value for 'replaced'
So a datetime, which according to MySQL documentation, stores the same data as timestamp, cannot have a default value of the current time? Bizarre.
I finally found a user comment which stated an undocumented oddity about MySQL:
"If your first TIMESTAMP column definition in a table has neither a DEFAULT or ON UPDATE clause then this is the same as TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP."
Well guess what, reversing the order of the columns in the first example makes it work. Nevermind if that's not the order you want, I guess. And if you want the default for both columns to be the current time, you have to set the defaults to some random balogne time, and then use triggers (adding substantial overhead per-insert) to make it happen.
Views
Distinguishing between tables and views
MySQL shows both tables and views when SHOW TABLES is used, presumably because it was not originally designed to support views. DESCRIBE TABLE unfortunately does not show the view definition - to look that up you must do 'SHOW CREATE TABLE <view_name>;' - now isn't that intuitive?. Use SHOW FULL TABLES to distinguish between tables and views. If you want to see only tables or views, as one often does, the following information_schema queries may be used:
select table_schema, table_name from information_schema.tables where table_type = 'BASE TABLE'; select table_schema, table_name from information_schema.tables where table_type = 'VIEW';
The plus side of these is that they will work on any database (see information_schema note above, though).
Cannot replace views in place
MySQL has no CREATE OR REPLACE functionality, so you must drop, then recreate a view as two steps. This however can be wrapped in a single transaction in order to guarantee constant availability of the view to an application, as follows:
BEGIN; DROP VIEW [...]; CREATE VIEW [...]; GRANT SELECT ON [...]; END;
No checking if views will break
MySQL has no verification that views will actually continue working if a table is changed. This is nice to have because if you try, you realize that you will need to recreate views to work after the change is made, so you can wrap it all up in a transaction as follows to guarantee that nothing ever breaks:
BEGIN; DROP VIEW [...]; ALTER TABLE [...]; CREATE VIEW [...]; END;
However in MySQL you must figure out which views will be affected in advance. You can query information_schema to look these up, though it's a kludge and may return a lot of stuff that's not actually affected:
select * from views where view_definition like '%hardware%';
Schemas
MySQL does support schemas - what it does not in fact support is multiple databases which are entirely separate (i.e. you cannot join a query between two databases) - it calls schemas databases which can be a bit confusing, but as long as you think of MySQL "databases" as standard "schemas", things make more sense.
Cannot move database objects between schemas
MySQL does not support an ALTER TABLE table SET SCHEMA new_schema syntax. To accomplish this, the old table/view must be dropped, and recreated in the new schema.
Human/DBA Interface
DESCRIBE is very limited
If you want to see a list of columns, DESCRIBE does the job, though it's not the most readable in the world. If you want to see indexes, you have to use SHOW INDEXES - similarly if you want to see triggers, you have to use SHOW TRIGGERS, and so on. Worst, if you want to see InnoDB foreign key constraints, the only way is with SHOW CREATE TABLE. In psql, \d shows all.
Update: More details, including the table engine (.e. InnoDB or MyISAM) can be found in the information_schema.tables table in MySQL 5.0 and up. Beware that this is a MySQL-specific column though (see "information_schema is not standard" note below).
information_schema is not standard
The whole reason information_schema was added to the standard was so that there would be a consistant way to query for data on any supporting database platform. Be warned, however - MySQL has taken it upon themselves to add what they call extensions - their own proprietary columns, to the information_schema tables. A good example of this is the information_schema.tables table - the differences from the standard are documented here:
As you can see, the majority of the columns are not standard. When writing database-independent queries, be careful to only use columns that the standard supports. Full documentation of the differences may be found here:
Tab-completion is not realtime
In the MySQL commandline client, object names are loaded and cached when the client is started. If you make any changes to the table structure in your session, or if somebody else does while you are logged in, your cache will not be updated, and tab completion will not match what is actually in the database. The only solution is to restart the client continually.
Ctrl+C kills commandline client
If you type something wrong, and want to cancel, your instinct says, "press ctrl+C!". But if you do that in the MySQL commandline client, the whole client itself is killed, and you must log in to MySQL again. For something as simple as forgetting a quote before hitting enter, you must restart the client, unless you feel like abusing the server with a broken query by typing '; on another line. This is risky as you might actually do something you don't want, like enter part of the query into a text column.
Double semicolons causes an error
If you mistakenly type an extra ; at the end of your query, i.e. select * from table;;, the second ; is interpreted as a second query and causes a syntax error.
Stored Procedures/Functions
Procedures versus Functions
In MySQL, procedures are blocks of code that do something, but don't return any results. To execute them, you issue 'CALL <sp_name (args)>;'. I'm not quite sure of where that's of much use - what one will almost always want to use are called stored functions, which return a result and may be used in a more standard manner, such as via 'SELECT <sp_name (args)>;' or 'select * from table where id = <sp_name (args)>;'.
Changing functions
There's not any function to describe a function or show it's code that I can find, aside from querying information_schema. The following query will work on any platform:
select routine_schema as "Schema", routine_name as "Name", routine_body as "Language", routine_definition as "Source Code" from information_schema.routines where routine_type = 'FUNCTION' and routine_schema not in ('pg_catalog', 'information_schema');
This has the following limitations versus \df+ in PostgreSQL:
- No idea what sort of data the function is supposed to return.
- No idea what argument data types the function accepts.
SHOW CREATE FUNCTION will show a create function statement to recreate the function, but it's not terribly readable. phpMyAdmin has no support whatsoever for stored functions or procedures, so there's no way to easily edit a function in place. The best bet is to either maintain the procedures in a text document and paste in drop/create commands every time you want to make a change, or in worst case, do SHOW CREATE FUNCTION and edit the results of that.
Crashes - Watch out!
Tries to use more memory than what's available
If you get a fun fun error like this:
ERROR 2013 (HY000): Lost connection to MySQL server during query ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
It probably means that MySQL is configured to use more memory than what's really available on the system, and once it runs out, it crashes. MySQL does no sanity check upon startup (PostgreSQL on the other hand will not start unless the configured resources are actually available), so you it can catch you by surprise. The DBA should be careful to configure MySQL so that this doesn't happen.