RE: determine safest value for max_connections

2006-08-07 Thread Rithish Saralaya
  This might help you 
  http://kjalleda.googlepages.com/maxconnbymysql
  
  Kishore Jalleda 

Thanks Kishore.
Regards,
Rithish.


determine safest value for max_connections

2006-08-02 Thread Rithish Saralaya
Hello folks.
 
How do I determine what is the safest value that I can set for
max_connections in my.cnf? The default value of 100 is proving to be a
shortfall during some cases of peak hour traffic.
 
Could you point me to a resource available if any?

1.  Our server is RHEL 3, 2*3.00 GHz, 4GB ram. This acts both as our web
and db server.
2.  The tables are of INNODB type.
3.  mytop shows qps as 66; Hits/s as 12.3

Regards,
Rithish.


RE: identify process that created the connection

2006-08-02 Thread Rithish Saralaya
Thanks Ravi. That definitely did help.

However, the scenario that I wish to monitor is when there are a lot of
sleeping threads, it is peak-hour, and the number of threads is dangerously
near to the max_connections value. Hence, I would want to log similar
information as described in the blog, but on another server. Hence, I would
not be using up any up any conenctions on my live server for this.

My issue is that I want to log the MySQL connection id of server1 in server
2. How will I achieve this, as connection_id() will return the current
connection id (i.e. for server2)?

Regards,
Rithish.

-Original Message-
From: Ravi Prasad LR [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 28, 2006 9:36 AM
To: Rithish Saralaya
Cc: MySQL general mailing list
Subject: Re: identify process that created the connection

This blog may help,
http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connecti
on/

Cheers,
Ravi

Rithish Saralaya wrote:
 Hello people.
  
 Is it possible to find the process that invoked the mysql thread, 
 given a mysql thread id?
  
 We have a web application that runs on Linux-Apache-MySQL-PHP; and I 
 sometimes see numerous mysql threads in sleeping mode when I run 
 mytop. I think the sleeping mysql threads could be due to the fact 
 that some of my
 web-page(s) have obtained a mysql connection, executed their queries, 
 but have not terminated(and have not released the mysql connection 
 also). If I could know the httpd processes that have created these 
 connections, I would be able to find out the pages that are the culprit.
  
 Regards,
 Rithish.

   



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



identify process that created the connection

2006-07-27 Thread Rithish Saralaya
Hello people.
 
Is it possible to find the process that invoked the mysql thread, given a
mysql thread id?
 
We have a web application that runs on Linux-Apache-MySQL-PHP; and I
sometimes see numerous mysql threads in sleeping mode when I run mytop. I
think the sleeping mysql threads could be due to the fact that some of my
web-page(s) have obtained a mysql connection, executed their queries, but
have not terminated(and have not released the mysql connection also). If I
could know the httpd processes that have created these connections, I would
be able to find out the pages that are the culprit.
 
Regards,
Rithish.


RE: How to log the execution time of each query

2006-07-12 Thread Rithish Saralaya
 I would like to log the execution time for each query in my MySQL server
 .Is there any possible way?

If you are using MySQL to serve an application, then it is advisable to do
the logging through the application scripts. For in most cases, you might
require more information other than time alone, to debug slowness, such as -
- Which script fired the query.
- Number of rows retrieved.
etc.

Regards,
Rithish.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



RE: Looking for free MySQL Administrator

2006-05-23 Thread Rithish Saralaya

 I'm looking for a MySQL administrator for 4.x/5.x that will allow me to 
 
 Any suggestions? TIA

http://www.webyog.com/

Regards,
Rithish.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



RE: Re: How to find size of my database

2006-05-02 Thread Rithish Saralaya

 1) I'll assume you use myisam tables, as your version is kinda outdated.
 2) I'll assume you want the total DISK space required for your
 database. (could be rows, could be tables, could be space used by the
 rows and tables, all of those are different, different storage engines
 use space differently, a innodb table could use 1GB in your disk, and
 800MB on rows)
 3) I'll assume that you KNOW how to get the size of a given
 file/directory on your Operating System.

 If I'm wrong in any assumption, please let me know.

 Locate your data directory, if you don't know where it is, or don't
 have access to it, you just can't do this (you can always take a look
 at the manual, windows is usually the data directory, linux is
 somewhere in /var/lib, YMMV). Into this directory, you'll see a new
 directory for every database, getting the size of the directory you
 see how much disk space the database has taken.

 --
 Daniel da Veiga
 Computer Operator - RS - Brazil

