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:[EMAIL PROTECTED]
Sent: Friday, February 24, 2006 3:55 PM
To: mysql@lists.mysql.com
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"" <[EMAIL PROTECTED]>
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:[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]
--
David Logan
South Australia
when in trouble, or in doubt
run in circles, scream and shout
