sql-info.de

MySQL Gotchas


Notes
  1. This is not an "anti-MySQL" list, although it does contain critical comments;
  2. MySQL is continually improving and some of the points listed here are no longer valid for recent versions;
  3. This page has not been updated for a long time
  4. This page deals with issues related to MySQL 4.1 and earlier, not 5.0
  5. There's a list for PostgreSQL too.
  6. This page may not render well in Internet Explorer, run along and get yourself a proper browser.

It's not a bug - it's a gotcha. A "gotcha" is a feature or function which works as advertised - but not as expected.

When working with the MySQL ™ database server I have repeatedly encountered situations where the results of various actions have been unexpected and/or contrary to the behaviour generally expected of an SQL relational database. The cause can usually be traced to implementation details which are documented in the manual. I have created this list in order to further a better understanding of the MySQL database server and hopefully save others unnecessary headscratching.

(For known bugs see this page in the MySQL documentation: http://dev.mysql.com/doc/mysql/en/Open_bugs.html).

The MySQL database server is being continually improved. Some gotchas described here are no longer relevant for the latest versions; in these cases the version numbers affected are noted at the top of each section. As a rule gotchas have been tested against the most recent stable versions from the 3.23.x, 4.0.x and 4.1.x series.

Corrections, suggestions and comments are welcome: .

Note: this document contains many examples as they would appear in the MySQL command line client mysql ("mysql monitor"). For convenience, where statements are more than one line, the continuation prompt -> has been omitted, so the statement can be copied directly from this page.

1. General SQL

1.1. NULL, or when NULL IS NOT NULL

In SQL NULL represents the the absence of a value. In MySQL an explicit NULL may also represent the next value of a pseudo-sequence and an implicit NULL may represent an implicit default value (a zero or empty string) determined by MySQL.

Example 1. When NULL is not NULL

CREATE TABLE null_1 (
id INT NOT NULL,
text1 VARCHAR(32) NOT NULL,
text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);

INSERT INTO null_1 (id) VALUES(1);
INSERT INTO null_1 (text1) VALUES('test');
mysql> SELECT * FROM null_1;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
| 1 | | foo |
| 0 | test | foo |
+----+-------+-------+
2 rows in set (0.00 sec)

Here MySQL has inserted an empty string into column text1 on the first row, and zero into column id on the second row, even though each column is defined as NOT NULL with no default value. As no value was provided in the INSERT statements, these can be considered an attempt to insert implicit NULLs into theses columns, which should normally cause the statements to fail.

 

... If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type: ...

 
--http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html 

Another example of NULL being used to trigger the insertion of an non-NULL is with the TIMESTAMP datatype, where NULL causes the field to be set to the current time:

Example 2. NULL and TIMESTAMP

mysql> CREATE TABLE timestamp (
id INT,
ts1 TIMESTAMP(8) NOT NULL,
ts2 TIMESTAMP(8)
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO timestamp VALUES(1, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp (id, ts1) VALUES(2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp (id, ts2) VALUES(3, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM timestamp;
+------+----------+----------+
| id | ts1 | ts2 |
+------+----------+----------+
| 1 | 20030625 | 20030625 |
| 2 | 20030625 | 00000000 |
| 3 | 20030625 | 20030625 |
+------+----------+----------+
3 rows in set (0.00 sec)

Note that in every case, even though ts1 has no default value, and even when no value was provided in the INSERT statement, the current timestamp was inserted into the column.

The manual explains this behaviour thus:

 

... The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. ...

 
--http://dev.mysql.com/doc/mysql/en/DATETIME.html 

Presumably this is a way around the limitation that columns could not be defined with a default of NOW() in MySQL versions prior to 4.1. See also Section 1.5 and Section 1.7.

Note: this behaviour can be changed from version 4.1.1:

 

... When MySQL is running in MAXDB mode, TIMESTAMP behaves like DATETIME. No automatic updating of TIMESTAMP columns occurs, as described in the following paragraphs. MySQL can be run in MAXDB mode as of version 4.1.1. ...

 
--http://dev.mysql.com/doc/mysql/en/DATETIME.html 

Example 3. Accessing pseudo-sequences with NULL

CREATE TABLE null_2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
text1 VARCHAR(32) NOT NULL
);

INSERT INTO null_2 VALUES();
INSERT INTO null_2 (id) VALUES(NULL);
mysql> select * from null_2;
+----+-------+
| id | text1 |
+----+-------+
| 1 | |
| 2 | |
+----+-------+
2 rows in set (0.00 sec)

In MySQL this behaviour is necessary, as it is the only way of accessing the AUTO_INCREMENT pseudo-sequence. It is still an odd notion though that inserting a NULL on a NOT NULL column can have this kind of effect.

See below for more fun with AUTO_INCREMENT

Continuing from the previous example:

Example 4. Is NULL an integer?

mysql> INSERT INTO null_2 () VALUES(NULL, 'This should be id 3');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM null_2 WHERE id IS NULL;
+----+---------------------+
| id | text1 |
+----+---------------------+
| 3 | This should be id 3 |
+----+---------------------+
1 row in set (0.00 sec)

The first person to spot a NULL value in the result set will receive a stuffed toy dolphin from the author.

There is an explanation though:

 

... For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row: ... SELECT * FROM tbl_name WHERE auto IS NULL;

 
--http://dev.mysql.com/doc/mysql/en/ODBC_and_last_insert_id.html 

All further executions of the same statement provide the expected result:

mysql> SELECT * FROM null_2 WHERE id IS NULL;
Empty set (0.00 sec)

1.2. AUTO_INCREMENT

The previous gotcha leads us to this interesting problem, which is possibly the proof that 0 equals 1:

CREATE TABLE exmpl3 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
val TEXT
);
INSERT INTO exmpl3 VALUES(0, 'test');
mysql> select * from exmpl3;
+----+------+
| id | val |
+----+------+
| 1 | test |
+----+------+
1 row in set (0.00 sec)

Isn't this fun? Now let's get ambitious:

CREATE TABLE exmpl4 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
val TEXT
);
mysql> INSERT INTO exmpl4 VALUES(-1, 'test');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO exmpl4 VALUES(0, 'test');
Query OK, 1 row affected (0.01 sec)

and then:

mysql> INSERT INTO exmpl4 VALUES(1, 'test');
ERROR 1062: Duplicate entry '1' for key 1
mysql> SELECT * FROM exmpl4;
+----+------+
| id | val |
+----+------+
| -1 | test |
| 1 | test |
+----+------+
2 rows in set (0.00 sec)

The probable explanation for this is:

 

... The behaviour of auto-increment is not defined if a user gives a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type. ...

 
--http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html 

The problem here is not the restriction itself, but the combintation of undefined behaviour and the absence of any warnings after inserting a negative value leading to unexpected errors.

Note

Several readers have asked why I would ever want negative values in an AUTO_INCREMENT column. As it happens, the table that caused the discovery of the above oddities didn't actually need an AUTO_INCREMENT column, as the primary key values were to be limited to between -2 and 2 (enforced by a foreign key relation, of course). One was added anyway, probably through force of habit. The problem occurred while loading data with predefined values from a script, which caused a primary key exception, even though the ID values were defined explicitly and the data itself was correct. It's MySQLs habit of discretely altering input, and only (coincidentally) barfing on the side-effects - instead of immediately throwing an error on data it doesn't know what to do with - which is causing me to compose lists like this.

Personally I've not yet ever needed sequences which start with negative values. If you do, you'll probably need to look at another database. (And if you're thinking "implement them in the application", you don't need a relational database).

One way of changing the value of the AUTO_INCREMENT sequence is with ALTER TABLE ... AUTO_INCREMENT=value. With InnoDB tables however the statement appears to succeed, does not have any effect. Use the syntax SET INSERT_ID=value instead:

mysql> CREATE TABLE exmpl5 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
val TEXT
) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test2');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE exmpl5 AUTO_INCREMENT=4;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test4');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM exmpl5;
+----+-------+
| id | val |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test4 |
+----+-------+
3 rows in set (0.00 sec)

