Checking and repairing partitions in MySQL 5.1

Talking with users at OSCON 2008, the biggest fear with new features was table corruption with partitioning. Until recently, this fear was substantiated by bug#20129, which reported inability of repairing corrupted partitions. The initial "fix" for this bug was to disable the offending commands.
After a while, especially when the Support department complained loudly, the bug was reopen and a proper fix implemented. Now you can maintain a partition using ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION.
I did some experiment, using MySQL sandbox and the employees test database, which supports partitioning.
You need to download the latest MySQL 5.1 release (5.1.28), where the patch is available.
To load the employees database with partitions, you edit employees_partitioned.sql to choose MyISAM as default engine and then run

$ $HOME/sandboxes/msb_5_1_28/use -t <>

Once we are satisfied that the data is safe, we can start messing with it. To simulate a corruption in MyISAM, the easiest way is to copy the index for a partition to the index of a different one.

$ cd $HOME/sandboxes/msb_5_1_28/
$ ./stop
$ cp data/employees/titles#P#p08.MYI data/employees/titles#P#p09.MYI
$ ./start

Now at least one partition is wrong.

ALTER TABLE titles CHECK PARTITION p09;
+------------------+-------+----------+-----------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                            |
+------------------+-------+----------+-----------------------------------------------------+
| employees.titles | check | warning  | Size of datafile is: 768416       Should be: 680072 |
| employees.titles | check | error    | Record-count is not ok; is 28286   Should be: 25274 |
| employees.titles | check | warning  | Found 28286 key parts. Should be: 25274             |
| employees.titles | check | error    | Partition p09 returned error                        |
| employees.titles | check | error    | Corrupt                                             |
+------------------+-------+----------+-----------------------------------------------------+

ALTER TABLE titles repair PARTITION p09;
+------------------+--------+----------+--------------------------------------------+
| Table            | Op     | Msg_type | Msg_text                                   |
+------------------+--------+----------+--------------------------------------------+
| employees.titles | repair | warning  | Number of rows changed from 25274 to 28286 |
| employees.titles | repair | status   | OK                                         |
+------------------+--------+----------+--------------------------------------------+

Run the test suite again, and you'll see that the feature works as advertised. However, what happens if we have more than one corrupted partition? Let's find out.

$ ./stop
$  for P in 02 05 10 11 16 ; \
 do /bin/cp data/employees/titles#P#p01.MYI \
    data/employees/titles#P#p$P.MYI; \
done
$ ./start

Now there are 5 corrupted partitions. Checking them one by one would be tedious. One side effect of fixing bug#20129 is that now you can use CHECK TABLE on partitioned tables (the manual states otherwise today (September 15, 2008) but it should be fixed soon).

check table titles;
+------------------+-------+----------+-------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                        |
+------------------+-------+----------+-------------------------------------------------+
| employees.titles | check | warning  | Size of datafile is: 478924       Should be: 0  |
| employees.titles | check | error    | Record-count is not ok; is 18293   Should be: 0 |
| employees.titles | check | warning  | Found 18293 key parts. Should be: 0             |
| employees.titles | check | error    | Partition p02 returned error                    |
| employees.titles | check | error    | Corrupt                                         |
+------------------+-------+----------+-------------------------------------------------+

Interestingly, CHECK TABLE reports only the first corruption. Let's fix it and try again.

alter table titles repair partition p02;
+------------------+--------+----------+----------------------------------------+
| Table            | Op     | Msg_type | Msg_text                               |
+------------------+--------+----------+----------------------------------------+
| employees.titles | repair | warning  | Number of rows changed from 0 to 18293 |
| employees.titles | repair | status   | OK                                     |
+------------------+--------+----------+----------------------------------------+
2 rows in set (0.07 sec)

mysql [localhost] {msandbox} (employees) > check table titles;
+------------------+-------+----------+-------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                        |
+------------------+-------+----------+-------------------------------------------------+
| employees.titles | check | warning  | Size of datafile is: 526268       Should be: 0  |
| employees.titles | check | error    | Record-count is not ok; is 20107   Should be: 0 |
| employees.titles | check | warning  | Found 20107 key parts. Should be: 0             |
| employees.titles | check | error    | Partition p05 returned error                    |
| employees.titles | check | error    | Corrupt                                         |
+------------------+-------+----------+-------------------------------------------------+

