mysql:8.0.28: cannot boot server with autocommit=0

[wtnb75]

mysql:8.0.28 (NG)

# docker run -ti -e MYSQL_RANDOM_ROOT_PASSWORD=1 mysql:8.0.28 --autocommit=0
2022-01-26 07:09:45+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.28-1debian10 started.
2022-01-26 07:09:45+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-01-26 07:09:45+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.28-1debian10 started.
2022-01-26 07:09:45+00:00 [Note] [Entrypoint]: Initializing database files
2022-01-26T07:09:45.498162Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28) initializing of server in progress as process 42
2022-01-26T07:09:45.510419Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-26T07:09:45.986800Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-26T07:09:47.489186Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-01-26T07:09:48.490082Z 6 [ERROR] [MY-000061] [Server] 1694  Cannot modify @@session.sql_log_bin inside a transaction.
2022-01-26T07:09:48.493835Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
2022-01-26T07:09:48.493906Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-01-26T07:09:50.256083Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.

mysql:8.0.27 (OK)

# docker run -ti -e MYSQL_RANDOM_ROOT_PASSWORD=1 mysql:8.0.27 --autocommit=0
2022-01-26 07:09:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-01-26 07:09:53+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-01-26 07:09:53+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-01-26 07:09:53+00:00 [Note] [Entrypoint]: Initializing database files
2022-01-26T07:09:53.902070Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 43
2022-01-26T07:09:53.914265Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-26T07:09:54.405108Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-26T07:09:55.476839Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-01-26T07:09:55.476899Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-01-26T07:09:55.529705Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-01-26 07:09:58+00:00 [Note] [Entrypoint]: Database files initialized
2022-01-26 07:09:58+00:00 [Note] [Entrypoint]: Starting temporary server
mysqld will log errors to /var/lib/mysql/7f03fb4b8b17.err
mysqld is running as pid 92
2022-01-26 07:09:59+00:00 [Note] [Entrypoint]: Temporary server started.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
2022-01-26 07:10:02+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: waevohth3ahZiciageF8jookahmoox5m
  :

environment = Docker Desktop for mac 4.4.2 (73305)

# docker version
Client:
 Cloud integration: v1.0.22
 Version:           20.10.12
 API version:       1.41
 Go version:        go1.16.12
 Git commit:        e91ed57
 Built:             Mon Dec 13 11:46:56 2021
 OS/Arch:           darwin/amd64
 Context:           default
 Experimental:      true

Server: Docker Engine - Community
 Engine:
  Version:          20.10.12
  API version:      1.41 (minimum version 1.12)
  Go version:       go1.16.12
  Git commit:       459d0df
  Built:            Mon Dec 13 11:43:56 2021
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.4.12
  GitCommit:        7b11cfaabd73bb80907dd23182b9347b4245eb5d
 runc:
  Version:          1.0.2
  GitCommit:        v1.0.2-0-g52b36a2
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

[wglambert]

I'm able to reproduce on Linux so it's not related to Docker for Mac

$ docker run -ti -e MYSQL_RANDOM_ROOT_PASSWORD=1 mysql:8.0.28 --autocommit=0
Unable to find image 'mysql:8.0.28' locally
8.0.28: Pulling from library/mysql
Digest: sha256:d0507b008897c39f6cbc76285af1171d4551988475e00e91344060023cd9c553
Status: Downloaded newer image for mysql:8.0.28
2022-01-26 17:29:51+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.28-1debian10 started.
2022-01-26 17:29:52+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-01-26 17:29:52+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.28-1debian10 started.
2022-01-26 17:29:52+00:00 [Note] [Entrypoint]: Initializing database files
2022-01-26T17:29:52.318339Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.28) initializing of server in progress as process 41
2022-01-26T17:29:52.331596Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-26T17:29:52.975994Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-26T17:29:54.157422Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-01-26T17:29:54.787451Z 6 [ERROR] [MY-000061] [Server] 1694  Cannot modify @@session.sql_log_bin inside a transaction.
2022-01-26T17:29:54.789803Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
2022-01-26T17:29:54.789846Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-01-26T17:29:56.012966Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.

And I don't see anything in the release notes that changed autocommit https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

[tianon]

I'm not sure what's broken, but looking at the details I'm actually a little surprised this ever worked? 😬 😅

I think most of the SQL snippets in our entrypoint script are implicitly assuming autocommit is enabled, so I don't doubt that there's something we need to change here.

[tianon]

I guess the simplest solution here would be to add an explicit SET autocommit=1; to all our hand-crafted SQL snippets?

[bromike]

We also noticed this issue while upgrading from MySQL 5.7 to MySQL 8.0. Since MySQL 5.7 is becoming deprecated in October 2023, this becomes a bigger concern.

[LaurentGoderre]

I'm reading the docs and maybe committing our custom SQL snippets manually might be a non-invasive way of doing it. Let me test it out.

https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html