Creation of a replicate

Posted in 2010

Topics: Installation, Setup & Upgrades, Storage & Space Management, SQL Development & Query Writing, Security, Permissions & Auditing, Data Types & Schema Design

Link, David A — Tue, 30 Nov 2010 10:08:07 -0600 — source: Usenet: comp.databases.informix

We upgraded from 10.00.FC8 to 11.50.FC6. We had a replicate built on the following table: CREATE TABLE "informix".my_history ( mydate VARCHAR(20,0), user CHAR(20), program CHAR(20), recordtype CHAR(20), data CHAR(255), mykey CHAR(20) ) WITH CRCOLS IN dbs1 EXTENT SIZE 1580 NEXT SIZE 156 LOCK MODE ROW; REVOKE ALL ON "informix".my_history FROM public;CREATE UNIQUE INDEX "informix".uix_my_history ON "informix".my_history ( mydate ASC, user ASC, program ASC, recordtype ASC, data ASC, mykey ASC ) USING btree IN dbs1; ALTER TABLE my_history ADD CONSTRAINT PRIMARY KEY ( mydate, user, program, recordtype, data, mykey ) CONSTRAINT pk_history; We had to rebuild the replicate and now it fails. The command which we used was: cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select * from my_history" "my_db@grp229:informix.my_history" "select * from my_history" Verification of my_db@grp200:informix.my_history started Verification of my_db@grp200:informix.my_history is successful Verification of my_db@grp229:informix.my_history started Verification of my_db@grp229:informix.my_history is successful command failed -- unsupported SQL syntax (join, etc..) (40) We figured the "USER" column was causing the problem so we explicitly defined it with the table name prepended. Still didn't work. cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history" "my_db@grp229:informix.my_history" "select my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history" Verification of my_db@grp200:informix.my_history started Verification of my_db@grp200:informix.my_history is successful Verification of my_db@grp229:informix.my_history started Verification of my_db@grp229:informix.my_history is successful command failed -- unsupported SQL syntax (join, etc..) (40) Suggestions?

↪ replying to Link, David A

mpruet — Tue, 30 Nov 2010 20:44:51 -0800 (PST) — source: Usenet: comp.databases.informix

On Nov 30, 10:08 am, "Link, David A" <DAL...@west.com> wrote: > We upgraded from 10.00.FC8 to 11.50.FC6. We had a replicate built on the following table: > > CREATE TABLE "informix".my_history ( > mydate VARCHAR(20,0), > user CHAR(20), > program CHAR(20), > recordtype CHAR(20), > data CHAR(255), > mykey CHAR(20) > ) WITH CRCOLS > IN dbs1 EXTENT SIZE 1580 NEXT SIZE 156 LOCK MODE ROW; > > REVOKE ALL ON "informix".my_history FROM public;> CREATE UNIQUE INDEX "informix".uix_my_history ON "informix".my_history ( > mydate ASC, > user ASC, > program ASC, > recordtype ASC, > data ASC, > mykey ASC > ) USING btree IN dbs1; > > ALTER TABLE my_history ADD CONSTRAINT PRIMARY KEY ( > mydate, > user, > program, > recordtype, > data,> mykey > ) CONSTRAINT pk_history; > > We had to rebuild the replicate and now it fails. The command which we used was: > > cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select > * from my_history" "my_db@grp229:informix.my_history" "select * from my_history" > Verification of my_db@grp200:informix.my_history started > Verification of my_db@grp200:informix.my_history is successful > Verification of my_db@grp229:informix.my_history started > Verification of my_db@grp229:informix.my_history is successful > command failed -- unsupported SQL syntax (join, etc..) (40) > > We figured the "USER" column was causing the problem so we explicitly defined it with the table name prepended. Still didn't work. > > cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select > my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history" "my_db@grp229:informix.my_history" "select my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history" > Verification of my_db@grp200:informix.my_history started > Verification of my_db@grp200:informix.my_history is successful > Verification of my_db@grp229:informix.my_history started > Verification of my_db@grp229:informix.my_history is successful > command failed -- unsupported SQL syntax (join, etc..) (40) > > Suggestions? Making my life difficult, eh??? ;-) Looks like you ran into a bug. Yes 'user' is the culprit - in conjunction with a mastered replicate. If you can afford to remove the -M option then it should work. Otherwise, we'll have to rename the column. (ugg...) I opened a bug (219876) on this and do have a hacky patch working, but before I can finalize the patch, I'll need to make sure that it doesn't cause a regression with other ER functionality. M.P.

↪ replying to mpruet

mpruet — Wed, 1 Dec 2010 05:59:14 -0800 (PST) — source: Usenet: comp.databases.informix

On Nov 30, 10:44 pm, mpruet <mpru...@verizon.net> wrote: > On Nov 30, 10:08 am, "Link, David A" <DAL...@west.com> wrote: > > > > > We upgraded from 10.00.FC8 to 11.50.FC6. We had a replicate built on the following table: > > > CREATE TABLE "informix".my_history ( > > mydate VARCHAR(20,0), > > user CHAR(20), > > program CHAR(20), > > recordtype CHAR(20), > > data CHAR(255), > > mykey CHAR(20) > > ) WITH CRCOLS > > IN dbs1 EXTENT SIZE 1580 NEXT SIZE 156 LOCK MODE ROW; > > > REVOKE ALL ON "informix".my_history FROM public;> > CREATE UNIQUE INDEX "informix".uix_my_history ON "informix".my_history ( > > mydate ASC, > > user ASC, > > program ASC, > > recordtype ASC, > > data ASC, > > mykey ASC > > ) USING btree IN dbs1; > > > ALTER TABLE my_history ADD CONSTRAINT PRIMARY KEY ( > > mydate, > > user, > > program, > > recordtype, > > data,> > mykey > > ) CONSTRAINT pk_history; > > > We had to rebuild the replicate and now it fails. The command which we used was: > > > cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select > > * from my_history" "my_db@grp229:informix.my_history" "select * from my_history" > > Verification of my_db@grp200:informix.my_history started > > Verification of my_db@grp200:informix.my_history is successful > > Verification of my_db@grp229:informix.my_history started > > Verification of my_db@grp229:informix.my_history is successful > > command failed -- unsupported SQL syntax (join, etc..) (40) > > > We figured the "USER" column was causing the problem so we explicitly defined it with the table name prepended. Still didn't work. > > > cdr define replicate -c grp200 -M grp200 -u -R --conflict=timestamp --scope=row my_db_my_history "my_db@grp200:informix.my_history" "select > > my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history" "my_db@grp229:informix.my_history" "select my_history.mydate, my_history.user, my_history.program, my_history.recordtype, my_history.data, my_history.mykey from my_history" > > Verification of my_db@grp200:informix.my_history started > > Verification of my_db@grp200:informix.my_history is successful > > Verification of my_db@grp229:informix.my_history started > > Verification of my_db@grp229:informix.my_history is successful > > command failed -- unsupported SQL syntax (join, etc..) (40) > > > Suggestions? > > Making my life difficult, eh??? ;-) > > Looks like you ran into a bug. Yes 'user' is the culprit - in > conjunction with a mastered replicate. If you can afford to remove > the -M option then it should work. Otherwise, we'll have to rename > the column. (ugg...) > > I opened a bug (219876) on this and do have a hacky patch working, but > before I can finalize the patch, I'll need to make sure that it > doesn't cause a regression with other ER functionality. > > M.P. Oops. Found out that 219876 is a duplicate of 219863. We'll be working this issue as 219863.