| Bug #82055 | Slow Query Log Lock_time=500,000 Years | ||
|---|---|---|---|
| Submitted: | 29 Jun 2016 15:02 | Modified: | 29 Jul 2016 22:19 |
| Reporter: | Jerry Riggin | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Logging | Severity: | S3 (Non-critical) |
| Version: | 5.1.46-log (SUSE MySQL RPM) | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Lock_time, locking, logging, slow query | ||
[29 Jun 2016 22:19]
MySQL Verification Team
Please try new version: https://www.mysql.com/support/eol-notice.html December 4, 2013 MySQL 5.1 covered under Oracle Lifetime Sustaining Support Per Oracle's Lifetime Support policy, as of December 31, 2013, MySQL 5.1 is covered under Oracle Sustaining Support. Users are encouraged to upgrade to MySQL 5.6.
[30 Jul 2016 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Slow query log reports some queries take over 500,000 year to get a lock. These are ISAM tables. Log snippet: /usr/sbin/mysqld, Version: 5.1.46-log (SUSE MySQL RPM). started with: Tcp port: 3306 Unix socket: /var/run/mysql/mysql.sock Time Id Command Argument # Time: 160627 9:10:05 # User@Host: sysop[sysop] @ [127.0.0.1] # Query_time: 3.768728 Lock_time: 0.034402 Rows_sent: 734 Rows_examined: 734 use asterisk; SET timestamp=1467033005; select PostID, lead_id, list_id from vdlist_temp where Posted is null; # Time: 160627 10:35:11 # User@Host: sysop[sysop] @ [192.168.0.248] # Query_time: 35.563521 Lock_time: 0.000054 Rows_sent: 1222017 Rows_examined: 2444034 SET timestamp=1467038111; SELECT `vicidial_list`.`lead_id`, `vicidial_list`.`source_id` FROM `vicidial_list` ORDER BY `vicidial_list`.`source_id`; # User@Host: sysop[sysop] @ [127.0.0.1] # Query_time: 0.000095 Lock_time: 18446744073699.406250 Rows_sent: 2 Rows_examined: 1 SET timestamp=1467038111; call spUpdate_VDList_from_temp(0, 0, 1324903); How to repeat: I have not found a way to force the error or isolate the issue. It seems to occur randomly on queries and stored procedures that are often slow but are also reported with sane Lock_times. I only know the most significant digits of the insane Lock_times do not change. 184467440736XX.XXXXXX which means the query has been waiting for a lock since the mid Paleolithic period. Suggested fix: I have no clue