~ubuntu-branches/ubuntu/maverick/mysql-5.1/maverick-proposed

Last_SQL_Error = 'Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7''

662

STOP SLAVE;

663

RESET SLAVE;

664

665

*** Drop t15 ***

666

DROP TABLE t15;

667

DROP TABLE t15;

668

RESET MASTER;

669

START SLAVE;

670

671

************************************************

672

* - Create index on Master column not on slave *

673

* Expect:Warning *

674

************************************************

675

676

*** Create t16 on slave ***

677

STOP SLAVE;

678

RESET SLAVE;

679

CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

680

) ENGINE='InnoDB';

681

682

*** Create t16 on Master ***

683

CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

684

c6 INT DEFAULT '1',

685

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

686

)ENGINE='InnoDB';

687

RESET MASTER;

688

689

*** Start Slave ***

690

START SLAVE;

691

692

*** Master Create Index and Data Insert ***

693

CREATE INDEX part_of_c6 ON t16 (c6);

694

set @b1 = 'b1b1b1b1';

695

set @b1 = concat(@b1,@b1);

696

INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

697

(2,@b1,'JOE',2,DEFAULT),

698

(3,@b1,'QA',3,DEFAULT);

699

SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;

700

c1 hex(c4) c5 c6 c7

701

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

702

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

703

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

704

705

*****************

706

*** BUG 30434 ***

707

*****************

708

709

include/wait_for_slave_sql_error.inc [errno=1072]

710

Last_SQL_Error = 'Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)''

711

STOP SLAVE;

712

RESET SLAVE;

713

714

*** Drop t16 ***

715

DROP TABLE t16;

716

DROP TABLE t16;

717

RESET MASTER;

718

START SLAVE;

719

720

*****************************************************

721

* - Delete rows using column on Master not on slave *

722

* Expect: Rows Deleted *

723

*****************************************************

724

725

*** Create t17 on slave ***

726

STOP SLAVE;

727

RESET SLAVE;

728

CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

729

) ENGINE='InnoDB';

730

731

*** Create t17 on Master ***

732

CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

733

c6 INT DEFAULT '1',

734

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

735

)ENGINE='InnoDB';

736

RESET MASTER;

737

738

*** Start Slave ***

739

START SLAVE;

740

741

*** Master Data Insert ***

742

set @b1 = 'b1b1b1b1';

743

set @b1 = concat(@b1,@b1);

744

INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

745

(2,@b1,'JOE',2,DEFAULT),

746

(3,@b1,'QA',3,DEFAULT);

747

SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;

748

c1 hex(c4) c5 c6 c7

749

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

750

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

751

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

752

753

** Select * from Slave **

754

SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;

755

c1 hex(c4) c5

756

1 62316231623162316231623162316231 Kyle

757

2 62316231623162316231623162316231 JOE

758

3 62316231623162316231623162316231 QA

759

760

** Delete from master **

761

DELETE FROM t17 WHERE c6 = 3;

762

SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;

763

c1 hex(c4) c5 c6 c7

764

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

765

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

766

767

** Check slave **

768

SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;

769

c1 hex(c4) c5

770

1 62316231623162316231623162316231 Kyle

771

2 62316231623162316231623162316231 JOE

772

DROP TABLE t17;

773

774

775

*****************************************************

776

* - Update row using column on Master not on slave *

777

* Expect: Rows updated *

778

*****************************************************

779

780

** Bug30674 **

781

782

*** Create t18 on slave ***

783

784

STOP SLAVE;

785

RESET SLAVE;

786

CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

787

) ENGINE='InnoDB';

788

789

*** Create t18 on Master ***

790

CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

791

c6 INT DEFAULT '1',

792

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

793

)ENGINE='InnoDB';

794

RESET MASTER;

795

796

*** Start Slave ***

797

START SLAVE;

798

799

*** Master Data Insert ***

800

set @b1 = 'b1b1b1b1';

801

set @b1 = concat(@b1,@b1);

802

INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

803

(2,@b1,'JOE',2,DEFAULT),

804

(3,@b1,'QA',3,DEFAULT);

805

SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;

806

c1 hex(c4) c5 c6 c7

807

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

808

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

809

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

810

811

** Select * from Slave **

812

SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;

813

c1 hex(c4) c5

814

1 62316231623162316231623162316231 Kyle

815

2 62316231623162316231623162316231 JOE

816

3 62316231623162316231623162316231 QA

817

818

** update from master **

819

UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;

820

SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;

821

c1 hex(c4) c5 c6 c7

822

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

823

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

824

3 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP

825

826

** Check slave **

827

SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;

828

c1 hex(c4) c5

829

1 62316231623162316231623162316231 Kyle

830

2 62316231623162316231623162316231 JOE

831

3 62316231623162316231623162316231 TEST

832

DROP TABLE t18;

833

834

835

*****************************************************

836

* - Insert UUID column on Master not on slave *

837

* Expect: Rows inserted *

838

*****************************************************

839

840

*** Create t5 on slave ***

841

STOP SLAVE;

842

RESET SLAVE;

843

CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

844

) ENGINE='InnoDB';

845

846

*** Create t5 on Master ***

847

CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

848

c6 LONG,

849

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

850

)ENGINE='InnoDB';

851

RESET MASTER;

852

853

*** Start Slave ***

854

START SLAVE;

855

856

*** Master Data Insert ***

857

set @b1 = 'b1b1b1b1';

858

INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),

859

(2,@b1,'JOE',UUID(),DEFAULT),

860

(3,@b1,'QA',UUID(),DEFAULT);

861

SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;

862

c1 hex(c4) c5 c6 c7

863

1 6231623162316231 Kyle UUID TIME

864

2 6231623162316231 JOE UUID TIME

865

3 6231623162316231 QA UUID TIME

866

867

** Select * from Slave **

868

SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;

869

c1 hex(c4) c5

870

1 6231623162316231 Kyle

871

2 6231623162316231 JOE

872

3 6231623162316231 QA

873

DROP TABLE t5;

874

set binlog_format=statement;

875

876

***********************************************************

877

***********************************************************

878

***************** Start of Testing ************************

879

***********************************************************

880

***********************************************************

881

* This test format == binlog_format STATEMENT and engine == 'InnoDB'

882

***********************************************************

883

***********************************************************

884

885

***** Testing more columns on the Master *****

886

887

CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

888

/* extra */

889

f5 FLOAT DEFAULT '2.00',

890

f6 CHAR(4) DEFAULT 'TEST',

891

f7 INT DEFAULT '0',

892

f8 TEXT,

893

f9 LONGBLOB,

894

f10 BIT(63),

895

f11 VARBINARY(64))ENGINE='InnoDB';

896

897

* Alter Table on Slave and drop columns f5 through f11 *

898

899

alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;

900

901

* Insert data in Master then update and delete some rows*

902

903

* Select count and 20 rows from Master *

904

905

SELECT COUNT(*) FROM t1;

906

COUNT(*)

907

908

909

SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,

910

hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;

911

f1 f2 f3 f4 f5 f6 f7 f8 f9 hex(f10) hex(f11)

912

2 2 2 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

913

3 3 3 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

914

5 5 5 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

915

6 6 6 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

916

8 8 8 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

917

9 9 9 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

918

11 11 11 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

919

12 12 12 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

920

14 14 14 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

921

15 15 15 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

922

17 17 17 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

923

18 18 18 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

924

20 20 20 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

925

21 21 21 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

926

23 23 23 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

927

24 24 24 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

928

26 26 26 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

929

27 27 27 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

930

29 29 29 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

931

30 30 30 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

932

933

* Select count and 20 rows from Slave *

934

935

SELECT COUNT(*) FROM t1;

936

COUNT(*)

937

938

939

SELECT * FROM t1 ORDER BY f3 LIMIT 20;

940

f1 f2 f3 f4

941

2 2 2 second

942

3 3 3 next

943

5 5 5 second

944

6 6 6 next

945

8 8 8 second

946

9 9 9 next

947

11 11 11 second

948

12 12 12 next

949

14 14 14 second

950

15 15 15 next

951

17 17 17 second

952

18 18 18 next

953

20 20 20 second

954

21 21 21 next

955

23 23 23 second

956

24 24 24 next

957

26 26 26 second

958

27 27 27 next

959

29 29 29 second

960

30 30 30 next

961

include/check_slave_is_running.inc

962

963

***** Testing Altering table def scenario *****

964

965

CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

966

/* extra */

967

f5 DOUBLE DEFAULT '2.00',

968

f6 ENUM('a', 'b', 'c') default 'a',

969

f7 DECIMAL(17,9) default '1000.00',

970

f8 MEDIUMBLOB,

971

f9 NUMERIC(6,4) default '2000.00',

972

f10 VARCHAR(1024),

973

f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

974

f12 SET('a', 'b', 'c') default 'b')

975

ENGINE='InnoDB';

976

Warnings:

