DataDeploy - MySQL, last_insert_id and JAVA sessions (conn)

OD 6.1.1
MySQL 5
Solaris

I've seen plenty of posts about auto/sequencing primary columns that need to be retrieved to populate a foreign key in another table. Most of the suggestions seem to be around using an External Data Source to retrieve the new/next value.

I'm a little stuck as to what to do with auto increment columns in MySQL.
In MySQL, you can specify a field to use auto increment which means an insert statement doesn't require a value for that field (it's left out). You then use the custom mysql function LAST_INSERT_ID() to retrieve the last auto increment value that was created to use in your inserts for your foreign keys in other tables. That function works against your connection/session to the database and table. So if I make a new connection to the database, LAST_INSERT_ID() will always be null as I didn't make an insert prior to the call in that session.

Now according to the doc's, the connection object in External Data Source is not the same object that DataDeploy is using (p154, 611.ddadmin) which makes me wonder if this will work as that might mean they are different sessions.

I haven't tried it yet - I would just like to know if anyone is using MySQL auto increment columns and how are they retrieving the LAST_INSERT_ID(). I thought it might be fairly common and hence I might be able to avoid some pit holes before I start. I saw this thread about do-sql-and-exec and wondered if I could use that somehow.

Also, while we’re at it – I’m also a little confused as to whether I need to specify a primary column attribute in the dd config for those auto increment columns as there is no value to specify.

Still thinking about the best method - any insights to MySQL and auto increment columns would be great. We have a decent sized database with about 50 tables - some of which are generic key/value map tables to extend other tables. There are plenty of foreign constraints set up. I wondering how difficult it will be to set up DataDeploy for this or whether I should just code it up in Java/Perl + SQL. Does this sound like a big ask?

I know I should just try this stuff out – I’d just like to hear any words of wisdom so that I can better grasp the concepts before scratching my head at error logs.

Thanks

Find more posts tagged with

Comments

Bill Klish

We are using the external data source calls within DataDeploy on MySQL 5 but not quite the same way you describe it.

Since MySQL doesn't support sequences like Oracle, we created a table to hold the sequence values for each table we need a sequence value for. Then we just increment that sequence value which enables the last_insert_id to work fine.

Johnny

It does support auto increment though, but I can't see how it can be used with DataDeploy. I was guessing that I wouldn't have been the first one to come accross this.

We will probably go with a custom sequence table like you described if we still continue to use DataDeploy.

Then we just increment that sequence value which enables the last_insert_id to work fine.

last_insert_id() wouldn't be applicable though right?

Someone from Interwoven mentioned (thread) that do-sql-and-exec would do the trick, but the docs seem to explain that it's for a different purpose and can't be run per insert.

Bill Klish

Confused, this works with last_insert_id using the approach I mentioned. The last_insert_id is needed to make sure you get the correct updated value in the sequence table. So, it does exactly what you need.

Perhaps I don't understand. We don't use auto-increment fields, we populate this id fields as I stated above.