MySQL Bugs: #111303: MySQL Server crashes when executing query

Bug #111303	MySQL Server crashes when executing query
Submitted:	6 Jun 2023 20:58	Modified:	19 Jul 2023 21:51
Reporter:	Yu Liang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S6 (Debug Builds)
Version:	8.0.33	OS:	Ubuntu (20.04 LTS)
Assigned to:		CPU Architecture:	x86 (Intel(R) Core(TM) i7-10700)

Description:
The latest version of the MySQL Server (version 8.0.33) (git commit hash: ea7087d8850) crashes when executing the following query:

```sql
mysql> create table v0(c1 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE OR REPLACE DEFINER = 'role_abc' VIEW any_view_name AS TABLE v0 WITH CASCADED CHECK OPTION ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP ROLE IF EXISTS 'role_abc' ;
mysqld: /home/mysql/mysql-server/sql/auth/sql_user.cc:2557: bool check_set_user_id_priv(THD *, const LEX_USER *, const std::string &): Assertion `0' failed.
2023-06-06T20:54:38Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=1d55d4671b7469352fc317efb0121b604c26443d
Thread pointer: 0xfffef4001040
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 = ffff80268538 thread_stack 0x100000
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x60) [0x4e6bc30]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(print_fatal_signal(int)+0x340) [0x1a89320]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(handle_fatal_signal+0x16c) [0x1a896dc]
linux-vdso.so.1(__kernel_rt_sigreturn+0) [0xffff9aec17a0]
/lib/aarch64-linux-gnu/libc.so.6(gsignal+0xe0) [0xffff9a535d78]
/lib/aarch64-linux-gnu/libc.so.6(abort+0x114) [0xffff9a522aac]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d490) [0xffff9a52f490]
/lib/aarch64-linux-gnu/libc.so.6(+0x2d4f4) [0xffff9a52f4f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(check_set_user_id_priv(THD*, LEX_USER const*, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)+0x7e8) [0x1caff20]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x1cb4608]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(mysql_drop_user(THD*, List<LEX_USER>&, bool, bool)+0x36c) [0x1cb674c]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(Sql_cmd_drop_role::execute(THD*)+0x274) [0x2558368]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x281c) [0x162bcd8]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x1124) [0x16252c0]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x5434) [0x161e224]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld(do_command(THD*)+0xb8c) [0x16228f4]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x1a59a64]
/home/mysql/mysql-server/bld/runtime_output_directory/mysqld() [0x655c1a0]
/lib/aarch64-linux-gnu/libpthread.so.0(+0x7624) [0xffff9ae67624]
/lib/aarch64-linux-gnu/libc.so.6(+0xd149c) [0xffff9a5d349c]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (fffef41f0e30): DROP ROLE IF EXISTS 'role_abc'
Connection ID (thread ID): 8
Status: NOT_KILLED
```

How to repeat:
Steps to repeat the crash:
1. Download the MySQL Server source code from the official github repo: `https://github.com/mysql/mysql-server`
2. Checkout to the latest mysql released version: 8.0.33 (hash: `ea7087d8850`)
3. Compile MySQL using the command: 

```
mkdir -p bld
cd bld
cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../boost -DWITH_UNIT_TESTS=OFF -DUSE_LD_GOLD=1 -DWITH_DEBUG=1
make
```

4. Run the MySQL Server with command: 

```
./bin/mysqld --basedir=$(pwd) --datadir=$(pwd)/data_all/ori_data --port=7000  --socket=/tmp/mysql_0.sock --mysqlx=OFF --performance_schema=OFF
```

5. Run the MySQL Client with the PoC:

```
./bin/mysql --port=7000 --user=root --socket=/tmp/mysql_0.sock < poc_0.sql
```

where `poc_0.sql` is:

```sql
drop database if exists test123;
create database test123;
use test123;
create table v0(c1 INT);
CREATE OR REPLACE DEFINER = 'role_abc' VIEW any_view_name AS TABLE v0 WITH CASCADED CHECK OPTION ;
DROP ROLE IF EXISTS 'role_abc' ;
```

Suggested fix:
The server should continue running instead of crashing.

Hello Yu Liang,

Thank you for the report and test case.
Observed that 8.0.33 debug build is affected.

regards,
Umesh

- 8.0.33 debug build affected

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

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
drop database if exists test123;
Warnings:
Note    1008    Can't drop database 'test123'; database doesn't exist
create database test123;
use test123;
create table v0(c1 INT);
CREATE OR REPLACE DEFINER = 'role_abc' VIEW any_view_name AS TABLE v0 WITH CASCADED CHECK OPTION ;
Warnings:
Note    1449    The user specified as a definer ('role_abc'@'%') does not exist
DROP ROLE IF EXISTS 'role_abc' ;
[ 50%] main.bug111303                            [ fail ]
        Test ended at 2023-06-07 07:12:44

CURRENT_TEST: main.bug111303
mysqltest: At line 6: Query 'DROP ROLE IF EXISTS 'role_abc' ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

- 8.0.33 release build not affected

 ./mtr bug111303 --nocheck-testcases
Logging: ./mtr  bug111303 --nocheck-testcases
MySQL Version 8.0.33
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
 - WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.33/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
drop database if exists test123;
Warnings:
Note    1008    Can't drop database 'test123'; database doesn't exist
create database test123;
use test123;
create table v0(c1 INT);
CREATE OR REPLACE DEFINER = 'role_abc' VIEW any_view_name AS TABLE v0 WITH CASCADED CHECK OPTION ;
Warnings:
Note    1449    The user specified as a definer ('role_abc'@'%') does not exist
DROP ROLE IF EXISTS 'role_abc' ;
Warnings:
Warning 4005    User 'role_abc'@'%' is referenced as a definer account in a view.
Note    3162    Authorization ID 'role_abc'@'%' does not exist.
[ 50%] main.bug111303                            [ pass ]     11
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.011 of 7 seconds executing testcases

Completed: All 2 tests were successful.

Documented fix as follows in the MySQL 8.0.35 and 8.2.0 changelogs:

    CREATE ROLE and DROP ROLE statements were not handled correctly
    when checking for orphaned SQL objects.

Closed.