MySQL Bugs: #42849: innodb crash with varying time_zone on partitioned timestamp primary key

Bug #42849	innodb crash with varying time_zone on partitioned timestamp primary key
Submitted:	13 Feb 2009 21:17	Modified:	15 Mar 2010 5:19
Reporter:	John Cesario	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.30, 5.1.32, 5.1.41, 6.0.9	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
 1 table - logfile (partitioned table)
 piped 1.3 million rows into table.  then ran "delete from logfile"

 090213 13:54:47  InnoDB: Assertion failure in thread 1141414208 in file row/row0mysql.c line 1378
 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON
 InnoDB: We intentionally generate a memory trap.
 InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
 InnoDB: If you get repeated assertion failures or crashes, even
 InnoDB: immediately after the mysqld startup, there may be
 InnoDB: corruption in the InnoDB tablespace. Please refer to
 InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
 InnoDB: about forcing recovery.
 /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0x895f2e]
 /usr/local/mysql/bin/mysqld(handle_segfault+0x322)[0x5d1e82]
 /lib64/libpthread.so.0[0x2ae752f87c10]
 /lib64/libc.so.6(gsignal+0x35)[0x2ae753569b95]
 /lib64/libc.so.6(abort+0x110)[0x2ae75356af90]
 /usr/local/mysql/bin/mysqld[0x7e4606]
 /usr/local/mysql/bin/mysqld(_ZN11ha_innobase10delete_rowEPKh+0x87)[0x7649e7]
 /usr/local/mysql/bin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x7f)[0x6bdc2f]
 /usr/local/mysql/bin/mysqld(_ZN12ha_partition10delete_rowEPKh+0x7b)[0x6c22bb]
 /usr/local/mysql/bin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x7f)[0x6bdc2f]
 /usr/local/mysql/bin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP11st_sql_listyyb+0x965)[0x662505]
 /usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0xe62)[0x5e2c82]
 /usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5e7647]
 /usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xcf3)[0x5e8343]
 /usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5e8d56]
 /usr/local/mysql/bin/mysqld(handle_one_connection+0x236)[0x5dba26]
 /lib64/libpthread.so.0[0x2ae752f80143]
 /lib64/libc.so.6(__clone+0x6d)[0x2ae7535f974d]
 090213 13:54:47 - mysqld got signal 6 ;

How to repeat:
 1 table - logfile (partitioned table)
 piped 1.3 million rows into table.  then ran "delete from logfile"