977

Warning 1264 Out of range value for column 'f9' at row 1

978

979

CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

980

/* extra */

981

f5 DOUBLE DEFAULT '2.00',

982

f6 ENUM('a', 'b', 'c') default 'a',

983

f8 MEDIUMBLOB,

984

f10 VARCHAR(1024),

985

f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

986

f12 SET('a', 'b', 'c') default 'b')

987

ENGINE='InnoDB';

988

989

CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

990

/* extra */

991

f5 DOUBLE DEFAULT '2.00',

992

f6 DECIMAL(17,9) default '1000.00',

993

f7 MEDIUMBLOB,

994

f8 NUMERIC(6,4) default '2000.00',

995

f9 VARCHAR(1024),

996

f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

997

f11 CHAR(255))

998

ENGINE='InnoDB';

999

Warnings:

1000

Warning 1264 Out of range value for column 'f8' at row 1

1001

1002

CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

1003

/* extra */

1004

f5 BIGINT,

1005

f6 BLOB,

1006

f7 DATE,

1007

f8 DATETIME,

1008

f9 FLOAT,

1009

f10 INT,

1010

f11 LONGBLOB,

1011

f12 LONGTEXT,

1012

f13 MEDIUMBLOB,

1013

f14 MEDIUMINT,

1014

f15 MEDIUMTEXT,

1015

f16 REAL,

1016

f17 SMALLINT,

1017

f18 TEXT,

1018

f19 TIME,

1019

f20 TIMESTAMP,

1020

f21 TINYBLOB,

1021

f22 TINYINT,

1022

f23 TINYTEXT,

1023

f24 YEAR,

1024

f25 BINARY(255),

1025

f26 BIT(64),

1026

f27 CHAR(255),

1027

f28 DECIMAL(30,7),

1028

f29 DOUBLE,

1029

f30 ENUM ('a','b', 'c') default 'a',

1030

f31 FLOAT,

1031

f32 NUMERIC(17,9),

1032

f33 SET ('a', 'b', 'c') default 'b',

1033

f34 VARBINARY(1025),

1034

f35 VARCHAR(257)

1035

) ENGINE='InnoDB';

1036

1037

** Alter tables on slave and drop columns **

1038

1039

alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop

1040

f12;

1041

alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;

1042

alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;

1043

alter table t31

1044

drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,

1045

drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,

1046

drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,

1047

drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,

1048

drop f33, drop f34, drop f35;

1049

1050

** Insert Data into Master **

1051

INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:

1052

some var char';

1053

INSERT into t2 values (2, 2, 2, 'second',

1054

2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',

1055

'01234567', 'c'),

1056

(3, 3, 3, 'third',

1057

3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',

1058

'01234567', 'c');

1059

Warnings:

1060

Warning 1264 Out of range value for column 'f9' at row 1

1061

Warning 1264 Out of range value for column 'f9' at row 2

1062

INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';

1063

INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:

1064

binary data';

1065

INSERT into t31 set f1=1, f2=1, f3=1, f4='first';

1066

INSERT into t31 set f1=1, f2=1, f3=2, f4='second',

1067

f9=2.2, f10='seven samurai', f28=222.222, f35='222';

1068

Warnings:

1069

Warning 1366 Incorrect integer value: 'seven samurai' for column 'f10' at row 1

1070

INSERT into t31 values (1, 1, 3, 'third',

1071

/* f5 BIGINT, */ 333333333333333333333333,

1072

/* f6 BLOB, */ '3333333333333333333333',

1073

/* f7 DATE, */ '2007-07-18',

1074

/* f8 DATETIME, */ "2007-07-18",

1075

/* f9 FLOAT, */ 3.33333333,

1076

/* f10 INT, */ 333333333,

1077

/* f11 LONGBLOB, */ '3333333333333333333',

1078

/* f12 LONGTEXT, */ '3333333333333333333',

1079

/* f13 MEDIUMBLOB, */ '3333333333333333333',

1080

/* f14 MEDIUMINT, */ 33,

1081

/* f15 MEDIUMTEXT, */ 3.3,

1082

/* f16 REAL, */ 3.3,

1083

/* f17 SMALLINT, */ 3,

1084

/* f18 TEXT, */ '33',

1085

/* f19 TIME, */ '2:59:58.999',

1086

/* f20 TIMESTAMP, */ 20000303000000,

1087

/* f21 TINYBLOB, */ '3333',

1088

/* f22 TINYINT, */ 3,

1089

/* f23 TINYTEXT, */ '3',

1090

/* f24 YEAR, */ 3000,

1091

/* f25 BINARY(255), */ 'three_33333',

1092

/* f26 BIT(64), */ b'011',

1093

/* f27 CHAR(255), */ 'three',

1094

/* f28 DECIMAL(30,7), */ 3.333,

1095

/* f29 DOUBLE, */ 3.333333333333333333333333333,

1096

/* f30 ENUM ('a','b','c')*/ 'c',

1097

/* f31 FLOAT, */ 3.0,

1098

/* f32 NUMERIC(17,9), */ 3.3333,

1099

/* f33 SET ('a','b','c'),*/ 'c',

1100

/*f34 VARBINARY(1025),*/ '3333 minus 3',

1101

/*f35 VARCHAR(257),*/ 'three times three'

1102

);

1103

Warnings:

1104

Warning 1264 Out of range value for column 'f5' at row 1

1105

Warning 1264 Out of range value for column 'f24' at row 1

1106

