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

[15 Feb 2007 19:39] Andre Timmer
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
[16 Feb 2007 12:41] Valeriy Kravchuk
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.
[16 Feb 2007 16:06] Andre Timmer
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.
[19 Feb 2007 12:35] Valeriy Kravchuk
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.
[19 Feb 2007 12:35] Valeriy Kravchuk
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.
[6 Mar 2007 10:37] Andre Timmer
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
[6 Mar 2007 10:44] Sergei Golubchik
An easier approach may be to use SELECT ... INTO OUTFILE
[18 Feb 2010 12:35] atish biswal
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