MySQL ODBC Connector - error 1016 : cant open ibd file even though it exists

Author

error 1016 : cant open ibd file even though it exists

Rithish Saralaya

2006-02-25, 9:43 am

------ =_NextPart_000_0078_
01C63865.D888BC90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.

------ =_NextPart_000_0078_
01C63865.D888BC90--

Logan, David

2006-02-25, 9:43 am

Hi Rithish,

Please check your ownership/permissions

hambone ~ $ perror 1
OS error code 1: Not owner
hambone ~ $

Regards=20

---------------------------------------------------------------
********** _/ ********** David Logan=20
******* _/ ******* ITO Delivery Specialist - Database
***** _/ ***** Hewlett-Packard Australia Ltd
**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
**** _/ _/ _/ _/ **** Desk: +618 8408 4273
**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
***** _/ ****** =20
****** _/ ******** Postal: 148 Frome Street,
******** _/ ********** Adelaide SA 5001
Australia=20
i n v e n t =20
---------------------------------------------------------------

-----Original Message-----
From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]=20
Sent: Thursday, 23 February 2006 3:43 PM
To: MySQL general mailing list
Subject: error 1016 : cant open ibd file even though it exists

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...sie.nctu.edu.tw

Rithish Saralaya

2006-02-25, 9:43 am

Hello David. Thanks for the prompt response.

The permissions were the first thing that I checked when I got the error. In
fact, I even tried giving 777 permissions on the .ibd files. No results.

Regards,
Rithish.

-----Original Message-----
From: Logan, David (SST - Adelaide) [mailto:David.Logan@hp.com]
Sent: Thursday, February 23, 2006 10:42 AM
To: Rithish Saralaya; MySQL general mailing list
Subject: RE: error 1016 : cant open ibd file even though it exists

Hi Rithish,

Please check your ownership/permissions

hambone ~ $ perror 1
OS error code 1: Not owner
hambone ~ $

Regards

---------------------------------------------------------------
********** _/ ********** David Logan
******* _/ ******* ITO Delivery Specialist - Database
***** _/ ***** Hewlett-Packard Australia Ltd
**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
**** _/ _/ _/ _/ **** Desk: +618 8408 4273
**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
***** _/ ******
****** _/ ******** Postal: 148 Frome Street,
******** _/ ********** Adelaide SA 5001
Australia
i n v e n t
---------------------------------------------------------------

-----Original Message-----
From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]
Sent: Thursday, 23 February 2006 3:43 PM
To: MySQL general mailing list
Subject: error 1016 : cant open ibd file even though it exists

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...sie.nctu.edu.tw

Logan, David

2006-02-25, 9:43 am

Hi Rithish,

I'm all out of ideas with this one, sorry I can't be of more help.
Perhaps Mr Tuuri or others with more nouse than myself can help.

Regards=20

---------------------------------------------------------------
********** _/ ********** David Logan=20
******* _/ ******* ITO Delivery Specialist - Database
***** _/ ***** Hewlett-Packard Australia Ltd
**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
**** _/ _/ _/ _/ **** Desk: +618 8408 4273
**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
***** _/ ****** =20
****** _/ ******** Postal: 148 Frome Street,
******** _/ ********** Adelaide SA 5001
Australia=20
i n v e n t =20
---------------------------------------------------------------

-----Original Message-----
From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]=20
Sent: Thursday, 23 February 2006 5:02 PM
To: Logan, David (SST - Adelaide); MySQL general mailing list
Subject: RE: error 1016 : cant open ibd file even though it exists

Hello David. Thanks for the prompt response.

The permissions were the first thing that I checked when I got the
error. In
fact, I even tried giving 777 permissions on the .ibd files. No results.

Regards,
Rithish.

-----Original Message-----
From: Logan, David (SST - Adelaide) [mailto:David.Logan@hp.com]
Sent: Thursday, February 23, 2006 10:42 AM
To: Rithish Saralaya; MySQL general mailing list
Subject: RE: error 1016 : cant open ibd file even though it exists

