Page 1 of 1

MySQL InnoDB foriegn key problems

Posted: Tue Aug 25, 2009 9:12 am
by N1gel
I know this is probably more a MySQL question but thought I would put it here as i'm using PHP and I don't know if anyone else has seen this or knows what it might be.

Esentially I have a Simple page that displays peoples names. For that I use a query like

Code: Select all

SELECT * FROM `PERSON`
Then I have the option to add new people. I do this through a pop-up form done with AJAX. The AJAX pop-up does a simple

Code: Select all

INSERT INTO `PERSON` (`ID`,`NAME`) (1, 'Nigel')
The Insert query will be successfull however when I reload the Select Query it won't show the new record.

I think the problem lies in the Select and Insert queries use different MySQL Connections. Because if immediatley after the Insert I was to do a select using that MySQL connection it would show the new record.

On looking into the MySQL Error Log I find Statments like the following.
090825 14:19:02 InnoDB: Warning: MySQL is trying to drop table `hub/#sql2-42c-26`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
090825 14:40:25 InnoDB: Dropped table hub/#sql2-42c-26 in background drop queue.
And once the temp table has been dropped I am able to see the new record but as you can see from the time codes that takes 20 minutes in this example.

Has anybody got any ideas on how I can trace down why MySQL is unable to drop this temp table? How can I trace what is using it?

Any thoughts are welcome. Thanks

*
I forgot to mention I think this might be due to the PERSON being a foriegn key in a different table beacuse I have also seen the following error message in my sql Error Log
090825 12:30:06 InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `hub/#sql2-42c-8` exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying rename table `hub/person` to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/ ... oting.html
InnoDB: If table `hub/#sql2-42c-8` is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

Re: MySQL InnoDB foriegn key problems

Posted: Tue Aug 25, 2009 9:33 am
by Ambush Commander
I think we might be missing a little context here. Why are you using temporary tables?

Re: MySQL InnoDB foriegn key problems

Posted: Tue Aug 25, 2009 9:38 am
by N1gel
I'm not. I'm just executing the two simple SQL commands.

The tables are InnoDB and there are several Foriegn Keys linking to the PERSON table. From what I can tell from the MySQL error logs the temp tables are created by MySQL in the process of updating the person table.