Bug #72375 Prepared statements whose execution fail are not logged to the general log

• Submitted: 17 Apr 2014 20:50
• Modified: 29 Jul 2014 15:46
• Reporter: Davi Arnaut (OCA)
• Status: Closed
• Category: MySQL Server: Logging
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Any
• CPU Architecture: Any
• Tags: general query log, logging, prepared statements, regression

[17 Apr 2014 20:50] Davi Arnaut

Description:
If a prepared statement being executed produces an error, the query is not logged to
the general log.  This makes it hard to debug prepared statements that are failing to
execute and even have some more serious implications if the general log is being used
for auditing.

How to repeat:
mysql> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log = 'on';
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table mysql.general_log;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> prepare stmt from 'drop table t1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;
ERROR 1051 (42S02): Unknown table 'test.t1'
mysql> select argument from mysql.general_log;
+----------------------------------------+
| argument                               |
+----------------------------------------+
| create table t1 (a int)                |
| PREPARE stmt FROM ...                  |
| drop table t1                          |
| execute stmt                           |
| drop table t1                          |
| execute stmt                           |
| select argument from mysql.general_log |
+----------------------------------------+
7 rows in set (0.00 sec)

[17 Apr 2014 21:36] MySQL Verification Team

Thank you for the bug report.

[29 Jul 2014 15:46] Paul DuBois

Noted in 5.7.5 changelog.

If a prepared statement being executed produced an error, the server
failed to write the statement to the general query log.

[27 Apr 2015 12:56] Laurynas Biveinis

commit 38dcb9fb00d5cb1a398977edc56fa0f454037498
Author: Kristofer Pettersson <[e-mail]>
Date:   Tue Mar 17 13:59:31 2015 +0100

    Bug #20536590: BACKPORT BUG #12368204 AND BUG #16953758 TO MYSQL-5.6
    
    Backported the fixes.
    Partially backported the fix for bug #19463877 too :
    the part about different audit events count for the different
    protocols.
    Changed the strategy of the original fix to account for the lack
    of the changes done in WL#6613 as follows:
    Instead of mixing the calls to the audit log API by calling them
    both outside of the general log hooks and inside some
    moved all audit log API calls inside the general log hooks but
    before any checks to the general log options. Made sure that the
    general log hooks are called regardless of the value of the
    general log option and moved all checking of it inside the
    hooks.
    Changed the binary log calles used by the prepared statement
    code to use the convenience hooks instead of directly calling the
    LOGGER functions. Ensured that prepared statements pass down the
    query text.
    Getting it from the THD is not correct for prepared statements.
    ** Added an extra test file that tests firewall with general log off.