cf 7 and mysql 5 stored procedures and temporary tables

[gnurulz]

We developed some stored procedures in mysql 5.0.10a that work with coldfusion
7. We upgraded the mysql database to the GA Release of 5.0.15 and now the
procedures return an error when called saying that the temporary table cannot
be found. We are calling the stored procedures using the standard <cfquery>
tag. The stored procedures seem to work fine, it seems to be a problem with
how cf7 is executing them.

[Baroti]

We have also encountered a problem with temporary tables. We are able to
create the tables and retrieve data from them when the data is numeric. When
trying to retrieve string data (varchar, text, mediumtext, etc.), CF throws an
error saying that the temporary table could not be found. Trying to get
information about the temporary table after the error indicates that the
temporary table is still present -- it has not been dropped by MySQL.

Using CF MX6 and MySQL 4.1, the temporary tables work fine.
Using CF MX7 and MySQL 4.1, the temporary tables work fine.
Using CF MX6 and MySQL 5.0, the temporary tables work fine.
Using CF MX7 and MySQL 5.0, the temporary tables do not work.

We have tested various JConnectors, and they do not seem to be the source of
the problem. Creating and reading from temporary tables using Java (and the
same JConnectors) works fine. See MySQL site:
[url]http://bugs.mysql.com/bug.php?id=15060[/url]

[TomDonovan]

I'm seeing different results with temporary tables:

The Connect/J 3.0 driver - mysql-connector-java-3.0.17-ga-bin.jar works for
me with both CFMX6.1 & CFMX7.0.1

The Connect/J 3.1 driver - mysql-connector-java-3.1.9-bin.jar fails for me
with CFMX7

The 3.1 driver doesn't work at all with CFMX6.1. Note that you should avoid
using Connect/J 3.1.10+ with CF7 because of
[url]http://bugs.mysql.com/bug.php?id=13469[/url]. Once this gets fixed, later 3.1 drivers
should be OK.

I'm using mySQL 5.0.16 on Windows.