Using an old JDBC for mysql5 database

Patrice · January 12, 2017, 9:03pm

Hi,

I want to connect to an old mysql database with a specific jar file that works ? any ways to do that ?

Thanks,
Patrice.

sameer · January 12, 2017, 9:21pm

Do you get an error when trying to connect with a specific stable build?

We don’t any clean ways to replace the jdbc driver. You might be able to make a custom build and just use an older version, but we’re not going to be able to do much to help you if aspects of our metadata sync break.

Patrice · January 12, 2017, 9:27pm

I am using 0.22, the database is created so the connection works, the tables are available for question, but a simple select * is giving “You have an error in your SQL syntax”.

sameer · January 12, 2017, 9:40pm

can you give the example sql, and the specific mysql version you’re using?

There’s no reason it should fail.

Patrice · January 12, 2017, 9:52pm

I have this log when i create the database and it sync :

Unable to determine row count for ‘table_name’: null
[“db.metadata_queries$table_row_count.invokeStatic(metadata_queries.clj:28)”
“db.metadata_queries$table_row_count.invoke(metadata_queries.clj:23)”
“sync_database.analyze$table_row_count.invokeStatic(analyze.clj:41)”
“sync_database.analyze$table_row_count.invoke(analyze.clj:36)”
“util$wrap_try_catch$fn__6026.doInvoke(util.clj:586)”
“util$try_apply.invokeStatic(util.clj:610)”
“util$try_apply.doInvoke(util.clj:598)”
“sync_database.analyze$make_analyze_table$fn__23586.invoke(analyze.clj:186)”
“driver.generic_sql$analyze_table.invokeStatic(generic_sql.clj:405)”
“driver.generic_sql$analyze_table.invoke(generic_sql.clj:402)”
“driver$fn__18691$G__18496__18700.invoke(driver.clj:43)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invokeStatic(analyze.clj:205)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invoke(analyze.clj:200)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_$fn__23635.invoke(analyze.clj:243)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invokeStatic(analyze.clj:242)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invoke(analyze.clj:230)”
“sync_database$sync_database_with_tracking_BANG_$fn__24017.invoke(sync_database.clj:86)”
“sync_database$sync_database_with_tracking_BANG_.invokeStatic(sync_database.clj:74)”
“sync_database$sync_database_with_tracking_BANG_.invoke(sync_database.clj:68)”
“driver$fn__18770.invokeStatic(driver.clj:249)”
“driver$fn__18770.invoke(driver.clj:249)”
“driver$fn__18719$G__18502__18728.invoke(driver.clj:43)”
“sync_database$sync_database_BANG_.invokeStatic(sync_database.clj:43)”
“sync_database$sync_database_BANG_.doInvoke(sync_database.clj:24)”
“events.sync_database$process_sync_database_event$fn__36832.invoke(sync_database.clj:32)”]

When selecting database and just clicking get answer (all field no filter) I get :