Hi Rithish,

Please check your ownership/permissions

hambone ~ $ perror 1
OS error code 1: Not owner
hambone ~ $

Regards

---------------------------------------------------------------
********** _/ ********** David Logan
******* _/ ******* ITO Delivery Specialist - Database
***** _/ ***** Hewlett-Packard Australia Ltd
**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
**** _/ _/ _/ _/ **** Desk: +618 8408 4273
**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
***** _/ ******
****** _/ ******** Postal: 148 Frome Street,
******** _/ ********** Adelaide SA 5001
Australia
i n v e n t
---------------------------------------------------------------

-----Original Message-----
From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]
Sent: Thursday, 23 February 2006 3:43 PM
To: MySQL general mailing list
Subject: error 1016 : cant open ibd file even though it exists

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...sie.nctu.edu.tw

Ady Wicaksono

2006-02-25, 9:43 am

Try to help

Please give us the "ls -l" result of this file, also make sure that the
owner of mysql process could read this file
how to test?

Try to "su" to mysql user, and try to open the file, using "cat" or
other command
If you can't, maybe parent directory is not permitted this user to read
, then fix it

Please also give us "lsattr" result of this file, maybe file attribute
not permitt mysql user to read this file

Good luck

Logan, David (SST - Adelaide) wrote:

>Hi Rithish,
>
>I'm all out of ideas with this one, sorry I can't be of more help.
>Perhaps Mr Tuuri or others with more nouse than myself can help.
>
>Regards
>
>
>
>---------------------------------------------------------------
>********** _/ ********** David Logan
>******* _/ ******* ITO Delivery Specialist - Database
>***** _/ ***** Hewlett-Packard Australia Ltd
>**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
>**** _/ _/ _/ _/ **** Desk: +618 8408 4273
>**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
>***** _/ ******
>****** _/ ******** Postal: 148 Frome Street,
>******** _/ ********** Adelaide SA 5001
> Australia
>i n v e n t
>---------------------------------------------------------------
>
>-----Original Message-----
>From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]
>Sent: Thursday, 23 February 2006 5:02 PM
>To: Logan, David (SST - Adelaide); MySQL general mailing list
>Subject: RE: error 1016 : cant open ibd file even though it exists
>
>Hello David. Thanks for the prompt response.
>
>The permissions were the first thing that I checked when I got the
>error. In
>fact, I even tried giving 777 permissions on the .ibd files. No results.
>
>Regards,
>Rithish.
>
>
>-----Original Message-----
>From: Logan, David (SST - Adelaide) [mailto:David.Logan@hp.com]
>Sent: Thursday, February 23, 2006 10:42 AM
>To: Rithish Saralaya; MySQL general mailing list
>Subject: RE: error 1016 : cant open ibd file even though it exists
>
>
>Hi Rithish,
>
>Please check your ownership/permissions
>
>hambone ~ $ perror 1
>OS error code 1: Not owner
>hambone ~ $
>
>Regards
>
>
>
>---------------------------------------------------------------
>********** _/ ********** David Logan
>******* _/ ******* ITO Delivery Specialist - Database
>***** _/ ***** Hewlett-Packard Australia Ltd
>**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
>**** _/ _/ _/ _/ **** Desk: +618 8408 4273
>**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
>***** _/ ******
>****** _/ ******** Postal: 148 Frome Street,
>******** _/ ********** Adelaide SA 5001
> Australia
>i n v e n t
>---------------------------------------------------------------
>
>-----Original Message-----
>From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]
>Sent: Thursday, 23 February 2006 3:43 PM
>To: MySQL general mailing list
>Subject: error 1016 : cant open ibd file even though it exists
>
>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...sie.nctu.edu.tw

Rithish Saralaya

2006-02-25, 9:43 am

"ls -l" results for the file.
-rwxrwxrwx 1 mysql mysql 5863636992 Feb 19 05:20
TBL_FORUM_MSG_BODY.ibd

