Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:60935
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain oracle.com designates 148.87.113.117 as permitted sender)
To: Anthony Ferrara <ircmaxell@gmail.com>
Cc: Ulf Wendel <ulf.wendel@phpdoc.de>, internals@lists.php.net
In-Reply-To: <CAAyV7nG_fWqbrq24vNB9EScB85zvF3JsGx16COoWs6JMgqbkOA@mail.gmail.com>
References: 
	 <CAAyV7nFciZcsL+AW9W4GbhM+PhM3RhWOJ7G0iBQsEHfSWO2QmA@mail.gmail.com>
	 <4FDB5604.5000704@oracle.com> <4FDB62C2.6090702@oracle.com>
	 <4FDC3396.10406@phpdoc.de> <4FE0F301.70808@oracle.com>
	 <CAEZPtU5SZn60FDiGpYmYZFqpNf0kDuL9_ACNvRfwfdn+Z0p_YA@mail.gmail.com>
	 <4FE2C5D7.2040606@phpdoc.de>
	 <CAAyV7nEepENT568W4jficZ2sO6ArM4kuVZR+X=DJbL2QxyGUCw@mail.gmail.com>
	 <CAAyV7nG_fWqbrq24vNB9EScB85zvF3JsGx16COoWs6JMgqbkOA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Organization: Oracle Corporation
Date: Fri, 22 Jun 2012 12:25:07 +0200
Message-ID: <1340360707.1719.61.camel@guybrush>
Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: Re: [PHP-DEV] [PATCH - PR] Disable ATTR_EMULATE_PREPARES by
 default for PDO_Mysql
From: johannes.schlueter@oracle.com (Johannes =?ISO-8859-1?Q?Schl=FCter?=)

On Thu, 2012-06-21 at 22:35 -0400, Anthony Ferrara wrote:
> Actually, the more I think about it, this would result in an
> inadvertent API change. Right now, if you have a SQL syntax error, the
> error would be thrown by ->execute(). But with this change, the error
> would be thrown by ->prepare(). So code that currently wraps execute()
> but not prepare() with a try{}catch(){} would start failing.
>=20
> Is this a show stopper? Or is restoring the "proper" execution path worth=
 it?
>=20
> From what I can see in the few open source drivers I looked at, it
> won't be an issue (Drupal, Doctrine, Zend, Lithium, etc)...

Well, one can easily argue that an error during prepare is the correct
behavior, so this could actually be seen as a bug fix.

The interesting question is whether PDO's emulation allows placeholders
in places where the MySQL server doesn't in old servers (5.0?) a case
was
   SELECT * FROM t LIMIT ?
which was invalid in MySQL but valid with PDO when binding as integer.
This would be a broken feature. On some quick(!) tests I didn't come up
with a case where this was relevant.


On a related note, as I wrote in a previous mail (which Ulf mentioned)
there are still statements which can't be prepared in MySQL. One example
is ALTER DATABASE. The MySQL documentation unfortunately has only a
positive list[1] which makes it a it hard to find the missing ones ...
anyways, PDO then tries to fallback to emulation, but there seems to be
a bug:

    <?php
    $p =3D new PDO("mysql:host=3Dlocalhost","root");
    $p->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $p->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    var_dump($p->query("ALTER DATABASE foo CHARACTER SET =3D utf8"));
    ?>

        Fatal error: Uncaught exception 'PDOException' with message
        'SQLSTATE[HY000]: General error: 2014 Cannot execute queries
        while other unbuffered queries are active.  Consider using
        PDOStatement::fetchAll().  Alternatively, if your code is only
        ever going to run against mysql, you may enable query buffering
        by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.'
       =20
I will try to look into the bug (unless anybody else is faster) but it
shows that that area needs more testing, too.

If you're still running 5.1 the list of prepareable statements is much
shorter[2]. 5.1 currently is still quite common as many distributions
bundle it, but I assume it's no big deal for php-master as it will take
sometime till php-master is released and reaches the people and by then
most are hopefully on 5.5 or even 5.6.

johannes

[1] http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.h=
tml
[2] http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.h=
tml

--=20
Johannes Schl=C3=BCter, MySQL Engineering, ORACLE Deutschland B.V. & Co KG