MySQL Bugs: #22240: Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected

Bug #22240	Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected
Submitted:	11 Sep 2006 15:39	Modified:	15 Sep 2007 12:11
Reporter:	Paul Maunders	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0,5.1	OS:	Any
Assigned to:	Guangbao Ni	CPU Architecture:	Any
Tags:	Backup, cluster, resize, server, upgrading, varchar

Description:
I have just upgraded an installation of MySQL cluster from 5.0 to 5.1 - I
did a backup, deleted 5.0, installed 5.1 and then ran ndb_restore to get
all the data back.

A lot of the space in the various tables was previously taken up by
VARCHARS, with an average row length of 800 bytes so you would expect the 5.1 version of the database to be significantly smaller, however after restoring the data, it seems that the overall database size and average row size remained the same.

I then dropped the database and built a seperate import script to insert each record one at a time from an existing data source. This time the row size was only 80 bytes, which is correct. 

So it appears there is a problem with the ndb_restore script when restoring a backup of a 5.0 database into a 5.1 database. 

How to repeat:
- Backup database from a 5.0 installation via ndb_mgm 'start backup'
- Remove 5.0 installation
- Install 5.1
- Restore backup via ndb_restore 

Suggested fix:
I haven't tried using a mysql_dump backup, but this may work as expected.

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

ChangeSet@1.2489, 2007-04-12 10:34:05+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected.

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

ChangeSet@1.2489, 2007-04-12 18:41:08+08:00, gni@dev3-221.dev.cn.tlan +4 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected. With --no-upgrade (-u) option, user can decide if ignore the upgrade, the default action is to upgrade them.

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

ChangeSet@1.2489, 2007-04-13 10:26:37+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected. With --no-upgrade (-u) option, user can decide if ignore the upgrade, the default action is to upgrade them.

1) When changing type, I think it would be nice with explicit check
  if version of file is < 5.1

Not strictly needed, but nice

2) The computation of real length (using initial bytes) is that needed?

/Jonas

Hi Jonas,
   1)yes, it needn't change type if the version >= 5.1. I will add  the judgement.
   2)It isn't necessary, but I think we should use the correct values to prevent new developer from misunderstanding, and i consult Tomas, he agrees with me also, do you agree on my opinion?

/Guangbao Ni

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

ChangeSet@1.2489, 2007-04-23 17:22:33+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected. With --no-upgrade (-u) option, user can decide if ignore the upgrade, the default action is to upgrade them.

i would have put if outside of loop,
but patch is ok...

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

ChangeSet@1.2489, 2007-04-24 09:45:15+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected. With --no-upgrade (-u) option, user can decide if ignore the upgrade, the default action is to upgrade them.

push into mysql-5.1-ndb-bj tree

merge into mysql-5.1-new-ndb tree

Pushed into 5.1.19-beta

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

ChangeSet@1.2556, 2007-06-27 13:25:58+08:00, gni@dev3-221.dev.cn.tlan +8 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected.
            add varchar restoring from 5.0 version backup into 5.1 with ndb_restore

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

ChangeSet@1.2521, 2007-06-27 13:57:43+08:00, gni@dev3-221.dev.cn.tlan +8 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected.
           add varchar type restoring test from 5.0 backup into 5.1

for test for bug 22240.... perhaps add an --exec ndb_desc on the table to check that the format has changed?
apart from that, looks okay.

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

ChangeSet@1.2521, 2007-07-20 19:43:56+08:00, gni@dev3-221.dev.cn.tlan +8 -0
  BUG#22240 Upgrading from cluster 5.0 to 5.1 does not resize VARCHARS as expected.
           add varchar type restoring test from 5.0 backup into 5.1

approved

push into mysql-5.1-new-ndb-bj

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

ChangeSet@1.2528, 2007-08-17 11:41:32+08:00, gni@dev3-221.dev.cn.tlan +8 -0
  BUG#22240 Add a test case into ndb_restore_compat

push into mysql-5.1-new-ndb-bj

Pushed into 5.1.23-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented behaviour change & new option in 5.1.23 changelog and description of ndb_restore in 5.1 Manual.

On a careful re-reading of this bug report, I see that the behaviour change and option were actually implemented in 5.1.19, and I've amended the recent changes/additions to the docs to reflect this state of affairs.