Hmm. This could be a painfully long process if I have an unknown number of corrupted partitions and many partitions to check. Fortunately, the same side effect that made possible CHECK TABLE is also allowing REPAIR TABLE. Let's do it.

repair table titles;
+------------------+--------+----------+----------------------------------------+
| Table            | Op     | Msg_type | Msg_text                               |
+------------------+--------+----------+----------------------------------------+
| employees.titles | repair | warning  | Number of rows changed from 0 to 20107 |
| employees.titles | repair | warning  | Number of rows changed from 0 to 30784 |
| employees.titles | repair | warning  | Number of rows changed from 0 to 33367 |
| employees.titles | repair | warning  | Number of rows changed from 0 to 33345 |
| employees.titles | repair | status   | OK                                     |
+------------------+--------+----------+----------------------------------------+

The four remaining corrupted partitions were fixed with one single command. Again, use the test suite to make sure that your data is OK.
Thanks, MySQL developers. Good job!

Introducing Maria, the new tough storage engine

Over the weekend, MySQL made available a new storage engine, codenamed Maria.
It is an improvement of MyISAM, the flagship engine, adding crash recovery to the already appreciated features of MyISAM. This release is a preview, based on MySQL 5.1 code base. The actual final implementation has not been announced yet, although it is safe to assume that it will happen in the 6.x series.
The source code is available as a bitkeeper tree.
There is some documentation, and more will follow.
What can we do with the new engine? The immediate answer is "use it as we use MyISAM, with crash recovery features".

Let's show an example, using MyISAM: (this demonstration was given live at the Linux Conference Australia during MySQL Mini-conf)

mysql [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (1.14 sec)

mysql [localhost] {msandbox} (test) > create  table t1 (id int, b longblob) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values (1, repeat('a',1000000));
Query OK, 1 row affected (0.02 sec)

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 8 rows affected (0.26 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 16 rows affected (1.17 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 32 rows affected (2.13 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 64 rows affected (4.77 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;

At this point, while the server is inserting data, from a different shell, we enter a vicious command:

$ kill -9 `pgrep mysqld`

which is as close as you can get to pulling your server plug. The result is disastrous.

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > exit
Bye

Let's restart the server and see what has happened.

[sandbox@dsl093-167-171 maria_5_1_23]$ ./start 
sandbox server started
[sandbox@dsl093-167-171 maria_5_1_23]$ ./use 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.23-maria-alpha Source distribution

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

mysql [localhost] {msandbox} (test) > check table t1;
+---------+-------+----------+-----------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                  |
+---------+-------+----------+-----------------------------------------------------------+
| test.t1 | check | warning  | 1 client is using or hasn't closed the table properly     | 
| test.t1 | check | warning  | Size of datafile is: 256004096       Should be: 128002048 | 
| test.t1 | check | error    | Record-count is not ok; is 256   Should be: 128           | 
| test.t1 | check | warning  | Found 256 parts                Should be: 128 parts       | 
| test.t1 | check | error    | Corrupt                                                   | 
+---------+-------+----------+-----------------------------------------------------------+
5 rows in set (0.62 sec)

As you can see, the table got corrupted, because we pulled the plug during an insertion.
Let's do the same operation with a Maria table.

mysql [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (0.15 sec)

mysql [localhost] {msandbox} (test) > create  table t1 (id int, b longblob) engine=maria;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values (1, repeat('a',1000000));
Query OK, 1 row affected (0.03 sec)

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 4 rows affected (0.29 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 8 rows affected (0.82 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 16 rows affected (1.61 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 32 rows affected (3.16 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 64 rows affected (6.54 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
#
# kill -9 from another terminal
#
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > exit
Bye

Here we pull the plug as in the MyISAM case, and the server dies.
On restart, the situation looks much different:

[sandbox@dsl093-167-171 maria_5_1_23]$ ./start   
sandbox server started
[sandbox@dsl093-167-171 maria_5_1_23]$ ./use 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.23-maria-alpha Source distribution

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

mysql [localhost] {msandbox} (test) > check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       | 
+---------+-------+----------+----------+
1 row in set (0.73 sec)

So, Maria survived the crash, while MyISAM was corrupted. There is still much to do to make this engine ready for production, but it looks like an excellent start.