All Forums Database
jliu 4 posts Joined 09/10
29 Sep 2010
errors "[Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000]"

when i use jdbc connection Teradata(12.0), i get errors "[TeraJDBC 13.00.00.16] [Error 5966] [SQLState HY000]"

my Teradata is 12.0 and jdbc version is 13.00.00.16.

in jdbc i ues addBatch() and executeBatch().

my record counts about 60000.

i use 3 or 2 or 1 thread insert these records into the same table,everying is ok.

but i use 4 thread insert these records into the same table(15000/thread) . i get flow exception:
aused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 13.00.00.16] [Error 1339] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. The parameter set was not executed and should be resubmitted individually using the PreparedStatement executeUpdate method.

thanks.

tomnolan 594 posts Joined 01/08
29 Sep 2010

I answered your question about Teradata Database error 5966 in the earlier thread. The answer is that you need to reduce the number of rows of data that your application binds per batch, and *** use more batches ***.

I did not expect you to use multiple threads. Attempting to insert rows into the same table with multiple threads is often counter-productive.

If you use multiple threads with a single connection, the inserts will be serialized, and you won't have gained anything.

If you use a separate connection for each thread (multiple connections, in other words) to insert into the same table, you are very likely to encounter rowhash lock contention, and possibly deadlock.

You application should look like the following:

for ( /* the number of batches */ )
{
for ( /* the number of rows per batch */ )
{
pstmt.setX(1, ...);
pstmt.setX(2, ...);
pstmt.setX(...);
pstmt.addBatch();
}
pstmt.executeBatch();
}

ekochne 15 posts Joined 09/13
30 Oct 2013

Hello,
Tomnolan,
I have the same problem. In my case I used 1000 records per batch and 15 threads.
After 25 million records was migrated - this exception began to appear more and more frequently.
I tried to reduce number of batch to 100 or increase number of batch to 10000 and reduce number of thread to 1 – it helped me for a short time.
But now I got this exception even for 50 records per batch.
So, the question – could it be some restrictions of scheme, session or connection (which was reinforced by DBA)?
Or because this table has 28 million records already.
and point me out on these settings/parameters.
Thanks,
Evgeny

ekochne 15 posts Joined 09/13
30 Oct 2013

So, I am not sure that pstmt.addBatch(); will help me, especially if I will have to add batch just with 1 records :)
 

ekochne 15 posts Joined 09/13
31 Oct 2013

Forgot mention exception:
Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.17] [Error 1339] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. The parameter set was not executed and should be resubmitted individually using the PreparedStatement executeUpdate method.
                at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:93) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:63) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.handleRunException(PreparedBatchStatementController.java:95) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:129) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:57) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:381) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:233) ~[terajdbc4.jar:14.10.00.17]
                ... 60 common frames omitted
 
Also I’ve found explanation of this exception at http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html
Table 19: BatchUpdateException Handling
“When the SQLException getErrorCode method returns a non-negative error code, the parameter set must be resubmitted individually using the PreparedStatement executeUpdate method.”
But there is not any explanation  a reason of this exception.
I'll very appreciate any help.

tomnolan 594 posts Joined 01/08
31 Oct 2013

>>> this exception began to appear more and more frequently
 
Which exception exactly?

ekochne 15 posts Joined 09/13
31 Oct 2013

Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.17] [Error 1339] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. The parameter set was not executed and should be resubmitted individually using the PreparedStatement executeUpdate method.
                at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:93) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:63) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.handleRunException(PreparedBatchStatementController.java:95) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:129) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:57) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:381) ~[terajdbc4.jar:14.10.00.17]
                at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:233) ~[terajdbc4.jar:14.10.00.17]
                ... 60 common frames omitted

ToddAWalter 316 posts Joined 10/11
31 Oct 2013

A SQL error such as uniqueness violation can get worse over the insert of more rows if there are duplicates in the source data. The first rows will all go in fine but as you get further along, more uniqueness violations will occur and need to be dealt with. Look to see what the underlying SQL error is that is causing the batch to be rejected.

tomnolan 594 posts Joined 01/08
31 Oct 2013

The Error 1339 is a generic error code that indicates a problem occured. Your application needs to walk the SQLException chain to obtain the root-cause exception. (This is what Todd Walter was suggesting when he said "Look to see what the underlying SQL error is".)
 
Change your application and add code similar to the following:

