Hi Guys, We are currently using MySQL as the backend to the RT Request Tracker Ticketing system. The problem is that we are seeing total data loss from the InnoDB after a proper shutdown of the database using mysqladmin shutdown.
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 extract below from the .err log file: 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' my.cnf file 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 We are doing an sqldump every hour and before any shutdowns now, because we can't guarantee that the database will come back up. If anyone can spot the problem or suggest a course of action to debug the issue I'll be very happy. -- Rick Ellis <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