mysql> SET INSERT_ID=5;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO exmpl5 VALUES(NULL, 'test5');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM exmpl5;
+----+-------+
| id | val |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test4 |
| 5 | test5 |
+----+-------+
4 rows in set (0.00 sec)

1.3. ENUM

An interesting and amusing mistake the author has often made is to forget that ENUM can only contain character values:

mysql> CREATE TABLE enum_exmpl (
id INT,
whatever ENUM(0,1)
);
ERROR 1064: You have an error in your SQL syntax near '0,1)
)' at line 3

Oops, my mistake, try again:

mysql> CREATE TABLE enum_exmpl (
id INT,
whatever ENUM('0','1')
);
Query OK, 0 rows affected (0.00 sec)

By the time data is ready to be inserted amnesia has set in:

INSERT INTO enum_exmpl VALUES(1,0);
INSERT INTO enum_exmpl VALUES(2,1);
INSERT INTO enum_exmpl VALUES(3,2);
INSERT INTO enum_exmpl VALUES(4,3);
mysql> SELECT * FROM enum_exmpl;
+------+----------+
| id | whatever |
+------+----------+
| 1 | |
| 2 | 0 |
| 3 | 1 |
| 4 | |
+------+----------+
2 rows in set (0.00 sec)

Two things are happening here:

  • when an integer is inserted, the enumeration member corresponding to the index value of that integer is inserted. This is the case with ids 2 and 3 in the above example, where the integer 1 is the first value of the ENUM index ('0') and 2 is the second value ('1').
  • when ENUM receives an invalid value, an empty string is inserted; this is the case with ids 0 and 4, where the inserted integers had no corresponding index value.

