Hi! If you answer to this post, please removed the [EMAIL PROTECTED] from the CC: as this isn't a full repeatable bug report. >>>>> "Jamie" == Jamie Mason <[EMAIL PROTECTED]> writes: Jamie> I've been experiencing a simmilar problem only with MyISAM tables. Jamie> This is under 3.23.35 and 3.23.36 on Sparc/Solaris 2.7. Jamie> For me the problem occurs when doing an ALTER TABLE or a simmilar Jamie> operation (eg OPTIMIZE) on relatively large tables (>4 millon rows) Jamie> with one or two indeces. Could you try to get a 'mysqladmin processlist' next time this happens. It would be interesting to see if there is any similarities with your case compared to Peters. Jamie> The table in question is occasionally accessed by other queries; Jamie> these access could occur during the ALTER TABLE. In this case, Jamie> the application running the other queries would not mind seeing the Jamie> old structure (for read) or wating until the ALTER is complete Jamie> (for write). During ALTER TABLE MySQL should allow access to old data. During OPTIMIZE TABLE and REPAIR TABLE MySQL should lock this table and only this table from future access until teh command completes. I did some quick tests of this on a large table using several 'mysql' command-line sessions but wasn't able to repeat this. I also modified fork2_test.pl to do a lot of ALTER TABLE but this didn't help either :( Jamie> The symptom is the same as described below; with ALL threads Jamie> (not just those touching the ALTERed table) getting stuck in Jamie> "opening tables" or "closing tables". This should of course not happen. The only time MySQL should be stuck in 'opening tables' in this context is during the time when alter table is ready has copied all data to the temporary table and wants to drop the original table and replace it with the temporary table. This *should* take a VERY short time. If one did a 'FLUSH TABLES' while the ALTER TABLE is running, then all other threads that wants to access the same table will be waiting with status 'opening tables' until the alter table and flush table is ready. Jason, could you verify that you are not duing 'FLUSH TABLES' at the same time as you do ALTER TABLE ? I don't think this is a general bug in ALTER TABLE, but rather something that is triggered by some combination of events. If we could figure out the combination of events, it shouldn't be that hard to create a test case for this. Jamie> To recover it is necessary to terminate mysqld a SIGKILL; Jamie> though data loss can usually be avoided by doing a Jamie> FLUSH TABLES first (which also hangs) and also sending Jamie> a SIGTERM and waiting a minute before sending the KILL. Jamie> Would upgrading to 3.23.39 fix this problem? Or is it Jamie> still a live bug in the current version? I am not aware of this bug in any version of MySQL, so I can't say if one upgrade would help. At least, an upgrade wouldn't hurt but.. Jamie or Peter; Could you spend a couple of minutes to change the tests/fork_big.pl test to simulate your application where this happens in an attempt to repeat this problem? If someone could create a test program where this happens repeatable, it will be trivial to find and fix this problem. I have myself modified fork2_test.pl to do an ALTER TABLE every 10 seconds, but couldn't get MySQL to hang during this. I have included the modified fork2_test.pl below, but as the MySQL mailing list handler strips of attachments, only Jason, Heikki and Peter will get this.
Regards, Monty
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
