#19 DG breaks the primaray key constraint for random generation

open
Xueyuan Peng
Bug (17)
5
2014-08-28
2011-10-16
Michael
No

Tested in IntegerGenerator.java
When you specify DG to generate random values for a field that is also a primary key, DG will not produce unique values to be inserted and causes the commit batch to fail. It is not documented whether random generation on primary key fields should be allowed or not. If it shouldn't be allowed, DG should print an error message when parsing and shut down gracefully.

Example table:
<table>
<attr name="name" value="inter.test"/>
<attr name="row_count" value="100"/>

<fields>
<field>
<attr name="name" value="neg"/>
<attr name="type" value="INTEGER"/>
<attr name="constraint" value="NOT NULL"/>
<attr name="pkey" value="true"/>

<data>
<attr name="type" value="integer"/>
<attr name="seq" value="false"/>
<attr name="loop" value="true"/>
<attr name="start_range" value="0"/>
<attr name="end_range" value="200"/>
</data>
</field>
</fields>
</table>

Output:
2076 [main] INFO com.ibm.dg.DataGenerator - Generating data for 'inter.test' table.
This table will have 100 row(s).
Threads: 4, with around 25 row(s) per thread.
Exception in thread "Thread-2" java.lang.RuntimeException: Thread failed to execute batch, stopping: SQLException 0
Code: -4229
SqlState: null
Error Message: [jcc][t4][102][10040][3.62.56] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
SQLException 1
Code: -803
SqlState: 23505
Error Message: Error for batch element #10: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;INTER.TEST, DRIVER=3.62.56
SQLException 2
Code: -803
SqlState: 23505
Error Message: Error for batch element #17: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;INTER.TEST, DRIVER=3.62.56

at com.ibm.dg.threads.TableThread.commitBatch(TableThread.java:331)
at com.ibm.dg.threads.TableThread.run(TableThread.java:251)
at java.lang.Thread.run(Thread.java:636)
Exception in thread "Thread-3" java.lang.RuntimeException: Thread failed to execute batch, stopping: SQLException 0
Code: -4229
SqlState: null
Error Message: [jcc][t4][102][10040][3.62.56] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
SQLException 1
Code: -803
SqlState: 23505
Error Message: Error for batch element #1: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;INTER.TEST, DRIVER=3.62.56

at com.ibm.dg.threads.TableThread.commitBatch(TableThread.java:331)
at com.ibm.dg.threads.TableThread.run(TableThread.java:251)
at java.lang.Thread.run(Thread.java:636)
Comm. (stmts) Comm. (size) Commit Rate (stmts/s) Commit Rate (size/s)
======
1 4.0 B -1 null
1 4.0 B -1 null
1 4.0 B -1 null
1 4.0 B -1 null
1 4.0 B -1 null

Discussion

  • Nikita
    Nikita
    2011-10-23

    Bear in mind that the solution should be generic (i.e. graceful exit whenever any of the generators fail, possibly with a specific reason).

    Also, one big thing to worry about when you are looking at this bug is that you should make sure that when you shutdown all of the threads exit correctly.

     
  • Nikita
    Nikita
    2011-10-23

    • assigned_to: nobody --> xueyuan0106
     
  • Xueyuan Peng
    Xueyuan Peng
    2011-11-16

    This bug is fixed by preventing users from using random generator to generate primary keys before threading.
    Shutting down all the threads and exiting gracefully will be handled in bug #3420549 "DG goes into an infinite loop when a commit batch fails".