See: http://dev.mysql.com/doc/mysql/en/ENUM.html for further details.

As MySQL does not have a true boolean datatype it is tempting to use ENUM like this:

mysql> CREATE TABLE enum_exmpl2 (
id INT,
whatever ENUM('0','1') NOT NULL DEFAULT 0
);
ERROR 1067: Invalid default value for 'whatever'

Once again, my mistake. But what if I want the default value to be true?

mysql> CREATE TABLE enum_exmpl2 (
id INT,
whatever ENUM('0','1') NOT NULL DEFAULT 1
);
Query OK, 0 rows affected (0.00 sec)

Syntax OK, looks good, but:

mysql> INSERT INTO enum_exmpl2 (id) VALUES(99);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM enum_exmpl2;
+------+----------+
| id | whatever |
+------+----------+
| 99 | 0 |
+------+----------+
1 row in set (0.00 sec)

Again, MySQL is performing to specification; to avoid this kind of mistake - DEFAULT 1 instead of DEFAULT '1', be sure to perform all ENUM operations including table creation with character values and not integers.

Note that ENUM also accepts insertion of NULL in columns defined as NOT NULL and as with other datatypes will insert the default value, if defined, or the first value in the ENUM index.

1.4. Case sensitivity in CHAR / VARCHAR fields

Try this:

