MySQL Bugs: #21360: mysqldump error on federated tables

Bug #21360	mysqldump error on federated tables
Submitted:	31 Jul 2006 10:46	Modified:	15 Apr 2009 1:36
Reporter:	Gianluca Gimigliano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5/5.0BK/5.1BK	OS:	Any (Windows/Linux)
Assigned to:	Satya B	CPU Architecture:	Any
Tags:	Backup, federated, mysqldump, restore, tables

Description:
If i create e federated table and i execute a mysqldump;
when i try to restore the DB on the same machine i have this number:
"ERROR 1031 (HY000)".

How to repeat:
On a server_A execute:

create database 'prova';
use 'prova';
CREATE TABLE `remoto` (
  `Id` varchar(20) NOT NULL ,
  PRIMARY KEY  (`Id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 ;

On server_B execute:
create database 'prova';
use 'prova';
CREATE TABLE `remoto` (
  `Id` varchar(20) NOT NULL ,
  PRIMARY KEY  (`Id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@server_A:3306/prova/remoto';

Make a backup:
mysqldump -B prova -u root -h localhost > back.sql

Try to restore:
C:\mysql\bin>mysql  -uroot -hlocalhost < back.sql
ERROR 1031 (HY000) at line 41: Il gestore delle tabelle per 'remoto' non ha ques
ta opzione

I corrected the bug category

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot prova
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `remoto` (
    ->   `Id` varchar(20) NOT NULL ,
    ->   PRIMARY KEY  (`Id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=latin1
    -> CONNECTION='mysql://miguel@192.168.0.33:3306/prova/remoto';
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysqldump -B prova -uroot > back.sql
miguel@hegel:~/dbs/5.0> bin/mysql -uroot < back.sql 
ERROR 1031 (HY000) at line 41: Table storage engine for 'remoto' doesn't have this option
miguel@hegel:~/dbs/5.0> cat back.sql
-- MySQL dump 10.10
--
-- Host: localhost    Database: prova
-- ------------------------------------------------------
-- Server version       5.0.25-debug

This looks related to this bug - if it's not please tell me and I'll create a separate bug report:

mpopp@suse:~> mysql -u mpopp -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.24-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table federated_test (
    -> id int unsigned not null auto_increment primary key,
    -> val int unsigned not null) engine = federated
    -> connection = 'mysql://mpopp:xxxxxxx@10.0.0.100/test/test';
Query OK, 0 rows affected (4.51 sec)

mysql> select * from federated_test;
+----+------+
| id | val  |
+----+------+
|  1 | 3144 |
|  2 | 9596 |
|  3 | 8547 |
|  4 | 3949 |
|  5 | 4103 |
|  6 | 8670 |
|  7 | 1043 |
|  8 | 9202 |
|  9 | 2883 |
| 10 | 6810 |
| 11 | 5400 |
| 12 | 6569 |
| 13 | 6649 |
| 14 | 3536 |
| 15 | 7734 |
| 16 | 8064 |
| 17 | 7116 |
| 18 | 1391 |
| 19 | 5606 |
| 20 | 3857 |
| 21 | 2469 |
| 22 |  773 |
+----+------+
22 rows in set (4.55 sec)

mysql> exit
Bye
mpopp@suse:~> mpopp@suse:~> mysqldump -h 127.0.0.1 -u mpopp -p --databases test > test.sql
Enter password:
mpopp@suse:~> vi test.sql

Content of test.sql:

-- MySQL dump 10.10
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version	5.0.24-max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

--
-- Table structure for table `federated_test`
--

DROP TABLE IF EXISTS `federated_test`;
CREATE TABLE `federated_test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `val` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://mpopp:xxxxxxx@10.0.0.100/test/test';

--
-- Dumping data for table `federated_test`
--

/*!40000 ALTER TABLE `federated_test` DISABLE KEYS */;
LOCK TABLES `federated_test` WRITE;
INSERT INTO `federated_test` VALUES (1,3144),(2,9596),(3,8547),(4,3949),(5,4103),(6,8670),(7,1043),(8,9202),(9,2883),(10,6810),(11,5400),(12,6569),(13,6649),(14,3536),(15,7734),(16,8064),(17,7116),(18,1391),(19,5606),(20,3857),(21,2469),(22,773);
UNLOCK TABLES;
/*!40000 ALTER TABLE `federated_test` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

---

The point is here:

/*!40000 ALTER TABLE `federated_test` DISABLE KEYS */;
LOCK TABLES `federated_test` WRITE;
INSERT INTO `federated_test` VALUES (1,3144),(2,9596),(3,8547),(4,3949),(5,4103),(6,8670),(7,1043),(8,9202),(9,2883),(10,6810),(11,5400),(12,6569),(13,6649),(14,3536),(15,7734),(16,8064),(17,7116),(18,1391),(19,5606),(20,3857),(21,2469),(22,773);
UNLOCK TABLES;
/*!40000 ALTER TABLE `federated_test` ENABLE KEYS */;

mysqldump re-inserts the data into the federated table although it is already available through the remote server. In the case of a federated table it would IMHO be enough to only create the table definition, but not insert any data.

Was it fixed in any version??

Please add an option to mysqldump to either include INSERT statements for Federated tables or exclude them. Like the -h option for the UNIX tar statement.

Will ever this be fixed ?

The problem here is with the ALTER TABLE command that gets issued before the INSERT.  ALTER TABLE cannot be issued on a Federated table:

"The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE."

http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html

But since mysqldump inserts this command (default with --opt), you get the following error when trying to import this:

ERROR 1031 (HY000) at line 40: Table storage engine for 'federated_table' doesn't have this option

--

Work-around: If using --opt, then you can also use --skip-disable-keys so that it doesn't add this to the dump file in the first place.  This will allow the import to work fine.  But if you do not want/need --opt (which is included by default), then you coud use --skip-opt and it will also omit the ALTER TABLE command.

Further, you could manually remove the offending ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS if you have other tables in the dump file that you would like to keep this DISABLE/ENABLE KEYS for.

Markus has a great point above also, but that is different, and should be filed separately as a 'Feature Request'.

By the way, just for clarity, this is the initial offending statement:

/*!40000 ALTER TABLE `federated_test` DISABLE KEYS */;

But also don't forget the following (if removing these manually):

/*!40000 ALTER TABLE `federated_test` ENABLE KEYS */;

In my understanding handling FEDERATED tables in mysqldump should be similar to MERGE tables: no data go to backup because these engines have "VIEW" nature: dropping MERGE and FEDERATED tables will not result in data loss.

This patch for 5.1 tree, 5.0 will be slightly different.

bash-3.2$ diff -u client/mysqldump.c.old client/mysqldump.c
--- client/mysqldump.c.old      2008-12-29 16:30:02.000000000 +0100
+++ client/mysqldump.c  2008-12-29 16:38:48.000000000 +0100
@@ -4512,7 +4512,8 @@
   SYNOPSIS
 
   Check if we the table is one of the table types that should be ignored:
-  MRG_ISAM, MRG_MYISAM, if opt_delayed, if that table supports delayed inserts.
+  MRG_ISAM, MRG_MYISAM, FEDERATED, if opt_delayed, if that table supports 
+  delayed inserts.
   If the table should be altogether ignored, it returns a TRUE, FALSE if it
   should not be ignored. If the user has selected to use INSERT DELAYED, it
   sets the value of the bool pointer supports_delayed_inserts to 0 if not
@@ -4583,11 +4584,12 @@
     }
 
     /*
-      If these two types, we do want to skip dumping the table
+      If these types, we do want to skip dumping the table data
     */
     if (!opt_no_data &&
         (!my_strcasecmp(&my_charset_latin1, table_type, "MRG_MyISAM") ||
-         !strcmp(table_type,"MRG_ISAM")))
+         !my_strcasecmp(&my_charset_latin1, table_type, "MRG_ISAM") ||
+         !my_strcasecmp(&my_charset_latin1, table_type, "FEDERATED")))
       result= IGNORE_DATA;
   }
   mysql_free_result(res);

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

