Can metabase support mysql cluster datasource?

kurtiswen · July 14, 2020, 3:16am

hi everyone, I’m having problem when trying to add a mysql server as new database. the target mysql server is a cluster, and I’m getting error message like: Count not connect to address(host=xxx.64.xxx.41)(port=xxxx)(type=master): UDAL - You have an error in your SQL syntax; check the manual for the right syntax to use set autocommit=1, sql_mode = concat(@@sql_mode,’,STRICT_TRANS_TABLES’) near ‘1’ at line ‘{2}’
anyone know about this, please help, thanks a lot.

flamber · July 14, 2020, 9:30am

Hi @kurtiswen
Check the log in Metabase or MySQL for more details.
It would be helpful if you provided more information, like “Diagnostic Info” from Admin > Troubleshooting, and which version of MySQL.

kurtiswen · July 15, 2020, 11:46am

hi @flamber, please see the Diagnostic Info below:
{
“browser-info”: {
“language”: “zh-CN”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_162-b12”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_162”,
“java.vm.name”: “Java HotSpot™ 64-Bit Server VM”,
“java.vm.version”: “25.162-b12”,
“os.name”: “Linux”,
“os.version”: “3.10.0-693.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “Asia/Shanghai”
},
“metabase-info”: {
“databases”: [
“h2”,
“mysql”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.35.4”,
“date”: “2020-05-28”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: null
}
}
}
and the error log：
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.task.send-pulses Sending scheduled pulses…
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.util STARTING: Sync metadata for mysql Database 2 ‘cpcp_test’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.util STARTING: step ‘sync-timezone’ for mysql Database 2 ‘cpcp_test’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.util FINISHED: step ‘sync-timezone’ for mysql Database 2 ‘cpcp_test’ (3.2 ms)
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.util STARTING: step ‘sync-tables’ for mysql Database 2 ‘cpcp_test’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.sync-metadata.tables Found new tables: (Table ‘obj_attr_rstr_rul_templet’ Table ‘obj_attr_value_rstr_rul’)
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.sync-metadata.tables Updating description for tables: (Table ‘tb_bp_ru_service_statement’ Table ‘tariff_desc_detail’ Table ‘obj_attr_value_rstr_rul’ Table ‘template_attr_spec’ Table ‘tb_bp_ru_opr_rule_comp_rel’ Table ‘obj_attr_rstr_rul_templet’ Table ‘tb_bp_ru_dynamic_statement’ Table ‘tb_bp_ru_service’ Table ‘tb_bp_ru_rule_component’ Table ‘interim_pricing_desc’)
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.util FINISHED: step ‘sync-tables’ for mysql Database 2 ‘cpcp_test’ (74.4 ms)
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 INFO metabase.sync.util STARTING: step ‘sync-fields’ for mysql Database 2 ‘cpcp_test’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 316 ‘streamingnologmore’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 317 ‘offer_res_limit_obj_value’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 318 ‘vsop_offer_prod_rel’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 319 ‘prod_catalog_location’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 320 ‘offer_prod_rel_his’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 321 ‘warn_rule’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 322 ‘process_instance’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 323 ‘log_collection’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 324 ‘staff_community_rel’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 325 ‘offer_rel_role’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 326 ‘auto_test_process’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 327 ‘g_chn_formate’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 328 ‘proc_mode’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 329 ‘rule’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 330 ‘prod_attr_value_restrict_his’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 331 ‘compete_attr’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 332 ‘offer_rela_process’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 333 ‘business_rule_event’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 334 ‘prod_access_platform’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 335 ‘offer_inform_prog’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 336 ‘offer_fair_def_t’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 337 ‘offer_proc_mode_inst_rel’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 338 ‘repel_rule’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 339 ‘wfbiz_operation_info’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 340 ‘offer_res_rel’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 341 ‘proc_mode_rel’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 342 ‘eos_unique_table’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 343 ‘catalog_item’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 344 ‘busi_store’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 345 ‘wfagentscope’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 346 ‘sys_attr_value_region’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 347 ‘flow_process’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 348 ‘offer_package_restrict’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 349 ‘prd_prd_kind_rel_his’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 350 ‘ppm_system_user’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 351 ‘offer’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 352 ‘brand’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 353 ‘busi_inform_log’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 354 ‘tb_sys_oper_group’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 355 ‘product_relation_his’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 356 ‘request_process_rela_his’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 357 ‘tb_prd_ofr_fea_attr_comb’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 358 ‘offer_fair_derail_isedit’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 359 ‘offer_exp_detail_cfg’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 360 ‘operators_contact_info’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 361 ‘prod_res_rel_his’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 362 ‘tb_sys_prod_group’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 363 ‘tb_sys_staff_scope’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 364 ‘component_info’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 365 ‘prod_rel_eff_exp_cfg’’
[57805616-2d44-4d75-ae0d-2d2b410e1591] 2020-07-15T19:00:00+08:00 ERROR metabase.sync.util Error syncing Fields for Table ‘Table 366 ‘obj_count_limit_rul’’

