Bug #13326 SQLPS statement logging is incomplete in 5.0
Submitted: 19 Sep 2005 16:12 Modified: 25 Jul 2007 3:39
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0.13 and up OS:Any
Assigned to: Konstantin Osipov CPU Architecture:Any

[19 Sep 2005 16:12] Paul Dubois
Description:
For prepared statements executed via the SQL interface (PREPARE, EXECUTE,
DEALLOCATE PREPARE), the server logs the SQL interface statements as Query
lines. It also writes Prepare and Execute lines to show which statements
are prepared and executed.

The following example shows a set of input SQL statements, and the
corrreponding lines that the server writes to the general query
log.

Input statements:              General query log:

SET @x = 3;                    Query    SET @x = 3
PREPARE s FROM 'SELECT ?';     Query    PREPARE s FROM 'SELECT ?'
                               Prepare  [1] SELECT ?
PREPARE t FROM 'SELECT ?*2';   Query    PREPARE t FROM 'SELECT ?*2'
                               Prepare  [2] SELECT ?*2
EXECUTE s USING @x;            Query    EXECUTE s USING @x
                               Execute  [1] SELECT @'x'
EXECUTE t USING @x;            Query    EXECUTE t USING @x
                               Execute  [2] SELECT @'x'*2
DEALLOCATE PREPARE s;          Query    DEALLOCATE PREPARE s
DEALLOCATE PREPARE t;          Query    DEALLOCATE PREPARE t

For PREPARE, the Query line in the log indicates receipt of the
PREPARE statement from the client, and the Prepare line indicates
the statement that is prepared.  For Execute, the Query line in the
log indicates receipt of the EXECUTE statement from the client, and
the Execute line indicates the statement that is executed.  Execute
lines show the prepared statement after substitution of user variables
for ? parameters.

The behavior described above was implemented at about MySQL 4.1.10,
and also was propagated to MySQL 5.0.  However, as of MySQL 5.0.13,
the Prepare and Execute lines are missing -- but only for SQLPS statements.
For prepared statements executed via mysql_stmt_prepare() and
mysql_stmt_execute(), the Prepare and Execute lines still are logged.

For consistency, the Prepare and Execute lines also should be logged
for SQLPS statements.  That is, the behavior that is seen in 5.0.12 should
be restored.

How to repeat:
See above.
[12 Jul 2007 19:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30831

ChangeSet@1.2518, 2007-07-12 23:14:00+04:00, kostja@bodhi.(none) +1 -0
  Apply community contributed fix for Bug#13326 SQLPS statement logging is 
  incomplete in 5.0 (and review fixes).
  
  When in 5.0.13 I introduced class Prepared_statement and methods
  ::prepare and ::execute, general logging was left out of this class.
  This was good for stored procedures, since in stored procedures
  we do not log sub-statements, but introduced a regression in case of SQL
  syntax for prepared statements, as previously we would log the actual
  statements to the log, and after the change we would log only
  COM_QUERY text.
  
  Restore the old behavior, but still suppress logging if inside a stored 
  procedure.
  
  Based on a community contributed patch from Vladimir Shebordaev.
  
  No test case since we do not have a mechanism to test output
  of the general log.
[12 Jul 2007 19:29] Konstantin Osipov
Queued into 5.0-runtime
[17 Jul 2007 15:30] Bugs System
Pushed into 5.0.48
[17 Jul 2007 15:30] Bugs System
Pushed into 5.1.21-beta
[25 Jul 2007 3:39] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.

For the general query log, logging of prepared statements executed
via the C API differed from logging of prepared statements performed
with PREPARE and EXECUTE. Logging for the latter was missing the
Prepare and Execute lines.