Rick, ----- Original Message ----- From: "Rick Ellis" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, April 01, 2004 11:27 AM Subject: InnoDB problems with 4.0.18-max
> 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. are you changing the InnoDB parameters in my.cnf when mysqld is running? The above could be explained if InnoDB on restart uses old ibdata files or old ib_logfiles in some other directory. Otherwise, this is probably some serious configuration problem in the OS or hardware. It is losing writes to files. I have not seen this problem before. > -- > Rick Ellis <[EMAIL PROTECTED]> Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
