From bc4a456758c8077e5377b8cfaed60af4311653a0 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 17 Oct 2012 15:43:56 +0300 Subject: MDEV-452 Add full support for auto-initialized/updated timestamp and datetime Generalized support for auto-updated and/or auto-initialized timestamp and datetime columns. This patch is a reimplementation of MySQL's "WL#5874: CURRENT_TIMESTAMP as DEFAULT for DATETIME columns". In order to ease future merges, this implementation reused few function and variable names from MySQL's patch, however the implementation is quite different. TODO: The only unresolved problem in this patch is the semantics of LOAD DATA for TIMESTAMP and DATETIME columns in the cases when there are missing or NULL columns. I couldn't fully comprehend the logic behind MySQL's behavior and its relationship with their own documentation, so I left the results to be more consistent with all other LOAD cases. The problematic test cases can be seen by running the test file function_defaults, and observing the test case differences. Those were left on purpose for discussion. --- sql/sql_table.cc | 89 ++++++++++++++++++++++++++++++++++++++------------------ 1 file changed, 61 insertions(+), 28 deletions(-) (limited to 'sql/sql_table.cc') diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 5a6090e5d9a..e66d4f529de 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2607,7 +2607,6 @@ void calculate_interval_lengths(CHARSET_INFO *cs, TYPELIB *interval, prepare_create_field() sql_field field to prepare for packing blob_columns count for BLOBs - timestamps count for timestamps table_flags table flags DESCRIPTION @@ -2621,7 +2620,6 @@ void calculate_interval_lengths(CHARSET_INFO *cs, TYPELIB *interval, int prepare_create_field(Create_field *sql_field, uint *blob_columns, - int *timestamps, int *timestamps_with_niladic, longlong table_flags) { unsigned int dup_val_count; @@ -2743,21 +2741,6 @@ int prepare_create_field(Create_field *sql_field, (sql_field->decimals << FIELDFLAG_DEC_SHIFT)); break; case MYSQL_TYPE_TIMESTAMP: - /* We should replace old TIMESTAMP fields with their newer analogs */ - if (sql_field->unireg_check == Field::TIMESTAMP_OLD_FIELD) - { - if (!*timestamps) - { - sql_field->unireg_check= Field::TIMESTAMP_DNUN_FIELD; - (*timestamps_with_niladic)++; - } - else - sql_field->unireg_check= Field::NONE; - } - else if (sql_field->unireg_check != Field::NONE) - (*timestamps_with_niladic)++; - - (*timestamps)++; /* fall-through */ default: sql_field->pack_flag=(FIELDFLAG_NUMBER | @@ -2829,6 +2812,40 @@ bool check_duplicate_warning(THD *thd, char *msg, ulong length) } +/** + Modifies the first column definition whose SQL type is TIMESTAMP + by adding the features DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. + + @param column_definitions The list of column definitions, in the physical + order in which they appear in the table. + */ +void promote_first_timestamp_column(List *column_definitions) +{ + List_iterator it(*column_definitions); + Create_field *column_definition; + + while ((column_definition= it++) != NULL) + { + if (column_definition->sql_type == MYSQL_TYPE_TIMESTAMP || // TIMESTAMP + column_definition->unireg_check == Field::TIMESTAMP_OLD_FIELD) // Legacy + { + if ((column_definition->flags & NOT_NULL_FLAG) != 0 && // NOT NULL, + column_definition->def == NULL && // no constant default, + column_definition->unireg_check == Field::NONE) // no function default + { + DBUG_PRINT("info", ("First TIMESTAMP column '%s' was promoted to " + "DEFAULT CURRENT_TIMESTAMP ON UPDATE " + "CURRENT_TIMESTAMP", + column_definition->field_name + )); + column_definition->unireg_check= Field::TIMESTAMP_DNUN_FIELD; + } + return; + } + } +} + + /* Preparation for table creation @@ -2869,7 +2886,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, ulong record_offset= 0; KEY *key_info; KEY_PART_INFO *key_part_info; - int timestamps= 0, timestamps_with_niladic= 0; int field_no,dup_no; int select_field_pos,auto_increment=0; List_iterator it(alter_info->create_list); @@ -3153,7 +3169,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, DBUG_ASSERT(sql_field->charset != 0); if (prepare_create_field(sql_field, &blob_columns, - ×tamps, ×tamps_with_niladic, file->ha_table_flags())) DBUG_RETURN(TRUE); if (sql_field->sql_type == MYSQL_TYPE_VARCHAR) @@ -3184,12 +3199,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, record_offset+= sql_field->pack_length; } } - if (timestamps_with_niladic > 1) - { - my_message(ER_TOO_MUCH_AUTO_TIMESTAMP_COLS, - ER(ER_TOO_MUCH_AUTO_TIMESTAMP_COLS), MYF(0)); - DBUG_RETURN(TRUE); - } if (auto_increment > 1) { my_message(ER_WRONG_AUTO_KEY, ER(ER_WRONG_AUTO_KEY), MYF(0)); @@ -4558,6 +4567,8 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table, /* Got lock. */ DEBUG_SYNC(thd, "locked_table_name"); + promote_first_timestamp_column(&alter_info->create_list); + result= mysql_create_table_no_lock(thd, create_table->db, create_table->table_name, create_info, alter_info, FALSE, 0, &is_trans); @@ -6371,6 +6382,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, need_copy_table= alter_info->change_level; set_table_default_charset(thd, create_info, db); + promote_first_timestamp_column(&alter_info->create_list); if (thd->variables.old_alter_table || (table->s->db_type() != create_info->db_type) @@ -6668,6 +6680,7 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, DEBUG_SYNC(thd, "alter_table_before_create_table_no_lock"); DBUG_EXECUTE_IF("sleep_before_create_table_no_lock", my_sleep(100000);); + /* Create a table with a temporary name. With create_info->frm_only == 1 this creates a .frm file only. @@ -6738,8 +6751,6 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name, */ if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)) { - /* We don't want update TIMESTAMP fields during ALTER TABLE. */ - new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; new_table->next_number_field=new_table->found_next_number_field; DBUG_EXECUTE_IF("abort_copy_table", { my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); @@ -7316,6 +7327,7 @@ copy_data_between_tables(THD *thd, TABLE *from,TABLE *to, ulonglong prev_insert_id, time_to_report_progress; List_iterator it(create); Create_field *def; + Field **dfield_ptr= to->default_field; DBUG_ENTER("copy_data_between_tables"); /* Two or 3 stages; Sorting, copying data and update indexes */ @@ -7345,6 +7357,7 @@ copy_data_between_tables(THD *thd, TABLE *from,TABLE *to, errpos= 3; copy_end=copy; + to->s->default_fields= 0; for (Field **ptr=to->field ; *ptr ; ptr++) { def=it++; @@ -7364,8 +7377,23 @@ copy_data_between_tables(THD *thd, TABLE *from,TABLE *to, } (copy_end++)->set(*ptr,def->field,0); } - + else + { + /* + Update the set of auto-update fields to contain only the newly added + fields. Only these fields should be updated automatically. Old fields + keep their current values, and therefore should not be present in the + set of autoupdate fields. + */ + if ((*ptr)->has_insert_default_function()) + { + *(dfield_ptr++)= *ptr; + ++to->s->default_fields; + } + } } + if (dfield_ptr) + *dfield_ptr= NULL; if (order) { @@ -7456,6 +7484,11 @@ copy_data_between_tables(THD *thd, TABLE *from,TABLE *to, prev_insert_id= to->file->next_insert_id; if (to->vfield) update_virtual_fields(thd, to, TRUE); + if (to->default_field && to->update_default_fields()) + { + error= 1; + break; + } if (thd->is_error()) { error= 1; -- cgit v1.2.1