MySQL Notes

Marko Tiikkaja points out this interesting SQL gotcha, where (in PostgreSQL) two string constants separated by a newline character are implicitly concatenated - whereas separated by any other whitespace, an error is raised:

postgres=# SELECT 'foo' IN ('foo','bar');
 ?column? 
----------
 t
(1 row)

postgres=# SELECT 'foo' IN ('foo'
postgres(#  'bar');
 ?column? 
----------
 f
(1 row)

postgres=# SELECT 'foo' IN ('foo' 'bar');
ERROR:  syntax error at or near "'bar'"
LINE 1: SELECT 'foo' IN ('foo' 'bar');

According to the PostgreSQL documentation, this slightly bizarre behavior is specified by SQL.

How does MySQL stack up in this admittedly obscure piece of SQL quirkiness?

So, I was just loading a MySQL schema from a test application I use to experiment with various databases, and suddenly it started spitting out errors like this:

  BLOB/TEXT column 'some_column' can't have a default value

which was kind of odd because it had evidently been working fine before, and what kind of database doesn't allow default values on certain datatypes anyway?

Some quick notes on installing MySQL 5 on a Debian or Ubuntu server using the command line.

1. Determine the available MySQL version(s)

This step is optional; to find out what MySQL versions are available, execute

root@server ~ # apt-cache search mysql-server
auth2db - Powerful and eye-candy IDS logger, log viewer and alert generator
mysql-cluster-server - MySQL database server (metapackage depending on the latest version)
mysql-cluster-server-5.1 - MySQL database server binaries
torrentflux - web based, feature-rich BitTorrent download manager
mysql-server - MySQL database server (metapackage depending on the latest version)
mysql-server-5.1 - MySQL database server binaries
mysql-server-core-5.1 - MySQL database core server files
cacti - Frontend to rrdtool for monitoring systems and services

Normally the mysql-server package will exist as a meta package which will install the latest MySQL version available on the system - in this case 5.1.

Use the function UNIX_TIMESTAMP():

mysql> SELECT ts, UNIX_TIMESTAMP(ts) FROM ts_test;
+---------------------+--------------------+
| ts                  | UNIX_TIMESTAMP(ts) |
+---------------------+--------------------+
| 2013-02-17 16:34:09 |         1361086449 |
+---------------------+--------------------+
1 row in set (0.00 sec)

To get the current epoch, use UNIX_TIMESTAMP() with out any arguments:

mysql> SELECT UNIX_TIMESTAMP();
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1361088422 |
+-----------------------+
1 row in set (0.00 sec)

A slight MySQL oddity: SELECT UNIX_TIMESTAMP(NOW()) is equivalent to SELECT UNIX_TIMESTAMP(), however SELECT UNIX_TIMESTAMP(UTC_TIMESTAMP()) will produce an epoch number in the past.

mysql> SELECT UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(UTC_TIMESTAMP());
+-----------------------+---------------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP(UTC_TIMESTAMP()) |
+-----------------------+---------------------------------+
|            1361088547 |                      1361056147 |
+-----------------------+---------------------------------+
1 row in set (0.00 sec)

A bit of a gotcha maybe, especially if you're used to the PostgreSQL CLI (where -U specifies the user name):

Although the connection to MySQL was seemingly successful, any commands only seem to result in the laconic message "Ignoring query to other database". I'm not quite sure why this happens, but -U has the following meaning in MySQL:

Once again a bit of a head-scratcher which would be helped at the very least by less ambiguous notification messages.

According to the changelog for the 5.1.12 beta release:

Incompatible change: Support for the BerkeleyDB (BDB) engine has been dropped from this release. Any existing tables that are in BDB format will not be readable from within MySQL from 5.1.12 or newer. You should convert your tables to another storage engine before upgrading to 5.1.12.

An interesting perspective from Lisa Vaas of eWeek.com on Oracle's purchase of InnoDB: The Truth Comes Out: Oracle Bought InnoDB Without a Clue

In MySQL, user is not a reserved word and can be used as-is as a table name. As web applications in particular often have their own user table, it is in common use, which is perfectly understandable.

However, according to the various ANSI SQL standards, user is a reserved word (see e.g. here or here), and this leads to problems when converting MySQL schema definitions to other databases, such as that described here.

Note that as of MySQL 5.0.19, even when selecting MySQL's ANSI Mode, "user" does not appear to treated as a reserved word, and no warnings are given when it is used to create a table.