#
# BUG#18985760 - "FAST" ALTER TABLE CHANGE ON ENUM COLUMN
#                 TRIGGERS FULL TABLE REBUILD.
# Test case below ensures that the columns with old temporal
# format are not upgraded on the slave since the global variable
# 'avoid_temporal_upgrade' is enabled on the slave.
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 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.
[connection master]
# Copy the table containing the old Mysql-5.5 temporal format
# to the master and slave.
# Enable the 'avoid_temporal_column' global variable on slave.
STOP SLAVE;
SET @@global.avoid_temporal_upgrade= ON;
Warnings:
Warning	1287	'@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
START SLAVE;
# ALTER TABLE on the master.
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
f_time	f_datetime	f_timestamp	HEX(WEIGHT_STRING(f_time))	HEX(WEIGHT_STRING(f_datetime))	HEX(WEIGHT_STRING(f_timestamp))
10:10:10	2010-10-10 10:10:10	2031-11-21 17:11:01	818A92	0000124821911312	7468F975
00:00:00	2000-01-01 01:01:01	2001-01-21 18:11:01	800000	00001230A2EA8AB5	3A6AFC05
01:01:10	2020-01-01 01:01:01	1980-11-21 18:06:01	80277E	0000125F33D85AB5	147BF1D9
NULL	2000-01-01 01:01:01	2015-11-21 17:11:01	NULL	00001230A2EA8AB5	56507B75
00:00:00	NULL	2011-11-21 17:11:01	800000	NULL	4ECA5BF5
ALTER TABLE t1 ADD COLUMN fld1 INT;
Warnings:
Note	1880	TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
INSERT INTO t1 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22', 0);
# Since the global variable 'avoid_temporal_upgrade' is disabled on 
# the master, the temporal columns of old format are upgraded to the
# new format.
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
f_time	f_datetime	f_timestamp	HEX(WEIGHT_STRING(f_time))	HEX(WEIGHT_STRING(f_datetime))	HEX(WEIGHT_STRING(f_timestamp))
10:10:10	2010-10-10 10:10:10	2031-11-21 17:11:01	80A28A	998714A28A	7468F975
00:00:00	2000-01-01 01:01:01	2001-01-21 18:11:01	800000	9964421041	3A6AFC05
01:01:10	2020-01-01 01:01:01	1980-11-21 18:06:01	80104A	99A5421041	147BF1D9
NULL	2000-01-01 01:01:01	2015-11-21 17:11:01	NULL	9964421041	56507B75
00:00:00	NULL	2011-11-21 17:11:01	800000	NULL	4ECA5BF5
22:22:22	2011-11-21 22:22:22	2011-11-21 22:22:22	816596	998AAB6596	4ECAA4EE
include/sync_slave_sql_with_master.inc
# Since 'avoid_temporal_upgrade' is enabled on the slave,
# the old temporal will not be upgraded on the slave.
SET @saved_show_old_temporals= @@session.show_old_temporals;
Warnings:
Warning	1287	'@@show_old_temporals' is deprecated and will be removed in a future release.
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
f_time	f_datetime	f_timestamp	HEX(WEIGHT_STRING(f_time))	HEX(WEIGHT_STRING(f_datetime))	HEX(WEIGHT_STRING(f_timestamp))
10:10:10	2010-10-10 10:10:10	2031-11-21 17:11:01	818A92	0000124821911312	7468F975
00:00:00	2000-01-01 01:01:01	2001-01-21 18:11:01	800000	00001230A2EA8AB5	3A6AFC05
01:01:10	2020-01-01 01:01:01	1980-11-21 18:06:01	80277E	0000125F33D85AB5	147BF1D9
NULL	2000-01-01 01:01:01	2015-11-21 17:11:01	NULL	00001230A2EA8AB5	56507B75
00:00:00	NULL	2011-11-21 17:11:01	800000	NULL	4ECA5BF5
22:22:22	2011-11-21 22:22:22	2011-11-21 22:22:22	83640E	0000124A7C3C8A4E	4ECAA4EE
SET SESSION show_old_temporals= ON;
Warnings:
Warning	1287	'@@show_old_temporals' is deprecated and will be removed in a future release.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_time` time /* 5.5 binary format */ DEFAULT NULL,
  `f_timestamp` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `f_datetime` datetime /* 5.5 binary format */ DEFAULT NULL,
  `fld1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
