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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.070 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.