[Haskell-cafe] Re: Weird behaviour with positional parameters in HDBC-mysql

[Chris Waterson]

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.