1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
#
# Test of function defaults on replicated tables.
#
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
connection master
SET TIME_ZONE="+10:30";
SET TIMESTAMP=123456.789123;
SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP
1970-01-02 20:47:36
connection slave
SET TIME_ZONE="+00:00";
SET TIMESTAMP=987654321.123456;
SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP
2001-04-19 04:25:21
connection master
CREATE TABLE t1 (
a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
b TIMESTAMP(1) NOT NULL DEFAULT CURRENT_TIMESTAMP(1),
c TIMESTAMP(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2),
d TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
e TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),
f TIMESTAMP(5) NOT NULL DEFAULT CURRENT_TIMESTAMP(5),
g TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
h DATETIME DEFAULT CURRENT_TIMESTAMP,
i DATETIME(1) DEFAULT CURRENT_TIMESTAMP(1),
j DATETIME(2) DEFAULT CURRENT_TIMESTAMP(2),
k DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
l DATETIME(4) DEFAULT CURRENT_TIMESTAMP(4),
m DATETIME(5) DEFAULT CURRENT_TIMESTAMP(5),
n DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
o INT
);
INSERT INTO t1 ( o ) VALUES ( 1 );
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t2 (
a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
b TIMESTAMP(1) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(1),
c TIMESTAMP(2) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(2),
d TIMESTAMP(3) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(3),
e TIMESTAMP(4) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(4),
f TIMESTAMP(5) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(5),
g TIMESTAMP(6) NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP(6),
h DATETIME ON UPDATE CURRENT_TIMESTAMP,
i DATETIME(1) ON UPDATE CURRENT_TIMESTAMP(1),
j DATETIME(2) ON UPDATE CURRENT_TIMESTAMP(2),
k DATETIME(3) ON UPDATE CURRENT_TIMESTAMP(3),
l DATETIME(4) ON UPDATE CURRENT_TIMESTAMP(4),
m DATETIME(5) ON UPDATE CURRENT_TIMESTAMP(5),
n DATETIME(6) ON UPDATE CURRENT_TIMESTAMP(6),
o INT
);
INSERT INTO t2 ( o ) VALUES ( 1 );
SET sql_mode = default;
sync_slave_with_master
include/sync_slave_sql_with_master.inc
connection slave
SELECT * FROM t1;
a 1970-01-02 10:17:36
b 1970-01-02 10:17:36.7
c 1970-01-02 10:17:36.78
d 1970-01-02 10:17:36.789
e 1970-01-02 10:17:36.7891
f 1970-01-02 10:17:36.78912
g 1970-01-02 10:17:36.789123
h 1970-01-02 20:47:36
i 1970-01-02 20:47:36.7
j 1970-01-02 20:47:36.78
k 1970-01-02 20:47:36.789
l 1970-01-02 20:47:36.7891
m 1970-01-02 20:47:36.78912
n 1970-01-02 20:47:36.789123
o 1
SELECT * FROM t2;
a 0000-00-00 00:00:00
b 0000-00-00 00:00:00.0
c 0000-00-00 00:00:00.00
d 0000-00-00 00:00:00.000
e 0000-00-00 00:00:00.0000
f 0000-00-00 00:00:00.00000
g 0000-00-00 00:00:00.000000
h NULL
i NULL
j NULL
k NULL
l NULL
m NULL
n NULL
o 1
connection master
SET TIMESTAMP=1234567890.123456;
SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP
2009-02-14 10:01:30
UPDATE t1 SET o = 2;
UPDATE t2 SET o = 2;
sync_slave_with_master
include/sync_slave_sql_with_master.inc
connection slave
SELECT * FROM t1;
a 1970-01-02 10:17:36
b 1970-01-02 10:17:36.7
c 1970-01-02 10:17:36.78
d 1970-01-02 10:17:36.789
e 1970-01-02 10:17:36.7891
f 1970-01-02 10:17:36.78912
g 1970-01-02 10:17:36.789123
h 1970-01-02 20:47:36
i 1970-01-02 20:47:36.7
j 1970-01-02 20:47:36.78
k 1970-01-02 20:47:36.789
l 1970-01-02 20:47:36.7891
m 1970-01-02 20:47:36.78912
n 1970-01-02 20:47:36.789123
o 2
SELECT * FROM t2;
a 2009-02-13 23:31:30
b 2009-02-13 23:31:30.1
c 2009-02-13 23:31:30.12
d 2009-02-13 23:31:30.123
e 2009-02-13 23:31:30.1234
f 2009-02-13 23:31:30.12345
g 2009-02-13 23:31:30.123456
h 2009-02-14 10:01:30
i 2009-02-14 10:01:30.1
j 2009-02-14 10:01:30.12
k 2009-02-14 10:01:30.123
l 2009-02-14 10:01:30.1234
m 2009-02-14 10:01:30.12345
n 2009-02-14 10:01:30.123456
o 2
connection master
DROP TABLE t1, t2;
include/rpl_end.inc
|