INSERT into t31 values (1, 1, 4, 'fourth',

1107

/* f5 BIGINT, */ 333333333333333333333333,

1108

/* f6 BLOB, */ '3333333333333333333333',

1109

/* f7 DATE, */ '2007-07-18',

1110

/* f8 DATETIME, */ "2007-07-18",

1111

/* f9 FLOAT, */ 3.33333333,

1112

/* f10 INT, */ 333333333,

1113

/* f11 LONGBLOB, */ '3333333333333333333',

1114

/* f12 LONGTEXT, */ '3333333333333333333',

1115

/* f13 MEDIUMBLOB, */ '3333333333333333333',

1116

/* f14 MEDIUMINT, */ 33,

1117

/* f15 MEDIUMTEXT, */ 3.3,

1118

/* f16 REAL, */ 3.3,

1119

/* f17 SMALLINT, */ 3,

1120

/* f18 TEXT, */ '33',

1121

/* f19 TIME, */ '2:59:58.999',

1122

/* f20 TIMESTAMP, */ 20000303000000,

1123

/* f21 TINYBLOB, */ '3333',

1124

/* f22 TINYINT, */ 3,

1125

/* f23 TINYTEXT, */ '3',

1126

/* f24 YEAR, */ 3000,

1127

/* f25 BINARY(255), */ 'three_33333',

1128

/* f26 BIT(64), */ b'011',

1129

/* f27 CHAR(255), */ 'three',

1130

/* f28 DECIMAL(30,7), */ 3.333,

1131

/* f29 DOUBLE, */ 3.333333333333333333333333333,

1132

/* f30 ENUM ('a','b','c')*/ 'c',

1133

/* f31 FLOAT, */ 3.0,

1134

/* f32 NUMERIC(17,9), */ 3.3333,

1135

/* f33 SET ('a','b','c'),*/ 'c',

1136

/*f34 VARBINARY(1025),*/ '3333 minus 3',

1137

/*f35 VARCHAR(257),*/ 'three times three'

1138

1139

(1, 1, 5, 'fifth',

1140

/* f5 BIGINT, */ 333333333333333333333333,

1141

/* f6 BLOB, */ '3333333333333333333333',

1142

/* f7 DATE, */ '2007-07-18',

1143

/* f8 DATETIME, */ "2007-07-18",

1144

/* f9 FLOAT, */ 3.33333333,

1145

/* f10 INT, */ 333333333,

1146

/* f11 LONGBLOB, */ '3333333333333333333',

1147

/* f12 LONGTEXT, */ '3333333333333333333',

1148

/* f13 MEDIUMBLOB, */ '3333333333333333333',

1149

/* f14 MEDIUMINT, */ 33,

1150

/* f15 MEDIUMTEXT, */ 3.3,

1151

/* f16 REAL, */ 3.3,

1152

/* f17 SMALLINT, */ 3,

1153

/* f18 TEXT, */ '33',

1154

/* f19 TIME, */ '2:59:58.999',

1155

/* f20 TIMESTAMP, */ 20000303000000,

1156

/* f21 TINYBLOB, */ '3333',

1157

/* f22 TINYINT, */ 3,

1158

/* f23 TINYTEXT, */ '3',

1159

/* f24 YEAR, */ 3000,

1160

/* f25 BINARY(255), */ 'three_33333',

1161

/* f26 BIT(64), */ b'011',

1162

/* f27 CHAR(255), */ 'three',

1163

/* f28 DECIMAL(30,7), */ 3.333,

1164

/* f29 DOUBLE, */ 3.333333333333333333333333333,

1165

/* f30 ENUM ('a','b','c')*/ 'c',

1166

/* f31 FLOAT, */ 3.0,

1167

/* f32 NUMERIC(17,9), */ 3.3333,

1168

/* f33 SET ('a','b','c'),*/ 'c',

1169

/*f34 VARBINARY(1025),*/ '3333 minus 3',

1170

/*f35 VARCHAR(257),*/ 'three times three'

1171

1172

(1, 1, 6, 'sixth',

1173

/* f5 BIGINT, */ NULL,

1174

/* f6 BLOB, */ '3333333333333333333333',

1175

/* f7 DATE, */ '2007-07-18',

1176

/* f8 DATETIME, */ "2007-07-18",

1177

/* f9 FLOAT, */ 3.33333333,

1178

/* f10 INT, */ 333333333,

1179

/* f11 LONGBLOB, */ '3333333333333333333',

1180

/* f12 LONGTEXT, */ '3333333333333333333',

1181

/* f13 MEDIUMBLOB, */ '3333333333333333333',

1182

/* f14 MEDIUMINT, */ 33,

1183

/* f15 MEDIUMTEXT, */ 3.3,

1184

/* f16 REAL, */ 3.3,

1185

/* f17 SMALLINT, */ 3,

1186

/* f18 TEXT, */ '33',

1187

/* f19 TIME, */ '2:59:58.999',

1188

/* f20 TIMESTAMP, */ 20000303000000,

1189

/* f21 TINYBLOB, */ '3333',

1190

/* f22 TINYINT, */ 3,

1191

/* f23 TINYTEXT, */ '3',

1192

/* f24 YEAR, */ 3000,

1193

/* f25 BINARY(255), */ 'three_33333',

1194

/* f26 BIT(64), */ b'011',

1195

/* f27 CHAR(255), */ 'three',

1196

/* f28 DECIMAL(30,7), */ 3.333,

1197

/* f29 DOUBLE, */ 3.333333333333333333333333333,

1198

/* f30 ENUM ('a','b','c')*/ 'c',

1199

/* f31 FLOAT, */ 3.0,

1200

/* f32 NUMERIC(17,9), */ 3.3333,

1201

/* f33 SET ('a','b','c'),*/ 'c',

1202

/*f34 VARBINARY(1025),*/ '3333 minus 3',

1203

/*f35 VARCHAR(257),*/ NULL

1204

);

1205

Warnings:

1206

Warning 1264 Out of range value for column 'f5' at row 1

1207

Warning 1264 Out of range value for column 'f24' at row 1

1208

Warning 1264 Out of range value for column 'f5' at row 2

1209

Warning 1264 Out of range value for column 'f24' at row 2

1210

Warning 1264 Out of range value for column 'f24' at row 3

1211

1212

** Sync slave with master **

1213

** Do selects from tables **

1214

1215

select * from t1 order by f3;

1216

f1 f2 f3 f4

1217

2 2 2 second

1218

3 3 3 next

1219

5 5 5 second

1220

6 6 6 next

1221

8 8 8 second

1222

9 9 9 next

1223

11 11 11 second

1224

12 12 12 next

1225

14 14 14 second

1226

15 15 15 next

1227

17 17 17 second

1228

18 18 18 next

1229

20 20 20 second

1230

21 21 21 next

1231

23 23 23 second

1232

24 24 24 next

1233

26 26 26 second

1234

27 27 27 next

1235

29 29 29 second

1236

30 30 30 next

1237

31 31 31 second

1238

32 32 32 second

1239

33 33 33 second

1240

34 34 34 second

1241

35 35 35 second

1242

36 36 36 second

1243

37 37 37 second

1244

38 38 38 second

1245

39 39 39 second

1246

40 40 40 second

1247

41 41 41 second

1248

42 42 42 second

1249

43 43 43 second

1250

44 44 44 second

1251

45 45 45 second

1252

46 46 46 second

1253

47 47 47 second

1254

48 48 48 second

1255

49 49 49 second

1256

50 50 50 second

1257

select * from t2 order by f1;

1258

f1 f2 f3 f4

1259

1 1 1 first

1260

2 2 2 second

1261

3 3 3 third

1262

select * from t3 order by f1;

1263

f1 f2 f3 f4

1264

1 1 1 first

1265

select * from t4 order by f1;

1266

f1 f2 f3 f4

1267

1 1 1 first

1268

select * from t31 order by f3;

1269

f1 f2 f3 f4

1270

1 1 1 first

1271

1 1 2 second

1272

1 1 3 third

1273

1 1 4 fourth

1274

1 1 5 fifth

1275

1 1 6 sixth

1276

1277

** Do updates master **

1278

1279

update t31 set f5=555555555555555 where f3=6;

1280

update t31 set f2=2 where f3=2;

1281

update t31 set f1=NULL where f3=1;

1282

update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;

1283

Warnings:

1284

Warning 1048 Column 'f3' cannot be null

1285

1286

** Delete from Master **

1287

1288

delete from t1;

1289

delete from t2;

1290

delete from t3;

1291

delete from t4;

1292

delete from t31;

1293

select * from t31;

1294

f1 f2 f3 f4

1295

1296

** Check slave status **

1297

1298

include/check_slave_is_running.inc

1299

1300

****************************************

1301

* columns in master at middle of table *

1302

* Expect: Proper error message *

1303

****************************************

1304

1305

** Stop and Reset Slave **

1306

1307

STOP SLAVE;

1308

RESET SLAVE;

1309

1310

** create table slave side **

1311

CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)

1312

) ENGINE='InnoDB';

1313

1314

** Connect to master and create table **

1315

1316

CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',

1317

c CHAR(5), e INT DEFAULT '1')ENGINE='InnoDB';

1318

RESET MASTER;

1319

1320

*** Start Slave ***

1321

START SLAVE;

1322

1323

*** Master Data Insert ***

1324

set @b1 = 'b1b1b1b1';

1325

set @b1 = concat(@b1,@b1);

1326

INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),

1327

(2,@b1,DEFAULT,'JOE',DEFAULT),

1328

(3,@b1,DEFAULT,'QA',DEFAULT);

1329

1330

********************************************

1331

*** Expect slave to fail with Error 1535 ***

1332

********************************************

1333

1334

include/wait_for_slave_sql_error_and_skip.inc [errno=1535]

1335

Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254'

1336

1337

*** Drop t10 ***

1338

DROP TABLE t10;

1339

1340

*********************************************

1341

* More columns in master at middle of table *

1342

* Expect: Proper error message *

1343

*********************************************

1344

1345

*** Create t11 on slave ***

1346

STOP SLAVE;

1347

RESET SLAVE;

1348

CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)

1349

) ENGINE='InnoDB';

1350

1351

*** Create t11 on Master ***

1352

CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,

1353

c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';

1354

RESET MASTER;

1355

1356

*** Start Slave ***

1357

START SLAVE;

1358

1359

*** Master Data Insert ***

1360

set @b1 = 'b1b1b1b1';

1361

set @b1 = concat(@b1,@b1);

1362

INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),

1363

(2,@b1,'Testing is cool','JOE',DEFAULT),

1364

(3,@b1,DEFAULT,'QA',DEFAULT);

1365

1366

********************************************

1367

*** Expect slave to fail with Error 1535 ***

1368

********************************************

1369

1370

include/wait_for_slave_sql_error_and_skip.inc [errno=1535]

1371

Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15'

1372

1373

*** Drop t11 ***

1374

DROP TABLE t11;

1375

1376

*********************************************

1377

* More columns in master at middle of table *

1378

* Expect: This one should pass blob-text *

1379

*********************************************

1380

1381

*** Create t12 on slave ***

1382

STOP SLAVE;

1383

RESET SLAVE;

1384

CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB

1385

) ENGINE='InnoDB';

1386

1387

*** Create t12 on Master ***

1388

CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,

1389

c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';

1390

RESET MASTER;

1391

1392

*** Start Slave ***

1393

START SLAVE;

1394

1395

*** Master Data Insert ***

1396

set @b1 = 'b1b1b1b1';

1397

set @b1 = concat(@b1,@b1);

1398

INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

1399

(2,@b1,'JOE',DEFAULT,DEFAULT),

1400

(3,@b1,'QA',DEFAULT,DEFAULT);

1401

1402

SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;

1403

a hex(b) f c e

1404

1 62316231623162316231623162316231 Kyle test 1

1405

2 62316231623162316231623162316231 JOE test 1

1406

3 62316231623162316231623162316231 QA test 1

1407

1408

*** Select on Slave ***

1409

SELECT a,hex(b),c FROM t12 ORDER BY a;

1410

a hex(b) c

1411

1 62316231623162316231623162316231 Kyle

1412

2 62316231623162316231623162316231 JOE

1413

3 62316231623162316231623162316231 QA

1414

1415

