0

Using phpMyAdmin, I tried to rename my 6GB database, only to discover that the operation takes a couple of hours. According to this SO question, it sounds like renaming a database requires creating a new database and copying the data over. Why isn't the name just a pointer to some text that can be changed at will?

Edit on 14 March 2016

I originally accepted Gandalf's answer because I could not replicate the DB renaming taking so long. But this long renaming process is happening again. Here is a screenshot of phpMyAdmin hanging:

enter image description here

And here is a screenshot of the results from SHOW PROCESSLIST when connecting directly to the MySQL server:

enter image description here

It looks like the query is spinning on adding a constraint. I'm pretty sure the command that phpMyAdmin ran was:

RENAME TABLE harmonizome_temp TO harmonizome;
Community
  • 1
  • 1
jds
  • 7,910
  • 11
  • 63
  • 101
  • What command are you using for renaming the database? – Gordon Linoff Dec 21 '15 at 21:58
  • @GordonLinoff, I just use phpMyAdmin's renaming functionality, which issues this query `CREATE DATABASE new_db / DROP DATABASE old_db;`. I reran the command today--I had tried this a few days ago--and it was instantaneous, so I'm not sure what happened the last time. – jds Dec 21 '15 at 22:19
  • 1
    I'm voting to close this question as off-topic because it is wrong. I asked it, and I can't replicate the error. – jds Dec 21 '15 at 22:21
  • . . As the author, you can just delete the question. You don't have to vote to close your own question. – Gordon Linoff Dec 22 '15 at 01:52
  • It's been answered, so I cannot. – jds Dec 22 '15 at 02:02

1 Answers1

0

This is probably necessary to ensure database integrity if there's an error during the process.

Information about the database is stored in both the filesystem (each database is a directory) and also in the INFORMATION_SCHEMA database. When you rename a database, both have to be updated. If it simply renamed the directory and updated INFORMATION_SCHEMA, there would be a period of time when these are out of sync. And if the process is aborted at this stage, the database will be corrupted.

By copying the data, it can ensure that they're never out of sync. The old directory is not deleted until after everything INFORMATION_SCHEMA is updated to refer to the new database name. If the process is aborted in the middle, you'll have duplicate data in the filesystem, but INFORMATION_SCHEMA will point to one or the other (a transaction can ensure that everything in INFORMATION_SCHEMA is updated atomically).

Barmar
  • 741,623
  • 53
  • 500
  • 612