Description:
I'm trying to reproduce a customer problem.
For this, I'm writing a file containing SQL commands which I will pass to the "mysql" client, either as standard input (redirection) or using the "source" command.
I want to get a log, listing the command, its result, and the error/warning/timing information, like I have on the screen when using "mysql" interactively.
However, in batch mode (including the "-e" parameter) the program will only produce the results, not the other parts of a log.
The only way which I found is to use "-v" ("--verbose"), but its formatting is not helpful because of blank lines in the wrong place (see in "How to repeat").
I also tried "--debug", this triggered my report of bug#77455.
Some people might propose I should use "mysqltest" - no, that is no solution:
1) It is designed to run complete test cases with individual protocol files, I need to combine SQL snippets with other commands in-between.
2) It is not part of a standard installation.
3) It has too much built-in special handling.
4) It is not known to the average MySQL user.
How to repeat:
mysql@support:~ [mysqld-5529, 3329]> echo "show global variables like 'version%' " | mysql -u root --table
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.5.29-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
mysql@support:~ [mysqld-5529, 3329]> echo "show global variables like 'version%' " > /tmp/t1
mysql@support:~ [mysqld-5529, 3329]> mysql -u root --table < /tmp/t1
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.5.29-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
mysql@support:~ [mysqld-5529, 3329]> mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost mysqld-5529 [(none)] SQL> source /tmp/t1
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.5.29-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)
root@localhost mysqld-5529 [(none)] SQL> Bye
mysql@support:~ [mysqld-5529, 3329]>
Note that "source" will display the row count and time, the other ways don't.
See below why "--verbose" is not really helpful:
mysql@support:~ [mysqld-5529, 3329]> cat /tmp/t2
show global variables like 'version%' ;
show databases;
mysql@support:~ [mysqld-5529, 3329]> mysql -u root --table --verbose < /tmp/t2
--------------
show global variables like 'version%'
--------------
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| version | 5.5.29-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
--------------
show databases
--------------
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql@support:~ [mysqld-5529, 3329]>
Note:
1) The semicolon is not logged.
2) There is a blank line between command and result, but none between result and next command.
Suggested fix:
I would like to see an option "--log-commands" (or similar) that makes "mysql" produce the same output in batch mode like it does in interactive mode:
- the statement text, including the terminator (';' or '\G'),
- the result (like now),
- the info on errors, warning, row count, and timing,
- no unmotivated separator lines (like "--verbose" does).
The option should have the same effect on statements read via "source" during an interactive run.
It might be good to also log a marker for the end of a "source" file.
Description: I'm trying to reproduce a customer problem. For this, I'm writing a file containing SQL commands which I will pass to the "mysql" client, either as standard input (redirection) or using the "source" command. I want to get a log, listing the command, its result, and the error/warning/timing information, like I have on the screen when using "mysql" interactively. However, in batch mode (including the "-e" parameter) the program will only produce the results, not the other parts of a log. The only way which I found is to use "-v" ("--verbose"), but its formatting is not helpful because of blank lines in the wrong place (see in "How to repeat"). I also tried "--debug", this triggered my report of bug#77455. Some people might propose I should use "mysqltest" - no, that is no solution: 1) It is designed to run complete test cases with individual protocol files, I need to combine SQL snippets with other commands in-between. 2) It is not part of a standard installation. 3) It has too much built-in special handling. 4) It is not known to the average MySQL user. How to repeat: mysql@support:~ [mysqld-5529, 3329]> echo "show global variables like 'version%' " | mysql -u root --table +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.5.29-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux2.6 | +-------------------------+------------------------------+ mysql@support:~ [mysqld-5529, 3329]> echo "show global variables like 'version%' " > /tmp/t1 mysql@support:~ [mysqld-5529, 3329]> mysql -u root --table < /tmp/t1 +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.5.29-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux2.6 | +-------------------------+------------------------------+ mysql@support:~ [mysqld-5529, 3329]> mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.5.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost mysqld-5529 [(none)] SQL> source /tmp/t1 +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.5.29-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux2.6 | +-------------------------+------------------------------+ 4 rows in set (0.00 sec) root@localhost mysqld-5529 [(none)] SQL> Bye mysql@support:~ [mysqld-5529, 3329]> Note that "source" will display the row count and time, the other ways don't. See below why "--verbose" is not really helpful: mysql@support:~ [mysqld-5529, 3329]> cat /tmp/t2 show global variables like 'version%' ; show databases; mysql@support:~ [mysqld-5529, 3329]> mysql -u root --table --verbose < /tmp/t2 -------------- show global variables like 'version%' -------------- +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.5.29-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | linux2.6 | +-------------------------+------------------------------+ -------------- show databases -------------- +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ mysql@support:~ [mysqld-5529, 3329]> Note: 1) The semicolon is not logged. 2) There is a blank line between command and result, but none between result and next command. Suggested fix: I would like to see an option "--log-commands" (or similar) that makes "mysql" produce the same output in batch mode like it does in interactive mode: - the statement text, including the terminator (';' or '\G'), - the result (like now), - the info on errors, warning, row count, and timing, - no unmotivated separator lines (like "--verbose" does). The option should have the same effect on statements read via "source" during an interactive run. It might be good to also log a marker for the end of a "source" file.