Skip to content

Commit bd622b7

Browse files
author
Mats Kindahl
committed
WL#5151: Conversion between different types when replicating
Row-based replication requires the types of columns on the master and slave to be approximately the same (some safe conversions between strings are allowed), but does not allow safe conversions between fields of similar types such as TINYINT and INT. This patch implement type conversions between similar fields on the master and slave. The conversions are controlled using a new variable SLAVE_TYPE_CONVERSIONS of type SET('ALL_LOSSY','ALL_NON_LOSSY'). Non-lossy conversions are any conversions that do not run the risk of losing any information, while lossy conversions can potentially truncate the value. The column definitions are checked to decide if the conversion is acceptable. If neither conversion is enabled, it is required that the definitions of the columns are identical on master and slave. Conversion is done by creating an internal conversion table, unpacking the master data into it, and then copy the data to the real table on the slave. --BZR-- revision-id: [email protected] property-branch-nick: conversion-table property-file-info: ld7:file_id59:sp1f-ignore-20001018235455-q4gxfbritt5f42nwix354ufpsvrf5ebj7:message15:New files added4:path10:.bzrignoreed7:file_id64:sp1f-makefile.am-19700101030959-zpaig4zjwqhmsob3ypilmrcrkq7pu6gf7:message15:New files added4:path18:client/Makefile.amed7:file_id67:sp1f-mysqlbinlog.cc-19700101030959-b3vgyo47ljent5mhbyj6ik33bi4bukad7:message60:Functions in rpl_utility.cc is now needed by mysqlbinlog.cc.4:path21:client/mysqlbinlog.cced7:file_id64:sp1f-makefile.am-20010411110351-26htpk3ynkyh7pkfvnshztqrxx3few4g7:message15:New files added4:path21:libmysqld/Makefile.amed7:file_id48:check_type.inc-20091118114807-nmq0zl0cc8c0d45m-17:message53:Test include file to check a single type conversion. property-file-info: 4:path41:mysql-test/extra/rpl_tests/check_type.inced7:file_id73:sp1f-rpl_extraslave_col.t-20061103140340-egmkull7owd2wp7d4egg6itzef6p7g237:message84:Switching to use INT instead of TEXT for column that should not have matching types.4:path50:mysql-test/extra/rpl_tests/rpl_extraSlave_Col.tested7:file_id70:sp1f-rpl_row_basic.inc-20051222053451-qbja3f3vz3mf5ikwca7eeqkxutnnzlbi7:message142:Adding code to enable type conversions for BIT tests since InnoDB property-file-info: cannot handle them properly due to incorrect information stored as property-file-info: metadata.4:path45:mysql-test/extra/rpl_tests/rpl_row_basic.tested7:file_id54:type_conversions.tes-20091118114807-nmq0zl0cc8c0d45m-27:message94:Test file to check a set of type conversions property-file-info: with current settings of slave_type_conversions. property-file-info: 4:path48:mysql-test/extra/rpl_tests/type_conversions.tested7:file_id51:rpl_typeconv.test-20091118114725-p4yrkiwge5c82p0p-17:message185:Test file to test conversions from master to slave with property-file-info: all possible values for slave_type_conversions. property-file-info: property-file-info: The test also checks that the slave_type_conversions property-file-info: variable works as expected.4:path40:mysql-test/suite/rpl/t/rpl_typeconv.tested7:file_id61:sp1f-field.cc-19700101030959-f4imaofclsea3n4fj4ow5m7havmyxa2r7:message282:Changing definition of compatible_field_size to both check if property-file-info: two field with identical base types are compatible and give an property-file-info: order between them if they are compatible. property-file-info: property-file-info: This only implement checking on the slave, so it will not affect property-file-info: replication from an old master to a new slave. property-file-info: 4:path12:sql/field.cced7:file_id60:sp1f-field.h-19700101030959-3n6smzxcwkjl7bikm3wg4hfkjn66uvvp7:message103:Changing prototypes for functions: property-file-info: - compatible_field_size() property-file-info: - init_for_tmp_table() property-file-info: - row_pack_length()4:path11:sql/field.hed7:file_id65:sp1f-log_event.cc-19700101030959-msmqlflsngxosswid2hpzxly5vfqdddc7:message123:Changing compability checks to build a conversion table if the fields property-file-info: are compatible, but does not have the same base type.4:path16:sql/log_event.cced7:file_id69:sp1f-log_event_old.cc-20070412135046-uu5xq4cnpwslzif6fbmj3g65x4vdkzxu7:message124:Changing compability checks to build a conversion table if the fields property-file-info: are compatible, but does not have the same base type. property-file-info: 4:path20:sql/log_event_old.cced7:file_id65:sp1f-mysql_priv.h-19700101030959-4fl65tqpop5zfgxaxkqotu2fa2ree5ci7:message56:Adding global option variable for SLAVE_TYPE_CONVERSIONS4:path16:sql/mysql_priv.hed7:file_id62:sp1f-mysqld.cc-19700101030959-zpswdvekpvixxzxf7gdtofzel7nywtfj7:message54:Adding SLAVE_TYPE_CONVERSIONS global server variable. property-file-info: 4:path13:sql/mysqld.cced7:file_id66:sp1f-rpl_record.cc-20070413125523-wuthuk5jk7uxikuioz6esll6xakdngs47:message60:Changing unpack_row to use the conversion table if present. property-file-info: 4:path17:sql/rpl_record.cced7:file_id62:sp1f-rpl_rli.h-20051222053448-bte4b72jikihtk3zbn5jyj2vbiawtwgc7:message159:Removing function get_tabledef and replacing it with get_table_data(). property-file-info: This function retrieve data for table opened for replication, not just property-file-info: table definition.4:path13:sql/rpl_rli.hed7:file_id67:sp1f-rpl_utility.cc-20060503130029-owknu72jo3254qe7gjoeh7oilfrz4atk7:message380:Function table_def::compatible_with is changed to compare table on master property-file-info: and slave for compatibility and generate a conversions table if they are property-file-info: compatible. property-file-info: property-file-info: Computing real type of fields from metadata for ENUM and SET types. property-file-info: Computing pack_length correctly for ENUM, SET, and BLOB types. property-file-info: property-file-info: Adding optimization to not check compatibility if no property-file-info: slave type conversions are enabled.4:path18:sql/rpl_utility.cced7:file_id66:sp1f-rpl_utility.h-20060503130029-u44nzzcbdenh2gegnnyzro26kbk5quw77:message127:Changing prototypes since implementation has changed. property-file-info: property-file-info: Modifying table_def::type() to return real type instead of stored type. property-file-info: 4:path17:sql/rpl_utility.hed7:file_id63:sp1f-set_var.cc-20020723153119-nwbpg2pwpz55pfw7yfzaxt7hsszzy7y37:message40:Adding SLAVE_TYPE_CONVERSIONS variable. property-file-info: 4:path14:sql/set_var.cced7:file_id62:sp1f-set_var.h-20020723153119-2yomygq3s4xjbqvuue3cdlpbjtj3kwmk7:message39:Adding SLAVE_TYPE_CONVERSIONS variable.4:path13:sql/set_var.hed7:file_id63:sp1f-errmsg.txt-20041213212820-do5w642w224ja7ctyqhyl6iihdmpkzv57:message49:Adding error messages for slave type conversions.4:path20:sql/share/errmsg.txted7:file_id64:sp1f-sql_class.h-19700101030959-jnqnbrjyqsvgncsibnumsmg3lyi7pa5s7:message39:Adding SLAVE_TYPE_CONVERSIONS variable.4:path15:sql/sql_class.hed7:file_id66:sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb7:message109:Correcting create_virtual_tmp_table() to compute null bit positions property-file-info: correctly in the presence of bit fields. property-file-info: 4:path17:sql/sql_select.ccee testament3-sha1: 249b4fd2c0c76bd8bfeef5b190f5c7a57bd134f6
1 parent 357e216 commit bd622b7

