Non informative error message when inserting in batch mode

[ofajardo]

When using the rest back end, if I insert (do an executemany) in batch=False and a problem arises, I get a very informative error. When I do the same in batch=True, the error is very vague. I tried the same connecting directly to the rest api and the error using batch is as good as no batch, therefore PyTD is not parsing it correctly. Tested on both windows and linux, python 3.6, and python 3.5

Would it be possible to fix the parsing of the error when batch=True? This would save development time, as otherwise I have to switch to batch=False, wait for the error to appear (slower) fix, and then go back to batch=True.

How to reproduce:

import teradata

udaExec = teradata.UdaExec (appName="HelloWorld", version="1.0",
        logConsole=False)

session = udaExec.connect(method="rest", system="systemanme", dbType="Teradata", host="hostname", 
                 protocol='http', 
                 webContext='/tdrest',
                 transactionMode='TERA', 
                 authentication='LDAP',
                 username="myusername", password="mypassword")

# the error here is that mytimestamp is of type TIMESTAMP and I will try to insert an int
sql = """
insert into somedatabase.sometable
(myid, mytimestamp, mystring, mydate, mybool, mytime, myfloat)
VALUES (?, ?, ?, ?, ?, ?, ?)
"""
somedata = [[1]*7 for x in range(100)]
# change to True to get the cryptic error message
session.executemany(sql, somedata, batch=False)

Error I get if batch = False:

Traceback (most recent call last):

  File "<ipython-input-2-0090cb7729b7>", line 1, in <module>
    session.executemany(sql, somedata, batch=False)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\udaexec.py", line 679, in executemany
    self.internalCursor.executemany(query, params, **kwargs)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\udaexec.py", line 754, in executemany
    self._execute(self.cursor.executemany, query, params, **kwargs)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\udaexec.py", line 790, in _execute
    func(query, params, **kwargs)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\tdrest.py", line 223, in executemany
    queryTimeout=queryTimeout))

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\tdrest.py", line 264, in _execute
    self.connection.system), options)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\tdrest.py", line 383, in post
    return self.send(uri, 'POST', data)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\tdrest.py", line 428, in send
    int(errorDetails['error']), errorDetails['message'])

DatabaseError: Invalid timestamp.

Error I get if batch=True

Traceback (most recent call last):

  File "<ipython-input-3-179c0a071d07>", line 1, in <module>
    session.executemany(sql, somedata, batch=True)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\udaexec.py", line 679, in executemany
    self.internalCursor.executemany(query, params, **kwargs)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\udaexec.py", line 754, in executemany
    self._execute(self.cursor.executemany, query, params, **kwargs)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\udaexec.py", line 790, in _execute
    func(query, params, **kwargs)

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\tdrest.py", line 223, in executemany
    queryTimeout=queryTimeout))

  File "C:\ProgramData\Anaconda3\lib\site-packages\teradata\tdrest.py", line 242, in _handleResults
    e.code, "Error reading JSON response: " + e.msg)

InterfaceError: Error reading JSON response: Expected queueDuration field but got message instead.

error from vanilla rest using batch False

Error Status Code: 420
{
  "message" : "Invalid timestamp.",
  "error" : "6760",
  "details" : "[Teradata Database] [TeraJDBC 15.10.00.09] [Error 6760] [SQLState HY000] Invalid timestamp."
}

error from vanilla rest using batch True

Error Status Code: 420
{
  "message" : "Invalid timestamp.",
  "error" : "-6760",
  "details" : "[Teradata JDBC Driver] [TeraJDBC 15.10.00.09] [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.[Teradata Database] [TeraJDBC 15.10.00.09] [Error 6760] [SQLState HY000] Invalid timestamp."
}

[Proteusiq]

The problem is as stated: invalid timestamp. :) Check out how you have defined your inputs and which order they are inserted.

E.g. timestamp values need to be a timestamp. I suspect this to be a table definition issue :)

[ofajardo]

Hi @Proteusiq

Thanks a lot for the quick answer =) . But I am afraid you did not understand the question ;) :

I know the error is I am trying to insert an integer when I should pass a timestamp, I did that on purpose to demonstrate the problem:

The problem is: if you do that on the executemany method, with batch= False, you get this error message:

DatabaseError: Invalid timestamp.

Very nice! very informative! It points exactly at the cause of the error.

Now, if you do with batch=True you get:

InterfaceError: Error reading JSON response: Expected queueDuration field but got message instead.

Not informative at all. You cannot understand what is the problem from that message.

Now, the rest API is giving a very similar informative error message in both cases, but PyTD is not parsing the second one correctly.

I would like that somebody corrects that, so that we get a nice informative error.

thanks!

[Proteusiq]

Ah! My bad, I did not understand your question. ;)

[ofajardo]

@proteusiq No prob! If you did not understand it means I did not explain clearly. Hopefully now it should be better. Thanks!

[sband]

i might have a fix for you but i need to test that. give me till tomorrow i will try to test and put the fix in here

[sband]

Apologies for a very late reply for this one, i had totally missed out on this one. I have put in a fix for this issue and raised a pull request for this with the master branch.

[escheie]

Looks like this issue is caused by TDREST returning HTTP status code 200 (OK) despite the batch insert failing.

I was able to prove this using curl:

curl -i -u xxxxxx:xxxxxx -H "Accept: application/vnd.com.teradata.rest-v1.0+json" -H "Content-Type: application/json" -XPOST http://sdlc4157:1080/tdrest/systems/xxxxxxxx/queries -d '{"format": "array", "batch": true, "params": [["0", "0", "0"], ["1", "1", "1"], ["2", "2", "2"], ["3", "3", "3"], ["4", "4", "4"], ["5", "5", "5"], ["6", "6", "6"], ["7", "7", "7"], ["8", "8", "8"], ["9", "9", "9"], ["10", "10", "10"], ["11", "11", "11"], ["12", "12", "12"], ["13", "13", "13"], ["14", "14", "14"], ["15", "15", "15"], ["16", "16", "16"], ["17", "17", "17"], ["18", "18", "18"], ["19", "19", "19"], ["20", "20", "20"], ["21", "21", "21"],["22", "22", "22"],["23", "23", "23"],["24", "24", "24"],["25", "25", "25"],["26", "26", "26"],["27", "27", "27"],["28", "28", "28"],["29", "29", "29"],["30", "30", "30"]], "rowLimit": 0, "query": "INSERT INTO xxxxx.testbatch VALUES (?, ?, ?)", "includeColumns": "true"}'

HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Cache-Control: public, no-store, max-age=0
Content-Type: application/vnd.com.teradata.rest-v1.0+json;charset=UTF-8
Transfer-Encoding: chunked
Date: Fri, 18 May 2018 01:11:14 GMT

{
  "message" : "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.",
  "error" : "1339",
  "details" : "[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata Database] [TeraJDBC 16.00.00.18] [Error 6760] [SQLState HY000] Invalid timestamp.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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.[Teradata JDBC Driver] [TeraJDBC 16.00.00.18] [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."
}

When the number of errors returned is large enough, TDREST responds with Transfer-Encoding: chunked instead of Content-Length. When this occurs, the HTTP status code is 200 instead of the expected 420. When PyTd sees status code 200, it expects to parse a successful response. Hence PyTd throws the parsing error.

I will report the issue to the TDREST dev team.

[escheie]

ECS-1861 opened to track the bug in TDREST (now called Query Service).

[Cornstar23]

If you are having this problem with MyBatis in Java, loop through next exceptions:

final int TERADATA_BATCH_ERROR_1 = 1338;
final int TERADATA_BATCH_ERROR_2 = 1339;

// Try to find the actual Teradata error within the batch exception
if (e instanceof BatchExecutorException) {
    BatchExecutorException bee = (BatchExecutorException) e;
    BatchUpdateException bue = bee.getBatchUpdateException();
    if (bue != null) {
        SQLException nextException = bue.getNextException();
        // Put a limit on the search to avoid infinite loops
        final int ERROR_DEPTH_LIMIT = columnValues.length;
        int depthCount = 0;
        // Recursively get the next exception when there is a PreparedStatement batch
        // exception.
        // Stop on the first non-batch exception.
        while (depthCount < ERROR_DEPTH_LIMIT) {
            if ((nextException.getErrorCode() == TERADATA_BATCH_ERROR_1
                    || nextException.getErrorCode() == TERADATA_BATCH_ERROR_2)
                    && nextException.getNextException() != null) {
                nextException = nextException.getNextException();
            } else {
                depthCount = ERROR_DEPTH_LIMIT;
            }
            depthCount++;
        }
        errorMessage = nextException.getMessage();
    }
}