CREATE TABLE `logfile` (
  `logfile_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `site` varchar(20) DEFAULT NULL,
  `hostname` varchar(100) NOT NULL DEFAULT '',
  `pathname` varchar(200) NOT NULL DEFAULT '',
  `filename` varchar(200) NOT NULL DEFAULT '',
  `log_start_time` datetime DEFAULT NULL,
  `log_end_time` datetime DEFAULT NULL,
  `entry_start_time` datetime DEFAULT NULL,
  `entry_end_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modification_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `size` int(11) NOT NULL DEFAULT '0',
  `num_requests` int(11) NOT NULL DEFAULT '0',
  `comment` varchar(200) NOT NULL DEFAULT '',
  `type` varchar(20) DEFAULT NULL,
  `num_bytes` double DEFAULT NULL,
  `num_seconds` double DEFAULT NULL,
  `num_packets` double DEFAULT NULL,
  `incomplete` tinyint(1) DEFAULT '0',
  `is_fuzzy` tinyint(4) NOT NULL DEFAULT '0',
  `index_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `checksum` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`logfile_id`,`entry_end_time`),
  UNIQUE KEY `pathname` (`pathname`,`entry_end_time`),
  UNIQUE KEY `hostname` (`hostname`,`type`,`filename`,`entry_end_time`),
  KEY `type` (`type`,`incomplete`,`entry_end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (TO_DAYS(entry_end_time)) (PARTITION p733636 VALUES LESS THAN (733636) ENGINE = InnoDB, PARTITION p733638 VALUES LESS THAN (733638) ENGINE = InnoDB, PARTITION p733640 VALUES LESS THAN (733640) ENGINE = InnoDB, PARTITION p733642 VALUES LESS THAN (733642) ENGINE = InnoDB, PARTITION p733644 VALUES LESS THAN (733644) ENGINE = InnoDB, PARTITION p733646 VALUES LESS THAN (733646) ENGINE = InnoDB, PARTITION p733648 VALUES LESS THAN (733648) ENGINE = InnoDB, PARTITION p733650 VALUES LESS THAN (733650) ENGINE = InnoDB, PARTITION p733652 VALUES LESS THAN (733652) ENGINE = InnoDB, PARTITION p733654 VALUES LESS THAN (733654) ENGINE = InnoDB, PARTITION p733656 VALUES LESS THAN (733656) ENGINE = InnoDB, PARTITION p733658 VALUES LESS THAN (733658) ENGINE = InnoDB, PARTITION p733660 VALUES LESS THAN (733660) ENGINE = InnoDB, PARTITION p733662 VALUES LESS THAN (733662) ENGINE = InnoDB, PARTITION p733664 VALUES LESS THAN (733664) ENGINE = InnoDB, PARTITION p733666 VALUES LESS THAN (733666) ENGINE = InnoDB, PARTITION p733668 VALUES LESS THAN (733668) ENGINE = InnoDB, PARTITION p733670 VALUES LESS THAN (733670) ENGINE = InnoDB, PARTITION p733672 VALUES LESS THAN (733672) ENGINE = InnoDB, PARTITION p733674 VALUES LESS THAN (733674) ENGINE = InnoDB, PARTITION p733676 VALUES LESS THAN (733676) ENGINE = InnoDB, PARTITION p733678 VALUES LESS THAN (733678) ENGINE = InnoDB, PARTITION p733680 VALUES LESS THAN (733680) ENGINE = InnoDB, PARTITION p733682 VALUES LESS THAN (733682) ENGINE = InnoDB, PARTITION p733684 VALUES LESS THAN (733684) ENGINE = InnoDB, PARTITION p733686 VALUES LESS THAN (733686) ENGINE = InnoDB, PARTITION p733688 VALUES LESS THAN (733688) ENGINE = InnoDB, PARTITION p733690 VALUES LESS THAN (733690) ENGINE = InnoDB, PARTITION p733692 VALUES LESS THAN (733692) ENGINE = InnoDB, PARTITION p733694 VALUES LESS THAN (733694) ENGINE = InnoDB, PARTITION p733696 VALUES LESS THAN (733696) ENGINE = InnoDB, PARTITION p733698 VALUES LESS THAN (733698) ENGINE = InnoDB, PARTITION p733700 VALUES LESS THAN (733700) ENGINE = InnoDB, PARTITION p733702 VALUES LESS THAN (733702) ENGINE = InnoDB, PARTITION p733704 VALUES LESS THAN (733704) ENGINE = InnoDB, PARTITION p733706 VALUES LESS THAN (733706) ENGINE = InnoDB, PARTITION p733708 VALUES LESS THAN (733708) ENGINE = InnoDB, PARTITION p733710 VALUES LESS THAN (733710) ENGINE = InnoDB, PARTITION p733712 VALUES LESS THAN (733712) ENGINE = InnoDB, PARTITION p733714 VALUES LESS THAN (733714) ENGINE = InnoDB, PARTITION p733716 VALUES LESS THAN (733716) ENGINE = InnoDB, PARTITION p733718 VALUES LESS THAN (733718) ENGINE = InnoDB, PARTITION p733720 VALUES LESS THAN (733720) ENGINE = InnoDB, PARTITION p733722 VALUES LESS THAN (733722) ENGINE = InnoDB, PARTITION p733724 VALUES LESS THAN (733724) ENGINE = InnoDB, PARTITION p733726 VALUES LESS THAN (733726) ENGINE = InnoDB, PARTITION p733728 VALUES LESS THAN (733728) ENGINE = InnoDB, PARTITION p733730 VALUES LESS THAN (733730) ENGINE = InnoDB, PARTITION p733732 VALUES LESS THAN (733732) ENGINE = InnoDB, PARTITION p733734 VALUES LESS THAN (733734) ENGINE = InnoDB, PARTITION p733736 VALUES LESS THAN (733736) ENGINE = InnoDB, PARTITION p733738 VALUES LESS THAN (733738) ENGINE = InnoDB, PARTITION p733740 VALUES LESS THAN (733740) ENGINE = InnoDB, PARTITION p733742 VALUES LESS THAN (733742) ENGINE = InnoDB, PARTITION p733744 VALUES LESS THAN (733744) ENGINE = InnoDB, PARTITION p733746 VALUES LESS THAN (733746) ENGINE = InnoDB, PARTITION p733748 VALUES LESS THAN (733748) ENGINE = InnoDB, PARTITION p733750 VALUES LESS THAN (733750) ENGINE = InnoDB, PARTITION p733752 VALUES LESS THAN (733752) ENGINE = InnoDB, PARTITION p733754 VALUES LESS THAN (733754) ENGINE = InnoDB, PARTITION p733756 VALUES LESS THAN (733756) ENGINE = InnoDB, PARTITION p733758 VALUES LESS THAN (733758) ENGINE = InnoDB, PARTITION p733760 VALUES LESS THAN (733760) ENGINE = InnoDB, PARTITION p733762 VALUES LESS THAN (733762) ENGINE = InnoDB, PARTITION p733764 VALUES LESS THAN (733764) ENGINE = InnoDB, PARTITION p733766 VALUES LESS THAN (733766) ENGINE = InnoDB, PARTITION p733768 VALUES LESS THAN (733768) ENGINE = InnoDB, PARTITION p733770 VALUES LESS THAN (733770) ENGINE = InnoDB, PARTITION p733772 VALUES LESS THAN (733772) ENGINE = InnoDB, PARTITION p733774 VALUES LESS THAN (733774) ENGINE = InnoDB, PARTITION p733776 VALUES LESS THAN (733776) ENGINE = InnoDB, PARTITION p733778 VALUES LESS THAN (733778) ENGINE = InnoDB, PARTITION p733780 VALUES LESS THAN (733780) ENGINE = InnoDB, PARTITION p733782 VALUES LESS THAN (733782) ENGINE = InnoDB, PARTITION p733784 VALUES LESS THAN (733784) ENGINE = InnoDB, PARTITION p733786 VALUES LESS THAN (733786) ENGINE = InnoDB, PARTITION p733788 VALUES LESS THAN (733788) ENGINE = InnoDB, PARTITION p733790 VALUES LESS THAN (733790) ENGINE = InnoDB, PARTITION p733792 VALUES LESS THAN (733792) ENGINE = InnoDB, PARTITION p733794 VALUES LESS THAN (733794) ENGINE = InnoDB, PARTITION p733796 VALUES LESS THAN (733796) ENGINE = InnoDB, PARTITION p733798 VALUES LESS THAN (733798) ENGINE = InnoDB, PARTITION p733800 VALUES LESS THAN (733800) ENGINE = InnoDB, PARTITION p733802 VALUES LESS THAN (733802) ENGINE = InnoDB, PARTITION p733804 VALUES LESS THAN (733804) ENGINE = InnoDB, PARTITION p733806 VALUES LESS THAN (733806) ENGINE = InnoDB, PARTITION p733808 VALUES LESS THAN (733808) ENGINE = InnoDB, PARTITION p733810 VALUES LESS THAN (733810) ENGINE = InnoDB, PARTITION p733812 VALUES LESS THAN (733812) ENGINE = InnoDB, PARTITION p733814 VALUES LESS THAN (733814) ENGINE = InnoDB, PARTITION p733816 VALUES LESS THAN (733816) ENGINE = InnoDB, PARTITION p733818 VALUES LESS THAN (733818) ENGINE = InnoDB, PARTITION p733820 VALUES LESS THAN (733820) ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

5.1.31 is the latest version out right now. Can you point me to 5.1.33?

This also occurs in 5.1.31

This also happens on .24

TESTCASE:
----------------

set time_zone='+00:00';
drop table if exists `t1`;
create table `t1` (`a` timestamp not null default current_timestamp primary key 
) engine=innodb auto_increment=1 default charset=latin1 
partition by range (to_days(`a`)) (
partition p733638 values less than (733638) engine = innodb, 
partition pmax values less than maxvalue engine = innodb);
insert into `t1` values ('2008-08-19 04:55:07');
set time_zone='-7:00';
delete from t1;

Version: '5.1.31-enterprise-gpl-advanced'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition (GPL)
090227  5:57:17  InnoDB: Assertion failure in thread 5916 in file .\row\row0mysql.c line 1385
InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON

ps. previous comments that contributed no value to the bug itself, I made hidden, to improve readability. hope that's okay.

another excellent test case from Shane, reproduced with InnoDB Plugin.
Apparently Shane didn't want to state the obvious:

mysql> select from_days(733638);
+-------------------+
| from_days(733638) |
+-------------------+
| 2008-08-19        |
+-------------------+
1 row in set (0.00 sec)

Marko, please have a look.

I don't think that this is an InnoDB bug but a bug in partitioning. Does the bug occur with other transactional engines, such as Falcon?

MySQL will map a partitioned table to a number of InnoDB tables, one per partition. InnoDB does not contain any code related to partitions. It seems to me that the partitioning code could be missing some initialization when shifting partitions during delete.

status=feedback to reflect Marko's questions. Maybe it's not a bug at all:

mysql> select to_days(a) from t1;
+------------+
| to_days(a) |
+------------+
|     733638 |
+------------+
1 row in set (0.07 sec)

mysql> set time_zone='-7:00';
Query OK, 0 rows affected (0.02 sec)

mysql> select to_days(a) from t1;
+------------+
| to_days(a) |
+------------+
|     733637 |
+------------+
1 row in set (0.41 sec)

how can you partition on something that changes values depending on the application state(timezone)? If you do, what is supposed to happen? The manual doesn't seem to say anything so it's a case of 'the behavior is undefined'. For example, an eventual crash may occur.

I still think this is a bug, as it should not cause the server to crash.

Using Shane's test case does not cause crashes when using Falcon or Maria.  However, the behavior does differ.

With Maria, it simply deletes the record.

With Falcon, it generates an error:

mysql> delete from t1;
ERROR 1032 (HY000): Can't find record in 't1'
mysql> select * from t1;
+---------------------+
| a                   |
+---------------------+
| 2008-08-18 21:55:07 |
+---------------------+
1 row in set (0.02 sec)

Falcon's erroneous result on DELETE means it's most likely not InnoDB specific bug
but rather a bug in partitioning. I like InnoDB's behavior more, a crash
because of an assert is far better IMO than a wrong result from a query,
DELETE in this case. Partitioning on non deterministic expressions should really 
be forbidden.

This is indeed a bug in the partitioning. The rnd_* functions finds a row, and when ::delete_row is called, it tries to calculate the partition id, and because the tz is changed, the calculated partition is different from the calculation done when insert. And then it redirect the delete to another partition.

The bug also affects a SELECT only test:
insert into `t1` values ('2008-08-19 04:55:07', 'Test text');
select * from t1 where a = '2008-08-19 04:55:07';
a       b
2008-08-19 04:55:07     Test text
set time_zone='-7:00';
select * from t1;
a       b
2008-08-18 21:55:07     Test text
select * from t1 where a = '2008-08-18 21:55:07';
a       b
select * from t1 where a = '2008-08-19 21:55:07' or a = '2008-08-19 04:55:07';
a       b
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `b` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(`a`))
(PARTITION p733638 VALUES LESS THAN (733638) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

This only affects partitioning over TIMESTAMP fields in the combination with time/date functions.

DATE/DATETIME does not adjust by time_zone.

Hi,
The idea behind the patch is ok and approved.
The implementation isn't.

I suggest the following:
1) Replace all the return FALSE with
return new_function_name(arg);
2) Implement new_function_name in item.h+cc as
if (fixed... and the rest of the code you duplicated in many places).

