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

[Chris Waterson]

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.

___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

[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.

___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Haskell-cafe] Re: hdbc-mysql on windows

[Chris Waterson]

Roderick, thanks for passing this along.  To be honest, I haven't tried to 
build this on Windows yet.  I have an old PC that I keep meaning to get things 
going on.  If you're able to get things working, I'd be more than happy to 
integrate any changes that you want to send along... patches are very much 
appreciated! :)

thanks!
chris

On Mar 18, 2010, at 1:46 PM, Roderick Ford wrote:

> Hello,
> In order to build (cabal install HDBC-mysql) the HDBC-mysql package, I am 
> having to make the following changes:
>  
> (1) I had to change the mysql (5.1.45 community distrib msi) to use a wrapper 
> exe on the mysql_config.pl and put this in c:\program files\mysql\bin since 
> although the c:\program files\mysql\scripts\, where mysql_config.pl resides, 
> was in the path, even the commandline call to it was not finding or using 
> it...very strange.  This of course doesn't directly have to do with the 
> HDBC-mysql package.  
> --The quick wrapper was:
> import System.Environment(getArgs)
> import System.Process(readProcessWithExitCode)
> import System.Exit(exitWith,ExitCode(ExitSuccess))
> main = do
>  args <- getArgs
>  (ecode,out,err) <- readProcessWithExitCode "c:\\Perl64\\bin\\perl.exe" 
> ("C:\\Program Files\\MySQL\\scripts\\mysql_config.pl" : args) ""
>  if ecode == ExitSuccess
>   then mapM_ putStrLn (lines out)
>   else mapM_ putStrLn (lines err)
> exitWith ecode
>  
> -- so now the output is: 
> "C:\Program Files\MySQL\lib\mysqlclient.lib" "wsock32.lib" "advapi32.lib" 
> "user3
> 2.lib"
> 
>  
> (2) Once that was done, the "cabal install HDBC-mysql" call was able to find 
> the mysql_config. However, it was splitting the values returned by 
> mysql_config on space, and thus failed to find mysql.h, as the resulting 
> command was:
>  
> C:\Program Files (x86)\Haskell Platform\2009.2.0.2\gcc.exe -c -BC:\
> Program Files (x86)\Haskell Platform\2009.2.0.2\gcc-lib -IC:\Program Files 
> (x86)
> \Haskell Platform\2009.2.0.2\include\mingw -D__GLASGOW_HASKELL__=610 
> -IIC:\Program -Iles\MySQL\include\mysql" ...
>  
> -- then I tried taking the -I out of the mysql_config.pl script and got the 
> following from the "cabal install HDBC-mysql"
> command was: C:\Program Files (x86)\Haskell Platform\2009.2.0.2\gcc.exe -c 
> -BC:\
> Program Files (x86)\Haskell Platform\2009.2.0.2\gcc-lib -IC:\Program Files 
> (x86)
> \Haskell Platform\2009.2.0.2\include\mingw -D__GLASGOW_HASKELL__=610 
> -I:\Program -Iles\MySQL\include\mysql" ... blah blah blah
> cabal: Error: some packages failed to install:
> HDBC-mysql-0.6.1 failed during the building phase. The exception was:
> exit: ExitFailure 1
>  
>  
> ... so finally I am downloading your package to see if I can figure out a fix 
> for windows boxes.  Just thought I would give you a heads up on this 
> windows-related bug.
>  
> Thanks,
> Roderick
>  

___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Re: [Haskell-cafe] Re: garbage collector woes

[Chris Waterson]

On Feb 19, 2009, at 3:41 PM, Don Stewart wrote:

Is the solution written up somewhere so we can point to that next  
time?

:)


Well, the applicability to the community at large is probably minimal,  
but my misadventure follows...


The MySQL C API has "statements" that are associated with a database  
"connection".  You connect to the database, and issue statements to  
query and manipulate it.  The statement encapsulates, basically, the  
state of iteration through a result set.


