Troubleshooting for MDM SQL Storage

EnrichVersion
6.4
6.3
6.2
6.1
6.0
5.6
EnrichProdName
Talend Open Studio for MDM
Talend MDM Platform
Talend Data Fabric
task
Installation and Upgrade
EnrichPlatform
Talend MDM Server

Troubleshooting for MDM SQL Storage

After you carried out the necessary configurations for using MDM SQL Storage and proceeded to use MDM SQL storage, you may encounter a variety of issues depending on the RDBMS you are using.

This article provides troubleshooting information and some specific information on how to tackle the following issues:

Deadlocks

When an unexpected issue occurs related to SQL query execution and there was no response from the server for a while, a message is printed in server's log, which reads:

014-01-17 00:29:12,733 WARN  [com.mchange.v2.async.ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@5614f781 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!

Although it indicates "DEADLOCK", it does not always mean there is indeed a deadlock in the sense of a database deadlock.

MDM uses a pool of JDBC connections and this pool is monitored by another thread. This monitor ensures the pool meets all the criteria defined in the configuration such as the min pool size, and connection renewal.

The important part of the message above is "APPARENT": this message is printed by the monitor thread. All it says is that it has not been able to maintain a healthy pool for a while. This can be caused by:

  • An actual database deadlock: all borrowed connections are waiting for the end of the execution, and the pool cannot be expanded.
  • An MDM-to-database connection issue: the pool cannot be expanded because all connections to the database are failing due to wrong credentials or network issues.

For some RDBMS like Oracle, MDM server can sometimes cause transaction deadlocking to happen in Oracle server. To fix deadlocks, you need to investigate which specific FK column(s) are missing their index(es), and then manually create the missing FK index(es) to resolve the issues.

Here is an Oracle PL/SQL script to create the indexes for all the FKs in the user tables in an Oracle database. Note that you need to run this script as the database user, not when connected as system or dba account.

DECLARE
  -- Public variable declarations
  sql_str        VARCHAR2(5000);
  tableName      user_cons_columns.TABLE_NAME%type;
  fkColumnName   user_cons_columns.COLUMN_NAME%type;
  tableSpaceName varchar2(50);
  num NUMBER(10) NOT NULL := 0;
 
  CURSOR table_fk_cur IS
    SELECT ucc.table_name, ucc.column_name
      FROM user_constraints uc, user_cons_columns ucc
     WHERE uc.constraint_name = ucc.constraint_name
       AND uc.constraint_type = 'R'
     ORDER BY ucc.table_name, uc.constraint_name;
 
BEGIN
  DBMS_OUTPUT.ENABLE(200000);
   
  --get tablespace name
  SELECT property_value
    INTO tableSpaceName
    FROM database_properties
   WHERE property_name like 'DEFAULT_PERMANENT_TABLESPACE';
 
 
  OPEN table_fk_cur;
  LOOP
    FETCH table_fk_cur
      INTO tableName, fkColumnName;
       
    EXIT WHEN table_fk_cur%NOTFOUND;
     
    num := num + 1;
    dbms_output.put_line(tableName || '  ' || fkColumnName);
   
    sql_str := 'CREATE INDEX MISSING_FK_IDX_' || num || ' ON ' || tableName || ' ( ' || fkColumnName ||
               ' ) TABLESPACE ' || tableSpaceName;
    dbms_output.put_line(sql_str);
   
    execute immediate sql_str;
     
  END LOOP;
  CLOSE table_fk_cur;
   
  dbms_output.put_line('Totally ' || num || ' of foreign key columns are indexed.'); 
END;

Data model specific issues

MDM SQL storage cannot handle entities that create a cyclic dependency in FK definition, that is, Entity A references Entity B which references Entity C which references back to Entity A. In this case, an error message is displayed:

Caused by: java.lang.IllegalArgumentException: Data model has at least one circular dependency.
(Hint: SiteInjectionRpd -> Contrat -> Edp -> SiteInjectionRpd)
(Possible fields: Edp/typeEdp/EdpRpd/SitesInjectionRpd/SiteInjectionRpd UniteAgregation/typeUA/UAPointInjectionRpd/SitesInjectionRpd/SiteInjectionRpd Edprev/typeEdprev/EdprevRpd/SitesInjectionRpd/SiteInjectionRpd )

The exception gives you a path that contains the cyclic dependency. Note that MDM gives you a cycle, not all cycles in the data model. The exception stacktrace also gives you a list of possible fields where you need to disable FK integrity (MDM does not do it automatically because this should remain an end-user decision).

One way to improve user experience would be to perform cyclic dependency detection in the data model editor of the studio. This check is currently performed only on the server side.

Database specific considerations

Bear in mind the following when you need to handle data types and SQL statements with different databases.

Oracle

For the xsd:decimal element, trailing zeros are removed. For example, if you write a value "130000.00", reading it later returns "130000".

SQL Server

For xsd:byte and xsd:unsignedByte, SQL Server uses unsigned bytes. If Microsoft JDBC driver is used, searching for "entity with field > -50" is automatically converted into "entity with field > 50" since the minus character will be removed.

Initialization issues

In case of initialization issue, SQL storage dumps information that may help to diagnose why the initialization failed.

The following information is dumped: data model being used for initialization (in a MDM interpreted form, not as XML schema).

Common causes for exceptions:

  • Data model has errors (for example, the FK points to a non-existing element in data model).
  • Use of SQL storage restricted names (Make sure that the elements you defined do not start with X_TALEND_).

Common causes for warnings:

  • Impossibility to express data model constraints into databases: MDM generates database schema that includes much information from the data model (FK integrity checks, not empty elements...). But in some cases usually related to sequences, database schema cannot express data model constraints. This is only a warning since XML validation will ensure all data model constraints are correctly enforced.

Delete issues

MDM SQL storage uses a query to select instances to be deleted. If there are errors, MDM dumps the query to the server’s log, which allows you to analyze the query and check why the deletion failed.

[SELECT]
        Types
                Person
        Selected fields
        Join
                <NONE>
        Condition
                <NONE>
        Order by
                <NONE>

Query results issues

Note that selecting an XML element with no text content will always return an empty string.

As shown in the following example, the selected element, Features, is a wrapper element. The Query will return an XML element "Features" but with no content (XML database may do a value concatenation in this case).

<Product>
    <Features>
        <Colors>
            <Color>Blue</Color>
            <Color>Red</Color>
        </Colors>
    </Features>
</Product>

Query execution issues

For query execution issues, MDM dumps the parsed query structure (joins, where conditions). For example:

[SELECT]
        Types
                Person
        Selected fields
                [FIELD]
                        Person/firstname
                [FIELD]
                        Address/Street
                [FIELD]
                        Country/name
        Join
                [JOIN]
                        [LEFT]
                                [FIELD]
                                        addresses/address
 
                        [RIGHT]
                                [FIELD]
                                        Address/Id
                                        Address/enterprise
                [JOIN]
                        [LEFT]
                                [FIELD]
                                        Address/country
                        [RIGHT]
                                [FIELD]
                                        Country/id
        Condition
                <NONE>
        Order by
                <NONE>

Common causes of exceptions (full text queries):

  • Full text search includes conditions other than full text: this is a known limitation due to the technology used for full text searches. Operators supported with a full text search are: greater than (>), greater than or equals (>=), less than (<), less than or equals (<=).

Common causes of exceptions (all other queries):

  • Join is performed on a field that cannot be reached from the main selected type.
  • A IsEmptyOrNull is performed on a repeatable element.

FAQ

Q: Why cannot my database work with MDM?

A: You need to check which versions of each database are supported.

Q: Does MDM support automatic database creation?

A: Yes, MDM supports automatic database creation. However, you need to ensure that the property "db.autoPrepare" in the mdm.conf file is set to "true" (any other value is equivalent to “false”). When the property is set to "true", you may see the following line in the log:

2014-05-14 17:58:45,772 29324 INFO
[com.amalto.core.storage.hibernate.HibernateStorage] (main:) Preparing
database before schema generation.

When it is set to "false", you may see this:

2014-05-14 13:26:58,107 INFO
[com.amalto.core.storage.hibernate.HibernateStorage] *NOT* preparing
database before schema generation.

Q: How could I delete all tables and let MDM recreate all the tables in Oracle?

A: The following script allows you to drop all tables in Oracle without deleting or recreating the tablespace.

Note that you need to run this script as the database user, not when connected as system or dba account.

declare
  
v_str1 varchar2(200)
:= null;
 
cursor get_sql is
 
select
'drop '||object_type||'
'|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE')
v_str1
 
from user_objects
 
where object_type in ('TABLE','VIEW','PACKAGE','TYPE','PROCEDURE','FUNCTION','TRIGGER','SEQUENCE')
 
order by object_type,object_name;
 
begin
 
open get_sql;
 
loop
 
fetch get_sql into v_str1;
 
if get_sql%notfound
 
then exit;
 
end if;
 
execute immediate v_str1;
 
end loop;
 
close get_sql;
 
end;
 
 
/