Tried su to mysql user. able to cat the above file.

"lsattr" results for the file.
------------- TBL_FORUM_MSG_BODY.ibd

Hope that helps.

Regards,
Rithish.

-----Original Message-----
From: Ady Wicaksono & #91;mailto:adywicaks
ono@regionalinvestme
nt.com]
Sent: Thursday, February 23, 2006 5:50 PM
To: Logan, David (SST - Adelaide)
Cc: Rithish Saralaya; MySQL general mailing list
Subject: Re: error 1016 : cant open ibd file even though it exists

Try to help

Please give us the "ls -l" result of this file, also make sure that the
owner of mysql process could read this file
how to test?

Try to "su" to mysql user, and try to open the file, using "cat" or
other command
If you can't, maybe parent directory is not permitted this user to read
, then fix it

Please also give us "lsattr" result of this file, maybe file attribute
not permitt mysql user to read this file

Good luck

Logan, David (SST - Adelaide) wrote:

>Hi Rithish,
>
>I'm all out of ideas with this one, sorry I can't be of more help.
>Perhaps Mr Tuuri or others with more nouse than myself can help.
>
>Regards
>
>
>
>---------------------------------------------------------------
>********** _/ ********** David Logan
>******* _/ ******* ITO Delivery Specialist - Database
>***** _/ ***** Hewlett-Packard Australia Ltd
>**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
>**** _/ _/ _/ _/ **** Desk: +618 8408 4273
>**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
>***** _/ ******
>****** _/ ******** Postal: 148 Frome Street,
>******** _/ ********** Adelaide SA 5001
> Australia
>i n v e n t
>---------------------------------------------------------------
>
>-----Original Message-----
>From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]
>Sent: Thursday, 23 February 2006 5:02 PM
>To: Logan, David (SST - Adelaide); MySQL general mailing list
>Subject: RE: error 1016 : cant open ibd file even though it exists
>
>Hello David. Thanks for the prompt response.
>
>The permissions were the first thing that I checked when I got the
>error. In
>fact, I even tried giving 777 permissions on the .ibd files. No results.
>
>Regards,
>Rithish.
>
>
>-----Original Message-----
>From: Logan, David (SST - Adelaide) [mailto:David.Logan@hp.com]
>Sent: Thursday, February 23, 2006 10:42 AM
>To: Rithish Saralaya; MySQL general mailing list
>Subject: RE: error 1016 : cant open ibd file even though it exists
>
>
>Hi Rithish,
>
>Please check your ownership/permissions
>
>hambone ~ $ perror 1
>OS error code 1: Not owner
>hambone ~ $
>
>Regards
>
>
>
>---------------------------------------------------------------
>********** _/ ********** David Logan
>******* _/ ******* ITO Delivery Specialist - Database
>***** _/ ***** Hewlett-Packard Australia Ltd
>**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
>**** _/ _/ _/ _/ **** Desk: +618 8408 4273
>**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
>***** _/ ******
>****** _/ ******** Postal: 148 Frome Street,
>******** _/ ********** Adelaide SA 5001
> Australia
>i n v e n t
>---------------------------------------------------------------
>
>-----Original Message-----
>From: Rithish Saralaya [mailto:rithish. saralaya@tallysoluti
ons.com]
>Sent: Thursday, 23 February 2006 3:43 PM
>To: MySQL general mailing list
>Subject: error 1016 : cant open ibd file even though it exists
>
>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...sie.nctu.edu.tw

Heikki Tuuri

2006-02-25, 9:43 am

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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw

sheeri kritzer

2006-02-25, 9:43 am

What are the permissions on the files? Which user runs mysql? If
you're not on Windows, is the case the same? When you type "mysqld
--print-defaults" (or whatever your mysql server binary is), what
directory shows up under "datadir"? Is it the same directory?

Sincerely,

Sheeri

