Assertion `count <= lock->read_no_write_count && count2 <= 100' failed in check_locks with numerous subqueries

[Philip Stoev]

When using an excessive number of subqueries, the server asserted as follows. If there is a 100-table limit, it must result in a client error message and not in an assertion. Furthermore, the constant 100 should not be used in the code as a literal -- it has to have a proper name.

mysqld: thr_lock.c:255: check_locks: Assertion `count <= lock->read_no_write_count && count2 <= 100' failed.

# 2011-10-14T19:23:51 #3 <signal handler called>
# 2011-10-14T19:23:51 #4 0x00000035a6e328f5 in raise () from /lib64/libc.so.6
# 2011-10-14T19:23:51 #5 0x00000035a6e340d5 in abort () from /lib64/libc.so.6
# 2011-10-14T19:23:51 #6 0x00000035a6e2b8b5 in __assert_fail () from /lib64/libc.so.6
# 2011-10-14T19:23:51 #7 0x0000000000c4590b in check_locks (lock=0x7f4f40155048,
# 2011-10-14T19:23:51 where=0xe94ff6 "read lock with no write locks", type=TL_READ,
# 2011-10-14T19:23:51 allow_no_locks=0 '\000') at thr_lock.c:254
# 2011-10-14T19:23:51 #8 0x0000000000c46af5 in thr_lock (data=0x7f4f4011b620, owner=0x4a1e150,
# 2011-10-14T19:23:51 lock_type=TL_READ) at thr_lock.c:665
# 2011-10-14T19:23:51 #9 0x0000000000c47c71 in thr_multi_lock (data=0x7f4f40017000, count=104,
# 2011-10-14T19:23:51 owner=0x4a1e150) at thr_lock.c:1088
# 2011-10-14T19:23:51 #10 0x00000000006a2002 in mysql_lock_tables (thd=0x4a1cca8,
# 2011-10-14T19:23:51 sql_lock=0x7f4f40016ca8, write_lock_used=false, flags=4,
# 2011-10-14T19:23:51 need_reopen=0x7f4fb03aa7e3) at lock.cc:286
# 2011-10-14T19:23:51 #11 0x00000000006a1daa in mysql_lock_tables (thd=0x4a1cca8,
# 2011-10-14T19:23:51 tables=0x7f4f40265348, count=104, flags=4, need_reopen=0x7f4fb03aa7e3)
# 2011-10-14T19:23:51 at lock.cc:209
# 2011-10-14T19:23:51 #12 0x000000000071ef6d in lock_tables (thd=0x4a1cca8, tables=0x7f4f400d5e70,
# 2011-10-14T19:23:51 count=104, need_reopen=0x7f4fb03aa7e3) at sql_base.cc:5472
# 2011-10-14T19:23:51 #13 0x000000000071e364 in open_and_lock_tables_derived (thd=0x4a1cca8,
# 2011-10-14T19:23:51 tables=0x7f4f400d5e70, derived=true) at sql_base.cc:5120
# 2011-10-14T19:23:51 #14 0x00000000006cfc9a in open_and_lock_tables (thd=0x4a1cca8,
# 2011-10-14T19:23:51 tables=0x7f4f400d5e70) at mysql_priv.h:1749
# 2011-10-14T19:23:51 #15 0x00000000006c7ec2 in execute_sqlcom_select (thd=0x4a1cca8,
# 2011-10-14T19:23:51 all_tables=0x7f4f400d5e70) at sql_parse.cc:5078
# 2011-10-14T19:23:51 #16 0x00000000006beebe in mysql_execute_command (thd=0x4a1cca8)
# 2011-10-14T19:23:51 at sql_parse.cc:2250
# 2011-10-14T19:23:51 #17 0x00000000006cac8f in mysql_parse (thd=0x4a1cca8,

test case:

--disable_abort_on_error
SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=on,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on';
SET SESSION join_cache_level = 1;
SET SESSION join_buffer_size = 131072;
SET SESSION debug = '';
--enable_abort_on_error

# Begin test case for query 0

--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
--enable_warnings

