List:General Discussion« Previous MessageNext Message »
From:Ken D'Ambrosio Date:December 11 2009 12:56pm
Subject:Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
View as plain text  
On Fri, December 11, 2009 7:38 am, Johan De Meersman wrote:
> This only works for MyISAM :-)

Good to know -- thanks!

> However, there's another solution where you don't need to shut down, and
> that works for any engine afaik:
>
> rename table oldschema.table to newschema.table;

Just to be 100% clear -- I assume you have to first create the destination
database, and then do this for all the tables in the source database?

> I agree that it's a silly thing to not have, but I can't say that I've
> encountered a whole lot of instances where I needed it, either.

Agreed.

Thanks much!

-Ken


> On Fri, Dec 11, 2009 at 7:04 AM, Ken D'Ambrosio <ken@stripped> wrote:
>
>
>> Uhhh... wow.  Unless I'm very, very, very mistaken, I think you're
>> missing something pretty obvious: I believe you can simply a) shut down
>> the database b) mv the directory to a different directory name.
>>
>> *DONE*  Your database now has a different name.  Boy, that 30 seconds
>> of hard labor was sure faster than waiting a week for SQL dumps.
>> Granted, I
>> can't swear that this is Officially Sanctioned And Approved(tm), but
>> I've
>> done it many times, myself (and, indeed, just verified it under 5.1 to
>> be sure it still worked).
>>
>> Since you are talking such a significant volume of data, I would
>> suggest either testing, or hearing from someone more knowledgeable than
>> I, but I
>> think this problem is substantially smaller than you've let yourself
>> believe.
>>
>> -Ken
>>
>>
>>
>> On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote:
>>
>>> How can it possibly be that mySQL doesn't allow you to rename a
>>> database? I
>>> can't fathom how this can be a difficult task at all to do. Aren't
>>> mySQL databases stored in a directory of the DB name? And for INNODB,
>>> can't you just find the spot in the ibdata file and alter whatever
>>> needs to be changed? This is absolutely absurd. Not even 5.1 has this
>>> most basic of features.
>>>
>>> We have nearly a billion rows. Exporting to a .sql file and importing
>>>  again can take nearly a week to do (3 days each way and that doesn't
>>> even begin to touch on the fact the server would be down)! WTF!?
>>>
>>> We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for
>>> debian-linux-gnu (i486) using readline 5.2
>>>
>>> Even the manual for 5.1 says this can lose data:
>>> http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
>>> "This statement was added in MySQL 5.1.7 but was found to be dangerous
>>>
>> and
>>> was removed in MySQL 5.1.23...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."
>>>
>>>
>>> Seriously? Please explain why a simple rename of a database is such a
>>>  daunting task to mySQL/Sun that all their brilliant minds can't
>>> figure this one out?
>>>
>>> Why isn't there even a bug report for this?
>>>
>>>
>>>
>>>
>> http://bugs.mysql.com/search.php?search_for=rename+database&boolean=on&
>> st
>>> at
>>>
> us[]=Active&severity=&limit=All&order_by=&cmd=display&phpver=&os=0&os
>>> _det
>>> ai
>>>
> ls=&bug_age=0&tags=&similar=&target=&defect_class=all&workaround_viab
>>> ilit y=
> all&impact=all&fix_risk=all&fix_effort=all&triageneeded=
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>>
>>>
>>> --
>>> This message has been scanned for viruses and
>>> dangerous content by MailScanner, and is believed to be clean.
>>>
>>>
>>
>>
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is believed to be clean.
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is believed to be clean.
>
>



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Thread
Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Daevid Vincent11 Dec
  • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Ken D'Ambrosio11 Dec
    • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Johan De Meersman11 Dec
      • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Ken D'Ambrosio11 Dec
        • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Johan De Meersman11 Dec
          • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Michael Dykman11 Dec
            • RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Daevid Vincent11 Dec
              • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Michael Dykman11 Dec
                • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Saravanan11 Dec
                  • RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Gavin Towey11 Dec
                    • RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Daevid Vincent11 Dec
              • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Johan De Meersman11 Dec
          • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Jim Lyons11 Dec
            • Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?Michael Dykman11 Dec