On 2/23/06, Rithish Saralaya <rithish. saralaya@tallysoluti
ons.com> wrote:
> 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 databa=
se.
> 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 t=
he
> 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 dictiona=
ry
> 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 t=
he
> 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...sie.nctu.edu.tw

Rithish Saralaya

2006-02-25, 9:43 am

I have tried giving 777 permissions on the files. Nothing happens.

All mysql processes run as 'mysql' except mysqld-safe, runs as root.

I am not on Windows. All the table names are in upper case. I don't have the
lower case setting in my.cnf also.

The 'datadir' is /var/lib/mysql/ This is where the mysql is located. That's
proper.

-----Original Message-----
From: sheeri kritzer & #91;mailto:awfief@gm
ail.com]
Sent: Friday, February 24, 2006 3:22 AM
To: Rithish Saralaya
Cc: MySQL general mailing list
Subject: Re: error 1016 : cant open ibd file even though it exists

What are the permissions on the files? Which user runs mysql? If
you're not on Windows, is the case the same? When you type "mysqld
--print-defaults" (or whatever your mysql server binary is), what
directory shows up under "datadir"? Is it the same directory?

Sincerely,

Sheeri

On 2/23/06, Rithish Saralaya <rithish. saralaya@tallysoluti
ons.com> wrote:
> 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...sie.nctu.edu.tw

Rithish Saralaya

2006-02-25, 9:43 am

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@oracle.com]
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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw

mysql@karsites.net

2006-02-25, 9:43 am

Sounds like you have any empty database, from the messages
below.

Try adding a new dummy database, and some test data.
See if you can do some selects on that test data.

Keith

In theory, theory and practice are the same;
In practice they are not.

On Fri, 24 Feb 2006, Rithish Saralaya wrote:

> To: MySQL general mailing list <mysql@lists.mysql.com>
> From: Rithish Saralaya <rithish. saralaya@tallysoluti
ons.com>
> 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@oracle.com]
> 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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...ites
.net
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Heikki Tuuri

2006-02-25, 9:43 am

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@tallysoluti
ons.com>
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@oracle.com]
> 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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rithish Saralaya

2006-02-25, 9:44 am

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@oracle.com]
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"" <rithish. saralaya@tallysoluti
ons.com>
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@oracle.com]
> 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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

David Logan

2006-02-25, 9:44 am

-------------- 01000308090301030507
0804
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

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@oracle.com]
>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"" <rithish. saralaya@tallysoluti
ons.com>
>Newsgroups: mailing.database.myodbc
>Sent: Friday, February 24, 2006 6:55 AM
>Subject: RE: error 1016 : cant open ibd file even though it exists
>
>
>
>
>
>
>
>
>
>

--

David Logan
South Australia

when in trouble, or in doubt
run in circles, scream and shout

-------------- 01000308090301030507
0804--

Rithish Saralaya

2006-02-25, 9:44 am

------ =_NextPart_000_001D_
01C63A22.BB0C91A0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit

Hello David.

There was supposed to be a power outage in our office that day. So the
server was shut down. Finally when the power was back, the machine was
plugged on. That's all. No file system change. Nothing.

Regards,
Rithish.
-----Original Message-----
From: David Logan & #91;mailto:david@lcs
creative.com]
Sent: Saturday, February 25, 2006 3:32 PM
To: Rithish Saralaya
Cc: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists

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@oracle.com]
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"" <rithish. saralaya@tallysoluti
ons.com>
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@oracle.com]
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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw

--

David Logan
South Australia

when in trouble, or in doubt
run in circles, scream and shout

------ =_NextPart_000_001D_
01C63A22.BB0C91A0--

Heikki Tuuri

2006-02-25, 9:44 am

Rithish,

ok, then the most probable explanation is that someone had edited my.cnf
earlier, when mysqld was running. When mysqld was restarted, it read the new
my.cnf, and got confused.

