revision-id: 18420037773d42cc72bebd1397c5a1eedecb80d6 (mariadb-10.2.26-47-g18420037773) parent(s): 1a3c77e524e87e8f4151d31cd9c3112df6de218f author: Sujatha committer: Sujatha timestamp: 2019-08-21 14:01:18 +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 octets 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/field.h | 31 +++++ sql/rpl_utility.cc | 26 ++-- sql/share/errmsg-utf8.txt | 2 +- .../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 +- 15 files changed, 287 insertions(+), 48 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..5865b75550f 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 octets) character set 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 octets) character set 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..f47d79b5b86 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 octets) character set 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 octets) character set 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..a9cbbd9fafe 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 octets)' to type 'char(5 octets) character set 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 octets)' to type 'char(5 octets) character set 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 octets)' to type 'char(5 octets) character set 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 octets)' 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 octets)' 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..11a716f22b6 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 octets)' to type 'char(5 octets) character set 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 octets)' to type 'char(5 octets) character set 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 octets)' to type 'char(5 octets) character set 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 octets)' 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 octets)' 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..98e95e8cc77 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 octets)' to type 'char(48 octets) character set 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 octets)' to type 'char(384 octets) character set 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..3203d5394df 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 octets)' to type 'char(48 octets) character set 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 octets)' to type 'char(384 octets) character set 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..748d83a7f8e 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 octets)' to type 'char(10 octets) character set 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 octets)' to type 'varchar(100 octets) character set 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 octets)' to type 'varchar(10 octets) character set 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 octets)' to type 'varchar(1000 octets) character set 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..4780d65b787 --- /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 octets\)\' to type \'varchar\(4 octets\) character set 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 octets\)\' to type \'char\(4 octets\) character set 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..6954614a4d6 --- /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 octets)' to type 'varchar(4 octets) character set utf8mb4' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'varchar\(3 octets\)\' to type \'varchar\(4 octets\) character set 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 octets)' to type 'char(4 octets) character set utf8mb4)' +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=\'char\(3 octets\)\' to type \'char\(4 octets\) character set 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/field.h b/sql/field.h index 083f3d01bb8..25c9860cd65 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1106,6 +1106,7 @@ class Field: public Value_source in str and restore it with set() if needed */ virtual void sql_type(String &str) const =0; + virtual void sql_rpl_type(String *str) const { sql_type(*str); } virtual uint size_of() const =0; // For new field inline bool is_null(my_ptrdiff_t row_offset= 0) const { @@ -3162,6 +3163,21 @@ class Field_string :public Field_longstr { int cmp(const uchar *,const uchar *); void sort_string(uchar *buff,uint length); void sql_type(String &str) const; + void sql_rpl_type(String *res) const + { + CHARSET_INFO *cs=charset(); + if (has_charset() && + (real_type() != MYSQL_TYPE_ENUM && real_type() != MYSQL_TYPE_SET) && + type() == MYSQL_TYPE_STRING) + { + size_t length= cs->cset->snprintf(cs, (char*) res->ptr(), + res->alloced_length(), + "%s(%u octets) character set %s", + "char", field_length, + charset()->csname); + res->length(length); + } + } virtual uchar *pack(uchar *to, const uchar *from, uint max_length); virtual const uchar *unpack(uchar* to, const uchar *from, @@ -3264,6 +3280,21 @@ class Field_varstring :public Field_longstr { uint get_key_image(uchar *buff,uint length, imagetype type); void set_key_image(const uchar *buff,uint length); void sql_type(String &str) const; + void sql_rpl_type(String *res) const + { + CHARSET_INFO *cs=charset(); + if (has_charset() && + (real_type() != MYSQL_TYPE_ENUM && real_type() != MYSQL_TYPE_SET) && + type() == MYSQL_TYPE_VARCHAR) + { + size_t length= cs->cset->snprintf(cs, (char*) res->ptr(), + res->alloced_length(), + "%s(%u octets) character set %s", + "varchar", field_length, + charset()->csname); + res->length(length); + } + } virtual uchar *pack(uchar *to, const uchar *from, uint max_length); virtual const uchar *unpack(uchar* to, const uchar *from, const uchar *from_end, uint param_data); diff --git a/sql/rpl_utility.cc b/sql/rpl_utility.cc index 4277e68a8b5..ead30657bf8 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 octets)", 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 octets)", bytes); str->length(length); } break; @@ -897,8 +897,10 @@ table_def::compatible_with(THD *thd, rpl_group_info *rgi, 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()); + field->sql_rpl_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/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 1c828db65a8..325858ed05c 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -6461,7 +6461,7 @@ ER_MESSAGE_AND_STATEMENT eng "%s Statement: %s" ER_SLAVE_CONVERSION_FAILED - eng "Column %d of table '%-.192s.%-.192s' cannot be converted from type '%-.32s' to type '%-.32s'" + eng "Column %d of table '%-.192s.%-.192s' cannot be converted from type '%-.50s' to type '%-.50s'" ER_SLAVE_CANT_CREATE_CONVERSION eng "Can't create conversion table for table '%-.192s.%-.192s'" ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT 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..6982078d2b8 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 octets) character set 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 octets) character set 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..318d5496255 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 octets)' to type 'char(5 octets) character set 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 octets)' to type 'char(5 octets) character set 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 octets)' to type 'char(5 octets) character set 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 octets)' 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 octets)' 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..e638a1aab12 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 octets)' to type 'char(48 octets) character set 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 octets)' to type 'char(384 octets) character set utf8'' include/rpl_reset.inc [expecting slave to replicate correctly] connection master;