Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

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&stat
us[]=Active&severity=&limit=All&order_by=&cmd=display&phpver =&os=0&os_detai
ls=&bug_age=0&tags=&similar=&target=&defect_class=all&workar ound_viability=
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=gcdmg-mysql-2@m.gmane.org

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

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&workar ound_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=ken@jots.org
>
>
>
> --
> 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=gcdmg-mysql-2@m.gmane.org

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

--0003255735d2a00cd4047a7334d3
Content-Type: text/plain; charset=ISO-8859-1

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 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&workar ound_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=ken@jots.org
> >
> >
> >
> > --
> > 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=vegivamp@tuxera.be
>
>

--0003255735d2a00cd4047a7334d3--

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

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 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&workar ound_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=ken@jots.org
>>>
>>>
>>>
>>>
>>> --
>>> 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=vegivamp@tuxera.be
>>
>>
>>
>
> --
> 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=gcdmg-mysql-2@m.gmane.org

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

--0015176f13b62ee234047a74513d
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio wrote:

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

Yep. Easily scriptable, though :-)

--0015176f13b62ee234047a74513d--

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

If you want to move the database atomically, a RENAME TABLE statement
may have multiple clauses.

RENAME TABLE
olddb.foo to newdb.foo,
olddb.bar to newdb.bar;

Here, I hot-swap a new lookup table 'active.geo' into a live system
confident that, at any given point, some version of this table always
exists:

RENAME TABLE
active.geo to archive.geo,
standby.geo to active geo;

- michael dykman


On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman wrote:
> On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio wrote:
>
>> > 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?
>>
>
> Yep. Easily scriptable, though :-)
>



--
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

--0016e6d99ba75664cf047a76833e
Content-Type: text/plain; charset=ISO-8859-1

Can you use that syntax if the databases are on different file systems? If
you can, and the original table is big, the command would take a while as it
moved data from one file system to another.

On Fri, Dec 11, 2009 at 7:58 AM, Johan De Meersman wrote:

> On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio wrote:
>
> > > 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?
> >
>
> Yep. Easily scriptable, though :-)
>



--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6d99ba75664cf047a76833e--

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

According to MySQL docs, it should still work atomically. Granted, I
have only used this particular trick when they are on the same
filesystem. Copying across filesystems, I imagine it should still be
atomic, but your system may be locked for awhile.

Obviously, a dedicated RENAME DATABASE command would have the same limitati=
ons.

- michael dykman


On Fri, Dec 11, 2009 at 11:35 AM, Jim Lyons wrote:
> Can you use that syntax if the databases are on different file systems? =
=A0If
> you can, and the original table is big, the command would take a while as=
it
> moved data from one file system to another.
>
> On Fri, Dec 11, 2009 at 7:58 AM, Johan De Meersman wr=
ote:
>
>> On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio wrote:
>>
>> > > 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?
>> >
>>
>> Yep. Easily scriptable, though :-)
>>
>
>
>
> --
> Jim Lyons
> Web developer / Database administrator
> http://www.weblyons.com
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

Will this work in 5.0?

If I'm reading this right, it seems like this is some kind of trick or
loophole then right? If it works and solves my dilemna, I'm fine with that,
but I'm just curious.

How fast is this? I mean, if I have an 80GB database, is it like a real
unix 'mv' command where it simply changing pointers or is it a full on
copy/rm? (Assume same filesystem/directory)

> -----Original Message-----
> From: Michael Dykman [mailto:mdykman@gmail.com]
> Sent: Friday, December 11, 2009 6:08 AM
> To: MySql
> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
> RENAME DATABASE?
>
> If you want to move the database atomically, a RENAME TABLE statement
> may have multiple clauses.
>
> RENAME TABLE
> olddb.foo to newdb.foo,
> olddb.bar to newdb.bar;
>
> Here, I hot-swap a new lookup table 'active.geo' into a live system
> confident that, at any given point, some version of this table always
> exists:
>
> RENAME TABLE
> active.geo to archive.geo,
> standby.geo to active geo;
>
> - michael dykman
>
>
> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman
> wrote:
> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio
> wrote:
> >
> >> > 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?
> >>
> >
> > Yep. Easily scriptable, though :-)
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
> Larry's First Law of Language Redesign: Everyone wants the colon.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

No, not a loophole. Just a plain-old management feature.. there is
nothing particularly hacky about it.. this is not trying to leverage
undocumented features: this has been a published part of the API for
at least a couple of years.

