Bug #103607 assert failed: assert(m_deque == other.m_deque) in mem_root_deque.h
Submitted: 6 May 2021 11:19 Modified: 26 Aug 2021 1:24
Reporter: lyp tennyson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S6 (Debug Builds)
Version:8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[6 May 2021 11:19] lyp tennyson 
Description:
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
                                                                                                                                                              1518,1        91%
/lib64/libc.so.6(gsignal+0x37) [0x7ffff5def277]
/lib64/libc.so.6(abort+0x148) [0x7ffff5df0968]
/lib64/libc.so.6(+0x2f096) [0x7ffff5de8096]
/lib64/libc.so.6(+0x2f142) [0x7ffff5de8142]
/home/tennyson.lyp/t/mysql/bin/mysqld(mem_root_deque<TABLE_LIST*>::Iterator<TABLE_LIST*>::operator==(mem_root_deque<TABLE_LIST*>::Iterator<TABLE_LIST*> const&) const+0x4e) [0x30c965c]
/home/tennyson.lyp/t/mysql/bin/mysqld(mem_root_deque<TABLE_LIST*>::Iterator<TABLE_LIST*>::operator!=(mem_root_deque<TABLE_LIST*>::Iterator<TABLE_LIST*> const&) const+0x23) [0x30c9357]
/home/tennyson.lyp/t/mysql/bin/mysqld() [0x319c93c]
/home/tennyson.lyp/t/mysql/bin/mysqld() [0x319cb56]
/home/tennyson.lyp/t/mysql/bin/mysqld() [0x319dc57]
/home/tennyson.lyp/t/mysql/bin/mysqld(JOIN::make_join_plan()+0x221) [0x3194e79]
/home/tennyson.lyp/t/mysql/bin/mysqld(JOIN::optimize()+0x13a4) [0x31887b6]
/home/tennyson.lyp/t/mysql/bin/mysqld(Query_block::optimize(THD*)+0xef) [0x3237977]
/home/tennyson.lyp/t/mysql/bin/mysqld(Query_expression::optimize(THD*, TABLE*, bool)+0x169) [0x32db315]
/home/tennyson.lyp/t/mysql/bin/mysqld(Item_subselect::exec(THD*)+0x2a1) [0x3686cc3]
/home/tennyson.lyp/t/mysql/bin/mysqld(Item_exists_subselect::val_bool()+0x61) [0x368a2bf]
/home/tennyson.lyp/t/mysql/bin/mysqld(sp_instr_jump_if_not::exec_core(THD*, unsigned int*)+0x88) [0x309ee30]
/home/tennyson.lyp/t/mysql/bin/mysqld(sp_lex_instr::reset_lex_and_exec_core(THD*, unsigned int*, bool)+0x422) [0x309cf42]
/home/tennyson.lyp/t/mysql/bin/mysqld(sp_lex_instr::validate_lex_and_execute_core(THD*, unsigned int*, bool)+0x1a7) [0x309dcd9]
/home/tennyson.lyp/t/mysql/bin/mysqld(sp_lex_instr::execute(THD*, unsigned int*)+0x40) [0x30a1e42]
/home/tennyson.lyp/t/mysql/bin/mysqld(sp_head::execute(THD*, bool)+0x71c) [0x308e614]
/home/tennyson.lyp/t/mysql/bin/mysqld(sp_head::execute_procedure(THD*, mem_root_deque<Item*>*)+0x7fa) [0x309053e]
/home/tennyson.lyp/t/mysql/bin/mysqld(Sql_cmd_call::execute_inner(THD*)+0x1e8) [0x3803664]
/home/tennyson.lyp/t/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x5c7) [0x3234a7d]
/home/tennyson.lyp/t/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0x51ef) [0x31ba7e4]
/home/tennyson.lyp/t/mysql/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x70b) [0x31bc702]
/home/tennyson.lyp/t/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1508) [0x31b2d08]
/home/tennyson.lyp/t/mysql/bin/mysqld(do_command(THD*)+0x482) [0x31b112d]
/home/tennyson.lyp/t/mysql/bin/mysqld() [0x3388aed]
/home/tennyson.lyp/t/mysql/bin/mysqld() [0x4fb549a]
/lib64/libpthread.so.0(+0x7e25) [0x7ffff7bc6e25]
/lib64/libc.so.6(clone+0x6d) [0x7ffff5eb6f1d]

How to repeat:

CREATE PROCEDURE  perf_test()
BEGIN

    DROP TEMPORARY TABLE IF EXISTS tmp_participant_data;

    CREATE TEMPORARY TABLE tmp_participant_data
    (
        field_name  VARCHAR(64),
        field_value  VARCHAR(64)
    );

    IF EXISTS(SELECT 1
              FROM tmp_participant_data d
              LEFT JOIN information_schema.columns c
              ON c.table_schema = 'test' AND upper(c.column_name) = upper(d.field_name)
              WHERE c.table_schema IS NULL
        )
    THEN
      SELECT 'then';
    ELSE
      SELECT 'else';
    END IF;

   DROP TEMPORARY TABLE  tmp_participant_data;
END;

-- call the procedure twice, mysql server built in debug mode:

mysql> call perf_test()$$
+------+
| else |
+------+
| else |
+------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.13 sec)

mysql> call perf_test()$$
ERROR 2013 (HY000): Lost connection to MySQL server during query
[6 May 2021 11:29] MySQL Verification Team 
Hello lyp tennyson,

Thank you for the report and test case.
Observed that 8.0.24 debug build is affected with provided test case.

regards,
Umesh
[26 Aug 2021 1:24] Jon Stephens 
Documented fix as follows in the MySQL 8.0.27 changelog:

    A stored procedure containing an IF statement with an EXISTS
    function inside, which acted on one or more tables that were
    deleted and recreated between executions, did not execute
    correctly for subsequent invocations following the first one.

Closed.