revision-id: bd4d159f18a27751a29569c92c731395884e4732 (mariadb-10.2.25-76-gbd4d159f18a) parent(s): 4e02e502f6f9622a84942fd3329241e790acab66 author: Sujatha committer: Sujatha timestamp: 2019-07-18 13:02:45 +0530 message: MDEV-19925: Column ... cannot be converted from type 'varchar(20)' to type 'varchar(20)' Cherry picking: Bug#25135304: RBR: WRONG FIELD LENGTH IN ERROR MESSAGE commit 47bd3f7cf3c8518f62b1580ec65af2ba7ac13b95 Description: ============ In row based replication, when replicating from a table with a field with character set set to UTF8mb3 to the same table with the same field set to character set UTF8mb4 I get a confusing error message: For VARCHAR: VARCHAR(1) 'utf8mb3' to VARCHAR(1) 'utf8mb4' "Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)'" Similar issue with CHAR type as well. Issue with respect to BLOB types: For BLOB: LONGBLOB to TINYBLOB - Error message displays incorrect blob type. "Column 0 of table 'test.t1' cannot be converted from type 'tinyblob' to type 'tinyblob'" Analysis: ========= In Row based replication charset information is not sent as part of metadata from master to slave. For VARCHAR field its character length is converted into equivalent octets/bytes and stored internally. At the time of displaying the data to user it is converted back to original character length. For example: VARCHAR(2)- utf8mb3 is stored as:2*3 = VARCHAR(6) At the time of displaying it to user VARCHAR(6)- charset utf8mb3:6/3= VARCHAR(2). At present the internally converted octect length is sent from master to slave with out providing the charset information. On slave side if the type conversion fails 'show_sql_type' function is used to get the type specific information from metadata. Since there is no charset information is available the filed type is displayed as VARCHAR(6). This results in confused error message. For CHAR fields CHAR(1)- utf8mb3 - CHAR(3) CHAR(1)- utf8mb4 - CHAR(4) 'show_sql_type' function which retrieves type information from metadata uses (bytes/local charset length) to get actual character length. If slave's chaset is 'utf8mb4' then CHAR(3/4)-->CHAR(0) CHAR(4/4)-->CHAR(1). This results in confused error message. Analysis for BLOB type issue: BLOB's length is represented in two forms. 1. Actual length i.e (length < 256) type= MYSQL_TYPE_TINY_BLOB; (length < 65536) type= MYSQL_TYPE_BLOB; ... 2. packlength - The number of bytes used to represent the length of the blob 1- tinyblob 2- blob ... In row based replication only the packlength is written in the binary log. On the slave side this packlength is interpreted as actual length of the blob. Hence the length is always < 256 and the type is displayed as tiny blob. Fix: === For CHAR and VARCHAR fields display their length in bytes for both source and target fields. For target field display the charset information if it is relevant. For blob type changed the code to use the packlength and display appropriate blob type in error message. --- .../suite/rpl/r/rpl_extra_col_master_innodb.result | 4 +- .../suite/rpl/r/rpl_extra_col_master_myisam.result | 4 +- .../suite/rpl/r/rpl_extra_col_slave_innodb.result | 12 +- .../suite/rpl/r/rpl_extra_col_slave_myisam.result | 12 +- .../suite/rpl/r/rpl_row_basic_2myisam.result | 4 +- .../suite/rpl/r/rpl_row_basic_3innodb.result | 4 +- mysql-test/suite/rpl/r/rpl_row_colSize.result | 10 +- .../suite/rpl/r/rpl_type_conv_utf8_utf8mb4.result | 62 +++++++++ .../suite/rpl/t/rpl_type_conv_utf8_utf8mb4.test | 144 +++++++++++++++++++++ sql/rpl_utility.cc | 45 +++++-- .../rpl/r/rpl_extra_col_master_tokudb.result | 4 +- .../rpl/r/rpl_extra_col_slave_tokudb.result | 12 +- .../mysql-test/rpl/r/rpl_row_basic_3tokudb.result | 4 +- 13 files changed, 274 insertions(+), 47 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result b/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result index 3aa7c07a845..497507dd89f 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result @@ -468,7 +468,7 @@ INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5(bytes) latin1)'' *** Drop t10 *** connection master; @@ -510,7 +510,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'tinyblob' to type 'varchar(254)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254(bytes) latin1)'' *** Drop t11 *** connection master; diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result b/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result index 0918364b28e..3907a549e05 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result @@ -468,7 +468,7 @@ INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5(bytes) latin1)'' *** Drop t10 *** connection master; @@ -510,7 +510,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'tinyblob' to type 'varchar(254)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254(bytes) latin1)'' *** Drop t11 *** connection master; diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result b/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result index 456c1c24f8f..1a128f9692c 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_slave_innodb.result @@ -64,7 +64,7 @@ a b c connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10)' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10(bytes))' to type 'char(5(bytes) latin1)'' STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -102,7 +102,7 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'tinyblob' to type 'int(11)'' +Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' *** Drop t3 *** connection master; DROP TABLE t3; @@ -160,7 +160,7 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6(bytes))' to type 'char(5(bytes) latin1)'' *** Drop t5 *** connection master; DROP TABLE t5; @@ -188,7 +188,7 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), ******************************************** connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6(bytes))' to type 'char(5(bytes) latin1)'' *** Drop t6 *** include/rpl_reset.inc connection master; @@ -310,7 +310,7 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5)' to type 'double'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5(bytes))' to type 'double'' *** Drop t10 *** connection master; DROP TABLE t10; @@ -338,7 +338,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254)' to type 'int(11)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254(bytes))' to type 'int(11)'' *** Drop t11 *** connection master; DROP TABLE t11; diff --git a/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result b/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result index 7178a2a78b8..2a14092fafc 100644 --- a/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extra_col_slave_myisam.result @@ -64,7 +64,7 @@ a b c connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10)' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10(bytes))' to type 'char(5(bytes) latin1)'' STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -102,7 +102,7 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'tinyblob' to type 'int(11)'' +Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' *** Drop t3 *** connection master; DROP TABLE t3; @@ -160,7 +160,7 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6(bytes))' to type 'char(5(bytes) latin1)'' *** Drop t5 *** connection master; DROP TABLE t5; @@ -188,7 +188,7 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), ******************************************** connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6(bytes))' to type 'char(5(bytes) latin1)'' *** Drop t6 *** include/rpl_reset.inc connection master; @@ -310,7 +310,7 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5)' to type 'double'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5(bytes))' to type 'double'' *** Drop t10 *** connection master; DROP TABLE t10; @@ -338,7 +338,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254)' to type 'int(11)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254(bytes))' to type 'int(11)'' *** Drop t11 *** connection master; DROP TABLE t11; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index 1d4b31a4a87..6c669a994a2 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -560,7 +560,7 @@ INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765(bytes))' to type 'char(48(bytes) utf8)'' include/rpl_reset.inc [expecting slave to stop] connection master; @@ -568,7 +568,7 @@ INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765(bytes))' to type 'char(384(bytes) utf8)'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master; diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result index 1e3ddd4f289..fce02e63962 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result @@ -565,7 +565,7 @@ INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765(bytes))' to type 'char(48(bytes) utf8)'' include/rpl_reset.inc [expecting slave to stop] connection master; @@ -573,7 +573,7 @@ INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765(bytes))' to type 'char(384(bytes) utf8)'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master; diff --git a/mysql-test/suite/rpl/r/rpl_row_colSize.result b/mysql-test/suite/rpl/r/rpl_row_colSize.result index dd324ef7807..578c321964a 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -185,7 +185,7 @@ INSERT INTO t1 VALUES ('This is a test.'); 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 'char(20)' to type 'char(10)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'char(20(bytes))' to type 'char(10(bytes) latin1)'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -264,7 +264,7 @@ INSERT INTO t1 VALUES ('This is a test.'); 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 'varchar(2000)' to type 'varchar(100)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(2000(bytes))' to type 'varchar(100(bytes) latin1)'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -287,7 +287,7 @@ INSERT INTO t1 VALUES ('This is a test.'); 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 'varchar(200)' to type 'varchar(10)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(200(bytes))' to type 'varchar(10(bytes) latin1)'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -310,7 +310,7 @@ INSERT INTO t1 VALUES ('This is a test.'); 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 'varchar(2000)' to type 'varchar(1000)'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'varchar(2000(bytes))' to type 'varchar(1000(bytes) latin1)'' SELECT COUNT(*) FROM t1; COUNT(*) 0 @@ -334,7 +334,7 @@ INSERT INTO t1 VALUES ('This is a test.'); 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 'tinyblob' to type 'tinyblob'' +Last_SQL_Error = 'Column 0 of table 'test.t1' cannot be converted from type 'longblob' to type 'tinyblob'' SELECT COUNT(*) FROM t1; COUNT(*) 0 diff --git a/mysql-test/suite/rpl/r/rpl_type_conv_utf8_utf8mb4.result b/mysql-test/suite/rpl/r/rpl_type_conv_utf8_utf8mb4.result new file mode 100644 index 00000000000..870a90d408b --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_type_conv_utf8_utf8mb4.result @@ -0,0 +1,62 @@ +include/master-slave.inc +[connection master] +#################################################################### +# Test Case1: Improved error message with charset information +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4'); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'varchar\(3\(bytes\)\)\' to type \'varchar\(4\(bytes\) utf8mb4\)\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case2: Improved error message with charset information for CHAR +# type +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb4'); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'char\(3\(bytes\)\)\' to type \'char\(4\(bytes\) utf8mb4\)\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +#################################################################### +# Test Case3: For BLOB type fileds, when type conversion failed on +# slave, the errormessage had incorrect type names. +#################################################################### +connection master; +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 LONGBLOB); +SET SQL_LOG_BIN=1; +connection slave; +CREATE TABLE t1 (c1 TINYBLOB); +connection master; +INSERT INTO t1 VALUES ('a'); +connection slave; +include/wait_for_slave_sql_error.inc [errno=1677] +FOUND 1 /\'longblob\' to type \'tinyblob\'/ in mysqld.2.err +connection master; +DROP TABLE t1; +connection slave; +DROP TABLE t1; +include/rpl_reset.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_type_conv_utf8_utf8mb4.test b/mysql-test/suite/rpl/t/rpl_type_conv_utf8_utf8mb4.test new file mode 100644 index 00000000000..ee56ffdcd06 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_type_conv_utf8_utf8mb4.test @@ -0,0 +1,144 @@ +# ==== Purpose ==== +# +# Test verifies that when slave side type conversion fails in row based +# replication, more informative error message is displayed. It also verifies +# that in the case of blob fields appropriate type name is displayed in error +# message. +# +# ==== Implementation ==== +# +# Steps: +# Test case1: +# 1. Create a table on master with VARCHAR filed and charset +# 'utf8mb3'. +# 2. Create a table on slave with VARCHAR field and charset +# 'utf8mb4'. +# 3. Insert a tuple on master. +# 4. Verify that slave provides more informative error message with +# respect to difference in charsets. +# Test case2: Repeat same steps as above for CHAR field +# Test case3: +# 1. Create a table on master with LONGBLOB field. +# 2. Create a table on slave with TINYBLOB field. +# 3. Insert a tuple on master. +# 4. Verify that error message displayed on slave clearly states type +# conversion failure from 'longblob' to 'tinyblob'. +# 5. Also verify that error message doesn't show additional details +# of charset when not required. +# +# ==== References ==== +# +# MDEV-19925: Column ... cannot be converted from type 'varchar(20)' to type +# 'varchar(20)' +# + +--source include/have_binlog_format_row.inc +# Inorder to grep a specific error pattern in error log a fresh error log +# needs to be generated. +--source include/force_restart.inc +--source include/master-slave.inc + +--echo #################################################################### +--echo # Test Case1: Improved error message with charset information +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4'); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Check error log for correct messages. +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!$log_error_) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.2.err; +} + +# Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)' +# Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'varchar(3(bytes))' to type 'varchar(4(bytes) utf8mb4)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'varchar\(3\(bytes\)\)\' to type \'varchar\(4\(bytes\) utf8mb4\)\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case2: Improved error message with charset information for CHAR +--echo # type +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb3'); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET 'utf8mb4'); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'char(0)' to type 'char(1)' +# Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'char(3(bytes))' to type 'char(4(bytes) utf8mb4)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'char\(3\(bytes\)\)\' to type \'char\(4\(bytes\) utf8mb4\)\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc + +--echo #################################################################### +--echo # Test Case3: For BLOB type fileds, when type conversion failed on +--echo # slave, the errormessage had incorrect type names. +--echo #################################################################### +--connection master +SET SQL_LOG_BIN=0; +CREATE TABLE t1 (c1 LONGBLOB); +SET SQL_LOG_BIN=1; + +--connection slave +CREATE TABLE t1 (c1 TINYBLOB); + +--connection master +INSERT INTO t1 VALUES ('a'); + +--connection slave +--let $slave_sql_errno= 1677 +--source include/wait_for_slave_sql_error.inc + +# Error msg before: Column 0 of table 'test.t1' cannot be converted from type 'tinyblob' to type 'tinyblob' +# Error msg after : Column 0 of table 'test.t1' cannot be converted from type 'longblob' to type 'tinyblob' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'longblob\' to type \'tinyblob\' +--source include/search_pattern_in_file.inc + +--connection master +DROP TABLE t1; +--connection slave +DROP TABLE t1; +--let $rpl_only_running_threads= 1 +--source include/rpl_reset.inc +--source include/rpl_end.inc diff --git a/sql/rpl_utility.cc b/sql/rpl_utility.cc index 4277e68a8b5..da814ae28b1 100644 --- a/sql/rpl_utility.cc +++ b/sql/rpl_utility.cc @@ -410,7 +410,7 @@ void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_ CHARSET_INFO *cs= str->charset(); uint32 length= cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), - "varchar(%u)", metadata); + "varchar(%u(bytes))", metadata); str->length(length); } break; @@ -460,22 +460,22 @@ void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_ it is necessary to check the pack length to figure out what kind of blob it really is. */ - switch (get_blob_type_from_length(metadata)) + switch (metadata) { - case MYSQL_TYPE_TINY_BLOB: + case 1: str->set_ascii(STRING_WITH_LEN("tinyblob")); break; - case MYSQL_TYPE_MEDIUM_BLOB: - str->set_ascii(STRING_WITH_LEN("mediumblob")); + case 2: + str->set_ascii(STRING_WITH_LEN("blob")); break; - case MYSQL_TYPE_LONG_BLOB: - str->set_ascii(STRING_WITH_LEN("longblob")); + case 3: + str->set_ascii(STRING_WITH_LEN("mediumblob")); break; - case MYSQL_TYPE_BLOB: - str->set_ascii(STRING_WITH_LEN("blob")); + case 4: + str->set_ascii(STRING_WITH_LEN("longblob")); break; default: @@ -493,7 +493,7 @@ void show_sql_type(enum_field_types type, uint16 metadata, String *str, CHARSET_ uint bytes= (((metadata >> 4) & 0x300) ^ 0x300) + (metadata & 0x00ff); uint32 length= cs->cset->snprintf(cs, (char*) str->ptr(), str->alloced_length(), - "char(%d)", bytes / field_cs->mbmaxlen); + "char(%d(bytes))", bytes); str->length(length); } break; @@ -893,12 +893,33 @@ table_def::compatible_with(THD *thd, rpl_group_info *rgi, const char *tbl_name= table->s->table_name.str; char source_buf[MAX_FIELD_WIDTH]; char target_buf[MAX_FIELD_WIDTH]; + String field_sql_type; String source_type(source_buf, sizeof(source_buf), &my_charset_latin1); String target_type(target_buf, sizeof(target_buf), &my_charset_latin1); THD *thd= table->in_use; - show_sql_type(type(col), field_metadata(col), &source_type, field->charset()); - field->sql_type(target_type); + show_sql_type(type(col), field_metadata(col), &source_type, + field->charset()); + if (field->has_charset() && + (field->real_type() != MYSQL_TYPE_ENUM && + field->real_type() != MYSQL_TYPE_SET) && + (field->type() == MYSQL_TYPE_VARCHAR || + field->type() == MYSQL_TYPE_STRING)) + { + field_sql_type.append((field->type() == MYSQL_TYPE_VARCHAR) ? + "varchar" : "char"); + const CHARSET_INFO *cs= field->charset(); + size_t length= cs->cset->snprintf(cs, (char*) target_type.ptr(), + target_type.alloced_length(), + "%s(%u(bytes) %s)", + field_sql_type.c_ptr_safe(), + field->field_length, + field->charset()->csname); + target_type.length(length); + } + else + field->sql_type(target_type); + rli->report(ERROR_LEVEL, ER_SLAVE_CONVERSION_FAILED, rgi->gtid_info(), ER_THD(thd, ER_SLAVE_CONVERSION_FAILED), col, db_name, tbl_name, diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result index cca18c7c1ab..f8867e8a98b 100644 --- a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result +++ b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_master_tokudb.result @@ -468,7 +468,7 @@ INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'double' to type 'char(5(bytes) latin1)'' *** Drop t10 *** connection master; @@ -510,7 +510,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT), connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'tinyblob' to type 'varchar(254)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'blob' to type 'varchar(254(bytes) latin1)'' *** Drop t11 *** connection master; diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result index 8906cf31d74..7cbe0d6092c 100644 --- a/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result +++ b/storage/tokudb/mysql-test/rpl/r/rpl_extra_col_slave_tokudb.result @@ -64,7 +64,7 @@ a b c connection slave; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10)' to type 'char(5)'' +Last_SQL_Error = 'Column 2 of table 'test.t2' cannot be converted from type 'char(10(bytes))' to type 'char(5(bytes) latin1)'' STOP SLAVE; RESET SLAVE; SELECT * FROM t2 ORDER BY a; @@ -102,7 +102,7 @@ INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TEST ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'tinyblob' to type 'int(11)'' +Last_SQL_Error = 'Column 0 of table 'test.t3' cannot be converted from type 'blob' to type 'int(11)'' *** Drop t3 *** connection master; DROP TABLE t3; @@ -160,7 +160,7 @@ INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098), ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'varchar(6(bytes))' to type 'char(5(bytes) latin1)'' *** Drop t5 *** connection master; DROP TABLE t5; @@ -188,7 +188,7 @@ INSERT INTO t6 () VALUES(1,'Kyle',200.23,1), ******************************************** connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6)' to type 'char(5)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'varchar(6(bytes))' to type 'char(5(bytes) latin1)'' *** Drop t6 *** include/rpl_reset.inc connection master; @@ -310,7 +310,7 @@ INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5)' to type 'double'' +Last_SQL_Error = 'Column 2 of table 'test.t10' cannot be converted from type 'char(5(bytes))' to type 'double'' *** Drop t10 *** connection master; DROP TABLE t10; @@ -338,7 +338,7 @@ INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); ******************************************** connection slave; include/wait_for_slave_sql_error_and_skip.inc [errno=1677] -Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254)' to type 'int(11)'' +Last_SQL_Error = 'Column 2 of table 'test.t11' cannot be converted from type 'varchar(254(bytes))' to type 'int(11)'' *** Drop t11 *** connection master; DROP TABLE t11; diff --git a/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result b/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result index 32a42143180..7d7a7e18a76 100644 --- a/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result +++ b/storage/tokudb/mysql-test/rpl/r/rpl_row_basic_3tokudb.result @@ -565,7 +565,7 @@ INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' +Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(765(bytes))' to type 'char(48(bytes) utf8)'' include/rpl_reset.inc [expecting slave to stop] connection master; @@ -573,7 +573,7 @@ INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; include/wait_for_slave_sql_error.inc [errno=1677] -Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' +Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(765(bytes))' to type 'char(384(bytes) utf8)'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master;