| Bug #34119 | CREATE INDEX changes data if no_zero_in_date | ||
|---|---|---|---|
| Submitted: | 29 Jan 2008 0:42 | Modified: | 27 Mar 2008 23:38 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.24-rc-debug | OS: | Linux (SUSE 10 / 64-bit) |
| Assigned to: | CPU Architecture: | Any | |
[29 Jan 2008 1:35]
MySQL Verification Team
Thank you for the bug report. Verified as described.
create table mmm (s1 date);
insert into mmm values ('2000-00-00'),('2000-01-00'),('2000-00-01');
set sql_mode=no_zero_in_date;
select * from mmm;
create index i on mmm (s1);
select * from mmm;
set sql_mode='';
select * from mmm;
[24 Jun 2008 0:17]
Joseph Spadavecchia
Also, 5.0.51b has the bug.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.51b Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database foo;
Query OK, 1 row affected (0.00 sec)
mysql> use foo;
Database changed
mysql> create table mmm (s1 date);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into mmm values ('2000-00-00'),('2000-01-00'),('2000-00-01');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> set sql_mode=no_zero_in_date;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mmm;
+------------+
| s1 |
+------------+
| 2000-00-00 |
| 2000-01-00 |
| 2000-00-01 |
+------------+
3 rows in set (0.00 sec)
mysql> create index i on mmm (s1);
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> select * from mmm;
+------------+
| s1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mmm;
+------------+
| s1 |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)
[24 Jun 2008 21:41]
Joseph Spadavecchia
The source code (5.0.51b) reveals the following.
Line 3161 of libmysqld/sql_parse.cc states
/*
Currently CREATE INDEX or DROP INDEX cause a full table rebuild
and thus classify as slow administrative statements just like
ALTER TABLE.
*/
So, adding an index rebuilds the table via mysql_alter_table(). As part of the rebuild, it seems the values (e.g. dates) are copied into a new table. In the problem example, the rebuild comes after no_zero_in_date is set. Therefore, the dates are truncated and warnings are generated. The warnings can be seen in the example.
mysql> create index i on mmm (s1);
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 's1' at row 1 |
| Warning | 1265 | Data truncated for column 's1' at row 2 |
| Warning | 1265 | Data truncated for column 's1' at row 3 |
+---------+------+-----------------------------------------+
3 rows in set (0.00 sec)
Although CREATE INDEX should make indexes and not change data, it currently rebuilds the table, which can result changed data.
There is hope -- line 3142 of libmysqld/sql_parse.cc states
"In the future ALTER TABLE will notice that only added
indexes and create these one by one for the existing table
without having to do a full rebuild."
[7 Oct 2008 20:27]
Konstantin Osipov
To fix this reliably we need to store sql_mode of the table in the .frm, and activate it during ALTER. An alternative is to abort CREATE INDEX in case of any warning, however, that would become quite complex if CREATE INDEX is part of a more sophisticated ALTER TABLE specification. We can't simply drop no_zero_in_date from sql_mode during alter, since that would lead to wrong results in case of CHANGE COLUMN alter specification.
Description: I create a table with a date column. I insert a zero month and a zero day. I set sql_mode to no_zero_in_date. I select. I get what I inserted. I create an index. I select. I don't get what I inserted. I set sql_mode to ''. I select. I don't get what I inserted. CREATE INDEX should make indexes, not change data. How to repeat: mysql> create table mmm (s1 date); Query OK, 0 rows affected (0.05 sec) mysql> insert into mmm values ('2000-00-00'),('2000-01-00'),('2000-00-01'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> set sql_mode=no_zero_in_date; Query OK, 0 rows affected (0.00 sec) mysql> select * from mmm; +------------+ | s1 | +------------+ | 2000-00-00 | | 2000-01-00 | | 2000-00-01 | +------------+ 3 rows in set (0.00 sec) mysql> create index i on mmm (s1); Query OK, 3 rows affected, 3 warnings (0.02 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> select * from mmm; +------------+ | s1 | +------------+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | +------------+ 3 rows in set (0.00 sec) mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select * from mmm; +------------+ | s1 | +------------+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | +------------+ 3 rows in set (0.00 sec)