Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards.
Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. ================================== 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack): 1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) ===================================== So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php ............. List: mysql Subject: error 1016 : cant open ibd file even though it exists From: "Rithish Saralaya" <rithish.saralaya () tallysolutions ! com> Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY 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. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]