revision-id: 7d1fd1c49c2137163f6b8d274b1d460cbf60e786 (mariadb-10.2.18-8-g7d1fd1c49c2) parent(s): 753117fed043ac3200093c2dc28161ebce2cd04b author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-10-04 21:26:52 +0300 message: MDEV-17098 DATE -> DATETIME replication conversion not working, even in ALL_NON_LOSSY mode Opened up MYSQL_TYPE DATETIME2 <-> DATE conversions for replication. --- mysql-test/suite/rpl/r/rpl_colSize.result | 58 +++++++++++++++++++++++++++ mysql-test/suite/rpl/r/rpl_row_colSize.result | 24 +++++++++++ mysql-test/suite/rpl/t/rpl_colSize.test | 55 +++++++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_colSize.test | 10 +++++ sql/rpl_utility.cc | 28 ++++++++++++- 5 files changed, 173 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_colSize.result b/mysql-test/suite/rpl/r/rpl_colSize.result index 503bf85222e..e08d1142bbb 100644 --- a/mysql-test/suite/rpl/r/rpl_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_colSize.result @@ -189,6 +189,64 @@ t1 CREATE TABLE `t1` ( `d` bit(25) DEFAULT NULL, `e` bit(13) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +Checking NON-LOSSY MYSQL_TYPE_DATE to MYSQL_TYPE_DATETIME +connection master; +DROP TABLE t1; +connection slave; +connection master; +CREATE TABLE t1 (a DATE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATETIME(6); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_date()), +(date_add(current_date(), INTERVAL 1 day)); +SELECT max(a) - min(a) as 'master: one' from t1; +master: one +1 +connection slave; +SELECT date(max(a)) - date(min(a)) as 'slave: 1' from t1; +slave: 1 +1 +Checking LOSSY MYSQL_TYPE_DATETIME to MYSQL_TYPE_DATE +SET @local_saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; +connection master; +DROP TABLE t1; +connection slave; +connection master; +CREATE TABLE t1 (a DATETIME(6)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime(6) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection slave; +ALTER TABLE t1 MODIFY a DATE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)); +SELECT date(max(a)) - date(min(a)) as 'master: one day' from t1; +master: one day +1 +connection slave; +SELECT max(a) - min(a) as 'slave: 1 day' from t1; +slave: 1 day +1 +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; *** Cleanup *** connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_row_colSize.result b/mysql-test/suite/rpl/r/rpl_row_colSize.result index dd324ef7807..ede73cbeedd 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -344,6 +344,30 @@ connection master; RESET MASTER; connection slave; START SLAVE; +Checking MYSQL_TYPE_DATETIME to MYSQL_TYPE_DATE +connection master; +DROP TABLE IF EXISTS t1; +connection slave; +STOP SLAVE; +RESET SLAVE; +CREATE TABLE t1 (a date); +connection master; +CREATE TABLE t1 (a datetime(6)); +RESET MASTER; +INSERT INTO t1 VALUES (current_date()); +connection slave; +START SLAVE; +include/wait_for_slave_sql_error.inc [errno=1677] +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'datetime' to type 'date'' +SELECT COUNT(*) FROM t1; +COUNT(*) +0 +STOP SLAVE; +RESET SLAVE; +connection master; +RESET MASTER; +connection slave; +START SLAVE; connection slave; call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 0 ...e mismatch.* error.* 1535"); call mtr.add_suppression("Slave SQL.*Column 0 of table .test.t1. cannot be converted from type.* error.* 1677"); diff --git a/mysql-test/suite/rpl/t/rpl_colSize.test b/mysql-test/suite/rpl/t/rpl_colSize.test index d6f817af189..dc62bb659f0 100644 --- a/mysql-test/suite/rpl/t/rpl_colSize.test +++ b/mysql-test/suite/rpl/t/rpl_colSize.test @@ -217,6 +217,61 @@ SELECT BIN(a), BIN(b), BIN(c), BIN(d), BIN(e) FROM t1; --replace_result default DEFAULT SHOW CREATE TABLE t1; +# +# Test: Checking MYSQL_TYPE _DATETIME to-from _DATE conversion +# +--echo Checking NON-LOSSY MYSQL_TYPE_DATE to MYSQL_TYPE_DATETIME +connection master; +DROP TABLE t1; +sync_slave_with_master slave; + +connection master; +CREATE TABLE t1 (a DATE); +--replace_result default DEFAULT +SHOW CREATE TABLE t1; + +sync_slave_with_master slave; +ALTER TABLE t1 MODIFY a DATETIME(6); +--replace_result default DEFAULT +SHOW CREATE TABLE t1; + +connection master; +INSERT INTO t1 VALUES (current_date()), + (date_add(current_date(), INTERVAL 1 day)); +SELECT max(a) - min(a) as 'master: one' from t1; + +sync_slave_with_master slave; +SELECT date(max(a)) - date(min(a)) as 'slave: 1' from t1; + + +--echo Checking LOSSY MYSQL_TYPE_DATETIME to MYSQL_TYPE_DATE +SET @local_saved_slave_type_conversions = @@slave_type_conversions; +SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_LOSSY'; + +connection master; +DROP TABLE t1; +sync_slave_with_master slave; + +connection master; +CREATE TABLE t1 (a DATETIME(6)); +--replace_result default DEFAULT +SHOW CREATE TABLE t1; + +sync_slave_with_master slave; +ALTER TABLE t1 MODIFY a DATE; +--replace_result default DEFAULT +SHOW CREATE TABLE t1; + +connection master; +INSERT INTO t1 VALUES (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 day)); +SELECT date(max(a)) - date(min(a)) as 'master: one day' from t1; + +sync_slave_with_master slave; +SELECT max(a) - min(a) as 'slave: 1 day' from t1; + +SET GLOBAL SLAVE_TYPE_CONVERSIONS = @local_saved_slave_type_conversions; + + --echo *** Cleanup *** connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_row_colSize.test b/mysql-test/suite/rpl/t/rpl_row_colSize.test index 21c68b55e92..63659c07a0a 100644 --- a/mysql-test/suite/rpl/t/rpl_row_colSize.test +++ b/mysql-test/suite/rpl/t/rpl_row_colSize.test @@ -160,6 +160,16 @@ let $test_table_slave = CREATE TABLE t1 (a TINYBLOB); let $test_insert = INSERT INTO t1 VALUES ('This is a test.'); source include/test_fieldsize.inc; +# +# Test: Checking MYSQL_TYPE_DATE* fields +# +--echo Checking MYSQL_TYPE_DATETIME to MYSQL_TYPE_DATE +let $test_table_master = CREATE TABLE t1 (a datetime(6)); +let $test_table_slave = CREATE TABLE t1 (a date); +let $test_insert = INSERT INTO t1 VALUES (current_date()); +source include/test_fieldsize.inc; + + connection slave; call mtr.add_suppression("Slave SQL.*Table definition on master and slave does not match: Column 0 ...e mismatch.* error.* 1535"); call mtr.add_suppression("Slave SQL.*Column 0 of table .test.t1. cannot be converted from type.* error.* 1677"); diff --git a/sql/rpl_utility.cc b/sql/rpl_utility.cc index bdf5b7dea80..d94d496fb15 100644 --- a/sql/rpl_utility.cc +++ b/sql/rpl_utility.cc @@ -765,14 +765,38 @@ can_convert_field_to(Field *field, case MYSQL_TYPE_TIME: case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_YEAR: - case MYSQL_TYPE_NEWDATE: case MYSQL_TYPE_NULL: case MYSQL_TYPE_ENUM: case MYSQL_TYPE_SET: case MYSQL_TYPE_TIMESTAMP2: - case MYSQL_TYPE_DATETIME2: case MYSQL_TYPE_TIME2: DBUG_RETURN(false); + case MYSQL_TYPE_NEWDATE: + { + if (field->real_type() == MYSQL_TYPE_DATETIME2) + { + *order_var= -1; + DBUG_RETURN(is_conversion_ok(*order_var, rli)); + } + else + { + DBUG_RETURN(false); + } + } + break; + case MYSQL_TYPE_DATETIME2: + { + if (field->real_type() == MYSQL_TYPE_NEWDATE) + { + *order_var= 1; + DBUG_RETURN(is_conversion_ok(*order_var, rli)); + } + else + { + DBUG_RETURN(false); + } + } + break; } DBUG_RETURN(false); // To keep GCC happy }