column exists but unknown
Hello.
I am running MySQL 5.0.45
and have the following query and error:
SQL query:
SELECT SQL_CALC_FOUND_ROWS CONCAT( 'Edit Space' ) AS '', CONCAT( 'Edit
Status' ) AS '', space_id AS 'Space ID',
TYPE AS 'Ad Type', scheme AS 'Scheme', adman_ad_spaces.status_approved
AS 'Approval Status', adman_ad_spaces.status_paid AS 'Paid Status',
adman_ad_spaces.notes AS 'Notes', start_date AS 'Start Date', end_date
AS 'End Date*', adman_ad_spaces.inserted_on AS 'Created'
FROM adman_ad_spaces, adman_pricing_schemes
INNER JOIN adman_campaigns ON adman_campaigns.campaign_id =
adman_ad_spaces.campaign_id
INNER JOIN adman_users ON adman_users.user_id = adman_campaigns.user_id
WHERE adman_ad_spaces.status = 'Active'
AND adman_ad_spaces.scheme_id = adman_pricing_schemes.scheme_id
AND adman_ad_spaces.status_admin = 'Active'
AND adman_users.name LIKE '%dadasd%'
LIMIT 0 , 10
MySQL said:
#1054 - Unknown column 'adman_ad_spaces.campaign_id' in 'on clause'
part of the structure of adman_ad_spaces:
Field Type Collation Attributes Null Default Extra Action
space_id int(10) UNSIGNED No auto_increment
scheme_id int(10) UNSIGNED No 0
campaign_id int(10) UNSIGNED No 0
type enum('Text', 'Banner', 'Rich', 'Text in List') latin1_swedish_ci No Text
as you can see campaign_id clearly exists in the table
Is my table corrupt?
Any ideas?
Thank you for your help.
Richard
--
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559
You come up with the ideas, I come up with the solutions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: column exists but unknown
Nevermind, the problem was not with the DB :\
On Tue, Mar 3, 2009 at 10:44 AM, Richard Whitney phpmy...@gmail.com wrote:
Hello.
I am running MySQL 5.0.45
and have the following query and error:
SQL query:
SELECT SQL_CALC_FOUND_ROWS CONCAT( 'Edit Space' ) AS '', CONCAT( 'Edit
Status' ) AS '', space_id AS 'Space ID',
TYPE AS 'Ad Type', scheme AS 'Scheme', adman_ad_spaces.status_approved
AS 'Approval Status', adman_ad_spaces.status_paid AS 'Paid Status',
adman_ad_spaces.notes AS 'Notes', start_date AS 'Start Date', end_date
AS 'End Date*', adman_ad_spaces.inserted_on AS 'Created'
FROM adman_ad_spaces, adman_pricing_schemes
INNER JOIN adman_campaigns ON adman_campaigns.campaign_id =
adman_ad_spaces.campaign_id
INNER JOIN adman_users ON adman_users.user_id = adman_campaigns.user_id
WHERE adman_ad_spaces.status = 'Active'
AND adman_ad_spaces.scheme_id = adman_pricing_schemes.scheme_id
AND adman_ad_spaces.status_admin = 'Active'
AND adman_users.name LIKE '%dadasd%'
LIMIT 0 , 10
MySQL said:
#1054 - Unknown column 'adman_ad_spaces.campaign_id' in 'on clause'
part of the structure of adman_ad_spaces:
Field Type Collation Attributes Null Default Extra Action
space_id int(10) UNSIGNED No auto_increment
scheme_id int(10) UNSIGNED No 0
campaign_id int(10) UNSIGNED No 0
type enum('Text', 'Banner', 'Rich', 'Text in List') latin1_swedish_ci No Text
as you can see campaign_id clearly exists in the table
Is my table corrupt?
Any ideas?
Thank you for your help.
Richard
--
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559
You come up with the ideas, I come up with the solutions.
--
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559
You come up with the ideas, I come up with the solutions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best method for checking if a row exists.
Hello, What is the best method to check if (one or more) row exists (note: primary key is auto inc and table engine is InnoDB - but what if these were not true) ? 1) SELECT * FROM table WHERE condition Check to see if the result set is non-empty. 2) SELECT COUNT(*) AS cnt FROM table WHERE condition Check to see if the field 'cnt' is non-zero. 3) SELECT primary key FROM table WHERE condition LIMIT 1 Check to see if the result set is non-empty. 4) SELECT 1 FROM table WHERE condition LIMIT 1 Check to see if the result set is non-empty. Any other possibilities? I hate (1) and don't mind the rest if PK is auto inc and table engine is InnoDB but I think (4) would be the best where PK is not auto inc and/or table engine is not InnoDB. Comments? Many thanks in advance, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method for checking if a row exists.
SELECT EXISTS( SELECT * FROM table WHERE condition ) - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to determine if temporary table exists
At 12:00 AM 11/21/2008, you wrote: In the last episode (Nov 20), mos said: At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Why not CREATE TEMPORARY TABLE IF NOT EXISTS ...? If you really need to know whether the table existed before or not, that command will return a warning if it was there already. http://dev.mysql.com/doc/refman/5.0/en/create-table.html Dan, That will work. :-) Thanks. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine if temporary table exists
How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine if temporary table exists
Try drop table if exists Tablex; On Fri, Nov 21, 2008 at 9:53 AM, mos [EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to determine if temporary table exists
At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Mike On Fri, Nov 21, 2008 at 9:53 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cnhttp://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine if temporary table exists
In the last episode (Nov 20), mos said: At 08:02 PM 11/20/2008, you wrote: Try drop table if exists Tablex; Ahhh, I don't necessarily want to drop the table if it already exists. :) If the table already exists then I'll add new rows to it (and keep the existing rows). If the table doesn't exist, then I'll create it. I suppose could count the rows in Tablex and it would throw an exception if the table did not exist . But I really didn't want to resort to trapping an exception in my program. I thought there should be an easy way using SQL to determine if a temporary table exists or not. Why not CREATE TEMPORARY TABLE IF NOT EXISTS ...? If you really need to know whether the table existed before or not, that command will return a warning if it was there already. http://dev.mysql.com/doc/refman/5.0/en/create-table.html -- 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: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
-Original Message- From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Sunday, August 24, 2008 12:00 PM To: Deniss Hennesy Cc: mysql Subject: Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists correction, the command that's failing evidently is the 'create table columns_priv', not a drop table. Sorry for the typo. [JS] Are you running as root, or whichever user has full privileges? It's easy to make that mistake, and you'll get a lot of barking and growling. 2008/8/24 Jim Lyons [EMAIL PROTECTED] The command that's failing evidently is the drop table columns_priv which is why you're getting the error message: *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* I assume you're trying to restore the mysql database from a backup and the script is trying to re-create the privileges tables. You cannot create a table that already exists, so you must drop it first. When you installed a mysql instance on the new server of course it put in a mysql database, that's required. You're now trying to replace it with the mysql database from your previous instance. You have to replace the old mysql database with the new mysql database. You do understand the difference between the mysql *database* and the mysql *instance*, right? 2008/8/24 Deniss Hennesy [EMAIL PROTECTED] I dont understand what you mean by drop table... why i must droped my table? cýrrent server is running properly.. but either OS version or HW is very old. We took new powerfull server, anc we want to migrate our data to new server... onlt this i want to do it. 2008/8/22 Jim Lyons [EMAIL PROTECTED] As I said, it appears your mysqldump output is not doing a DROP TABLE before each create. You need to correct that. Do the drop table commands yourself, then load the tables. Afterwards, do a FLUSH PRIVILEGES to activate the new permissions.l 2008/8/22 Deniss Hennesy [EMAIL PROTECTED] Ý am migrating database from mysql-client-4.0.20 running server(current) to mysql-client-5.0.51a running server(new) ..i ve took mysqldump and transfered to new server and restored but process broken up with this error message. On Fri, Aug 22, 2008 at 6:33 PM, Jim Lyons [EMAIL PROTECTED]wrote: You're restoring the mysql database itself, do you mean to do this? You probably do if it's a new server but there already exists a mysql database so, unless your restore program does DROP TABLE before each create, you'll keep getting this error. On Fri, Aug 22, 2008 at 10:22 AM, Deniss Hennesy [EMAIL PROTECTED] wrote: Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
All Dear friends İ am mentioning that 2 different server one is running on mysql 4.1 (Current) (dumped) the other is 5.1..xxx (New) (restored) İ solved my problem in such a way that i droped in my new server mysql and test databases mysql show databases; +---+ | Database | ++ | information_schema | | mysql | | test| ++ Than restore my backup finally no any error. thank for all you, for your help , time... regards
Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
The command that's failing evidently is the drop table columns_priv which is why you're getting the error message: *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* I assume you're trying to restore the mysql database from a backup and the script is trying to re-create the privileges tables. You cannot create a table that already exists, so you must drop it first. When you installed a mysql instance on the new server of course it put in a mysql database, that's required. You're now trying to replace it with the mysql database from your previous instance. You have to replace the old mysql database with the new mysql database. You do understand the difference between the mysql *database* and the mysql *instance*, right? 2008/8/24 Deniss Hennesy [EMAIL PROTECTED] I dont understand what you mean by drop table... why i must droped my table? cırrent server is running properly.. but either OS version or HW is very old. We took new powerfull server, anc we want to migrate our data to new server... onlt this i want to do it. 2008/8/22 Jim Lyons [EMAIL PROTECTED] As I said, it appears your mysqldump output is not doing a DROP TABLE before each create. You need to correct that. Do the drop table commands yourself, then load the tables. Afterwards, do a FLUSH PRIVILEGES to activate the new permissions.l 2008/8/22 Deniss Hennesy [EMAIL PROTECTED] İ am migrating database from mysql-client-4.0.20 running server(current) to mysql-client-5.0.51a running server(new) ..i ve took mysqldump and transfered to new server and restored but process broken up with this error message. On Fri, Aug 22, 2008 at 6:33 PM, Jim Lyons [EMAIL PROTECTED] wrote: You're restoring the mysql database itself, do you mean to do this? You probably do if it's a new server but there already exists a mysql database so, unless your restore program does DROP TABLE before each create, you'll keep getting this error. On Fri, Aug 22, 2008 at 10:22 AM, Deniss Hennesy [EMAIL PROTECTED] wrote: Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
correction, the command that's failing evidently is the 'create table columns_priv', not a drop table. Sorry for the typo. 2008/8/24 Jim Lyons [EMAIL PROTECTED] The command that's failing evidently is the drop table columns_priv which is why you're getting the error message: *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* I assume you're trying to restore the mysql database from a backup and the script is trying to re-create the privileges tables. You cannot create a table that already exists, so you must drop it first. When you installed a mysql instance on the new server of course it put in a mysql database, that's required. You're now trying to replace it with the mysql database from your previous instance. You have to replace the old mysql database with the new mysql database. You do understand the difference between the mysql *database* and the mysql *instance*, right? 2008/8/24 Deniss Hennesy [EMAIL PROTECTED] I dont understand what you mean by drop table... why i must droped my table? cırrent server is running properly.. but either OS version or HW is very old. We took new powerfull server, anc we want to migrate our data to new server... onlt this i want to do it. 2008/8/22 Jim Lyons [EMAIL PROTECTED] As I said, it appears your mysqldump output is not doing a DROP TABLE before each create. You need to correct that. Do the drop table commands yourself, then load the tables. Afterwards, do a FLUSH PRIVILEGES to activate the new permissions.l 2008/8/22 Deniss Hennesy [EMAIL PROTECTED] İ am migrating database from mysql-client-4.0.20 running server(current) to mysql-client-5.0.51a running server(new) ..i ve took mysqldump and transfered to new server and restored but process broken up with this error message. On Fri, Aug 22, 2008 at 6:33 PM, Jim Lyons [EMAIL PROTECTED]wrote: You're restoring the mysql database itself, do you mean to do this? You probably do if it's a new server but there already exists a mysql database so, unless your restore program does DROP TABLE before each create, you'll keep getting this error. On Fri, Aug 22, 2008 at 10:22 AM, Deniss Hennesy [EMAIL PROTECTED] wrote: Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
ERROR 1050 ( ) at line : Table 'columns_priv' already exists
Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards
Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
You're restoring the mysql database itself, do you mean to do this? You probably do if it's a new server but there already exists a mysql database so, unless your restore program does DROP TABLE before each create, you'll keep getting this error. On Fri, Aug 22, 2008 at 10:22 AM, Deniss Hennesy [EMAIL PROTECTED]wrote: Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: ERROR 1050 ( ) at line : Table 'columns_priv' already exists
As I said, it appears your mysqldump output is not doing a DROP TABLE before each create. You need to correct that. Do the drop table commands yourself, then load the tables. Afterwards, do a FLUSH PRIVILEGES to activate the new permissions.l 2008/8/22 Deniss Hennesy [EMAIL PROTECTED] İ am migrating database from mysql-client-4.0.20 running server(current) to mysql-client-5.0.51a running server(new) ..i ve took mysqldump and transfered to new server and restored but process broken up with this error message. On Fri, Aug 22, 2008 at 6:33 PM, Jim Lyons [EMAIL PROTECTED] wrote: You're restoring the mysql database itself, do you mean to do this? You probably do if it's a new server but there already exists a mysql database so, unless your restore program does DROP TABLE before each create, you'll keep getting this error. On Fri, Aug 22, 2008 at 10:22 AM, Deniss Hennesy [EMAIL PROTECTED] wrote: Hi to list i ve installed new server. while i was restoring my backup to this server. i took this error and restoring procees is to stop. *ERROR 1050 ( ) at line : Table 'columns_priv' already exists* My old server mysql version is mysql-client-4.0.20 is running on old server but mysql-client-5.0.51a is running now what can i do regards -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
DROP TABLE IF EXISTS - doesnt??
I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me?
Re: DROP TABLE IF EXISTS - doesnt??
Hmmm. It works okay for me, without an error when the tabel doesn't exist. I'm using mysql Server version 5.0.51a-3ubuntu5.1 For example: * mysql use lsldatabase; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +---+ | Tables_in_lsldatabase | +---+ | lslstore | +---+ 1 row in set (0.00 sec) mysql drop table if exists recipes; Query OK, 0 rows affected, 1 warning (0.00 sec) * As you see, the table recipes doesn't already exist, and I don't get an error. roger.maynard wrote: I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP TABLE IF EXISTS - doesnt??
I am getting this problem when I am calling this from within a stored procedure and from the command line area but from MySQLQueryBrowser Windows package. I have tried the DROP TABLE IF EXISTS from the mysql DOS-type command line and it doesn't error - I do notice that (also in your example) that there is a Warning provided. Maybe what I am seeing in the Query Browser area is in fact a Warning message and not an error!! If so, sorry to have wasted anyone's time Regards Roger -Original Message- From: Fish Kungfu [mailto:[EMAIL PROTECTED] Sent: 15 August 2008 12:43 To: mysql@lists.mysql.com Subject: Re: DROP TABLE IF EXISTS - doesnt?? Hmmm. It works okay for me, without an error when the tabel doesn't exist. I'm using mysql Server version 5.0.51a-3ubuntu5.1 For example: * mysql use lsldatabase; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +---+ | Tables_in_lsldatabase | +---+ | lslstore | +---+ 1 row in set (0.00 sec) mysql drop table if exists recipes; Query OK, 0 rows affected, 1 warning (0.00 sec) * As you see, the table recipes doesn't already exist, and I don't get an error. roger.maynard wrote: I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DROP TABLE IF EXISTS - doesnt??
I am certain the SQLBrowser program authors want to hear about this error It may be due to the fact that MYSQL procedures need to have a different delimiter (//) instead of ; e.g. use DBNAME; DROP PROCEDURE IF EXISTS TABLE_NAME; delimiter //; Anyone? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Subject: RE: DROP TABLE IF EXISTS - doesnt?? Date: Fri, 15 Aug 2008 14:50:16 +0100 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com I am getting this problem when I am calling this from within a stored procedure and from the command line area but from MySQLQueryBrowser Windows package. I have tried the DROP TABLE IF EXISTS from the mysql DOS-type command line and it doesn't error - I do notice that (also in your example) that there is a Warning provided. Maybe what I am seeing in the Query Browser area is in fact a Warning message and not an error!! If so, sorry to have wasted anyone's time Regards Roger -Original Message- From: Fish Kungfu [mailto:[EMAIL PROTECTED] Sent: 15 August 2008 12:43 To: mysql@lists.mysql.com Subject: Re: DROP TABLE IF EXISTS - doesnt?? Hmmm. It works okay for me, without an error when the tabel doesn't exist. I'm using mysql Server version 5.0.51a-3ubuntu5.1 For example: * mysql use lsldatabase; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +---+ | Tables_in_lsldatabase | +---+ | lslstore | +---+ 1 row in set (0.00 sec) mysql drop table if exists recipes; Query OK, 0 rows affected, 1 warning (0.00 sec) * As you see, the table recipes doesn't already exist, and I don't get an error. roger.maynard wrote: I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Talk to your Yahoo! Friends via Windows Live Messenger. Find out how. http://www.windowslive.com/explore/messenger?ocid=TXT_TAGLM_WL_messenger_yahoo_082008
RE: DROP TABLE IF EXISTS - doesnt??
Maybe what I am seeing in the Query Browser area is in fact a Warning message and not an error!! Ahhh...yes maybe that's it. Just fyi, here is the detail of the warning: mysql drop table if exists recipes; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+-+ | Level | Code | Message | +---+--+-+ | Note | 1051 | Unknown table 'recipes' | +---+--+-+ 1 row in set (0.00 sec) CheersFish --- ~~Second Life addict ~~ -Original Message- From: roger.maynard [EMAIL PROTECTED] Sent: Aug 15, 2008 9:50 AM To: mysql@lists.mysql.com Subject: RE: DROP TABLE IF EXISTS - doesnt?? I am getting this problem when I am calling this from within a stored procedure and from the command line area but from MySQLQueryBrowser Windows package. I have tried the DROP TABLE IF EXISTS from the mysql DOS-type command line and it doesn't error - I do notice that (also in your example) that there is a Warning provided. Maybe what I am seeing in the Query Browser area is in fact a Warning message and not an error!! If so, sorry to have wasted anyone's time Regards Roger -Original Message- From: Fish Kungfu [mailto:[EMAIL PROTECTED] Sent: 15 August 2008 12:43 To: mysql@lists.mysql.com Subject: Re: DROP TABLE IF EXISTS - doesnt?? Hmmm. It works okay for me, without an error when the tabel doesn't exist. I'm using mysql Server version 5.0.51a-3ubuntu5.1 For example: * mysql use lsldatabase; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show tables; +---+ | Tables_in_lsldatabase | +---+ | lslstore | +---+ 1 row in set (0.00 sec) mysql drop table if exists recipes; Query OK, 0 rows affected, 1 warning (0.00 sec) * As you see, the table recipes doesn't already exist, and I don't get an error. roger.maynard wrote: I am finding that DROP TABLE IF EXISTS mytable; Works fine if the table exists - but if it doesn't exist I get an error? Surely it should not error and just not try to drop the table. Is it me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT WHERE NOT EXISTS syntax
Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't? Roger
RE: INSERT WHERE NOT EXISTS syntax
I think I sorted it out ... INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS ( SELECT comment_no FROM master_comments WHERE mComments.comment_no = master_comments.comment_no ); Hope this helps someone else . -Original Message- From: roger.maynard [mailto:[EMAIL PROTECTED] Sent: 23 January 2008 18:58 To: mysql@lists.mysql.com Subject: INSERT WHERE NOT EXISTS syntax Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't? Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
Fantastic, thanks very much! Adam Rob Wultsch wrote: On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
Sorry I got carried away in my former response. When I tried this, ALTER IGNORE TABLE tbl_name DROP INDEX seems to fail if the index does not exist. The DROP INDEX statement is part of a larger script, what I want is for the script to conue to execute if this index does not exist. e.g. ALTER IGNORE TABLE table_name DROP INDEX index_name; ALTER IGNORE TABLE table_name ADD INDEX UNIQUE index_name(column_1,column_2); Thanks - Ada Rob Wultsch wrote: On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- Adam Lipscombe T: 01872 575083 M: 07957 548686 E: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
On Dec 5, 2007 7:03 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Sorry I got carried away in my former response. When I tried this, ALTER IGNORE TABLE tbl_name DROP INDEX seems to fail if the index does not exist. Right. ALTER IGNORE means that rows that violate unique indexes won't be copied to the new table during the ALTER. It has nothing to do with what you're looking for. There is no statement that does what you're looking for, as far as I know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote: Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam Sent my first response late at night and not the community... And the response also sucked. DROP INDEX is mapped to ALTER TABLE tbl_name DROP INDEX. ALTER IGNORE TABLE tbl_name DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to drop index if exists
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'given schema'
AND table_name = 'given table name'
AND index_name = 'given index name';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE tbl-name DROP INDEX index-name;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote:
Folks
How can one conditionally drop an index in MySQL?
Googling shows that the drop index does not support an if exists
qualifier - apparently a bug
has been raised but as far as I know its not fixed yet.
Does anyone know of a work-around?
TIA - Adam
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop index if exists
I actually suggested that last night, and thought better of it because
the alter ignore was so much simpler...
On Dec 3, 2007 11:18 AM, Rolando Edwards [EMAIL PROTECTED] wrote:
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'given schema'
AND table_name = 'given table name'
AND index_name = 'given index name';
This returns the number of columns the index contains.
If this query returns zero(0), then the index does not exist.
If this query returns a positive number, then call
ALTER TABLE tbl-name DROP INDEX index-name;
You may want to write this stored procedure to do this.
Here is the Code (change the 'util' schema to the schema you want) :
DELIMITER $$
DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
VARCHAR(64),ndxName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(1) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = tblSchema
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'`
DROP INDEX `',ndxName,'`');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
Give it a try !!!
-Original Message-
From: Rob Wultsch [mailto:[EMAIL PROTECTED]
Sent: Monday, December 03, 2007 12:51 PM
To: Adam Lipscombe
Cc: mysql@lists.mysql.com
Subject: Re: how to drop index if exists
On Nov 29, 2007 4:34 AM, Adam Lipscombe [EMAIL PROTECTED] wrote:
Folks
How can one conditionally drop an index in MySQL?
Googling shows that the drop index does not support an if exists
qualifier - apparently a bug
has been raised but as far as I know its not fixed yet.
Does anyone know of a work-around?
TIA - Adam
Sent my first response late at night and not the community... And the
response also sucked.
DROP INDEX is mapped to ALTER TABLE tbl_name
DROP INDEX.
ALTER IGNORE TABLE tbl_name
DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your
goal?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to drop index if exists
Folks How can one conditionally drop an index in MySQL? Googling shows that the drop index does not support an if exists qualifier - apparently a bug has been raised but as far as I know its not fixed yet. Does anyone know of a work-around? TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP TRIGGER IF EXISTS throws error??
I'm attempting to restore a couple of backups, and part way through, I get the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS alumni' at line 1 Query is: DROP TRIGGER IF EXISTS `alumni`; Seems perfectly valid to me. If I remove the IF EXISTS part, then I get the error, trigger doesn't exist. What's going on? This usually works fine. I'm running version 5.0.45-community-nt Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP TRIGGER IF EXISTS throws error??
Hi, Jesse wrote: I'm attempting to restore a couple of backups, and part way through, I get the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS alumni' at line 1 Query is: DROP TRIGGER IF EXISTS `alumni`; Seems perfectly valid to me. If I remove the IF EXISTS part, then I get the error, trigger doesn't exist. What's going on? This usually works fine. I'm running version 5.0.45-community-nt Are you sure that's the exact query and error message? The query has a backtick; the error message has none, which is unusual for a purely syntactic error. It makes me suspicious that the error is coming from something else: maybe a subtly mangled file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP TRIGGER IF EXISTS throws error??
Are you sure that's the exact query and error message? The query has a backtick; the error message has none, which is unusual for a purely syntactic error. It makes me suspicious that the error is coming from something else: maybe a subtly mangled file. I found the problem. When I ran the MySQL Administrator to start the restore, I didn't notice that I was connected to another server. In face, I was connecting to the server from which the backup came from originally, but from another machine. Once I caught this, and changed it to the local machine, the restore (and the DROP TRIGGER command) went through fine. It may simply be that the user I used to connect to the other server didn't have access rights to do that, I'm not sure, but the error message indicated a syntactical error (as you noted), not an access error. So, it's a mystery to me why the message didn't seem to indicate it properly, but I've got it working now. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump - IF NOT EXISTS
Hi Im contructing a build script and would like to dump my dev db with mysqldump so I can run a deploy script on the production server with the update db. I would like to construct the script so that it updates existing tables/fields/posts and add new tables/fields/post if they do not exists. My buildscript constructs this command (written in php): $command = mysqldump. --password=$pass. --user=$user. --skip-opt. --add-locks. --create-options. --disable-keys. --extended-insert. --lock-tables. --quick. --result-file=.self::BUILD_PATH.'/'.self::PROJECT_NAME./.$dbName._dump.sql. --databases .$dbName; shell_exec($command); and mysql dump generates the file with out any problems...but: I have noticed, that when I dump from phpmyadmin 'IF NOT EXISTS' is added to all create table statements. This dos not happen with the above command. It is added to create database statement thought... is that good enought? In the phpmyadmin dump you can use update statements instead of insert to populate the tables. Can, and if so, how is this accomplished with mysqldump? I have read alot about mysqldump at this adress: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html but can't seem to find the answers for the above questions. Any help or pointer greatly appreciated. -- Regards / Venlig hilsen Johannes Skov Frandsen *Address:* Egelundsvej 18, DK-5260 Odense S *Web:* www.omesc.com | *Email:* [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE (NOT) EXISTS problem
Hello Michael. Here is the query you wanted - you were almost there. SELECT DISTINCT NAME FROM PEOPLE WHERE ID IN (SELECT PID FROM PEOPLE_CITY_MAP WHERE CID = 1) Thanks. -Original Message- From: Michael Fischer [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 31, 2007 11:53 PM To: mysql@lists.mysql.com Subject: WHERE (NOT) EXISTS problem Having a very bad time with the subject sorts of queries. Here is a simple reproduction of the problem for me. Perhaps I'm blind/stupid while looking at the docs, or there's a bug... mysql version 5.0.24-standard simple schema: mysql desc people; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc cities; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc people_city_map; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | pid | int(11) | NO | | | | | cid | int(11) | NO | | | | +---+-+--+-+-+---+ mysql select * from people; ++-+ | id | name| ++-+ | 1 | michael | | 2 | daniel | | 3 | glenn | | 4 | susan | | 5 | lisa| | 6 | reggie | ++-+ mysql select * from cities; ++--+ | id | name | ++--+ | 1 | new york | | 2 | boston | | 3 | chicago | ++--+ mysql select * from people_city_map; +-+-+ | pid | cid | +-+-+ | 1 | 1 | | 2 | 1 | | 5 | 3 | | 6 | 3 | +-+-+ Now, not that this is the best way to do things generally, but, after all, this is a simplification of a more complex real app in development, so... mysql SELECT distinct name FROM people WHERE EXISTS (select * from people_city_map where cid = 1); +-+ | name| +-+ | michael | | daniel | | glenn | | susan | | lisa| | reggie | +-+ Huh? Shouldn't that be limited to the people mapped to New York (michael, daniel)? Conversely: mysql SELECT distinct name FROM people WHERE EXISTS (select * from people_city_map where cid = 1); Empty set (0.00 sec) *blink* Shouldn't that produce susan and glenn, who are not in the map table at all? Very confused. Normal joins across the tables work as expected. Am I overlooking something, or is there sign of a bug? I'll admit, I'm perplexed over the bit in the docs which says Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference. Thanks in advance Michael -- Michael Fischer Happiness is a config option. [EMAIL PROTECTED]Recompile and be happy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE (NOT) EXISTS problem
This also works... SELECT name FROM people AS p JOIN people_city_map AS pcm ON (p.id = pcm.pid) WHERE pcm.cid = 1; Not sure if it is any faster (or better) than what Chris suggested but it seems to be faster on my machine. Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 1:02 AM To: Michael Fischer Cc: mysql@lists.mysql.com Subject: Re: WHERE (NOT) EXISTS problem Michael Fischer wrote: Having a very bad time with the subject sorts of queries. Here is a simple reproduction of the problem for me. Perhaps I'm blind/stupid while looking at the docs, or there's a bug... mysql version 5.0.24-standard simple schema: mysql desc people; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc cities; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc people_city_map; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | pid | int(11) | NO | | | | | cid | int(11) | NO | | | | +---+-+--+-+-+---+ mysql select * from people; ++-+ | id | name| ++-+ | 1 | michael | | 2 | daniel | | 3 | glenn | | 4 | susan | | 5 | lisa| | 6 | reggie | ++-+ mysql select * from cities; ++--+ | id | name | ++--+ | 1 | new york | | 2 | boston | | 3 | chicago | ++--+ mysql select * from people_city_map; +-+-+ | pid | cid | +-+-+ | 1 | 1 | | 2 | 1 | | 5 | 3 | | 6 | 3 | +-+-+ Now, not that this is the best way to do things generally, but, after all, this is a simplification of a more complex real app in development, so... mysql SELECT distinct name FROM people WHERE EXISTS (select * from people_city_map where cid = 1); +-+ | name| +-+ | michael | | daniel | | glenn | | susan | | lisa| | reggie | +-+ Huh? Shouldn't that be limited to the people mapped to New York (michael, daniel)? No. Simple misunderstanding of the docs. EXISTS returns TRUE if it finds *any* result. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries. html You want: select distinct name from people where pid in (select pid from people_city_map where cid=1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE (NOT) EXISTS problem
Having a very bad time with the subject sorts of queries. Here is a simple reproduction of the problem for me. Perhaps I'm blind/stupid while looking at the docs, or there's a bug... mysql version 5.0.24-standard simple schema: mysql desc people; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc cities; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc people_city_map; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | pid | int(11) | NO | | | | | cid | int(11) | NO | | | | +---+-+--+-+-+---+ mysql select * from people; ++-+ | id | name| ++-+ | 1 | michael | | 2 | daniel | | 3 | glenn | | 4 | susan | | 5 | lisa| | 6 | reggie | ++-+ mysql select * from cities; ++--+ | id | name | ++--+ | 1 | new york | | 2 | boston | | 3 | chicago | ++--+ mysql select * from people_city_map; +-+-+ | pid | cid | +-+-+ | 1 | 1 | | 2 | 1 | | 5 | 3 | | 6 | 3 | +-+-+ Now, not that this is the best way to do things generally, but, after all, this is a simplification of a more complex real app in development, so... mysql SELECT distinct name FROM people WHERE EXISTS (select * from people_city_map where cid = 1); +-+ | name| +-+ | michael | | daniel | | glenn | | susan | | lisa| | reggie | +-+ Huh? Shouldn't that be limited to the people mapped to New York (michael, daniel)? Conversely: mysql SELECT distinct name FROM people WHERE EXISTS (select * from people_city_map where cid = 1); Empty set (0.00 sec) *blink* Shouldn't that produce susan and glenn, who are not in the map table at all? Very confused. Normal joins across the tables work as expected. Am I overlooking something, or is there sign of a bug? I'll admit, I'm perplexed over the bit in the docs which says Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference. Thanks in advance Michael -- Michael Fischer Happiness is a config option. [EMAIL PROTECTED]Recompile and be happy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE (NOT) EXISTS problem
Michael Fischer wrote: Having a very bad time with the subject sorts of queries. Here is a simple reproduction of the problem for me. Perhaps I'm blind/stupid while looking at the docs, or there's a bug... mysql version 5.0.24-standard simple schema: mysql desc people; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc cities; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | NO | PRI | NULL| auto_increment | | name | varchar(255) | YES | | NULL|| +---+--+--+-+-++ mysql desc people_city_map; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | pid | int(11) | NO | | | | | cid | int(11) | NO | | | | +---+-+--+-+-+---+ mysql select * from people; ++-+ | id | name| ++-+ | 1 | michael | | 2 | daniel | | 3 | glenn | | 4 | susan | | 5 | lisa| | 6 | reggie | ++-+ mysql select * from cities; ++--+ | id | name | ++--+ | 1 | new york | | 2 | boston | | 3 | chicago | ++--+ mysql select * from people_city_map; +-+-+ | pid | cid | +-+-+ | 1 | 1 | | 2 | 1 | | 5 | 3 | | 6 | 3 | +-+-+ Now, not that this is the best way to do things generally, but, after all, this is a simplification of a more complex real app in development, so... mysql SELECT distinct name FROM people WHERE EXISTS (select * from people_city_map where cid = 1); +-+ | name| +-+ | michael | | daniel | | glenn | | susan | | lisa| | reggie | +-+ Huh? Shouldn't that be limited to the people mapped to New York (michael, daniel)? No. Simple misunderstanding of the docs. EXISTS returns TRUE if it finds *any* result. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html You want: select distinct name from people where pid in (select pid from people_city_map where cid=1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drop Index if Exists
Folks, Does this work in MYSQL 5? I tried DROP INDEX [NAME] IF EXISTS; and got an error check your syntax. Thanks - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop Index if Exists
Hello Adam,This is the exact syntax to drop the index.DROP INDEX index_name ON tbl_nameThanksVisolve DB Team . - Original Message - From: Adam Lipscombe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, September 01, 2006 3:28 PM Subject: Drop Index if Exists Folks, Does this work in MYSQL 5? I tried DROP INDEX [NAME] IF EXISTS; and got an error check your syntax. Thanks - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop Index if Exists
Thanks What I am getting at is: does the IF EXISTS qualifier work in this context? I have an index that is present in some DB's but not in others. I want to run a generic script to upgrade them all. I don't want the script to stop if the index is not present. Thanks - Adam Visolve DB TEAM wrote: Hello Adam,This is the exact syntax to drop the index.DROP INDEX index_name ON tbl_nameThanksVisolve DB Team . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Return list where no data exists
Neil Tompkins wrote: Using this query seems to hang my computer and mySQL server reported the queries had been LOCKED. How many records are in each table? It could take a while, especially if you don't have indexes on the join fields. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Return list where no data exists
Hi, I've two tables. What query do I need to use to get a list of all records from table A where table B doesn't contain a a mathing record. For example TableA ID Name TableB Date ID Name Thanks Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: Return list where no data exists
Hi Neil, what you need is a LEFT JOIN: SELECT a.* FROM TableA a LEFT JOIN TableB b ON a.ID = b.ID (assuming ID is what you relate the tables on ) WHERE b.ID IS NULL; should hopefully do what you want. /Johan Neil Tompkins skrev: Hi, I've two tables. What query do I need to use to get a list of all records from table A where table B doesn't contain a a mathing record. For example TableA ID Name TableB Date ID Name Thanks Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 2006-08-18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Return list where no data exists
Using this query seems to hang my computer and mySQL server reported the
queries had been LOCKED.
Date: Mon, 21 Aug 2006 11:09:35 +0200 From: [EMAIL PROTECTED] To: [EMAIL
PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Return list where no data
exists Hi Neil, what you need is a LEFT JOIN: SELECT a.* FROM TableA a
LEFT JOIN TableB b ON a.ID = b.ID (assuming ID is what you relate the
tables on ) WHERE b.ID IS NULL; should
hopefully do what you want. /Johan Neil Tompkins skrev: Hi,
I've two tables. What query do I need to use to get a list of all records
from table A where table B doesn't contain a a mathing record. For example
TableA ID Name TableB Date ID Name
Thanks Neil
_ Be one
of the first to try Windows Live Mail.
http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 2006-08-18
_
Be one of the first to try Windows Live Mail.
http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: If exists query.
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events count(*) between 2005-12-10 and 2006-04-14
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid=1 AND
signature_id1 GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop through
top ten')
I now want to know; out of all of the days between first seen and last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to accomplish.
What is the source of your data?
What is the condition you are testing for?
And what, very specifically, is it that you would like us to help you with.
--John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
--- Paul Halliday [EMAIL PROTECTED] wrote:
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like
this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events count(*) between 2005-12-10 and
2006-04-14
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web
access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid=1 AND
signature_id1 GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that
address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop
through
top ten')
I now want to know; out of all of the days between first seen and
last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know
between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to
accomplish.
What is the source of your data?
What is the condition you are testing for?
And what, very specifically, is it that you would like us to help
you with.
--John
I think what you are looking for is the DISTINCT modifier to the
COUNT() aggregate function. That way you count only how many different
values exist in the list, not how many items are in the list.
Count | IP Address| First Seen| Last Seen | Days
SELECT
COUNT(src_ip) AS CNT
, INET_NTOA(src_ip)
, MIN(timestamp) first
, MAX(timestamp) last
, COUNT(DISTINCT DATE(timestamp)) days
FROM event
WHERE timestamp '2006-04-24 03:00:00'
AND sid=1
AND signature_id1
GROUP BY src_ip
ORDER BY cnt DESC
LIMIT 10
That is close to what you want. There are several ways to convert
timetsamps values into something that can be counted as a unique day
but I think the DATE function will be fast enough.
Shawn Green
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
Paul,
I am doing queries that produce a table that looks
something like this:
Count | IP Address | First Seen | Last
Seen | Days
5000 10.0.0.1 2005-12-10
2006-04-15 50*
6500 10.0.0.2 2006-04-01
2006-04-06 3**
*So of the 5000 events "count(*) between 2005-12-10
and 2006-04-14"
the count was distributed within 50 distinct days.
**This time the count is still high and it occured
within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days
part :) .
snip
You could grab the first day for each src_ip with something like ...
SELECT
COUNT(src_ip) AS CNT,
INET_NTOA(src_ip),
MAX(timestamp),
(SELECT MIN(timestamp) FROM event e2 WHERE e2.src_ip=e1.src_ip) AS
First
FROM event e1
WHERE timestamp '2006-04-24 03:00:00'
AND sid=1
AND signature_id1
GROUP BY src_ip
ORDER BY cnt DESC LIMIT 10
I now want to know; out of all of the days between first seen and
last
seen which days had events on them. I dont want the count(events)
for
eah day, just whether there was an event or not so that I know
between
first seen and last seen what the rate of appearance was.
That's easiest if you maintain a calendar table with one row per day in
the appropriate range. Then you can simply count the rows you get on a
ranged events-calendar join.
PB
-
Paul Halliday wrote:
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events "count(*) between 2005-12-10 and 2006-04-14"
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid="1" AND
signature_id"1" GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop through
top ten')
I now want to know; out of all of the days between first seen and last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to accomplish.
What is the source of your data?
What is the "condition" you are testing for?
And what, very specifically, is it that you would like us to help you with.
--John
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
Paul Halliday wrote: I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Could you be a bit more specific? Where do you want to store the count? What do you mean by if there was data on this day? Does the design of the table anticipate no more than one record per day? Does each record correspond to a single day? --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
I am doing queries that produce a table that looks something like this: Count | IP Address| First Seen| Last Seen | Days 5000 10.0.0.1 2005-12-102006-04-15 50* 6500 10.0.0.2 2006-04-012006-04-06 3** *So of the 5000 events count(*) between 2005-12-10 and 2006-04-14 the count was distributed within 50 distinct days. **This time the count is still high and it occured within 3 days between 2006-04-01 and 2006-04-06. I just cant figure out how to come up with the days part :). On 4/23/06, John Hicks [EMAIL PROTECTED] wrote: Paul Halliday wrote: I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Could you be a bit more specific? Where do you want to store the count? What do you mean by if there was data on this day? Does the design of the table anticipate no more than one record per day? Does each record correspond to a single day? --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If exists query.
I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if a trigger exists
Adrian Co wrote: Hi, Yes, the syntax is not valid. I have tried it. :-) I was hoping for maybe a workaround to simulate the described functionality. There might also be something wrong with the structure of my script. Maybe I shouldn't be creating triggers there? But I was thinking if the CREATE DATABASE and CREATE TABLE have a IF NOT EXISTS functionality, why should the triggers be any different? Maybe this is just an oversight, maybe the CREATE TRIGGER should have a IF NOT EXISTS functionality also? or I'm just doing something very wrong. Maybe someone can point me in the right direction? Hi Adrian you can check whether a trigger exists by querying the information schema database: http://dev.mysql.com/doc/refman/5.0/en/triggers-table.html Hope that helps. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if a trigger exists
Hi, Sorry if I wasn't very clear with my question. I was hoping to obtain the functionality such that I could do something similar to: CREATE TRIGGER IF NOT EXISTS Because I usually get a trigger already exists in my script. I might be missing something. The script basically does the basic things like create database if not exists, create table if not exists, etc. I was hoping the same thing for triggers maybe? Is this possible? Regards, Adrian Co Jim wrote: There is a TRIGGERS table in the information_schema Eg. select Trigger_Name from TRIGGERS where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK' -Original Message- From: Adrian Co [mailto:[EMAIL PROTECTED] Sent: Friday, 7 April 2006 1:54 PM To: mysql@lists.mysql.com Subject: Determining if a trigger exists Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if a trigger exists
Try the documentation. http://mysql.com/triggers sends you to http://dev.mysql.com/doc/refman/5.0/en/triggers.html from there you can click on CREATE TRIGGER SYNTAX to get to http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html which clearly shows that syntax is not valid. Alternatively, you could try it yourself and see that it fails. -Sheeri On 4/7/06, Adrian Co [EMAIL PROTECTED] wrote: Hi, Sorry if I wasn't very clear with my question. I was hoping to obtain the functionality such that I could do something similar to: CREATE TRIGGER IF NOT EXISTS Because I usually get a trigger already exists in my script. I might be missing something. The script basically does the basic things like create database if not exists, create table if not exists, etc. I was hoping the same thing for triggers maybe? Is this possible? Regards, Adrian Co Jim wrote: There is a TRIGGERS table in the information_schema Eg. select Trigger_Name from TRIGGERS where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK' -Original Message- From: Adrian Co [mailto:[EMAIL PROTECTED] Sent: Friday, 7 April 2006 1:54 PM To: mysql@lists.mysql.com Subject: Determining if a trigger exists Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if a trigger exists
Hi, Yes, the syntax is not valid. I have tried it. :-) I was hoping for maybe a workaround to simulate the described functionality. There might also be something wrong with the structure of my script. Maybe I shouldn't be creating triggers there? But I was thinking if the CREATE DATABASE and CREATE TABLE have a IF NOT EXISTS functionality, why should the triggers be any different? Maybe this is just an oversight, maybe the CREATE TRIGGER should have a IF NOT EXISTS functionality also? or I'm just doing something very wrong. Maybe someone can point me in the right direction? sheeri kritzer wrote: Try the documentation. http://mysql.com/triggers sends you to http://dev.mysql.com/doc/refman/5.0/en/triggers.html from there you can click on CREATE TRIGGER SYNTAX to get to http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html which clearly shows that syntax is not valid. Alternatively, you could try it yourself and see that it fails. -Sheeri On 4/7/06, Adrian Co [EMAIL PROTECTED] wrote: Hi, Sorry if I wasn't very clear with my question. I was hoping to obtain the functionality such that I could do something similar to: CREATE TRIGGER IF NOT EXISTS Because I usually get a trigger already exists in my script. I might be missing something. The script basically does the basic things like create database if not exists, create table if not exists, etc. I was hoping the same thing for triggers maybe? Is this possible? Regards, Adrian Co Jim wrote: There is a TRIGGERS table in the information_schema Eg. select Trigger_Name from TRIGGERS where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK' -Original Message- From: Adrian Co [mailto:[EMAIL PROTECTED] Sent: Friday, 7 April 2006 1:54 PM To: mysql@lists.mysql.com Subject: Determining if a trigger exists Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determining if a trigger exists
Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if a trigger exists
There is a TRIGGERS table in the information_schema Eg. select Trigger_Name from TRIGGERS where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK' -Original Message- From: Adrian Co [mailto:[EMAIL PROTECTED] Sent: Friday, 7 April 2006 1:54 PM To: mysql@lists.mysql.com Subject: Determining if a trigger exists Hi, Whats the simplest way to determine if a trigger already exists? i.e. For tables you have: CREATE TABLE IF NOT EXISTS ... Is there a way to do CREATE TRIGGER IF NOT EXISTS I'm using MySQL 5.0 btw. Thanks! Regards, Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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
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: To: mysql@lists.mysql.com From: Rithish Saralaya [EMAIL PROTECTED] 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: [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
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 -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
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: [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
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
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
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
Hi Rithish, After reading Heikkis points, I am inclined to agree. Did your sysadmins change a filesystem during the maintenance? Did they forget to restore a directory if they changed disks? What was the maintenance that was performed? Your InnoDB files disappeared at some point because the server would not have recreated them otherwise. I am sure it was a graceful shutdown, but something has changed. These things just don't happen by themselves. Regards Rithish Saralaya wrote: The mysql server was shut down for maintenance. However it was a graceful shutdown and restart. That's all. No files were touched or removed. How could this have happened, I fail to see. Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, February 24, 2006 3:55 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best
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. -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
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/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
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
Rithish, from the .err log we see that mysqld was shut down for 12 hours on Feb 19th. What did the sysadmins do during that time? There are two plausible explanations: 1) they edited datadir in my.cnf to point to a different location ( /var/lib/mysql), or 2) they removed ibdata1 and ib_logfiles from the the datadir. That caused InnoDB to recreate these files. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Rithish Saralaya [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 24, 2006 6:55 AM Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space: 102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY
RE: error 1016 : cant open ibd file even though it exists
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
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
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
Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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
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
RE: error 1016 : cant open ibd file even though it exists
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 [EMAIL PROTECTED] Subject: RE: error 1016 : cant open ibd file even though it exists Hello. The tables were working perfectly fine a week back. The database was created from a sql file generated through the mysqldump utility. So there was nothing wrong with the database. This irregularity happened this week onwards. Our system admins tell us that the server was restarted last weekend. When I dug up the mysql error logs, this was what I found saw. == 060219 5:20:25 InnoDB: Starting shutdown... 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 1867461149 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete Memory status: Non-mmapped space allocated from system: 16589028 Number of free chunks: 10 Number of fastbin blocks: 0 Number of mmapped regions: 19 Space in mmapped regions: 1472028672 Maximum total allocated space: 0 Space available in freed fastbin blocks: 0 Total allocated space: 16479548 Total free space: 109480 Top-most, releasable space:102224 Estimated memory (with thread stack):1488744676 060219 05:20:30 mysqld ended 060219 16:57:48 mysqld started 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist: InnoDB: a new database to be created! 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 060219 16:58:28 InnoDB: Started; log sequence number 0 0 /usr/sbin/mysqld: ready for connections. Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) = So... It shows that the ibdata1 file was recreated... But how can that be possible? when it was a regular server shutdown and startup? Regards, Rithish. -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 7:52 PM To: mysql@lists.mysql.com Subject: Re: error 1016 : cant open ibd file even though it exists Rithish, the table definition does not exist in the ibdata file. You have the .frm file and the .ibd file, but that does not help if the table definition is not stored in the ibdata file. How did you end up in this situation? Did you move .frm and .ibd files around? Did you recreate the ibdata1 file? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . List: mysql Subject:error 1016 : cant open ibd file even though it exists From: Rithish Saralaya rithish.saralaya () tallysolutions ! com Date: 2006-02-22 11:27:44 Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () tallysolutions ! com [Download message RAW] Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot
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.
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]
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.
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]
(mysqldump) CREATE TABLE IF NOT EXISTS. . .
Hi All, Having a bit of mysqldump trouble again. I've looked over the documentation (again) and can't seem to find the flag to make 'mysqldump' out put CREATE TABLE IF NOT EXISTS. Any ideas? Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) CREATE TABLE IF NOT EXISTS. . .
DROP TABLE IF NOT EXISTS? On 1/16/06, Michael Williams [EMAIL PROTECTED] wrote: Hi All, Having a bit of mysqldump trouble again. I've looked over the documentation (again) and can't seem to find the flag to make 'mysqldump' out put CREATE TABLE IF NOT EXISTS. Any ideas? Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (mysqldump) CREATE TABLE IF NOT EXISTS. . .
I appreciate the suggestion, but I'm not looking to drop anything. I only want it as a safety net in the event that the table doesn't already exist. I'm doing syncing of sorts, and I want the CREATE TABLE IF NOT EXISTS so as not to throw errors in the event that the table already exists, and to add if it's the first time a particular DB is syncing, etc. I never want to drop. Regards, Michael On Jan 16, 2006, at 6:22 PM, Jake Peavy wrote: DROP TABLE IF NOT EXISTS? On 1/16/06, Michael Williams [EMAIL PROTECTED] wrote: Hi All, Having a bit of mysqldump trouble again. I've looked over the documentation (again) and can't seem to find the flag to make 'mysqldump' out put CREATE TABLE IF NOT EXISTS. Any ideas? Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED]
Record exists but not found - grrr
Hi all, I've got a problem that I don't understand and that is driving me mad. I have a table 'tab_p' with this structure: nametype --- p_idmediumint(8) AUTOINCREMENT NOT NULL PRIMARY KEY d_idsmallint(5) NOT NULL namevarchar(50) NOT NULL And unique index on (d_id, name). ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_slovak_ci or utf8_czech_ci And there is a row with (for example) these data: p_idd_idname 953 20602 Machico When I try query: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico' - no results So I try to insert the row: INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico') - Error: #1062 - Duplicate entry '20602-Machico' for key 2 Why!!!??? There are thousands of records in the table an they work fine but then there occures some normal word (even with no special characters) and it behaves like this. Can anyone help me? I'm running MySQL 4.1.11 on Linux. Thanks Wenca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record exists but not found - grrr
Wenca wrote: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico' - no results Try it with ...AND `name` LIKE '%Machico%'; I have encountered it once when there were non printable characters in front of or after the text itself. Very frustrating! You can see what extra characters are there when you query SELECT *, HEX(`name`) FROM... This will add a column to the output with the hexadecimal representation of each character in the name. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record exists but not found - grrr
Hi Paul, I'm sure there are no spaces or other blank characters. I run exactly this query (I deleted the row and tried again): INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico'); INSERT INTO tab_p (d_id, name) VALUES (20602, 'Funchal'); SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico'; - no results SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Funchal'; - OK 1 row SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE '%Machico'; - OK 1 row I really don't understand it. I have this problem with about 3 or 4 words within nearly 5000 that I currently have in the table. Wenca Paul Rhodes wrote: Hi Wenca, Is it possible that you may a space at the beginning or end of the string. Try this to determine whether this is the case: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE '%Machico%' If this works, try deleting and re-inserting the row. I've had a similar situation in the past. Hope this helps, Paul -Original Message- From: Wenca [mailto:[EMAIL PROTECTED] Sent: 27 October 2005 10:56 To: mysql@lists.mysql.com Subject: Record exists but not found - grrr Hi all, I've got a problem that I don't understand and that is driving me mad. I have a table 'tab_p' with this structure: nametype --- p_idmediumint(8) AUTOINCREMENT NOT NULL PRIMARY KEY d_idsmallint(5) NOT NULL namevarchar(50) NOT NULL And unique index on (d_id, name). ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_slovak_ci or utf8_czech_ci And there is a row with (for example) these data: p_idd_idname 953 20602 Machico When I try query: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico' - no results So I try to insert the row: INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico') - Error: #1062 - Duplicate entry '20602-Machico' for key 2 Why!!!??? There are thousands of records in the table an they work fine but then there occures some normal word (even with no special characters) and it behaves like this. Can anyone help me? I'm running MySQL 4.1.11 on Linux. Thanks Wenca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Detect if table exists from within MySQL?
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
SELECT notifyto FROM cfgbiz
ELSE
SELECT '' as notifyto
Is there something similar in MySQL? I am running version 4.1.x.
Thanks,
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Detect if table exists from within MySQL?
Ryan,
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the table
doesn't exist.
PB
Ryan Stille wrote:
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
SELECT notifyto FROM cfgbiz
ELSE
SELECT '' as notifyto
Is there something similar in MySQL? I am running version 4.1.x.
Thanks,
-Ryan
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.11/121 - Release Date: 10/6/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Detect if table exists from within MySQL?
If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the table doesn't exist. This causes my application (ColdFusion) to throw an exception. If I have to, I could resort to doing another query in my application (SHOW TABLES) and seeing if my table was returned in that list. But I was hoping for a more elegant way to do it, within the single query. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Detect if table exists from within MySQL?
Ryan Stille wrote: If I have to, I could resort to doing another query in my application (SHOW TABLES) and seeing if my table was returned in that list. But I was hoping for a more elegant way to do it, within the single query. Maybe you could use SHOW TABLES LIKE 'your_table'; -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Detect if table exists from within MySQL?
On Thursday 06 October 2005 10:57 am, Ryan Stille wrote:
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
SELECT notifyto FROM cfgbiz
ELSE
SELECT '' as notifyto
Is there something similar in MySQL? I am running version 4.1.x.
5.X has information_schema..
However...
4.1, you can show tables like '%tab%' and you will get a row back if exists..
WARNING, myisam will be quick, innodb will be really slow, specially if the
tables are big..
Jeff
pgphkfOW9sFUY.pgp
Description: PGP signature
RE: Detect if table exists from within MySQL?
Maybe you could use SHOW TABLES LIKE 'your_table'; That's a great idea, I just tried it in several ways, like: IF EXISTS (SHOW TABLES LIKE 'cfgbiz') THEN SELECT siacnotifyto FROM cfgbiz ELSE SELECT '' as siacnotifyto END IF; -and- select IF((SHOW TABLES LIKE 'cfgbiz'),notifyto,'') FROM cfgbiz; But it looks like the SHOW TABLES statement just doesn't return like a regular SELECT statement does, because the above works if I use it like this: select IF(1,notifyto,'') FROM cfgbiz; -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Hello. You can parse the output of 'SHOW CREATE TABLE' or 'SHOW COLUMNS'. See: http://dev.mysql.com/doc/mysql/en/show-columns.html http://dev.mysql.com/doc/mysql/en/show-create-table.html Claire Lee wrote: I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exists BUG in IN ?
Hello Guys, I'm using the 5.0.12 version of MySQL PRO on AMD64 3000+ with 1 GB of ram and using the InnoDB Tables.. Here is my table: CREATE TABLE `svcs_filecontrol` ( `fc_id` int(10) unsigned NOT NULL auto_increment, `fc_us_id_lockby` int(10) unsigned default NULL, `fc_lbl_id` int(10) unsigned NOT NULL default '0', `fc_nome` varchar(255) NOT NULL default '', `fc_package` text NOT NULL, `fc_arquivo` longblob NOT NULL, `fc_versao` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`fc_id`), KEY `fc_us_id_lockby` (`fc_us_id_lockby`), KEY `fc_lbl_id` (`fc_lbl_id`), KEY `fc_nome` (`fc_nome`), KEY `fc_lbl_nome_pacote_versao` (`fc_lbl_id`,`fc_nome`,`fc_package`(500),`fc_versao`), KEY `fc_versao` (`fc_versao`), KEY `fc_pacote_nome` (`fc_package`(255),`fc_nome`), CONSTRAINT `svcs_filecontrol_ibfk_1` FOREIGN KEY (`fc_lbl_id`) REFERENCES `svcs_label` (`lbl_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql select count(*) from svcs_filecontrol; +--+ | count(*) | +--+ | 1147 | +--+ 1 row in set (0.35 sec) mysql Here is the Select: mysql select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in ( - *select max(f2.fc_id) from svcs_filecontrol f2 - where f2.fc_lbl_Id = 1 - group by f2.fc_package - order by f2.fc_versao desc*) - ; ++ | fc_package | ++ | br.com.escriba | | br.com.escriba.compartilhado.ejb | | br.com.escriba.compartilhado.ejb.interfaces| | br.com.escriba.components.actions | | br.com.escriba.components.config | | br.com.escriba.components.editor | | br.com.escriba.components.framework| | br.com.escriba.components.imageviewer | | br.com.escriba.components.interfacereport | | br.com.escriba.components.pesquisa | | br.com.escriba.components.table| | br.com.escriba.components.wordprocessor| | br.com.escriba.components.wordprocessor.multipage | | br.com.escriba.components.wordprocessor.rtf| | br.com.escriba.components.wordprocessor.rtf.app| | br.com.escriba.components.wordprocessor.rtf.reader | | br.com.escriba.components.wordprocessor.rtf.view | | br.com.escriba.components.wordprocessor.rtf.writer | | br.com.escriba.images.components | | br.com.escriba.testes | | br.com.escriba.util| | br.com.escriba.util.xml| | org.syntax.jedit | | org.syntax.jedit.tokenmarker | | br.com.escriba.compartilhado | | tests | | tests.compartilhado| | tests.compartilhado.ejb| | br.com.escriba.components | ++ 29 rows in set (7.77 sec) mysql The problem is ... 7.77 sec to a simple select :( , is a bug ? because if i run the SUB-SELECT separated of query , this run faster... look: mysql *select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f2.fc_package order by f2.fc_versao desc;* +---+ | max(f2.fc_id) | +---+ | 243 | | 2 | | 235 | . +---+ 29 rows in set (0.00 sec) mysql The DESC of this command is: mysql desc select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in (select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f 2.fc_package order by f2.fc_versao desc); +++---+--+-+---+-+---+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---+--+-+---+-+---+--+--+ | 1 | PRIMARY| f1| ALL | NULL| NULL | NULL| NULL | 2440 | Using where | | 2 | DEPENDENT SUBQUERY | f2| ref | fc_lbl_id,fc_lbl_nome_pacote_versao | fc_lbl_id | 4 | const | 435 | Using where; Using temporary; Using filesort | +++---+--+-+---+-+---+--+--+ 2 rows in set (0.00 sec) mysql Is a bug ? Tnks in advance... MySQL,InnoDB,hlppp --
add a column if not exists
I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exists BUG in IN ?
Dyego Souza Dantas Leal wrote: Hello Guys, I'm using the 5.0.12 version of MySQL PRO on AMD64 3000+ with 1 GB of ram and using the InnoDB Tables.. snip Here is the Select: mysql select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in ( - *select max(f2.fc_id) from svcs_filecontrol f2 - where f2.fc_lbl_Id = 1 - group by f2.fc_package - order by f2.fc_versao desc*) - ; I assume you added the '*'s for emphasis? I fail to see how ORDER BY in the _subquery_ helps you here. In fact, I think it slows you down, with no effect on the output. Shouldn't this be ORDER BY f1.fc_versao DESC, in the main query? snip The problem is ... 7.77 sec to a simple select :( , is a bug ? because Not so simple, as it turns out. if i run the SUB-SELECT separated of query , this run faster... look: mysql *select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f2.fc_package order by f2.fc_versao desc;* +---+ | max(f2.fc_id) | +---+ | 243 | | 2 | | 235 | . +---+ 29 rows in set (0.00 sec) The DESC of this command is: mysql desc select f1.fc_package from svcs_filecontrol f1 where f1.fc_id in (select max(f2.fc_id) from svcs_filecontrol f2 where f2.fc_lbl_Id = 1 group by f 2.fc_package order by f2.fc_versao desc); +++---+--+-+---+-+---+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | Extra| +++---+--+-+---+-+---+--+--+ | 1 | PRIMARY| f1| ALL | NULL| NULL | NULL| NULL | 2440 | Using where | | 2 | DEPENDENT SUBQUERY | f2| ref | fc_lbl_id,fc_lbl_nome_pacote_versao | fc_lbl_id | 4 | const | 435 | Using where; Using temporary; Using filesort | +++---+--+-+---+-+---+--+--+ 2 rows in set (0.00 sec) mysql Is a bug ? No (not exactly), it's a missing feature. The problem is that mysql thinks you have a dependent subquery, so your subquery is being run once for each row of your table. Ideally, I suppose the optimizer should notice that it can run the subquery once, then match rows against the IN list using the index, but it doesn't. Subqueries are relatively new in mysql. They work, but they often are not optimized well. Fixing that is on the TO-DO list, but seems to be a low priority. Joins, on the other hand, have been around a long time and are well-optimized. This seems to be a version of the groupwise-maximum problem http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html. If I understand your query, I think it's equivalent to SELECT f1.fc_package from svcs_filecontrol f1 WHERE f1.fc_id = ( SELECT max(f2.fc_id) FROM svcs_filecontrol f2 WHERE f1.fc_package = f2.fc_package AND f2.fc_lbl_Id = 1) ORDER BY f1.fc_versao DESC; which is how the manual solves this problem. I think that will still be a dependent subquery, though, so I'm not sure it will be any faster. A better bet is probably to do this in two steps. Get the ids using the inner query and store them in a temporary table, then join to the temporary table to get the rows you want. Something like: CREATE TEMPORARY TABLE max_ids SELECT MAX(fc_id) AS max_id FROM svcs_filecontrol WHERE fc_lbl_Id = 1 GROUP BY fc_package; SELECT f.fc_package FROM svcs_filecontrol f JOIN max_ids m ON f.fc_id = m.max_id ORDER BY f.fc_versao DESC; DROP TABLE max_ids; That may look like a pain, but it should certainly be fast. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Claire, I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. If you are using MySQL 5.0, query information_schema.columns (http://dev.mysql.com/doc/mysql/en/columns-table.html) for the table and column. Otherwise use SHOW COLUMNS FROM tablename. PB http://www.artfulsoftware.com - Claire Lee wrote: I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. Claire __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: add a column if not exists
Hi, 2005/9/26, Peter Brawley [EMAIL PROTECTED]: Claire, I want to check if a column exists in a table before I do an alter table to add it. How do I do this in mysql? Thanks. other solution, do your query in all case and check for the return error (if any). mysql alter table board add message varchar(255) not null default ''; ERROR 1060 (42S21): Duplicate column name 'message' So, if you get back that error, the column already exists. So if it exists the table is left untouched, otherwise it does what you want. (yeah, Information_schema would be better, but 5.0 is in gamma) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXISTS subquery optimization
Hi, Now as expected it's an dependent subquery and makes use of the index on document_id. BUT: If we change the SELECT id in the subquery to SELECT document_id or SELECT 1, we get: *** 1. row *** [...] *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: file type: ref possible_keys: document_id key: document_id key_len: 2 ref: djbdms.t1.id rows: 1 Extra: Using index Note the Using index! I played around with it and it really makes a huge performance difference - in my case the file table contains a lot of large blobs, is fragmented and I'm running weak hardware. Seems as if using index is not just (irrelevant or wrong) EXPLAIN output but is what is really going on? From the manual : - Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. So you should save an extra seek from the disk I guess. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EXISTS subquery optimization
Hi all, the manual says: If a subquery returns any rows at all, then EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. ... Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it doesn't matter. [http://dev.mysql.com/doc/mysql/en/exists-and-not-exists-subqueries.html ] However, please consider the following query and note that there's an index on file.document_id. EXPLAIN SELECT t1.id, t1.name, IF ( EXISTS ( ---,, NOTE that! SELECT id FROM file WHERE document_id = t1.id ), file_exists, no file ) FROM document AS t1 *** 1. row *** [...] *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: file type: ref possible_keys: document_id key: document_id key_len: 2 ref: djbdms.t1.id rows: 1 Extra: Now as expected it's an dependent subquery and makes use of the index on document_id. BUT: If we change the SELECT id in the subquery to SELECT document_id or SELECT 1, we get: *** 1. row *** [...] *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: file type: ref possible_keys: document_id key: document_id key_len: 2 ref: djbdms.t1.id rows: 1 Extra: Using index Note the Using index! I played around with it and it really makes a huge performance difference - in my case the file table contains a lot of large blobs, is fragmented and I'm running weak hardware. Seems as if using index is not just (irrelevant or wrong) EXPLAIN output but is what is really going on? Am I mistaking something, or is this an already-known bug? I did not find anything matching in the bug database. Probably not too hard to fix (whenever you're in an EXISTS subquery, just forget the select_expr), but maybe even worth a note on the manual page until it's fixed? Best regards, Matthias PS. Server version: 4.1.11-Debian_4sarge1 mysql Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i386) using readline 4.2a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
