replication of RENAME TABLE d1.t TO d2.t
i've a need to change the name of a database and haven't done this before in our live server. while the tables are myisam, i'm not inclined to rename the dirname of d1's datafiles because i'd rather not interrupt service for other databases and i'd prefer if the renaming would replicate. can i do it like this: in the application, kick users of d1 out create database d2 foreach t in d1: RENAME TABLE d1.t TO d2.t tell user to use d2 ? tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: replication of RENAME TABLE d1.t TO d2.t
Other way around. Assuming the aplcation is expecting your table to be named 'db.table1' and your replacement is newdb.table1 you can do the following. create a database for hold archives CREATE olddb; then RENAME TABLE db.table1 to olddb.table1, -- back up the current table newdb.table1 to db.table1 -- rename the new table as the old one You can have multiple oldtable TO newtable section, comma-separated. MySQL guarantees that this is an atomic action which is to say that, for all intents and purposed, all the table renames within a single statement occur simultaneously or not at all. I have swapped out quite larger database on the fly in production like this with no reported problems. - michael On Thu, Jun 3, 2010 at 5:01 PM, Tom Worster f...@thefsb.org wrote: i've a need to change the name of a database and haven't done this before in our live server. while the tables are myisam, i'm not inclined to rename the dirname of d1's datafiles because i'd rather not interrupt service for other databases and i'd prefer if the renaming would replicate. can i do it like this: in the application, kick users of d1 out create database d2 foreach t in d1: RENAME TABLE d1.t TO d2.t tell user to use d2 ? tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RENAME TABLE with CONCAT string fails
I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. mysql RENAME TABLE flows TO flows_tmp, flows_new TO flows, flows_tmp TO CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)); ERROR 1064: You have an error in your SQL syntax near 'CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day))' at line 1 If I run the CONCAT() command by itself, everything works as expected: ++ | CONCAT(flows_, DATE_SUB(CURDATE(), interval 1 day)) | ++ | flows_2005-05-27 | ++ 1 row in set (0.00 sec) Is there some magic pixie dust I need to throw around the CONCAT() to have it interpolate into the RENAME TABLE command correctly? This is on mysql-server 3.23.58 on a RHEL 3 clone. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Thanks, -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 11:00 -0400 5/28/05, Jason Dixon wrote: On May 28, 2005, at 8:51 AM, Paul DuBois wrote: At 8:12 -0400 5/28/05, Jason Dixon wrote: I'm trying to rename some tables for archival, but the table renaming is failing when I use CONCAT() to form the table string name: CONCAT() produces a string, not an identifier. Fine. Is there any way to do this in MySQL or do I need to fall back on my Perl? It's not a big deal, I'm just curious now. Perl. Construct the table identifier and place the result into your SQL statement, then execute the statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RENAME TABLE with CONCAT string fails
At 17:50 +0200 5/28/05, [EMAIL PROTECTED] wrote:
Hi,
As Paul said, since concat gives a string, you can use this fact in preparing
statement (v4.1). This works fine for me :
Ah, yes. This'll work. I forgot about prepared statements. :-)
But use replace to change '-' to '_' in the table_name.
set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
select @tt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_27|
+-+
1 row in set (0.00 sec)
mysql
mysql set @tt:=concat('rename table flows_2005_05_27 to ',CONCAT(flows_,
replace(DATE_SUB(CURDATE(), interval 2 day),'-','_')),';');
mysql prepare stmt from @tt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql execute stmt ;
mysql deallocate prepare stmt;
mysql show tables like 'flow%';
+-+
| Tables_in_world (flow%) |
+-+
| flows_2005_05_26|
+-+
1 row in set (0.00 sec)
Mathias
Selon Paul DuBois [EMAIL PROTECTED]:
At 11:00 -0400 5/28/05, Jason Dixon wrote:
On May 28, 2005, at 8:51 AM, Paul DuBois wrote:
At 8:12 -0400 5/28/05, Jason Dixon wrote:
I'm trying to rename some tables for archival, but the table
renaming is failing when I use CONCAT() to form the table string
name:
CONCAT() produces a string, not an identifier.
Fine. Is there any way to do this in MySQL or do I need to fall
back on my Perl? It's not a big deal, I'm just curious now.
Perl. Construct the table identifier and place the result into your
SQL statement, then execute the statement.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rename table?
Is it possible to rename a MySQL table? If so, how? If not, is there a simple query syntax to copy a table? I don't have access to the command-line version, just a web interface, and no, not anything nice like PhpMyAdmin! :-( -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rename table?
If you use PHP
program like below...
?
$conn = mysql_connect($host,$user,$pass)
if(!$conn) exit;
mysql_select_db($dbname);
$query = rename table old_table to new_table;
$res = mysql_query($query, $conn);
if(!$res) {
echo(Rename table failed);
exit;
}
echo(Rename table successfully !!!);
?
It's simple
OK ?
if you use other programming language, It's similar to above example.
Sorry, My english is so poor -_-;;
Is it possible to rename a MySQL table? If so, how?
If not, is there a simple query syntax to copy a table?
I don't have access to the command-line version,
just a web interface, and no, not anything nice like PhpMyAdmin! :-( --
Mark Wilson, Computer Programming Unlimited (cpuworks.com)
Web : http://cpuworks.com Tel: 410-549-6006
Email: [EMAIL PROTECTED] Fax: 410-549-4408
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is rename table to another database safe for INNODB tables
Jannie, - Original Message - From: Jannie Qu [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, January 21, 2003 1:30 AM Subject: Is rename table to another database safe for INNODB tables sql, query, Hi, all, As you may knew, mysql will implement rename database in version 4.1 Right now, I need to rename a database, what I plan to do is rename each table (INNODB type, version 3.23.53 on MAC OS) to another new database. RENAME TABLE current_db.tbl_name TO new_db.tbl_name; Do you think it's safe to (1) do the rename? or I'd better do a (2) import all to the new database? Which way is better? RENAME is safe. Thank you, Jannie Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Is rename table to another database safe for INNODB tables
sql, query, Hi, all, As you may knew, mysql will implement rename database in version 4.1 Right now, I need to rename a database, what I plan to do is rename each table (INNODB type, version 3.23.53 on MAC OS) to another new database. RENAME TABLE current_db.tbl_name TO new_db.tbl_name; Do you think it's safe to (1) do the rename? or I'd better do a (2) import all to the new database? Which way is better? Thank you, Jannie _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rename Table in Replication failed / command missing in slave log
Hi! The following might fix Pete's RENAME TABLE bug: ... August 12, 2002: If you ran in the AUTOCOMMIT mode, executed a SELECT, and immediately after that a RENAME TABLE, then RENAME would fail and MySQL would complain about error 1192 'Can't execute the given command because you have active locked tables or an active transaction'. Fixed in 3.23.52: RENAME now succeeds. ... But I have not reproduced Pete's problem, I do not know. Please test 3.23.52! I guess 3.23.52 will be released before Aug 16, 2002. Lutz' problem looks like somethig different. Do you use replication only to some databases? If yes, please check the manual about the exact behavior in such cases. Also check the changelogs after 3.23.49. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Copied message: .. If you're using InnoDB tables, replication stops the slaves from running. Heikki said he'd try to get this fixed for 3.23.52. --Pete On Mon, Aug 12, 2002 at 04:05:11PM +0200, Lutz Maibach wrote: Hi, today I noticed a strange behaviour in MySQL 3.23.49a-Replication I can't explain. A perl-script which controls whether master- and replication-tables are equal showed me, that a master-table was missing on both slaves (two backups are better than one :-) ) When I controlled the master update-log I saw that the master-table was renamed with the following command: ALTER TABLE ad_429_t RENAME adprj_7; This command was missing in the slave-updatelogs. The sql-statements before and right after the missing one were present and no error was written down in the mysql-error-log. Trying to find out whether the Rename-Statement works right or not I did some tests with renaming a table but all actions were performed on the slaves too. Has someone experienced the same? I'm now a bit unsure whether the replication is working right. Greetings from Germany Lutz Maibach - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rename Table in Replication failed / command missing in slave log
Lutz, - Original Message - From: Lutz Maibach [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Tuesday, August 13, 2002 5:09 PM Subject: Re: Rename Table in Replication failed / command missing in slave log Hi Heikki, Lutz' problem looks like somethig different. Do you use replication only to some databases? If yes, please check the manual about the exact behavior in such cases. Also check the changelogs after 3.23.49. Thanks for this hint, it was exactly the right one :-) The renamed table ad_429_twas not replicated (we use all ad_*-tables only as temp. tables so they are not replicated to minimize replication-traffic) when it was created so it couldn't be renamed on the slave. you are welcome! Greeting from Germany Lutz Maibach Best regards, Heikki Innobase Oy sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Rename Table in Replication failed / command missing in slave log
Hi, today I noticed a strange behaviour in MySQL 3.23.49a-Replication I can't explain. A perl-script which controls whether master- and replication-tables are equal showed me, that a master-table was missing on both slaves (two backups are better than one :-) ) When I controlled the master update-log I saw that the master-table was renamed with the following command: ALTER TABLE ad_429_t RENAME adprj_7; This command was missing in the slave-updatelogs. The sql-statements before and right after the missing one were present and no error was written down in the mysql-error-log. Trying to find out whether the Rename-Statement works right or not I did some tests with renaming a table but all actions were performed on the slaves too. Has someone experienced the same? I'm now a bit unsure whether the replication is working right. Greetings from Germany Lutz Maibach - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rename Table in Replication failed / command missing in slave log
If you're using InnoDB tables, replication stops the slaves from running. Heikki said he'd try to get this fixed for 3.23.52. --Pete On Mon, Aug 12, 2002 at 04:05:11PM +0200, Lutz Maibach wrote: Hi, today I noticed a strange behaviour in MySQL 3.23.49a-Replication I can't explain. A perl-script which controls whether master- and replication-tables are equal showed me, that a master-table was missing on both slaves (two backups are better than one :-) ) When I controlled the master update-log I saw that the master-table was renamed with the following command: ALTER TABLE ad_429_t RENAME adprj_7; This command was missing in the slave-updatelogs. The sql-statements before and right after the missing one were present and no error was written down in the mysql-error-log. Trying to find out whether the Rename-Statement works right or not I did some tests with renaming a table but all actions were performed on the slaves too. Has someone experienced the same? I'm now a bit unsure whether the replication is working right. Greetings from Germany Lutz Maibach - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RENAME TABLE and locking
I'm using MySQL-3.23.49a I use MySQL to replace plain text log files. The logging happens in a table named current. Every midnight, at 00:00:00, i perform a table rotation with this command from a cron job: CREATE TABLE new RENAME TABLE current TO $date, new TO current where $date is yesterday's date. The problem is, it's impossible to prevent today's messages from creeping into the old, rotated table. The reason is, there's always a very short delay before the rotation is performed, and some of today's messages always get into current before rotating it. I wonder if i can use locking to correct this problem. I mean: i create table new, lock it, perform the rotation (so now new is current, while the old current is $date), flush today's record from $date into current, then unlock current. Will it work this way? I'm not sure what happens to locking after RENAME; if i lock new and RENAME it to current, will the lock be inherited by current? -- Florin Andrei You know you're in trouble when packet floods are competing to flood you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication of rename table
Hi, I just run into a problem. RENAME TABLE put_tablename_here TO another_tablename here is not replicated. But a ALTER TABLE RENAME put_tablename_here TO another_tablename here is replicated. Is this a feature, or do I something wrong ? Regards PS (Filter): Mysql is the greatest database-software I have ever seen :p - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
rename table
hallo i want to change the name of a table. what must i do? -- Gruß bye Michael Ott - - Siemens AG - IS IT PS 51 ERL - - Werner-von-Siemens-Strasse 60 - - 91050 Erlangen- - Tel. +49 91 31 7 42 0 54 - - [EMAIL PROTECTED] - - open-source and you have much fun - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: rename table
In the last episode (Jun 27), Michael Ott said: hallo i want to change the name of a table. what must i do? http://www.mysql.com/doc, enter rename table, hit search: http://www.mysql.com/doc/R/E/RENAME_TABLE.html Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: rename table
Try this; ALTER TABLE old_table_name RENAME TO new_table_foobar \david -Original Message- From: Michael Ott [mailto:[EMAIL PROTECTED]] Sent: den 27 juni 2001 09:19 To: MySQL-Mailing-List Subject: rename table hallo i want to change the name of a table. what must i do? -- Gruß bye Michael Ott - - Siemens AG - IS IT PS 51 ERL - - Werner-von-Siemens-Strasse 60 - - 91050 Erlangen- - Tel. +49 91 31 7 42 0 54 - - [EMAIL PROTECTED] - - open-source and you have much fun - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
