Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15289

Binding an out-of-range DATETIME value in binary protocol breaks replication

    XMLWordPrintable

    Details

      Description

      I start the server as follows:

      ./mysqld --log-bin --binlog-format=statement
      

      Then I start the client and reset the master to start from a new binary log:

      RESET MASTER;
      

      Now I run this client program, which binds a bad DATETIME value with an out-of-range year value:

      #include <stdio.h>
      #include <string.h>
      #include <unistd.h>
      #include <mysql.h>
       
      static void myquery(MYSQL *mysql, int rc)
      {
        if (rc)
        {
          printf("%s\n", mysql_error(mysql));
          _exit(1);
        }
      }
       
      static void check_execute(MYSQL_STMT *stmt, int rc)
      {
        if (stmt && mysql_stmt_errno(stmt))
        {
          printf("[MySQL][%d] %s\n", mysql_stmt_errno(stmt),
                  mysql_stmt_error(stmt));
          _exit(0);
        }
      }
       
      static void test_mdev12579(MYSQL *mysql)
      {
        MYSQL_STMT *stmt= mysql_stmt_init(mysql);
        MYSQL_BIND bind[1];
        MYSQL_TIME tm[1];
        int rc;
       
        rc= mysql_query(mysql, "SET sql_mode=''");
        myquery(mysql, rc);
       
        rc= mysql_query(mysql, "CREATE OR REPLACE TABLE t1 (a TIME)");
        myquery(mysql, rc);
       
        rc= mysql_stmt_prepare(stmt, "INSERT INTO t1 VALUES (CAST(? AS TIME))", -1);
        myquery(mysql, rc);
       
        memset(&bind, 0, sizeof(bind));
        bind[0].buffer_type= MYSQL_TYPE_DATETIME;
        bind[0].buffer= &tm[0];
        mysql_stmt_bind_param(stmt, bind);
       
        tm[0].year= 12004; tm[0].month= 11; tm[0].day= 10;
        tm[0].hour= 12; tm[0].minute= 30; tm[0].second= 30;
        tm[0].second_part= 0; tm[0].neg= 0;
       
        rc= mysql_stmt_execute(stmt);
        check_execute(stmt, rc);
       
        mysql_stmt_close(stmt);
      }
       
       
      int main()
      {
        MYSQL mysql;
        mysql_init(&mysql);
        mysql_real_connect(&mysql, "localhost", "root", "", "test",
                           0, "/tmp/mysql.sock", 0);
       
        test_mdev12579(&mysql);
        mysql_close(&mysql);
        return 0;
      }
      

      Now I check what's in the table t1:

      SELECT * FROM t1;
      

      +----------+
      | a        |
      +----------+
      | 00:00:00 |
      +----------+
      

      Notice, a zero TIME value was inserted.

      Now I check what was written to the binary log:

      SHOW BINLOG EVENTS;
      

      +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
      | Log_name           | Pos | Event_type        | Server_id | End_log_pos | Info                                                 |
      +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
      | mariadb-bin.000001 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.13-MariaDB-debug-log, Binlog ver: 4 |
      | mariadb-bin.000001 | 256 | Gtid_list         |         1 |         285 | []                                                   |
      | mariadb-bin.000001 | 285 | Binlog_checkpoint |         1 |         330 | mariadb-bin.000001                                   |
      | mariadb-bin.000001 | 330 | Gtid              |         1 |         372 | GTID 0-1-1                                           |
      | mariadb-bin.000001 | 372 | Query             |         1 |         474 | use `test`; CREATE OR REPLACE TABLE t1 (a TIME)      |
      | mariadb-bin.000001 | 474 | Gtid              |         1 |         516 | BEGIN GTID 0-1-2                                     |
      | mariadb-bin.000001 | 516 | Query             |         1 |         623 | use `test`; INSERT INTO t1 VALUES (CAST('' AS TIME)) |
      | mariadb-bin.000001 | 623 | Xid               |         1 |         654 | COMMIT /* xid=5 */                                   |
      +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
      

      Notice, it prints:

      INSERT INTO t1 VALUES (CAST('' AS TIME))
      

      Now I replay queries from this binary log:

      SET sql_mode='';
      CREATE OR REPLACE TABLE t1 (a TIME);
      INSERT INTO t1 VALUES (CAST('' AS TIME));
      

      And check what's in the table t1 again:

      SELECT * FROM t1;
      

      +------+
      | a    |
      +------+
      | NULL |
      +------+
      

      Notice, it inserted NULL instead of the zero value 00:00:00.
      So the table on the slave side will have a different data comparing to the master.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: