Binary Values unreadable - Database corrupt?

[former_member1062500]

Hi,

I'm experiencing a really weird problem here, I have a table with some varchar columns and two binary columns. What I do now, is to select data from it via Python with the following select statement:

select * from torder where customerid=1624 order by torderid desc

What happens is, that only for this customerid some binary data is missing, they contain an empty string. If I do the select like this:

select * from torder where customerid=1624 order by torderid

It does work, the data is there. With this, it works too:

select * from torder order by torderid desc

So it seems, the combination of "customerid=1624" and "desc" somehow corrupts the results.

What's furthermore interesting is, that when I delete the last inserted row of the result, it works, and if I add a new one, it's broken again.

I could not check if the error is Python-related or not, as I found no way to retrieve/display the binary data in sqlcli, as it displays only something like 0x8002637A6F70652E6931 , but I doubt that this has something to do with Python.

Any clues of how to fix this?

My database version is Kernel 7.6.06 Build 003-121-202-135 | X32/LINUX 7.6.06 Build 003-121-20

2-135

Previously, I had 7.6.03 and updated it to the above version, hoping that this would fix the problem, but it did not.

Best Regards,

Hermann Himmelbauer

[lbreddemann]

Hello Hermann,

please provide something we can work with.

• Table/Index-DDL

• Data-DML (insert statements)

• your query

• your python coding

Without that we can only guess ...

regards,

Lars

[former_member1062500]

Ok, first many thanks for your quick reply. I did not answer at first as the problem magically went away. But unfortunately the problem is back today.

<p/>

The DDL statements look the following, there are no indexes on the table:

<p/>

<pre>

CREATE TABLE torder (

torderid INTEGER NOT NULL DEFAULT SERIAL,

creation_date TIMESTAMP,

transfer_date TIMESTAMP,

signed_date TIMESTAMP,

signed_with VARCHAR(3) CHECK (signed_with in ('TAN', 'BKU', 'MAN') OR signed_with IS NULL),

revocation_date TIMESTAMP,

done_date TIMESTAMP,

formdata LONG BYTE NOT NULL,

trans_function VARCHAR(30) NOT NULL CHECK (trans_function in ('do_bank_transfer', 'do_bank_collection',

'do_cheque_transfer', 'do_cash', 'do_cashdraw')),

trans_type VARCHAR(30) NOT NULL CHECK (trans_type in ('national','sepa','international')),

applet_location VARCHAR(35),

errors_text VARCHAR(100),

errors LONG BYTE,

customerid INTEGER,

dbuserid INTEGER,

torder_periodicid INTEGER,

PRIMARY KEY (torderid),

FOREIGN KEY(customerid) REFERENCES kunde (kundeid),

FOREIGN KEY(dbuserid) REFERENCES dbuser (dbuserid),

FOREIGN KEY(torder_periodicid) REFERENCES torder_periodic (torder_periodicid)

)

</pre>

<p/>

The insert statements look the following (They are copied out of the SQLAlchemy SQL log), one can see that the binary values are inserted here:

<p/>

<pre>

INFO:sqlalchemy.engine.base.Engine.0x...d110:INSERT INTO torder (creation_date, transfer_date, signed_date, si

gned_with, revocation_date, done_date, formdata, trans_function, trans_type, applet_location, errors_text, err

ors, customerid, dbuserid, torder_periodicid) VALUES (now(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

INFO:sqlalchemy.engine.base.Engine.0x...d110:['20091027113506345604', None, None, None, None, '\x80\x02}q\x01(

U\x0bcharges_forq\x02U\x04bothq\x03U\x05dcodeq\x04U\x0588888q\x05U\x0ccurrency_isoq\x06U\x03EURq\x07U\x06valut

aq\x08K\x00U\x11foreign_acc_name1q\tU\x11Foreign Account 2q\nU\x0cacc_payer_noq\x0bX\t\x00\x00\x00999111110q\x

0cU\ttextlinesq\r]q\x0e(U\x0c1st transferq\x0fU\tMultilineq\x10U\x0bFor testingq\x11eU\x06amountq\x12cdecimal\

nDecimal\nq\x13U\x071000.00\x85Rq\x14U\x0cacc_benef_noq\x15X\t\x00\x00\x00888111110q\x16U\x11foreign_acc_name2

q\x17U\x0bfor Testingq\x18u.', 'do_bank_transfer', 'national', 'url:test_transfer', None, None, None, None, No

ne]

</pre>

<p/>

All rows are inserted with binary data, so it's never NULL and it's also never set to an empty string ('').

<p/>

But when reading back the rows, some binary values are an empty string.

<p/>

The following Python code illustrates the problem:

<pre>

import sapdb.dbapi

def true_false_result(result):

if result:

return 'Bug begins'

else:

return 'Bug ends'

def check_bincols(bdb, sqlcmd):

print "----

---

"

print "QUERY: %s" % sqlcmd

print "----

---

"

bdbc = bdb.cursor()

bdbe = bdbc.execute(sqlcmd)

bug_occured = False

stored_bin_result = False

while 1:

row = bdbe.fetchone()

if row is None:

break

1. Now check if the binary data results to an empty string,

1. This should never happen (= the database bug)

bin_result = (row[7]() == '')

if bin_result != stored_bin_result:

bug_occured = True

print "Toggle to %s at torderid %s" % (

true_false_result(bin_result),

row[0])

stored_bin_result = bin_result

if not bug_occured:

print "No Bug for this query"

bdbe.close()

bdbc.close()

#bdb.close()

if __name__ == '__main__':

bdb = sapdb.dbapi.connect('USER', 'PASS', 'DBNAME', 'LOCALHOST')

1. First try the original query, which results in a bug

sqlcmd = 'select * from torder where customerid=1624 order by torderid desc'

check_bincols(bdb, sqlcmd)

1. This query normally is bugfree

sqlcmd = 'select * from torder where customerid=1624 order by torderid'

check_bincols(bdb, sqlcmd)

1. This query has a bug, too

sqlcmd = 'select * from torder order by torderid desc'

check_bincols(bdb, sqlcmd)

1. But this one not

sqlcmd = 'select * from torder order by torderid'

check_bincols(bdb, sqlcmd)

1. This triggers the bug, too, which is curious as there's practically no ordering

sqlcmd = 'select * from torder where customerid=1624 order by customerid desc'

check_bincols(bdb, sqlcmd)

1. So, it seems that the bug occurs only when using "DESC" for descending

1. ordering.

</pre>

<p/>

The output of this program is:

<p/>

<pre>

-

---

QUERY: select * from torder where customerid=1624 order by torderid desc

-

---

Toggle to Bug begins at torderid 1355

Toggle to Bug ends at torderid 582

-

---

QUERY: select * from torder where customerid=1624 order by torderid

-

---

No Bug for this query

-

---

QUERY: select * from torder order by torderid desc

-

---

No Bug for this query

-

---

QUERY: select * from torder order by torderid

-

---

No Bug for this query

-

---

QUERY: select * from torder where customerid=1624 order by customerid desc

-

---

Toggle to Bug begins at torderid 1355

Toggle to Bug ends at torderid 582

</pre>

<p/>

So, it can be seen that the problem occurs ONLY when using descending ordering. Moreover it's interesting that the result for "select * from torder order by torderid desc" is sometimes buggy, sometimes not, which seems to be related if someone inserted some more rows or not. What's furthermore interesting is the last query, as there the order is applied to "customerid", which is the very same for every row, so there is no ordering and the bug occurs here, too.

<p/>

All this happens on my production instance. I have a testing environment, where I imported the very same data set (same database version etc.) and there is no such problem (for now), so it's quite complicated to nail down the problem further as I cannot easily disrupt the availability of the production instance.

<p/>

All I could do for now is not to use the "DESC" command and reorder the data in my application, but that is really suboptimal as I have to keep all results in memory.

<p/>

Any help is really appreciated!

<p/>

Best Regards,<BR>

Hermann Himmelbauer

<p>

Update: I tried the same query with the following code with C++ / SQLDBC (I modified one of the SQLDBC examples):

<p>

<pre>

/*

• First you have to include SQLDBC.h

*/

#include "SQLDBC.h"

#include <stdio.h>

typedef struct ConnectArgsT {

char * username;

char * password;

char * dbname;

char * host;

} ConnectArgsT;

static void parseArgs (ConnectArgsT * connectArgs, int argc, char **argv);

using namespace SQLDBC;

/*

• Let start your program with a main function

*/

int main(int argc, char *argv[])

{

ConnectArgsT connectArgs;

parseArgs (&connectArgs, argc, argv);

char errorText[200];

/*

• Every application has to initialize the SQLDBC library by getting a

• reference to the ClientRuntime and calling the SQLDBC_Environment constructor.

*/

SQLDBC_IRuntime *runtime;

runtime = SQLDBC::GetClientRuntime(errorText, sizeof(errorText));

if (!runtime) {

fprintf(stderr, "Getting instance of the ClientRuntime failed %s", errorText);

return (1);

}

SQLDBC_Environment env(runtime);

/*

• Create a new connection object and open a session to the database.

*/

SQLDBC_Connection *conn = env.createConnection();

SQLDBC_Retcode rc;

rc = conn->connect(connectArgs.host, connectArgs.dbname,

connectArgs.username, connectArgs.password);

if(SQLDBC_OK != rc) {

fprintf(stderr, "Connecting to the database failed %s", conn->error().getErrorText());

return (1);

}

printf("Sucessfull connected to %s as user %s\n",

connectArgs.dbname, connectArgs.username);

/*

• Create a new statment object and execute it.

*/

SQLDBC_Statement *stmt = conn->createStatement();

rc = stmt->execute("select * from torder where customerid=1624 order by torderid desc");

if(SQLDBC_OK != rc) {

fprintf(stderr, "Execution failed %s", stmt->error().getErrorText());

return (1);

}

/*

/*

• Check if the SQL command return a resultset and get a result set object.

*/

SQLDBC_ResultSet *result;

result = stmt->getResultSet();

if(!result) {

fprintf(stderr, "SQL command doesn't return a result set %s", stmt->error().getErrorText());

return (1);

}

/*

• Position the curors within the resultset by doing a fetch next call.

*/

while (1) {

rc = result->next();

if(SQLDBC_OK != rc) {

break;

//fprintf(stderr, "Error fetching data %s", stmt->error().getErrorText());

//return (1);

}

char szString[30];

char szString1[3000];

SQLDBC_Length ind;

/*

• Get a string value from the column.

*/

rc = result->getObject(1, SQLDBC_HOSTTYPE_ASCII, szString, &ind, sizeof(szString));

if(SQLDBC_OK != rc) {

fprintf(stderr, "Error getObject %s", stmt->error().getErrorText());

return (1);

}

rc = result->getObject(8, SQLDBC_HOSTTYPE_ASCII, szString1, &ind, sizeof(szString1));

if(SQLDBC_OK != rc) {

fprintf(stderr, "Error getObject %s", stmt->error().getErrorText());

return (1);

}

printf("%s %s\n", szString, szString1);

}

/*

• Finish your program with a returncode.

*/

return 0;

}

static void parseArgs (ConnectArgsT * connectArgs, int argc, char **argv)

{

/*

• setting defaults for demo database

*/

connectArgs->username = (char*)"USER";

connectArgs->password = (char*)"PASS";

connectArgs->dbname = (char*)"MYDB";

connectArgs->host = (char*)"localhost";

/*

• use values from command line

*/

if (argc > 4) {

connectArgs->host = argv [4];

}

if (argc > 3) {

connectArgs->dbname = argv [3];

}

if (argc > 2) {

connectArgs->password = argv [2];

}

if (argc > 1) {

connectArgs->username = argv [1];

}

}

</pre>

<p/>

This works!! So it seems, that the problem is related to the Python module, which is interesting, as adding "DESC" should not be any difference to it. I personally suspect that there are some memory leaks in the code, which result in this strange behavior.

<p/>

Any suggestions?<p/>

Best Regards<br>

Hermann Himmelbauer

Edited by: Hermann Himmelbauer on Oct 28, 2009 12:51 PM

Edited by: Hermann Himmelbauer on Oct 28, 2009 12:55 PM

[lbreddemann]

Hello Hermann,

I wasn't able to reproduce this with 7.6.06 Build 4 on my local machine.

As this build is not yet available on SDN, could you please check if the issue also occurs on 7.7?

regards,

Lars