2828 Satya B	2009-02-27
      Fix for BUG#21360 - mysqldump error on federated tables
      
      When a mysqldump tool is used to dump federated tables,
      an error is thrown saying storage engine for the table
      doesn't have this option.
      
      mysqldump tries to re-insert the data into the federated
      table which causes the error. Since the data is already
      available on the remote server, mysqldump shouldn't try
      to dump the data again for FEDERATED tables.
      
      As stated it the bug page, it can be considered similar
      to the MERGE ENGINE with "view only" nature.
      
      Fixed by adding the "FEDERATED ENGINE" to the exception
      list to mysqldump tool
      modified:
        client/mysqldump.c
        mysql-test/suite/federated/federated.result
        mysql-test/suite/federated/federated.test

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

2775 Satya B	2009-03-17
      Fix for BUG#21360 - mysqldump error on federated tables
            
      When a mysqldump tool is used to dump federated tables,
      an error is thrown saying storage engine for the table
      doesn't have an option.
            
      mysqldump tries to re-insert the data into the federated
      table which causes the error. Since the data is already
      available on the remote server, mysqldump shouldn't try
      to dump the data again for FEDERATED tables.
            
      As stated it the bug page, it can be considered similar
      to the MERGE ENGINE with "view only" nature.
            
      Fixed by adding the "FEDERATED ENGINE" to the exception
      list to ignore the data.
      modified:
        client/mysqldump.c
        mysql-test/r/federated.result
        mysql-test/t/federated.test

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

