Bug #33429 Inndob innodb_file_per_table drop and add partition -> lost of other partition
Submitted: 21 Dec 2007 2:23 Modified: 27 Mar 2008 22:02
Reporter: Nicolae Namolovan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.22 OS:FreeBSD (6.1)
Assigned to: Mikael Ronström CPU Architecture:Any
Tags: Contribution, Drop, inndob, innodb_file_per_table

[21 Dec 2007 2:23] Nicolae Namolovan 
Description:
Had a table like this one

I have innodb_file_per_table in my.cnf, so each partition have own .ibd file too

CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lastcomment` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88134 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (96695) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

I where droping a parition, and after trying to add a new one

ALTER TABLE table DROP PARTITION p1;
ALTER TABLE table ADD PARTITION (PARTITION p1 VALUES LESS THAN (179721));

This what I got 

071220 23:33:59  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './torrent1/torrents#P#p1.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.

Other two partitions became completly unusable.. DISCARD TABLESPACE and IMPORT TABLESPACE don't work at all on partitions.. Really a nightmare.. And lost of data are the worst (luckily I had backups).. You should really fix that.

Thank you for your work.

How to repeat:
CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lastcomment` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88134 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (96695) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

ALTER TABLE table DROP PARTITION p1;
ALTER TABLE table ADD PARTITION (PARTITION p1 VALUES LESS THAN (179721));
SELECT COUNT(id) FROM table;
[21 Dec 2007 11:22] Nicolae Namolovan 
changed the a name a bit.

And happy holidays ! I think you have some party these days :)
[21 Dec 2007 13:47] MySQL Verification Team 
Thank you for the bug report.

[miguel@amanhecer 5.1]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `table` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `lastcomment` int(10) unsigned NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=88134 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id)
    -> (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN
    -> (96695) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE `table` DROP PARTITION p1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `table` ADD PARTITION (PARTITION p1 VALUES LESS THAN (179721));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `table`;
ERROR 1064 (42000): MAXVALUE can only be used in last partition definition near ') ENGINE = InnoDB)' at line 1
mysql>
[4 Jan 2008 6:30] Lu Jingdong 
I did some test and server crashed.

mysql> CREATE TABLE t (id int unsigned) ENGINE=InnoDB  PARTITION BY RANGE (id)
    ->           (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
    ->            PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.06 sec)

mysql> alter table t add partition (partition p2 values less than (100));
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t\G
ERROR 2013 (HY000): Lost connection to MySQL server during query

I think we should make sure that MAXVALUE is used in last partition definition. Otherwise it will get an error result from the function of "partition_info::check_range_constants" if we use "unsigned int" type during executing the "alter table ... add partition ..." operation. I give a patch and test result as following.

The patch:
--- sql/sql_partition.cc.orig   2008-01-04 11:06:08.000000000 +0800
+++ sql/sql_partition.cc        2008-01-04 13:53:51.786227232 +0800
@@ -4313,6 +4313,11 @@
       {
         DBUG_RETURN(TRUE);
       }
+      if (tab_part_info->defined_max_value)
+      {
+        my_error(ER_PARTITION_MAXVALUE_ERROR, MYF(0));
+        DBUG_RETURN(TRUE);
+      }
 /*
 Handling of on-line cases:

Test result:
mysql> CREATE TABLE `t` (
    ->        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->        `lastcomment` int(10) unsigned NOT NULL DEFAULT '0',
    ->        PRIMARY KEY (`id`)
    ->      ) ENGINE=InnoDB AUTO_INCREMENT=88134 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY
    -> RANGE (id)
    ->      (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p1 VALUES LESS
    -> THAN
    ->      (96695) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
    -> */;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> ALTER TABLE `t` DROP PARTITION p1;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `t` ADD PARTITION (PARTITION p1 VALUES LESS THAN (179721));
ERROR 1478 (HY000): MAXVALUE can only be used in last partition definition

mysql> show create table `t`;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `lastcomment` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88134 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (88134) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(id) FROM t;
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.06 sec)
[4 Jan 2008 6:39] Lu Jingdong 
I also think the following judgement is necessary.

diff -Nur sql/table.cc.orig sql/table.cc
--- sql/table.cc.orig   2007-12-26 14:30:40.000000000 +0800
+++ sql/table.cc        2008-01-04 14:37:52.768736864 +0800
@@ -1718,8 +1718,11 @@
                                 outparam, is_create_table,
                                 share->default_part_db_type,
                                 &work_part_info_used);
-    outparam->part_info->is_auto_partitioned= share->auto_partitioned;
-    DBUG_PRINT("info", ("autopartitioned: %u", share->auto_partitioned));
+    if (outparam->part_info)
+    {
+      outparam->part_info->is_auto_partitioned= share->auto_partitioned;
+      DBUG_PRINT("info", ("autopartitioned: %u", share->auto_partitioned));
+    }
     /* we should perform the fix_partition_func in either local or
        caller's arena depending on work_part_info_used value
     */
[4 Jan 2008 10:16] Sergei Golubchik 
patch: http://lists.mysql.com/internals/35262
[28 Jan 2008 15:01] Mikael Ronström 
Fixed according to proposed patch
[28 Jan 2008 15:08] Mikael Ronström 
Patch

Attachment: bug33429.patch (text/x-patch), 2.65 KiB.
[1 Feb 2008 23:47] Mattias Jonsson 
OK to push.
[7 Feb 2008 10:37] Mattias Jonsson 
Patch approved (Checked with Mikael that it was sufficient with me as reviewer)
[20 Feb 2008 22:53] Augusto Bott 
I can see the status seems to be 'Patch queued', however I need to ask you guys... do you have any kind of E.T.A on when this patch/fix will be pushed and released to us, mortals affected by this show-stopper?
[27 Mar 2008 11:18] Bugs System 
Pushed into 5.1.24-rc
[27 Mar 2008 17:50] Bugs System 
Pushed into 6.0.5-alpha
[27 Mar 2008 22:02] Jon Stephens 
Documented bugfix in the 5.1.24 and 6.0.5 changelogs as follows:

        Enabling innodb_file_per_table produced problems with
        partitioning and tablespace operations on partitioned
        InnoDB tables, in some cases leading to corrupt
        partitions or causing the server to crash.
[2 Apr 2008 16:10] Jon Stephens 
Also noted bugfix in the 5.1.23-ndb-6.3.11 changelog.