MySQL Bugs: #71449: Cannot import partitioned table.

Bug #71449	Cannot import partitioned table.
Submitted:	21 Jan 2014 17:13	Modified:	4 Feb 2014 11:13
Reporter:	DB Support	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	MySQL Enterprise version 5.6.10	OS:	Linux (RHEL 6.3)
Assigned to:		CPU Architecture:	Any
Tags:	dump, import, partition, problem, table

Description:
We are trying to develop a script to be able to backup (using mysqlbackup and mysqldump depending on the type of table) the whole database and then, be able to restore it.

The problem comes when we perform a mysqldump of some partitionated tables, when we try to restore them, MySQL says:

ERROR 1813 (HY000) at line 53 in file: 'file_to_import.sql': Tablespace for table '`database1`.`table1` /* Partition `p_20131223` */' exists. Please DISCARD the tablespace before IMPORT.

How to repeat:

Just dumping a partitioned table and then restore them.

Thank you for the report.

Please provide broken dump.

Thank you for the feedback.

You mix mysqlbackup and mysqldump in wrong way. This is not a bug in MySQL. And this database is for bug in MySQL code, not for helping to solve usage issues.

However support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

If you already have MySQL support contract simply open new service request and our engineers will help you.

Thank you for your interest in MySQL.

Hi Sveta.

We take a partial backup of database using mysqlbackup tool with "include" option set, where we specified each table using "database.table", following MOS note (How to Create Partial Backups Using MySQL Enterprise Backup (Doc ID 1446355.1)) and a instructions you gave us few months ago.

Another Thirty tables, including some partitioned tables that we don´t want to save the data, are backed up with mysqldump tool.

When we try to restore the backup, we restored all tables (backep up  with mysqlbackup and mysqldump) except partitioned tables. Only with these, we get the error:
 "[ERROR] InnoDB: The file './auxomega/RESUMEN_AGENTES_HIST#P#p_20131223.ibd' already exists though the corresponding table did not exist ..."

Although, we didn´t include these tables on mysqlbackup part of the script, all partitions of the all partitioned tables are backep up and restored, but not .frm and .par files.

Maybe I´m wrong and maybe this isn´t a specific mysqlbackup tool error, but this looks like a bug.

Best Regards

Thank you for the feedback.

I think would be safer if you create a SR for this case and we discuss it there. Just add hidden comment with SR #. If we confirm this is a bug I'll return to this report and change status.

For now scenario you use looks for me this way:

1. You make partial backup with MEB
2. You backup some of other tables with mysqldump
3. You create new clean instance of MySQL
4. You put files, created by MEB to empty datadir
5. You start the instance
6. You restore mysqldump there.

If I am correct regarding step 3 this is not a bug: partial backups, taken by MEB, contain full shared InnoDB tablespace. Therefore they contain data, related to tables which were not backed up. You should not restore them on clean instance. Exception is backups, taken with option --use-tts, but in this case restore should be more complicated.

You cannot open SR at this site. You need to login at https://support.oracle.com