Bug #116266 Binlog record error when delimiter is set to other symbols
Submitted: 29 Sep 2024 14:14 Modified: 10 Oct 2024 11:21
Reporter: xilin Chen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:8.0.39 OS:Any
Assigned to: CPU Architecture:Any

[29 Sep 2024 14:14] xilin Chen
Description:
Delimiter is set to other symbols, and the SQL to create the table contains a semicolon. The SQL is executed successfully in the master database, but the binlog record is incomplete, resulting in a syntax error in the standby database.

How to repeat:

1. Enable binlog and build a master-slave instance

2. Operation in the master database

create database mytest;
use mytest;

mysql> delimiter $
mysql> create table t(id int primary key)engine=innodb /*!50100 partition by range (id) (partition p10000 values less than (10000)); */ $
Query OK, 0 rows affected (0.06 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*/' at line 1

At this time, the master database is executed successfully and the table structure can be viewed normally
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p10000 VALUES LESS THAN (10000) ENGINE = InnoDB) */
1 row in set (0.01 sec)

But the standby database SQL thread is interrupted and reports the following error:
               Last_SQL_Errno: 1064
               Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'mytest'. Query: 'create table t(id int primary key)engine=innodb /*!50100 partition by range (id) (partition p10000 values less than (10000))'

Analyzing the binlog found that the SQL statement record is incorrect, */ is missing after less than (10000))

/*!80001 SET @@session.original_commit_timestamp=1727618027042577*//*!*/;
/*!80014 SET @@session.original_server_version=80039*//*!*/;
/*!80014 SET @@session.immediate_server_version=80039*//*!*/;
SET @@SESSION.GTID_NEXT= 'a80b7e62-7e19-11ef-b823-5254008aa8ea:24'/*!*/;
# at 5327
#240929 21:53:46 server id 200  end_log_pos 5543 CRC32 0xb8d8f8de       Query   thread_id=15    exec_time=1     error_code=0    Xid = 86
use `mytest`/*!*/;
SET TIMESTAMP=1727618026/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table t(id int primary key)engine=innodb /*!50100 partition by range (id) (partition p10000 values less than (10000))
/*!*/;
[30 Sep 2024 15:01] MySQL Verification Team
Hello xilin Chen,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[10 Oct 2024 11:21] xilin Chen
The reason is that the SQL length is truncated according to the semicolon. In fact, the semicolon here is not a separator. The corresponding code is as follows

8.0.39 sql/sql_parse.cc:5267
void dispatch_sql_command(THD *thd, Parser_state *parser_state) {

...

  if (!err) {
    err = parse_sql(thd, parser_state, nullptr);
    if (!err) err = invoke_post_parse_rewrite_plugins(thd, false);

    found_semicolon = parser_state->m_lip.found_semicolon;
    qlen = found_semicolon ? (found_semicolon - thd->query().str)
                           : thd->query().length;
    /*
      We set thd->query_length correctly to not log several queries, when we
      execute only first. We set it to not see the ';' otherwise it would get
      into binlog and Query_log_event::print() would give ';;' output.
    */

    if (!thd->is_error() && found_semicolon && (ulong)(qlen)) {
      thd->set_query(thd->query().str, qlen - 1);
    }
  }
[14 Oct 2024 3:46] huahua xu
Hi all:

This issue has persisted since the earlier versions of MySQL. I suggest that the special comment with ';' should be considered as a syntax error, when expanding the content as real sql statement.


diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 9a5b5c9..e613cf0 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1963,6 +1963,8 @@ static int lex_one_token(Lexer_yystype *yylval, THD *thd) {
         lip->yySkip();
         return (SET_VAR);
       case MY_LEX_SEMICOLON:  // optional line terminator
+        /* The special comments with ';' are a syntax error */
+        if (lip->in_comment == DISCARD_COMMENT) return (ABORT_SYM);
         state = MY_LEX_CHAR;  // Return ';'
         break;
       case MY_LEX_EOL: