SQL 2.10.03 problem: cannot UPDATE table
Posted in 1992
A user wants to delete a row from a table. The query:
delete from ft
where entry_name = "P90589";
dies with:
263: Could not lock row for UPDATE.
There is no possibility the table or row is being used by anyone
else. A number of other rows have been deleted from the same table
without incident. We've tried emptying the lock file. No luck.
Just for kicks, I created a new table, ft_new (using ft's dbschema output)
and got this when I tried to fill it:
insert into ft_new
select * from ft;
534: Could not insert new row into the table, table is locked.
107: C-ISAM error: record is locked.
(BTW, the manual says to "see C-ISAM error", but there's no explanation
of C-ISAM error messages. Do we need to buy a C-ISAM manual?)
I can select * from ft with no problem.
Now it gets fun. I run bcheck by doing a CHECK TABLE: (bcheck -n)
BCHECK C-ISAM B-tree Checker version 1.07
Copyright (C) 1981-1988 Informix Software, Inc.
Software Serial Number RDS#R537699
C-ISAM File: /eng/db/ifxdb/derwent.dbs/ft_____110
Checking dictionary and file sizes.
Index file node size = 1024
Current C-ISAM index file node size = 1024
Checking data file records.
Checking indexes and key descriptions.
Index 1 = unique key
0 index node(s) used -- 1 index b-tree level(s) used
Index 2 = duplicates (0,10,0)
1392 index node(s) used -- 3 index b-tree level(s) used
Checking data record and index node free lists.
ERROR: 8362 missing data record pointer(s)
Fix data record free list ? no
1399 index node(s) used, 1 free -- 133161 data record(s) used, 9448 free
Now, I'm no bcheck expert, but those 8362 missing data record pointers
sure look like a big clue to me.
So I try to run REPAIR TABLE: (bcheck -y)
<banner deleted>
C-ISAM File: /eng/db/ifxdb/derwent.dbs/ft_____110
ERROR: cannot open C-ISAM file
bcheck without any options gets the same results.
I'm stumped. I did get an ASCII dump from the table and I have the
schema, so I can recreate the table using dbload and judicious editing
and substitute it for the original ft, but I would really like to fix
the source table.
Any hints?
Thanks for reading this far.
Charleen