*** Drop t12 ***

1416

DROP TABLE t12;

1417

1418

****************************************************

1419

* - Alter Master adding columns at middle of table *

1420

* Expect: columns added *

1421

****************************************************

1422

1423

1424

*** Create t14 on slave ***

1425

STOP SLAVE;

1426

RESET SLAVE;

1427

CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

1428

) ENGINE='InnoDB';

1429

1430

*** Create t14 on Master ***

1431

CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

1432

c6 INT DEFAULT '1',

1433

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

1434

)ENGINE='InnoDB';

1435

RESET MASTER;

1436

1437

*** Start Slave ***

1438

START SLAVE;

1439

1440

*** Master Data Insert ***

1441

ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;

1442

ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;

1443

1444

set @b1 = 'b1b1b1b1';

1445

set @b1 = concat(@b1,@b1);

1446

INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),

1447

(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),

1448

(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);

1449

1450

SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;

1451

c1 c2 c3 hex(c4) c5 c6 c7

1452

1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

1453

2 2.00 This Test Should work 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP

1454

3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP

1455

1456

*** Select on Slave ****

1457

SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;

1458

c1 c2 c3 hex(c4) c5

1459

1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle

1460

2 2.00 This Test Should work 62316231623162316231623162316231 JOE

1461

3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA

1462

1463

****************************************************

1464

* - Alter Master Dropping columns from the middle. *

1465

* Expect: columns dropped *

1466

****************************************************

1467

1468

*** connect to master and drop columns ***

1469

ALTER TABLE t14 DROP COLUMN c2;

1470

ALTER TABLE t14 DROP COLUMN c7;

1471

1472

*** Select from Master ***

1473

SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;

1474

c1 c3 hex(c4) c5 c6

1475

1 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1

1476

2 This Test Should work 62316231623162316231623162316231 JOE 1

1477

3 If is does not, I will open a bug 62316231623162316231623162316231 QA 1

1478

1479

************

1480

* Bug30415 *

1481

************

1482

include/wait_for_slave_sql_error.inc [errno=1091]

1483

Last_SQL_Error = 'Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7''

1484

STOP SLAVE;

1485

RESET SLAVE;

1486

1487

*** Drop t14 ***

1488

DROP TABLE t14;

1489

DROP TABLE t14;

1490

RESET MASTER;

1491

START SLAVE;

1492

1493

*************************************************

1494

* - Alter Master adding columns at end of table *

1495

* Expect: Error 1054 *

1496

*************************************************

1497

1498

*** Create t15 on slave ***

1499

STOP SLAVE;

1500

RESET SLAVE;

1501

CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

1502

) ENGINE='InnoDB';

1503

1504

*** Create t15 on Master ***

1505

CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

1506

c6 INT DEFAULT '1',

1507

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

1508

)ENGINE='InnoDB';

1509

RESET MASTER;

1510

1511

*** Start Slave ***

1512

START SLAVE;

1513

1514

*** Master Data Insert ***

1515

ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;

1516

set @b1 = 'b1b1b1b1';

1517

set @b1 = concat(@b1,@b1);

1518

INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),

1519

(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),

1520

(3,@b1,'QA',DEFAULT,DEFAULT,3.00);

1521

SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;

1522

c1 hex(c4) c5 c6 c7 c2

1523

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 3.00

1524

2 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 3.00

1525

3 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 3.00

1526

1527

********************************************

1528

*** Expect slave to fail with Error 1054 ***

1529

********************************************

1530

1531

include/wait_for_slave_sql_error.inc [errno=1054]

1532

Last_SQL_Error = 'Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7''

1533

STOP SLAVE;

1534

RESET SLAVE;

1535

1536

*** Drop t15 ***

1537

DROP TABLE t15;

1538

DROP TABLE t15;

1539

RESET MASTER;

1540

START SLAVE;

1541

1542

************************************************

1543

* - Create index on Master column not on slave *

1544

* Expect:Warning *

1545

************************************************

1546

1547

*** Create t16 on slave ***

1548

STOP SLAVE;

1549

RESET SLAVE;

1550

CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

1551

) ENGINE='InnoDB';

1552

1553

*** Create t16 on Master ***

1554

CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

1555

c6 INT DEFAULT '1',

1556

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

1557

)ENGINE='InnoDB';

1558

RESET MASTER;

1559

1560

*** Start Slave ***

1561

START SLAVE;

1562

1563

*** Master Create Index and Data Insert ***

1564

CREATE INDEX part_of_c6 ON t16 (c6);

1565

set @b1 = 'b1b1b1b1';

1566

set @b1 = concat(@b1,@b1);

1567

INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

1568

(2,@b1,'JOE',2,DEFAULT),

1569

(3,@b1,'QA',3,DEFAULT);

1570

SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;

1571

c1 hex(c4) c5 c6 c7

1572

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

1573

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

1574

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

1575

1576

*****************

1577

*** BUG 30434 ***

1578

*****************

1579

1580

include/wait_for_slave_sql_error.inc [errno=1072]

1581

Last_SQL_Error = 'Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)''

1582

STOP SLAVE;

1583

RESET SLAVE;

1584

1585

*** Drop t16 ***

1586

DROP TABLE t16;

1587

DROP TABLE t16;

1588

RESET MASTER;

1589

START SLAVE;

1590

1591

*****************************************************

1592

* - Delete rows using column on Master not on slave *

1593

* Expect: Rows Deleted *

1594

*****************************************************

1595

1596

*** Create t17 on slave ***

1597

STOP SLAVE;

1598

RESET SLAVE;

1599

CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

1600

) ENGINE='InnoDB';

1601

1602

*** Create t17 on Master ***

1603

CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

1604

c6 INT DEFAULT '1',

1605

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

1606

)ENGINE='InnoDB';

1607

RESET MASTER;

1608

1609

*** Start Slave ***

1610

START SLAVE;

1611

1612

*** Master Data Insert ***

1613

set @b1 = 'b1b1b1b1';

1614

set @b1 = concat(@b1,@b1);

1615

INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

1616

(2,@b1,'JOE',2,DEFAULT),

1617

(3,@b1,'QA',3,DEFAULT);

1618

SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;

1619

c1 hex(c4) c5 c6 c7

1620

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

1621

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

1622

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

1623

1624

** Select * from Slave **

1625

SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;

1626

c1 hex(c4) c5

1627

1 62316231623162316231623162316231 Kyle

1628

2 62316231623162316231623162316231 JOE

1629

3 62316231623162316231623162316231 QA

1630

1631

** Delete from master **

1632

DELETE FROM t17 WHERE c6 = 3;

1633

SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;

1634

c1 hex(c4) c5 c6 c7

1635

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

1636

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

1637

1638

** Check slave **

1639

SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;

1640

c1 hex(c4) c5

1641

1 62316231623162316231623162316231 Kyle

1642

2 62316231623162316231623162316231 JOE

1643

DROP TABLE t17;

1644

1645

1646

*****************************************************

1647

* - Update row using column on Master not on slave *

1648

* Expect: Rows updated *

1649

*****************************************************

1650

1651

** Bug30674 **

1652

1653

*** Create t18 on slave ***

1654

1655

STOP SLAVE;

1656

RESET SLAVE;

1657

CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

1658

) ENGINE='InnoDB';

1659

1660

*** Create t18 on Master ***

1661

CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

1662

c6 INT DEFAULT '1',

1663

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

1664

)ENGINE='InnoDB';

1665

RESET MASTER;

1666

1667

*** Start Slave ***

1668

START SLAVE;

1669

1670

*** Master Data Insert ***

1671

set @b1 = 'b1b1b1b1';

1672

set @b1 = concat(@b1,@b1);

1673

INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

1674

(2,@b1,'JOE',2,DEFAULT),

1675

(3,@b1,'QA',3,DEFAULT);

1676

SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;

1677

c1 hex(c4) c5 c6 c7

1678

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

1679

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

1680

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

1681

1682

** Select * from Slave **

1683

SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;

1684

c1 hex(c4) c5

1685

1 62316231623162316231623162316231 Kyle

1686

2 62316231623162316231623162316231 JOE

1687

3 62316231623162316231623162316231 QA

1688

1689

** update from master **

1690

UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;

1691

SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;

1692

c1 hex(c4) c5 c6 c7

1693

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

1694

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

1695

3 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP

1696

1697

** Check slave **

1698

SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;

1699

c1 hex(c4) c5

1700

1 62316231623162316231623162316231 Kyle

1701

2 62316231623162316231623162316231 JOE

1702

3 62316231623162316231623162316231 TEST

1703

DROP TABLE t18;

1704

1705

1706

*****************************************************

1707

* - Insert UUID column on Master not on slave *

1708

* Expect: Rows inserted *

1709

*****************************************************

1710

1711

*** Create t5 on slave ***

1712

STOP SLAVE;

1713

RESET SLAVE;

1714

CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