Jan 12 15:56:07 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 12 15:56:07 ERROR metabase.query-processor :: {:status :failed,
:class java.lang.Exception,
:error “You have an error in your SQL syntax”,
:stacktrace
[“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:323)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:319)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:365)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:361)”
“driver$fn__18533$G__18526__18540.invoke(driver.clj:43)”
“query_processor$run_query.invokeStatic(query_processor.clj:431)”
“query_processor$run_query.invoke(query_processor.clj:415)”
“query_processor$pre_check_query_permissions$fn__22230.invoke(query_processor.clj:370)”
“query_processor$limit$fn__22215.invoke(query_processor.clj:342)”
“query_processor$cumulative_aggregation$fn__22209.invoke(query_processor.clj:322)”
“query_processor$cumulative_aggregation$fn__22209.invoke(query_processor.clj:322)”
“query_processor$post_format_rows$fn__22086.invoke(query_processor.clj:216)”
“query_processor$post_add_row_count_and_status$fn__22031.invoke(query_processor.clj:192)”
“query_processor$wrap_catch_exceptions$fn__22009.invoke(query_processor.clj:122)”
“query_processor$process_query.invokeStatic(query_processor.clj:479)”
“query_processor$process_query.invoke(query_processor.clj:470)”
“query_processor$dataset_query.invokeStatic(query_processor.clj:562)”
“query_processor$dataset_query.invoke(query_processor.clj:520)”
“api.dataset$fn__23009$fn__23012.invoke(dataset.clj:35)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)”
“api.dataset$fn__23009.invokeStatic(dataset.clj:29)”
“api.dataset$fn__23009.invoke(dataset.clj:29)”
“middleware$enforce_authentication$fn__31489.invoke(middleware.clj:84)”
“api.routes$fn__31597.invokeStatic(routes.clj:45)”
“api.routes$fn__31597.invoke(routes.clj:45)”
“routes$fn__32587.invokeStatic(routes.clj:24)”
“routes$fn__32587.invoke(routes.clj:24)”
“middleware$log_api_call$fn__31577$fn__31579.invoke(middleware.clj:275)”
“db$_do_with_call_counting.invokeStatic(db.clj:461)”
“db$_do_with_call_counting.invoke(db.clj:455)”
“middleware$log_api_call$fn__31577.invoke(middleware.clj:274)”
“middleware$add_security_headers$fn__31536.invoke(middleware.clj:209)”
“middleware$bind_current_user$fn__31493.invoke(middleware.clj:104)”],
:query
{:type “query”,
:query {:source_table 1929},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :card-id nil, :uuid “2df8354e-3cf5-431d-b1cf-d918985366d5”, :query-hash 1703790150, :query-type “MBQL”}},
:expanded-query
{:type “query”,
:query {:source-table {:schema nil, :name “table_name”, :id 1929}, :join-tables nil},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :card-id nil, :uuid “2df8354e-3cf5-431d-b1cf-d918985366d5”, :query-hash 1703790150, :query-type “MBQL”},
:fk-field-ids #{}}}

Jan 12 15:56:07 ERROR metabase.query-processor :: Query failure: You have an error in your SQL syntax
[“query_processor$assert_valid_query_result.invokeStatic(query_processor.clj:518)”
“query_processor$assert_valid_query_result.invoke(query_processor.clj:513)”
“query_processor$dataset_query.invokeStatic(query_processor.clj:563)”
“query_processor$dataset_query.invoke(query_processor.clj:520)”
“api.dataset$fn__23009$fn__23012.invoke(dataset.clj:35)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)”
“api.dataset$fn__23009.invokeStatic(dataset.clj:29)”
“api.dataset$fn__23009.invoke(dataset.clj:29)”
“middleware$enforce_authentication$fn__31489.invoke(middleware.clj:84)”
“api.routes$fn__31597.invokeStatic(routes.clj:45)”
“api.routes$fn__31597.invoke(routes.clj:45)”
“routes$fn__32587.invokeStatic(routes.clj:24)”
“routes$fn__32587.invoke(routes.clj:24)”
“middleware$log_api_call$fn__31577$fn__31579.invoke(middleware.clj:275)”
“db$_do_with_call_counting.invokeStatic(db.clj:461)”
“db$_do_with_call_counting.invoke(db.clj:455)”
“middleware$log_api_call$fn__31577.invoke(middleware.clj:274)”
“middleware$add_security_headers$fn__31536.invoke(middleware.clj:209)”
“middleware$bind_current_user$fn__31493.invoke(middleware.clj:104)”]

sameer · January 19, 2017, 11:25pm

what SQL is generated? it should be in the logs, or alternatively convert the question to sql?

Does that SQL run properly via another client? I.e. is it the jdbc driver or a specific sql syntax change. If the latter, then replacing the jdbc driver won’t actually do anything.

Patrice · January 19, 2017, 11:48pm

The query is a simple “select * from table”. Yes this works via other client using jdbc.

sameer · January 19, 2017, 11:51pm

is the name of the table “table”?

The specific error you’re seeing doesn’t make sense in that context. Can you share the table schema? I’m asking to try to understand what exactly is going wrong. The error message sound like it’s generating incorrect SQL, not that the JDBC driver wire format is off.

Patrice · January 20, 2017, 2:51pm

No , i replaced the table name. Let me create a dummy schema , and get back to you.

Thanks.

Patrice · January 20, 2017, 3:07pm

The following is at the creation of the database :