On the same file system, yes it should be pretty damned fast.
Depending on how your data is stored, it might now be 'quite' as
simple as a unix 'mv' command.. if this is a production system, I
would recommend you do a dry run with a replicant/slave. No amount of
theorizing will tell as much as the experiment.

- michael dykman

On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent wrote:
> Will this work in 5.0?
>
> If I'm reading this right, it seems like this is some kind of trick or
> loophole then right? If it works and solves my dilemna, I'm fine with tha=
t,
> but I'm just curious.
>
> How fast is this? I mean, if I have an 80GB database, is it like a real
> unix 'mv' command where it simply changing pointers or is it a full on
> copy/rm? (Assume same filesystem/directory)
>
>> -----Original Message-----
>> From: Michael Dykman [mailto:mdykman@gmail.com]
>> Sent: Friday, December 11, 2009 6:08 AM
>> To: MySql
>> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
>> RENAME DATABASE?
>>
>> If you want to move the database atomically, =A0a RENAME TABLE statement
>> may have multiple clauses.
>>
>> RENAME TABLE
>> =A0 =A0 =A0olddb.foo to newdb.foo,
>> =A0 =A0 =A0olddb.bar to newdb.bar;
>>
>> Here, =A0I hot-swap a =A0new lookup table 'active.geo' into a live syste=
m
>> confident that, at any given point, some version of this table always
>> exists:
>>
>> RENAME TABLE
>> =A0 =A0 =A0active.geo to archive.geo,
>> =A0 =A0 =A0standby.geo to active geo;
>>
>> =A0- michael dykman
>>
>>
>> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman
>> wrote:
>> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio
>> wrote:
>> >
>> >> > 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?
>> >>
>> >
>> > Yep. Easily scriptable, though :-)
>> >
>>
>>
>>
>> --
>> =A0- michael dykman
>> =A0- mdykman@gmail.com
>>
>> "May you live every day of your life."
>> =A0 =A0 Jonathan Swift
>>
>> Larry's First Law of Language Redesign: Everyone wants the colon.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Ddaevid@daevid.com
>>
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

--00151774041c4e4a00047a7b0569
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Dec 11, 2009 at 10:40 PM, Daevid Vincent wrote:

> Will this work in 5.0?
>

Yes.


> If I'm reading this right, it seems like this is some kind of trick or
> loophole then right? If it works and solves my dilemna, I'm fine with that,
> but I'm just curious.
>

Not really, this is by design afaik.


> How fast is this? I mean, if I have an 80GB database, is it like a real
> unix 'mv' command where it simply changing pointers or is it a full on
> copy/rm? (Assume same filesystem/directory)
>

Don't know, but given that it works with InnoDB, you should be able to
easily test on a small dataset by monitoring the size of a 1M autoextend
tablespace :-)

--00151774041c4e4a00047a7b0569--

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

--0-1537076363-1260568934=:64843
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

if you have myisam alone tables you can rename the folder of the database. =
That can work like rename database. If you have innodb table you have to mo=
ve one by one table because details of those tables will be stored in innod=
b shared table space. Moving folder cannot work.

Thanks,
Saravanan

--- On Fri, 12/11/09, Michael Dykman wrote:

From: Michael Dykman
Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
To: "MySql"
Date: Friday, December 11, 2009, 10:54 PM

No, not a loophole.=A0 Just a plain-old management feature..=A0 there is
nothing particularly hacky about it.. this is not trying to leverage
undocumented features: this has been a published part of the API for
at least a couple of years.

On the same file system, yes it should be pretty damned fast.
Depending on how your data is stored, it might now be 'quite' as
simple as a unix 'mv' command..=A0 if this is a production system, I
would recommend you do a dry run with a replicant/slave. No amount of
theorizing will tell as much as the experiment.

- michael dykman

