include/master-slave.inc [connection master] SET @positive= 18446744073709551615; SET @negative= -9223372036854775808; CREATE TABLE t1 (`tinyint` TINYINT, `smallint` SMALLINT, `mediumint` MEDIUMINT, `integer` INTEGER, `bigint` BIGINT, `utinyint` TINYINT UNSIGNED, `usmallint` SMALLINT UNSIGNED, `umediumint` MEDIUMINT UNSIGNED, `uinteger` INTEGER UNSIGNED, `ubigint` BIGINT UNSIGNED, `double` DOUBLE, `float` FLOAT, `real` REAL(30,2), `decimal` DECIMAL(30,2)) ENGINE = MyISAM; ### insert max unsigned ### a) declarative INSERT IGNORE INTO t1 VALUES (18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615,18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615);; ######################################### SELECT * FROM t1; tinyint 127 smallint 32767 mediumint 8388607 integer 2147483647 bigint 9223372036854775807 utinyint 255 usmallint 65535 umediumint 16777215 uinteger 4294967295 ubigint 18446744073709551615 double 1.8446744073709552e19 float 1.84467e19 real 18446744073709552000.00 decimal 18446744073709551615.00 connection slave; SELECT * FROM t1; tinyint 127 smallint 32767 mediumint 8388607 integer 2147483647 bigint 9223372036854775807 utinyint 255 usmallint 65535 umediumint 16777215 uinteger 4294967295 ubigint 18446744073709551615 double 1.8446744073709552e19 float 1.84467e19 real 18446744073709552000.00 decimal 18446744073709551615.00 ######################################### connection master; ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] connection master; TRUNCATE t1; ### b) user var INSERT IGNORE INTO t1 VALUES (@positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive); ######################################### SELECT * FROM t1; tinyint 127 smallint 32767 mediumint 8388607 integer 2147483647 bigint 9223372036854775807 utinyint 255 usmallint 65535 umediumint 16777215 uinteger 4294967295 ubigint 18446744073709551615 double 1.8446744073709552e19 float 1.84467e19 real 18446744073709552000.00 decimal 18446744073709551615.00 connection slave; SELECT * FROM t1; tinyint 127 smallint 32767 mediumint 8388607 integer 2147483647 bigint 9223372036854775807 utinyint 255 usmallint 65535 umediumint 16777215 uinteger 4294967295 ubigint 18446744073709551615 double 1.8446744073709552e19 float 1.84467e19 real 18446744073709552000.00 decimal 18446744073709551615.00 ######################################### connection master; ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] connection master; TRUNCATE t1; ### insert min signed ### a) declarative INSERT IGNORE INTO t1 VALUES (-9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808,-9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808);; ######################################### SELECT * FROM t1; tinyint -128 smallint -32768 mediumint -8388608 integer -2147483648 bigint -9223372036854775808 utinyint 0 usmallint 0 umediumint 0 uinteger 0 ubigint 0 double -9.223372036854776e18 float -9.22337e18 real -9223372036854776000.00 decimal -9223372036854775808.00 connection slave; SELECT * FROM t1; tinyint -128 smallint -32768 mediumint -8388608 integer -2147483648 bigint -9223372036854775808 utinyint 0 usmallint 0 umediumint 0 uinteger 0 ubigint 0 double -9.223372036854776e18 float -9.22337e18 real -9223372036854776000.00 decimal -9223372036854775808.00 ######################################### connection master; ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] connection master; TRUNCATE t1; ### b) user var INSERT IGNORE INTO t1 VALUES (@negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative); ######################################### SELECT * FROM t1; tinyint -128 smallint -32768 mediumint -8388608 integer -2147483648 bigint -9223372036854775808 utinyint 0 usmallint 0 umediumint 0 uinteger 0 ubigint 0 double -9.223372036854776e18 float -9.22337e18 real -9223372036854776000.00 decimal -9223372036854775808.00 connection slave; SELECT * FROM t1; tinyint -128 smallint -32768 mediumint -8388608 integer -2147483648 bigint -9223372036854775808 utinyint 0 usmallint 0 umediumint 0 uinteger 0 ubigint 0 double -9.223372036854776e18 float -9.22337e18 real -9223372036854776000.00 decimal -9223372036854775808.00 ######################################### connection master; ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] connection master; TRUNCATE t1; ## check: contents of both tables master's and slave's connection master; DROP TABLE t1; connection slave; include/rpl_reset.inc connection master; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE t1 ( c INT, PRIMARY KEY (c)) Engine=MyISAM; CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ; SET @aux = 10294947273192243200; SET @aux1= @aux; INSERT INTO t1 VALUES (@aux) , (@aux1); ERROR 23000: Duplicate entry '2147483647' for key 'PRIMARY' SET sql_mode = DEFAULT; connection slave; ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] connection master; DROP TRIGGER tr1; DROP TABLE t1; connection slave; # The GET DIAGNOSTICS itself is not replicated, but it can set # variables which can be used in statements that are replicated. include/rpl_reset.inc connection master; CREATE TABLE t1 (a INT, b INT); GET DIAGNOSTICS @var1 = NUMBER; INSERT INTO t1 VALUES (@var1, 0), (@var1, 0); CREATE PROCEDURE p1() LANGUAGE SQL BEGIN DECLARE count INT; UPDATE t1 SET b = 2 WHERE a = 0; GET DIAGNOSTICS count = ROW_COUNT; INSERT INTO t1 VALUES (1, count); END| CALL p1(); connection slave; connection slave; # check if the statement was replicated. SELECT * FROM t1 ORDER BY a; a b 0 2 0 2 1 2 connection master; # Show events and cleanup include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # User var # # @`var1`=0 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (@var1, 0), (@var1, 0) master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() BEGIN DECLARE count INT; UPDATE t1 SET b = 2 WHERE a = 0; GET DIAGNOSTICS count = ROW_COUNT; INSERT INTO t1 VALUES (1, count); END master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2 WHERE a = 0 master-bin.000001 # Query # # COMMIT master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1, NAME_CONST('count',2)) master-bin.000001 # Query # # COMMIT DROP TABLE t1; DROP PROCEDURE p1; connection slave; include/rpl_end.inc