Sunday, December 30, 2012

Don't forget to check your partitions!

As it's the end of the year it might be time to check your partition definitions.

If you forget to add a new partition in time partitions with no MAXVALUE might start to throw errors:
mysql> create table nye (`event_id` int not null auto_increment,`edate` year(4) not null, description varchar(200), 
    -> primary key(`event_id`,`edate`)) 
    -> partition by range( edate ) (
    -> partition p2010 VALUES LESS THAN (2011), 
    -> partition p2011 VALUES LESS THAN (2012), 
    -> partition p2012 VALUES LESS THAN (2013) );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2010','twenty ten'); 
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2011','twenty eleven');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2012','twenty twelve');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2013','twenty thirdteen');
ERROR 1526 (HY000): Table has no partition for value 2013

And partitions with a MAXVALUE might start to contain data of multiple years, and that might not be how it was intended.

So check your partitioning if you partition by year. It might also be a good moment to think about archiving old data.

You could use the common_schema.sql_range_partitions view to create the next partition.

mysql> select sql_add_next_partition from common_schema.sql_range_partitions where table_name='nye';
| sql_add_next_partition                                                              |
| alter table `test`.`nye` add partition (partition `p_2014` values less than (2014)) |
1 row in set, 22 warnings (0.08 sec)

mysql> call eval("select sql_add_next_partition from common_schema.sql_range_partitions where table_name='nye'");
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2013','twenty thirdteen');
Query OK, 1 row affected (0.00 sec)

O, and your sharding definitions might also contain a reference to a year.

And with the right partition definitions it's just:

mysql> SELECT TIMEDIFF(NOW(),'2013-01-01 00:00:00') AS 'Countdown';
| Countdown |
| -27:19:07 |
1 row in set (0.00 sec)

Sunday, December 9, 2012

A difficult XtraBackup restore

There was one MySQL server with a Adaptec Raid controller and 4 disks. One of the disks was having media errors and caused the whole SCSI bus to become unavailable.

This resulted in a corrupted InnoDB table.

Luckily we did have backups. A full backup and incrementals.

So to restore the backups I installed XtraBackup and MySQL 5.5 on another server.

Then the first step was to 'prepare' the backup. This worked okay for the full backup (redo only).

The second step to add the incremantals failed for the first incremental. This was easily resolved by specifying the full paths instead of relative paths.

Then the backup was fully prepared using the redo logs and undo logs.

As XtraBackup doesn't backup your my.cnf we copied the my.cnf from another server and adjusted it for this server. The my.cnf in your backup only contains everything needed for a restore, and some of those settings are Percona Server specific and will result in an error when used with MySQL.

So far everything went as expected.

Then we started MySQL with the newly restored backup by executing "service mysql start", this failed.

Then I tried to start it with a "mysqld_safe &", this worked. So I expected the init script to use wrong parameters. So I executed "bash -x /etc/init.d/mysql start" and expected the error to show, but MySQL started okay this time. Using "/etc/init.d/mysql stop" and then "/etc/init.d/mysql start" resulted in an error again.
Then I added some echo statements to the script to check which parameters it used for mysqld_safe. Then I tried to start mysqld_safe with the same parameters, this worked.

So mysqld_safe failed to start and didn't log anything to the error log when executed from the init script. It does work when called directly.

Then it hit me. The SELinux context for the MySQL files was wrong (ls -lZ).

This is something that must not be changed with chcon as that won't survive a relabel action. I changed the context for the datadir (not /var/lib/mysql in this case) and location for the socket and pid files with semanage. The I used restorecon to apply it to the files. And then MySQL started fine. This is explained in in detail in this blog post.

  • XtraBackup works great (and fast, disk I/O is quickly a bottleneck for restores)
  • Use full paths instead of relative paths for XtraBackup
  • Make sure to backup your my.cnf (or put in a CM tool like puppet)
  • If you use SELinux, don't forget to make sure the file context is correct after a restore.

Sunday, December 2, 2012