Bug #39372 "Smart" ALTER TABLE not so smart after all.
Submitted: 10 Sep 2008 16:25 Modified: 13 Nov 2008 4:39
Reporter: Tobias Asplund
Status: Closed
Category:Server: DDL Severity:S3 (Non-critical)
Version:5.1.26 OS:Any
Assigned to: Sergey Gluhov Target Version:5.1+
Triage: Triaged: D2 (Serious)

[10 Sep 2008 16:25] Tobias Asplund 
Description:
ALTER TABLE in 5.1 has a regression compared to 5.0 in the cases when you try to use the
flags MAX_ROWS or PACK_KEYS together with MyISAM tables.

Example below only shows PACK_KEYS, but similar error occurs with MAX_ROWS and
MAX_DATA_LENGTH.

How to repeat:
# Clean Copy of the world database from the docs pages

5.0> ALTER TABLE Country ADD INDEX (Name), ADD INDEX (Population), ADD INDEX (IndepYear),
ADD INDEX (GNP);

5.0> pager grep Index_le
PAGER set to 'grep Index_le'

5.0> SHOW TABLE STATUS LIKE 'Country'\G
   Index_length: 22528

5.0> ALTER TABLE Country PACK_KEYS=1;

5.0> SHOW TABLE STATUS LIKE 'Country'\G
   Index_length: 19456

But, look now at 5.1, it seems to not rebuild the table...

5.1> ALTER TABLE Country ADD INDEX (Name), ADD INDEX (Population), ADD INDEX (IndepYear),
ADD INDEX (GNP);

5.1> pager grep Index_le
PAGER set to 'grep Index_le'

5.1> SHOW TABLE STATUS LIKE 'Country'\G
   Index_length: 22528

5.1> ALTER TABLE Country PACK_KEYS=1;

5.1> SHOW TABLE STATUS LIKE 'Country'\G
   Index_length: 22528

Suggested fix:
Have MAX_ROWS and PACK_KEYS rebuild the table once changed.

There is a workaround by adding/dropping a new column, but that's very inefficient since
it rebuilds the table twice (Could possibly be a more efficient workaround by doing
something else in ALTER TABLE, but......)
[10 Sep 2008 19:18] Miguel Solorzano 
Thank you for the bug report. 6.0 bzr not affected.
[2 Oct 2008 9:36] 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/55000

2754 Sergey Glukhov	2008-10-02
      Bug#39372 "Smart" ALTER TABLE not so smart after all.
      The problem was that PACK_KEYS clause in ALTER TABLE did not trigger
      table reconstruction.
      The fix is to rebuild a table if PACK_KEYS is specified.
[2 Oct 2008 10:48] 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/55017

2754 Sergey Glukhov	2008-10-02
      Bug#39372 "Smart" ALTER TABLE not so smart after all.
      The problem was that PACK_KEYS and MAX_ROWS clause in ALTER TABLE did not trigger
      table reconstruction.
      The fix is to rebuild a table if PACK_KEYS or MAX_ROWS are specified.
[9 Oct 2008 12:50] 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/55906

2772 Sergey Glukhov	2008-10-09
      Bug#39372 "Smart" ALTER TABLE not so smart after all.
      The problem was that PACK_KEYS and MAX_ROWS clause in ALTER TABLE did not trigger
      table reconstruction.
      The fix is to rebuild a table if PACK_KEYS or MAX_ROWS are specified.
[10 Nov 2008 11:53] Bugs System 
Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081009104913-91h0wlocv02g1p88)
(version source revid:sergey.glukhov@sun.com-20081009105309-cs05hufzyohagnuh) (pib:5)
[10 Nov 2008 12:37] Bugs System 
Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081009104913-91h0wlocv02g1p88)
(version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)
[11 Nov 2008 17:08] Paul DuBois 
The versions are actually 5.1.31, 6.0.9.
[13 Nov 2008 4:37] Paul DuBois 
Noted in 5.1.31, 6.0.9 changelogs.

Use of the PACK_KEYS or MAX_ROWS table option in ALTER TABLE should
have triggered table reconstruction but did not.
[19 Jan 12:23] Bugs System 
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii)
(version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers:
5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 14:01] Bugs System 
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl)
(version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers:
5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 17:07] Bugs System 
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5)
(version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers:
5.1.31-ndb-6.4.1) (pib:6)