Bug #46478 timestamp field incorrectly defaulted when partition is reorganized
Submitted: 30 Jul 2009 14:46 Modified: 16 Sep 2009 8:29
Reporter: Anca Dogaru Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.36 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: current_timestamp, partitioning

[30 Jul 2009 14:46] Anca Dogaru
Description:
If a partitioned table has a timestamp column with a default value of CURRENT_TIMESTAMP
but no ON UPDATE value specified the timastamp value incorrectly gets set to CURRENT_TIMESTAMP when partitions are reorganized.

How to repeat:

CREATE TABLE `partitioning_table` (
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(10) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`added`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `partitioning_table` PARTITION BY RANGE ( TO_DAYS(`added`) ) (
PARTITION p1 VALUES LESS THAN (733407),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

INSERT INTO `partitioning_table` VALUES ('2007-07-30 17:35:48', 'in parition p1');
INSERT INTO `partitioning_table` VALUES ('2009-07-14 17:35:55', 'in pmax');
INSERT INTO `partitioning_table` VALUES ('2009-09-21 17:31:42', 'in pmax');

select * from partitioning_table;
+---------------------+------------+
| added               | name       |
+---------------------+------------+
| 2007-07-30 17:35:48 | in paritio |
| 2009-07-14 17:35:55 | in pmax    |
| 2009-09-21 17:31:42 | in pmax    |
+---------------------+------------+
3 rows in set (0.00 sec)

mysql> select from_days('733407');
+---------------------+
| from_days('733407') |
+---------------------+
| 2008-01-01          |
+---------------------+
1 row in set (0.00 sec)

create another partiton from 2008-01-01   to 2009-07-16  

mysql> select to_days('2009-07-16');
+-----------------------+
| to_days('2009-07-16') |
+-----------------------+
|                733969 |
+-----------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `partitioning_table` REORGANIZE PARTITION pmax INTO ( PARTITION p3  VALUES LESS THAN (733969),  PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>  select * from partitioning_table;
+---------------------+------------+
| added               | name       |
+---------------------+------------+
| 2007-07-30 17:35:48 | in paritio |
| 2009-07-30 17:39:29 | in pmax    |
| 2009-07-30 17:39:29 | in pmax    |
+---------------------+------------+
3 rows in set (0.00 sec)

Last 2 records are updated with NOW();

Suggested fix:
reorganizing partitions should not affect timestamp records.

This bug may be related to http://bugs.mysql.com/bug.php?id=38272
[31 Jul 2009 7:02] Sveta Smirnova
Thank you for the report.

Verified as described.
[5 Aug 2009 15:27] Mikael Ronström
The change of record data for timestamp records can be avoided
by setting table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET
in the beginning of fast_alter_partition_table

The problem that timestamp records can move due to new timezone
is however still an issue, but that's a different bug
[6 Aug 2009 12:29] 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/80267

3057 Mattias Jonsson	2009-08-06
      Bug#46478: timestamp field incorrectly defaulted
      when partition is reoganized.
      
      Problem was that table->timestamp_field_type was not changed
      before copying rows between partitions.
      
      fixed by setting it to TIMESTAMP_NO_AUTO_SET as the first thing
      in fast_alter_partition_table, so that all if-branches is covered.
[7 Aug 2009 15:58] Mattias Jonsson
Mikael approved it.
[2 Sep 2009 16:42] Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:mattias.jonsson@sun.com-20090812100305-d325jj82gfeu4mal) (merge vers: 5.1.38) (pib:11)
[3 Sep 2009 15:05] Jon Stephens
Documented bugfix in the 5.1.39 changelog as follows:

        A partitioned table having a TIMESTAMP column with a default
        value of CURRENT_TIMESTAMP and this column was not defined using
        an ON UPDATE option, an ALTER TABLE ... REORGANIZE PARTITION
        statement on the table caused the TIMESTAMP column value to be
        set to CURRENT_TIMESTAMP regardless.

Set status to NDI pending merge to 5.4 tree.
[14 Sep 2009 16:05] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 8:29] Jon Stephens
Bugfix also documented in the 5.4.4 changelog.

Closed.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:23] Paul DuBois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)