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]
