19

My Database name is SPM and I want to change it to spm (small letters).

I tried using 

RENAME DATABASE SPM TO spm;

, but I am getting the following error message:

ERROR 1064 (42000): 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 SPM to spm' at line 1

My server version: 5.0.45

Rory O'Kane
  • 29,210
  • 11
  • 96
  • 131
Rachel
  • 100,387
  • 116
  • 269
  • 365
  • ERROR 1064 (42000): 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 'SPM SPM to spm' at line 1 – Rachel Nov 10 '09 at 15:13
  • mysql> RENAME DATABASE SPM TO spm; ERROR 1064 (42000): 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 SPM TO spm' at line 1 – Rachel Nov 10 '09 at 15:16

6 Answers6

29

There is no database command to do it. You basically have to do it outside the database. Below are some references outlining possible solutions. It has been answered pretty good in this question

This is probably what it should look like in your case

mysqladmin create spm
mysqldump SPM | mysql spm

After you have verified that everything is in order you can drop the original database.

drop database SPM

References Rename database 1 / Rename database 2

[Note on "RENAME DATABASE" command: This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.]

Community
  • 1
  • 1
Peter Lindqvist
  • 10,122
  • 3
  • 41
  • 60
  • Just wanted to add, phpMyAdmin have a Rename feature. Just select the database and then click "Operations" and then Rename. It creates a database with the new name, copies the old one and then delete it. – Alex Angelico Feb 19 '14 at 15:13
3
RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names . However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

To perform the task of upgrading database names with the new encoding, use ALTER DATABASE db_name UPGRADE DATA DIRECTORY NAME instead.

krishna
  • 930
  • 1
  • 16
  • 35
1

Use mysql_dump to dump out the database contents of the old schema (it produces SQL output, and can include all the object CREATE statements), switch to the new schema, and execute that script mysql> . dump.sql

If it's a large database, this may take a while, but it's the safest way to do it (make sure you suspend any applications using the database while the conversion process is going on).

Delete the old schema when you're satisfied that everything worked.

MightyE
  • 2,679
  • 18
  • 18
0

Use rename database command.

You can also try to stop your mysql server and rename a folder that contains your db data to the name you prefer. Then start your server and check the grants - they might still contain references to your old database name.

silent
  • 3,843
  • 23
  • 29
  • ERROR 1064 (42000): 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 'SPM SPM to spm' at line 1 – Rachel Nov 10 '09 at 15:11
  • mysql> RENAME DATABASE SPM TO spm; ERROR 1064 (42000): 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 SPM TO spm' at line 1 – Rachel Nov 10 '09 at 15:16
  • You should upgrade your server to 5.1.7 – silent Nov 10 '09 at 15:21
  • I cannot upgrate it to 5.1.7, is there any way to work around it with 5.0 VERSION – Rachel Nov 10 '09 at 15:24
  • 1
    The rename database does not exist, and if it existed should not be used. `However, use of this statement could result in loss of database contents, which is why it was removed.` – Peter Lindqvist Nov 10 '09 at 15:27
  • You can also try to stop your mysql server and rename a folder that contains your db data to the name you prefer. Then start your server and check the grants - they might still contain references to your old database name. – silent Nov 10 '09 at 15:30
  • I cannot stop the server as it is centralized server. I agree with Peter's answer but do I need to have DBA permission for this purpose. – Rachel Nov 10 '09 at 16:00
  • You can try. But test it before dropping old database. – silent Nov 10 '09 at 16:02
  • You do not need to stop the server if you use the method i describe with mysqladmin/mysqldump which is actually answered on another question . – Peter Lindqvist Nov 10 '09 at 16:08
  • I will try your approach Peter and see if it works out. Actually currently our server is down for upgrade and will check as soon as server is up and running...it should not take more than 20 mins...I will update soon with the output. – Rachel Nov 10 '09 at 16:13
  • Peter: Do I have use this command at mysql prompt or $ prompt ? – Rachel Nov 10 '09 at 17:24
0

You can change your database name using Mysql User interface

Step1: First of all Go to localhost/phpmyadmin/ and click on your database

Step2: Click on Opertaion tab

Step3: Enter new database name into (Rename database to) textfield

Step4: Click on Go Buttton

Pritam Chaudhari
  • 789
  • 10
  • 10
-1

This is done with a RENAME DATABASE statement:

RENAME DATABASE old_db_name TO new_db_name;

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.

dstibbe
  • 1,589
  • 18
  • 33