MySQL Bugs: #110535: Mysql instance cannot be initialized correctly because autocommit is turned off.

Bug #110535	Mysql instance cannot be initialized correctly because autocommit is turned off.
Submitted:	28 Mar 2023 22:41	Modified:	9 Oct 2023 19:09
Reporter:	shu William	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: SYS Schema	Severity:	S3 (Non-critical)
Version:	8.0.31, 8.0.32	OS:	CentOS (7.9)
Assigned to:		CPU Architecture:	x86 (E5-2678 V3)
Tags:	autocommit, initialized

Description:
mysql version: mysql 8.0.31

[root@mydb ~]# mysql -V
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
scene 1: autocommit=OFF
1.Initialize the mysql instance

[root@mydb ~]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data/
2.Log information:

2023-03-25T16:32:50.775041+08:00 0 [System] [MY-013169] [Server] /mysql/app/mysql-8.0.31-linux-glibc2.17-x86_64-minimal/bin/mysqld (mysqld 8.0.31) initializing of server in progress as process 12421
2023-03-25T16:32:50.800058+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-25T16:32:51.194754+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-03-25T16:32:52.510564+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: BsP/v#Ngv4Zd
2023-03-25T16:32:53.625209+08:00 6 [ERROR] [MY-000061] [Server] 1694  Cannot modify @@session.sql_log_bin inside a transaction.
2023-03-25T16:32:53.627885+08:00 0 [ERROR] [MY-013236] [Server] The designated data directory /mysql/data/3306/data/ is unusable. You can remove all files that the server added to it.
2023-03-25T16:32:53.627936+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-03-25T16:32:55.275093+08:00 0 [System] [MY-010910] [Server] /mysql/app/mysql-8.0.31-linux-glibc2.17-x86_64-minimal/bin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
3.Start the database and log in

[root@mydb ~]# mysql -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
No default sys database found

How to repeat:
scene 2: autocommit=ON
1.Initialize the mysql instance

[root@mydb ~]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data/
2.Log information:

2023-03-25T16:56:42.250746+08:00 0 [System] [MY-013169] [Server] /mysql/app/mysql-8.0.31-linux-glibc2.17-x86_64-minimal/bin/mysqld (mysqld 8.0.31) initializing of server in progress as process 12903
2023-03-25T16:56:42.262931+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-25T16:56:42.673819+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-03-25T16:56:44.004737+08:00 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
3.Start the database and log in

[root@mydb ~]# mysql -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
Four default databases exist

Suggested fix:
Autocommit parameter should not affect the initialization of mysql instance, please ask the staff to make reasonable repairs.

Hi Mr. William,

Thank you for your bug report.

However, insecure initialisation is not recommended by us, as per our Reference Manual.

More important, for the initialisation, you should not set any settings by yourself, manually. You can test it with secure initialisation as well.

Not a bug.

I tried to initialize with --initialize, and I got the same result when autocommit equals OFF or ON.
1. 1.--initialize and autocommit=ON, initialization can be completed normally, and there is no error in the error log, and the default four databases exist.
2.-initialize and autocommit = off, initialization can be completed, and there is an error in the error log. By default, only three databases exist.

[root@mydb ~]# mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data/
[root@mydb ~]#
[root@mydb ~]# log
2023-04-03T11:00:05.998286+08:00 0 [System] [MY-013169] [Server] /mysql/app/mysql-8.0.31-linux-glibc2.17-x86_64-minimal/bin/mysqld (mysqld 8.0.31) initializing of server in progress as process 3185
2023-04-03T11:00:06.009875+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-03T11:00:06.408290+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-03T11:00:07.429903+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rsQ&XBMip9jY
2023-04-03T11:00:08.482970+08:00 6 [ERROR] [MY-000061] [Server] 1694  Cannot modify @@session.sql_log_bin inside a transaction.
2023-04-03T11:00:08.485490+08:00 0 [ERROR] [MY-013236] [Server] The designated data directory /mysql/data/3306/data/ is unusable. You can remove all files that the server added to it.
2023-04-03T11:00:08.485519+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-04-03T11:00:09.437634+08:00 0 [System] [MY-010910] [Server] /mysql/app/mysql-8.0.31-linux-glibc2.17-x86_64-minimal/bin/mysqld: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL.
^C

mysql> alter user root@'localhost' identified by 'root';
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

Hi,

Once again .....

When initialising a new data directory, you should NOT set any configuration variables manually.

Not a bug.

Hello shu William,

Thank you for the report and feedback.

Sincerely,
Umesh

Thank you Shane, correcting the Synopsis from "Mysql instance cannot be initialized correctly because autocommit is turned on." to "Mysql instance cannot be initialized correctly because autocommit is turned off".

Posted by developer:
 
Added the following note to the MySQL Server 8.2.0 release notes:

MySQL Server did not initialize correctly, using --initialize or --initialize-insecure, if autocommit was
set to OFF. The sys schema was not present.
As of this release, non-default values of autocommit are ignored for this scenario.

Thank you, Edward.