List:General Discussion« Previous MessageNext Message »
From:Cliff Date:March 4 2004 2:07am
Subject:Major problem converting MyISAM to InnoDB
View as plain text  
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.

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