Hence on linux, if your database folder is /var/lib/mysql/mydb, then execute
: du -sh /var/lib/mysql/mydb

Regards,
Rithish.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



deadlock - further information

2006-03-26 Thread Rithish Saralaya
 
  Hello.
 
  We have a web application for our intranet, which uses MySQL for db
  management. The application has a way of logging query errors that are
  generated in the system.
 
  We get a mysql error recorded as - Deadlock found when trying to get
  lock;
  try restarting transaction when one of the files tries to create a
  temporary table. The SQL query for it is as below -
  CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
  {... conditions for selection ...}
 
  I looked through the MySQL lists, but I could not get info as to why a
  deadlock is created in this scenario. I also do not know how to
replicate
  the scenario, as this error is not logged always. Is there a particular
  case
  when this happens?
 
  I looked through
  http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
  SELECT ... FROM is a consistent read, reading a snapshot of the
database
  and setting no locks ...  So why is a lock being taken in the first
  place?
 
  I am at a loss. Can someone shed light on this, or point me to some
  resource?

 in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
 make InnoDB to use a consistent read in the SELECT tables in CREATE ...
 SELECT. Read the caveats about the my.cnf option, though.

Thanks. However, whatever is written in
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on
innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my
my.cnf :o)

Also I believe this variable is available as of MySQL 4.1.4, whereas ours in
4.1.11. Upgradation is not a viable option for us right  now.


 This is explained at:
 http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html

 I noticed that this behavior is broken in 5.0. I filed the bug report
 http://bugs.mysql.com/bug.php?id=18350 about this. Thank you for bringing
 this up.

No problemos.. :o)


  Environment -
  OS : RHEL 3
  DB : MySQL 4.1.11 using INNoDB.
 
  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

Regards,
Rithish.


RE: deadlock - further information

2006-03-26 Thread Rithish Saralaya

 As 11  4, 4.1.11 is newer than 4.1.4, so this variable is available to
you.

Gawwddd... I really made an ass of myself that time... :o) Thanks for
pointing it out though.

 4.1.11 is almost a year old.  Have you read the list of bugs fixed since
then?
 http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html

Thanks for this too. However, db upgradation is not a viable option for us
right now. However, we are sure to do that in the future.

Regards,
Rithish.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



digest not received

2006-03-23 Thread Rithish Saralaya
I have not been receiving the daily digest for the past 2 days. Just wanted
to check if I am the only one it's the same for others too.

Regards,
Rithish.


deadlock - further information

2006-03-20 Thread Rithish Saralaya
Hello.

We have a web application for our intranet, which uses MySQL for db
management. The application has a way of logging query errors that are
generated in the system.

We get a mysql error recorded as - Deadlock found when trying to get lock;
try restarting transaction when one of the files tries to create a
temporary table. The SQL query for it is as below -
CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
{... conditions for selection ...}

I looked through the MySQL lists, but I could not get info as to why a
deadlock is created in this scenario. I also do not know how to replicate
the scenario, as this error is not logged always. Is there a particular case
when this happens?

I looked through
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
SELECT ... FROM is a consistent read, reading a snapshot of the database
and setting no locks ...  So why is a lock being taken in the first place?

I am at a loss. Can someone shed light on this, or point me to some
resource?

Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.

Regards,
Rithish.


RE: How to Log Warnings and Errors from queries

2006-03-12 Thread Rithish Saralaya
Hello Ryan. I am more of a developer than a MySQL administrator. Hence, I
would always favour applications logging query errors rather than being
dependent on MySQL to generate a log for me. Of course, I may be wrong.

You could write a query execution function, say exec_mysql_query(...) in one
of you files, say 'Db.inc' and have it included in all your files.

