[rt-users] Re: InnoDB questions

[Rick Ellis]

We have observed this once on a Sparc Enterprise 420R with 4 CPU's and 4
gigs of RAM, using the my-large.cnf as a template and Solaris 9, but we
see this most often on mysql 4.0-18-max running on a Enterprise 220R
with 2 CPU's and 2 gigs of ram, based on my-small.cnf and Solaris 8.

Both setups are using perl 5.8.3 and the latest Apache 3 release.

On restarting the Mysql server, (which serves a number of other
application, not using InnoDB perfectly) we cannot log in. Going into
Mysql itself, and use rt3; it complains that most of the tables are
blank. See Below:


040310  7:03:37  /usr/local/mysql/bin/mysqld: Normal shutdown

040310  7:03:38  InnoDB: Starting shutdown...
040310  7:03:42  InnoDB: Shutdown completed
040310  7:03:42  /usr/local/mysql/bin/mysqld: Shutdown Complete

040310 07:04:06  mysqld started
040310  7:04:07  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 2488475
InnoDB: Doing recovery: scanned up to log sequence number 0 2488475
040310  7:04:07  InnoDB: Flushing modified pages from the buffer pool...
040310  7:04:07  InnoDB: Started
/export/mysql4/bin/mysqld: ready for connections.
Version: '4.0.18-max'  socket: '/tmp/mysql.sock'  port: 3306
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
040310  7:05:41  InnoDB error:
Cannot find table rt3/Users from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.

Settings from the worst affected system are:

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = innodb_buffer_pool_size=32M
set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#set-variable = innodb_force_recovery=4

If anyone can spot the problem I'll be very happy.

Rik



On Wed, 2004-03-31 at 17:13, Michael S. Liebman wrote:
> On Wed, Mar 31, 2004 at 08:56:09AM +0100, Rick Ellis wrote:
> > RT is using Innodb tables. We have observed on several occasions now
> > that even shutting down mysql cleanly, using mysqladmin shutdown, the
> > Innodb files sometimes get corrupted, resulting in us losing all data.
> 
> That's not right at all. A clean shutdown should flush the InnoDB logs
> and finish writing the data file. Even on a dirty stop of MySQL,
> InnoDB should reconstruct the last few transactions from the log
> files. I haven't had problems like this on either Win32 or
> Redhat/Fedora with InnoDB tables. What version of MySQL are you
> running?
> 
> > We are using the settings from the .cnf files, customised to the two
> > machines RT is running on.
> 
> Which sample file? How does the sample file compare relative to the
> memory in the machine (i.e., did you choose the my-huge.cnf and you
> have 2 GB of RAM)?
> 
> Michael
-- 
Rick Ellis <Richard.Ellis at Sun.COM>