Ok I did some more research, and IMHO I still think this is something that could be fixed in the sequel mysql adapter.
According to this post... http://hawaii-wung.blogspot.com/2008/12/execute-mutli-statements-in-rubymysql.html Not only do you need to do set_server_option( Mysql::OPTION_MULTI_STATEMENTS_ON ) (which I am not sure how to do from sequel), but also you need to clear the results, even if there are not any results... http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html So I am not sure how I would do any of that from sequel. At a minimum I guess you need to document that DB.run() cannot run multiple statements on mysql. Thanks On May 27, 4:38 pm, Jim Morris <[email protected]> wrote: > Well that's not very good :) It works fine with postgresql, so any > workarounds would probably not be database agnostic. > > There is no reason for mysql to return that error, as INSERTS are not > supposed to return anything. > > Sequels handling of this error (which happens in a migration) is also > bad IMHO because it kills the connection the migration is not rolled > back, so the version is left at zero but all the tables are left > intact, leaving the migration in a bad state. > > I'm not a mysql expert I prefer postgresql (and now even more!), so > I'm not likely to be able to debug this for the mysql people. > > Is there a place I can report ruby-mysql bugs? > > Thanks > > On May 27, 4:28 pm, Jeremy Evans <[email protected]> wrote: > > > On May 27, 3:04 pm, Jim Morris <[email protected]> wrote: > > > > I have refined this to a simple repeatable test as shown below... > > > > Basically the first set of inserts runs, but the second fails, however > > > if I were to submit each line individually it will work, but this is a > > > test case, in the real world I am migrating an existing set of sql > > > commands which is why I need to run them raw this way. > > > > sequel version 3.11.0 > > > mysql 2.8.1 > > > ruby 1.8.6 (2008-08-11 patchlevel 287) [i686-linux] > > > > # test.rb > > > require 'rubygems' > > > require 'sequel' > > > require 'logger' > > > > # run 'DROP DATABASE IF EXISTS test1' > > > # run 'CREATE DATABASE test1' > > > > DB = Sequel.connect('mysql://r...@localhost/test1', :logger => > > > Logger.new(STDOUT)) > > > > DB.create_table :test1 do > > > primary_key :id > > > String :name > > > String :arg1 > > > String :arg2 > > > String :arg3 > > > end > > > > sql1= %q[ > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > ] > > > > print "running inserts first time" > > > DB.run(sql1) > > > print "first set of inserts done" > > > > sql2= %q[ > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > ] > > > > print "running inserts second time" > > > DB.run(sql2) > > > print "second set of inserts done" > > > > When run I get this error... > > > > > ruby test1.rb > > > > I, [2010-05-27T14:58:55.223230 #17067] INFO -- : (0.005202s) CREATE > > > TABLE `test1` (`id` integer PRIMARY KEY AUTO_INCREMENT, `name` > > > varchar(255), `arg1` varchar(255), `arg2` varchar(255), `arg3` > > > varchar(255)) > > > running inserts first timeI, [2010-05-27T14:58:55.223675 #17067] INFO > > > -- : (0.000297s) > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test', 'arg1', > > > 'arg2', 'arg3'); > > > > first set of inserts donerunning inserts second timeE, > > > [2010-05-27T14:58:55.223834 #17067] ERROR -- : Mysql::Error: Commands > > > out of sync; you can't run this command now: > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > INSERT INTO test1 (name, arg1, arg2, arg3) VALUES ('test2', 'arg1', > > > 'arg2', 'arg3'); > > > > /usr/local/lib/ruby/gems/1.8/gems/sequel-3.11.0/lib/sequel/adapters/ > > > mysql.rb:160:in `query': Mysql::Error: Commands out of sync; you can't > > > run this command now (Sequel::DatabaseDisconnectError) > > > from /usr/local/lib/ruby/gems/1.8/gems/sequel-3.11.0/lib/sequel/ > > > adapters/mysql.rb:160:in `_execute' > > > Sequel is operating as expected here. When MySQL tells you it gets a > > commands out of sync error, Sequel raises a DatabaseDisconnectError, > > which will cause the connection pool to disconnect the connection. A > > new connection will be created the next time one is needed. > > > So the problem isn't with Sequel, it's at a lower level. You need to > > figure out why ruby-mysql is giving you that error message, and fix or > > work around it. > > > Jeremy > > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
