| Bug #38272 | timestamps fields incorrectly defaulted on update accross partitions. | ||
|---|---|---|---|
| Submitted: | 21 Jul 2008 21:50 | Modified: | 15 Sep 2008 13:59 |
| Reporter: | Dale Davenport | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.24, 5.1.26 | OS: | Any |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
| Tags: | InnoDB Partition Timestamp | ||
[22 Jul 2008 6:46]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.1.26-rc:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.26-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS `ts_test`;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql>
mysql> CREATE TABLE `ts_test` (
-> `ID` bigint(20) NOT NULL AUTO_INCREMENT,
-> `createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `number` int,
-> PRIMARY KEY (`ID`, number)
-> ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (number) (
-> PARTITION p0 VALUES LESS THAN (6),
-> PARTITION p1 VALUES LESS THAN (11)
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO ts_test SET number=1;
Query OK, 1 row affected (0.13 sec)
mysql> SELECT * FROM ts_test;
+----+---------------------+--------+
| ID | createdDate | number |
+----+---------------------+--------+
| 27 | 2008-07-22 09:43:14 | 1 |
+----+---------------------+--------+
1 row in set (0.01 sec)
mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
| 0 |
+----------+
1 row in set (5.02 sec)
mysql> UPDATE ts_test SET number=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM ts_test;
+----+---------------------+--------+
| ID | createdDate | number |
+----+---------------------+--------+
| 27 | 2008-07-22 09:43:14 | 3 |
+----+---------------------+--------+
1 row in set (0.00 sec)
mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
mysql>
mysql> UPDATE ts_test SET number=6;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM ts_test;
+----+---------------------+--------+
| ID | createdDate | number |
+----+---------------------+--------+
| 27 | 2008-07-22 09:43:24 | 6 |
+----+---------------------+--------+
1 row in set (0.00 sec)
[27 Jul 2008 14:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/50573 2680 Alexey Botchkov 2008-07-27 Bug#38272 timestamps fields incorrectly defaulted on update accross partitions. It's not Innodb-specific bug. ha_partition::update_row() didn't set table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET when orig_timestamp_type == TIMESTAMP_AUTO_SET_ON_INSERT. So that a partition sets the timestamp field when a record is moved to a different partition. Fixed by doing '= TIMESTAMP_NO_AUTO_SET' unconditionally. Also ha_partition::write_row() is fixed in same way as now Field_timestamp::set() is called twice in SET_ON_INSERT case. per-file messages: mysql-test/r/partition.result Bug#38272 timestamps fields incorrectly defaulted on update accross partitions. test result mysql-test/t/partition.test Bug#38272 timestamps fields incorrectly defaulted on update accross partitions. test case sql/ha_partition.cc Bug#38272 timestamps fields incorrectly defaulted on update accross partitions. Do table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET unconditionally in ha_partition::update_row and ::write_row()
[15 Aug 2008 18:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/51764 2704 Chad MILLER 2008-08-15 Bug#38272: timestamps fields incorrectly defaulted on \ update accross partitions. It's not Innodb-specific bug. ha_partition::update_row() didn't set table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET when orig_timestamp_type == TIMESTAMP_AUTO_SET_ON_INSERT. So that a partition sets the timestamp field when a record is moved to a different partition. Fixed by doing '= TIMESTAMP_NO_AUTO_SET' unconditionally. Also ha_partition::write_row() is fixed in same way as now Field_timestamp::set() is called twice in SET_ON_INSERT case. (Chad queues this patch on demand by Trudy/Davi.)
[15 Aug 2008 18:39]
Chad MILLER
Queued to 5.1-bugteam and 6.0-bugteam.
[15 Aug 2008 18:40]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/51767 2704 Chad MILLER 2008-08-15 Bug#38272: timestamps fields incorrectly defaulted on \ update accross partitions. It's not Innodb-specific bug. ha_partition::update_row() didn't set table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET when orig_timestamp_type == TIMESTAMP_AUTO_SET_ON_INSERT. So that a partition sets the timestamp field when a record is moved to a different partition. Fixed by doing '= TIMESTAMP_NO_AUTO_SET' unconditionally. Also ha_partition::write_row() is fixed in same way as now Field_timestamp::set() is called twice in SET_ON_INSERT case. (Chad queues this patch on demand by Trudy/Davi.)
[21 Aug 2008 18:18]
Bugs System
Pushed into 5.1.28 (revid:chad@mysql.com-20080815182625-yfhxtes9hy8cxboq) (version source revid:sergefp@mysql.com-20080819132519-eimtstp3bx89ya9d) (pib:3)
[22 Aug 2008 8:17]
Jon Stephens
Documented in the 5.1.28 changelog as follows:
When a partitioned table had a TIMESTAMP column
defined with CURRENT_TIMESTAMP as the default but
with no ON UPDATE clause, the column's value was
incorrectly set to CURRENT_TIMESTAMP when
updating across partitions.
Set bug status back to Patch Queued pending merge to 6.0.
[14 Sep 2008 6:16]
Bugs System
Pushed into 6.0.7-alpha (revid:chad@mysql.com-20080815182625-yfhxtes9hy8cxboq) (version source revid:timothy.smith@sun.com-20080813081348-d1t79whh0xmepllq) (pib:3)
[15 Sep 2008 13:59]
Jon Stephens
Fix also documented in 6.0.7 changelog.
Description: If a partitioned table has a timestamp column with a default value of CURRENT_TIMESTAMP but no ON UPDATE value specified the value incorrectly gets set to CURRENT_TIMESTAMP when updating across partitions. It looks like when the row is moved to a different partition the on create default value is set even though the operation is an update. How to repeat: Example SQL ============================================= DROP TABLE IF EXISTS `ts_test`; CREATE TABLE `ts_test` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `number` int, PRIMARY KEY (`ID`, number) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 PARTITION BY RANGE (number) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11) ); INSERT INTO ts_test SET number=1; SELECT * FROM ts_test; SELECT SLEEP(5); UPDATE ts_test SET number=3; SELECT * FROM ts_test; SELECT SLEEP(5); UPDATE ts_test SET number=6; SELECT * FROM ts_test; ============================================= Example output: ============================================= mysql> source /Users/dale/ts_test.sql Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.07 sec) Query OK, 1 row affected (0.00 sec) +----+---------------------+--------+ | ID | createdDate | number | +----+---------------------+--------+ | 27 | 2008-07-21 15:44:35 | 1 | +----+---------------------+--------+ 1 row in set (0.00 sec) +----------+ | SLEEP(5) | +----------+ | 0 | +----------+ 1 row in set (5.00 sec) Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 +----+---------------------+--------+ | ID | createdDate | number | +----+---------------------+--------+ | 27 | 2008-07-21 15:44:35 | 3 | +----+---------------------+--------+ 1 row in set (0.00 sec) +----------+ | SLEEP(5) | +----------+ | 0 | +----------+ 1 row in set (5.00 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 +----+---------------------+--------+ | ID | createdDate | number | +----+---------------------+--------+ | 27 | 2008-07-21 15:44:45 | 6 | +----+---------------------+--------+ 1 row in set (0.00 sec) ============================================= Suggested fix: Unknown.