revision-id: 7e1bcb6b9ef0c7db3145d08036740b7518bfc5eb (mariadb-10.2.18-8-g7e1bcb6b9ef) parent(s): 753117fed043ac3200093c2dc28161ebce2cd04b author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-10-04 15:32:15 +0300 message: MDEV-17098 DATE -> DATETIME replication conversion not working, even in ALL_NON_LOSSY mode Opened up MYSQL_TYPE DATETIME2 <-> DATE and DATETIME2 <-> TIME conversions for replication. --- mysql-test/suite/rpl/r/rpl_colSize.result | 114 ++++++++++++++++++++++++++ mysql-test/suite/rpl/r/rpl_row_colSize.result | 48 +++++++++++ mysql-test/suite/rpl/t/rpl_colSize.test | 102 +++++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_colSize.test | 16 ++++ sql/rpl_utility.cc | 35 +++++++- 5 files changed, 311 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_colSize.result b/mysql-test/suite/rpl/r/rpl_colSize.result index 503bf85222e..4b19bd15631 100644 --- a/mysql-test/suite/rpl/r/rpl_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_colSize.result @@ -189,6 +189,120 @@ 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()), (current_date() + 1); +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; +Checking NON-LOSSY MYSQL_TYPE_TIME to MYSQL_TYPE_DATETIME +connection master; +DROP TABLE t1; +connection slave; +connection master; +CREATE TABLE t1 (a TIME); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time 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_time()), (current_time() + 1); +SELECT max(a) - min(a) as 'master: one second' from t1; +master: one second +1 +connection slave; +SELECT max(a) - min(a) as 'slave: 1.0 second' from t1; +slave: 1.0 second +1.000000 +Checking LOSSY MYSQL_TYPE_DATETIME to MYSQL_TYPE_TIME +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 TIME; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` time DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +connection master; +INSERT INTO t1 VALUES (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 second)); +SELECT max(a) - min(a) as 'master: one second' from t1; +master: one second +1.000000 +connection slave; +SELECT max(a) - min(a) as 'slave: 1 sec' from t1; +slave: 1 sec +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..97d5de20866 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -344,6 +344,54 @@ 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; +Checking MYSQL_TYPE_DATETIME to MYSQL_TYPE_TIME +connection master; +DROP TABLE IF EXISTS t1; +connection slave; +STOP SLAVE; +RESET SLAVE; +CREATE TABLE t1 (a time); +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 'time'' +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..02654484309 100644 --- a/mysql-test/suite/rpl/t/rpl_colSize.test +++ b/mysql-test/suite/rpl/t/rpl_colSize.test @@ -217,6 +217,108 @@ 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,TIME 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()), (current_date() + 1); +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 Checking NON-LOSSY MYSQL_TYPE_TIME to MYSQL_TYPE_DATETIME +connection master; +DROP TABLE t1; +sync_slave_with_master slave; + +connection master; +CREATE TABLE t1 (a TIME); +--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_time()), (current_time() + 1); +SELECT max(a) - min(a) as 'master: one second' from t1; + +sync_slave_with_master slave; +SELECT max(a) - min(a) as 'slave: 1.0 second' from t1; + +--echo Checking LOSSY MYSQL_TYPE_DATETIME to MYSQL_TYPE_TIME +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 TIME; +--replace_result default DEFAULT +SHOW CREATE TABLE t1; + +connection master; +INSERT INTO t1 VALUES (current_timestamp(6)), (date_add(current_timestamp(6), INTERVAL 1 second)); +SELECT max(a) - min(a) as 'master: one second' from t1; + +sync_slave_with_master slave; +SELECT max(a) - min(a) as 'slave: 1 sec' 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..ebcfe55896e 100644 --- a/mysql-test/suite/rpl/t/rpl_row_colSize.test +++ b/mysql-test/suite/rpl/t/rpl_row_colSize.test @@ -160,6 +160,22 @@ 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; + +--echo Checking MYSQL_TYPE_DATETIME to MYSQL_TYPE_TIME +let $test_table_master = CREATE TABLE t1 (a datetime(6)); +let $test_table_slave = CREATE TABLE t1 (a time); +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..b8fc2bc0b2d 100644 --- a/sql/rpl_utility.cc +++ b/sql/rpl_utility.cc @@ -626,7 +626,8 @@ can_convert_field_to(Field *field, (field->real_type() == MYSQL_TYPE_TIME2 && source_type == MYSQL_TYPE_TIME) || (field->real_type() == MYSQL_TYPE_DATETIME2 && - source_type == MYSQL_TYPE_DATETIME))) || + (source_type == MYSQL_TYPE_DATETIME || + source_type == MYSQL_TYPE_TIME2)))) || /* Conversion from MySQL56 TIMESTAMP(N), TIME(N), DATETIME(N) to the corresponding MariaDB or MySQL55 types is non-lossy. @@ -765,14 +766,40 @@ 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: + DBUG_RETURN(false); 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 || + field->real_type() == MYSQL_TYPE_TIMESTAMP2 || + field->real_type() == MYSQL_TYPE_TIME2) + { + *order_var= 1; + DBUG_RETURN(is_conversion_ok(*order_var, rli)); + } + else + { + DBUG_RETURN(false); + } + } + break; } DBUG_RETURN(false); // To keep GCC happy }