Error moving database between users

Richard G

Richard G

Verified User
Joined
Jul 6, 2008
Messages
13,530
Location
Maastricht
I tried to move several databases between users as described here:

http://help.directadmin.com/item.php?id=224

The user has put some xxx characters in his database name, and during the move I get an error notice of 1 of his databases:

PHP Warning: rename(/var/lib/mysql/user_xxx-nl,/var/lib/mysql/admin_xxx-nl): No such file or directory in /usr/local/directadmin/scripts/change_database_username.php on line 117
Error renaming /var/lib/mysql/user_xxx-nl to /var/lib/mysql/admin_xxx-nl
Click to expand...

This should have to do with the "-" bug I described before.
Because in /var/lib/mysql there is a database called user_xxx@002dnl present

How can I best move this last database to my admin user?

And at the same time I would like to suggest to change or adjust the script so this problem can not be an item anymore.
As it has to do with this:
http://www.directadmin.com/forum/showthread.php?t=43616
 
Additional info.

On the databases who were succeeded to be moved, the connection to the database goes wrong.
I changed in Settings.php (it's an SMF forum) the username to admin, but it keeps giving no connection to database until I restore the databases back to the old user.
Something is really going wrong with this move script on Mysql 5.1.
 
Have you tried renaming your databases manually?

Have you made sure your settings point to the right database name?

Jeff
 
No I did not try do rename them manually. Because the database which did not succeed to transfer was not very important.

However, with the working life databases, I changed the Settings.php of the SMF forum.
There we 2 I had to change, dbusername an dbname. But that did not work strangely enough.

I might try again tonight.
 
Make sure that with moving the database user who has permissions for DB gets also renamed. I mean check permissions on the moved database, you might want to use phpmyadmin to check it.
 
I mean check permissions on the moved database, you might want to use phpmyadmin to check it.
Click to expand...
I'm not sure what you mean by this. Don't know how to check those permissions with phpmyadmin.
 
Login as the root mysql user and go to Privileges and select the user and then you can look at database permissions.

Its under "Edit Privileges" in Privileges section
 
Last edited:
Hello,

Looks like a bug in change_database_username.php because of the naming scheme.
With the newer versions of mysql, the introduced the special characters in the database names, which didn't exist when the script was created.
Ultimately, DA shouldn't be touching the files in /var/lib/mysql at all.

The correct method would be to create a new database, and use the RENAME TABLE for each table in the db. (there is no RENAME DATABASE anymore).

I've added this to the versions system, but the new method to rename a database is more complicated than the simple folder rename.
http://www.directadmin.com/features.php?id=1343

John
 
Isn't it much easyer to backup the database and then restore it under the new user?
Like what you do if you move from one server to another as a customer?
 
That would work, yes, but it's much more resource intensive. Renaming a table is nearly instant.
No need for calls to mysqldump, and reloading with mysql, and no table charset issues in the raw sql reload, etc..

John
 
The correct method would be to create a new database, and use the RENAME TABLE for each table in the db.
Click to expand...
I presume you mean to create a new database under the new user.
You still have to import all the database tables in there, correct?
I don't quite see the difference. How do you easily import all tables into the new database then without importing like is done with a backup? You just copy all files in the /var/lib/mysql/user_dbname/ directory to the newly made /var/lib/mysql/otheruser_dbname/ and then change all databasetables by phpmyadmin? Or how is that done?
 
Hello,

Although it's not added to the change_username.sh yet, I've completed the php portion of the bug..

The script can be downloaded if you want to use it for manual moving of databases:
http://files1.directadmin.com/services/all/change_database_username.php

Sample usage in a change.sh:
Code: 
#!/bin/sh

MYSQL_USER=da_admin
MYSQL_PASS=yourdaadminpass


echo "Swapping $1 to $2";


VERBOSE=1 DBUSER="$MYSQL_USER" DBPASS="$MYSQL_PASS" USERNAME="$1" NEWUSERNAME="$2" /usr/local/bin/php ./change_database_username.php
and call
Code: 
./change.sh [B]olduser newuser[/B]
If anyone does end up trying it out, let us know how it goes. I've done some testing which seems to be working correctly. There was the though of "triggers" as the RENAME TABLE syntax mentions it will error if there are triggers.. but even if a table renaming fails, the old database will not be dropped. There must be 0 tables before the script will drop an old DB.

Ah yes, and I've converted it such that it's all mysqli compliant now.

John
 
It's almost 3 years further but nice that this part is fixed. :)
However... it would be nice if there would also be an option from the DA menu to add, remove of change a DB user (like Cpanel has).
 
You must log in or register to reply here.
Back
Top