On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent wrote:
> Will this work in 5.0?
>
> If I'm reading this right, it seems like this is some kind of trick or
> loophole then right? If it works and solves my dilemna, I'm fine with tha=
t,
> but I'm just curious.
>
> How fast is this? I mean, if I have an 80GB database, is it like a real
> unix 'mv' command where it simply changing pointers or is it a full on
> copy/rm? (Assume same filesystem/directory)
>
>> -----Original Message-----
>> From: Michael Dykman [mailto:mdykman@gmail.com]
>> Sent: Friday, December 11, 2009 6:08 AM
>> To: MySql
>> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
>> RENAME DATABASE?
>>
>> If you want to move the database atomically, =A0a RENAME TABLE statement
>> may have multiple clauses.
>>
>> RENAME TABLE
>> =A0 =A0 =A0olddb.foo to newdb.foo,
>> =A0 =A0 =A0olddb.bar to newdb.bar;
>>
>> Here, =A0I hot-swap a =A0new lookup table 'active.geo' into a live syste=
m
>> confident that, at any given point, some version of this table always
>> exists:
>>
>> RENAME TABLE
>> =A0 =A0 =A0active.geo to archive.geo,
>> =A0 =A0 =A0standby.geo to active geo;
>>
>> =A0- michael dykman
>>
>>
>> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman
>> wrote:
>> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio
>> wrote:
>> >
>> >> > 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?
>> >>
>> >
>> > Yep. Easily scriptable, though :-)
>> >
>>
>>
>>
>> --
>> =A0- michael dykman
>> =A0- mdykman@gmail.com
>>
>> "May you live every day of your life."
>> =A0 =A0 Jonathan Swift
>>
>> Larry's First Law of Language Redesign: Everyone wants the colon.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Ddaevid@daevid.com
>>
>
>



--=20
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
=A0 =A0 Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:=A0 =A0 http://lists.mysql.com/mysql?unsub=3Dsuzuki_babu@yah=
oo.com


--0-1537076363-1260568934=:64843--

RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

Don't forget triggers, stored routines, views, database/table specific user=
permissions, and replication/binlog options!

Regards,
Gavin Towey

-----Original Message-----
From: Saravanan [mailto:suzuki_babu@yahoo.com]
Sent: Friday, December 11, 2009 2:02 PM
To: MySql; Michael Dykman
Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

if you have myisam alone tables you can rename the folder of the database. =
That can work like rename database. If you have innodb table you have to mo=
ve one by one table because details of those tables will be stored in innod=
b shared table space. Moving folder cannot work.

Thanks,
Saravanan

--- On Fri, 12/11/09, Michael Dykman wrote:

From: Michael Dykman
Subject: Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?
To: "MySql"
Date: Friday, December 11, 2009, 10:54 PM

No, not a loophole. Just a plain-old management feature.. there is
nothing particularly hacky about it.. this is not trying to leverage
undocumented features: this has been a published part of the API for
at least a couple of years.

On the same file system, yes it should be pretty damned fast.
Depending on how your data is stored, it might now be 'quite' as
simple as a unix 'mv' command.. if this is a production system, I
would recommend you do a dry run with a replicant/slave. No amount of
theorizing will tell as much as the experiment.

- michael dykman

On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent wrote:
> Will this work in 5.0?
>
> If I'm reading this right, it seems like this is some kind of trick or
> loophole then right? If it works and solves my dilemna, I'm fine with tha=
t,
> but I'm just curious.
>
> How fast is this? I mean, if I have an 80GB database, is it like a real
> unix 'mv' command where it simply changing pointers or is it a full on
> copy/rm? (Assume same filesystem/directory)
>
>> -----Original Message-----
>> From: Michael Dykman [mailto:mdykman@gmail.com]
>> Sent: Friday, December 11, 2009 6:08 AM
>> To: MySql
>> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
>> RENAME DATABASE?
>>
>> If you want to move the database atomically, a RENAME TABLE statement
>> may have multiple clauses.
>>
>> RENAME TABLE
>> olddb.foo to newdb.foo,
>> olddb.bar to newdb.bar;
>>
>> Here, I hot-swap a new lookup table 'active.geo' into a live system
>> confident that, at any given point, some version of this table always
>> exists:
>>
>> RENAME TABLE
>> active.geo to archive.geo,
>> standby.geo to active geo;
>>
>> - michael dykman
>>
>>
>> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman
>> wrote:
>> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio
>> wrote:
>> >
>> >> > 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?
>> >>
>> >
>> > Yep. Easily scriptable, though :-)
>> >
>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> "May you live every day of your life."
>> Jonathan Swift
>>
>> Larry's First Law of Language Redesign: Everyone wants the colon.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=3Ddaevid@daevid.com
>>
>
>



--
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dsuzuki_babu@yahoo.c=
om


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

In our case, we purposely avoid using any of those features. Just straight
up INNODB tables. Permissions would be an issue, but in my case, I have a
new dump of a database that I want to 'swap' with the existing one. A
simple rename old, rename new to old would have solved it. Hence this
thread. :) Therefore permissions should be fine as they go by DB name AFAIK
and not some "pointer".

