Just upgraded MySQL to 5.1.23, ALTER database...

Finally upgraded to MySQL to 5.1.23,
ALTER database gives errors:
I get this error?

SHOW CREATE DATABASE `dbbkp`
ALTER DATABASE `dbbkp` CHARACTER SET latin1 COLLATE latin1_bin
RENAME DATABASE `dbbkp` TO `db1`
/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE `dbbkp` TO `db1`' at li */
SHOW FULL PROCESSLIST

Version x.y, Revision 1270

Cheers

Oh I just found this here:

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. ...

I'll have to change the SQL to this for 5.1.23:

ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME

Should not take too long.

Isn't it strange or murphy's law!
Once you get something working there is always something else to break the spirit. :?

Glad you found it
Will have to use it manually for now.

Cheers

How to get this right?

ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME

I can rename database in other apps with ease but don't get the code for doing it, have googled with no luck.

What is the syntax for me to drop in the query window of HeidiSQL?

I can rename database in other apps with ease but don't get the code for doing it, have googled with no luck.

I don't know how other apps do it, but after reading the manual I can't see any way of renaming a database in MySQL. The suggested syntax is meant for certain upgrade task.

In older versions, renaming the directory did the trick, but I'm not sure that's still a valid option. In any case, what I'd recommend you is:

Rename from FOO to BAR

[list=1:1e52db03d0]
[*:1e52db03d0]Create new a DB called BAR
[*:1e52db03d0]Copy everything from FOO to BAR
[*:1e52db03d0]Drop FOO[/list:o:1e52db03d0]

Sometimes, simple is better ;-)

HiediSQL only copies one table at a time.

phpMyadmin and Navicat SQL do it all, haven't bothered with other GUI tools.

I guess it's meant like this:

ALTER DATABASE old_name UPGRADE DATA DIRECTORY new_name

Simple isn't it?

I must be honest if it was that simple it would work.
All I get everytime I try:

"/* SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''db1' UPGRADE DATA DIRECTORY 'db'' at line 1 */"

ALTER DATABASE db1 UPGRADE DATA DIRECTORY db

I guess it's meant like this:

ALTER DATABASE old_name UPGRADE DATA DIRECTORY new_name

Simple isn't it?

I am currently downloading MySQL 5.1.7 to check out this ALTER table stuff with HeidiSQL

Works like a f'n DREAM [size=9:093c5554fc](well, like it should really)[/size:093c5554fc]
Get version 5.1.7 you wont turn back, for now that is.

I guess it's meant like this:

ALTER DATABASE old_name UPGRADE DATA DIRECTORY new_name

Simple isn't it?

Just upgraded to 1273.
F'n great!!!

Thank you!!!

Cheers

Ok, I obviously misunderstood that UPGRADE DATA DIRECTORY syntax - it can only fix the displayed name of a a database after upgrading from 5.0 to 5.1 . And MySQL is telling us we even shouldn't use the RENAME DATABASE syntax on servers which support it:

Do not use RENAME DATABASE in earlier versions in which it is present.

So I tend to remove this feature now. Opinions?

The only remaining strategy for renaming a database is:
1. copying its content to a new database
2. deleting the old one
Which can result in extremely poor performance on large databases. That's too bad.

Ok, I obviously misunderstood that UPGRADE DATA DIRECTORY syntax - it can only fix the displayed name of a a database after upgrading from 5.0 to 5.1 . And MySQL is telling us we even shouldn't use the RENAME DATABASE syntax on servers which support it:

Do not use RENAME DATABASE in earlier versions in which it is present.

So I tend to remove this feature now. Opinions?

Opinion:
As long as you have MySQL 5.1.7 and HeidiSQL then it works 100%
If you remove it from later versions then it wont really bother me too much.

Remember that most if not all hosts still use the older versions of MySQL and seem happy.

What about the danger to loose data using that feature? I'm not sure but I could imagine the database charset is somehow destroyed.

I know nothing about that MySQL stuff.
If it breaks then there is always phpmyadmin (if all else fails)?
Remember to stick to MyISAM, scrap InnoDB!