Uploaded image for project: 'OFBiz'
  1. OFBiz
  2. OFBIZ-6582

SQL Exceptions (OPTION) occuring when running OFBiz 13.07.02 against MySQL

    Details

    • 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
    • Labels:
      None
    • Environment:

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

      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

      1. error.log
        268 kB
        Morten Jensen
      2. ofbiz.log
        564 kB
        Morten Jensen
      3. OFBIZ-6582-ivy.xml.patch
        0.8 kB
        Pierre Smits

        Activity

        Hide
        morten@citizenme.com Morten Jensen added a comment -

        Log files from ofbiz installation - detailing both the MariaDB + MySQL exceptions

        Show
        morten@citizenme.com Morten Jensen added a comment - Log files from ofbiz installation - detailing both the MariaDB + MySQL exceptions
        Hide
        morten@citizenme.com Morten Jensen added a comment -

        Ok, after downloading the latest MySQL Connector/J library (5.1.36) by suggestion of someone from the OFBiz mailing list and re-populating the database problems went away. I would therefore suggest that " ./ant download-mySQL-JDBC" is updated to download a more recent release of MySQL Connector/J (5.1.6), which seemingly is from March 2008.

        For instance, according to http://dev.mysql.com/doc/connector-j/en/connector-j-versions.html, the current 5.1.36 release supports MySQL server releases all the way back to 4.1.

        Show
        morten@citizenme.com Morten Jensen added a comment - Ok, after downloading the latest MySQL Connector/J library (5.1.36) by suggestion of someone from the OFBiz mailing list and re-populating the database problems went away. I would therefore suggest that " ./ant download-mySQL-JDBC" is updated to download a more recent release of MySQL Connector/J (5.1.6), which seemingly is from March 2008. For instance, according to http://dev.mysql.com/doc/connector-j/en/connector-j-versions.html , the current 5.1.36 release supports MySQL server releases all the way back to 4.1.
        Hide
        pfm.smits Pierre Smits added a comment -

        This patch addresses the issue.

        Show
        pfm.smits Pierre Smits added a comment - This patch addresses the issue.
        Hide
        jacques.le.roux Jacques Le Roux added a comment -

        Thanks Pierre, Morten for the report and Vyom Jain for the fix

        Fixed in
        trunk r1697993
        R14.12 r1697994
        R13.07 r1697995
        R12.04 r1697996

        This updates the "MySQL Connector/J" from 5.1.6 to 5.1.36
        This follows OFBIZ-4800 where the "MySQL Connector/J 5.1.6" was used. See
        http://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.6
        and
        https://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1.html
        for details

        Show
        jacques.le.roux Jacques Le Roux added a comment - Thanks Pierre, Morten for the report and Vyom Jain for the fix Fixed in trunk r1697993 R14.12 r1697994 R13.07 r1697995 R12.04 r1697996 This updates the "MySQL Connector/J" from 5.1.6 to 5.1.36 This follows OFBIZ-4800 where the "MySQL Connector/J 5.1.6" was used. See http://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.6 and https://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1.html for details

          People

          • Assignee:
            pfm.smits Pierre Smits
            Reporter:
            morten@citizenme.com Morten Jensen
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development