To close the loop on this issue, I had numerous emails back and forth with
Tom to troubleshoot and Andy Wu took a look as well.
Andy discovered that specific versions of MySQL and the driver may have
issues. Tom then discovered that you need to add this to your connection
string to have it work properly on the combinations that do have issues:
useDynamicCharsetInfo=false
And so it's clear what the solution is in terms of the code to get this to
work properly, it's:
<cftransaction>
<cfquery name="foo" datasource="foo">
SELECT SQL_CALC_FOUND_ROWS * FROM foo
WHERE id > 2 LIMIT 2
</cfquery>
<cfquery name="bar" datasource="foo">
SELECT FOUND_ROWS() AS numRows
</cfquery>
</cftransaction>
That will return the correct number of rows in the second query, but it has
to be done in a transaction so both queries use the same connection.
Thanks to Tom and Andy for helping us get to the bottom of this!
--
online documentation: http://openbd.org/manual/
google+ hints/tips: https://plus.google.com/115990347459711259462
http://groups.google.com/group/openbd?hl=en
