MySQL Bugs: #99770: Confusing mysql.session user in mysql.user table

Bug #99770	Confusing mysql.session user in mysql.user table
Submitted:	3 Jun 2020 22:18	Modified:	4 Jun 2020 15:05
Reporter:	Venkatesh Duggirala	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S2 (Serious)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
AFAIU,  mysql_system_tables_fix.sql supposed to be used by mysql_upgrade to repair/adjust
any system_tables during upgrade. 
 
That file contains an insert query that inserts mysql.session entry in mysql.user table.

Why is it only mysql_system_tables_fix.sql ?? 

So when you start a server (Eg: 5.7.25 version), it wont contain "mysql.session" entry but if you execute mysql_upgrade , you get this entry in mysql.user table. 

That seems to be wrong that entry exists only after mysql_upgrade.

Please do not use ./mtr --start to verify the issue..because because I see a workaround (or a hack ?) in mysql-test-run.pl script is inserting these entries while starting the server. 

In the real time, no one uses ./mtr to start a server.  

This is not a problem for 8.0 because the entries are in scripts/mysql_system_users.sql
That's why 8.0's mysql-test-run.pl does not have that hack. 

 (Looks like something went wrong while back porting the patch from 8.0 to 5.7) 

 

How to repeat:

Start server (5.7.25) with normal procedure (not using ./mtr --start)
Observer that there is no mysql.session in mysql.user table
Execute mysql_upgrade tool
Observer that there is entry for mysql.session in mysql.user table

Suggested fix:
It does not sound right to have those entries only after mysql_upgrade tool. 

May be add them in mysql_system_tables_data.sql  or some other *proper* place where a simple server start also contains these entries.

Dear Venkat,

Thank you for the bug report and feedback.
However I'm not able to reproduce this reported issue with the binary tar builds(5.7.25 and even tried latest GA). Could you please confirm which exact build you are trying(if source build then full cmake options used etc)? Thank you.

-

rm -rf 99770/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/99770 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/99770 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/99770/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 &

cat docs/INFO_SRC
commit: b15f075d6ab0ca08be51a9dbf3113fc551f327e4
date: 2018-12-21 16:01:17 +0530
build-date: 2018-12-21 11:39:54 +0100
short: b15f075
branch: mysql-5.7.25-release

MySQL source 5.7.25

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

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

-
cat docs/INFO_SRC
commit: 2c28eddc02bb0fd9c7d8330bc161cecd53dd3c51
date: 2020-03-19 23:54:11 +0530
build-date: 2020-03-23 18:35:31 +0100
short: 2c28edd
branch: mysql-5.7.30-release

MySQL source 5.7.30

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

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

regards,
Umesh

I was using mysql-install-db way (the 5.6 way), 
now I realized that it is not supported for 5.7+. 

I see https://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html that it is deprecated and not supported and I should use --initialize instead.

Sorry, my bad. Closing the bug.

Closing bug