Bug #18566 Partitioning - Table definition changed, partitions not modified

• Submitted: 28 Mar 2006 5:01
• Modified: 26 May 2006 9:10
• Reporter: Jayant Kumar
• Status: No Feedback
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: mysql 5.1.7 beta
• OS: Linux (Linux)
• CPU Architecture: Any

[28 Mar 2006 5:01] Jayant Kumar

Description:
While altering the number of partitions (removing partitions), the table definition (no of partitions) was changed and the altering of partitions was in process. Killed my mysql server and restarted mysql. This changed the no of partitions in the table definition, but the partitions were not formed. 

Seems that the no of partitions and partition information is lost once mysql is killed or mysql crashes during any alter query execution. 

Exact commands and procedure are as below

How to repeat:
Create a table with 15 partitions : 
create table abc (id int ....... ) partition by HASH(id) partitions 15;

Alter the table to change no of partitions to 5 (remove 10 partitions) :
alter table abc COALESCE PARTITION 10;

kill the mysql process on linux command prompt and restart mysql : 
kill -9 <mysql_parent_pid>
./bin/mysqld_safe --user=mysql &

log into mysql:
./bin/mysql -u root -pxxxx

run show create table on the table:
show create table abc;

shows :
create table abc (id int ..... ) partition by HASH(id) PARTITIONS 5;

in the data directory following information is seen:
ls -lh ./data/test/abc*

-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p0.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p0.MYI
-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p0#TMP#.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p0#TMP#.MYI
-rw-rw----  1 mysql mysql 213M Mar 27 23:21 abc#P#p1.MYD
-rw-rw----  1 mysql mysql 173M Mar 27 23:21 abc#P#p1.MYI
-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p1#TMP#.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p1#TMP#.MYI
-rw-rw----  1 mysql mysql 224M Mar 27 23:21 abc#P#p2.MYD
-rw-rw----  1 mysql mysql 185M Mar 27 23:21 abc#P#p2.MYI
-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p2#TMP#.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p2#TMP#.MYI
-rw-rw----  1 mysql mysql 217M Mar 27 23:21 abc#P#p3.MYD
-rw-rw----  1 mysql mysql 176M Mar 27 23:21 abc#P#p3.MYI
-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p3#TMP#.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p3#TMP#.MYI
-rw-rw----  1 mysql mysql 205M Mar 27 23:21 abc#P#p4.MYD
-rw-rw----  1 mysql mysql 166M Mar 27 23:21 abc#P#p4.MYI
-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p4#TMP#.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p4#TMP#.MYI
-rw-rw----  1 mysql mysql 208M Mar 27 23:21 abc#P#p5.MYD
-rw-rw----  1 mysql mysql 168M Mar 27 23:21 abc#P#p5.MYI
-rw-rw----  1 mysql mysql 207M Mar 27 23:21 abc#P#p6.MYD
-rw-rw----  1 mysql mysql 167M Mar 27 23:21 abc#P#p6.MYI
-rw-rw----  1 mysql mysql 221M Mar 27 23:21 abc#P#p7.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p7.MYI
-rw-rw----  1 mysql mysql 185M Mar 27 23:21 abc#P#p8.MYD
-rw-rw----  1 mysql mysql 151M Mar 27 23:21 abc#P#p8.MYI
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p9.MYD
-rw-rw----  1 mysql mysql 150M Mar 27 23:21 abc#P#p9.MYI
-rw-rw----  1 mysql mysql 222M Mar 27 23:21 abc#P#p10.MYD
-rw-rw----  1 mysql mysql 183M Mar 27 23:21 abc#P#p10.MYI
-rw-rw----  1 mysql mysql 213M Mar 27 23:21 abc#P#p11.MYD
-rw-rw----  1 mysql mysql 173M Mar 27 23:21 abc#P#p11.MYI
-rw-rw----  1 mysql mysql 224M Mar 27 23:21 abc#P#p12.MYD
-rw-rw----  1 mysql mysql 185M Mar 27 23:21 abc#P#p12.MYI
-rw-rw----  1 mysql mysql 217M Mar 27 23:21 abc#P#p13.MYD
-rw-rw----  1 mysql mysql 176M Mar 27 23:21 abc#P#p13.MYI
-rw-rw----  1 mysql mysql 205M Mar 27 23:21 abc#P#p14.MYD
-rw-rw----  1 mysql mysql 166M Mar 27 23:21 abc#P#p14.MYI

So though the no of partitions has been changed in the table definition, the partitions have not been created.

Also pls check :

mysql> select count(*) from abc;
+----------+
| count(*) |
+----------+
| 86156321 |
+----------+

mysql> ALTER TABLE abc COALESCE PARTITION 5;
ERROR 1486 (HY000): Cannot remove all partitions, use DROP TABLE instead

mysql> alter table STATUS_PART1 check partition p8;
ERROR 1485 (HY000): Error in list of partitions to CHECK

Suggested fix:
Table definition and partition definition should be changed once the table has been altered properly.

Have had such crashes in past and there was no problem with MYISAM tables.  All i had to do was remove the temporary tables manually.

[28 Mar 2006 14:28] Valeriy Kravchuk

Thank you for a detailed problem report. So, you are killing MySQL server while it is processing

alter table abc COALESCE PARTITION 10;

on a reasonably large table, aren't you? Please, clarify.

[28 Mar 2006 15:01] Jayant Kumar

Thanks for the quick reply...

Yes, the table has a large no of records.
Any alter on the table takes some time, around 2-3 hours. So i prefered killing mysql server during the alter rather than waiting for the query to complete. Assuming that the original table would be maintained since i did not allow the alter to complete successfully.

Though this is a rare occurrance, data should not be lost in any case.

Thanks a ton for looking into this bug...

[28 Mar 2006 15:07] Valeriy Kravchuk

Thank you for a detailed problem report. So, you are killing MySQL server while it is processing

alter table abc COALESCE PARTITION 10;

on a reasonably large table, aren't you? Please, clarify.

[28 Mar 2006 15:20] Valeriy Kravchuk

Sorry, no need for feedback now.

[26 Apr 2006 9:10] Valeriy Kravchuk

Please, try to repeat with a newer version, 5.1.9 beta, and inform about the results.

[26 May 2006 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".