MySQL Bugs: #43385: Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist

Bug #43385	Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist
Submitted:	4 Mar 2009 17:21	Modified:	15 May 2009 1:41
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.32	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	#mysql50#it-services, alter database, changing schema, ERROR 1450, UPGRADE DATA DIRECTORY NAME, VIEW

Description:
When upgrading to 5.1 from an earlier version (say 5.0) and you have a "-" (hyphen) in the database name, such as a-b-c, the database name will become #mysql50#a-b-c. To fix this, the manual simply says to "use UPGRADE DATA DIRECTORY NAME in this case to explicitly tell the server to re-encode the database directory name to the current encoding format:

ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;

After executing this statement, you can refer to the database as a-b-c without the special #mysql50# prefix."

http://dev.mysql.com/doc/refman/5.1/en/alter-database.html

However, this fails with the following error if a view is included in the database:

mysql> ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
ERROR 1450 (HY000): Changing schema from '#mysql50#a-b-c' to 'a-b-c' is not allowed.

How to repeat:
Upgrade a 5.0 database with a view to 5.1 (in place). Then issue this command:

ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;

However, it is also possible to simply accomplish this with 5.1.32.

CREATE DATABASE `a-b-c`;
USE a-b-c;
CREATE VIEW v AS SELECT user, host FROM mysql.user;
-- stop mysqld
-- manually rename file folder "a@002db@002dc" to "a-b-c"
-- restart mysqld
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;

You'll then receive the following error:

ERROR 1450 (HY000): Changing schema from '#mysql50#a-b-c' to 'a-b-c' is not allowed.

Suggested fix:
Allow ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME to work with databases that contain views.

At a minimum, this should be documented.

I also suspect there may be problems with stored procs, routines, and/or events. If so, this should also be fixed and/or documented.

Work-around(s):

You'll have to dump the database using mysqldump. I'd recommend using the --databases format, even though only dumping a single database, as this will then include the CREATE DATABASE statement. You'll then need to manually edit the database name in the dump file in the CREATE DATABASE command and the USE command directly after it (so 2 edits in total). Then you can re-import this.

This will now create the new database "a-b-c". Note the old database (#mysql50#a-b-c) will also remain. Once you verify everything looks okay in the new a-b-c database, then feel free to drop the old #mysql50#a-b-c database.

Caution: Also be sure to include the --routines (-R) option should you have routines in your database that you do not want to lose. -E (or --events) should not be necessary as you would not have them in a 5.0 database, but noting it here just for reference.

You *might* simply be able to rename this file folder from the OS command line (stopping mysqld first, of course), but it may be possible to lose information (which was the reason RENAME DATABASE was removed). I would not try this method if you have any routines defined in your database. See the following page and bug for more historical details:

http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
http://bugs.mysql.com/bug.php?id=21741

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

2858 Sergey Glukhov	2009-04-06
      Bug#43385 Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist
      allow 'rename view' for ALTER ...UPGRADE DATA DIRECTORY NAME command.
      it's safe because a view has valid internal db&table names in this case.
     @ mysql-test/r/update.result
        test result
     @ mysql-test/std_data/bug43385.frm
        test case
     @ mysql-test/t/update.test
        test case
     @ sql/sql_rename.cc
        allow 'rename view' for ALTER ...UPGRADE DATA DIRECTORY NAME command.
        it's safe because a view has valid internal db&table names in this case.

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

2868 Sergey Glukhov	2009-04-10
      Bug#43385 Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist
      allow 'rename view' for ALTER ...UPGRADE DATA DIRECTORY NAME command.
      it's safe because a view has valid internal db&table names in this case.
     @ mysql-test/r/upgrade.result
        test result
     @ mysql-test/t/upgrade.test
        test case
     @ sql/sql_rename.cc
        allow 'rename view' for ALTER ...UPGRADE DATA DIRECTORY NAME command.
        it's safe because a view has valid internal db&table names in this case.

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

2873 Sergey Glukhov 2009-04-13
Bug#43385 Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist(addon)
mysql_rename_view can not rename view if database is not the same.
The fix is to add new argument 'new_db' to mysql_rename_view() and
allow rename with different databases
(only for ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME).
@ mysql-test/t/upgrade.test
test fix
@ sql/parse_file.cc
mysql_rename_view can not rename view if database is not the same.
The fix is to add new argument 'new_db' to mysql_rename_view() and
allow rename with different databases
(only for ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME).
@ sql/parse_file.h
mysql_rename_view can not rename view if database is not the same.
The fix is to add new argument 'new_db' to mysql_rename_view() and
allow rename with different databases
(only for ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME).
@ sql/sql_rename.cc
mysql_rename_view can not rename view if database is not the same.
The fix is to add new argument 'new_db' to mysql_rename_view() and
allow rename with different databases
(only for ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME).
@ sql/sql_view.cc
mysql_rename_view can not rename view if database is not the same.
The fix is to add new argument 'new_db' to mysql_rename_view() and
allow rename with different databases
(only for ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME).
@ sql/sql_view.h
mysql_rename_view can not rename view if database is not the same.
The fix is to add new argument 'new_db' to mysql_rename_view() and
allow rename with different databases
(only for ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME).

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:chad@mysql.com-20090413143537-klahm5lsxjv9ymgh) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.1.35, 6.0.12 changelogs.

ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME failed when the
database contained views.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)