Skip to content

Commit cc1fd36

Browse files
author
Nisha Gopalakrishnan
committed
BUG#17246318: ALTER TABLE SHOULD NOT ALLOW CREATION OF TABLES
 WITH BOTH 5.5 AND 5.6 TEMPORALS Analysis -------- 'ALTER TABLE' allows creation of tables containing temporal columns of both mysql-5.5 and mysql-5.6 format. Recreating tables having both 5.5 and 5.6 temporals becomes tedious process when the metadata file(.frm) for the table is unavailable. This would involve recreating the table having the old temporal columns using 5.5 server instance and then adding the 5.6 temporal columns using 5.6 server instance. Also recreating tables having 5.5 temporals and 5.6 INNODB FULLTEXT indexes has to follow the same process. Currently operations are supported on the old temporal columns. The old temporal columns are upgraded to the new format only when they are altered(like renaming the old temporal column) else they are retained in the old format. In order to overcome the above mentioned tedious process and use the space efficient new temporal format, the old temporal types are upgraded for certain ALTER TABLE operations listed in the fix info. Fix info: --------- The columns of old temporal types of mysql-5.5 are upgraded to mysql-5.6 format when ALTER TABLE requests ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE operation. Since such conversion cannot be done using INPLACE algorithm, the attempt to use ALGORITHM=INPLACE clause in such ALTER TABLE for a table with mysql-5.5 temporals leads to an error. Also a 'NOTE' is reported to indicate the upgrade of the old temporal columns to the new format under the above condition.
1 parent 9465072 commit cc1fd36

6 files changed

Lines changed: 154 additions & 60 deletions

File tree

‎mysql-test/r/type_temporal_upgrade.result‎

Lines changed: 12 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -211,47 +211,19 @@ NULL NULL
211211
0000-00-00 00:00:00 0000000000000000
212212
2011-11-21 22:22:23 0000124A7C3C8A4F
213213
#
214-
# This ALTER does not change old fields to new fields
215-
# Still expect old WEIGHT_STRING for the TIME and DATETIME fields
214+
# This ALTER should change old fields to the new fields
215+
# Expect new WEIGHT_STRING for the TIME and DATETIME fields
216216
#
217-
ALTER TABLE t1 FORCE;
218-
INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5');
219-
SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1;
220-
f_time HEX(WEIGHT_STRING(f_time))
221-
10:10:10 818A92
222-
00:00:00 800000
223-
01:01:10 80277E
224-
NULL NULL
225-
00:00:00 800000
226-
838:59:59 FFF5A7
227-
-838:59:59 000A59
228-
00:00:00 800000
229-
22:22:23 83640F
230-
22:22:23 83640F
231-
SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
232-
f_timestamp HEX(WEIGHT_STRING(f_timestamp))
233-
2031-11-21 17:11:01 7468F975
234-
2001-01-21 18:11:01 3A6AFC05
235-
1980-11-21 18:06:01 147BF1D9
236-
2015-11-21 17:11:01 56507B75
237-
2011-11-21 17:11:01 4ECA5BF5
238-
0000-00-00 00:00:00 00000000
239-
0000-00-00 00:00:00 00000000
240-
0000-00-00 00:00:00 00000000
241-
2011-11-21 22:22:23 4ECAA4EF
242-
2011-11-21 22:22:23 4ECAA4EF
243-
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
244-
f_datetime HEX(WEIGHT_STRING(f_datetime))
245-
2010-10-10 10:10:10 0000124821911312
246-
2000-01-01 01:01:01 00001230A2EA8AB5
247-
2020-01-01 01:01:01 0000125F33D85AB5
248-
2000-01-01 01:01:01 00001230A2EA8AB5
249-
NULL NULL
250-
0000-00-00 00:00:00 0000000000000000
251-
0000-00-00 00:00:00 0000000000000000
252-
0000-00-00 00:00:00 0000000000000000
253-
2011-11-21 22:22:23 0000124A7C3C8A4F
254-
2011-11-21 22:22:23 0000124A7C3C8A4F
217+
CREATE TABLE t2 LIKE t1;
218+
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
219+
ALTER TABLE t2 FORCE;
220+
Warnings:
221+
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
222+
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
223+
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
224+
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
225+
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
226+
DROP TABLE t2;
255227
#
256228
# This ALTER should change old fields to new fields
257229
# Expecting new WEIGHT_STRING the rounding.
@@ -270,7 +242,6 @@ NULL NULL
270242
00:00:00 800000
271243
22:22:23 816597
272244
22:22:23 816597
273-
22:22:23 816597
274245
SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
275246
f_timestamp HEX(WEIGHT_STRING(f_timestamp))
276247
2031-11-21 17:11:01 7468F975
@@ -283,7 +254,6 @@ f_timestamp HEX(WEIGHT_STRING(f_timestamp))
283254
0000-00-00 00:00:00 00000000
284255
2011-11-21 22:22:23 4ECAA4EF
285256
2011-11-21 22:22:23 4ECAA4EF
286-
2011-11-21 22:22:23 4ECAA4EF
287257
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
288258
f_datetime HEX(WEIGHT_STRING(f_datetime))
289259
2010-10-10 10:10:10 998714A28A
@@ -296,6 +266,5 @@ NULL NULL
296266
0000-00-00 00:00:00 8000000000
297267
2011-11-21 22:22:23 998AAB6597
298268
2011-11-21 22:22:23 998AAB6597
299-
2011-11-21 22:22:23 998AAB6597
300269
DROP TABLE t1;
301270
SET time_zone=DEFAULT;

