Invalid DB type - DATETIME /* MARIADB-5.3 */

Question

Recently have upgraded the MariaDB version to 10.6.11-MariaDB from 10.2.13-MariaDB. Whenever we execute related jobs, getting below error

Error Code :-  100.123  
Error Message :-  Invalid DB type - DATETIME /* MARIADB-5.3 */

[2023-07-04 16:38:46,010InputMonitorTask] FATAL- Exception while monitoring database : 
com.suntecgroup.orm.exceptions.RDBMSORMException: Error Context :- JavaDBMapping.getJavaDBType  
Error Code :-  100.123  
Error Message :-  Invalid DB type - DATETIME /* MARIADB-5.3 */

    at com.suntecgroup.orm.tools.JavaDBMapping.getJavaDBType(JavaDBMapping.java:167)
    at com.suntecgroup.tbms.tpe.io.IOUtility.getFieldDataType(IOUtility.java:5082)
    at com.suntecgroup.tbms.tpe.io.IOUtility.validateDetails(IOUtility.java:4805)
    at com.suntecgroup.tbms.tpe.io.IOUtility.validateInputTableDefnConfig(IOUtility.java:4615)
    at com.suntecgroup.tbms.tpe.io.monitors.DataBaseMonitor.monitorEvent(DataBaseMonitor.java:284)
    at com.suntecgroup.tbms.tpe.core.tasks.InputMonitorTask.executeTask(InputMonitorTask.java:318)
    at com.suntecgroup.tbms.tpe.core.tasks.InterruptableTask.run(InterruptableTask.java:131)

how it can be resolved?

1. Tried adding STRICT_TRANS_TABLES,NO_ZERO_DATE in my.cnf file.sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ZERO_DATE'

2. In Connection URL added useMysqlMetadata=true:

3. Updated the java_db_mapping table db_type column to TimeStamp

Answer 1

The issue is that several table column definitions are not updated with the latest updated date/time after your upgrade.

Please run the below query & check the COLUMN_TYPE.

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.`COLUMNS` WHERE TABLE_SCHEMA = DATABASE() AND COLUMN_TYPE LIKE '%mariadb%';

You may need to update those table columns definitions individually. For example :

ALTER TABLE `cms_page`
MODIFY `creation_time`
timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Page Creation Time';