distal-attribute
distal-attribute
distal-attribute
distal-attribute

Just upgraded MySQL to 5.1.23, ALTER database...

westmatrix posted 7 years ago in General
Finally upgraded to MySQL to 5.1.23,
ALTER database gives errors:
I get this error?

wrote: 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
ansgar posted 7 years ago
Oh I just found this here:

wrote: 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.
westmatrix posted 7 years ago
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 happy
Will have to use it manually for now.

Cheers
westmatrix posted 7 years ago
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?
kalvaro posted 7 years ago

westmatrix wrote: 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 ;-)
westmatrix posted 7 years ago
HiediSQL only copies one table at a time.

phpMyadmin and Navicat SQL do it all, haven't bothered with other GUI tools.
ansgar posted 7 years ago
I guess it's meant like this:

ALTER DATABASE old_name UPGRADE DATA DIRECTORY new_name


Simple isn't it?
westmatrix posted 7 years ago
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

westmatrix posted 7 years ago

anse wrote: 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
westmatrix posted 7 years ago
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.
westmatrix posted 7 years ago

anse wrote: 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
ansgar posted 7 years ago
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:

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



So I tend to remove this feature now. Opinions?
ansgar posted 7 years ago
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.
westmatrix posted 7 years ago

anse wrote: 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:

wrote: 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.

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

ansgar posted 7 years ago
What about the danger to loose data using that feature? I'm not sure but I could imagine the database charset is somehow destroyed.
westmatrix posted 7 years ago
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!

Please login to leave a reply, or register at first.