1715

) ENGINE='InnoDB';

1716

1717

*** Create t5 on Master ***

1718

CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

1719

c6 LONG,

1720

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

1721

)ENGINE='InnoDB';

1722

RESET MASTER;

1723

1724

*** Start Slave ***

1725

START SLAVE;

1726

1727

*** Master Data Insert ***

1728

set @b1 = 'b1b1b1b1';

1729

INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),

1730

(2,@b1,'JOE',UUID(),DEFAULT),

1731

(3,@b1,'QA',UUID(),DEFAULT);

1732

SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;

1733

c1 hex(c4) c5 c6 c7

1734

1 6231623162316231 Kyle UUID TIME

1735

2 6231623162316231 JOE UUID TIME

1736

3 6231623162316231 QA UUID TIME

1737

1738

** Select * from Slave **

1739

SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;

1740

c1 hex(c4) c5

1741

1 6231623162316231 Kyle

1742

2 6231623162316231 JOE

1743

3 6231623162316231 QA

1744

DROP TABLE t5;

1745

set binlog_format=mixed;

1746

1747

***********************************************************

1748

***********************************************************

1749

***************** Start of Testing ************************

1750

***********************************************************

1751

***********************************************************

1752

* This test format == binlog_format MIXED and engine == 'InnoDB'

1753

***********************************************************

1754

***********************************************************

1755

1756

***** Testing more columns on the Master *****

1757

1758

CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

1759

/* extra */

1760

f5 FLOAT DEFAULT '2.00',

1761

f6 CHAR(4) DEFAULT 'TEST',

1762

f7 INT DEFAULT '0',

1763

f8 TEXT,

1764

f9 LONGBLOB,

1765

f10 BIT(63),

1766

f11 VARBINARY(64))ENGINE='InnoDB';

1767

1768

* Alter Table on Slave and drop columns f5 through f11 *

1769

1770

alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;

1771

1772

* Insert data in Master then update and delete some rows*

1773

1774

* Select count and 20 rows from Master *

1775

1776

SELECT COUNT(*) FROM t1;

1777

COUNT(*)

1778

1779

1780

SELECT f1,f2,f3,f4,f5,f6,f7,f8,f9,

1781

hex(f10),hex(f11) FROM t1 ORDER BY f3 LIMIT 20;

1782

f1 f2 f3 f4 f5 f6 f7 f8 f9 hex(f10) hex(f11)

1783

2 2 2 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1784

3 3 3 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1785

5 5 5 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1786

6 6 6 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1787

8 8 8 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1788

9 9 9 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1789

11 11 11 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1790

12 12 12 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1791

14 14 14 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1792

15 15 15 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1793

17 17 17 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1794

18 18 18 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1795

20 20 20 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1796

21 21 21 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1797

23 23 23 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1798

24 24 24 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1799

26 26 26 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1800

27 27 27 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1801

29 29 29 second 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1802

30 30 30 next 2 kaks 2 got stolen from the paradise very fat blob 1555 123456

1803

1804

* Select count and 20 rows from Slave *

1805

1806

SELECT COUNT(*) FROM t1;

1807

COUNT(*)

1808

1809

1810

SELECT * FROM t1 ORDER BY f3 LIMIT 20;

1811

f1 f2 f3 f4

1812

2 2 2 second

1813

3 3 3 next

1814

5 5 5 second

1815

6 6 6 next

1816

8 8 8 second

1817

9 9 9 next

1818

11 11 11 second

1819

12 12 12 next

1820

14 14 14 second

1821

15 15 15 next

1822

17 17 17 second

1823

18 18 18 next

1824

20 20 20 second

1825

21 21 21 next

1826

23 23 23 second

1827

24 24 24 next

1828

26 26 26 second

1829

27 27 27 next

1830

29 29 29 second

1831

30 30 30 next

1832

include/check_slave_is_running.inc

1833

1834

***** Testing Altering table def scenario *****

1835

1836

CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

1837

/* extra */

1838

f5 DOUBLE DEFAULT '2.00',

1839

f6 ENUM('a', 'b', 'c') default 'a',

1840

f7 DECIMAL(17,9) default '1000.00',

1841

f8 MEDIUMBLOB,

1842

f9 NUMERIC(6,4) default '2000.00',

1843

f10 VARCHAR(1024),

1844

f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

1845

f12 SET('a', 'b', 'c') default 'b')

1846

ENGINE='InnoDB';

1847

Warnings:

1848

Warning 1264 Out of range value for column 'f9' at row 1

1849

1850

CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

1851

/* extra */

1852

f5 DOUBLE DEFAULT '2.00',

1853

f6 ENUM('a', 'b', 'c') default 'a',

1854

f8 MEDIUMBLOB,

1855

f10 VARCHAR(1024),

1856

f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

1857

f12 SET('a', 'b', 'c') default 'b')

1858

ENGINE='InnoDB';

1859

1860

CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

1861

/* extra */

1862

f5 DOUBLE DEFAULT '2.00',

1863

f6 DECIMAL(17,9) default '1000.00',

1864

f7 MEDIUMBLOB,

1865

f8 NUMERIC(6,4) default '2000.00',

1866

f9 VARCHAR(1024),

1867

f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

1868

f11 CHAR(255))

1869

ENGINE='InnoDB';

1870

Warnings:

1871

Warning 1264 Out of range value for column 'f8' at row 1

1872

1873

CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),

1874

/* extra */

1875

f5 BIGINT,

1876

f6 BLOB,

1877

f7 DATE,

1878

f8 DATETIME,

1879

f9 FLOAT,

1880

f10 INT,

1881

f11 LONGBLOB,

1882

f12 LONGTEXT,

1883

f13 MEDIUMBLOB,

1884

f14 MEDIUMINT,

1885

f15 MEDIUMTEXT,

1886

f16 REAL,

1887

f17 SMALLINT,

1888

f18 TEXT,

1889

f19 TIME,

1890

f20 TIMESTAMP,

1891

f21 TINYBLOB,

1892

f22 TINYINT,

1893

f23 TINYTEXT,

1894

f24 YEAR,

1895

f25 BINARY(255),

1896

f26 BIT(64),

1897

f27 CHAR(255),

1898

f28 DECIMAL(30,7),

1899

f29 DOUBLE,

1900

f30 ENUM ('a','b', 'c') default 'a',

1901

f31 FLOAT,

1902

f32 NUMERIC(17,9),

1903

f33 SET ('a', 'b', 'c') default 'b',

1904

f34 VARBINARY(1025),

1905

f35 VARCHAR(257)

1906

) ENGINE='InnoDB';

1907

1908

** Alter tables on slave and drop columns **

1909

1910

alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop

1911

f12;

1912

alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;

1913

alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;

1914

alter table t31

1915

drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,

1916

drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,

1917

drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,

1918

drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,

1919

drop f33, drop f34, drop f35;

1920

1921

** Insert Data into Master **

1922

INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:

1923

some var char';

1924

INSERT into t2 values (2, 2, 2, 'second',

1925

2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',

1926

'01234567', 'c'),

1927

(3, 3, 3, 'third',

1928

3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',

1929

'01234567', 'c');

1930

Warnings:

1931

Warning 1264 Out of range value for column 'f9' at row 1

1932

Warning 1264 Out of range value for column 'f9' at row 2

1933

INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';

1934

INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:

1935

binary data';

1936

INSERT into t31 set f1=1, f2=1, f3=1, f4='first';

1937

INSERT into t31 set f1=1, f2=1, f3=2, f4='second',

1938

f9=2.2, f10='seven samurai', f28=222.222, f35='222';

1939

Warnings:

1940

Warning 1366 Incorrect integer value: 'seven samurai' for column 'f10' at row 1

1941

INSERT into t31 values (1, 1, 3, 'third',

1942

/* f5 BIGINT, */ 333333333333333333333333,

1943

/* f6 BLOB, */ '3333333333333333333333',

1944

/* f7 DATE, */ '2007-07-18',

1945

/* f8 DATETIME, */ "2007-07-18",

1946

/* f9 FLOAT, */ 3.33333333,

1947

/* f10 INT, */ 333333333,

1948

/* f11 LONGBLOB, */ '3333333333333333333',

1949

/* f12 LONGTEXT, */ '3333333333333333333',

1950

/* f13 MEDIUMBLOB, */ '3333333333333333333',

1951

/* f14 MEDIUMINT, */ 33,

1952

/* f15 MEDIUMTEXT, */ 3.3,

1953

/* f16 REAL, */ 3.3,

1954

/* f17 SMALLINT, */ 3,

1955

/* f18 TEXT, */ '33',

1956

/* f19 TIME, */ '2:59:58.999',

1957

/* f20 TIMESTAMP, */ 20000303000000,

1958

/* f21 TINYBLOB, */ '3333',

1959

/* f22 TINYINT, */ 3,

1960

/* f23 TINYTEXT, */ '3',

1961

/* f24 YEAR, */ 3000,

1962

/* f25 BINARY(255), */ 'three_33333',

1963

/* f26 BIT(64), */ b'011',

1964

/* f27 CHAR(255), */ 'three',

1965

/* f28 DECIMAL(30,7), */ 3.333,

1966

/* f29 DOUBLE, */ 3.333333333333333333333333333,

1967

/* f30 ENUM ('a','b','c')*/ 'c',

1968

/* f31 FLOAT, */ 3.0,

1969

/* f32 NUMERIC(17,9), */ 3.3333,

1970

/* f33 SET ('a','b','c'),*/ 'c',

1971

/*f34 VARBINARY(1025),*/ '3333 minus 3',

1972

/*f35 VARCHAR(257),*/ 'three times three'

1973

);