40 files changed

Lines changed: 2893 additions & 464 deletions

‎.bzrfileids‎

385 Bytes
Binary file not shown.

‎.bzrignore‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -392,6 +392,7 @@ client/rpl_record_old.h
392392
client/rpl_tblmap.h
393393
client/rpl_tblmap.cc
394394
client/rpl_utility.h
395+
client/rpl_utility.cc
395396
client/select_test
396397
client/sql_string.cpp
397398
client/ssl_test
@@ -1142,6 +1143,7 @@ libmysqld/rpl_filter.cc
11421143
libmysqld/rpl_injector.cc
11431144
libmysqld/rpl_record.cc
11441145
libmysqld/rpl_record_old.cc
1146+
libmysqld/rpl_utility.cc
11451147
libmysqld/scheduler.cc
11461148
libmysqld/set_var.cc
11471149
libmysqld/simple-test

‎client/Makefile.am‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -104,9 +104,10 @@ DEFS = -DMYSQL_CLIENT_NO_THREADS \
104104
-DMYSQL_DATADIR="\"$(localstatedir)\""
105105

106106
sql_src=log_event.h mysql_priv.h rpl_constants.h \
107-
rpl_utility.h rpl_tblmap.h rpl_tblmap.cc \
107+
rpl_tblmap.h rpl_tblmap.cc \
108108
log_event.cc my_decimal.h my_decimal.cc \
109109
log_event_old.h log_event_old.cc \
110+
rpl_utility.h rpl_utility.cc \
110111
rpl_record_old.h rpl_record_old.cc
111112
strings_src=decimal.c
112113

