innobackup fails with ERROR 1005 (errno: 121)

转自：

[4 Sep 2007 11:54] Elan Ruusamäe

Description:
innobackup.pl dies as the creation of mysql/ibbackup_binlog_marker.frm table fails. that
table doesn't exist before neither after creating snapshot.

i also tried to disable binlog creation of that table as tought it's related, but no
success.

# innobackup --version
InnoDB Backup Utility v1.3.0; Copyright 2003-2005 Innobase Oy
# ibbackup --version
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
# uname -m
x86_64

How to repeat:
# innobackup /etc/mysql/mysqld.conf --no-timestamp /var/lib/mysql/innobackup
...

innobackup: Error: mysql child process has died: ERROR 1005 (HY000) at line 7: Can't
create table './mysql/ibbackup_binlog_marker.frm' (errno: 121)
 while waiting for reply to MySQL request: 'CREATE TABLE ibbackup_binlog_marker(a INT)
TYPE=INNODB;' at /usr/bin/innobackup line 342.

[4 Sep 2007 11:56] Elan Ruusamäe

console log of process failure

Attachment:  (text/x-log), 3.13 KiB.

[4 Sep 2007 13:01] Sveta Smirnova

Thank you for the report.

InnoDB Backup Utility is product of Innobase Oy so better if you open this bug there.

But "ERROR 1005 (HY000) at line 7: Can't create table
'./mysql/ibbackup_binlog_marker.frm' (errno: 121)" can be MySQL error too. So, please,
indicate accurate version of MySQL server you use.

[4 Sep 2007 13:48] Elan Ruusamäe

mysql-5.0.45-1.amd64

[4 Sep 2007 17:28] Heikki Tuuri

Elan,

error 121 means that the table mysql.ibbackup_binlog_marker already exists inside
InnoDB's system tablespace though you do not have the .frm file 
ibbackup_binlog_marker.frm in the /mysql database directory.

Have you used an earlier backup taken with the innobackup Perl script, but forgotten to
put the .frm files to appropriate directories?

Regards,

Heikki

[4 Sep 2007 21:36] Elan Ruusamäe

Thanks. that seemed the cause of the problem. I think the problem appeared when was
creating snapshot to update crashed slave and in the process i restored mysql database
from slave, ie not using the mysql database from master due different GRANT options.
However i looked the script and it seems to drop the mentioned table, so probably the
innobackup run was interrupted or it failed by some other reason.

Here's how i solved the situation:

mysql> CREATE TABLE ibbackup_binlog_marker2(a INT) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine'
instead

sh# mv mysql/ibbackup_binlog_marker{2,}.frm
sh# mysqladmin flush-tables

mysql> SET SQL_LOG_BIN = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS ibbackup_binlog_marker;
Query OK, 0 rows affected (0.12 sec)

[5 Sep 2007 14:36] Heikki Tuuri

Elan, thank you. Restoring a backup is tricky because you have to remember all files, .frm
and others.

Regards,

Heikki

今天在用innobackup 测试时也遇到了这个问题，按着红色字体部分的方法，问题解决。

我用的innobackup 的版本是1.5.2 ，ibbackup 的版本号是3.0.0

解决方法我作了一点修改了一点：

mysql> use mysql;

mysql> CREATE TABLE ibbackup_binlog_marker2(a INT) ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

sh# mv mysql/ibbackup_binlog_marker{2,}.frm
sh# mysqladmin flush-tables -uroot -p123456

mysql> SET SQL_LOG_BIN = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS ibbackup_binlog_marker;
Query OK, 0 rows affected (0.12 sec)

问题原因是这个:

error 121 means that the table mysql.ibbackup_binlog_marker already exists inside
InnoDB's system tablespace though you do not have the .frm file 
ibbackup_binlog_marker.frm in the /mysql database directory.

Have you used an earlier backup taken with the innobackup Perl script, but forgotten to
put the .frm files to appropriate directories?

mysql.ibbackup_binlog_marker 表一直在InnoDB的系统表空间里面存在,但是在 mysql的数据库目录

/var/lib/mysql/mysql 却没有 ibbackup_binlog_marker.frm 文件.