Get Result from Workbench but not in Metabase

Get Help
1

I can Get Result on MySql Workbench but can't get result from metabase

Error Shown

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when t' at line 3

SQL Query is

SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when type = ''',
          type,
          ''' then value end) AS ',
          replace(type, ' ', '')
        )
      ) INTO @sql
    from `tabProduct `;

    SET @sql = CONCAT('Select
    `tabTable2`.name,
    `tabTable2`.item_rate,
    ', @sql,'
    From
    `tabProduct ` Inner Join
    `tabTable2` On `tabTable2`.name = `tab Product`.link
    Group by
    `tabTable2`.name
    ;');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

Server Log IS

Query failure: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in > your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT

  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when t' at line 3

("clojure.core$ex_info.invokeStatic(core.clj:4739)"
"clojure.core$ex_info.invoke(core.clj:4739)"
"--> query_processor$assert_query_status_successful.invokeStatic(query_processor.clj:285)"
"query_processor$assert_query_status_successful.invoke(query_processor.clj:277)"
"query_processor$run_and_save_query_BANG_.invokeStatic(query_processor.clj:323)"
"query_processor$run_and_save_query_BANG_.invoke(query_processor.clj:316)"
"query_processor$fn__37732$process_query_and_save_execution_BANG___37737$fn__37738.invoke(query_processor.clj:354)"
"query_processor$fn__37732$process_query_and_save_execution_BANG___37737.invoke(query_processor.clj:340)"
"query_processor$fn__37756$process_query_and_save_with_max_BANG___37761$fn__37762.invoke(query_processor.clj:373)"
"query_processor$fn__37756$process_query_and_save_with_max_BANG___37761.invoke(query_processor.clj:369)"
"api.dataset$fn__44259$fn__44262.invoke(dataset.clj:45)"
"api.common$fn__23158$invoke_thunk_with_keepalive__23163$fn__23164$fn__23165.invoke(common.clj:435)")

2

Hi @Sagar
Which version of Metabase?
By default, Metabase doesn’t support multi-statement queries, but you can enable it with an JDBC option:

3

Thank you! @flamber for response
Metabase version is v0.31.2
Mariadb version is 10.2.22
and
I added

allowMultiQueries=true

but i got this error

java.sql.SQLException: ResultSet is from UPDATE. No Data.

4

@Sagar
Okay, I guess the query is doing something that resembles UPDATE, which Metabase does not allow - it’s a read-only system.
You should try using a View instead, so Metabase only sees the final result.
By the way, 0.32.2 was released yesterday and has updated drivers for MariaDB/MySQL, so that might make a difference. Always backup for upgrading.

5

Okay Let me try to upgrade…

6

Upgraded Metabase to v0.32.2, Built on 2019-04-03
While running the same Query i got No results!

No%20result
No%20result769×267 8.59 KB

7

@Sagar
Check the log for any errors. Otherwise use a View.