2
# BUG#18985760 - "FAST" ALTER TABLE CHANGE ON ENUM COLUMN
3
# TRIGGERS FULL TABLE REBUILD.
4
# Test case below ensures that the columns with old temporal
5
# format are not upgraded on the slave since the global variable
6
# 'avoid_temporal_upgrade' is enabled on the slave.
7
include/master-slave.inc
9
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
10
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
12
# Copy the table containing the old Mysql-5.5 temporal format
13
# to the master and slave.
14
# Enable the 'avoid_temporal_column' global variable on slave.
16
SET @@global.avoid_temporal_upgrade= ON;
18
Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
20
# ALTER TABLE on the master.
21
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
22
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
23
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
24
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 818A92 0000124821911312 7468F975
25
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 00001230A2EA8AB5 3A6AFC05
26
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80277E 0000125F33D85AB5 147BF1D9
27
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 00001230A2EA8AB5 56507B75
28
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
29
ALTER TABLE t1 ADD COLUMN fld1 INT;
31
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
32
INSERT INTO t1 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22', 0);
33
# Since the global variable 'avoid_temporal_upgrade' is disabled on
34
# the master, the temporal columns of old format are upgraded to the
36
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
37
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
38
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
39
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 80A28A 998714A28A 7468F975
40
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 9964421041 3A6AFC05
41
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80104A 99A5421041 147BF1D9
42
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 9964421041 56507B75
43
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
44
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
45
include/sync_slave_sql_with_master.inc
46
# Since 'avoid_temporal_upgrade' is enabled on the slave,
47
# the old temporal will not be upgraded on the slave.
48
SET @saved_show_old_temporals= @@session.show_old_temporals;
50
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
51
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
52
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
53
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
54
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 818A92 0000124821911312 7468F975
55
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 00001230A2EA8AB5 3A6AFC05
56
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80277E 0000125F33D85AB5 147BF1D9
57
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 00001230A2EA8AB5 56507B75
58
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
59
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
60
SET SESSION show_old_temporals= ON;
62
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
65
t1 CREATE TABLE `t1` (
66
`f_time` time /* 5.5 binary format */ DEFAULT NULL,
67
`f_timestamp` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
68
`f_datetime` datetime /* 5.5 binary format */ DEFAULT NULL,
69
`fld1` int(11) DEFAULT NULL
70
) ENGINE=MyISAM DEFAULT CHARSET=latin1
71
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
73
time /* 5.5 binary format */
74
timestamp /* 5.5 binary format */
75
datetime /* 5.5 binary format */
77
SET @@session.show_old_temporals= @saved_show_old_temporals;
79
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
82
include/sync_slave_sql_with_master.inc
83
# Testcase to ensure that the temporal columns of old format
84
# are upgraded on the slave since 'avoid_temporal_column'
85
# global variable is not enabled on the slave.
86
# Copy the table containing the old Mysql-5.5 temporal format
87
# to the master and slave.
88
# Disable the 'avoid_temporal_column' global variable on slave.
90
SET @@global.avoid_temporal_upgrade= 0;
92
Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
94
# ALTER TABLE on the master.
95
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
96
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
97
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
98
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 818A92 0000124821911312 7468F975
99
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 00001230A2EA8AB5 3A6AFC05
100
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80277E 0000125F33D85AB5 147BF1D9
101
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 00001230A2EA8AB5 56507B75
102
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
103
ALTER TABLE t1 ADD COLUMN fld1 INT;
105
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
106
INSERT INTO t1 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22', 0);
107
# Since the default value of 'avoid_temporal_upgrade' is FALSE,
108
# the old temporal will be upgraded on the master.
109
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
110
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
111
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
112
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 80A28A 998714A28A 7468F975
113
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 9964421041 3A6AFC05
114
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80104A 99A5421041 147BF1D9
115
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 9964421041 56507B75
116
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
117
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
118
SHOW CREATE TABLE t1;
120
t1 CREATE TABLE `t1` (
121
`f_time` time DEFAULT NULL,
122
`f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
123
`f_datetime` datetime DEFAULT NULL,
124
`fld1` int(11) DEFAULT NULL
125
) ENGINE=MyISAM DEFAULT CHARSET=latin1
126
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
132
include/sync_slave_sql_with_master.inc
133
# Since the default value of 'avoid_temporal_upgrade' is FALSE,
134
# the old temporal will be upgraded on the slave.
135
SET @saved_show_old_temporals= @@session.show_old_temporals;
137
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
138
SET SESSION show_old_temporals= ON;
140
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
141
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
142
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
143
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
144
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 80A28A 998714A28A 7468F975
145
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 9964421041 3A6AFC05
146
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80104A 99A5421041 147BF1D9
147
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 9964421041 56507B75
148
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
149
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
150
SHOW CREATE TABLE t1;
152
t1 CREATE TABLE `t1` (
153
`f_time` time DEFAULT NULL,
154
`f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
155
`f_datetime` datetime DEFAULT NULL,
156
`fld1` int(11) DEFAULT NULL
157
) ENGINE=MyISAM DEFAULT CHARSET=latin1
158
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
164
SET @@session.show_old_temporals= @saved_show_old_temporals;
166
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
169
include/sync_slave_sql_with_master.inc