Does not allow Empty value of "Create target control tables in schema" of Qlik Replicate V2021.11.0.107 ?

[khchoy]

Hi All,

When I internal testing , I met a error.

If I did not set value of  Create target control tables in schema, I met below error and stop task.

00037508: 2021-12-21T15:51:42:989237 [TARGET_APPLY ]T: Create table statement: CREATE TABLE "CDCADM"."attrep_apply_exceptions" ( "TASK_NAME" VARCHAR2(128) not null, "TABLE_OWNER" VARCHAR2(128) not null, "TABLE_NAME" VARCHAR2(128) not null, "ERROR_TIME" TIMESTAMP(3) not null, "STATEMENT" CLOB, "ERROR" CLOB ) (oracle_endpoint_load.c:676)
00037508: 2021-12-21T15:51:42:989267 [TARGET_APPLY ]T: Create table statement CREATE TABLE "CDCADM"."attrep_apply_exceptions" ( "TASK_NAME" VARCHAR2(128) not null, "TABLE_OWNER" VARCHAR2(128) not null, "TABLE_NAME" VARCHAR2(128) not null, "ERROR_TIME" TIMESTAMP(3) not null, "STATEMENT" CLOB, "ERROR" CLOB ) (oracle_endpoint_apply.c:1371)
00037508: 2021-12-21T15:51:42:994961 [TARGET_APPLY ]V: Oracle error code is '955' (oracle_endpoint_apply.c:1306)
00037508: 2021-12-21T15:51:42:994997 [TARGET_APPLY ]T: ORA-00955: name is already used by an existing object [1020413] (oracle_endpoint_apply.c:1306)
00037508: 2021-12-21T15:51:42:995028 [TARGET_APPLY ]T: Cannot create Special table [1020413] (endpointshell.c:3331)
00037508: 2021-12-21T15:51:42:995047 [TARGET_APPLY ]T: Cannot create Exception table [1020413] (endpointshell.c:3566)

But I  set value of  Create target control tables in schema to "CDCADM", Error is not occurred.

00037681: 2021-12-21T16:03:24:374534 [TARGET_APPLY ]T: Check owner statement select username from all_users where username='CDCADM' (oracle_endpoint_load.c:3937)
00037681: 2021-12-21T16:03:24:378632 [TARGET_APPLY ]T: Going to prepare the statement 'select object_id from ALL_OBJECTS where owner = :1 and object_name = :2 and (object_type = 'TABLE' or object_type = 'VIEW') and temporary = 'N' and object_name not like 'BIN$%' and object_name not like 'DR$%' for 'CDCADM.attrep_apply_exceptions' (oracle_endpoint_table.c:1065)
00037681: 2021-12-21T16:03:24:381586 [TARGET_APPLY ]T: Table 'CDCADM.attrep_apply_exceptions' already exists (oracle_endpoint_apply.c:1363)
00037681: 2021-12-21T16:03:24:381836 [TARGET_APPLY ]T: Execute table description query 'SELECT * FROM "CDCADM"."attrep_apply_exceptions"' (oracle_endpoint_table.c:1884)
00037681: 2021-12-21T16:03:24:383598 [TARGET_APPLY ]T: Retrieve the column info of table 'CDCADM.attrep_apply_exceptions' (oracle_endpoint_table.c:2050)
00037681: 2021-12-21T16:03:24:383680 [TARGET_APPLY ]T: Col 'TASK_NAME' charset: UTF8 converter: UTF-8 (oracle_endpoint_table.c:175)
00037681: 2021-12-21T16:03:24:383722 [TARGET_APPLY ]T: Col 'TABLE_OWNER' charset: UTF8 converter: UTF-8 (oracle_endpoint_table.c:175)
00037681: 2021-12-21T16:03:24:383750 [TARGET_APPLY ]T: Col 'TABLE_NAME' charset: UTF8 converter: UTF-8 (oracle_endpoint_table.c:175)
00037681: 2021-12-21T16:03:24:383799 [TARGET_APPLY ]T: Col 'STATEMENT' charset: UTF8 converter: UTF-8 (oracle_endpoint_table.c:175)
00037681: 2021-12-21T16:03:24:383828 [TARGET_APPLY ]T: Col 'ERROR' charset: UTF8 converter: UTF-8 (oracle_endpoint_table.c:175)
00037681: 2021-12-21T16:03:24:383868 [TARGET_APPLY ]T: Going to prepare the statement 'select cols.column_name, cons.constraint_name, NVL((select NVL(indcols.DESCEND, 'ASC') from all_ind_columns indcols where indcols.table_name = cols.table_name AND indcols.table_owner = cols.owner and cons.index_name = indcols.index_name and cons.index_owner = indcols.index_owner and cols.column_name = indcols.column_name), 'ASC') from all_constraints cons, all_cons_columns cols where cols.table_name = :1 and cols.owner = :2 and cons.constraint_type = 'P' and cons.constraint_name = cols.constraint_name and cons.owner = cols.owner order by cols.position' for 'CDCADM.attrep_apply_exceptions' (oracle_endpoint_table.c:624)
00037683: 2021-12-21T16:03:24:389970 [SOURCE_UNLOAD ]I: Supplemental logging automatic definition is disabled (oracle_endpoint_imp.c:889)
00037681: 2021-12-21T16:03:24:396935 [TARGET_APPLY ]T: Going to prepare the statement 'select i.index_name, i.tablespace_name, NVL(c.column_name,' '), NVL(c.DESCEND, 'ASC') from all_indexes i, all_ind_columns c where i.index_name = c.index_name and (i.INDEX_TYPE = 'NORMAL' or i.INDEX_TYPE = 'FUNCTION-BASED NORMAL') and i.table_name = :1 and i.owner = c.index_owner and i.table_owner = :2 and c.table_name = :1 and c.table_owner = :2 and i.uniqueness = 'UNIQUE' order by c.index_name, c.column_position for 'CDCADM.attrep_apply_exceptions' (oracle_endpoint_table.c:1537)
00037681: 2021-12-21T16:03:24:398423 [TARGET_APPLY ]T: Table schema 'CDCADM.attrep_apply_exceptions' is completed (oracle_endpoint_table.c:2214)
00037683: 2021-12-21T16:03:24:505630 [SOURCE_UNLOAD ]I: Oracle database uses 'STANDARD' max_string_size (oracle_endpoint_conn.c:570)

