One Star

[resolved] MySQL last insert id

Hi,
I try to use the tMysqlLastInsertId componant but I don't understand how to use it.
Description of the job I've made:
- Read a spreadsheet file
- Insert data into database
- Log id of inserted data
You can see a screenshot of my job in attachment.
The problem is that the autogenerated "Last_insert_id" column contains always the value "0" instead of the value of autoincrement.
Does anyone know how can I get the value of autoincrement ?
Thx in advance for your help.

  • Data Integration
18 REPLIES
Employee

Re: [resolved] MySQL last insert id

Hi,
first you seem to have an error on your tMySQLOutput component (see red cross).
Despite this, in the db table, make sure your id is set to autoincrement.

Re: [resolved] MySQL last insert id

Long-time lurker, first-time poster...
I'm having the same issue with virtually the same setup, though I don't have any compile problems in my job. In my tMySqlLastInsertId_1 component, I have tMySqlConnection_1 in my "Link with" field and I'm using a built-in schema. Any advice on this would be extremely helpful.
Community Manager

Re: [resolved] MySQL last insert id

Hello milou
Please see my screenshot.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] MySQL last insert id

Hello shong,
I try to reproduce your example.
See my screenshot.
I've just replace the tRowGenerator componant by a tFileInputExcel.
When I run the job, I see 2 problems:
- I have value 0 instead of null for last_insert_id column in console.
- If you look at the statistics, 20 rows have been inserted into database.
But only one row is displayed in console for last_insert_id...
Do you know why ?
Thx for your help.
Community Manager

Re: [resolved] MySQL last insert id

Hello
Can you show us your table structure? Is there a column defined as auto_increment in your talbe?
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] MySQL last insert id

Hi shong,
I've uploaded 2 screenshots:
- structure of my table (view from phpmyadmin)
- configuration of tMysqlOutput component (which insert value in my database)
You can see that my table has an autoincrement column as primary key.
Community Manager

Re: [resolved] MySQL last insert id

Hello milou
I have run my job again with a table created on phpmyadmin and it works fine.
Please send me your job via email.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] MySQL last insert id

Hi shong,
I sent you my job by email this morning.
One Star

Re: [resolved] MySQL last insert id

Hi shong,
I update my job and found what it doesn't work:
I must check option "use an existing connection" in the tMysqlOutput component
and select the good tMysqlConnection in drop down list.
So it works !
I get a not null last inserted id when I execute the job.
But if the job insert more than one row in my table, I only get the inserted id for the last row.
How can I get the id for each row ?
It is possible ?
Community Manager

Re: [resolved] MySQL last insert id

Hello
How can I get the id for each row ?
It is possible ?

Yes, we can get all the new inserted id. There is a global variable can be used to get the first new inserted id:
tMysqlOutput_1_NB_LINE: the total number of new inserted rows.
For more information, please see my screenshots.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] MySQL last insert id

Hi shong,
I will test your solution asap.
Many thanks for your help.
One Star

Re: [resolved] MySQL last insert id

Hi shong,
I'm very happy to say it works very well !!
But I've just changed a component in your example to make it working for me.
My table contains 50 lines and has been truncated sometimes.
The value of id for "autoincrement" column is different from the line number.
Example:
Line 1: id=8810
Line 2: id=8811
...
When I execute the job, I can see in output:
first insert id: 8820
last insert id: 8839
tsampleRow expression: 8820..8839
And no line are displayed in the tLogRow componant.
I known why: tSampleRow filters on line number, not on value.
So it will filter from line 8820 to line 8839.
And I want to filter from value 8820 to value 8839.
I've just replaced tSampleRow by tFilterRow and put this condition:
myAutoIncrementColumn >= context.firstInsertID and myAutoIncrementColumn <= context.lastInsertID
and I've the list of id previously inserted.
Thx for your help and time.
I hope we keep in touch ... :-)
One Star

Re: [resolved] MySQL last insert id

Hi together,
maybe I missed one point. But to get the "lastInsertedId" for each row you could just change the "onSubJobOk" and just continue in the data flow.
Bye
Volker
One Star

Re: [resolved] MySQL last insert id

This solution does not work in a concurrent access environment !
any other db client could have inserted any identity value in the meantime.
could it be possible to modify all sql output component so to gain access to the identity column in a extra schema field ?
One Star

Re: [resolved] MySQL last insert id

Hello,
I've got the same problem. When I try the example that illustrates tMysqlLastInsertId or your example, I only retrieve the same identifier 100 times. In my opinion, the job cannot retrieve the last identifier created by autoincrement when it's in the same transaction than insertion in Talend for Mysql, unlike the last_insert_id() of mysql wich works unitary.
.------------------------------.
| #1. tLogRow_2 |
+----------------+-------------+
| key | value |
+----------------+-------------+
| id | 0 |
| nom | Jean-Michel |
| last_insert_id | 0 |
+----------------+-------------+
.---------------------------------.
| #2. tLogRow_2 |
+----------------+----------------+
| key | value |
+----------------+----------------+
| id | 0 |
| nom | Jean-Micheline |
| last_insert_id | 0 |
+----------------+----------------+
last id inserted retrieve after commit:
14|Jean-Micheline
This problem seems to be pretty old:
http://www.talendforge.org/forum/viewtopic.php?id=463
One Star

Re: [resolved] MySQL last insert id

Hey guys. I have something similar going on and I think I found another solution to the problem. Let's say I was inserting 3 rows. After inserting said rows let's say their newly inserted IDs are 11, 12, and 13. Upon using logrow to see what was being retrieved by the lastinsertid component I would see the new insert ids 0, 11, and 12.
I fixed this problem by adding a "mysqlcommit" component between the output component and the lastinsertid component with the option to disconnect the session turned off. Upon making this change, the lastinsertids came back accurately. This really is NOT an optimal solution for the problem as I have to do a commit for the connection, which may not be optimal depending on what it is you're trying to achieve.
One Star

Re: [resolved] MySQL last insert id

Hi all,
I had the same issue (having the insert into a table, retrieve the id and use it in the rest of the treatment).
Instead of having to define a tConnection + tCommit + tLastInserteId + all subjobs + (...) , I've simply used a
Numeric.sequence in the tMap variable. I first retrieve the last index of the table I want to insert into and
add to this index the Numeric.sequence. The new index can be used therafter and works for each row (no
need to insert all and then retrieve all).
This might not solve eveybody's problem but hopefully it should help some.
:-)
One Star

Re: [resolved] MySQL last insert id

Hi all,
If you use this component with tMySqlOutput, verify that the Extend Insert check box
in the Advanced Settings tab is not selected. Extend Insert allows you to make a batch
insertion, however, if the check box is selected, only the ID of the last line in the last
batch will be returned.
I think this will help you people
Thank you