Jan 20 09:09:41 INFO metabase.sync-database :: Syncing MySQL database ‘metabase_gdb_test’…
Jan 20 09:09:41 INFO metabase.sync-database.introspect :: Introspecting schema on MySQL database ‘metabase_gdb_test’ …
Jan 20 09:09:41 DEBUG metabase.driver.generic-sql :: Creating new connection pool for database 14 …
Jan 20 09:09:41 DEBUG metabase.sync-database.introspect :: Found new table: table_myisam
Jan 20 09:09:41 INFO metabase.sync-database.introspect :: [*************************·························] 50% Synced table ‘table_myisam’.
Jan 20 09:09:41 DEBUG metabase.sync-database.introspect :: Found new table: table_infinidb
Jan 20 09:09:41 INFO metabase.sync-database.introspect :: [**************************************************] 100% Synced table ‘table_infinidb’.
Jan 20 09:09:41 INFO metabase.sync-database.introspect :: Introspection completed on MySQL database ‘metabase_gdb_test’ (30 ms)
Jan 20 09:09:41 INFO metabase.sync-database.analyze :: Analyzing data in MySQL database ‘metabase_gdb_test’ (this may take a while) …
Jan 20 09:09:41 DEBUG metabase.middleware :: GET /api/database 200 (2 ms) (2 DB calls)
Jan 20 09:09:41 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:41 ERROR metabase.sync-database.analyze :: Unable to determine row count for ‘table_infinidb’: null
[“db.metadata_queries$table_row_count.invokeStatic(metadata_queries.clj:28)”
“db.metadata_queries$table_row_count.invoke(metadata_queries.clj:23)”
“sync_database.analyze$table_row_count.invokeStatic(analyze.clj:41)”
“sync_database.analyze$table_row_count.invoke(analyze.clj:36)”
“util$wrap_try_catch$fn__6026.doInvoke(util.clj:586)”
“util$try_apply.invokeStatic(util.clj:610)”
“util$try_apply.doInvoke(util.clj:598)”
“sync_database.analyze$make_analyze_table$fn__23586.invoke(analyze.clj:186)”
“driver.generic_sql$analyze_table.invokeStatic(generic_sql.clj:405)”
“driver.generic_sql$analyze_table.invoke(generic_sql.clj:402)”
“driver$fn__18691$G__18496__18700.invoke(driver.clj:43)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invokeStatic(analyze.clj:205)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invoke(analyze.clj:200)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_$fn__23635.invoke(analyze.clj:243)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invokeStatic(analyze.clj:242)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invoke(analyze.clj:230)”
“sync_database$sync_database_with_tracking_BANG_$fn__24017.invoke(sync_database.clj:86)”
“sync_database$sync_database_with_tracking_BANG_.invokeStatic(sync_database.clj:74)”
“sync_database$sync_database_with_tracking_BANG_.invoke(sync_database.clj:68)”
“driver$fn__18770.invokeStatic(driver.clj:249)”
“driver$fn__18770.invoke(driver.clj:249)”
“driver$fn__18719$G__18502__18728.invoke(driver.clj:43)”
“sync_database$sync_database_BANG_.invokeStatic(sync_database.clj:43)”
“sync_database$sync_database_BANG_.doInvoke(sync_database.clj:24)”
“events.sync_database$process_sync_database_event$fn__36832.invoke(sync_database.clj:32)”]

Jan 20 09:09:41 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:41 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:42 INFO metabase.sync-database.analyze :: [*************************·························] 50% Analyzed table ‘table_infinidb’.
Jan 20 09:09:42 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:42 ERROR metabase.sync-database.analyze :: Unable to determine row count for ‘table_myisam’: null
[“db.metadata_queries$table_row_count.invokeStatic(metadata_queries.clj:28)”
“db.metadata_queries$table_row_count.invoke(metadata_queries.clj:23)”
“sync_database.analyze$table_row_count.invokeStatic(analyze.clj:41)”
“sync_database.analyze$table_row_count.invoke(analyze.clj:36)”
“util$wrap_try_catch$fn__6026.doInvoke(util.clj:586)”
“util$try_apply.invokeStatic(util.clj:610)”
“util$try_apply.doInvoke(util.clj:598)”
“sync_database.analyze$make_analyze_table$fn__23586.invoke(analyze.clj:186)”
“driver.generic_sql$analyze_table.invokeStatic(generic_sql.clj:405)”
“driver.generic_sql$analyze_table.invoke(generic_sql.clj:402)”
“driver$fn__18691$G__18496__18700.invoke(driver.clj:43)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invokeStatic(analyze.clj:205)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invoke(analyze.clj:200)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_$fn__23635.invoke(analyze.clj:243)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invokeStatic(analyze.clj:242)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invoke(analyze.clj:230)”
“sync_database$sync_database_with_tracking_BANG_$fn__24017.invoke(sync_database.clj:86)”
“sync_database$sync_database_with_tracking_BANG_.invokeStatic(sync_database.clj:74)”
“sync_database$sync_database_with_tracking_BANG_.invoke(sync_database.clj:68)”
“driver$fn__18770.invokeStatic(driver.clj:249)”
“driver$fn__18770.invoke(driver.clj:249)”
“driver$fn__18719$G__18502__18728.invoke(driver.clj:43)”
“sync_database$sync_database_BANG_.invokeStatic(sync_database.clj:43)”
“sync_database$sync_database_BANG_.doInvoke(sync_database.clj:24)”
“events.sync_database$process_sync_database_event$fn__36832.invoke(sync_database.clj:32)”]

