Hi Rithish,
After reading Heikkis points, I am inclined to agree. Did your sysadmins
change a filesystem during the maintenance? Did they forget to restore a
directory if they changed disks? What was the maintenance that was
performed? Your InnoDB files disappeared at some point because the
server would not have recreated them otherwise. I am sure it was a
graceful shutdown, but something has changed. These things just don't
happen by themselves.
Regards
Rithish Saralaya wrote:
>The mysql server was shut down for maintenance. However it was a graceful
>shutdown and restart. That's all. No files were touched or removed. How
>could this have happened, I fail to see.
>
>Regards,
>Rithish.
>
>
>-----Original Message-----
>From: Heikki Tuuri [mailto:heikki.tuuri@stripped]
>Sent: Friday, February 24, 2006 3:55 PM
>To: mysql@stripped
>Subject: Re: error 1016 : cant open ibd file even though it exists
>
>
>Rithish,
>
>from the .err log we see that mysqld was shut down for 12 hours on Feb 19th.
>What did the sysadmins do during that time?
>
>There are two plausible explanations:
>
>1) they edited datadir in my.cnf to point to a different location (
>/var/lib/mysql),
>
>or
>
>2) they removed ibdata1 and ib_logfiles from the the datadir.
>
>That caused InnoDB to recreate these files.
>
>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
>
>----- Original Message -----
>From: ""Rithish Saralaya"" <rithish.saralaya@stripped>
>Newsgroups: mailing.database.myodbc
>Sent: Friday, February 24, 2006 6:55 AM
>Subject: RE: error 1016 : cant open ibd file even though it exists
>
>
>
>
>>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:heikki.tuuri@stripped]
>>Sent: Thursday, February 23, 2006 7:52 PM
>>To: mysql@stripped
>>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/mysql?unsub=1
>>
>>
>>
>
>
>
>
>
>
--
David Logan
South Australia
when in trouble, or in doubt
run in circles, scream and shout
