MySQL Bugs: #34205: Backup: restore fails if Falcon and no autocommit

Bug #34205	Backup: restore fails if Falcon and no autocommit
Submitted:	31 Jan 2008 20:16	Modified:	31 Oct 2008 16:18
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any

Description:
I set @@autocommit=0;
I create a Falcon table.
I insert + backup + restore. Success.
I insert + backup + restore. Failure.

How to repeat:
mysql> use test
Database changed
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (s1 int) engine=falcon;
Query OK, 0 rows affected (0.68 sec)

mysql> insert into t values (0);
Query OK, 1 row affected (0.00 sec)

mysql> backup database test to '40';
+-----------+
| backup_id |
+-----------+
| 1         |
+-----------+
1 row in set (0.17 sec)

mysql> restore from '40';
+-----------+
| backup_id |
+-----------+
| 2         |
+-----------+
1 row in set (0.12 sec)

mysql> insert into t values (0);
Query OK, 1 row affected (0.01 sec)

mysql> backup database test to '41';
+-----------+
| backup_id |
+-----------+
| 3         |
+-----------+
1 row in set (0.15 sec)

mysql> restore from '41';
ERROR 1007 (HY000): Can't create database 'test'; database exists

Thank you for the report.

Verified as described.

A test case with which we could repeat the problem:

use test;
set @@autocommit=0;
create table t (s1 int) engine=falcon;
insert into t values (0);
backup database test to '40';
restore from '40';
backup database test to '41';
restore from '41';

2nd restore fails as reported above.

The problems shows up only if:

1. engine=falcon,
2. autocommit is off (0),
3. we do restore for the second time,
4. there is some data in the table,
5. we run it on 64bit platform.

Adding COMMIT after the INSERT does not help.

Analyzing the problem we found out that the DROP DATABASE statement executed inside second RESTORE command fails. This is at this moment during execution:

#0  mysql_rm_table_part2 (thd=0x1167318, tables=0x11d2360, if_exists=true,
    drop_temporary=false, drop_view=true, dont_log_query=true)
    at sql_table.cc:1699
#1  0x000000000073cdb8 in mysql_rm_known_files (thd=0x1167318, dirp=0x11c84b8,
    db=0x11d2358 "test", org_path=0x46e89640 "./test/", level=0,
    dropped_tables=0x46e89548) at sql_db.cc:1142
#2  0x000000000073c357 in mysql_rm_db (thd=0x1167318, db=0x11d2358 "test",
    if_exists=true, silent=false) at sql_db.cc:919

Inside mysql_rm_table_part2() function ha_delete_table() is called which returns arror 177.

MySQL error code 177: Lock or active transaction

Another test which fails, possibly for the same reasons.

use test;
set @@autocommit=0;
create table t (s1 int) engine=falcon;
insert into t values (0);
commit;
backup database test to 'test.bak';
restore from 'test.bak';
show tables;
select * from t;
drop table t;

DROP TABLE fails with error 'Unknown tbale `t`'. However, SHOW TABLES shows this table, and one can select data from it.

Tried moving set @@autocommit=0; to right before restore, and it still fails.

If backup is not done in the same session, dropping the table after restore works.  Test script:

use test;
set @@autocommit=0;
create table t (s1 int) engine=falcon;
insert into t values (0);
--exec cp $MYSQLTEST_VARDIR/../40.bak $MYSQLTEST_VARDIR/master-data/40
restore from '40';
drop table t;

BUG#33258 "Misleading Error when DROP TABLE operation while a transaction is open" is probably related to this issue.

I am not able to reproduce this now on my Linux laptop.
I will try to see if I can reproduce it on a 64-bit system.
Anyway, I guess a test case for this scenarios should be made.

I am not able to reproduce this.
I will check whether this use case is already covered by a test.
If not, I will add a test case.

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

2708 Oystein Grovlen	2008-10-17
      BUG#34205 Backup: restore fails if Falcon and no autocommit
      
      Add test case to cover this bug report.
      (It is not possible to repeat bug anymore)

Good to push.

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

2714 Oystein Grovlen	2008-10-23
      BUG#34205 Backup: restore fails if Falcon and no autocommit
            
      Add test case to cover this bug report.
      (It is not possible to repeat bug anymore)

Patch pushed into mysql-6.0-backup (revision 2714).

Pushed into 6.0.8-alpha  (revid:oystein.grovlen@sun.com-20081023081354-fx15zhno3pkzw1ba) (version source revid:oystein.grovlen@sun.com-20081023081354-fx15zhno3pkzw1ba) (pib:5)

This orginal issue was that if you had done a backup and restore in same session without autocommit, later drop table or restore in same session would fail.  This issue seems to been fixed by other bug fixes, so I just added a test case for the given scenario.

Noted in 6.0.8 changelog.

If BACKUP DATABASE and RESTORE were done in a session with autocommit
disabled, a later DROP TABLE or RESTORE in the same session failed.