set SESSION sql_mode = 'ANSI'
MySQL Translator (mysql/mysql5)
Also see common JDBC Translator Information
MySQL/MariaDB translation is supported by 2 translators.
The Mysql translator, known by the type name mysql, is for use with MySQL version 4.x.
The Mysql5 translator, known by the type name mysql5, is for use with MySQL version 5 or later.
Also supports compatible MySQL derivatives including MariaDB.
The MySQL Translators expect the database or session to be using ANSI mode. If the database is not using ANSI mode, an initialization query should be used on the pool to set ANSI mode:
If you may deal with null timestamp values, then set the connection property zeroDateTimeBehavior=convertToNull. Otherwise you’ll get conversion errors in Teiid that `0000-00-00 00:00:00' cannot be converted to a timestamp.
|If retrieving large result sets, you should consider setting the connection property useCursorFetch=true, otherwise MySQL will fully fetch result sets into memory on the Teiid instance.|
|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, you should change affected source BOOLEAN columns to have a native type of "TINYINT(1)" rather than BIT so that the translator can appropriately handle the boolean conversion.|