‎client/mysqlbinlog.cc‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2108,4 +2108,4 @@ int main(int argc, char** argv)
21082108
#include "my_decimal.cc"
21092109
#include "log_event.cc"
21102110
#include "log_event_old.cc"
2111-
2111+
#include "rpl_utility.cc"

‎libmysqld/Makefile.am‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,7 @@ sqlsources = derror.cc field.cc field_conv.cc strfunc.cc filesort.cc \
5555
item_geofunc.cc item_subselect.cc item_row.cc\
5656
item_xmlfunc.cc \
5757
key.cc lock.cc log.cc sql_state.c \
58-
log_event.cc rpl_record.cc \
58+
log_event.cc rpl_record.cc rpl_utility.cc \
5959
log_event_old.cc rpl_record_old.cc \
6060
protocol.cc net_serv.cc opt_range.cc \
6161
opt_sum.cc procedure.cc records.cc sql_acl.cc \

‎mysql-test/extra/rpl_tests/check_type.inc‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
# Helper file to perform one insert of a value into a table with
2+
# different types on master and slave. The file will insert the
3+
# result into the type_conversions table *on the slave* to get a
4+
# summary of failing and succeeding tests.
5+
6+
# Input:
7+
# $source_type Type on the master
8+
# $target_type Type on the slave
9+
# $source_value Value on the master (inserted into the table)
10+
# $target_value Value on the slave (expected value in the table
11+
# on the slave)
12+
# $can_convert True if conversion shall work, false if it
13+
# shall generate an error
14+
15+
16+
connection master;
17+
disable_warnings;
18+
DROP TABLE IF EXISTS t1;
19+
enable_warnings;
20+
eval CREATE TABLE t1 (a $source_type);
21+
sync_slave_with_master;
22+
eval ALTER TABLE t1 MODIFY a $target_type;
23+
24+
connection master;
25+
eval INSERT INTO t1 VALUES($source_value);
26+
if ($can_convert) {
27+
sync_slave_with_master;
28+
eval SELECT a = $target_value into @compare FROM t1;
29+
eval INSERT INTO type_conversions SET
30+
Source = "$source_type",
31+
Target = "$target_type",
32+
Flags = @@slave_type_conversions,
33+
On_Master = $source_value,
34+
Expected = $target_value,
35+
Compare = @compare;
36+
UPDATE type_conversions
37+
SET On_Slave = (SELECT a FROM t1)
38+
WHERE TestNo = LAST_INSERT_ID();
39+
}
40+
if (!$can_convert) {
41+
connection slave;
42+
wait_for_slave_to_stop;
43+
let $error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
44+
eval INSERT INTO type_conversions SET
45+
Source = "$source_type",
46+
Target = "$target_type",
47+
Flags = @@slave_type_conversions,
48+
On_Master = $source_value,
49+
Error = "$error";
50+
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
51+
START SLAVE;
52+
}

‎mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,9 @@ sync_slave_with_master;
3636
STOP SLAVE;
3737
RESET SLAVE;
3838

39+
SET @saved_slave_type_conversions = @@slave_type_conversions;
40+
SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';
41+
3942
eval CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20),
4043
d FLOAT DEFAULT '2.00',
4144
e CHAR(4) DEFAULT 'TEST')
@@ -62,6 +65,8 @@ SELECT * FROM t1 ORDER BY a;
6265
sync_slave_with_master;
6366
SELECT * FROM t1 ORDER BY a;
6467

