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

Just upgraded MySQL to 5.1.23, ALTER database...

User, date Message
Written by westmatrix
6 years ago
Category: General
82 posts since Wed, 11 Apr 07
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
Written by ansgar
6 years ago
4973 posts since Fri, 07 Apr 06
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.
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07
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
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07
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?
Written by kalvaro
6 years ago
592 posts since Thu, 29 Nov 07

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 ;-)
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07
HiediSQL only copies one table at a time.

phpMyadmin and Navicat SQL do it all, haven't bothered with other GUI tools.
Written by ansgar
6 years ago
4973 posts since Fri, 07 Apr 06
I guess it's meant like this:

ALTER DATABASE old_name UPGRADE DATA DIRECTORY new_name


Simple isn't it?
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07
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

Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07

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
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07
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.
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07

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
Written by ansgar
6 years ago
4973 posts since Fri, 07 Apr 06
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?
Written by ansgar
6 years ago
4973 posts since Fri, 07 Apr 06
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.
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07

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.

Written by ansgar
6 years ago
4973 posts since Fri, 07 Apr 06
What about the danger to loose data using that feature? I'm not sure but I could imagine the database charset is somehow destroyed.
Written by westmatrix
6 years ago
82 posts since Wed, 11 Apr 07
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.