Bug #53820 ALTER a MEDIUMINT column table causes full table copy

• Submitted: 19 May 2010 18:15
• Modified: 4 Aug 2010 22:47
• Reporter: Mattias Jonsson
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.5+
• OS: Any
• Assigned to: Dmitry Lenev
• CPU Architecture: Any

[19 May 2010 18:15] Mattias Jonsson

Description:
When doing ALTER TABLE on a table which have a MEDIUMINT column it will always do a full table copy, even if only renaming a column.

How to repeat:
in current mysql-trunk:
CREATE TABLE t (a INT, b MEDIUMINT);
ALTER TABLE t CHANGE a id INT;

by using gdb one can see that in Field_num::is_equal() the row:
(new_field->length <= max_display_length())
fails (i.e. 9 <= 8).

which causes compare_tables() to return that the tables have changed data, which will need a full table copy, instead of only changing the metadata.

This also means that it will be impossible to use wl#4445 EXCHANGE PARTITION on tables having MEDIUMINT columns since it relies on the compare_tables function.

Suggested fix:
Probably revert the change back to the 5.1 version.

in 5.1 the row was:
(new_field->pack_length == pack_length())

[26 May 2010 9:27] Sveta Smirnova

Thank you for the report.

Verified as described.

To repeat create test:

CREATE TABLE t (a INT, b MEDIUMINT);
ALTER TABLE t CHANGE a id INT;

Then run ./mtr --manual-gdb bug53820 and in another terminal:

(gdb)  b sql_table.cc:6930
Breakpoint 2 at 0x63b70f: file /users/ssmirnova/blade12/src/mysql-next-mr/sql/sql_table.cc, line 6930.
(gdb) c
Continuing.

Breakpoint 2, mysql_alter_table (thd=0x41f8df8, new_db=0x4287ed8 "test", new_name=0x4287998 "t", create_info=0x40a80470, table_list=0x42879d0, alter_info=0x40a80f40, order_num=0, order=0x0, ignore=false)
    at /users/ssmirnova/blade12/src/mysql-next-mr/sql/sql_table.cc:6930
6930        if (compare_tables(table, alter_info,
(gdb) n
6939        if (need_copy_table == ALTER_TABLE_METADATA_ONLY)
(gdb) p need_copy_table 
$1 = ALTER_TABLE_METADATA_ONLY
(gdb) n
6940          need_copy_table= need_copy_table_res;
(gdb) n
6948      if (need_copy_table == ALTER_TABLE_INDEX_CHANGED)
(gdb) p need_copy_table 
$2 = ALTER_TABLE_DATA_CHANGED

[24 Jun 2010 9:30] Konstantin Osipov

Please re-verify it, it should not longer be present.
If it's not repeatable, please assign back to us, we'll add a test case.

[5 Jul 2010 6:08] Sveta Smirnova

Not repeatable with current sources anymore.

[26 Jul 2010 9:23] Bugs System

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/114319

3085 Dmitry Lenev	2010-07-26
      Test for bug #53820 "ALTER a MEDIUMINT column table causes full 
      table copy".
      
      This patch only adds test case as the bug itself was addressed 
      by Ramil's fix for bug 50946 "fast index creation still seems
      to copy the table".

[26 Jul 2010 10:59] Dmitry Lenev

Pushed test case for this bug into
mysql-trunk-runtime tree.

[4 Aug 2010 7:53] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:04] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:20] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 9:01] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:[e-mail]-20100621094008-o9fa153s3f09merw) (pib:20)

[4 Aug 2010 22:47] Paul DuBois

Changes to test suite. No changelog entry needed.