|
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:
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:
+----------+
|
| a |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
Notice, a zero TIME value was inserted.
Now I check what was written to the binary log:
+--------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+
|
| 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:
+------+
|
| 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.
|