Parent job's subjob iterated twice error --- Multiple primary key defined Error

[Anonymous]

Using the job design below, I copy tables from DB1 to DB2 in child job and call it to the parent job using tRunJob component.

The parent job loads keys from DB1 and insert into DB2.

However, the subjob 'Inject Primary Key' iterated/looped twice(or more?) in the parent job, which causes an error.

Any ideas how I could stop the second iteration?

Parent Job

Parent's job design

Here's the log and error message

[statistics] connected
Table Name = tbl_inq
Table Name = tbl_mem
traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem 
 AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);

traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq 
 AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);

traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem 
 AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
||||
traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq 
 AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
||||
Exception in component tDBRow_1 (test03)
java.sql.SQLSyntaxErrorException: Multiple primary key defined
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at local_test.test03_0_1.test03.tDBRow_1Process(test03.java:5641)
	at local_test.test03_0_1.test03.tHashInput_1Process(test03.java:5218)
	at local_test.test03_0_1.test03.tDBInput_1Process(test03.java:2433)
	at local_test.test03_0_1.test03.tDBConnection_1Process(test03.java:1437)
	at local_test.test03_0_1.test03.tRunJob_1Process(test03.java:13091)
	at local_test.test03_0_1.test03.runJobInTOS(test03.java:13552)
	at local_test.test03_0_1.test03.main(test03.java:13337)
[FATAL]: local_test.test03_0_1.test03 - tDBRow_1 Multiple primary key defined
[statistics] disconnected

Child's job (generate tables)

dbinput's component

Please let me know if anything is unclear.

Thank you

[manodwhb]

Check the below link to understand and alive the error.
https://www.sql-server-performance.com/mulitple-primary-key-contraints/

[Anonymous]

@manodwhb
I understand about one primary key constraint restriction.
My tables have one primary key so it should've work well.
However, the current job design is trying to insert the same primary key twice(or maybe more?) to the same table, which causes the error.

Do you have any advice on how I could stop the subjob 'Inject Primary Keys' from looping (iterating twice)?

[manodwhb]

Why it is getting created twice?

[Anonymous]

@manodwhb
Just now I tried adding tRunJob component and trigger SubJobOk tDBConnection_1 to another copy of parent job, and it works now.
the job design looks exactly the same, however the one with problem, has a '| | | | ' showed up as in the log below, whereas the one that work does not have '| | | |'.

[statistics] connected
traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem
AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);

||||
traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq
AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);

||||
[statistics] disconnected

Thanks alot

[manodwhb · Accepted solution]

try to analyse the one which is working and not working job, so that you will find the difference, I believe you may using tlogrow which is not getting any data from the source component.

[Anonymous]

@manodwhb
I tried removing tlogrow from the subjob and '| | | | ' disappear.
Thanks!!