Monday, December 20, 2010

SOLVED: Moving InnoDB tables between Percona MySQL servers

Update. Warning: The procedure is not working. The table can be copied, imported into another server and successfully read, but an attempt to write to the table causes a server crash. The problem is probably caused by remaining tablespace ids in the ibd file.

Update 2. Kudos to the Percona team, who pointed me to an error in my configuration file. To import tablespaces, one more option had to be set on the server, innodb_expand_import. Xtrabackup documentation has been updated.

When trying to move a table from one server to another, I found a problem. I followed the procedures outlined in the Percona Xtrabackup manual, chapter Exporting Tables.

On the last step, when doing IMPORT TABLE, I received an error message saying:

mysql> alter table `document_entity_bodies` import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine

There was some more information in the log:

101220 16:14:51  InnoDB: Error: tablespace id and flags in file './dbx_replica/document_entity_bodies.ibd'
 are 21 and 0, but in the InnoDB
InnoDB: data dictionary they are 181 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
101220 16:14:51  InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `dbx_replica`.`document_entity_bodies`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

The page mentioned in the log was not really helpful. What saved me was this article: Recovering an InnoDB table from only an .ibd file.

Following the instructions, I used a hex editor (shed) to change tablespace id in the ibd file from 0x15 to 0xB5 and then the import worked fine.

I wonder if there is a way to avoid these manipulations. Perhaps, one more operation should be added to xtrabackup to make the tablespace ids agree?

Oh, and it's Xtrabackup 1.4 working with Percona Server 5.1.51

Wednesday, December 15, 2010

Amarok without panels and trays

What's the use of “desktop” ornamentation like panels and trays? Time of day, CPU load, WiFi status — I can't think of anything I would like to see every moment I spend at the keyboard. If I want to know what's the time, I press 'M-z a'. So, panels and trays do not occupy a single pixel on my display.

Some programs, though, have a strange habit of closing their main window and leaving only a small icon in the tray. And there's no other way to control them besides grabbing the mouse and clicking, clicking, clicking... In some cases, though, you can leave your mouse in the dust. Amarok is one of such cases. I was perplexed when I saw Amarok in the list of processes, but there was no main window anywhere. Unfortunately, Amarok uses one of those recent abominations that appeared in Linux, DBus. To show the main window, send a signal using `qdbus':

$ qdbus org.kde.amarok /amarok/MainWindow org.kde.amarok.MainWindow.showHide