| Bug #16201 | Indexed query on a table with clause using 3+ indexes crashes InnoDB server | ||
|---|---|---|---|
| Submitted: | 4 Jan 2006 19:36 | Modified: | 25 Sep 2006 17:08 |
| Reporter: | JM Marchand | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.16/5.0.18/5.0.19/5.0.20(a)/5.0.22 | OS: | Windows (Windows 2000 Pro/Windows XP Home/Windows Server 2003) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[4 Jan 2006 19:39]
JM Marchand
Server trace enabled with mysqld-debug.exe --gdb=TRUE
Attachment: mySqlError.zip (application/x-zip-compressed, text), 35.57 KiB.
[4 Jan 2006 19:41]
JM Marchand
Server config file.
Attachment: my.ini (application/octet-stream, text), 792 bytes.
[4 Jan 2006 23:09]
Heikki Tuuri
Hi! Looking at the mysqld-debug trace, it seems to crash in Row Ordered Retrieval optimization. Since also EXPLAIN SELECT crashes the server, the crash definitely happens during query optimization. Can you upload the whole table, so that MySQL engineers can try to repeat this? Regards, Heikki
[5 Jan 2006 2:24]
MySQL Verification Team
How Heikki already asked you, could you please provide the dump script for to insert rows. You can upload it into a zip file called i.e.: bug16201.zip at: ftp://ftp.mysql.com/pub/mysql/upload/ thanks in advance.
[6 Jan 2006 15:56]
MySQL Verification Team
verified on 5.0-bk on win2003
[6 Jan 2006 16:32]
MySQL Verification Team
couldn't repeat crash using windows 2003 & 4.1-bk. couldn't repeat using 5.0.18 on linux either.
[6 Jan 2006 16:56]
MySQL Verification Team
On my side 5.0.19 on Linux Suse not crashes.
[28 Apr 2006 11:15]
Sergey Petrunya
Judging from the stack trace, this is the same issue as one that has been fixed by BUG#19021. I was unable to verify (both BUG#19021 and this problem depend on cost calculations and may not be repeatable). Still I'm marking this bug as duplicate of BUG#19021. Feel free to reopen if you have still have crashes with post-BUG#19021 tree.
[9 Aug 2006 13:30]
JM Marchand
Reopened, still occurs in 5.0.22. Very odd, seems to occur when number of indexes in the select and clauses is > 3. Adding a non-indexed column in the clause sometimes makes it through, and selecting a non-indexed column also sometimes goes through.
[9 Aug 2006 13:31]
JM Marchand
Reopened, still occurs in 5.0.22. I changed synopsis. Very odd, seems to occur when number of indexes in the select and clauses is > 3. Adding a non-indexed column in the clause sometimes makes it through, and selecting a non-indexed column also sometimes goes through. I'll upload test case in a minute.
[9 Aug 2006 17:32]
MySQL Verification Team
Thank you for the feedback and test case. Indeed I was able to repeat with 5.0.24 released version and with server built from source tree: c:\mysql\bin>mysql -uroot mls Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT i.id as moyenne FROM mls.inscription i WHERE i.CODE_ <cut> ERROR 2013 (HY000): Lost connection to MySQL server during query
[9 Aug 2006 18:19]
MySQL Verification Team
The current Visual Studio project with option Buffer Security Check (/GS)
not allows to get the call stack. Then I disabled it and I got the below
call stack:
./opt_range.cpp
--3230--
for (ROR_SCAN_INFO **scan= ror_scan_mark; scan != ror_scans_end; ++scan)
{
bitmap_subtract(&(*scan)->covered_fields, &covered_fields);
(*scan)->used_fields_covered=
bitmap_bits_set(&(*scan)->covered_fields);
^^^^^^^^^^ line of crash
CALL STACK on XP 32-bits:
mysqld-nt.exe!_bitmap_subtract() + 0x36 C
>mysqld-nt.exe!get_best_covering_ror_intersect(st_qsel_param * param=0x02e2cf38,
SEL_TREE * tree=0x00e3dba8, double read_time=294.35506393588190) Line 3230 C++
mysqld-nt.exe!SQL_SELECT::test_quick_select(THD * thd=0x00000000, Bitmap<64> keys_to_use={...},
unsigned __int64 prev_tables=6306598177799920552, unsigned long limit=1081238958,
int force_quick_range=48433904) Line 2022 + 0x2f C++
mysqld-nt.exe!make_join_statistics(JOIN * join=0x00000000, st_table_list * tables=0x0048b646,
Item * conds=0x02e2cf38, st_dynamic_array * keyuse_array=0x00e3dba8) Line 2344 + 0x43 C++
mysqld-nt.exe!JOIN::optimize() Line 715 + 0x21 C++
mysqld-nt.exe!mysql_select(THD * thd=0x00e22268, Item * * * rref_pointer_array=0x00e226b4,
st_table_list * tables=0x02e30240, unsigned int wild_num=0, List<Item> & fields={...},
Item * conds=0x02e309c8, unsigned int og_num=0, st_order * order=0x00000000,
st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000,
unsigned long select_options=2156153344, select_result * result=0x02e30ae0,
st_select_lex_unit * unit=0x00e22310, st_select_lex * select_lex=0x02e30af0)
Line 1968 + 0x7 C++
mysqld-nt.exe!handle_select(THD * thd=0x00e22268, st_lex * lex=0x00e222a8,
select_result * result=0x02e30ae0, unsigned long setup_tables_done_option=0)
Line 254 + 0x6b C++
mysqld-nt.exe!mysql_execute_command(THD * thd=0x00000000) Line 2573 + 0xa C++
mysqld-nt.exe!mysql_parse(THD * thd=0x00e22268, char * inBuf=0x02e30058, unsigned int length=140)
Line 5832 C++
mysqld-nt.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00e22268,
char * packet=0x00e345b1, unsigned int packet_length=141) Line 1768 C++
mysqld-nt.exe!do_command(THD * thd=0x00e22268) Line 1550 + 0xd C++
mysqld-nt.exe!handle_one_connection(void * arg=0x00e22268) Line 1181 + 0xa C++
mysqld-nt.exe!_pthread_start() + 0x3b C
mswsock.dll!71a15ccc()
mysqld-nt.exe!_threadstart(void * ptd=0x00e17150) Line 196 + 0x6 C
kernel32.dll!7c80b683()
mswsock.dll!71a15ccc()
[19 Sep 2006 11:23]
Georgi Kodinov
Pushed into 5.0.26/5.1.12-beta
[19 Sep 2006 13:47]
Sergey Petrunya
Correction: The bugfix appears in 5.0.25 (not in 5.0.26).
[25 Sep 2006 12:38]
Sergey Petrunya
Commit changeset comment: # ChangeSet # 2006/08/22 04:14:39-07:00 igor@rurik.mysql.com # Fixed bug 16201: a memory corruption causing crashes due to a too small # buffer for a MY_BITMAP temporary buffer allocated on stack in the # function get_best_covering_ror_intersect(). # Now the buffer of a proper size is allocated by a request from this # function in mem_root. # # We succeeded to demonstrate the bug only on Windows with a very large # database. That's why no test case is provided for in the patch. # # sql/opt_range.cc # 2006/08/22 04:14:36-07:00 igor@rurik.mysql.com +12 -7 # Fixed bug 16201: a memory corruption causing crashes due to a too small # buffer for a MY_BITMAP temporary buffer allocated on stack in the # function get_best_covering_ror_intersect(). # Now the buffer of a proper size is allocated by a request from this # function in mem_root. # #
[25 Sep 2006 12:43]
Sergey Petrunya
i.e. The bug was that index_merge/Intersection optimizer could have a memory overrrun when the number of table fields covered by some index is sufficiently large. The most likely result of the overrun is a crash.
[25 Sep 2006 17:08]
Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.
Description: Executing a SELECT COUNT(*) on a single table with a WHERE clause on 3 indexes causes the mysqld-nt.exe process to crash with a Windows "Buffer overrun" exception. No log is written to hostname.err file, and the process ends instantly. The same query with a SELECT * works fine. The crash seems to depend on the number of returned rows for each criteria, but replacing any index by a new one on the same table crashes too, if that index returns a number of rows X which seems to depend on the optimizer. If one index returns zero rows, it always works. Replacing any criterias by LIKE 'thevalue%' always works. Using only 2 criterias always works, but I don't know if When it works, EXPLAIN shows an index_merge on the three indexes. For the query that doesn't work, the EXPLAIN crashes the server as well. Increasing the buffers just delays the crash a couple of seconds. Tried with innodb_buffer_pool_size=30M and 800M=same result, just longer. Also tried with mysql-debug.exe with tracing, and I'll post results after this. How to repeat: # Database: mls # Table: 'inscription' # CREATE TABLE `inscription` ( `ID` int(11) unsigned NOT NULL auto_increment, `NO_MLS` varchar(14) NOT NULL default '', `AGENT_INSCRIPTEUR_1` varchar(10) default NULL, `BUREAU_INSCRIPTEUR_1` varchar(12) default NULL, `AGENT_INSCRIPTEUR_2` varchar(10) default NULL, `BUREAU_INSCRIPTEUR_2` varchar(12) default NULL, `CODE_MUNICIPALITE` varchar(32) default NULL, `CODE_QUARTIER` varchar(32) default NULL, `NO_CIVIQUE_DEBUT` varchar(10) default NULL, `NO_CIVIQUE_FIN` varchar(10) default NULL, `NOM_RUE_COMPLET` varchar(60) default NULL, `CATEGORIE_PROPRIETE` varchar(10) default NULL, `GENRE_PROPRIETE` char(3) default NULL, `TYPE_BATIMENT` varchar(10) default NULL, `TYPE_COPROPRIETE` varchar(10) default NULL, `CODE_STATUT` varchar(10) default NULL, PRIMARY KEY (`ID`), KEY `BUREAU_INSCRIPTEUR_1` (`BUREAU_INSCRIPTEUR_1`), KEY `BUREAU_INSCRIPTEUR_2` (`BUREAU_INSCRIPTEUR_2`), KEY `CODE_MUNICIPALITE` (`CODE_MUNICIPALITE`), KEY `AGENT_INSCRIPTEUR_1` (`AGENT_INSCRIPTEUR_1`), KEY `CATEGORIE_PROPRIETE` (`CATEGORIE_PROPRIETE`), KEY `GENRE_PROPRIETE` (`GENRE_PROPRIETE`), KEY `AGENT_INSCRIPTEUR_2` (`AGENT_INSCRIPTEUR_2`), KEY `CODE_QUARTIER` (`CODE_QUARTIER`), KEY `NO_MLS` (`NO_MLS`), KEY `NOM_RUE_COMPLET_2` (`NOM_RUE_COMPLET`(10)), KEY `NO_CIVIQUE_DEBUT` (`NO_CIVIQUE_DEBUT`(3),`NO_CIVIQUE_FIN`(3)), KEY `CODE_STATUT` (`CODE_STATUT`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SELECT count(*) FROM mls.inscription WHERE mls.inscription.CODE_STATUT = 'VE' AND mls.inscription.GENRE_PROPRIETE = 'AP' AND mls.inscription.AGENT_INSCRIPTEUR_1 = 'MT11111' ; ## OK, result = 0 ## Same query against 45000 rows in INSCRIPTION with no NULL value, and ## 15669 rows with CODE_STATUT = 'VE' ## 11314 rows with GENRE_PROPRIETE = 'AP', of which 2196 have CODE_STATUT='VE' ## 38 rows with AGENT_INSCRIPTEUR_1 = 'MT11111' of which none have the above two criterias, hence result should be zero as well. Suggested fix: Use select * and count results, but very ineffective.