Expert: Jean-Pierre Zuate - 1/31/2008

Question
Hello again.

I have a present problem which i cannot solve.
I have a simple update command but will generate this error:

sqlstate=50000
[Ingres][Ingres 3.0 ODBC Driver]Error trying to replace a record.

I am updating only a single column without any reference.

I tried same update on a different database and it works.
I guess this is something to do with indexing.
Any insight regarding this issue.

Thanks in advance.

Cheers,
JB  

Answer
Hello JB,

This error is well knowed since Ingres 5.* and is part of a "familly" of sames error saying :
- error trying to get a record (case of SELECT)
- error trying to put a record (case of INSERT)
- error trying to replace a record (case of UPDATE)

It is often due to one of the following :
- table structure corruption
- index corruption
- no left space on device (linked to your location)

It can never be due to SQL syntax itself. Corruption can happen due to Ingres bugs ...

What to do then ?
First, check errlog.log. You might find a clear message containing the name of the table and the complete path of the file linked to the table. Some errors before and after this message should confirm the structure corruption or the lake of space on the device.

If you need space : add some :-)

In the others cases :
1/ take a checkpoint (ckpdb) : it is the only supported method to have a backup of your file (if you want to investigate with Ingres support or so on)
1-bis/ If you do not want (or can not) ckpdb your database try to copydb your table. With a little chance you should be able to read a part or all of your table, that mean all your data are not completly lost
2/ Check you have somewhere the SQL to reconstruct table structure, indexes, views and all objects linked to this table (because you will prehap's need to recreate the table)

Then try :
MODIFY your_table TO heap
if it not work, try this (OS command) :
verifydb -mrun -sdbname your_db -udba -otable table_name
at the end of this operation your table should be in HEAP, all indexes dropped

Then you should "just" recreate indexes and modify the table again.

Hope this help you,
Regards.
Jean-Pierre