mysql> CREATE TABLE casetest (
id INT,
string VARCHAR(32)
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO casetest VALUES(1, 'foo');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM casetest WHERE string = 'FOO';
+------+--------+
| id | string |
+------+--------+
| 1 | foo |
+------+--------+
1 row in set (0.02 sec)

To prevent this happening, the table should be defined like this:

CREATE TABLE casetest (
id INT,
string VARCHAR(32) BINARY
)

The manual says:

 

... Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved. ...

 
--http://dev.mysql.com/doc/mysql/en/CHAR.html 

Note that MySQL's behaviour in this regard is the diametrical opposite of the default behaviour of most other databases. (Tested: DB2 8.1, Firebird 1.5.1, Oracle 8.1.7 and PostgreSQL 7.4.3, details of other databases welcome).

1.5. VARCHAR limited to 255 characters

MySQL limits VARCHAR (aka CHARACTER VARYING) columns to a column length of 255.

A future MySQL version will remove this restriction:

 

Add true VARCHAR support (column lengths longer than 255, ... ). There is already support for this in the MyISAM storage engine, but it is not yet available at the user level.

 
--http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html 

1.6. VARCHAR's trailing blank allergy

In the MySQL world VARCHAR columns are the opposite of CHAR: instead of being padded, any trailing blanks are stripped.

mysql> CREATE TABLE vtest (id INT, val VARCHAR(32));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO vtest VALUES(1, 'Watch this space -> ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT concat(val, '|') FROM vtest;
+----------------------+
| concat(val, '|') |
+----------------------+
| Watch this space ->| |
+----------------------+
1 row in set (0.00 sec)

This is documented, and is not a bug:

 

... However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the SQL-99 specification.) ...

 
--http://dev.mysql.com/doc/mysql/en/CHAR.html 

Use BLOB or TEXT types to store values with trailing blanks.

Update: Apparently this behaviour will be corrected in the planned 5.0 release (see: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html).

1.7. DEFAULT NOW()

Affects: MySQL <4.1

It is not possible to create a column with a default value which is a function or expression, such as NOW():

mysql> CREATE TABLE timestamp_now (
id INT,
timestamp TIMESTAMP NOT NULL DEFAULT NOW()
);
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'NOW())' at line 3

Occasionally you may see the advice to put NOW() in single quotes:

CREATE TABLE timestamp_now (
id INT,
timestamp TIMESTAMP NOT NULL DEFAULT 'NOW()'
)

This syntax is accepted and even appears to work:

mysql> INSERT INTO timestamp_now(id) VALUES(1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM timestamp_now;
+------+----------------+
| id | timestamp |
+------+----------------+
| 1 | 20030625154717 |
+------+----------------+
1 row in set (0.00 sec)

but this is merely MySQL's automagical "first TIMESTAMP column rule" coming into play, where the first TIMESTAMP column is filled with the current timestamp on each INSERT or UPDATE:

mysql> update timestamp_now SET id=2 WHERE id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from timestamp_now;
+------+----------------+
| id | timestamp |
+------+----------------+
| 2 | 20030625154826 |
+------+----------------+
1 row in set (0.00 sec)

Note how the timestamp value has changed; the defined default column value has no effect. If this default is defined for any other column value, 'NOW()' is interpreted as a literal string which cannot be cast to a TIMESTAMP or other date type, and which MySQL in its infinite wisdom silently converts to a zero value.

See: http://dev.mysql.com/doc/mysql/en/DATETIME.html for MySQL's take on the matter.

1.8. INSERT INTO ... SELECT ...

Affects: MySQL <= 3.23.58; MySQL <= 4.0.13

INSERT INTO ... SELECT ... does not work in MySQL if source and target tables are the same.

CREATE TABLE insert_test (
id INT,
txt VARCHAR(32)
);

INSERT INTO insert_test VALUES(1, 'foo');
INSERT INTO insert_test VALUES(2, 'bar');
mysql> INSERT INTO insert_test SELECT 3, txt FROM insert_test WHERE id=2;
ERROR 1066: Not unique table/alias: 'insert_test'

The manual says about this:

 

The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing.)

 
--http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html 

Other databases tested have no problem with the same construction. The only exception found is Firebird 1.02, which went into an infinite loop; this problem seems to be fixed in Firebird 1.5. (Note: earlier versions of the MySQL manual implied this behaviour was SQL-conform, though not which version of the standard; MySQL tells me the version referred to was SQL-89).

A further idiosyncracy seems to be this behaviour:

CREATE TABLE insert_test2 (
id INT NOT NULL PRIMARY KEY,
txt VARCHAR(32)
);
INSERT INTO insert_test2 VALUES(1, 'foo');
INSERT INTO insert_test2 VALUES(2, 'bar');

Now watch this:

mysql> INSERT INTO insert_test2 VALUES(1, 'bar');
ERROR 1062: Duplicate entry '1' for key 1

Correct behaviour... now watch this:

mysql> INSERT INTO insert_test2 (id, txt) SELECT i.id, i.txt FROM insert_test i WHERE i.id=1;
Query OK, 0 rows affected (0.00 sec)
Records: 1 Duplicates: 1 Warnings: 0

mysql> SELECT * FROM insert_test2;
+----+------+
| id | txt |
+----+------+
| 1 | foo |
| 2 | bar |
+----+------+
2 rows in set (0.00 sec)

Expected behaviour would be for this statement to fail with the same error as above; instead it appears that the statement was successful, with only the Duplicates: 1 notice indicating the statement was effectively ignored.

Update: in version 4.0.20, and possibly previous versions, this statement returns the expected error message.

Note also that the syntax:

INSERT INTO insert_test SELECT 3, 'fubar';

is not supported, although I am not sure whether this is standard SQL.

Note: This is documented by MySQL thus:

 

Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14.

 
--http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html 

1.9. Comments beginning with --

MySQL is slightly allergic to comments beginning with a double dash: -- and requires that a space is inserted between the -- and the following comment, e.g.:

mysql> select 1, --comment
-> 2;
ERROR 1054: Unknown column 'comment' in 'field list'
mysql> select 1, -- comment
-> 2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.01 sec)