Jan 20 09:09:42 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:42 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:42 INFO metabase.sync-database.analyze :: [] 100% Analyzed table ‘table_myisam’.
Jan 20 09:09:42 INFO metabase.sync-database.analyze :: Analysis of MySQL database ‘metabase_gdb_test’ completed (330 ms).
Jan 20 09:09:42 INFO metabase.sync-database :: Finished syncing MySQL database ‘metabase_gdb_test’. (599 ms)
Jan 20 09:09:46 DEBUG metabase.middleware :: GET /api/database/14 200 (723 µs) (2 DB calls)
Jan 20 09:09:47 DEBUG metabase.middleware :: POST /api/database/14/sync 200 (412 µs) (1 DB calls)
Jan 20 09:09:47 INFO metabase.sync-database :: Syncing MySQL database ‘metabase_gdb_test’…
Jan 20 09:09:47 INFO metabase.sync-database.introspect :: Introspecting schema on MySQL database ‘metabase_gdb_test’ …
Jan 20 09:09:47 INFO metabase.sync-database.introspect :: [*************************·························] 50% Synced table ‘table_myisam’.
Jan 20 09:09:47 INFO metabase.sync-database.introspect :: [] 100% Synced table ‘table_infinidb’.
Jan 20 09:09:47 INFO metabase.sync-database.introspect :: Introspection completed on MySQL database ‘metabase_gdb_test’ (12 ms)
Jan 20 09:09:47 INFO metabase.sync-database.analyze :: Analyzing data in MySQL database ‘metabase_gdb_test’ (this may take a while) …
Jan 20 09:09:47 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:47 ERROR metabase.sync-database.analyze :: Unable to determine row count for ‘table_infinidb’: null
[“db.metadata_queries$table_row_count.invokeStatic(metadata_queries.clj:28)”
“db.metadata_queries$table_row_count.invoke(metadata_queries.clj:23)”
“sync_database.analyze$table_row_count.invokeStatic(analyze.clj:41)”
“sync_database.analyze$table_row_count.invoke(analyze.clj:36)”
“util$wrap_try_catch$fn__6026.doInvoke(util.clj:586)”
“util$try_apply.invokeStatic(util.clj:610)”
“util$try_apply.doInvoke(util.clj:598)”
“sync_database.analyze$make_analyze_table$fn__23586.invoke(analyze.clj:186)”
“driver.generic_sql$analyze_table.invokeStatic(generic_sql.clj:405)”
“driver.generic_sql$analyze_table.invoke(generic_sql.clj:402)”
“driver$fn__18691$G__18496__18700.invoke(driver.clj:43)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invokeStatic(analyze.clj:205)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invoke(analyze.clj:200)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_$fn__23635.invoke(analyze.clj:243)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invokeStatic(analyze.clj:242)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invoke(analyze.clj:230)”
“sync_database$sync_database_with_tracking_BANG_$fn__24017.invoke(sync_database.clj:86)”
“sync_database$sync_database_with_tracking_BANG_.invokeStatic(sync_database.clj:74)”
“sync_database$sync_database_with_tracking_BANG_.invoke(sync_database.clj:68)”
“driver$fn__18770.invokeStatic(driver.clj:249)”
“driver$fn__18770.invoke(driver.clj:249)”
“driver$fn__18719$G__18502__18728.invoke(driver.clj:43)”
“sync_database$sync_database_BANG_.invokeStatic(sync_database.clj:43)”
“sync_database$sync_database_BANG_.doInvoke(sync_database.clj:24)”
“events.sync_database$process_sync_database_event$fn__36832.invoke(sync_database.clj:32)”]

