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]
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: 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
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
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
