List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:March 4 2004 5:18am
Subject:Re: Major problem converting MyISAM to InnoDB
View as plain text  
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/
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