On 4/4/07, <b class="gmail_sendername">Larry Garfield</b> <<a href="mailto:larry@garfieldtech.com">larry@garfieldtech.com</a>> wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
This is an RFC of sorts. :-)<br><br>1) It turns out that PDO from PECL running under PHP 5.1.6, at least, has<br>issues. Specifically, segfault issues on otherwise perfectly sane queries.<br>This is apparently a known issue with
5.1.6, and one of the reasons 5.2<br>exists. :-) At present, I have no solution for it other than saying "well,<br>the PDO support is only if you're running 5.2, otherwise use the existing<br>MySQL/PostgreSQL drivers, deal". Does anyone have a problem with that, and
<br>if so, an alternate solution?</blockquote><div><br>Which distro? <br><br>I use Ubuntu 6.10 and if you install PHP 5, 5.1.6 is the default you get. This makes<br>it harder to use 5.2 (custom compile, ...etc.)<br><br>If you have working code, I can verify whether I have the same problem or not on
<br>my test server.<br><br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">2) Like any wrapper, PDO, while it offers some really nice features with a
<br>common API (like C-level prepared statements, which are what I'm mainly<br>after), has some "lowest common denominator" issues. The main one I've run<br>into so far is that there is no reliable equivalent of mysql_num_rows() for
<br>SELECT statements, only for data-changing statements[3]. In testing it<br>doesn't look like the MySQL PDO driver returns anything useful for rowCount()<br>on SELECT. That gives us 3 options.</blockquote><div><br>
In core HEAD, there are 43 occurrences in 25 files. Haven't checked contrib.<br></div><br>includes/common.inc:<br>includes/database.mysqli.inc:<br>includes/database.mysql.inc:<br>includes/database.pgsql.inc:<br>includes/locale.inc:
<br>includes/session.inc:<br>modules/aggregator/aggregator.module:<br>modules/block/block.module:<br>modules/blog/blog.module:<br>modules/book/book.module:<br>modules/comment/comment.module:<br>modules/drupal/drupal.module:
<br>modules/forum/forum.module:<br>modules/node/content_types.inc:<br>modules/node/node.module:<br>modules/path/path.module:<br>modules/ping/ping.module:<br>modules/statistics/statistics.module:<br>modules/system/system.install:
<br>modules/system/system.module:<br>modules/taxonomy/taxonomy.module:<br>modules/user/user.module:<br><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
A) Stop using db_num_rows() on result sets. (It's a database-specific feature<br>in the first place.) In places where we use it, use a separate count(*)<br>query instead. That's the more database-agnostic method, and is what the PHP
<br>manual recommends[3]. If we go this route, it will involve removing the<br>db_num_rows() function from the existing mysql and postgres drivers and<br>refactoring core queries accordingly.</blockquote><div><br>My concern here is that all db_num_rows() cases become two queries for
<br>people using MySQL (the vast majority) as well as PostgreSQL (it has <br>pg_num_rows()).<br><br>If these are index only queries, then it may not be a big deal, but anything more <br>(which we have a lot of), would incur a significant performance penalty. It also
<br>affects large sites using InnoDB which is notorious for very slow SELECT <br>COUNT(*). <br><br>See a benchmark here:<br><br><font size="-1"><span class="a"><a href="http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pitfalls.html">
http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pitfalls.html</a></span></font><br></div><br>Not sure what else can be done here. Is there a way to sneek in a pass thru function<br>(mysql_num_rows() in this case) like ODBC for example allows?
<br><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">B) Instead, have the PDO wrapper do a full ->fetchAll() on the result set.<br>That gives an array of array or object records (specified in the fetchAll()),
<br>which can then be simply sizeof()ed in the PDO/db_num_rows() implementation.<br>The downside here is that you need to specify in the fetchAll() whether you<br>want objects or arrays. To not horribly break any existing APIs (I'm trying
<br>to minimize the footprint for now; we can break everything later), we'd need<br>to therefore fetchAll() as an array in the PDO driver and then cast back to<br>an object in db_fetch_object() (or vice versa). That feels quite nasty to
<br>me, honestly, and is probably a not-unnoticeable performance hit.</blockquote><div><br>I don't think we can do that safely at all. The result set may exhaust available<br>memory, or tax other resources if it is rather large.
<br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">C) Drop the PDO idea. It should come as no surprise that this is my least<br>
favorite option. :-(</blockquote><div><br>Option D) : File a feature request against PDO to include a numRows() method, <br>but that is not a quick solution.<br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
I am hoping I can get this into Drupal 6, but time will tell if I can get it<br>ready in time.</blockquote><div><br>Ubuntu Feisty (due later in April) will have PHP 5.2.1.<br><a href="http://packages.ubuntu.com/feisty/web/php5">
http://packages.ubuntu.com/feisty/web/php5</a><br><br>But, what about other distros? If they are on PHP4 or 5.1, then this will be<br>a show stopper.<br></div><br></div>-- <br><a href="http://2bits.com">2bits.com</a><br><a href="http://2bits.com">
http://2bits.com</a><br>Drupal development, customization and consulting.