| Bug #33429 | Inndob innodb_file_per_table drop and add partition -> lost of other partition | ||
|---|---|---|---|
| Submitted: | 21 Dec 2007 3:23 | Modified: | 27 Mar 23:02 |
| Reporter: | Nicolae Namolovan | ||
| Status: | Closed | ||
| Category: | Server: Partition | Severity: | S1 (Critical) |
| Version: | 5.1.22 | OS: | FreeBSD (6.1) |
| Assigned to: | Mikael Ronstrom | Target Version: | 5.1+ |
| Tags: | Contribution, Drop, innodb_file_per_table, inndob | ||
| Triage: | D2 (Serious) | ||
[21 Dec 2007 3:23]
Nicolae Namolovan
[21 Dec 2007 12:22]
Nicolae Namolovan
changed the a name a bit. And happy holidays ! I think you have some party these days :)
[21 Dec 2007 14:47]
Miguel Solorzano
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 7: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 7: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 11:16]
Sergei Golubchik
patch: http://lists.mysql.com/internals/35262
[28 Jan 16:01]
Mikael Ronstrom
Fixed according to proposed patch
[28 Jan 16:08]
Mikael Ronstrom
Patch
Attachment: bug33429.patch (text/x-patch), 2.65 KiB.
[2 Feb 0:47]
Mattias Jonsson
OK to push.
[7 Feb 11:37]
Mattias Jonsson
Patch approved (Checked with Mikael that it was sufficient with me as reviewer)
[20 Feb 23: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 12:18]
Bugs System
Pushed into 5.1.24-rc
[27 Mar 18:50]
Bugs System
Pushed into 6.0.5-alpha
[27 Mar 23: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 18:10]
Jon Stephens
Also noted bugfix in the 5.1.23-ndb-6.3.11 changelog.