Jan 20 09:09:47 INFO metabase.sync-database.analyze :: [*************************·························] 50% Analyzed table ‘table_infinidb’.
Jan 20 09:09:47 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:09:47 ERROR metabase.sync-database.analyze :: Unable to determine row count for ‘table_myisam’: null
[“db.metadata_queries$table_row_count.invokeStatic(metadata_queries.clj:28)”
“db.metadata_queries$table_row_count.invoke(metadata_queries.clj:23)”
“sync_database.analyze$table_row_count.invokeStatic(analyze.clj:41)”
“sync_database.analyze$table_row_count.invoke(analyze.clj:36)”
“util$wrap_try_catch$fn__6026.doInvoke(util.clj:586)”
“util$try_apply.invokeStatic(util.clj:610)”
“util$try_apply.doInvoke(util.clj:598)”
“sync_database.analyze$make_analyze_table$fn__23586.invoke(analyze.clj:186)”
“driver.generic_sql$analyze_table.invokeStatic(generic_sql.clj:405)”
“driver.generic_sql$analyze_table.invoke(generic_sql.clj:402)”
“driver$fn__18691$G__18496__18700.invoke(driver.clj:43)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invokeStatic(analyze.clj:205)”
“sync_database.analyze$analyze_table_data_shape_BANG_.invoke(analyze.clj:200)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_$fn__23635.invoke(analyze.clj:243)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invokeStatic(analyze.clj:242)”
“sync_database.analyze$analyze_data_shape_for_tables_BANG_.invoke(analyze.clj:230)”
“sync_database$sync_database_with_tracking_BANG_$fn__24017.invoke(sync_database.clj:86)”
“sync_database$sync_database_with_tracking_BANG_.invokeStatic(sync_database.clj:74)”
“sync_database$sync_database_with_tracking_BANG_.invoke(sync_database.clj:68)”
“driver$fn__18770.invokeStatic(driver.clj:249)”
“driver$fn__18770.invoke(driver.clj:249)”
“driver$fn__18719$G__18502__18728.invoke(driver.clj:43)”
“sync_database$sync_database_BANG_.invokeStatic(sync_database.clj:43)”
“sync_database$sync_database_BANG_.doInvoke(sync_database.clj:24)”
“events.sync_database$process_sync_database_event$fn__36832.invoke(sync_database.clj:32)”]

Jan 20 09:09:47 INFO metabase.sync-database.analyze :: [**************************************************] 100% Analyzed table ‘table_myisam’.
Jan 20 09:09:47 INFO metabase.sync-database.analyze :: Analysis of MySQL database ‘metabase_gdb_test’ completed (121 ms).
Jan 20 09:09:47 INFO metabase.sync-database :: Finished syncing MySQL database ‘metabase_gdb_test’. (351 ms)

Patrice · January 20, 2017, 3:15pm

For the first table :

Jan 20 09:14:11 DEBUG metabase.query-processor ::
PREPROCESSED/EXPANDED:
{:database
{:name “metabase_gdb_test”,
:id 14,
:engine :mysql,
:details " ",
:features (:basic-aggregations :standard-deviation-aggregations :expression-aggregations :foreign-keys :native-parameters :expressions :set-timezone)},
:type “query”,
:query
{:source-table {:name “table_infinidb”, :id 1986},
:fields-is-implicit true,
:fields
({:base-type :type/Text, :table-id 1986, :field-name “txt”, :field-display-name “Txt”, :visibility-type :normal, :position 0, :field-id 46340, :table-name “table_infinidb”}
{:base-type :type/Integer, :table-id 1986, :field-name “nm”, :field-display-name “Nm”, :visibility-type :normal, :position 0, :field-id 46339, :table-name “table_infinidb”}
{:field {:base-type :type/Date, :table-id 1986, :field-name “dt”, :field-display-name “Dt”, :visibility-type :normal, :position 0, :field-id 46338, :table-name “table_infinidb”}, :unit :default}),
:limit 2000},
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :uuid “bb92b854-9787-4347-bdac-11b56a7846b8”, :query-hash 723182794, :query-type “MBQL”},
:driver “MySQL”,
:settings {},
:fk-field-ids #{}}

Jan 20 09:14:11 DEBUG metabase.driver.generic-sql.query-processor :: HoneySQL Form:
{:from (:table_infinidb), :select ([:table_infinidb.txt :txt] [:table_infinidb.nm :nm] [:table_infinidb.dt :dt]), :limit 2000}

