sql-info.de

MySQL Notes

1 | 2 | 3 | 4 | Index

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?

Posted at 8:23 AM

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?

Posted at 5:26 AM

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.

Posted at 2:43 AM

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)
Posted at 8:05 AM

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

[user@example.com]# mysql -Uroot -p
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, --safe-updates  Only allow UPDATE and DELETE that uses keys.
  -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.

Posted at 1:19 AM
19 comments  | 

August 24, 2006

MySQL | MySQL dropping BDB support

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.
Posted at 1:21 PM

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

Posted at 4:58 PM

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.

Posted at 10:57 PM

1 | 2 | 3 | 4 | Index