Hmm, Well, I guess I am stuck at directly calling exec() right now, and bypassing the query() function. After changing to either the Mysqli adapter (or hacking the PDO adapter to call exec when the are no parameters), I get a segfault when I run phpunit using the code I am developing. Not entirely sure how to track that down, since a segfault means the PHP engine crashed, not my script. I changed it to simply call exec() on the PDO adapter directly, and not it works correctly.
In case you are wondering, what I am doing is writing DB setup code for PHPUnit testing. I wrote some code that takes a copy of the live database, trims it down to a reasonable size, and then writes it out to disk with one file per table. Then in my PHPUnit testing code I can selectively restore any table back to a known pristine state for testing. What I have been testing that caused the segfault was to restore ALL the tables, which is a lot of data. Makes me think the problem is that PHP ran out of memory or something, and using exec() does not put as much pressure on the garbage collectors (all those statements being created will use a LOT of memory!). BTW, does anyone know why the mailing list seems to screw up the formatting of my code blocks when I get the email back? It looks correct in my sent items folder, just not on the email that comes back from the list? Regards, Regards, Kendall Bennett, CEO A Main Hobbies 424 Otterson Drive, Suite 160 Chico, CA 95928 1-800-705-2215 (Toll-Free) 1-530-894-0797 (Int'l & Local) 1-530-894-9049 (Fax) http://www.amainhobbies.com ________________________________ From: Kendall Bennett <[email protected]> Date: Sun, 14 Jun 2009 17:37:32 -0700 To: Zend Framework General <[email protected]>, <[email protected]> Subject: Re: [fw-general] A Zend_Db Adapter for ext/mysql (mysql_*) This actually brings up an issue I just ran into today. The first problem I found appears to be a bug in the PDO::MySQL adapter, in that certain queries passed to it with parameter binding characters (?) will fail even if you are not binding parameters to the statement. It fails saying that the parameters were not passed to the binding. I have no idea why it is doing this, but I did isolate it down to a single insert statement with two inserts combined, that caused the problem. When I took out the second statement, it worked correctly. This code will fail on PHP 5.2.6 and Zend Framework 1.8.3: $sql = "insert into configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description) values ('573','Server to use','MODULE_SHIPPING_UPS_SERVER','production','Use the test or production UPS server?'), ('557','Internal Comments','ACCOUNT_COMMENTS','true','Display internal (admin only) comments in the customer\'s account')"; $db->query($sql); It works correctly with the Mysqli adapter, just not the PDO adapter. It will work if I remove the question mark after the UPS server text, so that appears to be what is confusing it, even though I am not passing in any bound parameters. Also, if I remove the send insert and just do a single insert, it works correctly. I don't think this is a problem with ZF itself, as it appears to be failing inside the PDO adapter. I also tested the code by passing it directly to the Zend_Db_PDO adapter exec() function and it did not fail, which is to be expected. But the exec() function is only present for PDO adapters, not for any of the other adapters. Anyway, I have two solutions to fix this. I can either use the Mysqli adapter (which I am doing now), or I can work around it by directly calling the exec() function of the PDO adapter. Which brings me to the point of my email - I think there should be an exec() function at the highest level of the Zend_Db_Adapter class structure, so it can be used on all databases. There are two reasons for this: 1. On some databases like MySQL, using prepared statements when you simply wish to execute a native query, means that the query cache will be bypassed. Most sites are not yet using MySQL 5.1 or higher, so this is a real problem (it is on our site). 2. Bypassing the prepared statements in either PDO adapter or the Mysqli adapter will make the code run faster, since none of the overhead of preparing the statement is actually necessary, and the query can be executed directly Although the more I think about it, perhaps the correct solution is to change the internal implementation of the Zend_Db_Adapter_Abstract::query() function such that if there are no bound parameters passed in, that it will directly execute the query and not do the prepared statement thing. Internally this function ends up doing this to execute the actual query: // prepare and execute the statement with profiling $stmt = $this->prepare($sql); $stmt->execute($bind); Which is a LOT of extra work for now reason, if the the bind array is empty, since creating a prepared statement is NOT free, and if it is only used for a single query and tossed away, it is huge waste of resources (not to mention it bypasses the query cache on MySQL 5.0 and earlier). So I am thinking the code should be something like this: // prepare and execute the statement with profiling if ($bind) { $stmt = $this->prepare($sql); $stmt->execute($bind); } else { $this->exec($sql); } Of course the exec() function does not exist in the Mysqli class, and does not handle query profiling. It does exist in the PDO_MYSQL class though. Which is why I think the exec() function should be added to the abstract adapter class and implemented in all the drivers, and then this code would work. Then the developer can choose to call exec() directly if needed, or call query() with no parameters. Either way, from a performance standpoint I think there should be a function in the Zend_Db_Adapter class tree that will execute a SQL statement directly without any parameter binding, for situations where this is needed. Regards, Kendall Bennett, CEO A Main Hobbies 424 Otterson Drive, Suite 160 Chico, CA 95928 1-800-705-2215 (Toll-Free) 1-530-894-0797 (Int'l & Local) 1-530-894-9049 (Fax) http://www.amainhobbies.com ________________________________ From: till <[email protected]> Date: Sun, 14 Jun 2009 12:06:13 -0700 To: Matthew Ratzloff <[email protected]> Cc: Tobias Gies <[email protected]>, Benjamin Eberlei <[email protected]>, Zend Framework General <[email protected]>, <[email protected]> Subject: Re: [fw-general] A Zend_Db Adapter for ext/mysql (mysql_*) Don't want to hijack this thread -- but we recently developed an ext/mysqli-based adapter which doesn't use prepared statements. Does anyone have any interest in the code -- I'd look into opensourcing it then. Till On Sun, Jun 14, 2009 at 5:52 PM, Matthew Ratzloff<[email protected]> wrote: > Sure. I never said it should be Zend namespaced, you know. ;-) > -Matt > On Sat, Jun 13, 2009 at 4:02 PM, Tobias Gies <[email protected]> > wrote: >> >> Hi, >> >> Matthew, I don't think this should be included in the Zend namespace. >> Ben, you have done some excellent work on this adapter, I think it's >> great, but in my opinion, we shouldn't include adapters for "outdated" >> libraries in the framework. It might fit nicely into ZendX, but I'd >> rather not have it included in the main framework. >> >> Best regards, >> Tobias >> >> 2009/6/13 Matthew Ratzloff <[email protected]>: >> > CLA? Proposal? Inclusion in the framework? :-) >> > >> > -Matt >> > >> > On Saturday, June 13, 2009, Benjamin Eberlei <[email protected]> >> > wrote: >> >> Hello everyone, >> >> >> >> I have a project that i slowly want to migrate to Zend Framework. This >> >> project >> >> uses ext/mysql all over the place and therefore requires an adapter of >> >> this >> >> type to keep the number of database connections small. I wrote this >> >> adapter >> >> using soley mysql_* functions, which emulates prepared statements and >> >> works >> >> exactly like any other Zend_Db adapter, even works with Zend_Db_Table. >> >> >> >> Its passing all unittests of the Zend_Db test suite so should be pretty >> >> stable. >> >> >> >> If anyone needs this, i put it up on GitHub: >> >> http://github.com/beberlei/Zend_Db-Adapter-for-ext-mysql/tree/master >> >> -- >> >> Benjamin Eberlei >> >> http://www.beberlei.de >> >> >> > > >