1974

Warnings:

1975

Warning 1264 Out of range value for column 'f5' at row 1

1976

Warning 1264 Out of range value for column 'f24' at row 1

1977

INSERT into t31 values (1, 1, 4, 'fourth',

1978

/* f5 BIGINT, */ 333333333333333333333333,

1979

/* f6 BLOB, */ '3333333333333333333333',

1980

/* f7 DATE, */ '2007-07-18',

1981

/* f8 DATETIME, */ "2007-07-18",

1982

/* f9 FLOAT, */ 3.33333333,

1983

/* f10 INT, */ 333333333,

1984

/* f11 LONGBLOB, */ '3333333333333333333',

1985

/* f12 LONGTEXT, */ '3333333333333333333',

1986

/* f13 MEDIUMBLOB, */ '3333333333333333333',

1987

/* f14 MEDIUMINT, */ 33,

1988

/* f15 MEDIUMTEXT, */ 3.3,

1989

/* f16 REAL, */ 3.3,

1990

/* f17 SMALLINT, */ 3,

1991

/* f18 TEXT, */ '33',

1992

/* f19 TIME, */ '2:59:58.999',

1993

/* f20 TIMESTAMP, */ 20000303000000,

1994

/* f21 TINYBLOB, */ '3333',

1995

/* f22 TINYINT, */ 3,

1996

/* f23 TINYTEXT, */ '3',

1997

/* f24 YEAR, */ 3000,

1998

/* f25 BINARY(255), */ 'three_33333',

1999

/* f26 BIT(64), */ b'011',

2000

/* f27 CHAR(255), */ 'three',

2001

/* f28 DECIMAL(30,7), */ 3.333,

2002

/* f29 DOUBLE, */ 3.333333333333333333333333333,

2003

/* f30 ENUM ('a','b','c')*/ 'c',

2004

/* f31 FLOAT, */ 3.0,

2005

/* f32 NUMERIC(17,9), */ 3.3333,

2006

/* f33 SET ('a','b','c'),*/ 'c',

2007

/*f34 VARBINARY(1025),*/ '3333 minus 3',

2008

/*f35 VARCHAR(257),*/ 'three times three'

2009

2010

(1, 1, 5, 'fifth',

2011

/* f5 BIGINT, */ 333333333333333333333333,

2012

/* f6 BLOB, */ '3333333333333333333333',

2013

/* f7 DATE, */ '2007-07-18',

2014

/* f8 DATETIME, */ "2007-07-18",

2015

/* f9 FLOAT, */ 3.33333333,

2016

/* f10 INT, */ 333333333,

2017

/* f11 LONGBLOB, */ '3333333333333333333',

2018

/* f12 LONGTEXT, */ '3333333333333333333',

2019

/* f13 MEDIUMBLOB, */ '3333333333333333333',

2020

/* f14 MEDIUMINT, */ 33,

2021

/* f15 MEDIUMTEXT, */ 3.3,

2022

/* f16 REAL, */ 3.3,

2023

/* f17 SMALLINT, */ 3,

2024

/* f18 TEXT, */ '33',

2025

/* f19 TIME, */ '2:59:58.999',

2026

/* f20 TIMESTAMP, */ 20000303000000,

2027

/* f21 TINYBLOB, */ '3333',

2028

/* f22 TINYINT, */ 3,

2029

/* f23 TINYTEXT, */ '3',

2030

/* f24 YEAR, */ 3000,

2031

/* f25 BINARY(255), */ 'three_33333',

2032

/* f26 BIT(64), */ b'011',

2033

/* f27 CHAR(255), */ 'three',

2034

/* f28 DECIMAL(30,7), */ 3.333,

2035

/* f29 DOUBLE, */ 3.333333333333333333333333333,

2036

/* f30 ENUM ('a','b','c')*/ 'c',

2037

/* f31 FLOAT, */ 3.0,

2038

/* f32 NUMERIC(17,9), */ 3.3333,

2039

/* f33 SET ('a','b','c'),*/ 'c',

2040

/*f34 VARBINARY(1025),*/ '3333 minus 3',

2041

/*f35 VARCHAR(257),*/ 'three times three'

2042

2043

(1, 1, 6, 'sixth',

2044

/* f5 BIGINT, */ NULL,

2045

/* f6 BLOB, */ '3333333333333333333333',

2046

/* f7 DATE, */ '2007-07-18',

2047

/* f8 DATETIME, */ "2007-07-18",

2048

/* f9 FLOAT, */ 3.33333333,

2049

/* f10 INT, */ 333333333,

2050

/* f11 LONGBLOB, */ '3333333333333333333',

2051

/* f12 LONGTEXT, */ '3333333333333333333',

2052

/* f13 MEDIUMBLOB, */ '3333333333333333333',

2053

/* f14 MEDIUMINT, */ 33,

2054

/* f15 MEDIUMTEXT, */ 3.3,

2055

/* f16 REAL, */ 3.3,

2056

/* f17 SMALLINT, */ 3,

2057

/* f18 TEXT, */ '33',

2058

/* f19 TIME, */ '2:59:58.999',

2059

/* f20 TIMESTAMP, */ 20000303000000,

2060

/* f21 TINYBLOB, */ '3333',

2061

/* f22 TINYINT, */ 3,

2062

/* f23 TINYTEXT, */ '3',

2063

/* f24 YEAR, */ 3000,

2064

/* f25 BINARY(255), */ 'three_33333',

2065

/* f26 BIT(64), */ b'011',

2066

/* f27 CHAR(255), */ 'three',

2067

/* f28 DECIMAL(30,7), */ 3.333,

2068

/* f29 DOUBLE, */ 3.333333333333333333333333333,

2069

/* f30 ENUM ('a','b','c')*/ 'c',

2070

/* f31 FLOAT, */ 3.0,

2071

/* f32 NUMERIC(17,9), */ 3.3333,

2072

/* f33 SET ('a','b','c'),*/ 'c',

2073

/*f34 VARBINARY(1025),*/ '3333 minus 3',

2074

/*f35 VARCHAR(257),*/ NULL

2075

);

2076

Warnings:

2077

Warning 1264 Out of range value for column 'f5' at row 1

2078

Warning 1264 Out of range value for column 'f24' at row 1

2079

Warning 1264 Out of range value for column 'f5' at row 2

2080

Warning 1264 Out of range value for column 'f24' at row 2

2081

Warning 1264 Out of range value for column 'f24' at row 3

2082

2083

** Sync slave with master **

2084

** Do selects from tables **

2085

2086

select * from t1 order by f3;

2087

f1 f2 f3 f4

2088

2 2 2 second

2089

3 3 3 next

2090

5 5 5 second

2091

6 6 6 next

2092

8 8 8 second

2093

9 9 9 next

2094

11 11 11 second

2095

12 12 12 next

2096

14 14 14 second

2097

15 15 15 next

2098

17 17 17 second

2099

18 18 18 next

2100

20 20 20 second

2101

21 21 21 next

2102

23 23 23 second

2103

24 24 24 next

2104

26 26 26 second

2105

27 27 27 next

2106

29 29 29 second

2107

30 30 30 next

2108

31 31 31 second

2109

32 32 32 second

2110

33 33 33 second

2111

34 34 34 second

2112

35 35 35 second

2113

36 36 36 second

2114

37 37 37 second

2115

38 38 38 second

2116

39 39 39 second

2117

40 40 40 second

2118

41 41 41 second

2119

42 42 42 second

2120

43 43 43 second

2121

44 44 44 second

2122

45 45 45 second

2123

46 46 46 second

2124

47 47 47 second

2125

48 48 48 second

2126

49 49 49 second

2127

50 50 50 second

2128

select * from t2 order by f1;

2129

f1 f2 f3 f4

2130

1 1 1 first

2131

2 2 2 second

2132

3 3 3 third

2133

select * from t3 order by f1;

2134

f1 f2 f3 f4

2135

1 1 1 first

2136

select * from t4 order by f1;

2137

f1 f2 f3 f4

2138

1 1 1 first

2139

select * from t31 order by f3;

2140

f1 f2 f3 f4

2141

1 1 1 first

2142

1 1 2 second

2143

1 1 3 third

2144

1 1 4 fourth

2145

1 1 5 fifth

2146

1 1 6 sixth

2147

2148

** Do updates master **

2149

2150

update t31 set f5=555555555555555 where f3=6;

2151

update t31 set f2=2 where f3=2;

2152

update t31 set f1=NULL where f3=1;

2153

update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;

2154

Warnings:

2155

Warning 1048 Column 'f3' cannot be null

2156

2157

** Delete from Master **

2158

2159

delete from t1;

2160

delete from t2;

2161

delete from t3;

2162

delete from t4;

2163

delete from t31;

2164

select * from t31;

2165

f1 f2 f3 f4

2166

2167

** Check slave status **

2168

2169

include/check_slave_is_running.inc

2170

2171

****************************************

2172

* columns in master at middle of table *

2173

* Expect: Proper error message *

2174

****************************************

2175

2176

** Stop and Reset Slave **

2177

2178

STOP SLAVE;

2179

RESET SLAVE;

2180

2181

** create table slave side **

2182

CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)

2183

) ENGINE='InnoDB';