‎mysql-test/t/type_temporal_upgrade.test‎

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -70,14 +70,15 @@ SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
7070
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
7171

7272
--echo #
73-
--echo # This ALTER does not change old fields to new fields
74-
--echo # Still expect old WEIGHT_STRING for the TIME and DATETIME fields
73+
--echo # This ALTER should change old fields to the new fields
74+
--echo # Expect new WEIGHT_STRING for the TIME and DATETIME fields
7575
--echo #
76-
ALTER TABLE t1 FORCE;
77-
INSERT INTO t1 VALUES ('22:22:22.5','2011-11-21 22:22:22.5','2011-11-21 22:22:22.5');
78-
SELECT f_time, HEX(WEIGHT_STRING(f_time)) FROM t1;
79-
SELECT f_timestamp, HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
80-
SELECT f_datetime, HEX(WEIGHT_STRING(f_datetime)) FROM t1;
76+
CREATE TABLE t2 LIKE t1;
77+
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
78+
ALTER TABLE t2 FORCE;
79+
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
80+
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
81+
DROP TABLE t2;
8182

8283
--echo #
8384
--echo # This ALTER should change old fields to new fields

‎sql/field.cc‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -9714,11 +9714,12 @@ void Create_field::init_for_tmp_table(enum_field_types sql_type_arg,
97149714
TRUE on error.
97159715
*/
97169716

9717-
bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type,
9718-
char *fld_length, char *fld_decimals,
9719-
uint fld_type_modifier, Item *fld_default_value,
9720-
Item *fld_on_update_value, LEX_STRING *fld_comment,
9721-
char *fld_change, List<String> *fld_interval_list,
9717+
bool Create_field::init(THD *thd, const char *fld_name,
9718+
enum_field_types fld_type, const char *fld_length,
9719+
const char *fld_decimals, uint fld_type_modifier,
9720+
Item *fld_default_value, Item *fld_on_update_value,
9721+
LEX_STRING *fld_comment, const char *fld_change,
9722+
List<String> *fld_interval_list,
97229723
const CHARSET_INFO *fld_charset, uint fld_geom_type)
97239724
{
97249725
uint sign_len, allowed_type_modifier= 0;

‎sql/field.h‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3817,11 +3817,11 @@ class Create_field :public Sql_alloc
38173817
bool maybe_null, bool is_unsigned,
38183818
uint pack_length = ~0U);
38193819

3820-
bool init(THD *thd, char *field_name, enum_field_types type, char *length,
3821-
char *decimals, uint type_modifier, Item *default_value,
3822-
Item *on_update_value, LEX_STRING *comment, char *change,
3823-
List<String> *interval_list, const CHARSET_INFO *cs,
3824-
uint uint_geom_type);
3820+
bool init(THD *thd, const char *field_name, enum_field_types type,
3821+
const char *length, const char *decimals, uint type_modifier,
3822+
Item *default_value, Item *on_update_value, LEX_STRING *comment,
3823+
const char *change, List<String> *interval_list,
3824+
const CHARSET_INFO *cs, uint uint_geom_type);
38253825

38263826
ha_storage_media field_storage_type() const
38273827
{

‎sql/share/errmsg-utf8.txt‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7091,6 +7091,9 @@ ER_TEMP_FILE_WRITE_FAILURE
70917091
ER_INNODB_FT_AUX_NOT_HEX_ID
70927092
eng "Upgrade index name failed, please use create index(alter table) algorithm copy to rebuild index."
70937093

7094+
ER_OLD_TEMPORALS_UPGRADED
7095+
eng "TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format."
7096+
70947097
#
70957098
# End of 5.6 error messages.
70967099
#

‎sql/sql_table.cc‎

Lines changed: 120 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6661,6 +6661,123 @@ blob_length_by_type(enum_field_types type)
66616661
}
66626662

66636663

6664+
/**
6665+
Convert the old temporal data types to the new temporal
6666+
type format for ADD/CHANGE COLUMN, ADD INDEXES and ALTER
6667+
FORCE ALTER operation.
6668+
6669+
@param thd Thread context.
6670+
@param alter_info Alter info parameters.
6671+
6672+
@retval true Error.
6673+
@retval false Either the old temporal data types
6674+
are not present or they are present
6675+
and have been successfully upgraded.
6676+
*/
6677+
6678+
static bool
6679+
upgrade_old_temporal_types(THD *thd, Alter_info *alter_info)
6680+
{
6681+
bool old_temporal_type_present= false;
6682+
6683+
DBUG_ENTER("upgrade_old_temporal_types");
6684+
6685+
if (!((alter_info->flags & Alter_info::ALTER_ADD_COLUMN) ||
6686+
(alter_info->flags & Alter_info::ALTER_ADD_INDEX) ||
6687+
(alter_info->flags & Alter_info::ALTER_CHANGE_COLUMN) ||
6688+
(alter_info->flags & Alter_info::ALTER_RECREATE)))
6689+
DBUG_RETURN(false);
6690+
6691+
/*
6692+
Upgrade the old temporal types if any, for ADD/CHANGE COLUMN/
6693+
ADD INDEXES and FORCE ALTER operation.
6694+
*/
6695+
Create_field *def;
6696+
List_iterator<Create_field> create_it(alter_info->create_list);
6697+
6698+
while ((def= create_it++))
6699+
{
6700+
// Check if any old temporal type is present.
6701+
if ((def->sql_type == MYSQL_TYPE_TIME) ||
6702+
(def->sql_type == MYSQL_TYPE_DATETIME) ||
6703+
(def->sql_type == MYSQL_TYPE_TIMESTAMP))
6704+
{
6705+
old_temporal_type_present= true;
6706+
break;
6707+
}
6708+
}
6709+
6710+
// Upgrade is not required since there are no old temporal types.
6711+
if (!old_temporal_type_present)
6712+
DBUG_RETURN(false);
6713+
6714+
// Upgrade old temporal types to the new temporal types.
6715+
create_it.rewind();
6716+
while ((def= create_it++))
6717+
{
6718+
enum enum_field_types sql_type;
6719+
Item *default_value= def->def, *update_value= NULL;
6720+
6721+
/*
6722+
Set CURRENT_TIMESTAMP as default/update value based on
6723+
the unireg_check value.
6724+
*/
6725+
6726+
if ((def->sql_type == MYSQL_TYPE_DATETIME ||
6727+
def->sql_type == MYSQL_TYPE_TIMESTAMP)
6728+
&& (def->unireg_check != Field::NONE))
6729+
{
6730+
Item_func_now_local *now = new (thd->mem_root) Item_func_now_local(0);
6731+
if (!now)
6732+
DBUG_RETURN(true);
6733+
6734+
if (def->unireg_check == Field::TIMESTAMP_DN_FIELD)
6735+
default_value= now;
6736+
else if (def->unireg_check == Field::TIMESTAMP_UN_FIELD)
6737+
update_value= now;
6738+
else if (def->unireg_check == Field::TIMESTAMP_DNUN_FIELD)
6739+
{
6740+
update_value= now;
6741+
default_value= now;
6742+
}
6743+
}
6744+
6745+
switch (def->sql_type)
6746+
{
6747+
case MYSQL_TYPE_TIME:
6748+
sql_type= MYSQL_TYPE_TIME2;
6749+
break;
6750+
case MYSQL_TYPE_DATETIME:
6751+
sql_type= MYSQL_TYPE_DATETIME2;
6752+
break;
6753+
case MYSQL_TYPE_TIMESTAMP:
6754+
sql_type= MYSQL_TYPE_TIMESTAMP2;
6755+
break;
6756+
default:
6757+
continue;
6758+
}
6759+
6760+
// Replace the old temporal field with the new temporal field.
6761+
Create_field *temporal_field= NULL;
6762+
if (!(temporal_field= new (thd->mem_root) Create_field()) ||
6763+
temporal_field->init(thd, def->field_name, sql_type, NULL, NULL,
6764+
(def->flags & NOT_NULL_FLAG), default_value,
6765+
update_value, &def->comment, def->change, NULL,
6766+
NULL, 0))
6767+
DBUG_RETURN(true);
6768+
6769+
temporal_field->field= def->field;
6770+
create_it.replace(temporal_field);
6771+
}
6772+
6773+
// Report a NOTE informing about the upgrade.
6774+
push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
6775+
ER_OLD_TEMPORALS_UPGRADED,
6776+
ER(ER_OLD_TEMPORALS_UPGRADED));
6777+
DBUG_RETURN(false);
6778+
}
6779+
6780+
66646781
/**
66656782
Prepare column and key definitions for CREATE TABLE in ALTER TABLE.
66666783
@@ -7960,6 +8077,9 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
79608077
alter_info->requested_algorithm= Alter_info::ALTER_TABLE_ALGORITHM_COPY;
79618078
}
79628079

8080+
if (upgrade_old_temporal_types(thd, alter_info))
8081+
DBUG_RETURN(true);
8082+
79638083
/*
79648084
If the old table had partitions and we are doing ALTER TABLE ...
79658085
engine= <new_engine>, the new table must preserve the original

0 commit comments

Comments
 (0)