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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.2.14, 10.3.5
    • Temporal Types
    • None

    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

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description I start the server as follows:
            {noformat}
            ./mysqld --log-bin --binlog-format=statement
            {noformat}

            Then I start the client and reset the master to start from a new binary log:
            {code:sql}
            RESET MASTER;
            {code}


            Now I run this client program, which bind a bad {{DATETIME}} value with an out-of-range year value:
            {code:cpp}
            #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;
            }
            {code}


            Now I check what's in the table {{t1}}:
            {code:sql}
            SELECT * FROM t1;
            {code}
            {noformat}
            +----------+
            | a |
            +----------+
            | 00:00:00 |
            +----------+
            {noformat}
            Notice, a zero {{TIME}} value was inserted.

            Now I check what was written to the binary log:
            {code:sql}
            SHOW BINLOG EVENTS;
            {code}
            {noformat}
            +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
            | 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 */ |
            +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
            {noformat}


            Notice, it prints:
            {code:sql}
            INSERT INTO t1 VALUES (CAST('' AS TIME))
            {code}

            Now I replay queries from this binary log:
            {code:sql}
            SET sql_mode='';
            CREATE OR REPLACE TABLE t1 (a TIME);
            INSERT INTO t1 VALUES (CAST('' AS TIME));
            {code}

            And check what's in the table {{t1}} again:
            {code:sql}
            SELECT * FROM t1;
            {code}
            {noformat}
            +------+
            | a |
            +------+
            | NULL |
            +------+
            {noformat}

            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.
            		I start the server as follows:
            {noformat}
            ./mysqld --log-bin --binlog-format=statement
            {noformat}

            Then I start the client and reset the master to start from a new binary log:
            {code:sql}
            RESET MASTER;
            {code}


            Now I run this client program, which binds a bad {{DATETIME}} value with an out-of-range year value:
            {code:cpp}
            #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;
            }
            {code}


            Now I check what's in the table {{t1}}:
            {code:sql}
            SELECT * FROM t1;
            {code}
            {noformat}
            +----------+
            | a |
            +----------+
            | 00:00:00 |
            +----------+
            {noformat}
            Notice, a zero {{TIME}} value was inserted.

            Now I check what was written to the binary log:
            {code:sql}
            SHOW BINLOG EVENTS;
            {code}
            {noformat}
            +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
            | 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 */ |
            +--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
            {noformat}


            Notice, it prints:
            {code:sql}
            INSERT INTO t1 VALUES (CAST('' AS TIME))
            {code}

            Now I replay queries from this binary log:
            {code:sql}
            SET sql_mode='';
            CREATE OR REPLACE TABLE t1 (a TIME);
            INSERT INTO t1 VALUES (CAST('' AS TIME));
            {code}

            And check what's in the table {{t1}} again:
            {code:sql}
            SELECT * FROM t1;
            {code}
            {noformat}
            +------+
            | a |
            +------+
            | NULL |
            +------+
            {noformat}

            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.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.5 [ 22905 ]
            Fix Version/s 10.2.14 [ 22911 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.3 [ 22126 ]
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 85545 ] MariaDB v4 [ 153781 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.