2775 Satya B	2009-03-17
      Fix for BUG#21360 - mysqldump error on federated tables
                  
      When loading dump created by mysqldump tool an error is
      thrown saying storage engine for the table doesn't have 
      an option.
                  
      mysqldump tries to re-insert the data into the federated
      table which causes the error. Since the data is already
      available on the remote server, mysqldump shouldn't try
      to dump the data again for FEDERATED tables.
                  
      As stated it the bug page, it can be considered similar
      to the MERGE ENGINE with "view only" nature.
                  
      Fixed by adding the "FEDERATED ENGINE" to the exception
      list to ignore the data.
      modified:
        client/mysqldump.c
        mysql-test/r/federated.result
        mysql-test/t/federated.test

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

2775 Satya B	2009-03-18
      Fix for BUG#21360 - mysqldump error on federated tables
                        
      When loading dump created by mysqldump tool an error is
      thrown saying storage engine for the table doesn't have 
      an option.
                        
      mysqldump tries to re-insert the data into the federated
      table which causes the error. Since the data is already
      available on the remote server, mysqldump shouldn't try
      to dump the data again for FEDERATED tables.
                        
      As stated it the bug page, it can be considered similar
      to the MERGE ENGINE with "view only" nature.
                        
      Fixed by adding the "FEDERATED ENGINE" to the exception
      list to ignore the data.
      modified:
        client/mysqldump.c
        mysql-test/r/federated.result
        mysql-test/t/federated.test

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

2775 Satya B	2009-03-19
      Fix for BUG#21360 - mysqldump error on federated tables
                              
      When loading dump created by mysqldump tool an error is
      thrown saying storage engine for the table doesn't have 
      an option.
                              
      mysqldump tries to re-insert the data into the federated
      table which causes the error. Since the data is already
      available on the remote server, mysqldump shouldn't try
      to dump the data again for FEDERATED tables.
                              
      As stated in the bug page, it can be considered similar
      to the MERGE ENGINE with "view only" nature.
                              
      Fixed by adding the "FEDERATED ENGINE" to the exception
      list to ignore the data.
      modified:
        client/mysqldump.c
        mysql-test/r/federated.result
        mysql-test/t/federated.test

Pushed into 5.0.80 (revid:joro@sun.com-20090327142516-55gumdxj39z6eijj) (version source revid:leonard@mysql.com-20090324072904-7w2lxdxzw8hx1rnm) (merge vers: 5.0.80) (pib:6)

Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:satya.bn@sun.com-20090319084951-a80qstoos37i3k64) (merge vers: 5.1.34) (pib:6)

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:satya.bn@sun.com-20090319090710-fwl594sq9fzpq9mh) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.0.80, 5.1.34, 6.0.11 changelogs.

Restoring a mysqldump dump file containing FEDERATED tables because
the file contained the data for the table. Now only the table 
definition is dumped (because the data is located elsewhere).

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)