2184

2185

** Connect to master and create table **

2186

2187

CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233',

2188

c CHAR(5), e INT DEFAULT '1')ENGINE='InnoDB';

2189

RESET MASTER;

2190

2191

*** Start Slave ***

2192

START SLAVE;

2193

2194

*** Master Data Insert ***

2195

set @b1 = 'b1b1b1b1';

2196

set @b1 = concat(@b1,@b1);

2197

INSERT INTO t10 () VALUES(1,@b1,DEFAULT,'Kyle',DEFAULT),

2198

(2,@b1,DEFAULT,'JOE',DEFAULT),

2199

(3,@b1,DEFAULT,'QA',DEFAULT);

2200

2201

********************************************

2202

*** Expect slave to fail with Error 1535 ***

2203

********************************************

2204

2205

include/wait_for_slave_sql_error_and_skip.inc [errno=1535]

2206

Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 type mismatch - received type 5, test.t10 has type 254'

2207

2208

*** Drop t10 ***

2209

DROP TABLE t10;

2210

2211

*********************************************

2212

* More columns in master at middle of table *

2213

* Expect: Proper error message *

2214

*********************************************

2215

2216

*** Create t11 on slave ***

2217

STOP SLAVE;

2218

RESET SLAVE;

2219

CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)

2220

) ENGINE='InnoDB';

2221

2222

*** Create t11 on Master ***

2223

CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,

2224

c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';

2225

RESET MASTER;

2226

2227

*** Start Slave ***

2228

START SLAVE;

2229

2230

*** Master Data Insert ***

2231

set @b1 = 'b1b1b1b1';

2232

set @b1 = concat(@b1,@b1);

2233

INSERT INTO t11 () VALUES(1,@b1,'Testing is fun','Kyle',DEFAULT),

2234

(2,@b1,'Testing is cool','JOE',DEFAULT),

2235

(3,@b1,DEFAULT,'QA',DEFAULT);

2236

2237

********************************************

2238

*** Expect slave to fail with Error 1535 ***

2239

********************************************

2240

2241

include/wait_for_slave_sql_error_and_skip.inc [errno=1535]

2242

Last_SQL_Error = 'Table definition on master and slave does not match: Column 2 type mismatch - received type 252, test.t11 has type 15'

2243

2244

*** Drop t11 ***

2245

DROP TABLE t11;

2246

2247

*********************************************

2248

* More columns in master at middle of table *

2249

* Expect: This one should pass blob-text *

2250

*********************************************

2251

2252

*** Create t12 on slave ***

2253

STOP SLAVE;

2254

RESET SLAVE;

2255

CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB

2256

) ENGINE='InnoDB';

2257

2258

*** Create t12 on Master ***

2259

CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,

2260

c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='InnoDB';

2261

RESET MASTER;

2262

2263

*** Start Slave ***

2264

START SLAVE;

2265

2266

*** Master Data Insert ***

2267

set @b1 = 'b1b1b1b1';

2268

set @b1 = concat(@b1,@b1);

2269

INSERT INTO t12 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

2270

(2,@b1,'JOE',DEFAULT,DEFAULT),

2271

(3,@b1,'QA',DEFAULT,DEFAULT);

2272

2273

SELECT a,hex(b),f,c,e FROM t12 ORDER BY a;

2274

a hex(b) f c e

2275

1 62316231623162316231623162316231 Kyle test 1

2276

2 62316231623162316231623162316231 JOE test 1

2277

3 62316231623162316231623162316231 QA test 1

2278

2279

*** Select on Slave ***

2280

SELECT a,hex(b),c FROM t12 ORDER BY a;

2281

a hex(b) c

2282

1 62316231623162316231623162316231 Kyle

2283

2 62316231623162316231623162316231 JOE

2284

3 62316231623162316231623162316231 QA

2285

2286

*** Drop t12 ***

2287

DROP TABLE t12;

2288

2289

****************************************************

2290

* - Alter Master adding columns at middle of table *

2291

* Expect: columns added *

2292

****************************************************

2293

2294

2295

*** Create t14 on slave ***

2296

STOP SLAVE;

2297

RESET SLAVE;

2298

CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

2299

) ENGINE='InnoDB';

2300

2301

*** Create t14 on Master ***

2302

CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

2303

c6 INT DEFAULT '1',

2304

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

2305

)ENGINE='InnoDB';

2306

RESET MASTER;

2307

2308

*** Start Slave ***

2309

START SLAVE;

2310

2311

*** Master Data Insert ***

2312

ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;

2313

ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;

2314

2315

set @b1 = 'b1b1b1b1';

2316

set @b1 = concat(@b1,@b1);

2317

INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle',DEFAULT,DEFAULT),

2318

(2,2.00,'This Test Should work',@b1,'JOE',DEFAULT,DEFAULT),

2319

(3,3.00,'If is does not, I will open a bug',@b1,'QA',DEFAULT,DEFAULT);

2320

2321

SELECT c1,c2,c3,hex(c4),c5,c6,c7 FROM t14 ORDER BY c1;

2322

c1 c2 c3 hex(c4) c5 c6 c7

2323

1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

2324

2 2.00 This Test Should work 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP

2325

3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP

2326

2327

*** Select on Slave ****

2328

SELECT c1,c2,c3,hex(c4),c5 FROM t14 ORDER BY c1;

2329

c1 c2 c3 hex(c4) c5

2330

1 1.00 Replication Testing Extra Col 62316231623162316231623162316231 Kyle

2331

2 2.00 This Test Should work 62316231623162316231623162316231 JOE

2332

3 3.00 If is does not, I will open a bug 62316231623162316231623162316231 QA

2333

2334

****************************************************

2335

* - Alter Master Dropping columns from the middle. *

2336

* Expect: columns dropped *

2337

****************************************************

2338

2339

*** connect to master and drop columns ***

2340

ALTER TABLE t14 DROP COLUMN c2;

2341

ALTER TABLE t14 DROP COLUMN c7;

2342

2343

*** Select from Master ***

2344

SELECT c1,c3,hex(c4),c5,c6 FROM t14 ORDER BY c1;

2345

c1 c3 hex(c4) c5 c6

2346

1 Replication Testing Extra Col 62316231623162316231623162316231 Kyle 1

2347

2 This Test Should work 62316231623162316231623162316231 JOE 1

2348

3 If is does not, I will open a bug 62316231623162316231623162316231 QA 1

2349

2350

************

2351

* Bug30415 *

2352

************

2353

include/wait_for_slave_sql_error.inc [errno=1091]

2354

Last_SQL_Error = 'Error 'Can't DROP 'c7'; check that column/key exists' on query. Default database: 'test'. Query: 'ALTER TABLE t14 DROP COLUMN c7''

2355

STOP SLAVE;

2356

RESET SLAVE;

2357

2358

*** Drop t14 ***

2359

DROP TABLE t14;

2360

DROP TABLE t14;

2361

RESET MASTER;

2362

START SLAVE;

2363

2364

*************************************************

2365

* - Alter Master adding columns at end of table *

2366

* Expect: Error 1054 *

2367

*************************************************

2368

2369

*** Create t15 on slave ***

2370

STOP SLAVE;

2371

RESET SLAVE;

2372

CREATE TABLE t15 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

2373

) ENGINE='InnoDB';

2374

2375

*** Create t15 on Master ***

2376

CREATE TABLE t15 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

2377

c6 INT DEFAULT '1',

2378

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

2379

)ENGINE='InnoDB';

2380

RESET MASTER;

2381

2382

*** Start Slave ***

2383

START SLAVE;

2384

2385

*** Master Data Insert ***

2386

ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7;

2387

set @b1 = 'b1b1b1b1';

2388

set @b1 = concat(@b1,@b1);

2389

INSERT INTO t15 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT,3.00),

