3 Replies Latest reply on May 18, 2016 4:21 AM by Shivaraj Kumar DN RSS
    bsmith Apprentice

    Reading and Writing to external MySQL database

    Anybody else had these problems accessing MySQL database?

    I've done a lot of research and can supply all the background detail but the problem is basically when building a Database Thing with a Query service.

     

    Important Note: the MySQL database is up, healthy and reachable.  In fact with case #1 (below) when using the standard "Database" template I can actually see the Tables/Columns when editing the service and can issue DDL against the database change service type to SQL(command) such as 'create table x as y" .

    Just can't issue any DML (select *  from table a) because of the bad syntax (OPTION) mentioned below.

    Setting the "Max Rows" to 0 in the service description has no effect.

     

    Any ideas or someone successfully selected from a MySQL database please advise.

     

    Thanks,

     

    Problem descriptions follow:

    ===================================================================================================

    Note there are two different errors depending on the Database template used:

     

    1. When I create a database Thing using the standard "Database" template, I get the following error executing my query service:

     

    Error executing service

     

    Unable to Invoke Service BSQueryTestTable on BSMySQLDatabaseTest : 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=500' at line 1

     

     

    2. When I create a database Thing using the mysql-connector-java-5137-binJDBCTemplate, I get the following error executing my query service:

     

    Error executing service

     

    Unable to Invoke Service BSQueryTestTable on BSMySQLDBTest : Cannot get a connection, pool error Unable to validate object

     

    Thanks,