| Bug #79963 | myql 5.6.27 table locking / timing issue bug | ||
|---|---|---|---|
| Submitted: | 13 Jan 2016 21:00 | Modified: | 19 Jul 2018 14:38 |
| Reporter: | David Usher | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S3 (Non-critical) |
| Version: | 5.6.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | lock release, timing | ||
[14 Jan 2016 16:06]
David Usher
Additional information: 1. This bug applies only to executes that do not result in data being read back such as inserts, updates, and other executed maintenance commands. Selects and other commands that result in a data response do not appear to be impacted by this timing bug. 2. When the perl app is hung waiting for mysql to do an UPDATE/INSERT or other command, Heidi shows a wait lock. The lock does not really exist -- I can take the query that is waiting to process and run it in Heidi while Heidi is showing the wait lock.
[14 Jan 2016 16:08]
David Usher
More information: Both perl DO and prepare/execute/finish commands result in this timing issue.
[19 Jul 2018 12:41]
MySQL Verification Team
Hi, Before going any further ... First of all, and least important, I do hope you are not running Perl programs on the same machine where you run MySQL, as Perl is taking lot's of CPU resources, particularly in the garbage collection. Second, using FLUSH and OPTIMIZE commands on InnoDB is totally unnecessary. InnoDB practically does very, very little with both commands, while you raise a MDL without any need. Third, FLUSH should be used with MyISAM only during backups. OPTIMIZE on MyISAM should be used couple of times per annum. Hence, I have no idea why are you introducing MDL without any need. Table(s) will be locked without any need. And those locked will not lifted until the operation finishes, regardless if you start one or thousand new queries on the same table !!!!!
[19 Jul 2018 14:29]
David Usher
1. We had separate app and db servers. 2. InnoDb was even worse the MyIsam. It locked up constantly. I reported that and somebody in Oracle said that Innodb was new and that it might not work as well. Our db experts here ageed, so I went back to MyIsam. 3. The problems began with a mysql update in Aug. 2016. The problem was not just the Optimize/Flush commands. All Mysql table functions resulted in mysql locking up. The app could not talk through the mysql->Perl->Linux->IP layers to the db server end stack of layers IP->Linux->Mysql. 4. The Mysql update was definitely buggy. Everything worked fine until we updated mysql in Aug 2016. 5. I am 100% sure the problem was on the db server end because using Heidi to do any kind of table commands resulted in a lockup. 6. In the end, we gave up and shut down the server and replace the app too. 7. Thanks to the buggy Mysql update, my killer app that was in production for 17 years was replaced. 8. Your support is useless. This post was made in Jan 2016 and you did not reply until July 2018 (2.5 years later). I am not impressed with Oracle. You can tell your boss Larry Ellison I said so. I stayed in a house on the beach in Laguna Beach a few houses down from where he lives. Perhaps I should have dropped by for support there?
[19 Jul 2018 14:38]
MySQL Verification Team
Hi, I think that you have misunderstood certain basic facts. This is not a forum for free support. This is a forum for bug with fully repeatable test cases. FULLY repeatable test cases. I do not see one here in your report. We do not provide free support. If you are looking for the free support, there are MySQL groups on Facebook, Twitter ........
Description: 1. running Linux red hat centos VM db server with nothing else on it. 2. Using Perl DBI: Queries work fine for a couple of days after server restart. After that, table releases take a long time and mysql does not actually wait for the release before executing the next query. 3. A couple of days after server restart, I see table locks that are not released before the next query is executed. This impacts both MyISAM and INNODB engines. --Query 1 is executed and finished. --Query 2 runs but mysql shows the query is waiting for a table lock. The first lock never releases and the process and app server script is hung. If I put a perl sleep(1) after query 1 to give mysql time to finish the first query and clear the lock, it works. If I remove the sleep, it locks up. It appears that mysql assumes that the finish has cleared the table lock and there is no signalling to make perl actually wait for the finish to completely execute. Here is an example of a clean run against a table that has not yet been accessed, with no processes running in mysql at runtime: This hangs forever: $query = "FLUSH TABLE `RouterAttributes` "; $sti = $dbh->prepare("$query"); $sti->execute();$sti->finish; $query = "OPTIMIZE TABLE `RouterAttributes` "; $sti = $dbh->prepare("$query"); $sti->execute();$sti->finish; This works: $query = "FLUSH TABLE `RouterAttributes` "; $sti = $dbh->prepare("$query"); $sti->execute();$sti->finish; sleep(1); $query = "OPTIMIZE TABLE `RouterAttributes` "; $sti = $dbh->prepare("$query"); $sti->execute();$sti->finish; I have tested this against both innodb and myisam engines. It is possible this relates only to commands that do not result in a response that can be read back that execute very quickly. How to repeat: Not sure how to duplicate on another machine. We have 4 cpu and 32gb memory with little traffic, latest version of Centos. This was working fine on a previous version of mysql, so I think this is a new bug. What is interesting is that everything works fine for a day or so. It appears that when it takes too long for the table lock to release, the next query puts another lock on the table, and then the first lock cannot be released, which keeps the second query from executing forever. However, when a script is locked up on a query, I can execute a command manually using Heidi. So it appears that when the second query above is executed and is waiting for a lock, it does not recheck to see if the first lock quit. In Heidi, mysql reports that the first lock exists, but i can manually run a query in Heidi and it executes. Suggested fix: Fixing table locking timing/reporting/verification issues.