new_function_name is a method on the item baseclass that is.

The patch is approved, under these conditions I will approve it,
Mattias will ensure that he doesn't approve it until the change
has been made.

There may be possible to use TIMESTAMP column in the partitioning function, but only together with UNIX_TIMESTAMP, which seems to cancel out the dependence of the sessions time_zone. I.e. PARTITION BY RANGE/LIST/HASH (UNIX_TIMESTAMP(timestamp_col)) should be deterministic and allowed.

However, UNIX_TIMESTAMP() is currently not allowed as a partitioning function for no apparent reasons.

The reason UNIX_TIMESTAMP is not allowed is IIRC because it depends on the session time_zone for DATE/DATETIME, but for TIMESTAMP (which is also time_zone dependent) it will always be the UTC time_t.

Short test:
SET @@time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP);
CREATE TABLE t2 (a DATE);
CREATE TABLE t3 (a DATETIME);
INSERT INTO t1 VALUES ('1970-01-02 00:00:00');
INSERT INTO t2 VALUES (CAST('1970-01-02' AS DATE));
INSERT INTO t3 VALUES (CAST('1970-01-02' AS DATETIME));
SELECT a FROM t1;
a
1970-01-02 00:00:00
SELECT a FROM t2;
a
1970-01-02
SELECT a FROM t3;
a
1970-01-02 00:00:00
SELECT UNIX_TIMESTAMP(a) FROM t1;
UNIX_TIMESTAMP(a)
86400
SELECT UNIX_TIMESTAMP(a) FROM t2;
UNIX_TIMESTAMP(a)
86400
SELECT UNIX_TIMESTAMP(a) FROM t3;
UNIX_TIMESTAMP(a)
86400
SET @@time_zone='+06:00';
SELECT a FROM t1;
a
1970-01-02 06:00:00
SELECT a FROM t2;
a
1970-01-02
SELECT a FROM t3;
a
1970-01-02 00:00:00
SELECT UNIX_TIMESTAMP(a) FROM t1;
UNIX_TIMESTAMP(a)
86400
SELECT UNIX_TIMESTAMP(a) FROM t2;
UNIX_TIMESTAMP(a)
64800
SELECT UNIX_TIMESTAMP(a) FROM t3;
UNIX_TIMESTAMP(a)
64800