68+
SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
69+
6570
--echo *** Drop t1 ***
6671
connection master;
6772
DROP TABLE t1;
@@ -495,7 +500,7 @@ sync_slave_with_master;
495500
--echo *** Create t11 on slave ***
496501
STOP SLAVE;
497502
RESET SLAVE;
498-
eval CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
503+
eval CREATE TABLE t11 (a INT KEY, b BLOB, f INT,
499504
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE=$engine_type;
500505

501506
--echo *** Create t11 on Master ***

‎mysql-test/extra/rpl_tests/rpl_row_basic.test‎

Lines changed: 29 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@
66
# First we test tables with only an index.
77
#
88

9+
connection master;
910
eval CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)$extra_index_t1) ENGINE = $type ;
1011
SELECT * FROM t1;
1112
sync_slave_with_master;
@@ -156,6 +157,12 @@ SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5
156157
# Testing special column types
157158
#
158159

160+
if (`select char_length('$bit_field_special') > 0`) {
161+
SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS;
162+
connection slave;
163+
eval SET GLOBAL SLAVE_TYPE_CONVERSIONS = '$bit_field_special';
164+
}
165+
159166
connection master;
160167
eval CREATE TABLE t4 (C1 CHAR(1) PRIMARY KEY, B1 BIT(1), B2 BIT(1) NOT NULL DEFAULT 0, C2 CHAR(1) NOT NULL DEFAULT 'A') ENGINE = $type ;
161168

@@ -164,6 +171,10 @@ SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
164171
sync_slave_with_master;
165172
SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1;
166173

174+
if (`select char_length('$bit_field_special') > 0`) {
175+
SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
176+
}
177+
167178
#
168179
# Testing conflicting operations
169180
#
@@ -350,6 +361,10 @@ eval CREATE TABLE t7 (i INT NOT NULL,
350361
c CHAR(255) CHARACTER SET utf8 NOT NULL,
351362
j INT NOT NULL) ENGINE = $type ;
352363

364+
connection slave;
365+
SET @saved_slave_type_conversions = @@slave_type_conversions;
366+
SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY';
367+
353368
--echo [expecting slave to replicate correctly]
354369
connection master;
355370
INSERT INTO t1 VALUES (1, "", 1);
@@ -370,17 +385,9 @@ let $diff_table_1=master:test.t2;
370385
let $diff_table_2=slave:test.t2;
371386
source include/diff_tables.inc;
372387

373-
--echo [expecting slave to stop]
374-
connection master;
375-
INSERT INTO t3 VALUES (1, "", 1);
376-
INSERT INTO t3 VALUES (2, repeat(_utf8'a', 128), 2);
377-
378388
connection slave;
379-
source include/wait_for_slave_sql_to_stop.inc;
380-
let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
381-
disable_query_log;
382-
eval SELECT "$last_error" AS Last_SQL_Error;
383-
enable_query_log;
389+
SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
390+
384391
connection master;
385392
RESET MASTER;
386393
connection slave;
@@ -600,7 +607,15 @@ sync_slave_with_master;
600607

601608
connection master;
602609

610+
# Since t1 contain a bit field, we have to do this trick to handle InnoDB
611+
if (`select char_length('$bit_field_special') > 0`) {
612+
SET @saved_slave_type_conversions = @@SLAVE_TYPE_CONVERSIONS;
613+
connection slave;
614+
eval SET GLOBAL SLAVE_TYPE_CONVERSIONS = '$bit_field_special';
615+
}
616+
603617
--disable_warnings
618+
connection master;
604619
eval CREATE TABLE t1 (a bit) ENGINE=$type;
605620
INSERT IGNORE INTO t1 VALUES (NULL);
606621
INSERT INTO t1 ( a ) VALUES ( 0 );
@@ -645,6 +660,10 @@ UPDATE t1 SET a = 9 WHERE a < 5 LIMIT 3;
645660

646661
sync_slave_with_master;
647662

663+
if (`select char_length('$bit_field_special') > 0`) {
664+
SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions;
665+
}
666+
648667
let $diff_table_1=master:test.t1;
649668
let $diff_table_2=slave:test.t1;
650669
source include/diff_tables.inc;

0 commit comments

Comments
 (0)