| Bug #29821 | MySQL hangs forever while "sending data" | ||
|---|---|---|---|
| Submitted: | 16 Jul 2007 13:25 | Modified: | 30 Oct 2012 17:59 |
| Reporter: | Vadim Smelyansky | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.45, 5.1.41, 5.0.92, 5.1.57, 5.6.3 | OS: | Linux (CentOS5 x86_64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[16 Jul 2007 17:31]
MySQL Verification Team
Thank you for the bug report. Could you please provide the dump file to populate the tables?. Thanks in advance.
[17 Jul 2007 7:44]
Vadim Smelyansky
Unfortunately I can not provide database content. After playing with MySQL settings a little I got this query working - now it takes about 50min current /etc/my.cnf following: ------------------------------------------------------------- [mysqld] datadir = /opt/mdb/mysql max_connections=250 connect_timeout=30 key_buffer = 128M max_allowed_packet = 64M table_cache = 400 sort_buffer_size = 4M tmp_table_size = 32M read_buffer_size = 4M read_rnd_buffer_size=4M myisam_sort_buffer_size = 64M open-files-limit=8192 net_buffer_length=4K net_read_timeout=300 net_write_timeout=600 table_lock_wait_timeout=500 thread_cache = 16 query_cache_size = 1M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 big-tables innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=16M innodb_log_file_size=5242880 innodb_buffer_pool_size=1024M default-table-type=InnoDB # # Replication settings # server-id = 1275659283 log-bin = /var/log/mysql/bin.log log-bin-index = /var/log/mysql/log-bin.index log-error = /var/log/mysql/error.log log-slow-queries=/var/log/mysql/slow-queries.log long_query_time=30 log_queries_not_using_indexes=1 log_warning=2 relay-log = /var/log/mysql/relay.log relay-log-info-file = /var/log/mysql/relay-log.info relay-log-index = /var/log/mysql/relay-log.index expire_logs_days = 7 #Use compression on master/slave protocol. slave_compressed_protocol
[17 Jul 2007 9:09]
Valeriy Kravchuk
Please, send the EXPLAIN results for the query and SHOW TABLE STATUS results for all the tables used.
[17 Jul 2007 11:13]
Vadim Smelyansky
I did another experiment and copy database to next door server where version 5.0.41 installed - query took 16min which is big difference from 58min on 5.0.45
below is explain for this query:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages_free
type: index
possible_keys: NULL
key: randomized
key_len: 9
ref: NULL
rows: 1438285
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages_free.adv_page_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: adv_areas
type: ref
possible_keys: uniq_area_id,promoted_type
key: uniq_area_id
key_len: 4
ref: lm_new.adv_pages.adv_page_id
rows: 4
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: adv_hosts
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages.hostid
rows: 1
Extra:
----------------------------------------------
SHOW TABLE STATUS for each table in query following:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages_free
type: index
possible_keys: NULL
key: randomized
key_len: 9
ref: NULL
rows: 1438285
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: adv_pages
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages_free.adv_page_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: adv_areas
type: ref
possible_keys: uniq_area_id,promoted_type
key: uniq_area_id
key_len: 4
ref: lm_new.adv_pages.adv_page_id
rows: 4
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: adv_hosts
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: lm_new.adv_pages.hostid
rows: 1
Extra:
*************************** 1. row ***************************
Name: adv_pages
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 6186980
Avg_row_length: 104
Data_length: 646156000
Max_data_length: 281474976710655
Index_length: 509118464
Data_free: 0
Auto_increment: 21777936
Create_time: 2007-04-30 20:34:24
Update_time: 2007-07-17 10:20:09
Check_time: 2007-07-17 10:30:22
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 1. row ***************************
Name: adv_areas
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 21570410
Avg_row_length: 32
Data_length: 708646668
Max_data_length: 281474976710655
Index_length: 2921625600
Data_free: 0
Auto_increment: NULL
Create_time: 2007-06-14 10:45:33
Update_time: 2007-07-17 01:44:31
Check_time: 2007-07-17 10:31:24
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
*************************** 1. row ***************************
Name: adv_hosts
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 29333
Avg_row_length: 105
Data_length: 3092392
Max_data_length: 281474976710655
Index_length: 2875392
Data_free: 0
Auto_increment: 100406
Create_time: 2007-07-15 10:24:18
Update_time: 2007-07-17 10:19:03
Check_time: 2007-07-17 10:31:24
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
[25 Jul 2007 21:06]
Valeriy Kravchuk
Please, send the results of SHOW TABLE STATUS for the tables involved from both 5.0.45 and 5.0.41. Have you ran ANALYZE TABLE for tables involved on 5.0.45 recently? Please, send also query plan from 5.0.41 (where query runs faster).
[6 Aug 2007 16:00]
John Henry
I've experienced the bug where MySQL hangs forever in "sending data" but not deterministically. I run a single query tens of thousands of times before the actual error occurs, and the error seems to not happen on any one specific query. When I've experienced the error, MySQL has always had an uptime of more than a week on the same set of queries with no error. Unfortunately, when this error happens, the next update that occurs on the table that has a query stuck in "sending data" destroys the database because the update is locked and all subsequent selects cannot complete.
[20 Aug 2007 10:38]
Vadim Smelyansky
Unfortunately repro just gone after a few uninstall/install of different versions (5.0.41,5.0.45,i386,x86_64) of MySQL and linux packages update
[12 Nov 2007 10:55]
Valeriy Kravchuk
So, can anybody repeat similar behaviour for 5.0.45?
[13 Nov 2007 0:08]
Vadim Smelyansky
Current state on our server unstable, queries on big tables (millions of records) slow down in 10 times other application with short queries on small tables (23minutes instead 3 minutes). Sometime (once in 3-4 days) MySQL loack up the server and only reset button can help. That what happened today and 5 tables was lost after crash. thank god we have daily backup and binary logs to restore it. Our hosting provider suppose that instability caused by PAE linux kernel which we installed after RAM upgrade. So we planning to migrate this server to x86_64 bit Linux version.
[13 Nov 2007 4:41]
Valeriy Kravchuk
Please, inform about any results with x86_64. Upload SHOW INNODB STATUS results for one of this slowdown moments.
[14 Dec 2007 0: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".
[17 Jan 2008 9:07]
Vadim Smelyansky
Problem just disappeared after moving from Fedora6 i386 to CentOS5 x86_64 platform
[14 Jan 2010 16:55]
Marc Kowal
i'm running into simmilar problems using 5.1.41 with Debian. Provided InnoDB status above
[21 Jan 2010 12:49]
Marc Kowal
original-query
Attachment: query.rtf (application/octet-stream, text), 4.04 KiB.
[21 Jan 2010 12:50]
Marc Kowal
explain-query
Attachment: explain.rtf (application/octet-stream, text), 5.23 KiB.
[21 Jan 2010 12:50]
Marc Kowal
database-layout
Attachment: db_layout.rtf (application/octet-stream, text), 38.49 KiB.
[21 Jan 2010 12:51]
Marc Kowal
innodb status for provided query
Attachment: innodb-status.rtf (application/octet-stream, text), 5.07 KiB.
[28 Jan 2010 5:59]
Sveta Smirnova
Marc, thank you for the feedback. I can not repeat described behavior with your layout and generic data. Please provide output of SHOW FULL PROCESSLIST in hang time.
[1 Feb 2010 9:12]
Sveta Smirnova
Marc, thank you for the feedback. No, you don't need to open new bug report. Nobody except MySQL developers will see hidden comments if you concerns about private data.
[9 Feb 2010 2:31]
Sebastian Waisbrot
I was able to reproduce this bug on two different computers, both running Ubuntu. One of them is running Ubuntu 9.10, 32 bits and mysql 5.1.37-1ubuntu5 The other has Ubuntu 8.04.3 LTS, 32 bits and mysql 5.0.51a-3ubuntu5.4 I've uploaded the database dump I'm using, the query, the explain, the table status, and the analyze of the used tables to the FTP. The filename is database-bug-29821.tar.bz2
[9 Feb 2010 8:11]
Sveta Smirnova
Sebastian, thank you for the feedback. But in your case this is not a bug: you search for 330340*330340 rows without using indexes. Adding index(node_id) to table way_nodes solves the problem.
[1 Mar 2010 0: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".
[19 Oct 2010 13:02]
Andrew Holgate
We have also experienced the same problem as outlined by John Henry on the 6 Aug 2007 18:00. There is one query in particular that "blocks" the thread and it becomes "Sending Data.." which then also stops all other subsequent queries - which all become "Locked". The number of open threads then reaches the maximum (in our case, 150). Operating System: Redhat MySQL: 5.1.14 Community Edition Output of SHOW TABLE STATUS: Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time node MyISAM 10 Dynamic 18660 87 1624400 2,81475E+14 3144704 0 66340 27/04/2010 19/10/2010 19/10/2010 utf8_general_ci The query itself performs a self-join, which from what we can tell seems to be causing the issue with the query. Query: SELECT DISTINCT n.nid, n.created FROM node n INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'en' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'content_access_author') OR (na.gid = 1 AND na.realm = 'content_access_rid'))) AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR n.language = 'it' AND i18n.nid IS NULL) AND ( n.type = 'articolo' AND n.status = 1) ORDER BY n.created DESC LIMIT 6410, 10 I was able to recreate the problem by executing the above query directly on the database. Reducing the query, I discovered the part of the query that is causing problems is the self-join. Perforning a self-join using other columns of the table did not create the issue, it seems to be only for the column titled tnid. Feedback regarding this issue would aid us greatly.
[26 Dec 2010 14:14]
Valeriy Kravchuk
Andrew, Please, send the results of EXPLAIN for the problematic query.
[24 Jan 2011 19:42]
Pavel Niederle
Hi everyone, I have the same problem from the time I migrated the web from the old server to the new server. The mysql configuration file was exactly same, database same, scripts and queries same. On the "old" server I was running several years MySQL version 5.0.90 without any problem. On the "new" server I use MySQL version 5.5.8 and have trouble. Both servers use Gentoo 64-bit OS. Same bad experience I had on the "new" server with MySQL version 5.1.? ... that was the reason to upgrade to the 5.5.8 version, but as I found it has the same bug. The SQL query which cause the trouble was not nice written, I know, I already changed it, anyway until the new MySQL version bug appeared, it was not doing any trouble.
[24 Jan 2011 19:43]
Pavel Niederle
New server running MySQL
Attachment: EXPLAIN-novy-server.png (image/png, text), 99.64 KiB.
[25 Jan 2011 21:12]
Sveta Smirnova
Pavel, thank you for the feedback. Plan for 5.5 looks better. It is strange it actually run longer. Could you please send us dump or SHOW CREATE TABLE/SHOW TABLE STATUS for all tables used in the query and full query text?
[23 Feb 2011 10:11]
Alexey Furmanov
Hi guys! I've became more and more interested in this issue... Well, not in the issue itself particularly, but in the fact that MySQL is the only popular DBMS that can't execute JOINs if it performs something a little more complex than a kiddy "2 + 2" task. And moreover, for such an issue you don't even have an assignee?!!!! It's not like I'm complaining or anything, after all I haven't paid a cent to a developers, I'm just deploying my CMS on a different hostings and I'm stepping on the issue regularly, but... hey!?? The issue is definitely critical and you never ever seriously tried to solve it for more than 3 years! If I ever to start providing hosting services by myself, do you think I will ever buy such a crappy support? P.S. So, guys, if you're still have even a little pride in your own work, don't you want to try solving the issue? Someone like me even could provide you gladly with an stable environment to reproduce the issue (mysql binaries, used to run the DB, database dump, queries flow in order to reproduce the issue).
[25 Feb 2011 20:59]
Sveta Smirnova
Alexey, thank you for the test case provided. Latest query hangs in status "statistics" in my environment. Do you experience same issue?
[26 Feb 2011 9:00]
Alexey Furmanov
Hello Svetlana, Firstly thank you very much for your reply, it is really bothersome issue and so I'm glad you could reproduce it with the provided testcase and hope it will get good chance to be solved. Then regarding the matter... Yes, with the current queries flow I'm expiriencing the same issue. But if instead of creating temporary table TMP_HM_240460bd66f077d722639e3f7194e069 you will try to use its SELECT as sub-query, then the status probably will change to the 'Sending Data' (actually that is the only reason why TMP_HM_240460bd66f077d722639e3f7194e069 was introduced). Moreover, while experimenting over the query flow in order to avoid the issue I have seen hangs on some other statuses too, but at this point I can't remember these statuses names.
[23 Mar 2011 12:00]
Sveta Smirnova
Alexey, thank you for the feedback. Finally verified as described.
[24 Apr 2011 23:10]
Maurice Birchler
Running 5.0.45 on hosted service. Hit this problem after running for over a year with no code changes. After several restarts system is running again, but I am now having to drop MySQL for MS-SQL as it appears that there is no remedy for this problem at it could occur at anytime. Very hard to belive that MySQL does not come with a "User Beware" statement advising that the product is not reliable.
[14 Jul 2011 11:48]
Shashidhar Y
This issues seems to be verified, and reproduced in march'2011. Is it a bug in MySQL? Has this issue been resolved? If yes, which version of MySQL has this fix? If it is not a bug, did mysql guys suggested any workaround? I don't see the discussion continued, if you continue such type of discussion till end it would be useful to the world!! Could any one of you respond me please? Just out of interest, like Redhat had support agreement to solve their product issues in time (in fact lesser time with faster time), does mysql have anything like that? Cheers
[14 Jul 2011 20:56]
Maurice Birchler
You asked for an update. Here's my theory... It is not a bug in MySQL. The JOIN statement is not the problem. I have been running the same JOIN statement millions of times ad day without a problem, only once it occurred. I did what others did and changed the configuration. I moved the MySQL to a virtual private server, changing nothing else save the connection string. It has not had the problem again. What I think happened the one time it did this. I was at a client site an connected via their guest wireless and it was not a good connection, so while still connected, I connect via my smart phone and tried to continue the session. I think the connection confusion created the block entry in "Sending Data" which then created the problem. As others have not returned to this discussion, I think I am right that the cause is a glitch in the connection and happens so rarely that the problem is not able to be reliably reproduced to diagnose what the fix would be. Being on it's own server now makes me happier, if this did occur again, because the solution is to restart the MySQL service. By the way, in the flurry, I moved the application to MS-SQL (with some pain changing SQL statements etc) but it ran so slow, I am stayng with MySQL.
[14 Jul 2011 21:05]
Alexey Furmanov
@Maurice Birchler don't buzz please, bug is repeatable, and as you can see - verified and no one virtual server doesn't solve this bug, probably you have different one, but others none
[15 Jul 2011 5:11]
Shashidhar Y
Thanks for the responses!! Alexey, I see your messages here. Did you get any chance to solve this problem or in other words would you be able to find any workaround? Could you please share this with me, if you don't mind? Thanks in advance. Well, I am wondering there are no posts/messages from mysql developers (or from any one, in fact) after this bug has been verified. Cheers
[18 Jul 2011 12:15]
Sveta Smirnova
Shashidhar, all, thank you for the feedback. "Verified" means bug was confirmed by MySQL employee and consistent method of how to repeat the problem known. Now it is turn of MySQL developers to fix the bug. We can not make any prognosis on when bug is fixed, so, please, be patient.
[18 Jul 2011 13:18]
Shashidhar Y
Okay, I 'll be patient. To All reporters, Just want to reiterate, this bug has been verified on & by [23 Mar 13:00] Sveta Smirnova. It's been 4 months, with no one has been assigned to fix this issue at all and moreover this bug still exist (even it has been reported by and from across the world)!!!
[25 Jul 2011 13:10]
Shashidhar Y
Hi Sveta Smirnova Do you have any further any updates, alteast when can we expect fix for/patch for this problem. I do really appreciate, if you understand this problem as critical for us. Even if you tell us, it may fix in so and so version of MySQL, we can plan our activities accordingly. Appreciate your response in this regard, Cheers
[25 Jul 2011 13:11]
Shashidhar Y
Hi Sveta Smirnova Do you have any further any updates, alt east when can we expect fix for/patch for this problem. I do really appreciate, if you understand this problem as critical for us. Even if you tell us, it may fix in so and so version of MySQL, we can plan our activities accordingly. Appreciate your response in this regard, Cheers
[25 Jul 2011 14:49]
Alexey Furmanov
@Shashidhar Y Try to divide one select query to several ones using temporary tables for interim result thus reducing the number of joins in each query. It's stupid workaround but sometimes it works.
[14 Oct 2011 11:08]
David Corley
We believe we're seeing this issue on 5.0.45. There hasn't been an update on this since July, so we'd appreciate some feedback.
[24 Oct 2011 23:21]
Carlos Maynard
I am experiencing this same problem. Two out of three tables in the query are particularly big, 21M and 96M. Indexes seem fine and the query runs in about a second which is reasonable considering the number of rows involved. NOW every few minutes (or longer) it will get stuck, and take 1 or 2 minutes (or more)(there is no telling how long it is going to take, I have not found a pattern as far as how long it takes to execute the query when it get's stuck). Has anyone found a solution or alternative? I've tried everything, subqueries, temp tables, stored procedures, with the same results, it runs fine most of the time, but when it gets stuck, it brings down the database for minutes at a time. The Query: SELECT Messages.PK_id FROM Messages INNER JOIN Nodes ON Nodes.PK_id=Messages.rootnode INNER JOIN Elements ON Elements.nodepointer=Nodes.PK_id WHERE Nodes.title='TEST' AND Elements.`index`=1 AND Elements.`data`='anything' AND Messages.customer=1 AND Messages.tp=2 AND Messages.doctype='TEST' AND Messages.deleted=0 AND Messages.PK_id != 1834802; Explain SELECT.... 1, 'SIMPLE', 'Messages', '', 'ref', 'PRIMARY,idx_customer,idx_tp,idx_rootnode', 'idx_customer', '8', 'const', 1, 'Using where' 1, 'SIMPLE', 'Nodes', '', 'eq_ref', 'PRIMARY,INDEX', 'PRIMARY', '8', 'webedi30.Messages.rootnode', 1, 'Using where' 1, 'SIMPLE', 'Elements', '', 'ref', 'idx_nodepointer,idx_index,DATA', 'idx_nodepointer', '8', 'webedi30.Nodes.PK_id', 4, 'Using where' SHOW TABLE STATUS LIKE 'Nodes'; 'Nodes', 'MyISAM', 10, 'Dynamic', 21528792, 47, 1023000176, 281474976710655, 439949312, 0, 23234927, '2009-08-01 07:42:44', '2011-10-24 18:16:57', '2011-01-05 09:49:34', 'latin1_swedish_ci', , 'row_format=DYNAMIC', '' ; SHOW TABLE STATUS LIKE 'Elements'; 'Elements', 'MyISAM', 10, 'Dynamic', 96234744, 47, 4600356132, 281474976710655, 6221511680, 0, 101609674, '2010-07-13 17:37:02', '2011-10-24 18:17:18', '2011-01-05 17:33:41', 'latin1_swedish_ci', , '', '' ; SHOW TABLE STATUS LIKE 'Messages'; 'Messages', 'MyISAM', 10, 'Dynamic', 556409, 4100, 2281603764, 281474976710655, 77821952, 0, 1834882, '2011-10-06 16:43:09', '2011-10-24 18:17:23', '2011-10-06 16:46:15', 'latin1_swedish_ci', , 'row_format=DYNAMIC', '' ; I'm running version 5.1.48-log on CentOS 32bits.
[17 Nov 2011 11:36]
Adriano Azzarone
I really made crazy to discover the origin of this problem..
The eternal "sending_data", proportional with dimension of the table, is caused (well, for me) by a really strange cause:
mysql_query('SET CHARACTER SET utf8');
Toggling this statement in my PHP code, every query now working fastest than light, without any other huge delay.
I think it is a bug and I hope someone can verify it.
MySQL 5.5.12 - OpenSuse 11.4
[17 Nov 2011 13:44]
Alexey Furmanov
2Adriano: we always use mysql_query('SET CHARACTER SET utf8');, but bug doesn't disappear :(
[17 Nov 2011 13:50]
Alexey Furmanov
oops, I missed, you said to turn it off... but we need UTF-8 unfortunately :)
[18 Nov 2011 9:19]
Adriano Azzarone
Alexey, did you try to turn it off just for while to see something? If you can edit my.cnf, add this line: init_connect = SET CHARACTER SET utf8 (and remove the mysql_query in PHP, of course) This setting is required to my site with PHP 5.2.14, but not to the same site in server test with PHP 5.3 (another strange thing). All my database tables have "utf8_unicode_ci" collaction, and also in my webpages I set both PHP header and HTML charset to UTF-8, but only PHP 5.3 tells it correctly to MySQL. I have not any other information about, but I hope this can be useful to someone.
[12 Dec 2011 18:00]
Alejandro Garcia
i have the same problem, does any one could solve it?
[7 Jan 2012 5:00]
PXP Live Operations
We recently hit this bug with a large table (400,000 rows) on centos 64 bit. The above suggested fix worked for us, and we saw an immediate improvement. There is no ryhme or reason to it. A simple reboot, and we couldn't get it to serve data again. Hope it gets fixed again. PS: It should be noted that if you're using memcached, if you have a key value pair that comes from the DB, it never makes it into the daemon.
[23 May 2012 19:24]
Eric Coll
I am able to reproduce what it seems related to this bug. If it's not, please let me know and I will submit another bug.
I have the following query, let's call it query1:
select date(date) as day, region,1, count(distinct account) as request_count,
(select count(distinct account)
from test.v_ice where date >= '2012-05-20' and date < '2012-05-21' and
v_ice.region = rd.region and zone='ANY') <> 'cc1'
) as ice_count, 0
from request_data.v_request rd
where date >= '2012-05-20' and date < '2012-05-21'and target='ANY'
This query executes in 30 seconds. However, any attempt to use that data as a subquery, either by a simple select or an insert results in the query hanging forever in either the "sending data" or "sorting data" stage.
For example:
select <fields> from <query1> hangs
create temporary table X <query1> hangs
The view that's being query join several tables. Replacing that view with a table that contains the original feed in a denormalized format solves the problem. This is not a solution for us, the flat feed is huge and we only keep temporary data on it.
This is a major problem. Is this the same bug or should I file a new bug?
[30 Oct 2012 17:59]
Sveta Smirnova
We discussed to don't fix this bug internally. Here is description why (by Chaithra Gopalareddy): If we use the queries as presented in the crash_queries_flow.sql file, we see that mysqld hangs. The problem here is that, with the default optimizer search depth being 62 and with a 24-way join present in the query, of which most of the conditions are on a non-unique indexed field (some of them do not even have a index), optimizer would take time to come up with a plan. Hence the hang. A workaround for this query would be to reduce the optimizer_search_depth and optimizer_prune_level.
Description: MySQL hangs forever while "sending data" on query with chained joins. Database contained about 3 million records joined tables contains about 200000 Looks like regression - same query was work on 5.0.37 (took around 1h) Running this query with LIMIT like LIMIT 600000,30 succeed to return data How to repeat: following query always stuck for infinity: SELECT adv_pages_free.adv_page_id,CONCAT('',randomized) as rnd, adv_areas.adv_page_id, adv_areas.promoted_type, adv_areas.area_id, adv_areas.promoted_id, adv_pages.industry_id, adv_pages.filename, adv_pages.hostid as hout, adv_hosts.ip as ipout, adv_hosts.classc as cout, adv_hosts.hostname, adv_hosts.g_known, adv_hosts.y_known, adv_hosts.m_known, adv_hosts.g_banned, adv_hosts.y_banned, adv_hosts.m_banned FROM adv_pages_free LEFT JOIN adv_pages ON adv_pages.adv_page_id=adv_pages_free.adv_page_id LEFT JOIN adv_areas ON adv_pages.adv_page_id=adv_areas.adv_page_id LEFT JOIN adv_hosts ON adv_pages.hostid=adv_hosts.hostid ORDER BY randomized,adv_pages_free.adv_page_id tables structure is: adv_pages_free | CREATE TABLE `adv_pages_free` ( `adv_page_id` int(11) NOT NULL default '0', `randomized` int(11) unsigned default NULL, PRIMARY KEY (`adv_page_id`), KEY `randomized` (`randomized`,`adv_page_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 adv_pages | CREATE TABLE `adv_pages` ( `adv_page_id` int(11) NOT NULL auto_increment, `hostid` int(11) NOT NULL default '0', `uri` varchar(255) NOT NULL default '', `industry_id` smallint(4) NOT NULL default '0', `theme` varchar(255) default NULL, `filename` varchar(36) default NULL, `committed` timestamp NOT NULL default '0000-00-00 00:00:00', `commit_id` int(11) NOT NULL default '0', `nlinks` int(11) NOT NULL default '0', `keyword` text, PRIMARY KEY (`adv_page_id`), UNIQUE KEY `uniq_page_id` (`hostid`,`uri`), KEY `page_id1` (`hostid`,`adv_page_id`,`uri`,`industry_id`) ) ENGINE=MyISAM AUTO_INCREMENT=21777537 DEFAULT CHARSET=latin1 adv_areas | CREATE TABLE `adv_areas` ( `adv_page_id` int(11) NOT NULL default '0', `area_id` tinyint(4) NOT NULL default '1', `sentence_id` int(11) NOT NULL default '0', `anchor_text` varchar(255) NOT NULL default '', `promoted_id` int(11) NOT NULL default '0', `promoted_type` tinyint(1) NOT NULL default '2', `crawlMask` tinyint(4) NOT NULL default '0', UNIQUE KEY `uniq_area_id` (`adv_page_id`,`area_id`), KEY `promoted_id` (`promoted_type`,`promoted_id`), KEY `promoted_type_2` (`promoted_type`), KEY `promoted_type` (`adv_page_id`,`promoted_type`,`area_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 adv_hosts | CREATE TABLE `adv_hosts` ( `hostid` int(11) NOT NULL auto_increment, `hostname` varchar(255) default NULL, `rev_hostname` varchar(255) default NULL, `port` smallint(6) NOT NULL default '80', `ip` varchar(50) default NULL, `classc` int(11) NOT NULL default '0', `oldip` varchar(50) default NULL, `link_industry_id` int(11) default '18', `g_known` tinyint(1) default '0', `y_known` tinyint(1) default '0', `m_known` tinyint(1) default '0', `g_banned` tinyint(1) default '0', `y_banned` tinyint(1) default '0', `m_banned` tinyint(1) default '0', `customized` tinyint(1) NOT NULL default '0', `modified` datetime default NULL, PRIMARY KEY (`hostid`), UNIQUE KEY `hostname` (`hostname`,`port`), KEY `ip` (`ip`), KEY `iphostid` (`ip`,`hostid`), KEY `rev_host_name` (`rev_hostname`) ) ENGINE=MyISAM AUTO_INCREMENT=100404 DEFAULT CHARSET=latin1