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:
None 
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
Triage: D2 (Serious) / R2 (Low) / E1 (None/Negligible)

[21 Jul 2008 21:50] Dale Davenport
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.
[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.