catch (BatchUpdateException exCaught)
{
    // walk the exception chain and print all chained exceptions
    for (SQLException ex = exCaught ; ex != null ; ex = ex.getNextException())
        ex.printStackTrace () ;

    // do whatever other exception handling your application needs
    // ...

 

ekochne 15 posts Joined 09/13
31 Oct 2013

 
Thanks, it is interesting explanation but I've created new empty table in ordetr to upload records to this temporary table and use "insert from select" after but anyway have the same exception for even first batch (1000 records). :(

ToddAWalter 316 posts Joined 10/11
31 Oct 2013

There can certainly be rows that are not unique per any uniqueness definition on the table within the first 1000 rows - or a constraint violation or a bad date or ... .  But until we know the SQL error, we are just guessing.

ekochne 15 posts Joined 09/13
31 Oct 2013

 
I thought that [Error 1339] [SQLState HY000] is initial code/message of exception.
I will try to figure out the initial exception but for the same data and the same code I got following well described exception (please see below) for duplication records. So I don't understand what could be changed to throw new one.
 
Caused by: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 14.10.00.17] [Error -2801] [SQLState 23000] Duplicate unique prime key error in SYWCOM_T.t_score_interactions.
    at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:300) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:119) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:321) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:202) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:123) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.statemachine.PreparedBatchStatementController.run(PreparedBatchStatementController.java:57) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:381) ~[terajdbc4.jar:14.10.00.17]
    at com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray(TDPreparedStatement.java:233) ~[terajdbc4.jar:14.10.00.17]
 
 

tomnolan 594 posts Joined 01/08
01 Nov 2013

You're making progress -- you have obtained a root-cause exception that represents a database error.
 
Errors 1338 and 1339 are both generic errors from the Teradata JDBC Driver that indicate a problem occurred with a PreparedStatement batch. An application needs to do some additional work to handle those errors.
Here is a link to the section in the Teradata JDBC Driver User Guide that explains what an application should do to handle BatchUpdateException:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#CCHJHFDI

k3na 19 posts Joined 09/14
03 Sep 2014

Hello,

 

I have a very similar problem, as described here.

The code that I look at was working on Oracle, and with Teradata I get errors with codes 1339 and 1338. 

The teradata version is 13.10.00.05, the teradata jdbc driver version is 14.00.00.37. The batch  size is 1000. Code is written in scala, I've tried to get more info with regard to sql errors as recommended by tomnolan:

 

try {
    insertStmt.executeBatch;
     } catch {
         case e: SQLException => {
         var e1 = e
         while (e1 != null) {
             e1.printStackTrace()
             errors = errors + ", " + e1.getErrorCode().toString()
             e1 = e1.getNextException()
         }
         println("Error codes:" + errors)
       }
}

After every batch, 1001 errors are printed: one with code 1338, others with 1339.

I've also tried to execute some of these inserts individually in SQL assistant, and it worked correctly.

 

Any ideas what could be wrong here and why I can't get more info using getNextException? 

 

Many thanks!

 

tomnolan 594 posts Joined 01/08
03 Sep 2014

>>> After every batch, 1001 errors are printed: one with code 1338, others with 1339.
 
My guess is that the first exception has error code 1338, and 999 of the following have code 1339, but one of the following has a different code -- the important code that indicates the database error.
 
Try changing your program to count up the number of occurrences of error 1339 but not print them, and print only exceptions with error code other than 1339.
 
Keep in mind that the solution that your application must implement is described in detail in the Teradata JDBC Driver User Guide:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#CCHJHFDI

k3na 19 posts Joined 09/14
04 Sep 2014

Thank you very much, you are completely right! one of them was 2644, which I've solved as described in admin guide

siddha.sumit 3 posts Joined 10/15
15 Oct 2015

Hello,
I am using "TeraJDBC 14.00.00.41" teradata driver but getting [Error 1338] with below exception chain : 

Exception Occured::java.sql.BatchUpdateException: 

[Teradata JDBC Driver] [TeraJDBC 14.00.00.41] 

[Error 1338] [SQLState HY000] A failure occurred while executing a PreparedStatement batch request. Details of the failure can be found in the exception chain that is accessible with getNextException.

[com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:147), com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeBatchUpdateException(ErrorFactory.java:136), 

com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatchDMLArray

(TDPreparedStatement.java:254), com.teradata.jdbc.jdbc_4.TDPreparedStatement.executeBatch(TDPreparedStatem, sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

 

Any idea about this above problem.

 

Thanks,

SJ

SJ

tomnolan 594 posts Joined 01/08
15 Oct 2015

Sumit, this is a duplicate post, and you should have posted this question as a new forum thread. Please do not "necro" old threads from 2 years ago.
 
The Teradata JDBC Driver User Guide documents how an application should handle a BatchUpdateException. Here is a link to the relevant section in the documentation:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#CCHJHFDI

siddha.sumit 3 posts Joined 10/15
15 Oct 2015

Ok from next time I will put in different thread.
Put rejected parameter into error file but what to do with this error file ?  those row insertion got failed due to these rejected parameter set.

SJ

tomnolan 594 posts Joined 01/08
16 Oct 2015

You must design your application to handle the error condition when a row, or rows, cannot be inserted. Logging the failed rows to a file is just a suggestion. If the data is important, then you must design a more comprehensive solution for your application.

You must sign in to leave a comment.