[OFBIZ-6582] SQL Exceptions (OPTION) occuring when running OFBiz 13.07.02 against MySQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 13.07.02
• Fix Version/s: 14.12.01, 12.04.06, 13.07.03, 16.11.01
• Component/s: ALL COMPONENTS

Environment

Ubuntu 15.04 LTS Server x86_64
Server version: 5.6.25-0ubuntu0.15.04.1 (Ubuntu)
OFBiz 13.07.02

• Assignee: Pierre Smits
• Reporter: Morten Jensen
• Created: 26/Aug/15 13:24
• Updated: 26/Aug/15 19:40
• Resolved: 26/Aug/15 19:40

Description

Standard installation from installation documentation - before installing schema/data I reconfigured OFBiz to run against MySQL.

Issue occurs for some SQL
statements during load of data and after start-up - but in this case the specific OPTION invoked is revealed as
"SQL_SELECT_LIMIT=1" (or default - or whatever).

A quick grep through the enclosed java code the only location I see the
string "SQL_SELECT_LIMIT" is in an array in
framework/entity/src/org/ofbiz/entity/model/ModelEntityChecker.java.

Btw. for reference, I installed the mysql JDBC drivers as per the build
documentation: ./ant download-mySQL-JDBC.

Similar issue also occurs against MariaDB although error message is slightly different.

More information on the issue here: http://ofbiz.135035.n4.nabble.com/OFBiz-MySQL-installation-Caused-by-java-sql-SQLException-Unknown-system-variable-OPTION-td4671579.html

org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing
the following:SELECT JOB_ID, JOB_NAME, RUN_TIME, POOL_ID, STATUS_ID,
PARENT_JOB_ID, PREVIOUS_JOB_ID, SERVICE_NAME, LOADER_NAME, MAX_RETRY,
CURRENT_RETRY_COUNT, AUTH_USER_LOGIN_ID, RUN_AS_USER, RUNTIME_DATA_ID,
RECURRENCE_INFO_ID, TEMP_EXPR_ID, CURRENT_RECURRENCE_COUNT,
MAX_RECURRENCE_COUNT, RUN_BY_INSTANCE_ID, START_DATE_TIME,
FINISH_DATE_TIME, CANCEL_DATE_TIME, JOB_RESULT, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM JOB_SANDBOX
WHERE ((STATUS_ID = ? AND SERVICE_NAME = ?)) ORDER BY START_DATE_TIME DESC
(You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'OPTION
SQL_SELECT_LIMIT=1' at line 1)
runtime/logs/error.log:Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=1'
at line 1