There is not concunrrently running ANY queries on the table I am ALTERing. In some cases I am the only person connected to the server. Unfortunately I am not able to provide you with the proper test case because this problem is not repeatable. Yes, I encounter this problem very frequently but sometimes I am geting error messages and sometimes I am not.
There is two kind of queries that cause my problems:
1) I add new columns to the table "ALTER TABLE tblName ADD COLUMN ... "
2) I make updates "UPDATE TABLE tblName SET field1='something' WHERE field2=...."
These queries are executed succesfully but following queries are blocked. Maybe UPDATE/ALTER queries are still hanging around and database engine thinks that operation has not been completed!? ( I do not know database engine internals ;=) This was a wild guess).
I am looking forward your reply,
Teemy
Teemu,
what kind of queries, if any, you are concurrently running on the tables you are ALTERing?
" 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. "
This warning means that there may still be queries running on the table MySQL is trying to drop. InnoDB must delay the actual drop operation. It would be valuable if you could provide a repeatable test case which generates this warning. This should have been fixed in some MySQL-4.0.xx, but the bug seems to persist.
Best regards,
Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html
............... Subject: Re: MySQL does not release locks From: Teemu Kuulasmaa Date: Thu, 04 Sep 2003 14:54:56 +0300
Hi,
Unfortunately, nobody responded my mail. I am still trying to solve the problem I described in my earlier mail (quoted below).
I am mailing again because I have some new information about the issue. I converted all my tables to InnoDB (MyISAM -> InnoDB) and this solved the problem. With InnoDB tables I have not encountered any table locks BUT database engine is continuously reporting a new kind of warnings. A section from the server ".err" log file: <----------------------------------------------------------------> 030904 10:22:14 InnoDB: Warning: MySQL is trying to drop table research/#sql2-4a4-1a8 InnoDB: though there are still open handles to it. InnoDB: Adding the table to the background drop queue. 030904 10:32:54 InnoDB: Dropped table research/#sql2-4a4-1a8 in background drop queue. <---------------------------------------------------------------->
MySQL database engine is still behaving badly but InnoDB engine is able to overcome or prevent this.
Has anyone any ideas?!?! Maybe Heikki Tuuri could explain behavior of the InnoDB engine.
Sincerely,
Teemu
Teemu wrote:Hi
I have problems with mysql (4.0.6-4.0.14b) on windows 2000 SP3. MySQL locks tables when I alter table structure or execute update queries. I know that this is the exactly what database engine is supposed to do but the engine doesn't release the locks at all. This happens frequently but not allways. Approximately every third alter/update query locks table "permanently". Recently I found out that by executing "FLUSH TABLES" release locks and I am able to keep on working with the table. I have been useing only MyISAM table types.
Table locking is annoying because there might be concurrent users useing the same table. They are not able to access the table at all or SELECT queries returns wrong number of records.
I use different clients to execute queries phpMyAdmin, mysqlcc, mysql, MSAccess (ODBC). Table locking occurs independently of client used. For example phpMyAdmin sometimes shows following error message when I try to alter structure of locked table:
ERROR 7: Error on rename of '.\front\industry.MYI to '.\front\#sql-a64-439.MYI' (ERROR: 13)
I checked error code 13: Permission denied. But there shouldn't be permission problems because I use account having all privileges and only some of my queries cause these king of errors.
I am not alone with this kind of problem because there is a lot of reports in various mailing lists. I searched from web and news groups but nobody knows how to overcome the issue. It might be that the problem is win32 specific.
I would be more than thankfull if someone knows reason for table locking.
Sincerely,
Teemu
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
