View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0004722 | SymmetricDS | Bug | public | 2020-12-24 08:33 | 2021-01-11 13:47 |
| Reporter | kraynopp | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.5 | ||||
| Target Version | 3.12.6 | Fixed in Version | 3.12.6 | ||
| Summary | 0004722: Invalid PK order on send-schema if source db is oracle | ||||
| Description | Symadmin send-schema command sends table with invalid PK column order if source database is oracle | ||||
| Steps To Reproduce | 1. I have source table in oracle: CREATE TABLE "DATA"."SYMPKTEST" ("ID2" NUMBER(10,0) NOT NULL ENABLE, "ID1" NUMBER(10,0) NOT NULL ENABLE, "ID3" NUMBER(10,0) NOT NULL ENABLE, "SOMEDATA" VARCHAR2(100 BYTE), CONSTRAINT "SYMPKTEST_PK" PRIMARY KEY ("ID3", "ID2", "ID1") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DATA" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DATA" ; 2. Execute symadmin send-schema in order to send table to destination database (postgresql). SymmetricDS generates batch: nodeid,001 binary,BASE64 channel,config batch,203443 catalog, schema,data table,sympktest keys,ID1,ID2,ID3 columns,ID1,ID2,ID3,SOMEDATA create,"<?xml version=\"1.0\"?> <!DOCTYPE database SYSTEM \"http://db.apache.org/torque/dtd/database\"> <database name=\"dataextractor\" schema=\"data\"> <table name=\"sympktest\"> <column name=\"ID1\" primaryKey=\"true\" primaryKeySeq=\"1\" required=\"true\" type=\"NUMERIC\" size=\"10\"> <platform-column name=\"oracle\" type=\"NUMBER\" size=\"10\"/> </column> <column name=\"ID2\" primaryKey=\"true\" primaryKeySeq=\"2\" required=\"true\" type=\"NUMERIC\" size=\"10\"> <platform-column name=\"oracle\" type=\"NUMBER\" size=\"10\"/> </column> <column name=\"ID3\" primaryKey=\"true\" primaryKeySeq=\"3\" required=\"true\" type=\"NUMERIC\" size=\"10\"> <platform-column name=\"oracle\" type=\"NUMBER\" size=\"10\"/> </column> <column name=\"SOMEDATA\" type=\"VARCHAR\" size=\"100\"> <platform-column name=\"oracle\" type=\"VARCHAR2\" size=\"100\"/> </column> </table> </database>" commit,203443 So, PK column order is changed to alphabetical (I suppose). 3. Table in destination database: CREATE TABLE data.sympktest ( id1 numeric(10,0) NOT NULL, id2 numeric(10,0) NOT NULL, id3 numeric(10,0) NOT NULL, somedata character varying(100), CONSTRAINT sympktest_pkey PRIMARY KEY (id1, id2, id3) ) WITH ( OIDS=FALSE ); | ||||
| Additional Information | Invalid PK column order leads to creation of index with invalid column order and poor performance of sql queries. This bug was discovered accidentally during investigation of extremely poor performance on destination database. Workaround: drop pk constraint on destination database, recreate pk with valid column order and execute symadmin sync-triggers. send-schema from postgres to postgres works fine. | ||||
| Tags | ddl/schema, dialect: oracle | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2020-12-24 08:33 | kraynopp | New Issue | |
| 2020-12-24 08:33 | kraynopp | Tag Attached: ddl/schema | |
| 2020-12-24 08:33 | kraynopp | Tag Attached: dialect: oracle | |
| 2020-12-30 19:21 | elong | Assigned To | => elong |
| 2020-12-30 19:21 | elong | Status | new => resolved |
| 2020-12-30 19:21 | elong | Resolution | open => fixed |
| 2020-12-30 19:21 | elong | Fixed in Version | => 3.12.6 |
| 2020-12-30 19:21 | elong | Target Version | => 3.12.6 |
| 2020-12-30 19:21 | elong | Note Added: 0001874 | |
| 2020-12-30 20:00 | admin | Changeset attached | => SymmetricDS 3.12 94e58e4c |
| 2021-01-11 13:47 | admin | Status | resolved => closed |