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 INTO t1 VALUES (18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615,18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615);; ######################################### #### [ on master ] 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 #### [ on 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 ######################################### ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] TRUNCATE t1; ### b) user var INSERT INTO t1 VALUES (@positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive, @positive); ######################################### #### [ on master ] 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 #### [ on 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 ######################################### ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] TRUNCATE t1; ### insert min signed ### a) declarative INSERT INTO t1 VALUES (-9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808,-9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808);; ######################################### #### [ on master ] 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 #### [ on 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 ######################################### ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] TRUNCATE t1; ### b) user var INSERT INTO t1 VALUES (@negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative, @negative); ######################################### #### [ on master ] 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 #### [ on 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 ######################################### ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] TRUNCATE t1; ## check: contents of both tables master's and slave's DROP TABLE t1; include/rpl_reset.inc 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' ## assertion: master and slave tables are in sync include/diff_tables.inc [master:t1,slave:t1] DROP TRIGGER tr1; DROP TABLE t1; # 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 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(); # On slave, check if the statement was replicated. SELECT * FROM t1 ORDER BY a; a b 0 2 0 2 1 2 # 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; include/rpl_end.inc