#3338 Rename Database fails with views

[Anonymous]

Originally created by: SBosanq...@gmail.com

What exact steps will reproduce the problem?
1.Create a database with some tables and views
2.Right click on the database and select edit
3.Change the name

What was the expected output?
The database name would change

What happened instead?
An error appears "Altering database "db_name" failed.  SQL Error (1450): Changing schema from 'old_db_name' to 'new_db_name' is not allowed".

Suggested fix (optional)?
Not sure but the problem is because you are trying to rename the views using the RENAME TABLE command.

Version used?
HeidiSQL revision: 8.0.0.4525
MySQL Server version: MariaDB 5.5.33a
Operating system: Windows 7 x64

[Anonymous]

Originally posted by: a...@anse.de

Please post the previous 10 lines from your SQL log panel here, from before that error occured.

Status: NeedInfo

[Anonymous]

Originally posted by: SBosanq...@gmail.com

here is the steps from scratch:

I have imported a new database from an sql dump.  I then try to rename it.  Then the error appears.

[Anonymous]

Originally posted by: a...@anse.de

So, this is the relevant portion here:

USE `logdata`;
SHOW COLLATION;
SHOW VARIABLES LIKE 'collation_server';
SHOW CREATE DATABASE `logdata`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='logdata';
SHOW TABLE STATUS FROM `logdata`;
SHOW FUNCTION STATUS WHERE `Db`='logdata';
SHOW PROCEDURE STATUS WHERE `Db`='logdata';
SHOW TRIGGERS FROM `logdata`;
SHOW EVENTS FROM `logdata`;
SHOW DATABASES;
CREATE DATABASE `logdata-c003-heze` /*!40100 COLLATE 'latin1_swedish_ci' */;
RENAME TABLE `logdata`.`boc` TO `logdata-c003-heze`.`boc`, `logdata`.`bocdata` TO `logdata-c003-heze`.`bocdata`, `logdata`.`eoc` TO `logdata-c003-heze`.`eoc`, `logdata`.`eocdata` TO `logdata-c003-heze`.`eocdata`, `logdata`.`eop` TO `logdata-c003-heze`.`eop`, `logdata`.`eopdata` TO `logdata-c003-heze`.`eopdata`, `logdata`.`general` TO `logdata-c003-heze`.`general`, `logdata`.`soc` TO `logdata-c003-heze`.`soc`, `logdata`.`socdata` TO `logdata-c003-heze`.`socdata`, `logdata`.`sop` TO `logdata-c003-heze`.`sop`, `logdata`.`sopdata` TO `logdata-c003-heze`.`sopdata`, `logdata`.`v_schedule` TO `logdata-c003-heze`.`v_schedule`;
/* SQL Error (1450): Changing schema from 'logdata' to 'logdata-c003-heze' is not allowed. */

Status: Accepted

[Anonymous]

Originally posted by: a...@anse.de

What makes you think views are the problem here?

Labels: Severity-Broken
Status: NeedInfo

[Anonymous]

Originally posted by: SBosanq...@gmail.com

if you run the rename table command without the view portion then you don't get the schema error and the database will change name correctly.  If you to run the query with the views included you will get the sql error and the database will not change name.

For example, if you run this then it will work ok:

CREATE DATABASE `logdata-c003-heze` /*!40100 COLLATE 'latin1_swedish_ci' */;
RENAME TABLE `logdata`.`boc` TO `logdata-c003-heze`.`boc`, `logdata`.`bocdata` TO `logdata-c003-heze`.`bocdata`, `logdata`.`eoc` TO `logdata-c003-heze`.`eoc`, `logdata`.`eocdata` TO `logdata-c003-heze`.`eocdata`, `logdata`.`eop` TO `logdata-c003-heze`.`eop`, `logdata`.`eopdata` TO `logdata-c003-heze`.`eopdata`, `logdata`.`general` TO `logdata-c003-heze`.`general`, `logdata`.`soc` TO `logdata-c003-heze`.`soc`, `logdata`.`socdata` TO `logdata-c003-heze`.`socdata`, `logdata`.`sop` TO `logdata-c003-heze`.`sop`, `logdata`.`sopdata` TO `logdata-c003-heze`.`sopdata`;

if you try:
RENAME TABLE `logdata`.`v_schedule` TO `logdata-c003-heze`.`v_schedule`;

then you get the SQL error as this is a view.

The manual says: "As of MySQL 5.0.14, RENAME TABLE also works for views, as long as you do not try to rename a view into a different database."

If this is the case then there should be a warning to indicate that this operation cannot be done rather than a generic sql error

[Anonymous]

Originally posted by: SBosanq...@gmail.com

for me it looks like you will have to create and drop the views in the now/old database respectively.

[Anonymous]

Originally posted by: a...@anse.de

Sounds like a good idea.

Labels: -Type-Defect -Severity-Broken Type-Enhancement Severity-Default
Status: Accepted

[Anonymous]

Originally posted by: ricardo....@blogfarm.com.br

A workaround:

You can use the maintenance tool to archieve something similar: instead of renaming the database, create the target database. After you've done that, click (right button) on your original database and choose "Maintenance". Then, "Mass table edit" (not sure if this is the name in English, but it is the last tab).

Click "Move to database" and chose the target database. Everything will be moved, expect the views. After it is done, at least here, the tool hung for some minutes giving the impression the program has freezes. But just wait for awhile.

Everything went fine here.

Take care all.