MySQL - Renaming database

Question

Using PHPMyAdmin version 4.5.2 and SQL 5.7.9. I have a database called 'name' with 3 tables 'one' 'three' 'two' in that specific order, ascending. Now when I use the Operations>Rename Database feature in PHPMyAdmin and set the new name to 'newname' it renames just fine. I look into the console to see what command it used to do that:

 RENAME TABLE `name`.`two` TO `newname`.`two`; DROP DATABASE `name`;

What I need is someone to explain these commands, how did it do that? I only see it mentioning the table 'two' and yet it properly manages to copy all of them (one, three, two) I'd like to know because this being just a simple example, I will need to use these commands (Without using operations>rename database) in a more complex database filled will different all kinds of tables.

EDIT:

 RENAME TABLE `newname`.`two` TO `name`.`two`; RENAME TABLE `newname`.`one` TO `name`.`one`; RENAME TABLE `newname`.`three` TO `name`.`three`; DROP DATABASE `newname`;

Produces error:

#1025 - Error on rename of '.\newname\two' to '.\name\two' (errno: 168 - Unknown (generic) error from engine)

Shadow · Accepted Answer · 2016-03-20 02:07:52Z

3

Probably the console did not log all commands because rename table command in the form you specified moves only one table from the source db to the target. Try issuing the 2 commands manually yourself and you will see what I mean.

You can use rename table to rename multiple tables and move them to a different db by providing a list of tables to rename:

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

answered Mar 20, 2016 at 2:07

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

xmaxert Over a year ago

I seem to be misunderstanding this completely. So what am I supposed to write, overall. To achieve the stated task? I tried something like " RENAME TABLE newname.two TO name.two; RENAME TABLE newname.one TO name.one; RENAME TABLE newname.three TO name.three; DROP DATABASE newname;" I simply receive errors. I will paste the above as a code snippet to be more readable in my initial post.

Shadow Over a year ago

I know that it is my fault, but I'm not a mind reader! What errors do you get?

xmaxert Over a year ago

"#1025 - Error on rename of '.\newname\two' to '.\name\two' (errno: 168 - Unknown (generic) error from engine)"

xmaxert Over a year ago

I'd also like to add that this was me trying to convert the name from 'newname' to 'name' this time. Not the other way around like I mentioned I did initially.

Michael - sqlbot Over a year ago

You have to create the new database, before everything. You can't rename a table to be in a database that doesn't yet exist.

|

Collectives™ on Stack Overflow

MySQL - Renaming database

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related