CREATE TABLE t1 ( col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333))) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('tzvyvdkmdh','she');
INSERT INTO t1 VALUES ('fltzvyvdkm','hfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdx');
INSERT INTO t1 VALUES ('LWWVD','w');
INSERT INTO t1 VALUES ('w','mhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiqnisjifgdqrwolfvlhrghlapglknbwsyxqxcrlokpjfsfazhqncpwsofhsssitqrsmlkqsjdyoqhmabcwylzrrpmpfsmvuoeayzxlgonxozrivytdcoyqueqfysnbdltsxigwtkzoobgirrsbaosrncbqpibfjdstljqwliysdoroffhrmzutzbdainhyanjwytlanlxcovdzpkuxmammsgkgqevdakdvuqeptlaudjvihzmnqecwcibdwbzsfqoxnxzdhntphbmkoxbqgtgyndknmdfqmgsniflrlikgnafgybpsticffsaiuhrqtxgyvluerppwdumq');
INSERT INTO t1 VALUES ('can\'t','just');
INSERT INTO t1 VALUES ('plkpmhfltz','hplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfx');
INSERT INTO t1 VALUES ('jhplkpmhfl','like');
INSERT INTO t1 VALUES ('m','LSPKM');
INSERT INTO t1 VALUES ('LAYAK','cvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiqnisjifgdqrwolfvlhrghlapglknbwsyxqxcrlokpjfsfazhqncpwsofhsssitqrsmlkqsjdyoqhmabcwylzrrpmpfsmvuoeayzxlgonxozrivytdcoyqueqfysnbdltsxigwtkzoobgirrsbaosrncbqpibfjdstljqwliysdoroffhrmzutzbdainhyanjwytlanlxcovdzpkuxmammsgkgqevdakdvuqeptlaudjvihzmnqecwcibdwbzsfqoxnxzdhntphbmkoxbqgtgyndknmdfqmgsniflrlikgnafgybpsticffsaiuhrqtxgyvluerppwdumqrktxjwbzwwhhbiuqwuntarpigplemosmhqrbpgqvnmimrggpoktamszbswmyxdgtaqrcthasqjwveuzgfxkjthlklhuhvflffnnteztrrnyzyxqoxasxxmhzqzeyrrrpizfyjbaexravemn');
INSERT INTO t1 VALUES ('GGSSM','there');
INSERT INTO t1 VALUES ('j','txrcvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiq');
INSERT INTO t1 VALUES ('z','HLRYV');
INSERT INTO t1 VALUES ('y','mvxdvtxrcvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytakthmsyfxxyptiamxoqlskrfnebrviqurxiqnisjifgdqrwolfvlhrghlapglknbwsyxqxcrlokpjfsfazhqncpwsofhsssitqrsmlkqsjdyoqhmabcwylzrrpmpfsmvuoeayzxlgonxozrivytdcoyqueqfysnbdltsxigwtkzoobgirrsbaosrncbqpibfjdstljqwliysdoroffhrmzutzbdainhyanjwytlanlxcovdzpkuxmammsgkgqevdakdvuqeptlaudjvihzmnqecwcibdwbzsfqoxnxzdhntphbmkoxbqgtgyndknmdfqmgsniflrlikgnafgybpsticffsaiuh');
INSERT INTO t1 VALUES ('d','w');
INSERT INTO t1 VALUES ('VDBGR','IGRLP');
INSERT INTO t1 VALUES ('IZZWX','pwlqomvxdvtxrcvwsqxqjhplkpmhfltzvyvdkmdhannuiyyyryclvfazbvxqtplasnwfomgyppjzhvsqidaxagdajbsbshdcaghteqofojtewvlgclqpoewtctkzfejqdkwqhfhwfjsaxnmlwtcrvdvduhjtohoswabqhfwhwopnzrhykrvpawjxqizawkghjtfqoktvzrroijawamfptevacqmqvmqccqnohrbvannyikkcwtmjicreqfjqrceyjlsbbyludchkrongokiwbkbkjcyvtnealdxlgzjwphobhvxljgvufxorzyrynigvapkdhtwugxntrxukvmiimqkropwcmpknipyhheljayqflasfapkcykdltcvvnyfvocoruznpoakktpwnlumfdczqtcdmnqfoizsfegnjehaiawvforshlqlgytak');
INSERT INTO t1 VALUES ('just','HIHXI');
INSERT INTO t1 VALUES ('something','l');
INSERT INTO t1 VALUES ('ljadnpwlqo','b');
INSERT INTO t1 VALUES ('n','IJMQT');

SELECT alias2
FROM t1 AS alias1
LEFT OUTER JOIN view_F AS alias2 ON alias1.col_varchar_1024_utf8_key = alias2.col_varchar_10_utf8
WHERE
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 7 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 AND alias2 BETWEEN
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
 OR 4 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
 OR
( 65 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 30 )
 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 25 )
 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 100 )
 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 7 )
 ,
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 AND alias1 BETWEEN
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 10 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 65 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 65 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 0 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 4 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 14 )
 OR alias1 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 2 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 7 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 5 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 9 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 1 )
 +
(
SELECT i1
FROM literals.integers
WHERE i1 = 8 )
 OR
(
SELECT i1
FROM literals.integers
WHERE i1 = 3 )
 AND
(
SELECT i1
FROM literals.integers
WHERE i1 = 6 );

DROP TABLE t1;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

This test suite has a lot of syntax error (fixed).

But it is not repeatable because the 100 limit now is 1000 and it id constant now:

#define MAX_LOCKS 1000

If change it to 100 the problem is repeatable. So we should decide is it OK to put in debugging version of the server such artificial limit as protection against invinite loop?

[Michael Widenius (monty)]

The reason for the 100 is to in a debug binary detect loops in a list. It does not affect production binaries.

I have increased the limit to 1000, which I think should be more than enough for our test suite and test systems.

I prefer to keep this limit in the debug binaries to be able to detect loops in the list (as other ways to detect loops are complex and expensive).