[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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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

[phil974]

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.

[Anonymous]

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

[Anonymous]

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.