Please ask your sysadmins to scan the file system of the computer if they
can find another ibdata1 file or ib_logfiles.

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@tallysoluti
ons.com>
Newsgroups: mailing.database.myodbc
Sent: Saturday, February 25, 2006 12:14 PM
Subject: RE: error 1016 : cant open ibd file even though it exists

> ------ =_NextPart_000_001D_
01C63A22.BB0C91A0
> Content-Type: text/plain;
> charset="Windows-1252"
> Content-Transfer-Encoding: 7bit
>
> Hello David.
>
> There was supposed to be a power outage in our office that day. So the
> server was shut down. Finally when the power was back, the machine was
> plugged on. That's all. No file system change. Nothing.
>
> Regards,
> Rithish.
> -----Original Message-----
> From: David Logan & #91;mailto:david@lcs
creative.com]
> Sent: Saturday, February 25, 2006 3:32 PM
> To: Rithish Saralaya
> Cc: mysql@lists.mysql.com
> Subject: Re: error 1016 : cant open ibd file even though it exists
>
>
> 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@oracle.com]
> 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"" <rithish. saralaya@tallysoluti
ons.com>
> 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@oracle.com]
> 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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw
>
>
>
>
>
>
>
>
> --
>
> David Logan
> South Australia
>
> when in trouble, or in doubt
> run in circles, scream and shout
>
> ------ =_NextPart_000_001D_
01C63A22.BB0C91A0--
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-02-25, 9:44 am

I do not use Innodb tables at the moment, so all this is
pure speculation.

Is/was the server connected to a UPS when the power failure
happened?

If so, did the UPS function properly and do you have any UPS
logs to match against the mysql error log?

Things to check for would be the time the UPS took over from
the mains supply?

If there was no UPS in operation, were your InnoDB tables
corrupted when the power failure occured.

Is it possible for the InnoDB tables to have become so
corrupted that mysql had no alternative but to re-construct
the tables?

What are your config settings for mysql at boot up time?
Do you have any directives to force checking of InnoDB
tables at boot-time, and if so what are they please?

As soon as you mentioned 'power failure' the words 'table
corruption' sprang to my mind.

Also, are there any mysql transaction logs (*.log) you can
check, to see what was happening when the power outage
occured?

Keith

In theory, theory and practice are the same;
In practice they are not.

On Sat, 25 Feb 2006, Rithish Saralaya wrote:

> To: david@lcscreative.com
> From: Rithish Saralaya <rithish. saralaya@tallysoluti
ons.com>
> Subject: RE: error 1016 : cant open ibd file even though it exists
>
> Hello David.
>
> There was supposed to be a power outage in our office that day. So the
> server was shut down. Finally when the power was back, the machine was
> plugged on. That's all. No file system change. Nothing.
>
> Regards,
> Rithish.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rithish Saralaya

2006-02-28, 8:28 pm

Heikki.

I found the ibdata1 and ib_log files located in only '/var/lib/mysql' which
is defined as the mysql home folder.

The last time my.cnf was editied was on the 9th of Dec, and the database was
created somewhere in the month of January. So the database would have been
created with the same configurations.

Thankfully, I have a dump of the original database. Looks like I have to
restore all of the 35 GB of data again.

Regards,
Rithish.

-----Original Message-----
From: Heikki Tuuri [mailto:heikki.tuuri@oracle.com]
Sent: Saturday, February 25, 2006 4:05 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists

Rithish,

ok, then the most probable explanation is that someone had edited my.cnf
earlier, when mysqld was running. When mysqld was restarted, it read the new
my.cnf, and got confused.

Please ask your sysadmins to scan the file system of the computer if they
can find another ibdata1 file or ib_logfiles.

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@tallysoluti
ons.com>
Newsgroups: mailing.database.myodbc
Sent: Saturday, February 25, 2006 12:14 PM
Subject: RE: error 1016 : cant open ibd file even though it exists

