MySQL Bugs: #109917: server crash when execute create table with check constraint in ps mode

Bug #109917	server crash when execute create table with check constraint in ps mode
Submitted:	3 Feb 11:38	Modified:	4 Feb 8:15
Reporter:	dongsheng zhao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S6 (Debug Builds)
Version:	8.0, 8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	check constraint, crash, prepare statement

Description:
This WL#9384 add parameters type check before execute prepare-statement, through function Prepared_statement::check_parameter_types() locate in commit https://github.com/apecloud/wesql-server/commit/67c3c70e4895874d43434f1df556f9f30d781b48. As of MySQL 8.0.16, CREATE TABLE permits the core features of table and column CHECK constraints, but the CHECK constraints now allowed to execute in prepare-statement mode. The parameter type is MYSQL_TYPE_INVALID with query like 'PREPARE stmt FROM 'CREATE TABLE t1 (id INT CHECK(id != ?))';', lead to assert failed in Prepared_statement::check_parameter_types() when execute it.

#0  0x00007f5f73080aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000004ed03fa in my_write_core (sig=6) at /u01/mysql/mysys/stacktrace.cc:295
#2  0x0000000003af7458 in handle_fatal_signal (sig=6) at /u01/mysql/sql/signal_handler.cc:202
#3  <signal handler called>
#4  0x00007f5f717d9387 in raise () from /lib64/libc.so.6
#5  0x00007f5f717daa78 in abort () from /lib64/libc.so.6
#6  0x00007f5f717d21a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f5f717d2252 in __assert_fail () from /lib64/libc.so.6
#8  0x00000000039152b9 in Prepared_statement::check_parameter_types (this=0x7f5e8c5ae930) at /u01/mysql/sql/sql_prepare.cc:2943
#9  0x0000000003915562 in Prepared_statement::execute_loop (this=0x7f5e8c5ae930, expanded_query=0x7f5f4c4f3120, open_cursor=false) at /u01/mysql/sql/sql_prepare.cc:3023
#10 0x00000000039129c4 in mysql_sql_stmt_execute (thd=0x7f5e8c508340) at /u01/mysql/sql/sql_prepare.cc:1964
#11 0x00000000038cd0db in mysql_execute_command (thd=0x7f5e8c508340, first_level=true) at /u01/mysql/sql/sql_parse.cc:3360
#12 0x00000000038d3615 in dispatch_sql_command (thd=0x7f5e8c508340, parser_state=0x7f5f4c4f47f0) at /u01/mysql/sql/sql_parse.cc:5472
#13 0x00000000038c95f2 in dispatch_command (thd=0x7f5e8c508340, com_data=0x7f5f4c4f5940, command=COM_QUERY) at /u01/mysql/sql/sql_parse.cc:2009
#14 0x00000000038c7674 in do_command (thd=0x7f5e8c508340) at /u01/mysql/sql/sql_parse.cc:1407
#15 0x0000000003ae23f9 in handle_connection (arg=0x104283b0) at /u01/mysql/sql/conn_handler/connection_handler_per_thread.cc:302
#16 0x00000000057ca8f4 in pfs_spawn_thread (arg=0x104cd910) at /u01/mysql/storage/perfschema/pfs.cc:2942
#17 0x00007f5f7307bea5 in start_thread () from /lib64/libpthread.so.0
#18 0x00007f5f718a1b0d in clone () from /lib64/libc.so.6

How to repeat:
The following test case can repeat:

```
CREATE DATABASE zds;
USE zds;
SET @v = 10;
PREPARE stmt FROM 'CREATE TABLE t1 (id INT CHECK(id != ?))';
EXECUTE stmt using @v;
```

Suggested fix:
Check if prepare-statement contain placeholder in CHECK constraints or not, report not-allowed error if find.

Hello zhao,

Thank you for the report and test case.
I confirmed that 8.0.32 debug build is affected where as release build complains "ERROR 3815 (HY000): An expression of a check constraint 't1_chk_1' contains disallowed function.". Could you please confirm whether you are seeing this in debug build or release build? Thank you.

- 8.0.32 release build

 ./mtr bug109917
Logging: ./mtr  bug109917
MySQL Version 8.0.32
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.32/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] main.bug109917                            [ fail ]
        Test ended at 2023-02-03 13:03:23

CURRENT_TEST: main.bug109917
mysqltest: At line 5: Query 'EXECUTE stmt using @v' failed.
ERROR 3815 (HY000): An expression of a check constraint 't1_chk_1' contains disallowed function.

-- debug build
./mtr bug109917 --debug-server
Logging: ./mtr  bug109917 --debug-server
MySQL Version 8.0.32
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.32/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] main.bug109917                            [ fail ]

.
Thread 1 (Thread 0x7ff7002f8700 (LWP 6595)):
#0  0x00007ff7217bbaa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000003fce8c8 in my_write_core(int) ()
#2  0x00000000032810c2 in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007ff71fc7c387 in raise () from /lib64/libc.so.6
#5  0x00007ff71fc7da78 in abort () from /lib64/libc.so.6
#6  0x00007ff71fc751a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007ff71fc75252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000314065b in Prepared_statement::check_parameter_types() ()
#9  0x0000000003140b4e in Prepared_statement::execute_loop(THD*, String*, bool) ()
#10 0x00000000031410f2 in mysql_sql_stmt_execute(THD*) ()
#11 0x000000000310e49e in mysql_execute_command(THD*, bool) ()
#12 0x00000000031128c6 in dispatch_sql_command(THD*, Parser_state*) ()
#13 0x0000000003113dd9 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#14 0x000000000311596d in do_command(THD*) ()
#15 0x000000000327312c in handle_connection ()
#16 0x00000000047fb036 in pfs_spawn_thread ()
#17 0x00007ff7217b6ea5 in start_thread () from /lib64/libpthread.so.0
#18 0x00007ff71fd44b0d in clone () from /lib64/libc.so.6

regards,
Umesh

I see this in debug build. And i try it in release build, which report "ERROR 3815 (HY000): An expression of a check constraint 't1_chk_1' contains disallowed function.", as you say.

Thank you for confirming.
I'm changing severity to debug only issue for now.

Sincerely,
Umesh