SEO

#1 (**permalink**) 02-28-2008, 05:21 AM

I want to move 3 100GB .ibd files into a new DB.

I followed the instructions here:

http://dev.mysql.com/doc/refman/5.0/...blespaces.html

But it doesn't work:

mysql> alter table reports discard tablespace;
Query OK, 0 rows affected (0.04 sec)

mysql> alter table reports import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine

mysqld.err says:

061206 14:57:35 InnoDB: Error: tablespace id in file
'./money/reports.ibd' is 88, but in the InnoDB
InnoDB: data dictionary it is 34.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB:
http://dev.mysql.com/doc/mysql/en/In..._datadict.html
InnoDB: for how to resolve the issue.
061206 14:57:35 InnoDB: cannot find or open in the database directory
the .ibd file of
InnoDB: table `money/reports`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

How to fix this?

ds

#2 (**permalink**) 02-28-2008, 05:22 AM

David Sparks wrote:
> I want to move 3 100GB .ibd files into a new DB.
>
> I followed the instructions here:
>
> http://dev.mysql.com/doc/refman/5.0/...blespaces.html
>
> But it doesn't work:
>
> mysql> alter table reports discard tablespace;
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> alter table reports import tablespace;
> ERROR 1030 (HY000): Got error -1 from storage engine

Oh no!

http://bugs.mysql.com/bug.php?id=5904

[5 Oct 2004 20:04] John David Duncan

Description:
Allow ALTER TABLE t IMPORT TABLESPACE to import a tablespace
created on some other MySQL server. This requires changing the
tablespace number (unless it is available...) and resetting the
transaction identifiers in the imported .ibd file.
--->%---

Argh! Note that "some other MySQL server" above applies to the same
hardware and just a different mysql instance. ie If you're trying to
restore from backup. Import tablespace has little use without being
able to move the files between server instances.

ds

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

alter table ... import tablespace NFG?