It turns out that a connection allows only one statement to be active  
at a time, and that "closing" any statement associated with a  
connection appears to close all other statements associated with that  
connection, too.  I wrap the MySQL "statement" in a ForeignPtr whose  
finalizer closes the statement.  Which, as it turns out, would close  
the *next* statement that I'd created on the connection as a side  
effect.  I was incorrectly interpreting


I tried to mitigate this surprising effect by 1) making sure that a  
statement gets finalized as soon as its result set is exhausted, and  
2) adding some warnings to the driver docs about this wonderful feature.


chris

___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Haskell-cafe] Re: garbage collector woes

[Chris Waterson]

On Feb 17, 2009, at 12:22 PM, Chris Waterson wrote:


I'm at wits end with respect to GHC's garbage collector and would very
much appreciate a code review of my MySQL driver for HDBC, which is
here:

 <http://www.maubi.net/~waterson/REPO/HDBC-mysql/Database/HDBC/MySQL/Connection.hsc 
>


In particular, the problem that I'm having is that my "statements"
(really, just iterators over a SQL query result set) are getting
garbage collected prematurely.


So (*blush*), my woes turned out to be my misunderstanding of the  
MySQL C API, which

I have now come to terms with.  I apologize for the noise here.

chris



___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Haskell-cafe] garbage collector woes

[Chris Waterson]

I'm at wits end with respect to GHC's garbage collector and would very
much appreciate a code review of my MySQL driver for HDBC, which is
here:

  


In particular, the problem that I'm having is that my "statements"
(really, just iterators over a SQL query result set) are getting
garbage collected prematurely.

I've included the source for the relevant functions below.  The first
is newStatement, which creates a new HDBC Statement record (an elided
version, below):

> newStatement :: ForeignPtr MYSQL -> String -> IO Types.Statement
> newStatement mysql__ query = withForeignPtr mysql__ $ \mysql_ -> do
>   stmt_ <- mysql_stmt_init mysql_
>   ...
>   stmt__ <- newForeignPtr mysql_stmt_close stmt_
>   ...
>   return $ Types.Statement
>  {
>  , Types.fetchRow   = fetchRow mysql__ stmt__ results
>  }

The mysql_stmt_init function is the MySQL C API that creates a new
statement record.  Its result must be freed by mysql_stmt_close, and
therefore is wrapped with a ForeignPtr appropriately.  The fetchRow
function is part of the HDBC Statement API, and its signature is:

> fetchRow :: IO (Maybe [SqlValue])

I.e., it retrieves the next row from the result set and returns the
column values as a list.  The "results" parameter is not relevant to
this discussion: it maintains the buffers used for storage of the
column values.

The next function of interest is my implementation of fetchRow, copied
in its entirety, below:

> fetchRow :: ForeignPtr MYSQL -> ForeignPtr MYSQL_STMT -> [MYSQL_BIND]
>-> IO (Maybe [Types.SqlValue])
> fetchRow mysql__ stmt__ results =
>   withForeignPtr mysql__ $ \_ ->
>   withForeignPtr stmt__ $ \stmt_ -> do
>   rv <- mysql_stmt_fetch stmt_
>   case rv of
> 0 -> row
> #{const MYSQL_DATA_TRUNCATED} -> row
> #{const MYSQL_NO_DATA}-> return Nothing
> _ -> statementError stmt_
> where row = mapM cellValue results >>= \cells -> return $ Just  
cells


It unwraps the ForeignPtrs and invokes the mysql_stmt_fetch C API with
the native MYSQL_STMT structure, causing the column values for the
next row to get bound into storage areas that we can convert to
Haskell values.

Typically, this code gets invoked in the generic HDBC layer by the
fetchAllRows routine, which looks like this:

> fetchAllRows :: Statement -> IO [[SqlValue]]
> fetchAllRows sth = unsafeInterleaveIO $
> do row <- fetchRow sth
>case row of
>  Nothing -> return []
>  Just x -> do remainder <- fetchAllRows sth
>   return (x : remainder)

The sth argument is simply an instance of the Statement record that
I've returned from low-level MySQL driver.

This all works, except...

The behavior that I'm seeing is that the ForeignPtr to the native
MYSQL_STMT struct has become unreachable after some number of
iterations of the above loop.  This causes mysql_stmt_close to be
invoked, and subsequent calls to mysql_stmt_fetch fail with an error.

Curiously, if I replace

>   stmt__ <- newForeignPtr mysql_stmt_close stmt_

with

>   stmt__ <- newForeignPtr_ stmt_

in my newStatement routine (i.e., so that mysql_stmt_close is never
called), then fetchAllRows will merrily fetch all the rows from my
statement handle.  The down side, of course, being that I'll leak
statement handles.

So, I'm completely puzzled as to how I can continue to extract values
from the apparently unreachable pointer!

Using System.Mem.Weak, I've verified that:

  * The Statement record that I return from newStatement becomes
unreachable.

  * The applied value of fetchRow becomes unreachable; e.g.:

  let r = fetchRow mysql__ stmt__ results
  addFinalizer r $ putStrLn "r is unreachable!"

I'm not sure how this can be, given that fetchAllRows appears to be
explicitly maintaining a reference to sth, which in turn ought to
reference my fetchRow application.

I've created a test program that directly invokes my driver code the
same way that the generic HDBC layer does, and have verified that the
lack of "-fno-cse" with unsafeInterleaveIO in the generic HDBC module
is not relevant.  I was similarly able to verify that this problem
occurs with "-O0", so it's not due to any particular GHC optimization.

Any thoughts on how to debug this would be greatly appreciated!

Thanks in advance,
chris

___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Re: [Haskell-cafe] MySQL and HDBC?

[Chris Waterson]

On Jan 25, 2009, at 4:25 AM, Yitzchak Gale wrote:


o Transactions - if a connection does not support transactions,
dbTransactionSupport should return False, and commit and rollback
should raise errors. (SqlError I suppose?) Not warnings.


You probably already know this, but...the problem is that the
"connection" doesn't really have anything to do with supporting
transactions in MySQL; the storage engine that backs a particular
table does.  A single connection can interact with tables that are
backed by a variety of different storage engines.  In fact, you can
mix tables from different storage engines in a single *statement*.

To strictly implement HDBC's semantics, I could report that a MySQL
connection doesn't support transactions, ever.  Internally, I'd turn
on auto-commit to force each statement to run in its own transaction,
always.  This is certainly correct, however, it severely limits the
utility of the driver IMO.

For now, I've opted to do (essentially) what the ODBC driver does with
MySQL connections.  Namely, I trust the programmer to use the driver
correctly, and fail silently if he misuses this privilege.  I don't
love this, but it seems to be the way MySQL is generally dealt with in
other languages.

It is possible for me to detect when a ROLLBACK has been issued
against a non-transactional table.  In this case, I could raise an
error; however, doing this would be different behavior than the ODBC
driver.  (Besides being too late for the programmer to do anything
about it!)  But perhaps this is preferable?


o Threading - see the section at the bottom of the Haddock page
for a nice specification of what the driver needs and does not need
to support.


Gah.  I don't know how I missed this.  Thanks for pointing it out.


Also -

o Non-ASCIIText - OK for now, but MySQL has significant
support for Unicode and encodings, so that should be on
the to-do list.


Agreed.

thanks!
chris

___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Re: [Haskell-cafe] MySQL and HDBC?

[Chris Waterson]

On Jan 23, 2009, at 1:23 AM, Duncan Coutts wrote:


On Fri, 2009-01-23 at 08:06 +, Sebastian Sylvan wrote:



2009/1/23 Galchin, Vasili 
   Hello,

