Bug #23975 prepare() does not respect mysql_emulated_prepare option (patch attached)
Submitted: 4 Nov 2006 16:03 Modified: 29 Dec 2006 18:56
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:Connectors: DBD::mysql ( Perl ) Severity:S3 (Non-critical)
Version:3.0008_1 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: Contribution

[4 Nov 2006 16:03] Philip Stoev
Description:
When mysql_emulated_prepare is passed directly to prepare(), the option is ignored.

Also please note that the old option name - mysql_server_prepare appears to be still present in the documentation and the prepare_noerror.t file.

Finally, the documentation talks about using mysql_emulated_prepare=1 option, which intuitively implies that one can use mysql_server_prepare=0 on a per-statement basis, which is not true, since it appears some of those options are checked with SvOK() which returns true in both cases, since both 0 and 1 are != undef. Maybe SvTRUE() should be used everywhere.

How to repeat:
# This does not use prepared statements, server receives COM_QUERY
$dbh->do("SELECT 1", { mysql_emulated_prepare => 1 });

# This uses prepared statements, the server receives COM_STMT_PREPARE packet
my $sth = $dbh->prepare("SELECT 1", { mysql_emulated_prepare => 1 });
$sth->execute();

Suggested fix:
diff -u -r DBD-mysql-3.0008_1/dbdimp.c dbd-mysql-mine-prepared/dbdimp.c
--- DBD-mysql-3.0008_1/dbdimp.c 2006-10-16 16:10:37.000000000 +0300
+++ dbd-mysql-mine-prepared/dbdimp.c    2006-11-04 17:31:34.000000000 +0200
@@ -2395,8 +2395,8 @@
   if (attribs)
   {
     svp= DBD_ATTRIB_GET_SVP(attribs, "mysql_emulated_prepare", 22);
     imp_sth->use_server_side_prepare = (svp) ?
-      SvTRUE(*svp) : imp_dbh->use_server_side_prepare;
+      !SvTRUE(*svp) : imp_dbh->use_server_side_prepare;
   }

   imp_sth->fetch_done= 0;
[8 Dec 2006 20:50] Sveta Smirnova
Test

Attachment: bug23975.pl (text/plain), 781 bytes.

[8 Dec 2006 20:52] Sveta Smirnova
Thank you for the report.

I can not repeat the problem using attached test. Value Com_stmt_prepare is zero after test run:

mysql> show status like '%stmt%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_close          | 0     | 
| Com_stmt_execute        | 0     | 
| Com_stmt_fetch          | 0     | 
| Com_stmt_prepare        | 0     | 
| Com_stmt_reset          | 0     | 
| Com_stmt_send_long_data | 0     | 
| Prepared_stmt_count     | 0     | 
+-------------------------+-------+
7 rows in set (0.00 sec)

Please examine if test is correct.
[8 Dec 2006 21:24] Philip Stoev
Hello, unfortunately, for some reason (maybe another bug?), those variables that you are SHOW-ing are either per-session or get periodically reset. You can not query them using the standalone mysql client. You need to do that from within the perl script, and the following one-liner demonstrates the bug:

use Data::Dumper;
print Dumper $dbh->selectall_arrayref(" show status like '%stmt%'", { mysql_emulated_prepare => 1});

Alternative ways to spot the bug are to examine the patch provided and to sniff the network traffic between the client and the server.
[20 Dec 2006 19:39] Sveta Smirnova
Thank you for addition.

Verified as described.
[29 Dec 2006 18:56] Jim Winstead
mysql_emulated_prepare is no more, mysql_server_prepare is the option to control whether to use server-side prepared statements. They are disabled by default due to their (server-side) limitations.