COLUMN_TYPE
time /* 5.5 binary format */
timestamp /* 5.5 binary format */
datetime /* 5.5 binary format */
int(11)
SET @@session.show_old_temporals= @saved_show_old_temporals;
Warnings:
Warning	1287	'@@show_old_temporals' is deprecated and will be removed in a future release.
#Cleanup
DROP TABLE t1;
include/sync_slave_sql_with_master.inc
# Testcase to ensure that the temporal columns of old format
# are upgraded on the slave since 'avoid_temporal_column'
# global variable is not enabled on the slave.
# Copy the table containing the old Mysql-5.5 temporal format
# to the master and slave.
# Disable the 'avoid_temporal_column' global variable on slave.
STOP SLAVE;
SET @@global.avoid_temporal_upgrade= 0;
Warnings:
Warning	1287	'@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
START SLAVE;
# ALTER TABLE on the master.
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
f_time	f_datetime	f_timestamp	HEX(WEIGHT_STRING(f_time))	HEX(WEIGHT_STRING(f_datetime))	HEX(WEIGHT_STRING(f_timestamp))
10:10:10	2010-10-10 10:10:10	2031-11-21 17:11:01	818A92	0000124821911312	7468F975
00:00:00	2000-01-01 01:01:01	2001-01-21 18:11:01	800000	00001230A2EA8AB5	3A6AFC05
01:01:10	2020-01-01 01:01:01	1980-11-21 18:06:01	80277E	0000125F33D85AB5	147BF1D9
NULL	2000-01-01 01:01:01	2015-11-21 17:11:01	NULL	00001230A2EA8AB5	56507B75
00:00:00	NULL	2011-11-21 17:11:01	800000	NULL	4ECA5BF5
ALTER TABLE t1 ADD COLUMN fld1 INT;
Warnings:
Note	1880	TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
INSERT INTO t1 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22', 0);
# Since the default value of 'avoid_temporal_upgrade' is FALSE,
# the old temporal will be upgraded on the master.
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
f_time	f_datetime	f_timestamp	HEX(WEIGHT_STRING(f_time))	HEX(WEIGHT_STRING(f_datetime))	HEX(WEIGHT_STRING(f_timestamp))
10:10:10	2010-10-10 10:10:10	2031-11-21 17:11:01	80A28A	998714A28A	7468F975
00:00:00	2000-01-01 01:01:01	2001-01-21 18:11:01	800000	9964421041	3A6AFC05
01:01:10	2020-01-01 01:01:01	1980-11-21 18:06:01	80104A	99A5421041	147BF1D9
NULL	2000-01-01 01:01:01	2015-11-21 17:11:01	NULL	9964421041	56507B75
00:00:00	NULL	2011-11-21 17:11:01	800000	NULL	4ECA5BF5
22:22:22	2011-11-21 22:22:22	2011-11-21 22:22:22	816596	998AAB6596	4ECAA4EE
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_time` time DEFAULT NULL,
  `f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `f_datetime` datetime DEFAULT NULL,
  `fld1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
COLUMN_TYPE
time
timestamp
datetime
int(11)
include/sync_slave_sql_with_master.inc
# Since the default value of 'avoid_temporal_upgrade' is FALSE,
# the old temporal will be upgraded on the slave.
SET @saved_show_old_temporals= @@session.show_old_temporals;
Warnings:
Warning	1287	'@@show_old_temporals' is deprecated and will be removed in a future release.
SET SESSION show_old_temporals= ON;
Warnings:
Warning	1287	'@@show_old_temporals' is deprecated and will be removed in a future release.
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
f_time	f_datetime	f_timestamp	HEX(WEIGHT_STRING(f_time))	HEX(WEIGHT_STRING(f_datetime))	HEX(WEIGHT_STRING(f_timestamp))
10:10:10	2010-10-10 10:10:10	2031-11-21 17:11:01	80A28A	998714A28A	7468F975
00:00:00	2000-01-01 01:01:01	2001-01-21 18:11:01	800000	9964421041	3A6AFC05
01:01:10	2020-01-01 01:01:01	1980-11-21 18:06:01	80104A	99A5421041	147BF1D9
NULL	2000-01-01 01:01:01	2015-11-21 17:11:01	NULL	9964421041	56507B75
00:00:00	NULL	2011-11-21 17:11:01	800000	NULL	4ECA5BF5
22:22:22	2011-11-21 22:22:22	2011-11-21 22:22:22	816596	998AAB6596	4ECAA4EE
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f_time` time DEFAULT NULL,
  `f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `f_datetime` datetime DEFAULT NULL,
  `fld1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
COLUMN_TYPE
time
timestamp
datetime
int(11)
SET @@session.show_old_temporals= @saved_show_old_temporals;
Warnings:
Warning	1287	'@@show_old_temporals' is deprecated and will be removed in a future release.
# cleanup
DROP TABLE t1;
include/sync_slave_sql_with_master.inc
include/rpl_end.inc
