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

[28 Mar 2023 22:41] shu William
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.
[29 Mar 2023 12:36] MySQL Verification Team
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.
[3 Apr 2023 3:04] shu William
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)
[3 Apr 2023 12:35] MySQL Verification Team
Hi,

Once again .....

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

Not a bug.
[4 Apr 2023 9:17] MySQL Verification Team
Hello shu William,

Thank you for the report and feedback.

Sincerely,
Umesh
[4 Apr 2023 10:31] MySQL Verification Team
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".
[9 Oct 2023 19:09] Edward Gilmore
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.
[10 Oct 2023 9:17] MySQL Verification Team
Thank you, Edward.