| Bug #33572 | Backup: no backup of merge tables | ||
|---|---|---|---|
| Submitted: | 29 Dec 2007 1:02 | Modified: | 28 Aug 2008 17:50 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
| Version: | 6.0.5-alpha-debug | OS: | Linux (SUSE 10 64-bit) |
| Assigned to: | Chuck Bell | CPU Architecture: | Any |
[29 Dec 2007 6:09]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[26 Jan 2008 2:34]
Chuck Bell
This bug has been tested against the work from Orlando (WL#3574, WL#4205). This bug has degraded performance when run with the new code. Instead of the restore succeeding, it fails when the data for the merge table is written. The default driver currently reads all of the rows on backup and therefore on restore attempts to insert the data into the merge table (which could succeed under the right circumstances). But in most cases it fails. The error stems from trying to insert data into the merge table when the merged tables have been locked. There are at least three solutions for this bug. 1) Create a backup driver for MYISAM_MERGE engine type and have the default driver reject merge tables. 2) Until a backup driver exists for the MYISAM_MERGE engine type, patch the default driver to reject merge tables, produce an error, and exit backup. 3) Until a backup driver exists for the MYISAM_MERGE engine type, patch the default driver to accept merge tables but do not process them (no data will be saved on backup or written on restore). The first solution is the correct solution, but the third solutions is a safe workaround. Unfortunately, the third solution would become incorrect when the merge table driver is added. The second solution is less desireable but can be managed easily and would not be rendered incorrect when the merge table driver is added. A patch for #2 is shown below. ===== kernel.cc 1.15 vs edited ===== --- 1.15/sql/backup/kernel.cc 2007-12-14 02:51:28 -05:00 +++ edited/kernel.cc 2008-01-25 17:31:51 -05:00 @@ -1021,6 +986,16 @@ int no= find_backup_engine(tl->table,t); // Note: reports errors + /* + If no engine found, this is an error because the + default engine cannot handle merge tables. + */ + if (no < 0) + { + ti= NULL; + goto end; + } + DBUG_PRINT("backup",(" table %s backed-up with %s engine", t.describe(buf), m_snap[no]->name())); ===== be_default.h 1.6 vs edited ===== --- 1.6/sql/backup/be_default.h 2007-12-03 15:28:13 -05:00 +++ edited/be_default.h 2008-01-25 17:27:47 -05:00 @@ -223,9 +223,12 @@ const char* name() const { return "Default"; } - bool accept(const Table_ref&, const ::handlerton*) - { return TRUE; }; // accept all tables - + bool accept(const Table_ref&, const ::handlerton* h) + { + // accept all tables except merge tables until merge driver is ready + return h->db_type != DB_TYPE_MRG_MYISAM ? TRUE : FALSE; + }; + result_t get_backup_driver(Backup_driver* &ptr) { return (ptr= new default_backup::Backup(m_tables,::current_thd, TL_READ_NO_INSERT)) ? OK : ERROR; }
[28 Feb 2008 17:51]
Chuck Bell
Patch committed that implements option #2. This will allow correct operation of backup with the limitation that merge tables are not supported until the no data driver is completed. See WL#3572. http://lists.mysql.com/commits/43164
[3 Mar 2008 10:50]
Rafal Somla
Changing to "in-review" as it was not yet accepted. I assume the patch was not pushed anywhere yet.
[25 Mar 2008 14:23]
Chuck Bell
Corrected state.
[14 May 2008 16:54]
Chuck Bell
This bug was fixed by the patch for WL#3572.
[23 May 2008 19:38]
Peter Gulutzan
Bug is still repeatable, using mysql-6.0-backup, pulled today.
[28 Aug 2008 17:50]
Chuck Bell
There is an error in the sample test. The merge table definition refers to tables that do not exist. Once you fix that, the test passes and this bug is not repeatable.
Definition should be:
CREATE TABLE d3.total (
a INT NOT NULL AUTO_INCREMENT,
message CHAR(20), INDEX(a))
ENGINE=MERGE UNION=(d1.t1,d2.t2) INSERT_METHOD=LAST;
^^^^^^^^^^^
Description: I create a MERGE table. I say BACKUP DATABASE. I say RESTORE. The table is not restored. I know there are some unsupported engines, but expected that this would work, since the underlying tables are built with supported engines. Built from mysql-6.0-backup source with BUILD/compile-pentium-debug-max. Last changeset = "ChangeSet@1.2753, 2007-12-20 15:32:22-05:00". How to repeat: use test drop database d1; drop database d2; drop database d3; create database d1; create database d2; create database d3; CREATE TABLE d1.t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM; CREATE TABLE d2.t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM; INSERT INTO d1.t1 (message) VALUES ('Testing'),('table'),('t1'); INSERT INTO d2.t2 (message) VALUES ('Testing'),('table'),('t2'); CREATE TABLE d3.total ( a INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; backup database d1,d2,d3 to '/usr/local/mysql/var/t66'; drop database d1; drop database d2; drop database d3; restore from '/usr/local/mysql/var/t66'; select * from d3.total;