mysql2: how to actually use native prepared statements? #1832
prettycoder
started this conversation in
General
Replies: 1 comment 2 replies
-
Thanks for posting about this. I think this is a bug. Looks like native prepared statements didn't work correctly in the mysql2 adapter. This wasn't caught because everything still works, it just results in lost performance. Can you try the patch below? diff --git a/lib/sequel/adapters/mysql2.rb b/lib/sequel/adapters/mysql2.rb
index 681ce7930..187f6cc16 100644
--- a/lib/sequel/adapters/mysql2.rb
+++ b/lib/sequel/adapters/mysql2.rb
@@ -86,20 +86,26 @@ module Sequel
if NativePreparedStatements
# Use a native mysql2 prepared statement to implement prepared statements.
def execute_prepared_statement(ps_name, opts, &block)
- ps = prepared_statement(ps_name)
+ if ps_name.is_a?(Sequel::Dataset::ArgumentMapper)
+ ps = ps_name
+ ps_name = ps.prepared_statement_name
+ else
+ ps = prepared_statement(ps_name)
+ end
sql = ps.prepared_sql
synchronize(opts[:server]) do |conn|
stmt, ps_sql = conn.prepared_statements[ps_name]
unless ps_sql == sql
stmt.close if stmt
- stmt = log_connection_yield(conn, "Preparing #{ps_name}: #{sql}"){conn.prepare(sql)}
+ stmt = log_connection_yield("Preparing #{ps_name}: #{sql}", conn){conn.prepare(sql)}
conn.prepared_statements[ps_name] = [stmt, sql]
end
- if ps.log_sql
- opts = Hash[opts]
- opts = opts[:log_sql] = " (#{sql})"
+ opts = Hash[opts]
+ opts[:sql] = "Executing #{ps_name || sql}"
+ if ps_name && ps.log_sql
+ opts[:log_sql] = " (#{sql})"
end
_execute(conn, stmt, opts, &block)
@@ -120,6 +126,7 @@ module Sequel
case sql
when ::Mysql2::Statement
stmt = sql
+ sql = opts[:sql] || ''
when Dataset
sql = sql.sql
close_stmt = true
diff --git a/lib/sequel/adapters/utils/mysql_mysql2.rb b/lib/sequel/adapters/utils/mysql_mysql2.rb
index acaad1b46..d2c4a255f 100644
--- a/lib/sequel/adapters/utils/mysql_mysql2.rb
+++ b/lib/sequel/adapters/utils/mysql_mysql2.rb
@@ -34,7 +34,7 @@ module Sequel
def execute(sql, opts=OPTS, &block)
if opts[:sproc]
call_sproc(sql, opts, &block)
- elsif sql.is_a?(Symbol)
+ elsif sql.is_a?(Symbol) || sql.is_a?(Sequel::Dataset::ArgumentMapper)
execute_prepared_statement(sql, opts, &block)
else
synchronize(opts[:server]){|conn| _execute(conn, sql, opts, &block)}
That passes the mysql adapter tests using the mysql and mysql2 adapters, and from adding some debugging code, looks like it is reusing the same Mysql2::Statement object. |
Beta Was this translation helpful? Give feedback.
2 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
I'm trying to use native prepared statements with mysql2, but it is very tricky and I am wondering if this is intended?
My observations:
sequel/lib/sequel/dataset/prepared_statements.rb
Line 357 in 9e73e78
To reproduce:
I adapted a test in prepared_statement_test.rb as follows and ran it with a mysql db
this is what I see in the global_log,
I only expected 1 PREPARE command for this. Is this the correct behavior and if so why?
Is #execute the best way to actually call native statements or am I missing something?
Beta Was this translation helpful? Give feedback.
All reactions