now the problem is, we can add the mysql server successfully, but cannot get fields info of each table in the database. we don’t known full information about the target mysql server, we only known that it is a cluster, and maybe altered by another company for cloud。please help on this, thanks a lot!

flamber · July 15, 2020, 12:32pm

@kurtiswen

How did you get it to connect?

Which cloud provider? In Metabase, open the SQL editor, and run this code for the MySQL database to get the version info:

show variables like 'version%';

All the “Error syncing Fields for Table” errors will likely be fixed in 0.36.0, so you can try that version if you want:
https://github.com/metabase/metabase/releases/tag/v0.36.0-rc2

You should update your Java - I would recommend AdoptOpenJDK 11:
https://adoptopenjdk.net/releases.html?variant=openjdk11&jvmVariant=hotspot

And you should migrate away from H2 if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

kurtiswen · July 15, 2020, 12:47pm

hi @flamber,
we can manage to connect to the target mysql for the first time after deploy metabase again, but still we cannot add it again after delete it. since we can connect it the first time, we decide to ignore this for now. and then we encountered the problem of failed to get fields of each table. I will try to test this using 0.36.0 rc version tomorrow. thank you!

kurtiswen · July 21, 2020, 3:38am

hi @flamber, we still cannot access table fields info of the target mysql cluster using 0.36.0-rc2 version, I’m trying to collection log information, and will introduce it later. the mysql is delpoyed in private cloud server, so I don’t think this info could help.
the version info or mysql server is as below:
version 5.7.35-2.4.2-0841aaab47-log
version_comment Source distribution
version_compile_machine x86_64
version_compile_os Linux

however, we encountered other problems when trying to connect oracle 11g. it is an ordinary oracle 11g, but the target database contains too many tables (thousands of tables, and the db user has almost all access to these tables). then the problem is, it takes too long to get table list, and it’s also very slow when I try to see fields info of a table. can you please provide some guidance to make the performance better?
ps. 1. I’m still using h2 since it’s in testing stage, does this affect the above performance?
2. is there any configuration which can do some optimization about this, like thread pool or connection pool?
3. I assume metabase would store the table list and field info in cache, it is correct? but from what I see on the metabase page, every time I try to see the fields info of a selected table it takes a long time, does it access oracle server to get the fields info everytime instead of cache?

flamber · July 21, 2020, 9:22am

@kurtiswen
Try starting with more debug information and post the log errors during sync+scan process:

java -Dlog4j.configuration="https://log4j.us/templates/metabase?trace=metabase.sync" -jar metabase.jar

Oracle, that’s likely this - or one of the referenced issues - there’s work being done to only sync schemas/tables that are accessible, but that will likely not arrive in 0.36
https://github.com/metabase/metabase/issues/8955

Yes, H2 is less performant than a correctly setup Postgres/MySQL
If your log says that you’re running out of connections, then yes, but you haven’t posted anything that would indicate that.
https://github.com/metabase/metabase/blob/master/docs/operations-guide/environment-variables.md
The schema/table/columns are returned from the metadata, so the application database (H2 in your case). It should cache the request, but I don’t know where you’re seeing this. Check your browser console’s Network-tab for more information.