Re: Single row update - assert failed
A user on IDS 7.30/Solaris 9 hit an assertion failure ("undo-rowalter error: rowsize exceeding space available", undo_rowalter failed) after a simple single-row update, forcing a shutdown; fast recovery also failed, so the instance was restored from tape. Replies pointed to outstanding in-place alters (IPAs) from a previous ALTER TABLE ADD column, gave corrected sysmaster SQL (plus oncheck -pT page-version checks and a Perl parser) to find them, and advised completing IPAs with a dummy update (table by table, with checkpoints). No definitive root-cause fix was found: the advice was to contact IBM Tech Support and upgrade from the outdated 7.30.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
MF wrote:
> Hi ,
>
> I'm using Informix 7.30 on a solaris 9 box and it recently crashed after
> a single row update with the following error:
>
> 14:35:58 fatal undo-rowalter error: rowsize(72) exceeding space> available(66) in orignal page
> 14:35:58 fatal undo-pgalter error: undo_rowalter() failed
> ...
> 14:35:59 Assert Failed: Page Check Error in undopgalter:undo_rowalter> failed
> 14:35:59 Who: Session(35, root@lsbprod, 0, 0)
> Thread(136, xchg_1.3, 0, 1)
> File: rsdebug.c Line: 943
> 14:35:59 Results: Possible inconsistencies in 'lsb:"".'
> 14:35:59 Action: Run 'oncheck -cD 2097203'
> 14:35:59 sh /opt/informix/etc/evidence.sh 2 0 /tmp/af.88de0e 35 0x0 136> 0x8a340880 1 0 0 0 0
> 14:35:59 See Also: /tmp/af.88de0e, shmem.88de0e.0
> 14:35:59 Error writing '/tmp/shmem.88de0e.0' errno = 22
> 14:35:59
> ------------------ End of assertion failure 0 ----------------->
> 14:35:59
> 14:35:59 Assert Failed: Dynamic Server must abort
> 14:35:59 Who: Session(35, root@lsbprod, 0, 0)
> Thread(136, xchg_1.3, 0, 1)
> File: called by ASF mt_affail Line: 0
> 14:35:59 Results: Fatal Internal Error requires system shutdown
> 14:35:59 Action: Restart OnLine
> 14:35:59 sh /opt/informix/etc/evidence.sh 2 0 /tmp/af.88de0e 35 0x0 136> 0x8a340880 1 0 0 0 0
> 14:35:59 See Also: /tmp/af.88de0e, shmem.88de0e.1
> 14:35:59 Error writing '/tmp/shmem.88de0e.1' errno = 22
> 14:35:59
> ------------------ End of assertion failure 1 ----------------->
> Unfortunately the system could not perform a fast recovery as it would
> crash when restoring the logical logs. As the system could not enter
> Quiescent mode there was no option to run oncheck so the system had to
> be completely restored from tape. Any ideas as to why this would have
> happened (is it just a random inconsistency that required oncheck) and
> what solution other than restore the instance from backup could I have
> followed.
>
> Thanks for any ideas
Contact IBM Informix Tech Support.
> PS - I know I need to upgrade
You do. You also need to get restarted, and IBM Informix Tech Support
is probably the quickest way to do so - maybe even the cheapest way.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
To find outstanding in-place alters, execute the following SQL statements:
set OPTCOMPIND to 0;
database sysmaster; set index to dirty read;
select pg_partnum + pg_pagenum - 1 partn
from syspaghdr, sysdbspaces a
where pg_partnum = 1048576 * a.dbsnum + 1
and pg_next != 0
into temp pp with no log;
select b.dbsname database, b.tabname table
from systabnames b, pp where path = partnum
sumGirl wrote in comp.databases.informix:
> To find outstanding in-place alters, execute the following SQL statements:
>
> set OPTCOMPIND to 0;
> database sysmaster;> set index to dirty read;
> select pg_partnum + pg_pagenum - 1 partn
> from syspaghdr, sysdbspaces a
> where pg_partnum = 1048576 * a.dbsnum + 1
> and pg_next != 0
> into temp pp with no log;
> select b.dbsname database, b.tabname table
> from systabnames b, pp where path = partnum
A verbatim quote from the manual (9.40 Migration Guide, ct1usna.pdf,
p3-52) - bugs and all.
export OPTCOMPIND=0 in the shell.
Then...
database sysmaster;
set isolation to dirty read; select p.pg_partnum + p.pg_pagenum - 1 partn
from syspaghdr p, sysdbspaces a
where p.pg_partnum = 1048576 * a.dbsnum + 1
and p.pg_next != 0
into temp pp with no log;
select b.dbsname database, b.owner, b.tabname table
from systabnames b, pp where pp.partn = b.partnum
index --> isolation
path --> partn
Add owner in case you have MODE ANSI databases.
That works, but can be pessimistic, telling you there are outstanding
alters when in fact they are complete. You need to validate which
tables really have outstanding alters by running 'oncheck -pT
dbase:owner.table' and viewing the information (lost in amongst a lot
more information) which identifies page versions.
You're looking for lines with all pages on the most recent version.
You might see some lines like:
3 (oldest) 23
4 6
5 (newest) 345
This indicates there were two in-place alters, and that there are 23
pages at the oldest version (completely unchanged), 6 which were
changed after the first alter but not after the second, and 345 fully
up to date. This table needs fixing.
You might also find lines like:
3 (oldest) 0
4 0
5 (newest) 374
This table fragment has no outstanding IPAs, but the query above would
list it still. After some time, the extra rows vanish. I do not know
what triggers the information to change. (If you know tell me - it'll
save me some angst.)
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Jonathan Leffler <jleffler@earthlink.net> wrote in
news:41708CEA.10508@earthlink.net:
>> To find outstanding in-place alters, execute the following SQL
>> statements:
>>
>> set OPTCOMPIND to 0;
>> database sysmaster;>> set index to dirty read;
>> select pg_partnum + pg_pagenum - 1 partn
>> from syspaghdr, sysdbspaces a
>> where pg_partnum = 1048576 * a.dbsnum + 1
>> and pg_next != 0
>> into temp pp with no log;
>> select b.dbsname database, b.tabname table
>> from systabnames b, pp where path = partnum
>
> You might see some lines like:
>
> 3 (oldest) 23
> 4 6
> 5 (newest) 345
>
> This indicates there were two in-place alters, and that there are 23
> pages at the oldest version (completely unchanged), 6 which were
> changed after the first alter but not after the second, and 345 fully
> up to date. This table needs fixing.
Okay, thanks for the advice. Firstly - I've reinitialize my Instance and
reloaded the databases from a dbexport - everything appears fine except
now I'm scared to touch anything. I presume that the fresh load of the
tables has cleaned up all of my IPA's.
Luckily I have a copy of this instance on my test server and on running
the sql provided I've identified 26 IPA's. Most of these contain values
in the 3 rows suggested (Oldest, blank, and newest). Now I know these
have arisen due to an alter table statement which added a new column to
that table.... naturally any row updated after the alter table is in the
"newest" whereas rows that have not been updated are in the "oldest"
(pages).
As adding columns to a table is something I still want to do should I:
- use alter table then do a dummy update on all rows to force them
into the newest pages? ie
update tablex
set field1 = field1
where 1=1;
Do I risk experiencing the same type of crash? All I was
doing in the original crash was:
update tablex
set field1 = "ABC"
where field1 = "123" - single row update, nothing
fancy.
Or should I never use alter table again... instead - unloading the
table, dropping it, creating the new table, reloading all the data,
recreating indexes and constraints.
Thanks
Mark
MF wrote:
> Jonathan Leffler <jleffler@earthlink.net> wrote:
>>>To find outstanding in-place alters, execute the following SQL
>>>statements:
>>>
>>> set OPTCOMPIND to 0;
export OPTCOMPIND=0
>>> database sysmaster;
>>> set isolation to dirty read;>>> select pg_partnum + pg_pagenum - 1 partn
>>> from syspaghdr, sysdbspaces a
>>> where pg_partnum = 1048576 * a.dbsnum + 1
>>> and pg_next != 0
>>> into temp pp with no log;
>>> select b.dbsname database, b.tabname table
>>> from systabnames b, pp where partn = partnum
SQL syntax fixed.
>>You might see some lines like:
>>
>> 3 (oldest) 23
>> 4 6
>> 5 (newest) 345
>>
>>This indicates there were two in-place alters, and that there are 23
>>pages at the oldest version (completely unchanged), 6 which were
>>changed after the first alter but not after the second, and 345 fully
>>up to date. This table needs fixing.
>
> Okay, thanks for the advice. Firstly - I've reinitialize my Instance and
> reloaded the databases from a dbexport - everything appears fine except
> now I'm scared to touch anything. I presume that the fresh load of the
> tables has cleaned up all of my IPA's.
Yes. An IPA means that a table has been altered (via ALTER TABLE)
since it was built.
> Luckily I have a copy of this instance on my test server and on running
> the sql provided I've identified 26 IPA's. Most of these contain values
> in the 3 rows suggested (Oldest, blank, and newest). Now I know these
> have arisen due to an alter table statement which added a new column to
> that table.... naturally any row updated after the alter table is in the
> "newest" whereas rows that have not been updated are in the "oldest"
> (pages).
>
> As adding columns to a table is something I still want to do should I:
>
> - use alter table then do a dummy update on all rows to force them
> into the newest pages? ie
> update tablex
> set field1 = field1
> where 1=1;
IPAs which have not completed are really only an issue on reversion,
but you need to account for them before you upgrade so that you will
be able to revert easily, if it proves necessary. (There is probably
a secondary reason - if you are persistently selecting data that has
an IPA outstanding, then the DBMS has to persistently do the IPA
transformation on the fly, which is presumably more expensive than
simply reading the updated values.)
If you need to complete an IPA, then a dummy update is reasonable.
> Do I risk experiencing the same type of crash?
The $64 question - to which I cannot guess the answer. You should not
be getting a crash; period. I've gone back to your original post -
you're using IDS 7.30 (no more detailed version) on Solaris 9. That's
an interesting combination of retrograde and avant garde versions.
You should not still be using that version of IDS (as I said in a
previous post). You should be using 7.31.UD7 or later.
Anybody on 7.3x earlier than 7.31.xD7 is playing with fire and should
upgrade before getting burnt. (The burn is unrelated to this problem,
but is nevertheless a valid statement.)
Since your version is so archaic, it is hard to say, but yes, there is
a non-negligible chance you'll run into problems again.
> All I was doing in the original crash was:
> update tablex
> set field1 = "ABC"
> where field1 = "123" - single row update, nothing fancy.
That's not a dummy update - that's a real update. A dummy update
would be:
UPDATE tablex SET field1 = field1;
Note that if your table is fragmented by an expression, you may be
able to make your updates selective - by only updating the fragments
with outstanding IPAs.
The script below my signature block is a prototype that works on the
example outputs from 'oncheck -pT' that I've tested it against - a set
of outputs which is far from exhaustive (but includes some 9.50 output
with new features, as well as some 9.40 output). It might help you -
but I emphasize it is a prototype and not a finished product. (Sorry
about the line wrapping - it is pretty obvious how to fix it up, I
think.) It has not been tested on 9.40 fragmented tables; I expect it
will need fixing with a new or modified regex in place of:
elsif (/Table fragment partition (\\w+) in DBspace (\\w+)/)
> Or should I never use alter table again... instead - unloading the
> table, dropping it, creating the new table, reloading all the data,
> recreating indexes and constraints.
The intent behind IPA is to make ALTER TABLE more tractable. If you
have to abandon it, we have defeated ourselves. However, if the
problem is only in 7.30 and not in 7.31, let alone 9.40, then we would
claim that the problem is fixed - albeit not in the version you are using.
Specifically, if you have a terabyte of data in a table which you must
alter, it allows the original ALTER TABLE to complete almost
immediately, and the changes can take place over a period of months,
rather than hammering the logical logs etc and taking forever to
complete. In general, this is beneficial - right up to the point at
which you need to revert after upgrading.
So, no - you shouldn't abandon the use of ALTER TABLE. More
precisely, you should not need to abandon it.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Debugging code removed while posting - contact me if I've screwed up.
Tested on Perl 5.8.5 - should work on Perl 5.005_03 upwards, I think.
#!/bin/perl -w
#
# Extract outstanding IPA information from 'oncheck -pT' output
use strict;
# Array or hash for versions? Advantages and disadvantages to both.
my($dbase, $owner, $table, $partition, $dbspace, %versions);
my $stage = 0;
while (<>)
{
if (/^TBLspace Report for (\\w+):(\\w+)\\.(\\w+)/)
{
$stage = 1;
$dbase = $1;
$owner = $2;
$table = $3;
$partition = "";
$dbspace = "";
%versions = ();
}
elsif (/Table fragment partition (\\w+) in DBspace (\\w+)/)
{
# 9.50 fragmented table
$partition = $1;
$dbspace = $2;
$stage = 2;
%versions = ();
}
elsif (/Home Data Page Version Summary/ && $stage > 0)
{
$_ = <>; # Blank line
$_ = <>; # Version/Count header
$_ = <>; # Blank line
while (<>)
{
last if (/^\\s*$/);
my($version, $tag, $pages);
m/(\\d+)\\s+(\\((oldest|current)\\)\\s+)?(\\d+)/;
$version = $1;
$tag = $3;
$pages = $4;
$versions{$version} = $pages;
}
$stage = 0;
my($dbinfo) = ($partition && $dbspace) ? "- $partition
($dbspace) " : "";
my(@keys) =
> now I'm scared to touch anything. I presume that the fresh load of the
> tables has cleaned up all of my IPA's.
Yes!!
> As adding columns to a table is something I still want to do should I:
contact TS to get more info; maybe a certain addition may fail
best is to find the bugno and find which version it is fixed in!!
Then upgrade.
I sure would recomend to do the dummy update; (maybe unlogged and
lock the table exclusive).
if you have to do more then one table do them one by one and force
a checkpoint when a table is done.
It is no garantee that this will prevent problems in the future;
contact TS!!
See you
Superboer.
BTW maybe the version 0...thing disappeares after a checkpoint dono;
haven't tryed this though!!! and i did not realize Jonathan can be afraid
of something.....(grin).
Last remark: the qry supplied: make damn sure it does not do a seq scan
on syspaghdr this will read every page in your instance;
dependent on how big it is; it may give you grey hair...
So use an optimizer hint to avoid that!!
MF <aussiewoof@aol.com> wrote in message news:<Xns95867E2BE3BF1aussiewoofaolcom@140.99.99.130>...
> Jonathan Leffler <jleffler@earthlink.net> wrote in
> news:41708CEA.10508@earthlink.net:
>
> >> To find outstanding in-place alters, execute the following SQL
> >> statements:
> >>
> >> set OPTCOMPIND to 0;
> >> database sysmaster;> >> set index to dirty read;
> >> select pg_partnum + pg_pagenum - 1 partn
> >> from syspaghdr, sysdbspaces a
> >> where pg_partnum = 1048576 * a.dbsnum + 1
> >> and pg_next != 0
> >> into temp pp with no log;
> >> select b.dbsname database, b.tabname table
> >> from systabnames b, pp where path = partnum
> >
> > You might see some lines like:
> >
> > 3 (oldest) 23
> > 4 6
> > 5 (newest) 345
> >
> > This indicates there were two in-place alters, and that there are 23
> > pages at the oldest version (completely unchanged), 6 which were
> > changed after the first alter but not after the second, and 345 fully
> > up to date. This table needs fixing.
>
>
> Okay, thanks for the advice. Firstly - I've reinitialize my Instance and
> reloaded the databases from a dbexport - everything appears fine except
> now I'm scared to touch anything. I presume that the fresh load of the
> tables has cleaned up all of my IPA's.
>
> Luckily I have a copy of this instance on my test server and on running
> the sql provided I've identified 26 IPA's. Most of these contain values
> in the 3 rows suggested (Oldest, blank, and newest). Now I know these
> have arisen due to an alter table statement which added a new column to
> that table.... naturally any row updated after the alter table is in the
> "newest" whereas rows that have not been updated are in the "oldest"
> (pages).
>
> As adding columns to a table is something I still want to do should I:
>
> - use alter table then do a dummy update on all rows to force them
> into the newest pages? ie
> update tablex
> set field1 = field1
> where 1=1;
>
> Do I risk experiencing the same type of crash? All I was
> doing in the original crash was:
> update tablex
> set field1 = "ABC"
> where field1 = "123" - single row update, nothing
> fancy.
>
> Or should I never use alter table again... instead - unloading the
> table, dropping it, creating the new table, reloading all the data,
> recreating indexes and constraints.
>
> Thanks
> Mark