Teemu, the standard Perl stress test
perl run-all-tests --create-options=type=innodb in the sql-bench directory does a lot of ADD COLUMN operations. But it does not produce the warning you have seen. How big are the tables you ALTER? What does a typical CREATE TABLE look like? It would be valuable if you can write a script which repeats the warning. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "Teemu Kuulasmaa" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, September 05, 2003 12:53 AM Subject: Re: MySQL does not release locks > Heikki, > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