2390

(2,@b1,'JOE',DEFAULT,DEFAULT,3.00),

2391

(3,@b1,'QA',DEFAULT,DEFAULT,3.00);

2392

SELECT c1,hex(c4),c5,c6,c7,c2 FROM t15 ORDER BY c1;

2393

c1 hex(c4) c5 c6 c7 c2

2394

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP 3.00

2395

2 62316231623162316231623162316231 JOE 1 CURRENT_TIMESTAMP 3.00

2396

3 62316231623162316231623162316231 QA 1 CURRENT_TIMESTAMP 3.00

2397

2398

********************************************

2399

*** Expect slave to fail with Error 1054 ***

2400

********************************************

2401

2402

include/wait_for_slave_sql_error.inc [errno=1054]

2403

Last_SQL_Error = 'Error 'Unknown column 'c7' in 't15'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c2 DECIMAL(8,2) AFTER c7''

2404

STOP SLAVE;

2405

RESET SLAVE;

2406

2407

*** Drop t15 ***

2408

DROP TABLE t15;

2409

DROP TABLE t15;

2410

RESET MASTER;

2411

START SLAVE;

2412

2413

************************************************

2414

* - Create index on Master column not on slave *

2415

* Expect:Warning *

2416

************************************************

2417

2418

*** Create t16 on slave ***

2419

STOP SLAVE;

2420

RESET SLAVE;

2421

CREATE TABLE t16 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

2422

) ENGINE='InnoDB';

2423

2424

*** Create t16 on Master ***

2425

CREATE TABLE t16 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

2426

c6 INT DEFAULT '1',

2427

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

2428

)ENGINE='InnoDB';

2429

RESET MASTER;

2430

2431

*** Start Slave ***

2432

START SLAVE;

2433

2434

*** Master Create Index and Data Insert ***

2435

CREATE INDEX part_of_c6 ON t16 (c6);

2436

set @b1 = 'b1b1b1b1';

2437

set @b1 = concat(@b1,@b1);

2438

INSERT INTO t16 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

2439

(2,@b1,'JOE',2,DEFAULT),

2440

(3,@b1,'QA',3,DEFAULT);

2441

SELECT c1,hex(c4),c5,c6,c7 FROM t16 ORDER BY c1;

2442

c1 hex(c4) c5 c6 c7

2443

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

2444

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

2445

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

2446

2447

*****************

2448

*** BUG 30434 ***

2449

*****************

2450

2451

include/wait_for_slave_sql_error.inc [errno=1072]

2452

Last_SQL_Error = 'Error 'Key column 'c6' doesn't exist in table' on query. Default database: 'test'. Query: 'CREATE INDEX part_of_c6 ON t16 (c6)''

2453

STOP SLAVE;

2454

RESET SLAVE;

2455

2456

*** Drop t16 ***

2457

DROP TABLE t16;

2458

DROP TABLE t16;

2459

RESET MASTER;

2460

START SLAVE;

2461

2462

*****************************************************

2463

* - Delete rows using column on Master not on slave *

2464

* Expect: Rows Deleted *

2465

*****************************************************

2466

2467

*** Create t17 on slave ***

2468

STOP SLAVE;

2469

RESET SLAVE;

2470

CREATE TABLE t17 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

2471

) ENGINE='InnoDB';

2472

2473

*** Create t17 on Master ***

2474

CREATE TABLE t17 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

2475

c6 INT DEFAULT '1',

2476

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

2477

)ENGINE='InnoDB';

2478

RESET MASTER;

2479

2480

*** Start Slave ***

2481

START SLAVE;

2482

2483

*** Master Data Insert ***

2484

set @b1 = 'b1b1b1b1';

2485

set @b1 = concat(@b1,@b1);

2486

INSERT INTO t17 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

2487

(2,@b1,'JOE',2,DEFAULT),

2488

(3,@b1,'QA',3,DEFAULT);

2489

SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;

2490

c1 hex(c4) c5 c6 c7

2491

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

2492

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

2493

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

2494

2495

** Select * from Slave **

2496

SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;

2497

c1 hex(c4) c5

2498

1 62316231623162316231623162316231 Kyle

2499

2 62316231623162316231623162316231 JOE

2500

3 62316231623162316231623162316231 QA

2501

2502

** Delete from master **

2503

DELETE FROM t17 WHERE c6 = 3;

2504

SELECT c1,hex(c4),c5,c6,c7 FROM t17 ORDER BY c1;

2505

c1 hex(c4) c5 c6 c7

2506

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

2507

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

2508

2509

** Check slave **

2510

SELECT c1,hex(c4),c5 FROM t17 ORDER BY c1;

2511

c1 hex(c4) c5

2512

1 62316231623162316231623162316231 Kyle

2513

2 62316231623162316231623162316231 JOE

2514

DROP TABLE t17;

2515

2516

2517

*****************************************************

2518

* - Update row using column on Master not on slave *

2519

* Expect: Rows updated *

2520

*****************************************************

2521

2522

** Bug30674 **

2523

2524

*** Create t18 on slave ***

2525

2526

STOP SLAVE;

2527

RESET SLAVE;

2528

CREATE TABLE t18 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

2529

) ENGINE='InnoDB';

2530

2531

*** Create t18 on Master ***

2532

CREATE TABLE t18 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

2533

c6 INT DEFAULT '1',

2534

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

2535

)ENGINE='InnoDB';

2536

RESET MASTER;

2537

2538

*** Start Slave ***

2539

START SLAVE;

2540

2541

*** Master Data Insert ***

2542

set @b1 = 'b1b1b1b1';

2543

set @b1 = concat(@b1,@b1);

2544

INSERT INTO t18 () VALUES(1,@b1,'Kyle',DEFAULT,DEFAULT),

2545

(2,@b1,'JOE',2,DEFAULT),

2546

(3,@b1,'QA',3,DEFAULT);

2547

SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;

2548

c1 hex(c4) c5 c6 c7

2549

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

2550

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

2551

3 62316231623162316231623162316231 QA 3 CURRENT_TIMESTAMP

2552

2553

** Select * from Slave **

2554

SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;

2555

c1 hex(c4) c5

2556

1 62316231623162316231623162316231 Kyle

2557

2 62316231623162316231623162316231 JOE

2558

3 62316231623162316231623162316231 QA

2559

2560

** update from master **

2561

UPDATE t18 SET c5 = 'TEST' WHERE c6 = 3;

2562

SELECT c1,hex(c4),c5,c6,c7 FROM t18 ORDER BY c1;

2563

c1 hex(c4) c5 c6 c7

2564

1 62316231623162316231623162316231 Kyle 1 CURRENT_TIMESTAMP

2565

2 62316231623162316231623162316231 JOE 2 CURRENT_TIMESTAMP

2566

3 62316231623162316231623162316231 TEST 3 CURRENT_TIMESTAMP

2567

2568

** Check slave **

2569

SELECT c1,hex(c4),c5 FROM t18 ORDER BY c1;

2570

c1 hex(c4) c5

2571

1 62316231623162316231623162316231 Kyle

2572

2 62316231623162316231623162316231 JOE

2573

3 62316231623162316231623162316231 TEST

2574

DROP TABLE t18;

2575

2576

2577

*****************************************************

2578

* - Insert UUID column on Master not on slave *

2579

* Expect: Rows inserted *

2580

*****************************************************

2581

2582

*** Create t5 on slave ***

2583

STOP SLAVE;

2584

RESET SLAVE;

2585

CREATE TABLE t5 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)

2586

) ENGINE='InnoDB';

2587

2588

*** Create t5 on Master ***

2589

CREATE TABLE t5 (c1 INT KEY, c4 BLOB, c5 CHAR(5),

2590

c6 LONG,

2591

c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

2592

)ENGINE='InnoDB';

2593

RESET MASTER;

2594

2595

*** Start Slave ***

2596

START SLAVE;

2597

2598

*** Master Data Insert ***

2599

set @b1 = 'b1b1b1b1';

2600

INSERT INTO t5 () VALUES(1,@b1,'Kyle',UUID(),DEFAULT),

2601

(2,@b1,'JOE',UUID(),DEFAULT),

2602

(3,@b1,'QA',UUID(),DEFAULT);

2603

SELECT c1,hex(c4),c5,c6,c7 FROM t5 ORDER BY c1;

2604

c1 hex(c4) c5 c6 c7

2605

1 6231623162316231 Kyle UUID TIME

2606

2 6231623162316231 JOE UUID TIME

2607

3 6231623162316231 QA UUID TIME

2608

2609

** Select * from Slave **

2610

SELECT c1,hex(c4),c5 FROM t5 ORDER BY c1;

2611

c1 hex(c4) c5

2612

1 6231623162316231 Kyle

2613

2 6231623162316231 JOE

2614

3 6231623162316231 QA

2615

DROP TABLE t5;

2616

include/rpl_end.inc

Older »