set SESSION sql_mode = 'ANSI'MySQL translator (mysql/mysql5)
Also see common JDBC translators information.
Translation of MySQL and MariaDB is supported by the following translators.
- mysql
-
The MySQL translator, known by the type name mysql, is for use with MySQL version 4.x.
- mysql5
-
The MySQL5 translator, known by the type name mysql5, is for use with MySQL version 5 or later.
The translator also supports the translation of other compatible MySQL derivatives such as MariaDB.
Usage
The MySQL translators expect the database or session to be using ANSI mode. If the database is not using ANSI mode, you can set ANSI mode on the pool by submitting the following initialization query:
When data includes null timestamp values, Teiid generates the following conversion error:
0000-00-00 00:00:00 cannot be converted to a timestamp.
To avoid error, if you expect data with null timestamp values, set the connection property zeroDateTimeBehavior=convertToNull.
|
Warning
|
If you must retrieve large result sets, consider setting the connection property useCursorFetch=true. Otherwise MySQL will fully fetch
result sets into memory on the Teiid instance.
|
|
Note
|
MySQL reports TINYINT(1) columns as a JDBC BIT type - however the value range is not actually restricted and may cause issues if for example you are relying on -1 being recognized as a true value. If not using the native importer, change the BOOLEAN columns in the affected source to have a native type of "TINYINT(1)" rather than BIT so that the translator can appropriately handle the Boolean conversion. |