MySQL Bugs: #26422: Generating statements in MySQL client

Bug #26422	Generating statements in MySQL client
Submitted:	15 Feb 2007 19:39	Modified:	6 Mar 2007 10:37
Reporter:	Andre Timmer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	5.0.18	OS:
Assigned to:		CPU Architecture:	Any
Tags:	client, Generating, statements

Description:
Problems generating statements to file and running it because format cannot be disabled in client mode.

How to repeat:
mysql> tee /var/tmp/statements.sql
Logging to file '/var/tmp/statements.sql'
mysql> select concat('drop table ', table_schema, '.', table_name, ';') Statement
    -> from   information_schema.tables
    -> where  table_schema = 'bbr'
    -> and    table_name like 'rep\_x\_%' escape '\\';
+-------------------------------------------------------------------------------+
| Statement                                                                     |
+-------------------------------------------------------------------------------+
| drop table bbr.rep_x_gen7report_adres7check_abuse_antwoordnummer2;            |
| drop table bbr.rep_x_gen7report_adres7summary_postcodes_shared_by_gemeenten1; |
| drop table bbr.rep_x_gen7report_adres7summary_postcodes_shared_by_gemeenten2; |
| drop table bbr.rep_x_gen7report_contactnr7check_strange_mobile1;              |
| drop table bbr.rep_x_gen7report_contactnr7check_strange_no_mobile1;           |
| drop table bbr.rep_x_gen7report_contactnr7check_strange_no_mobile2;           |
| drop table bbr.rep_x_gnrl7rprt_drs7smmry_pstcds_shrd_by_gmntn1;               |
+-------------------------------------------------------------------------------+
7 rows in set (0.01 sec)

mysql> notee
Outfile disabled.
mysql>
mysql> source /var/tmp/statements.sql

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> select concat('drop table ', table_schema, '.', table_name, ';') Statemen' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+-------------------------------------------------------------------------------' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
| drop table bbr.rep_x_gen7report_adres7summary_postcodes_shared_by_gemeenten1' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
| drop table bbr.rep_x_gen7report_adres7summary_postcodes_shared_by_gemeenten2' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
| drop table bbr.rep_x_gen7report_contactnr7check_strange_mobile1' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
| drop table bbr.rep_x_gen7report_contactnr7check_strange_no_mobile1' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
| drop table bbr.rep_x_gen7report_contactnr7check_strange_no_mobile2' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
| drop table bbr.rep_x_gnrl7rprt_drs7smmry_pstcds_shrd_by_gmntn1' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '|
+-----------------------------------------------------------------------------' at line 1

Suggested fix:
mysql> DISABLE FORMATTING

mysql> tee /var/tmp/statements.sql
Logging to file '/var/tmp/statements.sql'

mysql> select concat('drop table ', table_schema, '.', table_name, ';') Statement
    -> from   information_schema.tables
    -> where  table_schema = 'bbr'
    -> and    table_name like 'rep\_x\_%' escape '\\';

==> Desired output:
drop table bbr.rep_x_gen7report_adres7check_abuse_antwoordnummer2;            
drop table bbr.rep_x_gen7report_adres7summary_postcodes_shared_by_gemeenten1; 
drop table bbr.rep_x_gen7report_adres7summary_postcodes_shared_by_gemeenten2; 
drop table bbr.rep_x_gen7report_contactnr7check_strange_mobile1;              
drop table bbr.rep_x_gen7report_contactnr7check_strange_no_mobile1;           
drop table bbr.rep_x_gen7report_contactnr7check_strange_no_mobile2;           
drop table bbr.rep_x_gnrl7rprt_drs7smmry_pstcds_shrd_by_gmntn1;               

mysql> notee
Outfile disabled.
mysql>
mysql> source /var/tmp/statements.sql

==> And a succesfull run

Remark:
- this is a very handy feature for dba's and developers doing database maintenance
- in my years as an Oracle developer i used it a lot

Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html.

Looks like command similar to:

openxs@suse:~/dbs/5.0> bin/mysql -uroot -B -s -e "select concat('drop table ',
table_schema, '.', table_name, ';') from tables where table_schema='test' and table_name like 't%'" information_schema
drop table test.T;
drop table test.T1;
drop table test.T2;
...

will do what you want.

Thanks for the 'workaround'.

It would be nice if this where supported when the MySQL client is already running and one is during normal querying and then needs to 'generate commands without headers'. All in MySQL client session.

What you describe as the way to do it is for me a workaround.
So i think this feature request still stands, if you and your collega's decide not to honour it is another matter.

I think that some creative usage of pager and tee commands (http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html) in mysql may give you what you really need, even in intractive mode. But if you just want something very similar to features of Oracle's SQL*Plus, I agree - this is a reasonable feature request.

I think that some creative usage of pager and tee commands (http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html) in mysql may give you what you really need, even in intractive mode. But if you just want something very similar to features of Oracle's SQL*Plus, I agree - this is a reasonable feature request.

Thanks for making it a feature request. 

I gave your pager suggestion a try, just for fun.

mysql> \P ./noformat.sh
PAGER set to './noformat.sh'
mysql> select upper(name) from company limit 5;
  upper(name)
  GO B.V.
  NTERACTIE PROJECTENBURO
  SOTTO
  STRICTLY B.V.
  STATES ARTIST SQUARE C.V.
5 rows in set (0.01 sec)

Script noformat.sh:
#!/bin/ksh
sed -f noformat.sed

Script noformat.sed:
/+-/ {
d
}

s/|/ /g

An easier approach may be to use SELECT ... INTO OUTFILE

am using mysql 5.1 in ubuntu and got this error

ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

after executing mysql> DISABLE FORMATTING
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISABLE FORMATTING' at line 1

so please help me on this issue