"Real World Haskell" seems to say that the abstraction
   layer HDBC doesn't support MySQL. If so, in what sense doesn't
   HDBC support
   MySQL??

It doesn't have a MySQL  backend. However, it does have an ODBC
backend which should work fine with MySQL.


This was uploaded to hackage yesterday:

http://hackage.haskell.org/cgi-bin/hackage-scripts/package/HDBC-mysql-0.1

You might like to test it and give feedback to the author.


I would very much appreciate feedback.

I'm still working with John Goerzen (who has been extremely helpful)  
to iron our some details, see [1] for a list of issues that you should  
be aware of before you start doing anything too serious.


thanks!
chris

[1] http://www.maubi.net/~waterson/hacks/hdbc-mysql.html
___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Re: [Haskell-cafe] Haskell audio libraries & audio formats

[Chris Waterson]

On Aug 24, 2008, at 8:41 AM, Eric Kidd wrote:


Greetings, Haskell folks!

I'm working on a toy program for drum synthesis.  This has lead me to
explore the various sound-related libraries in Hackage.  Along the  
way,

I've learned several things:


Hey Erik, I've also created (minimal, for fun) bindings to libmad and  
OS/X CoreAudio.


The original post is here:

  http://www.haskell.org/pipermail/haskell-cafe/2008-March/040796.html

Here is a direct link to the source:

  http://maubi.net/~waterson/REPO/hmad

  http://maubi.net/~waterson/REPO/CoreAudio

I represented the samples using a lazily generated list of numbers,  
and it worked pretty well.


chris


___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

Re: [Haskell-cafe] sound synthesis

[Chris Waterson]

On May 2, 2008, at 2:58 AM, Thomas Girod wrote:

I remember an article ranting about how lazy evaluation would be  
great to do signal processing, but it was lacking real world example.


I did something similar a few weeks ago.  I used libmad to lazily  
decode an MP3 file and play it using OS/X's core audio.  Here's that  
post, with links to the libmad bindings (which might be useful for  
you, even if the CoreAudio isn't.)


chris


http://www.haskell.org/pipermail/haskell-cafe/2008-March/040796.html
___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe

[Haskell-cafe] libmad and os/x coreaudio wrappers

[Chris Waterson]

Hi there!  I've taken my first stab at writing some (admittedly
minimal) libraries for Haskell, and would love to get feedback on
them:

  * hmad: a wrapper for the libmad MP3 decoder.
  http://maubi.net/~waterson/REPO/hmad

  * CoreAudio: a wrapper for OS/X CoreAudio.
  http://maubi.net/~waterson/REPO/CoreAudio

(You should be able to "darcs get" the above links, if you want.)

I wrote the libmad wrapper to generate a "stream" (i.e., a lazy list)
of audio samples.  CoreAudio allows the input stream to be lazy, as
well.  So, here's a simple MP3 player:

> module Main where
>
> import Sound.CoreAudio
> import Codec.Audio.MP3.Mad
> import qualified Data.ByteString.Lazy as B
> import System
> import System.IO
>
> main :: IO ()
> main = do files <- getArgs
>   mapM_ playFile files
>
> playFile :: String -> IO ()
> playFile file =
> withBinaryFile file ReadMode $ \ inHandle ->
>do xs  <- B.hGetContents inHandle
>   samples <- decode xs
>   play samples

I do have a couple questions...

  * The CoreAudio library requires its users to be compiled with
"-threaded".  Is there a way to specify that in the Cabal file?

  * I wanted to be able to generate a variety of streams from libmad.
Besides stereo linear PCM data, it also seemed like it might be
worth-while to produce a stream of MP3 frame headers, the
unsynthesized frequency domain data, and so on.  I tried to
accomplish this with a the DecoderSink class, but I'm not sure I
succeeded.  Any thoughts here would be appreciated!

I hope someone else finds these useful.  The FFI was a joy to use once
I figured it out... :)

chris


___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe