Bug #46968 'Incorrect information in file' with partitioned table created with ANSI_QUOTES
Submitted: 28 Aug 2009 5:14 Modified: 28 Aug 2009 6:23
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.39, 6.0.12, next bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: ANSI_QUOTES, Incorrect information in file

[28 Aug 2009 5:14] Shane Bester
testcase outputs this error:

mysql> show create table `t1`;
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 '"1" VALUES LESS THAN (1) ENGINE = MyISAM)' at line 2

mysql error log contains this:

090828  7:13:14 [ERROR] mysqld: Incorrect information in file: '.\test\t1.frm'

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
set session sql_mode='ANSI_QUOTES';
alter table `t1` partition by range(`a`)(partition `1` values less than (1));
set session sql_mode='';
show create table `t1`;
[28 Aug 2009 6:23] Sveta Smirnova
Thank you for the report.

Verified as described.
[28 Aug 2009 15:53] Peter Gulutzan
As the MySQL Reference Manual says:
"Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. As discussed in Section 5.1.8, “Server SQL Modes”, the results of many MySQL functions and operators may change according to the server SQL mode. Therefore, a change in the SQL mode at any time after the creation of partitioned tables may lead to major changes in the behavior of such tables, and could easily lead to corruption or loss of data. For these reasons, it is strongly recommended that you never change the server SQL mode after creating partitioned tables."
[14 Jan 2010 10:04] Jon Stephens
Updated docs as requested by James: http://lists.mysql.com/commits/96856
[19 Jun 2014 13:45] Mattias Jonsson
Related to bug#18336321 (fix exists in rb#5797).