MySQL Bugs: #111891: Assertion failed in table.h: void Table_ref::set

Bug #111891	Assertion failed in table.h: void Table_ref::set_tableno(uint): Assertion `table
Submitted:	26 Jul 2023 6:22	Modified:	7 Aug 2023 7:57
Reporter:	QI XIAODONG	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S6 (Debug Builds)
Version:	8.1.0, 8.0.34	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
2023-07-25T03:30:42.554759Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2023-07-25T03:30:42.965810Z 0 [System] [MY-010116] [Server] /opt/mysql/bin/mysqld (mysqld 8.1.0-debug) starting as process 296587
2023-07-25T03:30:42.988848Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-07-25T03:30:43.689402Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-07-25T03:30:48.060327Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2023-07-25T03:30:48.079927Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2023-07-25T03:30:50.652777Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-07-25T03:30:50.652843Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-07-25T03:30:50.872383Z 0 [System] [MY-010931] [Server] /opt/mysql/bin/mysqld: ready for connections. Version: '8.1.0-debug'  socket: ''  port: 13306  Source distribution.
mysqld: /home/kosaka/Projects/mysql-8.1.0/sql/table.h:3882: void Table_ref::set_tableno(uint): Assertion `tableno < MAX_TABLES' failed.
2023-07-25T19:30:13Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=99b734624d918ef385d652679129f25884b8c39b
Thread pointer: 0x7ff2c6215570
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ff36835ea58 thread_stack 0x100000
/opt/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x59) [0x555bb8d72f48]
/opt/mysql/bin/mysqld(print_fatal_signal(int)+0x3a6) [0x555bb774cc17]
/opt/mysql/bin/mysqld(handle_fatal_signal+0x79) [0x555bb774ce38]
/lib/x86_64-linux-gnu/libc.so.6(+0x42520) [0x7ff378429520]
/lib/x86_64-linux-gnu/libc.so.6(pthread_kill+0x12c) [0x7ff37847da7c]
/lib/x86_64-linux-gnu/libc.so.6(raise+0x16) [0x7ff378429476]
/lib/x86_64-linux-gnu/libc.so.6(abort+0xd3) [0x7ff37840f7f3]
/lib/x86_64-linux-gnu/libc.so.6(+0x2871b) [0x7ff37840f71b]
/lib/x86_64-linux-gnu/libc.so.6(+0x39e96) [0x7ff378420e96]
/opt/mysql/bin/mysqld(Table_ref::set_tableno(unsigned int)+0x41) [0x555bb74f1acf]
/opt/mysql/bin/mysqld(JOIN::setup_semijoin_materialized_table(JOIN_TAB*, unsigned int, POSITION*, POSITION*)+0x4fd) [0x555bb757dd71]
/opt/mysql/bin/mysqld(JOIN::get_best_combination()+0x581) [0x555bb74af4d9]
/opt/mysql/bin/mysqld(JOIN::make_join_plan()+0x5d8) [0x555bb74b5342]
/opt/mysql/bin/mysqld(JOIN::optimize(bool)+0x17e1) [0x555bb74a7481]
/opt/mysql/bin/mysqld(Query_block::optimize(THD*, bool)+0x10e) [0x555bb7579cc0]
/opt/mysql/bin/mysqld(Query_expression::optimize(THD*, TABLE*, bool, bool)+0x1e7) [0x555bb76334a9]
/opt/mysql/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x44) [0x555bb75775c8]
/opt/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x727) [0x555bb75769c7]
/opt/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0x5bdf) [0x555bb74e91a1]
/opt/mysql/bin/mysqld(Prepared_statement::execute(THD*, String*, bool)+0x930) [0x555bb7534e74]
/opt/mysql/bin/mysqld(Prepared_statement::execute_loop(THD*, String*, bool)+0x324) [0x555bb753325c]
/opt/mysql/bin/mysqld(mysqld_stmt_execute(THD*, Prepared_statement*, bool, unsigned long, PS_PARAM*)+0x21d) [0x555bb753004d]
/opt/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xfb6) [0x555bb74e030b]
/opt/mysql/bin/mysqld(do_command(THD*)+0x656) [0x555bb74de8d7]
/opt/mysql/bin/mysqld(+0x390a183) [0x555bb7736183]
/opt/mysql/bin/mysqld(+0x584a922) [0x555bb9676922]
/lib/x86_64-linux-gnu/libc.so.6(+0x94b43) [0x7ff37847bb43]
/lib/x86_64-linux-gnu/libc.so.6(+0x126a00) [0x7ff37850da00]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7ff2bc011790): WITH  jennifer_0 AS (select       ref_16.v5 as c0,      ref_0.v1 as c1   from      test.table12 as ref_0           left join test.table12 as ref_1             inner join test.table12 as ref_2             on (ref_2.v6 is NULL)           on (ref_2.v0 is NULL)         right join test.table12 as ref_3           inner join test.table12 as ref_4           on (EXISTS (               select                     ref_5.v4 as c0,                    ref_5.v2 as c1,                    ref_5.v6 as c2,                    ref_3.v0 as c3,                    ref_3.v6 as c4,                    ref_3.v0 as c5,                    ref_4.v1 as c6,                    (select v3 from test.table12)                      as c7,                    ref_5.v1 as c8,                    ref_5.v3 as c9,                    ref_5.v4 as c10                 from                    test.table12 as ref_5                 where (false)                    and ((ref_5.v0 is NULL)                      or ((false)                        or (true)))))
Connection ID (thread ID): 1678
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:

1. Start MySQL Server
2. Start MySQL Client
3. Pipe init.sql into client (please ignore execution errors here, as it is machine-generated)
4. Pipe case.sql into client 
5. Server crashed with stacktrace

Uploaded mysql-bug-data-111891.zip to Oracle SFTP Server

Hello QI XIAODONG,

Thank you for the report and test case.
I tried reproduce on 8.0.34(debug/release) and 8.1.0(debug and release) builds but not seeing any issues. Could you please share the exact cmake options used for the build? Thank you.

-

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.1.0]$ bin/mysql -uroot -S /tmp/mysql.sock  test < ../mysql-8.0.34/mysql-bug-data-111891/init.sql
ERROR 1822 (HY000) at line 226: Failed to add the foreign key constraint. Missing index for constraint 'table12_ibfk_1' in the referenced table 'table86'
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.1.0]$ bin/mysql -uroot -S /tmp/mysql.sock  test < ../mysql-8.0.34/mysql-bug-data-111891/case2.sql
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.1.0]$

- debug

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.1.0-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.1.0]$ bin/mysql -uroot -S /tmp/mysql.sock  test < ../mysql-8.0.34/mysql-bug-data-111891/init.sql
ERROR 1822 (HY000) at line 226: Failed to add the foreign key constraint. Missing index for constraint 'table12_ibfk_1' in the referenced table 'table86'
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.1.0]$ bin/mysql -uroot -S /tmp/mysql.sock  test < ../mysql-8.0.34/mysql-bug-data-111891/case2.sql

regards,
Umesh

Sorry, I've attached another case.sql that can replicate the problem

Attachment: case18.sql (application/octet-stream, text), 61.01 KiB.

Thank you for the feedback.
Observed that 8.1.0 debug build is affected.

#0  0x00007ff508a2aaa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000004076b77 in my_write_core(int) ()
#2  0x000000000330766e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007ff506d75387 in raise () from /lib64/libc.so.6
#5  0x00007ff506d76a78 in abort () from /lib64/libc.so.6
#6  0x00007ff506d6e1a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007ff506d6e252 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000003195abc in Table_ref::set_tableno(unsigned int) ()
#9  0x00000000031ec238 in JOIN::setup_semijoin_materialized_table(JOIN_TAB*, unsigned int, POSITION*, POSITION*) ()
#10 0x000000000317662d in JOIN::get_best_combination() ()
#11 0x000000000317f659 in JOIN::make_join_plan() ()
#12 0x00000000031805d4 in JOIN::optimize(bool) ()
#13 0x00000000031e6c87 in Query_block::optimize(THD*, bool) ()
#14 0x000000000325c579 in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#15 0x00000000031e68af in Sql_cmd_dml::execute_inner(THD*) ()
#16 0x00000000031f03d3 in Sql_cmd_dml::execute(THD*) ()
#17 0x0000000003191136 in mysql_execute_command(THD*, bool) ()
#18 0x0000000003192579 in dispatch_sql_command(THD*, Parser_state*) ()
#19 0x0000000003193b7f in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#20 0x0000000003195763 in do_command(THD*) ()
#21 0x00000000032f967c in handle_connection ()
#22 0x000000000482e4e4 in pfs_spawn_thread ()
#23 0x00007ff508a25ea5 in start_thread () from /lib64/libpthread.so.0
#24 0x00007ff506e3db2d in clone () from /lib64/libc.so.6
(gdb) quit

- 8.0.34 debug build affected as well

[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.0.34]$ bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.34-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> \q
Bye
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.0.34]$ bin/mysql -uroot -S /tmp/mysql.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34-debug MySQL Community Server - GPL - Debug

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

 bin/mysql -uroot -S /tmp/mysql.sock test  < ../mysql-8.0.34/mysql-bug-data-111891/init.sql
ERROR 1822 (HY000) at line 226: Failed to add the foreign key constraint. Missing index for constraint 'table12_ibfk_1' in the referenced table 'table86'
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.0.34]$ bin/mysql -uroot -S /tmp/mysql.sock test  < ../mysql-8.0.34/case18.sql
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

gdb) bt
#0  0x00007ff338eb0aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x000000000402c28e in my_write_core(int) ()
#2  0x00000000032d235e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007ff3371fb387 in raise () from /lib64/libc.so.6
#5  0x00007ff3371fca78 in abort () from /lib64/libc.so.6
#6  0x00007ff3371f41a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007ff3371f4252 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000003165cc4 in Table_ref::set_tableno(unsigned int) ()
#9  0x00000000031bcad4 in JOIN::setup_semijoin_materialized_table(JOIN_TAB*, unsigned int, POSITION*, POSITION*) ()
#10 0x0000000003146961 in JOIN::get_best_combination() ()
#11 0x000000000314f97d in JOIN::make_join_plan() ()
#12 0x00000000031508c8 in JOIN::optimize(bool) ()
#13 0x00000000031b755f in Query_block::optimize(THD*, bool) ()
#14 0x000000000322d695 in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#15 0x00000000031b7187 in Sql_cmd_dml::execute_inner(THD*) ()
#16 0x00000000031c0beb in Sql_cmd_dml::execute(THD*) ()
#17 0x0000000003161263 in mysql_execute_command(THD*, bool) ()
#18 0x00000000031626a2 in dispatch_sql_command(THD*, Parser_state*) ()
#19 0x0000000003163cd5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#20 0x000000000316596c in do_command(THD*) ()
#21 0x00000000032c437c in handle_connection ()
#22 0x00000000047e1af5 in pfs_spawn_thread ()
#23 0x00007ff338eabea5 in start_thread () from /lib64/libpthread.so.0
#24 0x00007ff3372c3b2d in clone () from /lib64/libc.so.6
(gdb)