Why this symptom is occurred ?

Regards,

Kwangho

[Steve_Nguyen]

for the first error :

00037508: 2021-12-21T15:51:42:989267 [TARGET_APPLY ]T: Create table statement CREATE TABLE "CDCADM"."attrep_apply_exceptions" ( "TASK_NAME" VARCHAR2(128) not null, "TABLE_OWNER" VARCHAR2(128) not null, "TABLE_NAME" VARCHAR2(128) not null, "ERROR_TIME" TIMESTAMP(3) not null, "STATEMENT" CLOB, "ERROR" CLOB ) (oracle_endpoint_apply.c:1371)
00037508: 2021-12-21T15:51:42:994961 [TARGET_APPLY ]V: Oracle error code is '955' (oracle_endpoint_apply.c:1306)
00037508: 2021-12-21T15:51:42:994997 [TARGET_APPLY ]T: ORA-00955: name is already used by an existing object [1020413] (oracle_endpoint_apply.c:1306)

----

try to stop the task, and manually create this table on your target, what do you get ?

[Steve_Nguyen]

notes from the user guide :

https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/C...

Create target control tables in tablespace: When the target endpoint is Oracle, specify the tablespace where you want the target control tables to be created. If you do not enter any information in this field, the tables will be created in the default tablespace in the target database.

Create target control table indexes in tablespace: When the target endpoint is Oracle, specify the tablespace where you want the control table indexes to be created. If you do not enter any information in this field, the indexes will be created in the same tablespace as the control tables.

[Heinvandenheuvel]

In the log from which you retrieved that first first set of trace, was there not something like the line:

"'select object_id from ALL_OBJECTS where owner = :1 and object_name = :2 and ....' for 'CDCADM.attrep_apply_exceptions'

That SQL should be the first in a series of tests whether there is an attrep_apply_exceptions already and whether it is useable.

Maybe the query wasn't executed due to schema-name being null instead of trying to obtain the default schema name for the replicate target user, which best I understand is simply the username.

There is "ALTER SESSION SET CURRENT_SCHEMA =", but there is no 'default' schema other than username is there?

Anyway, feels like a bug with an easy workaround.

Can you try on older versions per chance?

fwiw,

Hein.

[khchoy]

Hi All,

I tested it with another version, but if there is no value in the corresponding column, a problem occurs.

This problem seems to have to be corrected in the manual by unconditionally entering a value in the corresponding column and using it.

If the column is left blank, various types of errors occur depending on the environment, so I think it is necessary to modify the contents of the manual. 

The current manual is as follows.

In the User Guide :---------------------------------------------------------------------------------------------------------------------------

Create target control tables in schema: Enter the endpoint schema for the target Control Tables. If you do not enter any information in this field, then the tables will be created in the default location in the endpoint.

Note:

When this field is left empty, the target endpoint is MySQL, and the Multiple Endpoints option is enabled, a default database named attrep_control will be created on the MySQL server. The selected control tables will be created in this database.

For more information on the Multiple Endpoints option, see Setting general connection properties.

Note:

When replicating to a Hadoop target endpoint, the value specified in this field will be interpreted as a database name (as opposed to a schema name).

--------------------------------------------------------------------------------------------------------------------------------