 | |
#1
 
| |||
| |||
|
ISAM error 151 on UNLOAD -
05-27-2011
, 10:00 AM
#2
| |||
| |||
|
|
I'm stuck trying to do DBA tasks because our DBA was laid off ... I'm getting the following error when I try to UNLOAD one of the tables inour IDS 11.50.FC6 database: * *244: Could not do a physical-order read to fetch next row. * * *151: ISAM error: Illegal value in varchar length field * This error is also causing dbexport to fail. *I ran oncheck -cd on the table and it "fixed" something, but I still can't unload the table or select some rows in the table. Is there a way to fix this? *Is the data lost? This table does not have any indexes or unique keys. *There are about 940,000 rows, and I can only unload about 866,000 of them. *The schema is: create table thetable * ( * * trx_serial serial not null , * * trx_type char(1) not null , * * trx_datetime datetime year to minute * * * * default current year to minute, * * trx_user varchar(20) * * * * default user, * * ac_job_id integer, * * request_number smallint, * * product_item_enm varchar(30), * * art_item_enm varchar(30), * * center_enm varchar(30,1), * * submit_datetime datetime year to minute, * * priority_enm varchar(30), * * ac_status_enm varchar(30), * * r_day_zero_rule_id integer, * * r_due_date_rule_id integer, * * completed_date date, * * artist varchar(20), * * instructions lvarchar(4095), * * prior_request_number smallint, * * prior_product_item varchar(30,1), * * derived_panel_enm varchar(30), * * derived_templateid integer, * * derived_prod_code char(2), * * derived_fold_enm varchar(30), * * derived_is_window smallint, * * derived_color_count smallint, * * derived_size_enm varchar(30), * * derived_points smallint, * * derived_day_zero date, * * derived_due_date date, * * derived_fof_date date, * * derived_is_late smallint, * * is_online_order smallint, * * is_canceled smallint, * * requester_mm_net_login varchar(20), * * orientation_enm varchar(30) * ); Thanks, Sean. |
lost, but if the table is not fragmented you should be able to use the
rowid concept to unload the rows passed the bad one.
So for example if you do select rowid, * from thetable you should get
output which would contain the rowid of the last good row the engine
could get. Now this rowid column will be in decimal format, but what
you can do is convert the number of hex. Then the 4 byte hex
representation of the rowid has the following meaning:
0xLLLLLLSS where 0xLLLLLL is the logical page address of the table,
and SS is the physical slot on the data page. (note this is both why a
single table fragment is limited to 0xffffff pages, and why you can
have a max of 255 rows even if they are really tiny rows or really big
page sizes)
So once you have the last good rowid, the bad rowid will either be +1
if the next row is on that same page, or it will be on the next page
so the bad rowid would then be 0xLLLLLL(+1)01 (so the 1st slot on the
next logical page). But basically once you find the actual rowid of
the bad row, you should then be able to start increasing the rowid
value of 1 until you can start unloading rows again by putting the
rowid column in the where clause of the unload select statement. For
example:
unload to thetable.unl select * from thetable where rowid > 513;
I think the rowid value you use has to be decimal. So in my example
above 513 is = 0x201 Which is then the 1st slot on logical page
address 2. So I'd then try to grab everything in the table passed the
1st slot in logical page two. Using this rowid concept works because
of how sequential scans work, which is by starting at logical page 0
in the table and going all the way out to number of pages used in the
table. So you can use the rowid concept to tell the scan to skip over
pages in the table. Which you need to do to get over the corrupted
row (or rows) to get as much of the data in your table as possible.
If you can do this yourself, and you have support you could also
contact support and they should be able to help you figure this out.
Jacques Renaut
IBM Informix Advanced Support
APD Team
#3
| |||
| |||
|
|
I'm stuck trying to do DBA tasks because our DBA was laid off ... I'm getting the following error when I try to UNLOAD one of the tables inour IDS 11.50.FC6 database: * *244: Could not do a physical-order read to fetch next row. * * *151: ISAM error: Illegal value in varchar length field * This error is also causing dbexport to fail. *I ran oncheck -cd on the table and it "fixed" something, but I still can't unload the table or select some rows in the table. Is there a way to fix this? *Is the data lost? This table does not have any indexes or unique keys. *There are about 940,000 rows, and I can only unload about 866,000 of them. *The schema is: create table thetable * ( * * trx_serial serial not null , * * trx_type char(1) not null , * * trx_datetime datetime year to minute * * * * default current year to minute, * * trx_user varchar(20) * * * * default user, * * ac_job_id integer, * * request_number smallint, * * product_item_enm varchar(30), * * art_item_enm varchar(30), * * center_enm varchar(30,1), * * submit_datetime datetime year to minute, * * priority_enm varchar(30), * * ac_status_enm varchar(30), * * r_day_zero_rule_id integer, * * r_due_date_rule_id integer, * * completed_date date, * * artist varchar(20), * * instructions lvarchar(4095), * * prior_request_number smallint, * * prior_product_item varchar(30,1), * * derived_panel_enm varchar(30), * * derived_templateid integer, * * derived_prod_code char(2), * * derived_fold_enm varchar(30), * * derived_is_window smallint, * * derived_color_count smallint, * * derived_size_enm varchar(30), * * derived_points smallint, * * derived_day_zero date, * * derived_due_date date, * * derived_fof_date date, * * derived_is_late smallint, * * is_online_order smallint, * * is_canceled smallint, * * requester_mm_net_login varchar(20), * * orientation_enm varchar(30) * ); Thanks, Sean. |
lost, but if the table is not fragmented you should be able to use the
rowid concept to unload the rows passed the bad one.
So for example if you do select rowid, * from thetable you should get
output which would contain the rowid of the last good row the engine
could get. Now this rowid column will be in decimal format, but what
you can do is convert the number of hex. Then the 4 byte hex
representation of the rowid has the following meaning:
0xLLLLLLSS where 0xLLLLLL is the logical page address of the table,
and SS is the physical slot on the data page. (note this is both why a
single table fragment is limited to 0xffffff pages, and why you can
have a max of 255 rows even if they are really tiny rows or really big
page sizes)
So once you have the last good rowid, the bad rowid will either be +1
if the next row is on that same page, or it will be on the next page
so the bad rowid would then be 0xLLLLLL(+1)01 (so the 1st slot on the
next logical page). But basically once you find the actual rowid of
the bad row, you should then be able to start increasing the rowid
value of 1 until you can start unloading rows again by putting the
rowid column in the where clause of the unload select statement. For
example:
unload to thetable.unl select * from thetable where rowid > 513;
I think the rowid value you use has to be decimal. So in my example
above 513 is = 0x201 Which is then the 1st slot on logical page
address 2. So I'd then try to grab everything in the table passed the
1st slot in logical page two. Using this rowid concept works because
of how sequential scans work, which is by starting at logical page 0
in the table and going all the way out to number of pages used in the
table. So you can use the rowid concept to tell the scan to skip over
pages in the table. Which you need to do to get over the corrupted
row (or rows) to get as much of the data in your table as possible.
If you can do this yourself, and you have support you could also
contact support and they should be able to help you figure this out.
Jacques Renaut
IBM Informix Advanced Support
APD Team
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list
#4
| |||
| |||
|
|
Thanks, Jacques, that worked. The table I tried it on (I have several that are giving me the same error) lost 49 rows out of 414,761. I was able to get the rowid of the first bad from the log, which gave me the error: 09:46:06 Assert Failed: read_record: decompress_row, source = 0x0x779da818, target = 0x0x1694ff0a8 09:46:06 IBM Informix Dynamic Server Version 11.50.FC6 09:46:06 Who: Session(17063, sbaker@venus, 17850, 0x15e5b8640) Thread(26304, sqlexec, 15e57b010, 3) File: rsread.c Line: 3634 09:46:06 Results: Record not read 09:46:06 Action: Please notify IBM Informix Technical Support. 09:46:06 stack trace for pid 1919 written to /opt/IBM/informix/tmp/af.6aa8d54d 09:46:06 See Also: /opt/IBM/informix/tmp/af.6aa8d54d 09:46:08 read_record: decompress_row, source = 0x0x779da818, target = 0x0x1694ff0a8 09:46:08 read_record: invalid rowid = 0x6b4301, partnum = 0x4004f5 I can use "partnum = 0x4004f5" to verify the table I'm working with, and use "invalid rowid = 0x6b4301" to get the first bad rowid. Since the rowids are not necessarily sequential, it took a while to get past the bad records. Finally, I dropped and re-created the table, loaded the data and re-created the indexes. I did this all on our development system, I still need to take care of it in production. Is there any way to get the data for those bad records so I can try to fix the data and re-insert? Thanks, Sean. -----Original Message----- From: informix-list-bounces (AT) iiug (DOT) org [mailto: informix-list-bounces (AT) iiug (DOT) org] On Behalf Of jrenaut Sent: Friday, May 27, 2011 9:00 AM To: informix-list (AT) iiug (DOT) org Subject: Re: ISAM error 151 on UNLOAD On May 27, 10:00 am, Sean Baker <SBa... (AT) moneymailer (DOT) com> wrote: I'm stuck trying to do DBA tasks because our DBA was laid off ... I'm getting the following error when I try to UNLOAD one of the tables in our IDS 11.50.FC6 database: 244: Could not do a physical-order read to fetch next row. 151: ISAM error: Illegal value in varchar length field This error is also causing dbexport to fail. I ran oncheck -cd on the table and it "fixed" something, but I still can't unload the table or select some rows in the table. Is there a way to fix this? Is the data lost? This table does not have any indexes or unique keys. There are about 940,000 rows, and I can only unload about 866,000 of them. The schema is: create table thetable ( trx_serial serial not null , trx_type char(1) not null , trx_datetime datetime year to minute default current year to minute, trx_user varchar(20) default user, ac_job_id integer, request_number smallint, product_item_enm varchar(30), art_item_enm varchar(30), center_enm varchar(30,1), submit_datetime datetime year to minute, priority_enm varchar(30), ac_status_enm varchar(30), r_day_zero_rule_id integer, r_due_date_rule_id integer, completed_date date, artist varchar(20), instructions lvarchar(4095), prior_request_number smallint, prior_product_item varchar(30,1), derived_panel_enm varchar(30), derived_templateid integer, derived_prod_code char(2), derived_fold_enm varchar(30), derived_is_window smallint, derived_color_count smallint, derived_size_enm varchar(30), derived_points smallint, derived_day_zero date, derived_due_date date, derived_fof_date date, derived_is_late smallint, is_online_order smallint, is_canceled smallint, requester_mm_net_login varchar(20), orientation_enm varchar(30) ); Thanks, Sean. It does sound like you have at least 1 corrupted row that will be lost, but if the table is not fragmented you should be able to use the rowid concept to unload the rows passed the bad one. So for example if you do select rowid, * from thetable you should get output which would contain the rowid of the last good row the engine could get. Now this rowid column will be in decimal format, but what you can do is convert the number of hex. Then the 4 byte hex representation of the rowid has the following meaning: 0xLLLLLLSS where 0xLLLLLL is the logical page address of the table, and SS is the physical slot on the data page. (note this is both why a single table fragment is limited to 0xffffff pages, and why you can have a max of 255 rows even if they are really tiny rows or really big page sizes) So once you have the last good rowid, the bad rowid will either be +1 if the next row is on that same page, or it will be on the next page so the bad rowid would then be 0xLLLLLL(+1)01 (so the 1st slot on the next logical page). But basically once you find the actual rowid of the bad row, you should then be able to start increasing the rowid value of 1 until you can start unloading rows again by putting the rowid column in the where clause of the unload select statement. For example: unload to thetable.unl select * from thetable where rowid > 513; I think the rowid value you use has to be decimal. So in my example above 513 is = 0x201 Which is then the 1st slot on logical page address 2. So I'd then try to grab everything in the table passed the 1st slot in logical page two. Using this rowid concept works because of how sequential scans work, which is by starting at logical page 0 in the table and going all the way out to number of pages used in the table. So you can use the rowid concept to tell the scan to skip over pages in the table. Which you need to do to get over the corrupted row (or rows) to get as much of the data in your table as possible. If you can do this yourself, and you have support you could also contact support and they should be able to help you figure this out. Jacques Renaut IBM Informix Advanced Support APD Team _______________________________________________ Informix-list mailing list Informix-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list _______________________________________________ Informix-list mailing list Informix-list (AT) iiug (DOT) org http://www.iiug.org/mailman/listinfo/informix-list |
#5
| |||
| |||
|
|
I'm stuck trying to do DBA tasks because our DBA was laid off ... I'm getting the following error when I try to UNLOAD one of the tables inour IDS 11.50.FC6 database: 244: Could not do a physical-order read to fetch next row. 151: ISAM error: Illegal value in varchar length field This error is also causing dbexport to fail. I ran oncheck -cd on the table and it "fixed" something, but I still can't unload the table or select some rows in the table. Is there a way to fix this? Is the data lost? This table does not have any indexes or unique keys. There are about 940,000 rows, and I can only unload about 866,000 of them. The schema is: create table thetable ( trx_serial serial not null , trx_type char(1) not null , trx_datetime datetime year to minute default current year to minute, trx_user varchar(20) default user, ac_job_id integer, request_number smallint, product_item_enm varchar(30), art_item_enm varchar(30), center_enm varchar(30,1), submit_datetime datetime year to minute, priority_enm varchar(30), ac_status_enm varchar(30), r_day_zero_rule_id integer, r_due_date_rule_id integer, completed_date date, artist varchar(20), instructions lvarchar(4095), prior_request_number smallint, prior_product_item varchar(30,1), derived_panel_enm varchar(30), derived_templateid integer, derived_prod_code char(2), derived_fold_enm varchar(30), derived_is_window smallint, derived_color_count smallint, derived_size_enm varchar(30), derived_points smallint, derived_day_zero date, derived_due_date date, derived_fof_date date, derived_is_late smallint, is_online_order smallint, is_canceled smallint, requester_mm_net_login varchar(20), orientation_enm varchar(30) ); Thanks, Sean. |
lost, but if the table is not fragmented you should be able to use the
rowid concept to unload the rows passed the bad one.
So for example if you do select rowid, * from thetable you should get
output which would contain the rowid of the last good row the engine
could get. Now this rowid column will be in decimal format, but what
you can do is convert the number of hex. Then the 4 byte hex
representation of the rowid has the following meaning:
0xLLLLLLSS where 0xLLLLLL is the logical page address of the table,
and SS is the physical slot on the data page. (note this is both why a
single table fragment is limited to 0xffffff pages, and why you can
have a max of 255 rows even if they are really tiny rows or really big
page sizes)
So once you have the last good rowid, the bad rowid will either be +1
if the next row is on that same page, or it will be on the next page
so the bad rowid would then be 0xLLLLLL(+1)01 (so the 1st slot on the
next logical page). But basically once you find the actual rowid of
the bad row, you should then be able to start increasing the rowid
value of 1 until you can start unloading rows again by putting the
rowid column in the where clause of the unload select statement. For
example:
unload to thetable.unl select * from thetable where rowid > 513;
I think the rowid value you use has to be decimal. So in my example
above 513 is = 0x201 Which is then the 1st slot on logical page
address 2. So I'd then try to grab everything in the table passed the
1st slot in logical page two. Using this rowid concept works because
of how sequential scans work, which is by starting at logical page 0
in the table and going all the way out to number of pages used in the
table. So you can use the rowid concept to tell the scan to skip over
pages in the table. Which you need to do to get over the corrupted
row (or rows) to get as much of the data in your table as possible.
If you can do this yourself, and you have support you could also
contact support and they should be able to help you figure this out.
Jacques Renaut
IBM Informix Advanced Support
APD Team
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org<ma...iiug (DOT) org>
http://www.iiug.org/mailman/listinfo/informix-list
_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org<ma...iiug (DOT) org>
http://www.iiug.org/mailman/listinfo/informix-list
#6
| |||
| |||
|
|
Thanks, Jacques, that worked. *The table I tried it on (I have several that are giving me the same error) lost 49 rows out of 414,761. I was able to get the rowid of the first bad from the log, which gave me the error: 09:46:06 *Assert Failed: read_record: decompress_row, source = 0x0x779da818, target = 0x0x1694ff0a8 09:46:06 *IBM Informix Dynamic Server Version 11.50.FC6 09:46:06 * Who: Session(17063, sbaker@venus, 17850, 0x15e5b8640) * * * * * * * * Thread(26304, sqlexec, 15e57b010, 3) * * * * * * * * File: rsread.c Line: 3634 09:46:06 * Results: Record not read 09:46:06 * Action: Please notify IBM Informix Technical Support. 09:46:06 *stack trace for pid 1919 written to /opt/IBM/informix/tmp/af.6aa8d54d 09:46:06 * See Also: /opt/IBM/informix/tmp/af.6aa8d54d 09:46:08 *read_record: decompress_row, source = 0x0x779da818, target = 0x0x1694ff0a8 09:46:08 *read_record: invalid rowid = 0x6b4301, partnum = 0x4004f5 I can use "partnum = 0x4004f5" to verify the table I'm working with, and use "invalid rowid = 0x6b4301" to get the first bad rowid. *Since therowids are not necessarily sequential, it took a while to get past the badrecords. Finally, I dropped and re-created the table, loaded the data and re-created the indexes. *I did this all on our development system, I still need to take care of it in production. Is there any way to get the data for those bad records so I can try to fix the data and re-insert? Thanks, Sean. -----Original Message----- From: informix-list-boun... (AT) iiug (DOT) org [mailto:informix-list-boun... (AT) iiug (DOT) org] On Behalf Of jrenaut Sent: Friday, May 27, 2011 9:00 AM To: informix-l... (AT) iiug (DOT) org Subject: Re: ISAM error 151 on UNLOAD On May 27, 10:00*am, Sean Baker <SBa... (AT) moneymailer (DOT) com> wrote: I'm stuck trying to do DBA tasks because our DBA was laid off ... I'm getting the following error when I try to UNLOAD one of the tables in our IDS 11.50.FC6 database: * *244: Could not do a physical-order read to fetch next row. * * *151: ISAM error: Illegal value in varchar length field * This error is also causing dbexport to fail. *I ran oncheck -cd on the table and it "fixed" something, but I still can't unload the table or select some rows in the table. Is there a way to fix this? *Is the data lost? This table does not have any indexes or unique keys. *There are about940,000 rows, and I can only unload about 866,000 of them. *The schema is: create table thetable * ( * * trx_serial serial not null , * * trx_type char(1) not null , * * trx_datetime datetime year to minute * * * * default current year to minute, * * trx_user varchar(20) * * * * default user, * * ac_job_id integer, * * request_number smallint, * * product_item_enm varchar(30), * * art_item_enm varchar(30), * * center_enm varchar(30,1), * * submit_datetime datetime year to minute, * * priority_enm varchar(30), * * ac_status_enm varchar(30), * * r_day_zero_rule_id integer, * * r_due_date_rule_id integer, * * completed_date date, * * artist varchar(20), * * instructions lvarchar(4095), * * prior_request_number smallint, * * prior_product_item varchar(30,1), * * derived_panel_enm varchar(30), * * derived_templateid integer, * * derived_prod_code char(2), * * derived_fold_enm varchar(30), * * derived_is_window smallint, * * derived_color_count smallint, * * derived_size_enm varchar(30), * * derived_points smallint, * * derived_day_zero date, * * derived_due_date date, * * derived_fof_date date, * * derived_is_late smallint, * * is_online_order smallint, * * is_canceled smallint, * * requester_mm_net_login varchar(20), * * orientation_enm varchar(30) * ); Thanks, Sean. It does sound like you have at least 1 corrupted row that will be lost, but if the table is not fragmented you should be able to use the rowid concept to unload the rows passed the bad one. So for example if you do select rowid, * from thetable you should get output which would contain the rowid of the last good row the engine could get. *Now this rowid column will be in decimal format, but what you can do is convert the number of hex. *Then the 4 byte hex representation of the rowid has the following meaning: 0xLLLLLLSS *where 0xLLLLLL is the logical page address of the table, and SS is the physical slot on the data page. (note this is both why a single table fragment is limited to 0xffffff pages, and why you can have a max of 255 rows even if they are really tiny rows or really big page sizes) So once you have the last good rowid, the bad rowid will either be +1 if the next row is on that same page, or it will be on the next page so the bad rowid would then be 0xLLLLLL(+1)01 (so the 1st slot on the next logical page). *But basically once you find the actual rowid of the bad row, you should then be able to start increasing the rowid value of 1 until you can start unloading rows again by putting the rowid column in the where clause of the unload select statement. *For example: unload to thetable.unl select * from thetable where rowid > *513; I think the rowid value you use has to be decimal. *So in my example above 513 is = 0x201 *Which is then the 1st slot on logical page address 2. *So I'd then try to grab everything in the table passed the 1st slot in logical page two. *Using this rowid concept works because of how sequential scans work, which is by starting at logical page 0 in the table and going all the way out to number of pages used in the table. *So you can use the rowid concept to tell the scan to skip over pages in the table. *Which you need to do to get over the corrupted row (or rows) to get as much of the data in your table as possible. If you can do this yourself, and you have support you could also contact support and they should be able to help you figure this out. Jacques Renaut IBM Informix Advanced Support APD Team _______________________________________________ Informix-list mailing list Informix-l... (AT) iiug (DOT) orghttp://www.iiug.org/mailman/listinfo/informix-list |
what happened to the page/pages or rows that the engine is having
problems reading. The more I think about it, the more it seems it
would really exceed the scope of what I could post on a forum trying
to explain, between the internal storage of different data types,
forwarding pointers for rows greater then dbspace page size or updates
to rows with varchars that don't fight on the current page,
etc...Under simplistic cases, if you could identify the bad rows via
rowid, you could use oncheck -pp <partnum> <logical page offset> to
have oncheck dump out the page in a hex format. It will break the
slots on the page down, and each slot would be a row, or a piece of a
row. But in theory from that hex dump you could pull some data out,
depending on what really happened to the page/row...
Depending on when this happened, you are likely better off trying to
get those rows out of an archive using the table level restore
feature, but that would depend on you having an archive that had good
copies of the rows, rather then the corrupted versions you have
currently.
Jacques Renaut
IBM Informix Advanced Support
APD Team
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.