[resolved] Autoincrement key in MySQL

[Anonymous]

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

[phil974]

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

[Anonymous]

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

[janhess]

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

[phil974]

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

[Anonymous]

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...

[_AnonymousUser]

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