MySQL Bugs: #38751: Add partition on a archive table wipes the whole table

Bug #38751	Add partition on a archive table wipes the whole table
Submitted:	12 Aug 2008 15:29	Modified:	6 Oct 2008 21:18
Reporter:	Mattias Jonsson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.29,6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
When adding a partition to a partitioned archive table, it looses all records.

This is in 6.0 only, in 5.1 it works correctly, and it works on all other engines as well.

How to repeat:
CREATE TABLE t1 (a INT) ENGINE = 'Archive'
PARTITION BY HASH (a)
PARTITIONS 2;
INSERT INTO t1 VALUES (1), (2), (7), (8), (9), (10);
INSERT INTO t1 VALUES (3), (4), (5), (6), (11), (12);
SELECT * FROM t1;
a
2
8
10
4
6
12
1
7
9
3
5
11
ALTER TABLE t1 t1 ADD PARTITION (PARTITION added_partition_1);
SELECT * FROM t1;
a
DROP TABLE t1;

Suggested fix:
Seems like some problem in ha_partition::copy_partitions or ha_archive::rnd_init/ha_archive::rnd_next

Thank you for a problem report. What exact version, 6.0.x, you had used? In 6.0.5 I've got:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-community MySQL Community Server (GPL)

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

mysql> CREATE TABLE t1 (a INT) ENGINE = 'Archive'
    -> PARTITION BY HASH (a)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (7), (8), (9), (10);
Query OK, 6 rows affected (0.47 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 VALUES (3), (4), (5), (6), (11), (12);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+
| a    |
+------+
|    2 |
|    8 |
|   10 |
|    4 |
|    6 |
|   12 |
|    1 |
|    7 |
|    9 |
|    3 |
|    5 |
|   11 |
+------+
12 rows in set (0.14 sec)

mysql> ALTER TABLE t1 ADD PARTITION (PARTITION added_partition_1);
Query OK, 0 rows affected (1.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+
| a    |
+------+
|    6 |
|   12 |
|    9 |
|    3 |
|   10 |
|    4 |
|    1 |
|    7 |
|    2 |
|    8 |
|    5 |
|   11 |
+------+
12 rows in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (a) (PARTITION
p0 ENGINE = ARCHIVE, PARTITION p1 ENGINE = ARCHIVE, PARTITION added_partition_1
ENGINE = ARCHIVE) */
1 row in set (0.03 sec)

Latest mysql-6.0-bugteam (on mac osx) and some 6.0.6 marked version from an older mysql-6.0-bugteam on ubuntu

It is repeatable with a bzr source Windows server 1 week older:

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.7-alpha-nt-debug-log Source distribution

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

mysql 6.0 > use test
Database changed
mysql 6.0 > CREATE TABLE t1 (a INT) ENGINE = 'Archive'
    -> PARTITION BY HASH (a)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.11 sec)

mysql 6.0 > INSERT INTO t1 VALUES (1), (2), (7), (8), (9), (10);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 6.0 > INSERT INTO t1 VALUES (3), (4), (5), (6), (11), (12);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 6.0 > SELECT * FROM t1;
+------+
| a    |
+------+
|    2 |
|    8 |
|   10 |
|    4 |
|    6 |
|   12 |
|    1 |
|    7 |
|    9 |
|    3 |
|    5 |
|   11 |
+------+
12 rows in set (0.05 sec)

mysql 6.0 > ALTER TABLE t1 ADD PARTITION (PARTITION added_partition_1);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 6.0 > SELECT * FROM t1;
Empty set (0.02 sec)

mysql 6.0 >

Regression according Valeriy's test.

Seem to be in 5.1.29 too (mysql-5.1-5.1.29-rc) after my push for bug#38804 (backport of bug#33479). Investigating...

I'm reassigning this bug to myself, since I found the problem:

After the fix for bug#33479, the ::info call for HA_STATUS_AUTO in ha_partition was not redirected to the partitions (one kind of optimizition), resulting in archive did not flush its data to disk/updated its row-counter.

Will commit a fix soon, and will try to get it into 5.1.29.

one way to fix this is:
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc	2008-10-01 10:40:05 +0000
+++ sql/ha_partition.cc	2008-10-02 21:06:29 +0000
@@ -1717,6 +1717,11 @@
 
 void ha_partition::update_create_info(HA_CREATE_INFO *create_info)
 {
+  /*
+    Fix for bug#38751, some engines needs info-calls in ALTER.
+    Archive need this since it flushes in ::info
+  */
+  info(HA_STATUS_VARIABLE);
   info(HA_STATUS_AUTO);
 
   if (!(create_info->used_fields & HA_CREATE_USED_AUTO))

But the correct way would probably be to implement ha_archive::extra, and send a HA_EXTRA_FLUSH_CACHE in mysql_prepare_alter_table, instead of doing the flush through:
mysql_alter_table
  mysql_prepare_alter_table
    table->file->update_create_info
      info(HA_STATUS_AUTO)
        flush of written records...

Should probably be:
mysql_alter_table
  mysql_prepare_alter_table
    table->file->extra(HA_EXTRA_FLUSH_CACHE)
      flush of written records...

I will fix the problem as a 'post push fix' for bug#38804/bug#33479, but I would like a behavior change in mysql_prepare_alter_table and ha_archive.

So I'm assigning it back, and will include a couple of working patches (may not be 'production ready')

OK to lower the prio and remove the tag, since the main problem is fixed.

Three different patches, I will include the 'small' in 5.1.29, and propose that the archive-extra is applied instead.

Attachment: b38751.tar.gz (application/x-gzip, text), 2.66 KiB.

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

2686 Mattias Jonsson	2008-10-03
      post push fix for bug#38804 (back port of bug#33479)
      Removes the regression bug#38751.

Just for clearing the status of this bug:

The problem was fixed by a post push fix to bug#38804/bug#33479 and is no longer in the 5.1.29-rc trees (5.1 and 6.0).

But I did not close this bug report, since I would like to have a change in sql_table.cc and ha_archive.cc to address the strange behavior of using a ::info call to do flushing and rely on this for flushing before alter table. (I would rather see it use ::extra(HA_EXTRA_FLUSH_CACHE))

So in short: This is no longer a serious bug, only a request for code change.
I pushed the small fix into mysql-5.1-5.1.29-rc a couple of hour ago.

Closing this bug as a duplicate (was fixed where it was introduced, bug#38804/bug#33479)

The change request is now in bug#39892.

Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007082452-gk4l86zq8k53wwyo) (pib:4)

Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)

Pushed into 5.1.29-ndb-6.2.17  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)

Pushed into 5.1.29-ndb-6.3.19  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)

Pushed into 5.1.29-ndb-6.4.0  (revid:mattias.jonsson@sun.com-20081003093054-0sc082k21jboj166) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)