sqlcli: strange behaviour, why are no rows deleted ?

[Former Member]

Hello,

currently we're facing a strange situation while running a job for removing old data from one table.

This job is a shell script with embedded sqlcli calls, like

...

$SQLCLI -i remove_data_monthly.txt -o sqlcli_output.txt -f -d dbname -T maxdb_remove_data/trace.txt -U userkey

...

The input script "remove_data_monthly.txt" includes:

""

select count(id) from tab1 where datetime < '2010-11-01 00:00:00' and id > 0 and id not in (select id from tab2)

//

delete from tab1 where datetime < '2010-11-01 00:00:00' and id > 0 and id not in (select id from tab2)

//

commit

""

Now the strange thing happens:

The select returns a count of "1065023", but the delete statement doesn't delete this number of entries. On the first run it deleted about 6000 entries, and now every further run deletes nothing (while the count returns the same number of entries any time)...?!?!?!

Anyone an idea how can this happen ?

any help appreciated ......::GERD:::...

-

---

Here's the output copied from the trace file:

SQL COMMAND: 'select count(id) from tab1 where datetime < '2010-11-01 00:00:00' and id > 0 and id not in (select \

id from tab2)'

RESULT COUNT: 1

CURSOR NAME: 'SQLCURS_2'

::GET RESULT SET [0x692500]

CURSOR NAME: 'SQLCURS_2' [0x6A6780]

::FETCH NEXT 'SQLCURS_2' 2011-01-14 08:37:42.631079

::FETCH 'SQLCURS_2' 2011-01-14 08:37:42.631112

SQL COMMAND: 'FETCH FIRST "SQLCURS_2" INTO ?'

FETCH SIZE: 32767

FETCH BUFFER START: 1

FETCH BUFFER END : 1

::GETOBJECT 'SQLCURS_2'

COLUMN

I T AT L I D P

1 ASCII T 14 0x00007FFF5D985828 0x00000000006A6660 0x0000000000000000

DATA

I T AT L I DATA

1 ASCII T 14 7 '1065023'

::FETCH NEXT 'SQLCURS_2' 2011-01-14 08:37:42.631418

::PARSE 'SQLCURS_2' 2011-01-14 08:37:42.631489 [0x692500]

SQL COMMAND:'delete from tab1 where datetime < '2010-11-01 00:00:00' and id > 0 and id not in (select id from tab2)'

PARSE ID: E24D595B 00000601 0A000000 [1] not cached

::EXECUTE 'SQLCURS_2' 2011-01-14 08:37:42.632267 [0x692500]

PARSE ID: E24D595B 00000601 0A000000 [1] not cached

SQL COMMAND: 'delete from tab1 where datetime < '2010-11-01 00:00:00' and id > 0 and d not in (select id from tab2)'

RESULT COUNT: 1

::GET RESULT SET [0x692500]

CURSOR NAME: 'SQLCURS_2' [0x(null)]

[lbreddemann]

Hi there,

any way for you to provide a backup of the data so that we can reproduce this?

regards,

Lars

[Former Member]

Hi Lars,

thanks for your reply and sorry for the delay...

There is a way to reproduce it. We created a fullbackup of the db and restored it on a different server. The script/the database behaves the same way as the original. Regrettably, there's no way to send you a backup, since it contains confidential customer data.

Currently there's only one workaround for this problem. I replaced the script with a new one, which selects all the ids of the tuples to be deleted. Afterwards the tuples are deleted in blocks of 1000's. This is still working but faaaaaaar slower, of course.

Is it possible that there is some kind of "internal overflow" if the sub-select in the original delete statement returns too many records ?

br....GERD....

[lbreddemann]

HI Gerd,

hmm... I never came across such a situation, but many things are possible....

To be honest: I don't even have a 7.5 installation anymore.

As soon as I find some free time, I'll check whether I can reproduce it on 7.6/7.7 or 7.8 but this might take a while until then...

Sorry, I cannot give a better reply this time.

regards,

Lars

[Former Member]

I forgot to mention that we're running a pretty old MaxDB 7.5.0.38