So UNIX_TIMESTAMP(timestamp_col) will be OK to support, since it will not be affected by the sessions time_zone.

Patch approved with some minor changes to the tests.

To triage team:

This patch still allows opening a table which uses timestamp columns in the partitioning expression, but it adds a warning.

It also adds support for UNIX_TIMESTAMP(timestamp_column) to still allow timestamp data type to be used with partitioning (since UNIX_TIMESTAMP(timestamp_col) is not affected by time_zone).

And it prevents creating new tables (or alter tables to) using timestamp columns in the partitioning expression (unless it is UNIX_TIMESTAMP(timestamp column)).

I would like to have a SR51MRU tag on this bug, but I can understand if it is considered as a change in current (bad) behavior.

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/93810

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/93812

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/93837

3267 Alexey Kopytov	2009-12-14
      Post-merge test fix for bug #42849.

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/93968

3269 Alexey Kopytov	2009-12-14
      Post-push fixes for the bug #42849:
      
      All tests in the parts suite that use partitioning
      on a timezone-dependent expression were commented
      out, since it is not valid anymore.

After reviewing bug#49591, I think it is necessary to back port that fix for this bug too, since a dump or SHOW CREATE TABLE with UNIX_TIMESTAMP as a partitioning function will not be accepted in earlier versions. So the choice is to either fail (as now) or ignore the partitioning clause.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)

Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091216134707-o96eqw0u2ynvo9gm) (merge vers: 5.5.0-beta) (pib:15)

Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)

