Just upgraded MySQL to 5.1.23, ALTER database...
| User, date | Message |
|---|---|
|
Written by westmatrix
5 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` Version x.y, Revision 1270 Cheers |
|
Written by ansgar
5 years ago 3972 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
5 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  Will have to use it manually for now. Cheers |
|
Written by westmatrix
5 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
5 years ago 442 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
5 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
5 years ago 3972 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
5 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
5 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 I am currently downloading MySQL 5.1.7 to check out this ALTER table stuff with HeidiSQL |
|
Written by westmatrix
5 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
5 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 Just upgraded to 1273. F'n great!!! Thank you!!! Cheers |
|
Written by ansgar
5 years ago 3972 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
5 years ago 3972 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
5 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. 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
5 years ago 3972 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
5 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. |