MySQL Bugs: #46478: timestamp field incorrectly defaulted when partition is reorganized

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

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

Thank you for the report.

Verified as described.

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

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.

Mikael approved it.

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)

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.

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)

Bugfix also documented in the 5.4.4 changelog.

Closed.

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)

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)

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)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

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)