Documented bugfix in the 5.5.1 and 6.0.14 changelogs as follows:

      The UNIX_TIMESTAMP() function is now supported in partitioning
      expressions using TIMESTAMP columns. For example, it now possible 
      to create a partitioned table such as this one:

      CREATE TABLE t (c TIMESTAMP) 
      PARTITION BY RANGE ( UNIX_TIMESTAMP(c) ) (
          PARTITION p0 VALUES LESS THAN (631148400),
          PARTITION p1 VALUES LESS THAN (946681200),
          PARTITION p2 VALUES LESS THAN (MAXVALUE)
      );

Also updated permitted functions table in Partitioning Limitations section of Manual, added note to RANGE Partitioning section.

(Commit is here: http://lists.mysql.com/commits/95147)

Set bug status to NDI, waiting for merge to 5.1 tree.

Added the following to the existing changelog entry per developer comments:

        All other expressions involving TIMESTAMP values are now
        rejected with an error when attempting to create a new
        partitioned table or to alter an existing partitioned table.

        When accessing an existing partitioned table having a
        timezone-dependent partitioning function (where the table was
        using a previous version of MySQL), a warning rather than an
        error is issued. In such cases, you should fix the table. One
        way of doing this is to alter the table's partitioning
        expression so that it uses UNIX_TIMESTAMP().

Also noted this additional info in the 5.5 and 6.0 versions of the Partitioning chapter.

Set status back to NDI, still waiting for push to 5.1 tree.

Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:alexey.kopytov@sun.com-20091214172743-9rbjevolsluzxtwe) (merge vers: 5.1.42) (pib:16)

Also documented in the 5.1.43 changelog and 5.1 Manual Partitioning chapter. Closed.

If you are upgrading to MySQL 5.1.43 here are the steps you can do:
1. on the older mysql change timestamp to datetime (please check if this will not break your application first!):
alter table table_name change partition_date_field partition_date_field datetime
2. upgrade to MySQL 5.1.43+

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

No additional changelog entries required. Returning to Closed state.

Hello,

I had done partitioning use the syntax in online document.

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
after the explain of query, it shows it accessing all the partitions instead of accessing exact partition where the data contains.

Is it a bug or should i change something syntactically.