Hi Ian,
RETURN_GENERATED_KEYS won't work because it's an UPDATE statement
doing the writing, not an INSERT (this isn't really a typical case).
Persistent connections may help. I'll give that a try.
This raises another question - if I'm modifying data in a stored
function, does Sequoia know to run the query against all back-ends?
If you are calling the stored function using a standard "{call ...}"
JDBC CallableStatement call, then Sequoia will broadcast it to all nodes
in mutual exclusion with all other queries (because Sequoia does not
know what data is going to be modified). So expect it to work but to be
slow. Sometimes, this can introduce deadlocks.
Sequoia 4 has the concept of semantic information that allows you to
describe what data is touched by a stored function but this is still a
feature under development.
Hope this helps,
Emmanuel
On Fri, Jan 9, 2009 at 12:40 PM, Emmanuel Cecchet
<[email protected] <mailto:[email protected]>> wrote:
Hi Ian,
Using last_insert_id() is a bad idea, you should use the JDBC 3
call Statement.execute(sql, RETURN_GENERATED_KEYS).
If you cannot modify your code and must use last_insert_id(), you
have to call it on the same connection than the one that was used
for the update (which does not work with a connection pool unless
this is done within a transaction). Sequoia has a workaround for
that called persistent connections that you can pass as an option
to the Sequoia driver. More info about that feature in the doc at
http://sequoia.continuent.org/doc/infocenter/topic/org.continuent.sequoia.doc/html/Sequoia_URL_options.html
Hope this helps,
Emmanuel
I thought I posted about this earlier, but I don't see the
message in the archives. I apologize if this is a duplicate.
Anyone have a solution for using MySQL sequences with Sequoia?
We're using fake MySQL sequences using the parameterized
last_insert_id() technique (described at
http://www.mysqlperformanceblog.com/2008/04/02/stored-function-to-generate-sequences/
among other places).
Using the stored function, we're seeing duplicate sequence
entries.
Executing the update/select statements outside of a function,
we're seeing duplicate sequence entries.
Using a stored procedure, we're not seeing duplicate sequence
entries, but performance is poor.
What's a guy to do?
Thanks,
Ian.
------------------------------------------------------------------------
_______________________________________________
Sequoia mailing list
[email protected]
<mailto:[email protected]>
https://forge.continuent.org/mailman/listinfo/sequoia
--
Emmanuel Cecchet
FTO @ Frog Thinker Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: [email protected] <mailto:[email protected]>
Skype: emmanuel_cecchet
_______________________________________________
Sequoia mailing list
[email protected]
<mailto:[email protected]>
https://forge.continuent.org/mailman/listinfo/sequoia
------------------------------------------------------------------------
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: [email protected]
Skype: emmanuel_cecchet
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
