RE: CRCOLS and VERCOLS and REPLCHECK
Actually think I may have just found the problem.
Always happens about 30s after sending the email.
My primary key on the r1 server is different to s67...
Cdr check is running now. Will see if it completes successfully.
James
From: James Brunskill
Sent: Friday, April 23, 2010 10:49 AM
To: informix-list@iiug.org
Subject: RE: CRCOLS and VERCOLS and REPLCHECK
Hey All,
I was just experimenting with the replcheck cols to improve sync
performance, and I seem to get a segmentation fault when trying to do a
cdr check replicate.
Nothing relevant seems to show up in the online log on either server.
[informix@r1) ~]$ cdr check replicate --master g_s67 --repl=aqs_packs_67
g_r1;
Apr 23 2010 09:13:20 ------ Table scan for aqs_packs_67 start
--------
Segmentation fault
------------------
We are running on RHEL 4 with informix 11.50.UC5 / (X2)
The X2 patch fixed an sever memory leak we had with ER transmits. It is
only installed on s67 as r1 doesn't send much er data so memory leak
doesn't really affect it.
R1 is and enterprise er server, and s67 is a leaf node WE edition.
[informix@r1 ~]$ onstat -
IBM Informix Dynamic Server Version 11.50.UC5 -- On-Line -- Up
17:48:00 -- 2295016 Kbytes
[sdev@s67 ~]$ onstat -
IBM Informix Dynamic Server Version 11.50.UC5X2 -- On-Line -- Up 20
days 17:42:21 -- 505544 Kbytes
Has anyone else seen this? Could it be due to our X2 patch?
Should also note that packs_new on r1 was an empty table, just created
it to test. I then copied some rows from s67 with a insert into select
* from and tried again...
[informix@r1 ~]$ cdr check replicate --master g_sirrp67
--repl=aqs_packs_67 g_rhea;
Apr 23 2010 10:36:24 ------ Table scan for aqs_packs_67 start
--------
Error in cmpCol
SQL types do not match for pack_id(117) unit_id(103)
command failed -- Source and Target do not have the same data type (202)
ER/DB details:
[informix@r1) ~]$ cdr define repl -C ignore aqs_packs_67 \\
"R aqs@g_r1:sdev.packs_new" "SELECT * FROM packs_new" \\
"P aqs@g_s67:sdev.packs" "SELECT * FROM packs"
[informix@r1) ~]$ cdr modify repl --ignoredel y aqs_packs_67
[informix@r1) ~]$ cdr start repl aqs_packs_67
[informix@r1) ~]$ dbschema -d aqs -t packs_new -ss
DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.UC5
{ TABLE "sdev".packs_new row size = 102 number of columns = 18 index
size = 51 }
create table "sdev".packs_new
(
pack_id serial8 not null ,
unit_id integer not null ,
item_dt datetime year to fraction(3),
pack_number integer,
check_weight float not null ,
tare_weight float,
head_target float,
head_number integer,
po_number char(12),
productsource integer,
aqs_control smallint
default 0,
valid_pack smallint
default 0,
seal_check smallint
default 0,
metal_detected smallint
default 0,
underweight smallint
default 0,
overweight smallint
default 0,
print_check smallint
default 0
) in aqs extent size 25000000 next size 409600 lock mode page;
alter table "sdev".packs_new add crcols;
alter table "sdev".packs_new add replcheck;
revoke all on "sdev".packs_new from "public" as "sdev";
create unique index "sdev".packs_er_idx on "sdev".packs_new (pack_id,
ifx_replcheck) using btree in aqs;
create unique index "sdev".packs_idx on "sdev".packs_new (unit_id,
item_dt) using btree in aqs;
alter table "sdev".packs_new add constraint primary key (unit_id,
item_dt) constraint "sdev".packs_pk ;
alter table "sdev".packs_new add constraint (foreign key (unit_id)
references "sdev".units constraint "sdev".pack_new_units_fk);
[informix@s67 ~]$ dbschema -d aqs -t packs -ss
DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.UC5X2
{ TABLE "sdev".packs row size = 102 number of columns = 18 index size =
79 }
create table "sdev".packs
(
pack_id serial8 not null ,
unit_id integer not null ,
item_dt datetime year to fraction(3),
pack_number integer,
check_weight float not null ,
tare_weight float,
head_target float,
head_number integer,
po_number char(12),
productsource integer,
aqs_control smallint
default 0,
valid_pack smallint
default 0,
seal_check smallint
default 0,
metal_detected smallint
default 0,
underweight smallint
default 0,
overweight smallint
default 0,
print_check smallint
default 0
) extent size 40960 next size 40960 lock mode row;
alter table "sdev".packs add crcols;
alter table "sdev".packs add replcheck;
revoke all on "sdev".packs from "public" as "sdev";
create index "sdev".item_dt_idx on "sdev".packs (item_dt) using
btree in data01;
create unique index "sdev".pack_id_idx on "sdev".packs (pack_id)
using btree in data01;
create unique index "sdev".packs_er_idx on "sdev".packs (pack_id,
ifx_replcheck) using btree in data01;
create index "sdev".po_idx on "sdev".packs (po_number) using btree
in data01;
alter table "sdev".packs add constraint (foreign key (unit_id)
references "sdev".units constraint "sdev".pack_units_fk);
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Thursday, April 22, 2010 5:25 AM
To: Laurie Gustin
Cc: informix-list@iiug.org
Subject: Re: CRCOLS and VERCOLS and REPLCHECK
Why choose, you can have all three:
> create table has_it_all(one serial) with crcols with replcheck withvercols;
Table created.
ER needs CRCOLs for normal processing and conflict resolution. It can
On Apr 22, 5:53 pm, "James Brunskill" <James.Brunsk...@fonterra.com>
wrote:
> Actually think I may have just found the problem.
>
> Always happens about 30s after sending the email.
>
> My primary key on the r1 server is different to s67...
>
> Cdr check is running now. Will see if it completes successfully.
>
> James
Well that's good news. Can you get the details about the original one
which was failing? While there may have been a bit of an issue, we
shouldn't have crashed. At most we should have complained.... ;-)
Thanks,
M.Pruet
>
> From: James Brunskill
> Sent: Friday, April 23, 2010 10:49 AM
> To: informix-l...@iiug.org
> Subject: RE: CRCOLS and VERCOLS and REPLCHECK
>
> Hey All,
>
> I was just experimenting with the replcheck cols to improve sync
> performance, and I seem to get a segmentation fault when trying to do a
> cdr check replicate.
>
> Nothing relevant seems to show up in the online log on either server.
>
> [informix@r1) ~]$ cdr check replicate --master g_s67 --repl=aqs_packs_67
> g_r1;
>
> Apr 23 2010 09:13:20 ------ Table scan for aqs_packs_67 start
> --------
>
> Segmentation fault
>
> ------------------
>
> We are running on RHEL 4 with informix 11.50.UC5 / (X2)
>
> The X2 patch fixed an sever memory leak we had with ER transmits. It is
> only installed on s67 as r1 doesn't send much er data so memory leak
> doesn't really affect it.
>
> R1 is and enterprise er server, and s67 is a leaf node WE edition.
>
> [informix@r1 ~]$ onstat -
>
> IBM Informix Dynamic Server Version 11.50.UC5 -- On-Line -- Up
> 17:48:00 -- 2295016 Kbytes
>
> [sdev@s67 ~]$ onstat -
>
> IBM Informix Dynamic Server Version 11.50.UC5X2 -- On-Line -- Up 20
> days 17:42:21 -- 505544 Kbytes>
> Has anyone else seen this? Could it be due to our X2 patch?
>
> Should also note that packs_new on r1 was an empty table, just created
> it to test. I then copied some rows from s67 with a insert into select
> * from and tried again...
>
> [informix@r1 ~]$ cdr check replicate --master g_sirrp67
> --repl=aqs_packs_67 g_rhea;
>
> Apr 23 2010 10:36:24 ------ Table scan for aqs_packs_67 start
> --------
>
> Error in cmpCol
>
> SQL types do not match for pack_id(117) unit_id(103)
>
> command failed -- Source and Target do not have the same data type (202)
>
> ER/DB details:
>
> [informix@r1) ~]$ cdr define repl -C ignore aqs_packs_67 \\
>
> "R aqs@g_r1:sdev.packs_new" "SELECT * FROM packs_new" \\
>
> "P aqs@g_s67:sdev.packs" "SELECT * FROM packs"
>
> [informix@r1) ~]$ cdr modify repl --ignoredel y aqs_packs_67
>
> [informix@r1) ~]$ cdr start repl aqs_packs_67
>
> [informix@r1) ~]$ dbschema -d aqs -t packs_new -ss
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.UC5
>
> { TABLE "sdev".packs_new row size = 102 number of columns = 18 index
> size = 51 }
>
> create table "sdev".packs_new
>
> (
>
> pack_id serial8 not null ,
>
> unit_id integer not null ,
>
> item_dt datetime year to fraction(3),
>
> pack_number integer,
>
> check_weight float not null ,
>
> tare_weight float,
>
> head_target float,
>
> head_number integer,
>
> po_number char(12),
>
> productsource integer,
>
> aqs_control smallint
>
> default 0,
>
> valid_pack smallint
>
> default 0,
>
> seal_check smallint
>
> default 0,
>
> metal_detected smallint
>
> default 0,
>
> underweight smallint
>
> default 0,
>
> overweight smallint
>
> default 0,
>
> print_check smallint
>
> default 0
>
> ) in aqs extent size 25000000 next size 409600 lock mode page;
>
> alter table "sdev".packs_new add crcols;
>
> alter table "sdev".packs_new add replcheck;
>
> revoke all on "sdev".packs_new from "public" as "sdev";>
> create unique index "sdev".packs_er_idx on "sdev".packs_new (pack_id,
>
> ifx_replcheck) using btree in aqs;
>
> create unique index "sdev".packs_idx on "sdev".packs_new (unit_id,
>
> item_dt) using btree in aqs;
>
> alter table "sdev".packs_new add constraint primary key (unit_id,
>
> item_dt) constraint "sdev".packs_pk ;
>
> alter table "sdev".packs_new add constraint (foreign key (unit_id)
>
> references "sdev".units constraint "sdev".pack_new_units_fk);
>
> [informix@s67 ~]$ dbschema -d aqs -t packs -ss
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.UC5X2
>
> { TABLE "sdev".packs row size = 102 number of columns = 18 index size =
> 79 }
>
> create table "sdev".packs
>
> (
>
> pack_id serial8 not null ,
>
> unit_id integer not null ,
>
> item_dt datetime year to fraction(3),
>
> pack_number integer,
>
> check_weight float not null ,
>
> tare_weight float,
>
> head_target float,
>
> head_number integer,
>
> po_number char(12),
>
> productsource integer,
>
> aqs_control smallint
>
> default 0,
>
> valid_pack smallint
>
> default 0,
>
> seal_check smallint
>
> default 0,
>
> metal_detected smallint
>
> default 0,
>
> underweight smallint
>
> default 0,
>
> overweight smallint
>
> default 0,
>
> print_check smallint
>
> default 0
>
> ) extent size 40960 next size 40960 lock mode row;
>
> alter table "sdev".packs add crcols;
>
> alter table "sdev".packs add replcheck;
>
> revoke all on "sdev".packs from "public" as "sdev";>
> create index "sdev".item_dt_idx on "sdev".packs (item_dt) using
>
> btree in data01;
>
> create unique index "sdev".pack_id_idx on "sdev".packs (pack_id)
>
> using btree in data01;
>
> create unique index "sdev".packs_er_idx on "sdev".packs (pack_id,
>
> ifx_replcheck) using btree in data01;
>
> create index "sdev".po_idx on "sdev".packs (po_number) using btree
>
> in data01;
>
> alter table "sdev".packs add constraint (foreign key (unit_id)
>
> references "sdev".units constraint "sdev".pack_units_fk);
>
> From: informix-list-boun...@iiug.org
> [mailto:informix-list-boun...@iiug.org] On Behalf Of Art Kagel
> Sent: Thursday, April 22, 2010 5:25 AM
> To: Laurie Gustin
> Cc: informix-l...@iiug.org
> Subject: Re: CRCOLS and VERCOLS and REPLCHECK
>
> Why c
What details do you want?
I think the issue was that there were no rows in the destination table.
After I inserted some rows the segfault stopped. Not many of us are stupid enough to check a replicate which you know has no rows in it :)
I could setup another test case? Don't know if it is related to replcheck cols, or if it would still segfault without.
Cheers,
James
BTW: the cdr check replicate did complete successfully and found all the missing rows.
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of mpruet
Sent: Friday, April 23, 2010 4:25 PM
To: informix-list@iiug.org
Subject: Re: CRCOLS and VERCOLS and REPLCHECK
On Apr 22, 5:53 pm, "James Brunskill" <James.Brunsk...@fonterra.com>
wrote:
> Actually think I may have just found the problem.
>
> Always happens about 30s after sending the email.
>
> My primary key on the r1 server is different to s67...
>
> Cdr check is running now. Will see if it completes successfully.
>
> James
Well that's good news. Can you get the details about the original one
which was failing? While there may have been a bit of an issue, we
shouldn't have crashed. At most we should have complained.... ;-)
Thanks,
M.Pruet
>
> From: James Brunskill
> Sent: Friday, April 23, 2010 10:49 AM
> To: informix-l...@iiug.org
> Subject: RE: CRCOLS and VERCOLS and REPLCHECK
>
> Hey All,
>
> I was just experimenting with the replcheck cols to improve sync
> performance, and I seem to get a segmentation fault when trying to do a
> cdr check replicate.
>
> Nothing relevant seems to show up in the online log on either server.
>
> [informix@r1) ~]$ cdr check replicate --master g_s67 --repl=aqs_packs_67
> g_r1;
>
> Apr 23 2010 09:13:20 ------ Table scan for aqs_packs_67 start
> --------
>
> Segmentation fault
>
> ------------------
>
> We are running on RHEL 4 with informix 11.50.UC5 / (X2)
>
> The X2 patch fixed an sever memory leak we had with ER transmits. It is
> only installed on s67 as r1 doesn't send much er data so memory leak
> doesn't really affect it.
>
> R1 is and enterprise er server, and s67 is a leaf node WE edition.
>
> [informix@r1 ~]$ onstat -
>
> IBM Informix Dynamic Server Version 11.50.UC5 -- On-Line -- Up
> 17:48:00 -- 2295016 Kbytes
>
> [sdev@s67 ~]$ onstat -
>
> IBM Informix Dynamic Server Version 11.50.UC5X2 -- On-Line -- Up 20
> days 17:42:21 -- 505544 Kbytes>
> Has anyone else seen this? Could it be due to our X2 patch?
>
> Should also note that packs_new on r1 was an empty table, just created
> it to test. I then copied some rows from s67 with a insert into select
> * from and tried again...
>
> [informix@r1 ~]$ cdr check replicate --master g_sirrp67
> --repl=aqs_packs_67 g_rhea;
>
> Apr 23 2010 10:36:24 ------ Table scan for aqs_packs_67 start
> --------
>
> Error in cmpCol
>
> SQL types do not match for pack_id(117) unit_id(103)
>
> command failed -- Source and Target do not have the same data type (202)
>
> ER/DB details:
>
> [informix@r1) ~]$ cdr define repl -C ignore aqs_packs_67 \\
>
> "R aqs@g_r1:sdev.packs_new" "SELECT * FROM packs_new" \\
>
> "P aqs@g_s67:sdev.packs" "SELECT * FROM packs"
>
> [informix@r1) ~]$ cdr modify repl --ignoredel y aqs_packs_67
>
> [informix@r1) ~]$ cdr start repl aqs_packs_67
>
> [informix@r1) ~]$ dbschema -d aqs -t packs_new -ss
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.UC5
>
> { TABLE "sdev".packs_new row size = 102 number of columns = 18 index
> size = 51 }
>
> create table "sdev".packs_new
>
> (
>
> pack_id serial8 not null ,
>
> unit_id integer not null ,
>
> item_dt datetime year to fraction(3),
>
> pack_number integer,
>
> check_weight float not null ,
>
> tare_weight float,
>
> head_target float,
>
> head_number integer,
>
> po_number char(12),
>
> productsource integer,
>
> aqs_control smallint
>
> default 0,
>
> valid_pack smallint
>
> default 0,
>
> seal_check smallint
>
> default 0,
>
> metal_detected smallint
>
> default 0,
>
> underweight smallint
>
> default 0,
>
> overweight smallint
>
> default 0,
>
> print_check smallint
>
> default 0
>
> ) in aqs extent size 25000000 next size 409600 lock mode page;
>
> alter table "sdev".packs_new add crcols;
>
> alter table "sdev".packs_new add replcheck;
>
> revoke all on "sdev".packs_new from "public" as "sdev";>
> create unique index "sdev".packs_er_idx on "sdev".packs_new (pack_id,
>
> ifx_replcheck) using btree in aqs;
>
> create unique index "sdev".packs_idx on "sdev".packs_new (unit_id,
>
> item_dt) using btree in aqs;
>
> alter table "sdev".packs_new add constraint primary key (unit_id,
>
> item_dt) constraint "sdev".packs_pk ;
>
> alter table "sdev".packs_new add constraint (foreign key (unit_id)
>
> references "sdev".units constraint "sdev".pack_new_units_fk);
>
> [informix@s67 ~]$ dbschema -d aqs -t packs -ss
>
> DBSCHEMA Schema Utility INFORMIX-SQL Version 11.50.UC5X2
>
> { TABLE "sdev".packs row size = 102 number of columns = 18 index size =
> 79 }
>
> create table "sdev".packs
>
> (
>
> pack_id serial8 not null ,
>
> unit_id integer not null ,
>
> item_dt datetime year to fraction(3),
>
> pack_number integer,
>
> check_weight float not null ,
>
> tare_weight float,
>
> head_target float,
>
> head_number integer,
>
> po_number char(12),
>
> productsource integer,
>
> aqs_control smallint
>
> default 0,
>
> valid_pack smallint
>
> default 0,
>
> seal_check smallint
>
> default 0,
>
> metal_detected smallint
>
> default 0,
>
> underweight smallint
>
> default 0,
>
> overweight smallint
>
> default 0,
>
> print_check smallint
>
> default 0
>
> ) extent size 40960 next size 40960 lock mode row;
>
> alter table "sdev".packs add crcols;
>
> alter table "sdev".packs add replcheck;
>
> revoke all on "sdev".packs from "public" as "sdev";>
> create index "sdev".item_dt_idx on "sdev".packs (item_dt) using
>
> btree in data01;
>
> create unique index "sdev".pack_id_idx on "sdev".packs (pack_id)
>
> u