> -----Original Message-----
> From: Gavin Towey [mailto:gtowey@ffn.com]
> Sent: Friday, December 11, 2009 2:18 PM
> To: Saravanan; MySql; Michael Dykman
> Subject: RE: Are you serious? mySQL 5.0 does NOT have a
> RENAME DATABASE?
>
> Don't forget triggers, stored routines, views, database/table
> specific user permissions, and replication/binlog options!
>
> Regards,
> Gavin Towey
>
> -----Original Message-----
> From: Saravanan [mailto:suzuki_babu@yahoo.com]
> Sent: Friday, December 11, 2009 2:02 PM
> To: MySql; Michael Dykman
> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
> RENAME DATABASE?
>
> if you have myisam alone tables you can rename the folder of
> the database. That can work like rename database. If you have
> innodb table you have to move one by one table because
> details of those tables will be stored in innodb shared table
> space. Moving folder cannot work.
>
> Thanks,
> Saravanan
>
> --- On Fri, 12/11/09, Michael Dykman wrote:
>
> From: Michael Dykman
> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
> RENAME DATABASE?
> To: "MySql"
> Date: Friday, December 11, 2009, 10:54 PM
>
> No, not a loophole. Just a plain-old management feature.. there is
> nothing particularly hacky about it.. this is not trying to leverage
> undocumented features: this has been a published part of the API for
> at least a couple of years.
>
> On the same file system, yes it should be pretty damned fast.
> Depending on how your data is stored, it might now be 'quite' as
> simple as a unix 'mv' command.. if this is a production system, I
> would recommend you do a dry run with a replicant/slave. No amount of
> theorizing will tell as much as the experiment.
>
> - michael dykman
>
> On Fri, Dec 11, 2009 at 4:40 PM, Daevid Vincent
> wrote:
> > Will this work in 5.0?
> >
> > If I'm reading this right, it seems like this is some kind
> of trick or
> > loophole then right? If it works and solves my dilemna, I'm
> fine with that,
> > but I'm just curious.
> >
> > How fast is this? I mean, if I have an 80GB database, is it
> like a real
> > unix 'mv' command where it simply changing pointers or is
> it a full on
> > copy/rm? (Assume same filesystem/directory)
> >
> >> -----Original Message-----
> >> From: Michael Dykman [mailto:mdykman@gmail.com]
> >> Sent: Friday, December 11, 2009 6:08 AM
> >> To: MySql
> >> Subject: Re: Are you serious? mySQL 5.0 does NOT have a
> >> RENAME DATABASE?
> >>
> >> If you want to move the database atomically, a RENAME
> TABLE statement
> >> may have multiple clauses.
> >>
> >> RENAME TABLE
> >> olddb.foo to newdb.foo,
> >> olddb.bar to newdb.bar;
> >>
> >> Here, I hot-swap a new lookup table 'active.geo' into a
> live system
> >> confident that, at any given point, some version of this
> table always
> >> exists:
> >>
> >> RENAME TABLE
> >> active.geo to archive.geo,
> >> standby.geo to active geo;
> >>
> >> - michael dykman
> >>
> >>
> >> On Fri, Dec 11, 2009 at 8:58 AM, Johan De Meersman
> >> wrote:
> >> > On Fri, Dec 11, 2009 at 1:56 PM, Ken D'Ambrosio
> >> wrote:
> >> >
> >> >> > 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?
> >> >>
> >> >
> >> > Yep. Easily scriptable, though :-)
> >> >
> >>
> >>
> >>
> >> --
> >> - michael dykman
> >> - mdykman@gmail.com
> >>
> >> "May you live every day of your life."
> >> Jonathan Swift
> >>
> >> Larry's First Law of Language Redesign: Everyone wants the colon.
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
> >>
> >
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
> Larry's First Law of Language Redesign: Everyone wants the colon.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=suzuki_babu@yahoo.com
>
>
> This message contains confidential information and is
> intended only for the individual named. If you are not the
> named addressee, you are notified that reviewing,
> disseminating, disclosing, copying or distributing this
> e-mail is strictly prohibited. Please notify the sender
> immediately by e-mail if you have received this e-mail by
> mistake and delete this e-mail from your system. E-mail
> transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The
> sender therefore does not accept liability for any loss or
> damage caused by viruses or errors or omissions in the
> contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt
> court, Sunnyvale, CA 94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org