> ------ =_NextPart_000_001D_
01C63A22.BB0C91A0
> Content-Type: text/plain;
> charset="Windows-1252"
> Content-Transfer-Encoding: 7bit
>
> Hello David.
>
> There was supposed to be a power outage in our office that day. So the
> server was shut down. Finally when the power was back, the machine was
> plugged on. That's all. No file system change. Nothing.
>
> Regards,
> Rithish.
> -----Original Message-----
> From: David Logan & #91;mailto:david@lcs
creative.com]
> Sent: Saturday, February 25, 2006 3:32 PM
> To: Rithish Saralaya
> Cc: mysql@lists.mysql.com
> Subject: Re: error 1016 : cant open ibd file even though it exists
>
>
> 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@oracle.com]
> 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"" <rithish. saralaya@tallysoluti
ons.com>
> 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@oracle.com]
> 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: ANEAKJJGBMNHIAEBLIAI
MEPCECAA.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...sie.nctu.edu.tw
>
>
>
>
>
>
>
>
> --
>
> David Logan
> South Australia
>
> when in trouble, or in doubt
> run in circles, scream and shout
>
> ------ =_NextPart_000_001D_
01C63A22.BB0C91A0--
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Heikki Tuuri

2006-02-28, 8:28 pm

Rithish,

----- Original Message -----
From: ""Rithish Saralaya"" <rithish. saralaya@tallysoluti
ons.com>
Newsgroups: mailing.database.myodbc
Sent: Monday, February 27, 2006 7:06 AM
Subject: RE: error 1016 : cant open ibd file even though it exists

> Heikki.
>
> I found the ibdata1 and ib_log files located in only '/var/lib/mysql'
> which
> is defined as the mysql home folder.
>
> The last time my.cnf was editied was on the 9th of Dec, and the database
> was
> created somewhere in the month of January. So the database would have been
> created with the same configurations.
>
> Thankfully, I have a dump of the original database. Looks like I have to
> restore all of the 35 GB of data again.

then this remains a mystery. InnoDB never deletes ibdata files or
ib_logfiles by itself.

> Regards,
> Rithish.

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: Heikki Tuuri [mailto:heikki.tuuri@oracle.com]
> Sent: Saturday, February 25, 2006 4:05 PM
> To: mysql@lists.mysql.com
> Subject: Re: error 1016 : cant open ibd file even though it exists
>
>
> Rithish,
>
> ok, then the most probable explanation is that someone had edited my.cnf
> earlier, when mysqld was running. When mysqld was restarted, it read the
> new
> my.cnf, and got confused.
>
> Please ask your sysadmins to scan the file system of the computer if they
> can find another ibdata1 file or ib_logfiles.
>
> 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@tallysoluti
ons.com>
> Newsgroups: mailing.database.myodbc
> Sent: Saturday, February 25, 2006 12:14 PM
> Subject: RE: error 1016 : cant open ibd file even though it exists
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rithish Saralaya

2006-02-28, 8:28 pm

Hello Keith.

The power outage was known before-hand, and the server was shutdown before
the outage happened. The server was brought up once the power returned. So
no UPs intervention happened here.

Regards,
Rithish.

-----Original Message-----
From: mysql@karsites.net & #91;mailto:mysql@kar
sites.net]
Sent: Saturday, February 25, 2006 8:13 PM
To: mysql@lists.mysql.com
Subject: RE: error 1016 : cant open ibd file even though it exists

I do not use Innodb tables at the moment, so all this is
pure speculation.

Is/was the server connected to a UPS when the power failure
happened?

If so, did the UPS function properly and do you have any UPS
logs to match against the mysql error log?

Things to check for would be the time the UPS took over from
the mains supply?

If there was no UPS in operation, were your InnoDB tables
corrupted when the power failure occured.

Is it possible for the InnoDB tables to have become so
corrupted that mysql had no alternative but to re-construct
the tables?

What are your config settings for mysql at boot up time?
Do you have any directives to force checking of InnoDB
tables at boot-time, and if so what are they please?

As soon as you mentioned 'power failure' the words 'table
corruption' sprang to my mind.

