matrixorigin / matrixone

Hyperconverged cloud-edge native database
https://docs.matrixorigin.cn/en
Apache License 2.0
1.78k stars 276 forks source link

[Bug]: Unable to connect to Tableau BI normally #11171

Open WuMenglong opened 1 year ago

WuMenglong commented 1 year ago

Is there an existing issue for the same bug?

Environment

- Version: v0.8.0
- Tableau: 2019.4 & 2023.2

Actual Behavior

1、ODBC连接报错 在 Tableau 中使用 MySQL连接器(ODBC方式)连接MatrixOne,会直接报错: [MySQL][ODBC 8.0(w) Driver]internal error: the system variable does not exist

image image

2、JDBC连接报错 使用 Tableau 中通用的 JDBC 方式,驱动使用 mysql-connector-j-8.0.33.jar,可以正常登录,登录后选择数据库时出现报错:SQL syntax error: column "tables.table_type" must appear in the GROUP BY clause or be used in an aggregate function. image image

3、调整SQL_MODE后不可用 根据 JDBC 下的报错,尝试调整 MatrixOne 的 SQL_MODE,去掉其中的 ONLY_FULL_GROUP_BY: set global sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'; 修改完成后,JDBC 连接时不再报错,但数据库中也无法正常的展示表信息。 image

Expected Behavior

No response

Steps to Reproduce

No response

Additional information

No response

dengn commented 1 year ago

below are failed SQLs. statement_info_202308091256.xlsx

YANGGMM commented 1 year ago

SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'abc' HAVING TABLE_TYPE IN ('LOCAL TEMPORARY','TABLE','VIEW',null,null) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME;

having的问题,执行返回empty set

aunjgr commented 1 year ago

mysql连接方式报错是找不到系统变量,建议errorSystemVariableDoesNotExist这个报错打印一下具体变量名,这样也许可以通过加一些系统变量的方式来达到兼容。

后面group by的错误是其他数据库(jdbc)这个连接方式报的,我们甚至都没法知道它用的什么标准。

aronchanisme commented 6 months ago

@WuMenglong 暂时有个绕行方法:可以先用mysql odbc 驱动版本:8.0.26试一下,见 https://github.com/matrixorigin/matrixone/issues/13997#issuecomment-2078555783

allengaoo commented 3 days ago

suggest support on version 2.1.0