| Bug #72222 | Strange interaction of SQL_MODE=ANSI and partition by TIMESTAMP | ||
|---|---|---|---|
| Submitted: | 3 Apr 2014 14:21 | Modified: | 3 Apr 2014 16:42 |
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Patch pending | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | mysql-5.5.35; mysql-5.6.16 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[3 Apr 2014 14:27]
Hartmut Holzgraefe
The problem seems to be in the ansi qotes in
PARTITION BY RANGE ( UNIX_TIMESTAMP("t"))
only, when removing these everything works fine
[3 Apr 2014 16:42]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[28 May 1:12]
MySQL Admin
Posted by developer: Bug status updated to 'Patch pending'
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 ...