| Bug #117530 | create talbe start transaction with comments will result in an error | ||
|---|---|---|---|
| Submitted: | 20 Feb 9:16 | Modified: | 25 Feb 7:51 |
| Reporter: | chengwu yu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[25 Feb 7:51]
MySQL Verification Team
Hi,
--comment is deprecated, please do not use it.
arhimed@Mac ~ % mysql -uroot --comments
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.41 Homebrew
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
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> use test;
Database changed
mysql> DELIMITER /*!*/;
mysql> BEGIN /*!*/;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t(id int) START TRANSACTION
-> /*!*/;
Query OK, 0 rows affected (0.03 sec)
mysql> # asbc
ERROR 3977 (HY000): Only BINLOG INSERT, COMMIT and ROLLBACK statements are allowed after CREATE TABLE with START TRANSACTION statement.
mysql> -- asbc
ERROR 3977 (HY000): Only BINLOG INSERT, COMMIT and ROLLBACK statements are allowed after CREATE TABLE with START TRANSACTION statement.
mysql>
[6 May 14:28]
Tsubasa Tanaka
MySQL 8.4 and later, --comments is ON by default, therefore, `mysqlbinlog -v` + `mysql` to replay binlog is affected by this problem. Workaround is "Not using -v with mysqlbinlog". How to repeat: ``` $ docker run -d -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" --hostname=node1 --name=node1 container-registry.oracle.com/mysql/community-server:8.4.5 $ mysql -h node1 -uroot CREATE DATABASE d1; CREATE TABLE d1.t1 (num int PRIMARY KEY); INSERT INTO d1.t1 VALUES (1); CREATE TABLE d1.t2 AS SELECT * FROM d1.t1; $ mysqlbinlog -v -R -h node1 -uroot binlog.000002 > /tmp/117530.txt ### Create new mysqld $ docker run -d -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" --hostname=node2 --name=node2 container-registry.oracle.com/mysql/community-server:8.4.5 $ mysql -h node2 < /tmp/117530.txt ERROR 3977 (HY000) at line 105: Only BINLOG INSERT, COMMIT and ROLLBACK statements are allowed after CREATE TABLE with START TRANSACTION statement. ```
Description: when connect mysql with --comments, create talbe start transaction with comments will result in an error:ERROR 3977 (HY000): Only BINLOG INSERT, COMMIT and ROLLBACK statements are allowed after CREATE TABLE with START TRANSACTION statement. How to repeat: [root@kscvresv148 ~]# mysql -ubackup -p --comments Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. 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> use test; Database changed mysql> DELIMITER /*!*/; mysql> BEGIN /*!*/; Query OK, 0 rows affected (0.00 sec) mysql> create table t(id int) START TRANSACTION -> /*!*/; Query OK, 0 rows affected (0.02 sec) mysql> # asbc ERROR 3977 (HY000): Only BINLOG INSERT, COMMIT and ROLLBACK statements are allowed after CREATE TABLE with START TRANSACTION statement. mysql> Suggested fix: do not get error when create table START TRANSACTION with comments.