Jan 20 09:14:11 DEBUG metabase.query-processor :: NATIVE FORM:
{:query “SELECT table_infinidb.txt AS txt, table_infinidb.nm AS nm, table_infinidb.dt AS dt FROM table_infinidb LIMIT 2000”, :params nil}

Jan 20 09:14:11 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:14:11 ERROR metabase.query-processor :: {:status :failed,
:class java.lang.Exception,
:error “You have an error in your SQL syntax”,
:stacktrace
[“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:323)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:319)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:365)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:361)”
“driver$fn__18533$G__18526__18540.invoke(driver.clj:43)”
“query_processor$run_query.invokeStatic(query_processor.clj:431)”
“query_processor$run_query.invoke(query_processor.clj:415)”
“query_processor$pre_check_query_permissions$fn__22230.invoke(query_processor.clj:370)”
“query_processor$limit$fn__22215.invoke(query_processor.clj:342)”
“query_processor$cumulative_aggregation$fn__22209.invoke(query_processor.clj:322)”
“query_processor$cumulative_aggregation$fn__22209.invoke(query_processor.clj:322)”
“query_processor$post_format_rows$fn__22086.invoke(query_processor.clj:216)”
“query_processor$post_add_row_count_and_status$fn__22031.invoke(query_processor.clj:192)”
“query_processor$wrap_catch_exceptions$fn__22009.invoke(query_processor.clj:122)”
“query_processor$process_query.invokeStatic(query_processor.clj:479)”
“query_processor$process_query.invoke(query_processor.clj:470)”
“query_processor$dataset_query.invokeStatic(query_processor.clj:562)”
“query_processor$dataset_query.invoke(query_processor.clj:520)”
“api.dataset$fn__23009$fn__23012.invoke(dataset.clj:35)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)”
“api.dataset$fn__23009.invokeStatic(dataset.clj:29)”
“api.dataset$fn__23009.invoke(dataset.clj:29)”
“middleware$enforce_authentication$fn__31489.invoke(middleware.clj:84)”
“api.routes$fn__31597.invokeStatic(routes.clj:45)”
“api.routes$fn__31597.invoke(routes.clj:45)”
“routes$fn__32587.invokeStatic(routes.clj:24)”
“routes$fn__32587.invoke(routes.clj:24)”
“middleware$log_api_call$fn__31577$fn__31579.invoke(middleware.clj:275)”
“db$_do_with_call_counting.invokeStatic(db.clj:461)”
“db$_do_with_call_counting.invoke(db.clj:455)”
“middleware$log_api_call$fn__31577.invoke(middleware.clj:274)”
“middleware$add_security_headers$fn__31536.invoke(middleware.clj:209)”
“middleware$bind_current_user$fn__31493.invoke(middleware.clj:104)”],
:query
{:type “query”,
:query {:source_table 1986},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :card-id nil, :uuid “bb92b854-9787-4347-bdac-11b56a7846b8”, :query-hash 723182794, :query-type “MBQL”}},
:expanded-query
{:type “query”,
:query {:source-table {:schema nil, :name “table_infinidb”, :id 1986}, :join-tables nil},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :card-id nil, :uuid “bb92b854-9787-4347-bdac-11b56a7846b8”, :query-hash 723182794, :query-type “MBQL”},
:fk-field-ids #{}}}

Jan 20 09:14:11 ERROR metabase.query-processor :: Query failure: You have an error in your SQL syntax
[“query_processor$assert_valid_query_result.invokeStatic(query_processor.clj:518)”
“query_processor$assert_valid_query_result.invoke(query_processor.clj:513)”
“query_processor$dataset_query.invokeStatic(query_processor.clj:563)”
“query_processor$dataset_query.invoke(query_processor.clj:520)”
“api.dataset$fn__23009$fn__23012.invoke(dataset.clj:35)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)”
“api.dataset$fn__23009.invokeStatic(dataset.clj:29)”
“api.dataset$fn__23009.invoke(dataset.clj:29)”
“middleware$enforce_authentication$fn__31489.invoke(middleware.clj:84)”
“api.routes$fn__31597.invokeStatic(routes.clj:45)”
“api.routes$fn__31597.invoke(routes.clj:45)”
“routes$fn__32587.invokeStatic(routes.clj:24)”
“routes$fn__32587.invoke(routes.clj:24)”
“middleware$log_api_call$fn__31577$fn__31579.invoke(middleware.clj:275)”
“db$_do_with_call_counting.invokeStatic(db.clj:461)”
“db$_do_with_call_counting.invoke(db.clj:455)”
“middleware$log_api_call$fn__31577.invoke(middleware.clj:274)”
“middleware$add_security_headers$fn__31536.invoke(middleware.clj:209)”
“middleware$bind_current_user$fn__31493.invoke(middleware.clj:104)”]

