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):
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.41-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database some_database;
Ignoring query to other database
mysql> show databases;
Ignoring query to other database
mysql> Bye
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:
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
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.