[resolved] Autoincrement key in MySQL

One Star

[resolved] Autoincrement key in MySQL

Hello,
I have been unable to find a solution to my problem, so apologies up front if it is totally FAQ: How do I auto-increment the primary key in a MySQL table?
I am using:
TOS V3.1.3 r26090
Linux/Ubuntu 9.09
Generating Java
I am getting just aquianted with TOS and am trying out the introductory tutorial using some future data of mine. I want to import a tab-seperated file and add to a db. In the db I want to have a primary key, but it seems to dissapear... I suppose I can set the primary key to be auto incremented , but I have no idea where.
This is what I have done and a workaround:
I create the database:
mysql> CREATE TABLE testdb.extab (
-> intid INT NOT NULL AUTO_INCREMENT,
-> accession_num TINYBLOB COMMENT 'IMGT/LIGM-DB accession number(s)',
-> PRIMARY KEY (intid)
-> );
Query OK, 0 rows affected (0.29 sec)
mysql> SHOW COLUMNS FROM testdb.extab;
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| intid | int(11) | NO | PRI | NULL | auto_increment |
| accession_num | tinyblob | YES | | NULL | |
+---------------+----------+------+-----+---------+----------------+

In Talend I get the schema. The intid is shown as key (see snapshot 1). As is done in the tutorial, the old table is droped and a new created, so I do the too (snapshot 2). The mapping of the fields is set up, and the intid is left alone (Snapshot 3). Running the job results in an exception:
Starting job testimport at 21:26 29/08/2009.
connecting to socket on port 3890
connected
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:353)
at com.mysql.jdbc.Util.getInstance(Util.java:336)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1016)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2938)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1601)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2436)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402)
disconnected
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1694)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1608)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1593)
at test.testimport_0_1.testimport.tFileInputDelimited_1Process(testimport.java:1302)
at test.testimport_0_1.testimport.runJobInTOS(testimport.java:1587)
at test.testimport_0_1.testimport.main(testimport.java:1497)
Job testimport ended at 21:27 29/08/2009.

Because the 'auto_increment' has dissapeared somehow:
mysql> SHOW COLUMNS FROM testdb.extab;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| intid | int(10) | NO | PRI | NULL | |
| accession_num | tinyblob | YES | | NULL | |
+---------------+----------+------+-----+---------+-------+
2 rows in set (0.86 sec)

When adding 'Numeric.sequence("s1",1,1)' to the Expression of intid (shapshot5) everything works:
Starting job testimport at 21:31 29/08/2009.
connecting to socket on port 4284
connected
disconnected
Job testimport ended at 21:32 29/08/2009.

However, I am not sure how to get this to work when I add more rows to the table...
Any comments and hints are mostly welcome
Ulrik

Accepted Solutions
Community Manager

Re: [resolved] Autoincrement key in MySQL

Hello
My problem is that I don't know how or where to define a columns as auto_increment

To define a column as auto_increment, only when you create a table and declare one column as auto_increment. In talend, you don't need define a column as auto_increment.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: [resolved] Autoincrement key in MySQL

Hello guy
Duplicate entry '0' for key 'PRIMARY'

Because intid column is a primay key column, so the inserted value into this column should be unique. If you don't specify a value (as the third image), it will insert a default value(0 for int, null for string) into table. That's why you get the exception' Duplicate entry '0' for key 'PRIMARY' when the job insert the second record.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Autoincrement key in MySQL

Hi Shong,
Thanks for the reply.
How do I specify a unique value? 'Will Numeric.sequence("s1",1,1)' insert a unique value, or will it start at 1 every time? How can I then count the number or rows in the table and use that as start in 'Numeric.sequence'?
Also, why do the intid column change from:
| intid         | int(11)  | NO   | PRI | NULL    | auto_increment |

to:
| intid         | int(10)  | NO   | PRI | NULL    |       |

when running the job? The length of the INT has become shorter, and the 'auto_increment' is gone. All I did was retrieve the schema from the database.
Isn't there a flag to set for auto increment?
Thanks
Ulrik
Community Manager

