Hi, I ran CHECK TABLE with the EXTENDED option on the table and it reports everything ok:
mysql> check table allusa extended; +-----------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+-------+----------+----------+ | bb.allusa | check | status | OK | +-----------+-------+----------+----------+ 1 row in set (4.74 sec) I am running FreeBSD 4.9-STABLE. The error log did not show anything while or after the query ran: 040304 09:09:32 mysqld started 040304 9:09:33 InnoDB: Started /mnt/disk2/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 If I do a select statement inside of mysql into an outfile, the server crashes. However if I do a select on the same data to stdout, it works fine most of the time. I am having my terminal program capture the screen output from mysql to a file for each field and editing the records to a format that is easy to insert back in. As you can imagine this is less than ideal, but it is the only solution I can come up with at this point. Thanks for your help, if you need anything else let me know. ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 04, 2004 12:36 AM Subject: Re: Major problem converting MyISAM to InnoDB > 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" <[EMAIL PROTECTED]> > 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/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
