Description:
A table created with ansi-quoted identifiers and partitioned by UNIX_TIMESTAMP() of a timestamp column can't be dumped with mysqldump unless it is opened in table_cache
On closer look it turned out that if the table was created with SQL_MODE='ANSI_QUOTES' and indeed using ANSI quotes around identifiers
it is not possible to open it with ANSI_QUOTES mode being off
(mysqldump does "SET @@sql_mode='';" as its very first statement
after connecting)
The error message thrown on any attempt to open the table is
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
although UNIX_TIMESTAMP(timestamp_column) should not be
treated as timezone-dependent in >= 5.5, and isn't in
other circumstances
How to repeat:
DROP TABLE IF EXISTS t1;
SET @@SQL_MODE='ANSI_QUOTES';
CREATE TABLE "t1" ( "id" int NOT NULL
, "t" timestamp NOT NULL
, PRIMARY KEY ("id","t")
) PARTITION BY RANGE ( UNIX_TIMESTAMP("t"))
( PARTITION p0 VALUES LESS THAN (1371513600)
, PARTITION p1 VALUES LESS THAN (1371600000));
FLUSH TABLES;
SET @@SQL_MODE='';
SHOW CREATE TABLE t1\G -- fails
SET @@SQL_MODE='ANSI_QUOTES';
SHOW CREATE TABLE t1\G -- works, table is now in open tables cache
SET @@SQL_MODE='';
SHOW CREATE TABLE t1\G -- now works, too, as table is still open / cached
FLUSH TABLES;
SHOW CREATE TABLE t1\G -- table no longer in cache: fails again
Suggested fix:
Remove whatever side effect is causing this ...
Description: A table created with ansi-quoted identifiers and partitioned by UNIX_TIMESTAMP() of a timestamp column can't be dumped with mysqldump unless it is opened in table_cache On closer look it turned out that if the table was created with SQL_MODE='ANSI_QUOTES' and indeed using ANSI quotes around identifiers it is not possible to open it with ANSI_QUOTES mode being off (mysqldump does "SET @@sql_mode='';" as its very first statement after connecting) The error message thrown on any attempt to open the table is ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed although UNIX_TIMESTAMP(timestamp_column) should not be treated as timezone-dependent in >= 5.5, and isn't in other circumstances How to repeat: DROP TABLE IF EXISTS t1; SET @@SQL_MODE='ANSI_QUOTES'; CREATE TABLE "t1" ( "id" int NOT NULL , "t" timestamp NOT NULL , PRIMARY KEY ("id","t") ) PARTITION BY RANGE ( UNIX_TIMESTAMP("t")) ( PARTITION p0 VALUES LESS THAN (1371513600) , PARTITION p1 VALUES LESS THAN (1371600000)); FLUSH TABLES; SET @@SQL_MODE=''; SHOW CREATE TABLE t1\G -- fails SET @@SQL_MODE='ANSI_QUOTES'; SHOW CREATE TABLE t1\G -- works, table is now in open tables cache SET @@SQL_MODE=''; SHOW CREATE TABLE t1\G -- now works, too, as table is still open / cached FLUSH TABLES; SHOW CREATE TABLE t1\G -- table no longer in cache: fails again Suggested fix: Remove whatever side effect is causing this ...