Note: In recent MySQL versions the error message is returned with the code 1064.

See: http://dev.mysql.com/doc/mysql/en/ANSI_diff_comments.html for further information.

1.10. UNION and literal values

Affects: MySQL <= 3.23.58; MySQL <= 4.0.21

If the first SELECT in a UNION join contains literal values, i.e. values not derived from a column, values from subsequent SELECT statements will be truncated to the length of the literal columns:

mysql> CREATE TABLE union_test (
id INT,
val1 VARCHAR(10),
val2 VARCHAR(10)
);
mysql> INSERT INTO union_test VALUES(1, 'abcdef','123456');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT 'abc' AS c1, '123' AS c2
UNION ALL
SELECT val1 AS c1, val2 AS c2 FROM union_test;
+------+------+
| c1 | c2 |
+------+------+
| abc | 123 |
| abc | 123 |
+------+------+
2 rows in set (0.00 sec)

No reason for this behaviour could be found.

In September 2003 this was confirmed to me by someone from MySQL as a bug. It has been fixed in the 4.1.x series, but remains in earlier versions.

Other databases tested (DB2 8.1, Firebird 1.5rc4, Oracle 8.1.7 and PostgreSQL 7.3) displayed the query results as expected with no truncation.

A more serious consequence of this behaviour is this:

mysql> SELECT 'abc' AS c1, '123' AS c2 
UNION
SELECT val1 AS c1, val2 AS c2 FROM union_test;
+------+------+
| c1 | c2 |
+------+------+
| abc | 123 |
+------+------+
1 row in set (0.20 sec)

where ALL is ommitted from the UNION clause. Even though each row contains different values MySQL treats them as being identical - which is clearly not the case.

Note that the UNION clause is supported in MySQL beginning with version 4.0 (see http://dev.mysql.com/doc/mysql/en/UNION.html).

1.11. Division by zero

mysql> SELECT 1/0;
+------+

| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

Other databases (tested: DB2 8.1, Firebird 1.5rc4, Oracle 8.1.7, PostgreSQL 7.3.4) all raise a "division by zero" error when performing the same calculation.

See: http://dev.mysql.com/doc/mysql/en/Arithmetic_functions.html

1.12. 'concatenation' || 'or'

In MySQL || is translated as a logical OR and not as a concatenation operator:

mysql> select 'a' || 'b';
+------------+
| 'a' || 'b' |
+------------+
| 0 |
+------------+
1 row in set (0.01 sec)

The expected result would be: ab. See: http://dev.mysql.com/doc/mysql/en/Logical_Operators.html.

To perform concatenation in MySQL use CONCAT() (see: http://dev.mysql.com/doc/mysql/en/String_functions.html).

To force || to be used as a string concatenation operator rather than a synonym for OR, MySQL must be started in ANSI mode. See: http://dev.mysql.com/doc/mysql/en/ANSI_mode.html.

1.13. What goes in - isn't (always) what comes out

So - your app messed up. It tried to insert an invalid value into a column. There are two options here: 1) the database throws an error and your app either deals with it gracefully or fails. 2) The database truncates the value, or takes a guess at what might be an alternative value and silently inserts it without giving you the teensiest hint that what you put in is different to what you'll get out.

mysql> CREATE TABLE bounds_test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
price NUMERIC(4,2),
code VARCHAR(8),
numbers_only INT
);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO bounds_test VALUES (

99999999999999,
21474.83,
'ABCDEFGHIJK',
'A quick brown dolphin...'
);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM bounds_test;
+------------+--------+----------+--------------+
| id | price | code | numbers_only |
+------------+--------+----------+--------------+
| 2147483647 | 999.99 | ABCDEFGH | 0 |
+------------+--------+----------+--------------+
1 row in set (0.01 sec)

(Note: in MySQL 4.1.x, the presence of warnings is notified on the query status line; executing SHOW WARNINGS after the INSERT displays Data truncated messages for each column).

