List:General Discussion« Previous MessageNext Message »
From:Cliff Date:March 4 2004 5:07pm
Subject:Re: Major problem converting MyISAM to InnoDB
View as plain text  
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" <Heikki.Tuuri@stripped>
To: <mysql@stripped>
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" <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
> >
>
>
> -- 
> 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