List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:March 4 2004 8:36am
Subject:Re: Major problem converting MyISAM to InnoDB
View as plain text  
Cliff,

please run

CHECK TABLE ...

on your table and look if mysqld prints anything to the .err log. Send the
WHOLE .err log to me.

What is your operating system version?

> > amount of changes since today. I am trying all I can to recover the data
> > from this table because I know it is in there. I deleted the frm file
and
> > recreated it because mysql was complaining about the file being corrupt.
I
> > used the same table definition as before. I pointed the datafile for
innodb

What does this mean: "mysql was complaining about the file being corrupt"?
Did it say that the .frm file was corrupt? What did it print exactly?

> > used the same table definition as before. I pointed the datafile for
innodb
> > to the correct file that is believe to be corrupt ...

What does the above mean?

Are you sure that you recreated the .frm file with the right CREATE TABLE?

You can use the innodb_table_monitor to print the internal data dictionary
of InnoDB:
http://www.innodb.com/ibman.php#InnoDB.Monitor

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables

Register now for the 2004 MySQL Users Conference!
http://www.mysql.com/events/uc2004/index.html

----- Original Message ----- 
From: "Sasha Pachev" <sasha@stripped>
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 04, 2004 7:18 AM
Subject: Re: Major problem converting MyISAM to InnoDB


> Cliff wrote:
> > Recently I tried to convert our largest table from MyISAM to InnoDB.
During
> > the process I believe there was a problem where something was corrupt
along
> > the way. It was stupid, but I did not verify that our backup system was
> > working correctly, since I assumed it had been running as usual. It was
not
> > however. Our latest backup was from November of 2003, which is a large
> > amount of changes since today. I am trying all I can to recover the data
> > from this table because I know it is in there. I deleted the frm file
and
> > recreated it because mysql was complaining about the file being corrupt.
I
> > used the same table definition as before. I pointed the datafile for
innodb
> > to the correct file that is believe to be corrupt and this is what
happens.
> > I can login to mysql and execute queries like usual, however some
queries
> > are crashing mysqld and restarting it. For instance, a record with id of
> > 10027 was one that was edited just before the table altering. If I
select *
> > where id=10027, the server restarts. I have tried using set-variable =
> > innodb_force_recovery = 4 in my.cnf with no luck either. I know the data
is
> > in the file because I can page through it and see portions of each IDs
> > information. Here is the error I get when the server restarts from
> > server.err:
> >
> > 040303 17:49:08  mysqld ended
> >
> >
> > 040303 17:49:09  mysqld started
> > 040303 17:49:10  InnoDB: Started
> > /mnt/disk2/mysql/bin/mysqld: ready for connections.
> > Version: '4.0.18-standard-log'  socket: '/tmp/mysql.sock'  port: 3306
> > mysqld got signal 11;
> > This could be because you hit a bug. It is also possible that this
binary
> > or one of the libraries it was linked against is corrupt, improperly
built,
> > or misconfigured. This error can also be caused by malfunctioning
hardware.
> > We will try our best to scrape up some info that will hopefully help
> > diagnose
> > the problem, but since we have already crashed, something is definitely
> > wrong
> > and this may fail.
> >
> > key_buffer_size=8388600
> > read_buffer_size=131072
> > max_used_connections=0
> > max_connections=100
> > threads_connected=1
> > It is possible that mysqld could use up to
> > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
=
> > 225791 K
> > bytes of memory
> > Hope that's ok; if not, decrease some variables in the equation.
> >
> > Number of processes running now: 0
> > 040303 17:49:29  mysqld restarted
> > 040303 17:49:29  InnoDB: Started
> > /mnt/disk2/mysql/bin/mysqld: ready for connections.
> > Version: '4.0.18-standard-log'  socket: '/tmp/mysql.sock'  port: 3306
> >
> >
> >
> >
> >
> >
> > As you can see I am running mysql 4.0.18. Here is the my.cnf file with
> > innodb settings:
> >
> > basedir=/mysql
> > long_query_time=3
> > log-slow-queries=/tmp/slowmysql.log
> > innodb_data_home_dir =
> > innodb_data_file_path = /mysql/data/ibdata1:10M:autoextend
> > #innodb_data_file_path = /mysql/innodb/ib_datafile:2000M:autoextend
> > set-variable = innodb_buffer_pool_size=300M
> > set-variable = innodb_additional_mem_pool_size=20M
> > set-variable = innodb_log_file_size=150M
> > set-variable = innodb_log_buffer_size=8M
> > innodb_flush_log_at_trx_commit=0
> > #skip-innodb
> > #innodb_force_recovery=1
> >
> >
> >
> > This information is extremely important and any help would be greatly
> > appreciated. If there is any tools I could possibly use to recover this
is
> > mysql is not an option that would be great. I also have tried mysqldump
and
> > it only restarts the server just like a query does. Thanks in advance.
>
> I would recommend that you purchase MySQL support, and get Heikki Tuuri
( the
> author of InnoDB) to work on your case. He should be able to fix it.
>
> -- 
> Sasha Pachev
> Create online surveys at http://www.surveyz.com/
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
>

Thread
Major problem converting MyISAM to InnoDBCliff4 Mar
  • Re: Major problem converting MyISAM to InnoDBSasha Pachev4 Mar
Re: Major problem converting MyISAM to InnoDBHeikki Tuuri4 Mar
Re: Major problem converting MyISAM to InnoDBCliff4 Mar
Re: Major problem converting MyISAM to InnoDBMartijn Tonies4 Mar
Re: Major problem converting MyISAM to InnoDBHeikki Tuuri4 Mar
Re: Major problem converting MyISAM to InnoDBCliff4 Mar
Re: Major problem converting MyISAM to InnoDBCliff5 Mar
  • Re: Major problem converting MyISAM to InnoDBSasha Pachev5 Mar
Re: Major problem converting MyISAM to InnoDBHeikki Tuuri5 Mar
Re: Major problem converting MyISAM to InnoDBCliff7 Mar