MySQL Bugs: #64458: alter table statement corrupts innodb table with partitions

Bug #64458	alter table statement corrupts innodb table with partitions
Submitted:	26 Feb 2012 13:05	Modified:	6 Jun 2012 8:57
Reporter:	vishnu chanderraju (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S2 (Serious)
Version:	5.1.49-3-log, 5.1.61	OS:	Any (debian-linux-gnu, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	alter, corrupt, frm, innodb, partition

Description:
"alter table add primary key" on an existing partitioned innodb table corrupts the frm file. 

How to repeat:
we created a innodb table with partitions by key. 

mysql> CREATE TABLE IF NOT EXISTS testing3 (
    ->  ID VARCHAR(50) NOT NULL,
    -> TYPE VARCHAR(20) NOT NULL,
    ->  CREATED_ON TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
    -> ENGINE = InnoDB CHARACTER SET utf8
    -> PARTITION BY KEY (TYPE)
    -> PARTITIONS 10;
Query OK, 0 rows affected (0.02 sec)

------------------------------

then we decided to add a primary key

mysql> ALTER TABLE testing3 ADD PRIMARY KEY (ID);

we get the following error (which is valid):
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

--------------------------------

Now if we run 

mysql> desc testing3;

we get the following error:
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

---------------------------------
mysql> show warnings;

has the following 2 warnings / errors:

Error | 1503 | A PRIMARY KEY must include all columns in the table's partitioning function |

&&

Error | 1033 | Incorrect information in file: './testing/testing3.frm'

-----------------------------------------

The alter table add primary key seems to have to corrupted the frm file.

mysql error log also the following line after the we execute the 'alter' statement.
" [ERROR] /usr/sbin/mysqld: Incorrect information in file: './testing/testing3.frm'"

This is happening on a particular instance of mysql.
-----------------------------------------

other details:

mysql> show variables like '%version%';
+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| innodb_version          | 1.0.10           |
| protocol_version        | 10               |
| version                 | 5.1.49-3-log     |
| version_comment         | (Debian)         |
| version_compile_machine | x86_64           |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+
6 rows in set (0.00 sec)

screen shot of the entire scenario step by step

Attachment: bug.png (image/png, text), 110.87 KiB.

Please, check if the same problem ever happens with a recent version, 5.1.61. Look:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE IF NOT EXISTS testing3 (
    -> ID VARCHAR(50) NOT NULL,
    -> TYPE VARCHAR(20) NOT NULL,
    -> CREATED_ON TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
    -> ENGINE = InnoDB CHARACTER SET utf8
    -> PARTITION BY KEY (TYPE)
    -> PARTITIONS 10;
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER TABLE testing3 ADD PRIMARY KEY (ID);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> desc testing3;
+------------+-------------+------+-----+-------------------+-------+
| Field      | Type        | Null | Key | Default           | Extra |
+------------+-------------+------+-----+-------------------+-------+
| ID         | varchar(50) | NO   |     | NULL              |       |
| TYPE       | varchar(20) | NO   |     | NULL              |       |
| CREATED_ON | timestamp   | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

hi Valeriy,

i found out why the corruption was happening on that mysql instance(5.1.58). 

The my.cnf file had the following lines:
----------------------------------
default-storage-engine = InnoDB

ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
 
innodb_file_format = barracuda
innodb_file_format_check
----------------------------

When i removed these lines, the corruption went away and everything worked fine.
Operating system does not matter here. I reproduced this on my local mac book by adding the above lines

changing category to innodb plugin.

Verified just as described with 5.1.61 on Mac OS X using your settings:

macbook-pro:5.1 openxs$ bin/mysql -uroot testWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE IF NOT EXISTS testing3 (    -> ID VARCHAR(50) NOT NULL,    -> TYPE VARCHAR(20) NOT NULL,
    -> CREATED_ON TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
    -> ENGINE = InnoDB CHARACTER SET utf8
    -> PARTITION BY KEY (TYPE)
    -> PARTITIONS 10;
Query OK, 0 rows affected (0.26 sec)

mysql> ALTER TABLE testing3 ADD PRIMARY KEY (ID);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> desc testing3;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> show create table testing3\G
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Server was started with the following command:

bin/mysqld_safe --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.so --innodb-file-format=barracuda --innodb_file_per_table --innodb_file_format_check  &

I had tried to debug function mysql_alter_table() , and i was totally confused.

fix_partition_func() was called to check if ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF  happen. while return back to mysql_alter_table(), this error was ignored and table->file->add_index was called later to create a new index .

i think this is duplicate of Bug#57985

Closed as duplicate of bug#57985