Also, are there any mysql transaction logs (*.log) you can
check, to see what was happening when the power outage
occured?

Keith

In theory, theory and practice are the same;
In practice they are not.

On Sat, 25 Feb 2006, Rithish Saralaya wrote:

> To: david@lcscreative.com
> From: Rithish Saralaya <rithish. saralaya@tallysoluti
ons.com>
> Subject: RE: error 1016 : cant open ibd file even though it exists
>
> Hello David.
>
> There was supposed to be a power outage in our office that day. So the
> server was shut down. Finally when the power was back, the machine was
> plugged on. That's all. No file system change. Nothing.
>
> Regards,
> Rithish.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rithish Saralaya

2006-02-28, 8:28 pm

Hello.

Yes. Maybe a mystery that will remain unsolved for some time; however,
hopefully will get solved.

I deleted all the related files, dropped the database, and recreated it
again freshly from a backup. It was needed to try out some migration of data
across tables, and hence the data was not 'that' critical. I shudder at the
thought of this happening on the live server... I would have ended up having
a rocket lighted behind me... ;o)

On another note, what gets written into the ibdata1 file, wrt a
database/table? Can't I write/update something into it to rectify any
dangling references it has to any db/table?

Regards,
Rithish.

-----Original Message-----
From: Heikki Tuuri [mailto:heikki.tuuri@oracle.com]
Sent: Monday, February 27, 2006 1:54 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists

Rithish,

----- Original Message -----
From: ""Rithish Saralaya"" <rithish. saralaya@tallysoluti
ons.com>
Newsgroups: mailing.database.myodbc
Sent: Monday, February 27, 2006 7:06 AM
Subject: RE: error 1016 : cant open ibd file even though it exists

> Heikki.
>
> I found the ibdata1 and ib_log files located in only '/var/lib/mysql'
> which
> is defined as the mysql home folder.
>
> The last time my.cnf was editied was on the 9th of Dec, and the database
> was
> created somewhere in the month of January. So the database would have been
> created with the same configurations.
>
> Thankfully, I have a dump of the original database. Looks like I have to
> restore all of the 35 GB of data again.

then this remains a mystery. InnoDB never deletes ibdata files or
ib_logfiles by itself.

> Regards,
> Rithish.

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-02-28, 8:28 pm

Still a mystery about your missing InnoDB database files
Rithish.

I seem to remember there is an option that will allow you to
store InnoDB files in their own seperate directory safely,
and not in the /var/lib/mysql default directory.

One of the many things I like about MyISAM tables is that
each database is kept in its own subdirectory, whereas the
default for InnoDB databases seems to be mixed in with the
mysql log files. This gives me a uneasy feeling, especially
as I delete the log files when backing up mysql databases.

The moral of this post must certainly be something like:

"As the data stored in a company's databases is vital to the
operation of the company, when it comes to the integrity and
safety of your company's databases, you cannot be too
paranoid. Make regular checks and backups of your databases,
implementing a staged backup policy, so that whatever
happens, you as the DBA, are fully covered for all possible
circumstances."

Which is obviously, thank God, what you have done Rithish.

Kind Regards

Keith

In theory, theory and practice are the same;
In practice they are not.

On Tue, 28 Feb 2006, Rithish Saralaya wrote:
[color=darkred]
> To: mysql@lists.mysql.com
> From: Rithish Saralaya <rithish. saralaya@tallysoluti
ons.com>
> Subject: RE: error 1016 : cant open ibd file even though it exists
>
> Hello Keith.
>
> The power outage was known before-hand, and the server was
> shutdown before the outage happened. The server was
> brought up once the power returned. So no UPs intervention
> happened here.
>
> Regards,
> Rithish.
>
> -----Original Message-----
> From: mysql@karsites.net & #91;mailto:mysql@kar
sites.net]
> Sent: Saturday, February 25, 2006 8:13 PM
> To: mysql@lists.mysql.com
> Subject: RE: error 1016 : cant open ibd file even though it exists
>
>