Weird behaviour with positional parameters in HDBC-mysql
Dear café (CC John and Chris), I'm having some trouble with getting positional parameters in HDBC-mysql to work. Most of the time they work fine, but sometimes (and often enough that it's a serious bother) the parameters don't reach the server correctly. Let me first describe my setup: * Ubuntu 8.10 intrepid (inside VMWare Fusion on Mac OS X Snow Leopard) * mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2 * ghc 6.10.3 * HDBC-2.2.4 * HDBC-mysql-0.6.1 I have a database with a single, empty table:
CREATE TABLE `foo` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1031 DEFAULT CHARSET=latin1
And I have the following Haskell program:
import Database.HDBC import Database.HDBC.MySQL import Data.List import Data.Traversable import Control.Monad
main :: IO () main = do conn <- connectMySQL defaultMySQLConnectInfo { mysqlUnixSocket = "/var/run/mysqld/mysqld.sock" , mysqlUser = "root" , mysqlDatabase = "db" } for [1..1000] $ \n -> do let sql = "select id from foo where id in (" ++ intercalate "," (replicate (fromInteger n) "?") ++ ")" stmt <- prepare conn sql execute stmt (map SqlInteger [1..n]) finish stmt return ()
The program produces no output. All 1000 queries that are sent to the database are expected to return 0 results, since table foo is empty. This is all fine. The problem is in the queries that the server receives. If I turn on full logging, I expect all queries to have a "where id in (...)" where there is an increasing list of integers within the parentheses. But when examining the logs, some are small negative numbers, NULLs or just other numbers:
506 Execute [980] select id from foo where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26, 27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,824,825,826,827,828,829, 830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847, 848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865, 866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883, 884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901, 902,903,904,905,906,907,908,0,0,34359738368,-5210566582833489576,NULL, NULL,0,34359738368,-5210565758199768552,NULL,NULL,0,34359738368, -5210564933566047528,NULL,NULL,0,34359738368,-5210564108932326504,NULL, NULL,0,34359738368,-5210563284298605480,NULL,NULL,0,34359738368, -5210562459664884456,NULL,NULL,158,159,160,161,162,(snip)
The server then faithfully executes the statement and returns the results expected for the query it reports to have received, which (in a table with actual data) is not what the Haskell program was expecting. If I compile the program once, the results vary per run. If I don't use positional parameters but instead render the full SQL query myself, there is no problem. Is this a problem in HDBC or in HDBC-mysql? What's going on here? How do I fix or work around this? Many thanks, Martijn.
Martijn van Steenbergen wrote:
Is this a problem in HDBC or in HDBC-mysql?
Smells to me like a bug in HDBC-mysql. However, it is possible that the bug lies in the C MySQL library itself. To help isolate, it would be good to try your program: * with HDBC-postgresql * with HDBC-sqlite3 * with HDBC-ODBC and the MySQL driver -- John
Hi Martijn, I can reproduce the problem and investigate. My guess is that something is getting garbage collected that shouldn't be... I'll let you know what I figure out. thanks, chris On Apr 7, 2010, at 3:02 PM, Martijn van Steenbergen wrote:
Dear café (CC John and Chris),
I'm having some trouble with getting positional parameters in HDBC-mysql to work. Most of the time they work fine, but sometimes (and often enough that it's a serious bother) the parameters don't reach the server correctly.
Let me first describe my setup: * Ubuntu 8.10 intrepid (inside VMWare Fusion on Mac OS X Snow Leopard) * mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2 * ghc 6.10.3 * HDBC-2.2.4 * HDBC-mysql-0.6.1
I have a database with a single, empty table:
CREATE TABLE `foo` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1031 DEFAULT CHARSET=latin1
And I have the following Haskell program:
import Database.HDBC import Database.HDBC.MySQL import Data.List import Data.Traversable import Control.Monad
main :: IO () main = do conn <- connectMySQL defaultMySQLConnectInfo { mysqlUnixSocket = "/var/run/mysqld/mysqld.sock" , mysqlUser = "root" , mysqlDatabase = "db" } for [1..1000] $ \n -> do let sql = "select id from foo where id in (" ++ intercalate "," (replicate (fromInteger n) "?") ++ ")" stmt <- prepare conn sql execute stmt (map SqlInteger [1..n]) finish stmt return ()
The program produces no output. All 1000 queries that are sent to the database are expected to return 0 results, since table foo is empty. This is all fine.
The problem is in the queries that the server receives. If I turn on full logging, I expect all queries to have a "where id in (...)" where there is an increasing list of integers within the parentheses. But when examining the logs, some are small negative numbers, NULLs or just other numbers:
506 Execute [980] select id from foo where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26, 27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,824,825,826,827,828,829, 830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847, 848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865, 866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883, 884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901, 902,903,904,905,906,907,908,0,0,34359738368,-5210566582833489576,NULL, NULL,0,34359738368,-5210565758199768552,NULL,NULL,0,34359738368, -5210564933566047528,NULL,NULL,0,34359738368,-5210564108932326504,NULL, NULL,0,34359738368,-5210563284298605480,NULL,NULL,0,34359738368, -5210562459664884456,NULL,NULL,158,159,160,161,162,(snip)
The server then faithfully executes the statement and returns the results expected for the query it reports to have received, which (in a table with actual data) is not what the Haskell program was expecting.
If I compile the program once, the results vary per run. If I don't use positional parameters but instead render the full SQL query myself, there is no problem.
Is this a problem in HDBC or in HDBC-mysql? What's going on here? How do I fix or work around this?
Many thanks,
Martijn.
I think that this should be fixed with the latest version of the driver (0.6.2). I was incorrectly squirreling away pointers that I'd acquired using "with" or "withCString". This worked -- sometimes. :) Please let me know if you see any other problems. thanks, chris On Apr 7, 2010, at 3:02 PM, Martijn van Steenbergen wrote:
Dear café (CC John and Chris),
I'm having some trouble with getting positional parameters in HDBC-mysql to work. Most of the time they work fine, but sometimes (and often enough that it's a serious bother) the parameters don't reach the server correctly.
Let me first describe my setup: * Ubuntu 8.10 intrepid (inside VMWare Fusion on Mac OS X Snow Leopard) * mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2 * ghc 6.10.3 * HDBC-2.2.4 * HDBC-mysql-0.6.1
I have a database with a single, empty table:
CREATE TABLE `foo` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1031 DEFAULT CHARSET=latin1
And I have the following Haskell program:
import Database.HDBC import Database.HDBC.MySQL import Data.List import Data.Traversable import Control.Monad
main :: IO () main = do conn <- connectMySQL defaultMySQLConnectInfo { mysqlUnixSocket = "/var/run/mysqld/mysqld.sock" , mysqlUser = "root" , mysqlDatabase = "db" } for [1..1000] $ \n -> do let sql = "select id from foo where id in (" ++ intercalate "," (replicate (fromInteger n) "?") ++ ")" stmt <- prepare conn sql execute stmt (map SqlInteger [1..n]) finish stmt return ()
The program produces no output. All 1000 queries that are sent to the database are expected to return 0 results, since table foo is empty. This is all fine.
The problem is in the queries that the server receives. If I turn on full logging, I expect all queries to have a "where id in (...)" where there is an increasing list of integers within the parentheses. But when examining the logs, some are small negative numbers, NULLs or just other numbers:
506 Execute [980] select id from foo where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26, 27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,824,825,826,827,828,829, 830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847, 848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865, 866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883, 884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901, 902,903,904,905,906,907,908,0,0,34359738368,-5210566582833489576,NULL, NULL,0,34359738368,-5210565758199768552,NULL,NULL,0,34359738368, -5210564933566047528,NULL,NULL,0,34359738368,-5210564108932326504,NULL, NULL,0,34359738368,-5210563284298605480,NULL,NULL,0,34359738368, -5210562459664884456,NULL,NULL,158,159,160,161,162,(snip)
The server then faithfully executes the statement and returns the results expected for the query it reports to have received, which (in a table with actual data) is not what the Haskell program was expecting.
If I compile the program once, the results vary per run. If I don't use positional parameters but instead render the full SQL query myself, there is no problem.
Is this a problem in HDBC or in HDBC-mysql? What's going on here? How do I fix or work around this?
Many thanks,
Martijn.
Thanks! That's great news. Yes, all seems fine now. :-) It was a very interesting bug to isolate. At one point I was in the situation where compiling with -O2 fixed the problem and -O0 didn't, seemingly consistently. By the way, I got two warnings while compiling: * Warning: Fields of `Types.Statement' not initialised: Types.executeRaw * Pattern match(es) are non-exhaustive in the definition of `bindOfSqlValue' Thank you again, Martijn. On 4/17/10 6:37, Chris Waterson wrote:
I think that this should be fixed with the latest version of the driver (0.6.2). I was incorrectly squirreling away pointers that I'd acquired using "with" or "withCString". This worked -- sometimes. :)
participants (3)
-
Chris Waterson -
John Goerzen -
Martijn van Steenbergen