Re: [resolved] Autoincrement key in MySQL

Hello
How do I specify a unique value? 'Will Numeric.sequence("s1",1,1)' insert a unique value, or will it start at 1 every time?

Yes, the method Numeric.sequence("s1",1,1) will generate a sequence digit for each row, here 's1' the sequnence name, the first 1 is start value, the second 1 is step.
How can I then count the number or rows in the table and use that as start in 'Numeric.sequence'?

If you define one column as auto_increment, you don't need to insert value into this column, it will auto be inserted a increase value by Mysql itself. So, don't add this column on the schema on TOS.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Autoincrement key in MySQL

Hi,
If you define one column as auto_increment, you don't need to insert value into this column

My problem is that I don't know how or where to define a columns as auto_increment, and that the auto_increment defined in my original table, seems to be eaten when I retrieve the schema.
Thanks
Ulrik
Community Manager

Re: [resolved] Autoincrement key in MySQL

Hello
My problem is that I don't know how or where to define a columns as auto_increment

To define a column as auto_increment, only when you create a table and declare one column as auto_increment. In talend, you don't need define a column as auto_increment.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Autoincrement key in MySQL

Hello Shong,
Thanks fro your replies.
I am still not sure how to declare a column as auto_increment or why this declaration disappears when I retrieve the schema from the database (and if this indeed is a bug).
My work around is to have:
Action on table: None
Action on data: Update or Insert
As this leaves my table alone, I am fine for now.
Cheers,
Ulrik
One Star

Re: [resolved] Autoincrement key in MySQL

Hi,
Is there a way to insert rows into a table with an auto increment id, without removing the id column ??
It's a pity, because you can't use the repository schema.
One Star

Re: [resolved] Autoincrement key in MySQL

Hi Phil,
For Mysql, set the id column to 0 prior to the insert.
Regards,
Rick

Re: [resolved] Autoincrement key in MySQL

You can insert into an auto-increment column by inserting NULL for the key of the table. Mysql will generate the new key and replace the NULL for you.
One Star

Re: [resolved] Autoincrement key in MySQL

In fact, i work with postgre.
When i put a null value, i got the following error message:
ERROR: null value in column "id" violates not-null
And with zero, i got this message:
ERROR: duplicate key value violates unique constraint
One Star

Re: [resolved] Autoincrement key in MySQL

Hi Phil,
Postgres obviously doesn't work the same way as Mysql.
Have you tried leaving the expression blank?
Regards,
Rick
One Star

Re: [resolved] Autoincrement key in MySQL

For MySQL you can leave the field blank as well. (3.1.4)
One Star

Re: [resolved] Autoincrement key in MySQL

yes, i tried blank value, but it doesn't work neither.
One Star

Re: [resolved] Autoincrement key in MySQL

Hey, so I did end up finding a solution to this that should be applicable regardless of database server.
Instead of bringing the identity column into the schema, simply leave it out. So, in the database table schema, remove any reference to the identity column; likewise, remove any mappings for that column. By doing this, Talend has no idea of the existence of that column, and so will not try to handle it on your behalf; rather, it will be left up to the database to auto-increment and populate the identity column.
I'm using a MS SQL server and ran into the exact same issues as others in this posting where Talend would 'guess' that column of the schema to be an 'INT IDENTITY' column, but then would not handle it correctly. By finally removing any reference to that column in my schemas and jobs, the database handles it just as you'd expect and want. Hope this helps some people out...
One Star

Re: [resolved] Autoincrement key in MySQL

Hey, so what if you want to create a table via schema and allow it to be able to have a auto increment Primary Key... in that case it won't be possible to leave that column.
Is there a way to allow to create a pk autnumber column in schema when creating schema in DB and after that specify that same schema must be used to load bulk data.

Scenario: blank server database.
1.) Talend connects to DB create new table with auto pk.
2.) Talend used same schema to load bulk data into that table
Would that be possible... reason why this is desireable is to have an adhoc table creation if table does not exist and load bulk data instantly