error_class="Mysql2::Error" error="Commands out of sync; you can't run this command now"

[kuzuha]

I tried to write multiple query in fluentd's config like that.

key_names foo, bar
sql set @foo:=?, @bar:=?; insert into foo values(@foo); insert into bar values(@bar);

However, fluent-plugin-mysql rises error below.

2015-01-29 20:59:13 +0900 [warn]: temporarily failed to flush the buffer. next_retry=2015-01-29 20:59:14 +0900 error_class="Mysql2::Error" error="Commands out of sync; you can't run this command now" plugin_id="object:3fe259077120"
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/mysql2-cs-bind-0.0.6/lib/mysql2-cs-bind.rb:14:in `query'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/mysql2-cs-bind-0.0.6/lib/mysql2-cs-bind.rb:14:in `xquery'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluent-plugin-mysql-0.0.7/lib/fluent/plugin/out_mysql.rb:107:in `block in write'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluentd-0.12.4/lib/fluent/plugin/buf_memory.rb:61:in `feed_each'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluentd-0.12.4/lib/fluent/plugin/buf_memory.rb:61:in `msgpack_each'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluent-plugin-mysql-0.0.7/lib/fluent/plugin/out_mysql.rb:106:in `write'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluentd-0.12.4/lib/fluent/buffer.rb:325:in `write_chunk'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluentd-0.12.4/lib/fluent/buffer.rb:304:in `pop'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluentd-0.12.4/lib/fluent/output.rb:321:in `try_flush'
  2015-01-29 20:59:13 +0900 [warn]: /usr/local/lib/ruby/gems/2.2.0/gems/fluentd-0.12.4/lib/fluent/output.rb:140:in `run'

Those links say Mysql2::Client must call store_result method if query has multiple results. https://github.com/brianmario/mysql2#multiple-result-sets http://stackoverflow.com/questions/11204221/ruby-mysql2-multiple-statements-in-single-query

I wrote this patch. It seems works well.

--- out_mysql.rb    2015-01-29 22:18:32.000000000 +0900
+++ /usr/local/lib/ruby/gems/2.2.0/gems/fluent-plugin-mysql-0.0.7/lib/fluent/plugin/out_mysql.rb    2015-01-29 22:18:09.000000000 +0900
@@ -105,6 +105,9 @@
     handler = self.client
     chunk.msgpack_each { |tag, time, data|
       handler.xquery(@sql, data)
+      while handler.next_result
+        handler.store_result
+      end
     }
     handler.close
   end

[tagomoris]

This patch seems to bring many problems. What happen if first INSERT fails?

[kuzuha]

What happen if first INSERT fails?

Simply, handler.xquery rises Mysql2::Error. handler.next_result is never called.

However, I found abandon_results! method was implemented since Mysql2 0.3.12. It's seems match this use case, I think. https://github.com/brianmario/mysql2/blob/2622dc0edb309f950a5515dd84ecb8510af97aae/ext/mysql2/client.c#L609

[tagomoris]

Please send pull request to me with code and tests if you need this feature :)