MySQL Bugs: #43508: Renaming timestamp or date column triggers table copy

Bug #43508	Renaming timestamp or date column triggers table copy
Submitted:	9 Mar 2009 16:47	Modified:	18 Dec 2009 20:45
Reporter:	Olivier Chédru	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.32, 5.1 bzr	OS:	Any (MS Windows, Linux)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
When altering a table to change the name of a timestamp, the table is actually copied. This is not optimal.

How to repeat:
CREATE DATABASE `test_timestamp`;
CREATE TABLE  `test_timestamp`.`test` (
  `t` timestamp NULL DEFAULT NULL,
  `i` int(11) DEFAULT NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

Under debugger, set a breakpoint in sql_table.cc, line 5369 (function compare_tables), then run:

ALTER TABLE `test_timestamp`.`test` CHANGE COLUMN `t` `t_changed` TIMESTAMP NULL DEFAULT NULL;

Notice *need_copy_table= ALTER_TABLE_DATA_CHANGED.

This is because the new field has not the BINARY_FLAG (charset is binary on old field and UTF-8 on new field).

Thank you for the report.

Verified as described.

We override the charset for these types, deep in creating fields.

field.cc:9998
  switch (field_type) {
  case MYSQL_TYPE_DATE:
  case MYSQL_TYPE_NEWDATE:
  case MYSQL_TYPE_TIME:
  case MYSQL_TYPE_DATETIME:
  case MYSQL_TYPE_TIMESTAMP:
    field_charset= &my_charset_bin;

And later in the constructor for Field_timestamp's superclass Field_str, we have

  if (charset_arg->state & MY_CS_BINSORT)
    flags|=BINARY_FLAG;

The alter-table code takes another path and never encounters this setting of charset or BINARY_FLAG.

So, when we run bool Field_str::compare_str_field_flags, which compares these flags, the test fails.

I think the best thing is to pull this first setting of charset=binary up into the parser level, and set it in a place common to all instances of encountering a field_spec "columnname TIMESTAMP" anywhere.  Not totally certain about this approach yet.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77016

2973 Tatiana A. Nurnberg	2009-06-24
      Bug#43508: Renaming timestamp or date column triggers table copy
      
      Altering a table to update a column with types DATE or TIMESTAMP
      would incorrectly be seen as a significant change that necessitates
      a slow copy+rename operation instead of a fast update.
      
      There were two problems:
      
      The character set is magically set for TIMESTAMP to be "binary",
      but that was done too deep in field use code for ALTER TABLE to
      know of it.  Now, put that in the constructor for Field_timestamp.
      
      Also, when we set the character set for the new replacement/
      comparison field, also raise the "binary" field flag that tells us
      we should compare it exactly.  That is necessary to match the old
      stored definition.
      
      Next is the problem that the default length for TIMESTAMP and DATE
      fields is different than the length read from the .frm .  The
      compressed size is written to the file, but the human-readable,
      part-delimited length is used as default length.  IIRC, for
      timestamp it was 19!=14, and for date it was 8!=10.  Length
      mismatch causes a table copy.
      
      Also, clean up a place where a comparison function alters one of its
      parameters and replace it with an assertion of the condition it
      mutates.

Actually, we can test for this really easily without a debugger:
----8<----

43508.test:

#
# Bug#43508: Renaming timestamp or date column triggers table copy
#

CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL,
                 f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2);

--echo this should affect no rows as there is no real change
--enable_info
ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL;
--disable_info
DROP TABLE t1;

----8<----

43508.result:

CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL,
f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2);
this should affect no rows as there is no real change
ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL;
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 0
DROP TABLE t1;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86348

3068 Tatiana A. Nurnberg	2009-10-09 [merge]
      Bug#43508: Renaming timestamp or date column triggers table copy
      
      We set up DATE and TIMESTAMP differently in field-creation than we
      did in field-MD creation (for CREATE). Admirably, ALTER TABLE
      detected this and didn't damage any data, but it did initiate a
      full copy/conversion, which we don't really need to do.
      
      Now we describe Field and Create_field the same for those types.
      As a result, ALTER TABLE that only changes meta-data (like a
      field's name) no longer forces a data-copy when there needn't
      be one.
     @ mysql-test/r/alter_table.result
        0 rows should be affected when a meta-data change is enough ALTER TABLE.
     @ mysql-test/t/alter_table.test
        add test-case: show that we don't do a full data-copy on ALTER TABLE
        when we don't need to.
     @ sql/field.cc
        Remove Field_str::compare_str_field_flags() (now in Field/Create_field as
        field_flags_are_binary().
        
        Correct some field-lengths!
     @ sql/field.h
        Clean-up: use defined constants rather than numeric literals for certain
        field-lengths.
        
        Add enquiry-functions binaryp() to classes Field and Create_field.
        This replaces field.cc's Field_str::compare_str_field_flags().

queued for 5.1.41, 6.0.14 in -bugteam

Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092041-sp6eyod7sdlfuj3b) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.1.41, 5.5.0, 6.0.14 changelogs.

For ALTER TABLE, renaming a DATETIME or TIMESTAMP column
unnecessarily caused a table copy operation.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)