[development] PDO and database limitations

[Khalid Baheyeldin]

On 4/4/07, Larry Garfield <larry at garfieldtech.com> wrote:
>
> This is an RFC of sorts. :-)
>
> 1) It turns out that PDO from PECL running under PHP 5.1.6, at least, has
> issues.  Specifically, segfault issues on otherwise perfectly sane
> queries.
> This is apparently a known issue with 5.1.6, and one of the reasons 5.2
> exists. :-)  At present, I have no solution for it other than saying
> "well,
> the PDO support is only if you're running 5.2, otherwise use the existing
> MySQL/PostgreSQL drivers, deal".  Does anyone have a problem with that,
> and
> if so, an alternate solution?


Which distro?

I use Ubuntu 6.10 and if you install PHP 5, 5.1.6 is the default you get.
This makes
it harder to use 5.2 (custom compile, ...etc.)

If you have working code, I can verify whether I have the same problem or
not on
my test server.

2) Like any wrapper, PDO, while it offers some really nice features with a
> common API (like C-level prepared statements, which are what I'm mainly
> after), has some "lowest common denominator" issues.  The main one I've
> run
> into so far is that there is no reliable equivalent of mysql_num_rows()
> for
> SELECT statements, only for data-changing statements[3].  In testing it
> doesn't look like the MySQL PDO driver returns anything useful for
> rowCount()
> on SELECT.  That gives us 3 options.


In core HEAD, there are 43 occurrences in 25 files. Haven't checked contrib.

includes/common.inc:
includes/database.mysqli.inc:
includes/database.mysql.inc:
includes/database.pgsql.inc:
includes/locale.inc:
includes/session.inc:
modules/aggregator/aggregator.module:
modules/block/block.module:
modules/blog/blog.module:
modules/book/book.module:
modules/comment/comment.module:
modules/drupal/drupal.module:
modules/forum/forum.module:
modules/node/content_types.inc:
modules/node/node.module:
modules/path/path.module:
modules/ping/ping.module:
modules/statistics/statistics.module:
modules/system/system.install:
modules/system/system.module:
modules/taxonomy/taxonomy.module:
modules/user/user.module:

A) Stop using db_num_rows() on result sets.  (It's a database-specific
> feature
> in the first place.)  In places where we use it, use a separate count(*)
> query instead.  That's the more database-agnostic method, and is what the
> PHP
> manual recommends[3].  If we go this route, it will involve removing the
> db_num_rows() function from the existing mysql and postgres drivers and
> refactoring core queries accordingly.


My concern here is that all db_num_rows() cases become two queries for
people using MySQL (the vast majority) as well as PostgreSQL (it has
pg_num_rows()).

If these are index only queries, then it may not be a big deal, but anything
more
(which we have a lot of), would incur a significant performance penalty. It
also
affects large sites using InnoDB which is notorious for very slow SELECT
COUNT(*).

See a benchmark here:

http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pitfalls.html

Not sure what else can be done here. Is there a way to sneek in a pass thru
function
(mysql_num_rows() in this case) like ODBC for example allows?

B) Instead, have the PDO wrapper do a full ->fetchAll() on the result set.
> That gives an array of array or object records (specified in the
> fetchAll()),
> which can then be simply sizeof()ed in the PDO/db_num_rows()
> implementation.
> The downside here is that you need to specify in the fetchAll() whether
> you
> want objects or arrays.  To not horribly break any existing APIs (I'm
> trying
> to minimize the footprint for now; we can break everything later), we'd
> need
> to therefore fetchAll() as an array in the PDO driver and then cast back
> to
> an object in db_fetch_object() (or vice versa).  That feels quite nasty to
> me, honestly, and is probably a not-unnoticeable performance hit.


I don't think we can do that safely at all. The result set may exhaust
available
memory, or tax other resources if it is rather large.

C) Drop the PDO idea.  It should come as no surprise that this is my least
> favorite option. :-(


Option D) : File a feature request against PDO to include a numRows()
method,
but that is not a quick solution.

I am hoping I can get this into Drupal 6, but time will tell if I can get it
> ready in time.


Ubuntu Feisty (due later in April) will have PHP 5.2.1.
http://packages.ubuntu.com/feisty/web/php5

But, what about other distros? If they are on PHP4 or 5.1, then this will be
a show stopper.

-- 
2bits.com
http://2bits.com
Drupal development, customization and consulting.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20070404/194fff38/attachment.htm