Jan 20 09:14:11 DEBUG metabase.middleware :: POST /api/dataset 200 (209 ms) (12 DB calls)

For the second table :

Jan 20 09:16:38 DEBUG metabase.query-processor ::
PREPROCESSED/EXPANDED:
{:database
{:name “metabase_gdb_test”,
:id 14,
:engine :mysql,
:details " ",
:features (:basic-aggregations :standard-deviation-aggregations :expression-aggregations :foreign-keys :native-parameters :expressions :set-timezone)},
:type “query”,
:query
{:source-table {:name “table_myisam”, :id 1987},
:fields-is-implicit true,
:fields
({:base-type :type/Text, :table-id 1987, :field-name “txt”, :field-display-name “Txt”, :visibility-type :normal, :position 0, :field-id 46343, :table-name “table_myisam”}
{:base-type :type/Integer, :table-id 1987, :field-name “nm”, :field-display-name “Nm”, :visibility-type :normal, :position 0, :field-id 46342, :table-name “table_myisam”}
{:field {:base-type :type/Date, :table-id 1987, :field-name “dt”, :field-display-name “Dt”, :visibility-type :normal, :position 0, :field-id 46341, :table-name “table_myisam”}, :unit :default}),
:limit 2000},
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :uuid “6d7b6c20-b23d-4787-94d6-3aba7716199b”, :query-hash 1608300168, :query-type “MBQL”},
:driver “MySQL”,
:settings {},
:fk-field-ids #{}}

Jan 20 09:16:38 DEBUG metabase.driver.generic-sql.query-processor :: HoneySQL Form:
{:from (:table_myisam), :select ([:table_myisam.txt :txt] [:table_myisam.nm :nm] [:table_myisam.dt :dt]), :limit 2000}

Jan 20 09:16:38 DEBUG metabase.query-processor :: NATIVE FORM:
{:query “SELECT table_myisam.txt AS txt, table_myisam.nm AS nm, table_myisam.dt AS dt FROM table_myisam LIMIT 2000”, :params nil}