As a nice extra touch note that although the price was defined with a precision of 4 digits, MySQL inserted a number containing 5. Possibly this is because MySQL internally adds an extra "digit" to store a minus sign, and because the storage space is there uses it with positive numbers to pack an extra digit in, if the explanation here: http://dev.mysql.com/doc/mysql/en/Numeric_types.html is anything to go by.

Other databases (tested: Firebird 1.5rc4, Oracle 8.1.7 and PostgreSQL 7.4) raised errors with the same data. On a column defined as NUMERIC(4,2) the highest value accepted in all databases was the expected 99.99.

For another reason why this is seriously bad database mojo see section 3.5

1.14. February 31st

Throughout history many different calendar systems have been developed around the world. Although the way of counting years still varies, most countries and regions have adopted the Roman-Nordic system of months and weekdays - except, ironically enough, a small corner of Scandinavia with a high dolphin population ;-).

mysql> CREATE TABLE datetest (id INT, a_date DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO datetest VALUES(1, '2003-02-31');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM datetest;
+------+------------+
| id | a_date |
+------+------------+
| 1 | 2003-02-31 |
+------+------------+
1 row in set (0.00 sec)

So, what's the day before February 31st?

mysql> SELECT DATE_SUB('2003-02-31', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_SUB('2003-02-31', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-03-02 |
+----------------------------------------+
1 row in set (0.00 sec)

Which is of course two days before the day after February 31st:

mysql> SELECT DATE_ADD('2003-02-31', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2003-02-31', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-03-04 |
+----------------------------------------+
1 row in set (0.00 sec)

So what kind of checking does MySQL do on date values? A hint:

 

If you use really malformed dates, the result is NULL.

 
--http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html 

Obviously, the 31st of February is not malformed enough. Let's try again:

mysql> SELECT DATE_ADD('2003-02-!!!!!!31!!!!!', INTERVAL 1 DAY);
+---------------------------------------------------+
| DATE_ADD('2003-02-!!!!!!31!!!!!', INTERVAL 1 DAY) |
+---------------------------------------------------+
| 2003-03-04 |
+---------------------------------------------------+
1 row in set (0.00 sec)

Nope.

mysql> SELECT DATE_ADD('2003-02-99', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2003-02-99', INTERVAL 1 DAY) |
+----------------------------------------+
| NULL |
+----------------------------------------+
1 row in set (0.00 sec

Hmmm. A pattern emerges...

 

The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999. Any date not within this range will revert to 0000-00-00. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application.

 
--http://dev.mysql.com/doc/mysql/en/DATETIME.html 

Makes you wonder why they bother...

Addendum: future MySQL versions are scheduled to contain server options which enable sane data checks, although the need to retain backwards-compatibility means these will probably not be enabled by default.

1.15. Space between function name and parenthesis

MySQL raises an error if there is a space between a function identifier and its parameter list:

mysql> SELECT MAX(id) FROM datetest;
+---------+
| MAX(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT MAX (id) FROM datetest;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'(id) FROM datetest' at line 1

The documentation says:

 

Note: By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. Spaces around function arguments are permitted, though.

 
--http://dev.mysql.com/doc/mysql/en/Functions.html 

Most other databases systems (including DB2, Firebird, MSSQL, Oracle and PostgreSQL) do not have a problem with whitespace in this context.


Comments
There is another gotcha in the NULL handling: If you define a column as NOT NULL and query the table with a IS NULL condition this should return no rows. But MySQL knows that the column cannot be NULL, thus simply "optimizes" the IS NULL condition away and returns all rows!!!!

See:
http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html
Posted by: Thomas | 2006-10-05 12:07
Thank's for a detailed list. Another gotcha is in MySQL's handling of GROUP BY-queries. The feature/gotcha is described in MySQL's manual 12.10.3: http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html

In short, MySQL allows non-aggregated columns to appear in the select list without having them in the group by-list.
Posted by: Jens Gyldenkærne Clausen | 2006-12-05 10:52
Here's another gotcha... any time you create or drop and index in mysql, it rebuilds the entire table and all existing indexes!

http://lists.mysql.com/mysql/202489

Apparently this still exists it 5.0: http://bugs.mysql.com/bug.php?id=2364
Posted by: Decibel | 2007-01-25 19:21
An impressive overview over MySQL flaws and gotchas. Thank you very much for collecting all these pieces of information!

Regarding <strong>1.15. Space between function name and parenthesis</strong>: This behaviour can be controlled by setting the 'SQL_MODE' 'IGNORE_SPACE'. See chapter <a href="http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html">5.2.5. SQL Modes</a> in the MySQL Documentation.
Posted by: Sascha A. Carlin | 2007-02-19 09:37
While I think that it is good to have this information here, I also think that v5.0 is stable enough that these items need to be revisited. I have taken a look at a few, and about half have been "fixed".
Posted by: WStemple | 2007-05-23 19:53
Regarding Jens Gyldenkærne Clausen's comment about GROUP BY:

"In short, MySQL allows non-aggregated columns to appear in the select list without having them in the group by-list."

The SQL standard allows you to omit any column that is functionally dependent on the grouped key from the GROUP BY clause. See here for more information and stop spreading a myth that this is a standard requirement:

http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html

Cheers,

Jay Pipes
Posted by: Jay Pipes | 2007-05-30 02:20
Also note, as Sascha Carlin and WStemple have said above, that the vast majority of these gotchas have been solved with SQL_MODE in MySQL 5.0. The SQL_MODE must be set in your configuration file once in then you're done.

For those who say "Why isn't it that way by default?" Answer: backwards compatibility.
Posted by: Jay Pipes | 2007-05-30 02:23
Also doesnt work:

DELETE FROM foo WHERE foo.a = bar.b ;
Posted by: Enrico Weigelt | 2007-07-31 05:58
i keep getting a invalid argument error when i have a auto increment which sets up my client id automaticly and i keep getting the error invalid argument any help would be appreciated

mike
Posted by: mike young | 2007-11-15 01:29
Will this article updated to mysql 5.0 or 5.1?
the first one is fixed in mysql 5。
Posted by: Ben | 2008-01-18 09:41
Good job by the author with all the references. INHO, this has to be updated to any new stable release.
Posted by: Rizwan | 2008-02-12 08:46
A further clarification to 1.2 about negative values in auto_increment, I just found out (the REALLY hard way as in I'll have to rewrite half my app) that this problem also appears to apply to ZERO. I had my app set up to use ID 0 as the initial content and autoincrement from there, but I kept getting my 0 content set to 1 whenever I copied tables (i.e. from my dev server to production, etc.). I was manually going in and fixing it whenever it popped up (annoying to say the least but I didn't have time to do anything proper about it). Then I finally got fed up and did enough google searches that I found this page. So if you're using auto_increment, don't use a value of zero. I'm looking at either rewriting half my app or not using auto_increment, neither of which is a great option, although dropping auto_increment would certainly be the easiest.
Posted by: Tom Callahan | 2008-07-12 03:39
Oh, and this is in 5.0.51 so nothing's changed in 5 apparently.
Posted by: Tom Callahan | 2008-07-12 03:43
I made two(2)deposits to www.imperiainvest.net.Everytime I go on REQUEST TEP CONTRACT and to confirme my deposits ,the following message appears: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DIN ASSI GREGOIRE', address = '01 bp 1304 Abidjan 01', ' at line 3
Warning: Cannot modify header information - headers already sent by (output started at /home/doreenp/public_html/memberarea/inc/deposit.account.confirm.inc:281) in /home/doreenp/public_html/memberarea/inc/deposit.account.confirm.inc on line 286
I don't know how to process.

PLEASE I NEED YOUR HELP.
Posted by: N'DIN ASSI GREGOIRE | 2008-09-17 12:50
It was good of you to list all of this.

It may also have been part of what sparked the MySQL guys to go ahead and come out with version 5. ;-)


--
Furry cows moo and decompress.
Posted by: Wyrd | 2008-10-21 19:26
N'DIN ASSI GREGOIRE. The problem is that you put a ' in your name :-). type it without the '.
Posted by: grep | 2008-12-30 17:52
I have noticed that people who hate george bush also hate internet explorer and Windows in general. You people are mentally ill, sorry to say.
Posted by: Mike | 2009-02-07 22:58
adding constraint: id column should be less than 20 or in id column should hold 10 letters in which 4 letter should either a, b, c
Posted by: rama | 2009-04-27 05:39