Troubleshooting for MDM SQL Storage
This article provides troubleshooting information and some specific information on how to tackle the issues.
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
For the xsd:decimal element, trailing zeros are removed. For example, if you write a value "130000.00", reading it later returns "130000".
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.
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.
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):
- The contains and full_text operands cannot be used together with other search operators. Since a full text search internally covers conditions from contains or full_text, these operands cannot be combined together.
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.
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; /