List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:December 11 2009 12:38pm
Subject:Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
View as plain text  
This only works for MyISAM :-)

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;

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.


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_viabilit
> > 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
>
>

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