exec_mysql_query(...) will log all mysql errors into a file. and you may
provide an web-interface (assuming this is a web application) to
view/download the log files.

Regards,
Rithish.



-Original Message-
From: ryan lwf [mailto:[EMAIL PROTECTED]
Sent: Friday, March 10, 2006 8:14 PM
To: Dan Nelson
Cc: mysql@lists.mysql.com
Subject: Re: How to Log Warnings and Errors from queries


Hi Dan,

Noted with thanks.

As such, is there a workaround to log problematic sql queries ran against
the mysqld server ?  Do I need to write  separate script to do this ?

Regards,
Ryan.

On 3/10/06, Dan Nelson [EMAIL PROTECTED] wrote:

 In the last episode (Mar 08), ryan lwf said:
  I understand that the option log-errors and log-warnings only logs
  server related internal errors.  How do I enable logging errors from
  queries executed, so that I can fix the problematic query statement
  accordingly?
 
  The statement SHOW WARNINGS and SHOW ERRORS does not work on my
  server with mysqld-4.0.25 binary version.

 Those commands appeared in MySQL 4.1.  Before then, warnings were
 simply counted.

 --
Dan Nelson
[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



RE: mysqldump backup on filters

2006-03-09 Thread Rithish Saralaya
Your table is missing. Try this:

mysqldump --single-transaction -u root clientdb table
--where=FLD_CLIENT_ID=1  client1_dbbackup.sql

Yes. The tables are missing. That's because I want the backup of all the
tables in the db, and those tables that have the column FLD_CLIENT_ID, they
should be filtered by the where clause.

This is because, if we are to reproduce the system for a client, then all
we
need to do is to run this backup on a new db.

So, it looks like I will have to backup the db in phases.

1. Backup client-specific tables
mysqldump --single-transaction -u root clientdb table_M1 table_M
table_MX --where=FLD_CLIENT_ID=1  client1_dbbackup.sql

2. Then append the other tables to the backup.
mysqldump --single-transaction -u root clientdb table_N1 table_N2 table_NX
 client1_dbbackup.sql

However, this means that I cannot take a backup when the system is up and
running.

Any other ideas, folks?

Regards,
Rithish.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



manual deletion of binary log files

2006-03-03 Thread Rithish Saralaya
Hello.

Can I delete off the binary log files manually? I do not want to 'RESET
MASTER', as it will clear all the binary logs, and that's not what I want to
do. The database is backed up every midnight, and I wouldn't want to keep
any of the bin logs except the latest one.

We are on RHEL - MySQL 4.1.11 - INNoDB storage engine.

Regards,
Rithish.


mysqldump backup on filters

2006-03-02 Thread Rithish Saralaya
Hello.

We have a database driven system that serves multiple clients. We have a
single database for this purpose. The data(rows) in some of the tables are
specific to individual clients. In all such tables, we have a field
FLD_CLIENT_ID whose value depicts the client to whom that row of information
corresponds to. All other tables in the system, are agnostic to client
information; but are required for the system to function as a whole.

I would want to take seperate backups for individual clients. I try to use
mysqldump to generate a dump file by filtering on the FLD_CLIENT_ID column.
However, since not all tables contain the FLD_CLIENT_ID column, mysqldump
fails.

I use mysqldump from the commandline as


---
?mysqldump --single-transaction -u root clientdb --where=FLD_CLIENT_ID=1
 client1_dbbackup.sql


---

The error that is thrown is


---
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM
`ACCOUNT` WHERE FLD_CLIENT_ID=1': Unknown column FLD_CLIENT_ID' in 'where
clause' (1054)


---
which means thats the dump can't be created as the table ACCOUNT does not
contain the column FLD_CLIENT_ID.

Is it possible to write the where clause such that it should apply the
FLD_CLIENT_ID filter only if the column exists in the table; and if not,
dump the data anyways.

I am on RHEL - MySQL 4.1.11 - Storage engine INNoDB

Regards,
Rithish.


RE: create/restore database without binary logging

2006-03-02 Thread Rithish Saralaya
Thanks Sheeri.

Regards,
Rithish.

-Original Message-
From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 11:26 PM
To: Rithish Saralaya
Cc: MySQL general mailing list
Subject: Re: create/restore database without binary logging


Rithish,

It's possible to do this by turning off binary logging, restarting the
server, importing, turning on binary logging, and restarting.

-Sheeri

On 2/27/06, Rithish Saralaya [EMAIL PROTECTED] wrote:
 Hello.

 I was going to recreate a database of size 35 GB from sql dump file.
Wanted
 to know if it is possible to do it without mysql writing into the binary
 log. If yes, how?

 Regards,
 Rithish.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



RE: mysqldump backup on filters

2006-03-02 Thread Rithish Saralaya

Your table is missing. Try this:

?mysqldump --single-transaction -u root clientdb table
--where=FLD_CLIENT_ID=1  client1_dbbackup.sql

Yes. The tables are missing. That's because I want the backup of all the
tables in the db, and those tables that have the column FLD_CLIENT_ID, they
should be filtered by the where clause.

This is because, if we are to reproduce the system for a client, then all we
need to do is to run this backup on a new db.

Regards,
Rithish.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



create/restore database without binary logging

2006-02-27 Thread Rithish Saralaya
Hello.

I was going to recreate a database of size 35 GB from sql dump file. Wanted
to know if it is possible to do it without mysql writing into the binary
log. If yes, how?

Regards,
Rithish.


RE: error 1016 : cant open ibd file even though it exists

2006-02-27 Thread Rithish Saralaya
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
 From: Rithish Saralaya [EMAIL PROTECTED]
 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/[EMAIL PROTECTED]



RE: error 1016 : cant open ibd file even though it exists

2006-02-27 Thread Rithish Saralaya
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
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/[EMAIL PROTECTED]



RE: error 1016 : cant open ibd file even though it exists

2006-02-26 Thread Rithish Saralaya
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
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 [mailto:[EMAIL PROTECTED]
  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:[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

RE: error 1016 : cant open ibd file even though it exists

2006-02-25 Thread Rithish Saralaya
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

RE: error 1016 : cant open ibd file even though it exists

2006-02-25 Thread Rithish Saralaya
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 [mailto:[EMAIL PROTECTED]
  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:[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

RE: error 1016 : cant open ibd file even though it exists

2006-02-23 Thread Rithish Saralaya
ls -l results for the file.
-rwxrwxrwx1 mysqlmysql5863636992 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 [mailto:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia
invent
---

-Original Message-
From: Rithish Saralaya [mailto:[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia
invent
---

-Original Message-
From: Rithish Saralaya [mailto:[EMAIL PROTECTED]
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/[EMAIL PROTECTED]



RE: error 1016 : cant open ibd file even though it exists

2006-02-23 Thread Rithish Saralaya
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 [mailto:[EMAIL PROTECTED]
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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED]



RE: error 1016 : cant open ibd file even though it exists

2006-02-23 Thread Rithish Saralaya
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

error 1016 : cant open ibd file even though it exists

2006-02-22 Thread Rithish Saralaya
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.


error 1016 : cant open ibd file even though it exists

2006-02-22 Thread Rithish Saralaya
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.


RE: error 1016 : cant open ibd file even though it exists

2006-02-22 Thread Rithish Saralaya
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:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia
invent
---

-Original Message-
From: Rithish Saralaya [mailto:[EMAIL PROTECTED]
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/[EMAIL PROTECTED]



OPTIMIZE fails on very large table

2006-01-31 Thread Rithish Saralaya
Hello.

We had an INNoDB table of very large size of 47 GB. Recently we deleted a
lot of rows from the table in order to reduce the size. The number of rows
now stands at 164,000+, however, the disk usage remains the same. I tried
OPTIMIZEing the table; but the optimize fails after running for about an
hour and a half.

We have 15 GB of free space left in /var/lib/mysql/ and about 14GB in /tmp/

The MySQL server is running on a 2*3.00 GHz linux box of 4 GB ram and 140 GB
HDD.

How will we able to regain the free space?

Comments/suggestions/flamings solicited.

Regards,
Rithish.