Jan 20 09:16:38 ERROR metabase.driver.generic-sql.query-processor :: nil
Jan 20 09:16:38 ERROR metabase.query-processor :: {:status :failed,
:class java.lang.Exception,
:error “You have an error in your SQL syntax”,
:stacktrace
[“driver.generic_sql.query_processor$do_with_try_catch.invokeStatic(query_processor.clj:323)”
“driver.generic_sql.query_processor$do_with_try_catch.invoke(query_processor.clj:319)”
“driver.generic_sql.query_processor$execute_query.invokeStatic(query_processor.clj:365)”
“driver.generic_sql.query_processor$execute_query.invoke(query_processor.clj:361)”
“driver$fn__18533$G__18526__18540.invoke(driver.clj:43)”
“query_processor$run_query.invokeStatic(query_processor.clj:431)”
“query_processor$run_query.invoke(query_processor.clj:415)”
“query_processor$pre_check_query_permissions$fn__22230.invoke(query_processor.clj:370)”
“query_processor$limit$fn__22215.invoke(query_processor.clj:342)”
“query_processor$cumulative_aggregation$fn__22209.invoke(query_processor.clj:322)”
“query_processor$cumulative_aggregation$fn__22209.invoke(query_processor.clj:322)”
“query_processor$post_format_rows$fn__22086.invoke(query_processor.clj:216)”
“query_processor$post_add_row_count_and_status$fn__22031.invoke(query_processor.clj:192)”
“query_processor$wrap_catch_exceptions$fn__22009.invoke(query_processor.clj:122)”
“query_processor$process_query.invokeStatic(query_processor.clj:479)”
“query_processor$process_query.invoke(query_processor.clj:470)”
“query_processor$dataset_query.invokeStatic(query_processor.clj:562)”
“query_processor$dataset_query.invoke(query_processor.clj:520)”
“api.dataset$fn__23009$fn__23012.invoke(dataset.clj:35)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)”
“api.dataset$fn__23009.invokeStatic(dataset.clj:29)”
“api.dataset$fn__23009.invoke(dataset.clj:29)”
“middleware$enforce_authentication$fn__31489.invoke(middleware.clj:84)”
“api.routes$fn__31597.invokeStatic(routes.clj:45)”
“api.routes$fn__31597.invoke(routes.clj:45)”
“routes$fn__32587.invokeStatic(routes.clj:24)”
“routes$fn__32587.invoke(routes.clj:24)”
“middleware$log_api_call$fn__31577$fn__31579.invoke(middleware.clj:275)”
“db$_do_with_call_counting.invokeStatic(db.clj:461)”
“db$_do_with_call_counting.invoke(db.clj:455)”
“middleware$log_api_call$fn__31577.invoke(middleware.clj:274)”
“middleware$add_security_headers$fn__31536.invoke(middleware.clj:209)”
“middleware$bind_current_user$fn__31493.invoke(middleware.clj:104)”],
:query
{:type “query”,
:query {:source_table 1987},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :card-id nil, :uuid “6d7b6c20-b23d-4787-94d6-3aba7716199b”, :query-hash 1608300168, :query-type “MBQL”}},
:expanded-query
{:type “query”,
:query {:source-table {:schema nil, :name “table_myisam”, :id 1987}, :join-tables nil},
:parameters [],
:constraints {:max-results 10000, :max-results-bare-rows 2000},
:info {:executed-by 1, :card-id nil, :uuid “6d7b6c20-b23d-4787-94d6-3aba7716199b”, :query-hash 1608300168, :query-type “MBQL”},
:fk-field-ids #{}}}

Jan 20 09:16:38 ERROR metabase.query-processor :: Query failure: You have an error in your SQL syntax
[“query_processor$assert_valid_query_result.invokeStatic(query_processor.clj:518)”
“query_processor$assert_valid_query_result.invoke(query_processor.clj:513)”
“query_processor$dataset_query.invokeStatic(query_processor.clj:563)”
“query_processor$dataset_query.invoke(query_processor.clj:520)”
“api.dataset$fn__23009$fn__23012.invoke(dataset.clj:35)”
“api.common.internal$do_with_caught_api_exceptions.invokeStatic(internal.clj:222)”
“api.common.internal$do_with_caught_api_exceptions.invoke(internal.clj:219)”
“api.dataset$fn__23009.invokeStatic(dataset.clj:29)”
“api.dataset$fn__23009.invoke(dataset.clj:29)”
“middleware$enforce_authentication$fn__31489.invoke(middleware.clj:84)”
“api.routes$fn__31597.invokeStatic(routes.clj:45)”
“api.routes$fn__31597.invoke(routes.clj:45)”
“routes$fn__32587.invokeStatic(routes.clj:24)”
“routes$fn__32587.invoke(routes.clj:24)”
“middleware$log_api_call$fn__31577$fn__31579.invoke(middleware.clj:275)”
“db$_do_with_call_counting.invokeStatic(db.clj:461)”
“db$_do_with_call_counting.invoke(db.clj:455)”
“middleware$log_api_call$fn__31577.invoke(middleware.clj:274)”
“middleware$add_security_headers$fn__31536.invoke(middleware.clj:209)”
“middleware$bind_current_user$fn__31493.invoke(middleware.clj:104)”]

Jan 20 09:16:38 DEBUG metabase.middleware :: POST /api/dataset 200 (200 ms) (12 DB calls)

table description :

MySQL [metabase]> describe table_infinidb;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| txt | varchar(16) | YES | | NULL | |
| dt | date | YES | | NULL | |
| nm | int(11) | YES | | NULL | |
±------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

MySQL [metabase]> describe table_myisam;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| txt | varchar(16) | YES | | NULL | |
| dt | date | YES | | NULL | |
| nm | int(11) | YES | | NULL | |
±------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

Let me know if you need more,

sameer · January 22, 2017, 7:26pm

It looks like the root cause of the error is in that generated SQL.

If I had to guess, I expect that GitHub - seancorfield/honeysql: Turn Clojure data structures into SQL, which is the library we use to convert our parsed versions of SQL queries into specific SQL dialects isn't working properly with the version of MySQL you're using. We're probably not going to be able to fix that. What specific version number are you on? Eg, MySQL 5.0 was released in 2005, and we're probably not going to be able to fix an upstream library to support it. =(

This won't be fixed by using an older version of the JDBC driver (I think).