Re: mysqldump --opt crashes server on InnoDB tables

[Thomas Plümpe]

> >   mysql> CREATE DATABASE myinnotest;
> >   mysql> CREATE TABLE myinnotest.t (id INT) TYPE=InnoDB;
> >   $ mysqldump -uroot --opt myinnotest > myinnotest.sql
> >   mysqldump: Can't get CREATE TABLE for table `t` (Lost connection to
> >   MySQL server during query)
> 
> 
> you usually get this error when you have the .frm file for the table in
> question, but there's nothing recorded in the InnoDB tablespace corresponding
> to it.
Thanks for the explanation, but are you sure? I had an "assertion error"
in the server logs. I mean, clearly this may be an assertion checking
whether the .frm file has corresponding records in the tablespace, but I
would have thought there'd be a more telling error message in such a
case.

> ie, if you do a filecopy on the database directories under your data-dir, but
> not with InnoDB Hot Backup or similiar to the InnoDB tablespace.
I hadn't done anything like that. I only unsinstalled the previous MySQL
version (4.0.15-mdk without InnoDB), installed the newer one from
mysql.com, enabled InnoDB tables and started importing stuff with
Type=InnoDB.

Anyway, I had tried to reinstall MySQL and didn't get it to work at all:
With Mandrake there seems to be an issue with TMP or TMP_DIR being set
to /root/tmp which isn't writable for the mysql user and the fix which
worked for me the first time around -- setting TMP and/or TMP_DIR to
empty values or to /tmp at the beginning of the /etc/init.d/mysql script
-- didn't work the second time (I have no idea why).

So I eventually installed a MySQL-Max rpm for Mandrake and now I have
InnoDB tables, the export works and for now I am happy with it.

Thomas