Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Enhanced MySQL compatibility #9336

Open
meob opened this issue Feb 24, 2020 · 23 comments
Open

Enhanced MySQL compatibility #9336

meob opened this issue Feb 24, 2020 · 23 comments
Labels
comp-foreign-db Integrations with other databases comp-mysql feature

Comments

@meob
Copy link
Contributor

meob commented Feb 24, 2020

Use case
The basic idea is to increase the MySQL compatibility with wire protocol in order to have tools like phpMyAdmin, DBeaver, ... working easily on ClickHouse (reading data)

Describe the solution you'd like
ClickHouse MySQL wire protocol works fine with application (eg. PHP, Perl, ...) but has problems with MySQL tools since they use "custom" SQL commands that MySQL understand while ClickHouse gives problems.
We propose to add a flag that enhance MySQL compatibility.
The solution requires two approach:

  1. Support more MySQL command
    There are commands like "SET NAMES utf8", "SET autocommit=1", ... that have no meaning
    and can simply do nothing in CH but reply OK if the flag is active
  2. Create the "mysql" data dictionary database
    This can be easly created in ClickHouse using VIEWs from SYSTEM

The goal is to reach a "read only" compatibility with MySQL.
Creating CH data structures or traslating DML language requires much more work
and is not needed.

Describe alternatives you've considered
I used a simple self-made proxy to skip MySQL custom command... and it works!

Additional context
This compatibility could enable CH+wire protocol to be used as a MySQL DB with Linked Server in SQLServer, FDW in PostgreSQL, Oracle gateway, ...

I can provide the list of commands, variables, data dictionary views I have found till now...

@meob meob added the feature label Feb 24, 2020
@alex-zaitsev
Copy link
Contributor

@meob , yes please add all incompatible commands that you have found so far. It will help a lot with implementation and testing.

One more requirement: compatibility mode should also support double quotes for strings, as MySQL does.

@filimonov filimonov added the comp-foreign-db Integrations with other databases label Feb 24, 2020
@filimonov
Copy link
Contributor

One more requirement: compatibility mode should also support double quotes for strings, as MySQL does.

This one is very questionable, as MySQL behavior in that part is not standard and adjustable:
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_ansi_quotes

@alex-zaitsev
Copy link
Contributor

One more requirement: compatibility mode should also support double quotes for strings, as MySQL does.

This one is very questionable, as MySQL behavior in that part is not standard and adjustable:
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_ansi_quotes

Yes, it is not standard, but it is default MySQL behaviour. If we want compatibility mode it makes sense to be compatible with defaults.

@meob
Copy link
Contributor Author

meob commented Feb 26, 2020

I collected the SQL statements used by some tipical MySQL tools.
I used a legacy phpMyAdmin version since it is the easiest to hack.
Then the latest pgpMyAdmin version that is a bit more difficoult
to understand since it uses different threads connected to the database.
The latest one is DBeaver in which the first statement is performed
by the JDBC driver while the latter ones are performed by DBeaver itself.

phpMyAdmin 3.5.5 (Legacy)
SELECT @@Version, @@version_comment
SELECT * FROM information_schema.CHARACTER_SETS
SELECT * FROM information_schema.COLLATIONS
SHOW PLUGINS
SHOW DATABASES
SHOW TABLE STATUS FROM dd
SHOW TABLE STATUS FROM information_schema
SHOW TABLE STATUS FROM my2
SHOW TABLE STATUS FROM mysql
SHOW TABLE STATUS FROM performance_schema
SHOW TABLE STATUS FROM sys
... The same query for all MySQL databases
SELECT COUNT(*) FROM mysql.user
SHOW MASTER LOGS
SELECT USER()
SHOW GRANTS
SELECT 1 FROM information_schema.CHARACTER_SETS LIMIT 100000
SELECT 1 FROM information_schema.COLLATIONS LIMIT 100000
... The same query for all tables shown in the left menu

phpMyAdmin 5.0.1 (Latest version)
SELECT @@Version, @@version_comment
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'
SET lc_messages = 'en_US' SELECT CURRENT_USER()
SHOW GRANTS
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
SELECT 1 FROM (SELECT GRANTEE, IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES UNION SELECT GRANTEE, IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES UNION SELECT GRANTEE, IS_GRANTABLE FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES UNION SELECT GRANTEE, IS_GRANTABLE FROM INFORMATION_SCHEMA.USER_PRIVILEGES) t WHERE IS_GRANTABLE = 'YES' AND '''meo''@''%''' LIKE GRANTEE LIMIT 1
SELECT 1 FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'CREATE USER' AND '''meo''@''%''' LIKE GRANTEE LIMIT 1
SELECT 1 FROM mysql.user LIMIT 1
SELECT CHARACTER_SET_NAME AS Charset, DEFAULT_COLLATE_NAME AS Default collation, DESCRIPTION AS Description, MAXLEN AS Maxlen FROM information_schema.CHARACTER_SETS
SELECT COLLATION_NAME AS Collation, CHARACTER_SET_NAME AS Charset, ID AS Id, IS_DEFAULT AS Default, IS_COMPILED AS Compiled, SORTLEN AS Sortlen FROM information_schema.COLLATIONS
SHOW SESSION VARIABLES LIKE 'character_set_server'
SELECT USER()
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA, (SELECT DB_first_level FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t ORDER BY DB_first_level ASC LIMIT 0, 100) t2 WHERE TRUE AND 1 = LOCATE(CONCAT(DB_first_level, ''), CONCAT(SCHEMA_NAME, '')) ORDER BY SCHEMA_NAME ASC
SELECT COUNT(*) FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '
', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t
SHOW MASTER LOGS

DBeaver 6.3.5 (Latest version)
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) /SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
SET NAMES utf8
SET character_set_results = NULL
SET autocommit=1
/
ApplicationName=DBeaver 6.3.5 - Main / SET autocommit=1
/
ApplicationName=DBeaver 6.3.5 - Main / SELECT DATABASE()
/
ApplicationName=DBeaver 6.3.5 - Metadata / SHOW ENGINES
/
ApplicationName=DBeaver 6.3.5 - Metadata / SHOW CHARSET
/
ApplicationName=DBeaver 6.3.5 - Metadata / SHOW COLLATION
/
ApplicationName=DBeaver 6.3.5 - Metadata / SELECT @@GLOBAL.character_set_server,@@GLOBAL.collation_server
/
ApplicationName=DBeaver 6.3.5 - Metadata / SHOW VARIABLES LIKE 'lower_case_table_names'
/
ApplicationName=DBeaver 6.3.5 - Metadata */ show databases

BohuTANG added a commit to BohuTANG/ClickHouse that referenced this issue Apr 2, 2020
…-java setup for MySQL Handler ClickHouse#9336

mysql-connector setup query:
/* mysql-connector-java-5.1.38 ( Revision: ${revinfo.commit} ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout...

ClickHouse side Error:
{} <Error> executeQuery: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 74: @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_conn. Expected one of: CAST, NULL...

Client side Exception:
java.sql.SQLException: Syntax error: failed at position 74: @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_conn. Expected one of: CAST...
@BohuTANG
Copy link
Contributor

BohuTANG commented Apr 3, 2020

Comments:

mysql-connector-java-5.1.34:
/* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect' 2020.04.03 16:32:21.671607 [ 4101 ] {} <Error> executeQuery: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 108: VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR V. Expected one of: CREATE, GRANTS, QUOTA USAGE, QUOTAS, POLICIES, DICTIONARIES, TABLES, PROCESSLIST, CREATE, ROW POLICIES (version 20.4.1.1) (from 127.0.0.1:57474) (in query: /* mysql-connector-java-5.1.34 ( Revision: jess.balint@oracle.com-20141014163213-wqbwpf1ok2kvo1om ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'

blinkov pushed a commit that referenced this issue Apr 8, 2020
* Skip the `/* comments */ SELECT @@variables ...` from mysql-connector-java setup for MySQL Handler #9336

mysql-connector setup query:
/* mysql-connector-java-5.1.38 ( Revision: ${revinfo.commit} ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout...

ClickHouse side Error:
{} <Error> executeQuery: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 74: @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_conn. Expected one of: CAST, NULL...

Client side Exception:
java.sql.SQLException: Syntax error: failed at position 74: @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_conn. Expected one of: CAST...

* add repalce 'SHOW VARIABLES' for mysql-connector-java-5.1.34 #9336

* Add java client(JDBC) integration test to test_mysql_protocol

* shift out java tests from dbms

* Update MySQLHandler.cpp

* Update MySQLHandler.cpp

* test_mysql_protocol: add Test.java exit code 1 when expection

Co-authored-by: alexey-milovidov <milovidov@yandex-team.ru>
@BohuTANG
Copy link
Contributor

Comments:

There are already 3 patches in place to address this compatibility issue:
[0] #10021
[1] #10140
[2] #10199

They addressed the SET commands:

  • SET NAMES ...
  • SET character_set_results ...
  • SET FOREIGN_KEY_CHECKS ...
  • SET AUTOCOMMIT ...
  • SET sql_mode ...
  • SET SESSION TRANSACTION ISOLATION LEVEL ...

These SET commands will return OK to the client/driver-side.

SELECT commands:

  • SELECT @@... (match this pattern)
  • /* ... */ SELECT @@... (match this pattern)
  • /* ... */ SHOW VARIABLES ... (match this pattern)

These SELECT commands will return max_allowed_packet by default.

@meob
Copy link
Contributor Author

meob commented Apr 13, 2020

:)

@BohuTANG
Copy link
Contributor

Comments:

workbench 8.0:

 SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
2020.04.22 18:05:14.519488 [ 19356 ] {} <Error> executeQuery: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 6: SESSION VARIABLES LIKE 'lower_case_table_names'. Expected one of: PROCESSLIST, CREATE, GRANTS, QUOTAS, QUOTA USAGE, POLICIES, ROW POLICIES, TABLES, DICTIONARIES, CREATE (version 20.4.1.1) (from 127.0.0.1:37774) (in query: SHOW SESSION VARIABLES LIKE 'lower_case_table_names'), Stack trace (when copying this message, always include the lines below):

@BohuTANG
Copy link
Contributor

BohuTANG commented Jun 25, 2020

Comments:
Add select @@version #11815

@BohuTANG
Copy link
Contributor

BohuTANG commented Jul 7, 2020

Comments:
Add KILL QUERY [connection_id] #12152

@BohuTANG
Copy link
Contributor

Comments:
Add SELECT DATABASE() #12314

@AlexeyMatskevich
Copy link

I have the same problem for 20.7.2.30

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 5 ('@@'): @@wait_timeout = 2147483. Expected one of: ROLE, identifier, ROLE DEFAULT, DEFAULT ROLE (version 20.7.2.30 (official build))

Is there any news about this?

@BohuTANG
Copy link
Contributor

BohuTANG commented Sep 4, 2020

I have the same problem for 20.7.2.30

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 5 ('@@'): @@wait_timeout = 2147483. Expected one of: ROLE, identifier, ROLE DEFAULT, DEFAULT ROLE (version 20.7.2.30 (official build))

Is there any news about this?

Not support SET @@var=x yet

@BohuTANG
Copy link
Contributor

BohuTANG commented Sep 4, 2020

Comments:
Add SET @@var=x #14469

@maqroll
Copy link
Contributor

maqroll commented Sep 23, 2020

Most prefab queries use just UNION. MySQL assumes DISTINCT by default. CK fails. Wouldn't be nice to have a setting to establish default behaviour of UNION to UNION ALL (and set it by default on mysql sessions)?

@siradjev
Copy link

.NET driver needs TIMEDIFF function:
Code: 46, e.displayText() = DB::Exception: Unknown function TIMEDIFF. Maybe you meant: ['dateDiff'] (version 20.8.9.6 (official build))

@jploski
Copy link

jploski commented Dec 26, 2021

While trying to connect to Clickhouse using MySQL Workbench I ran into those:

show character set where charset = .*
SELECT current_user()
SHOW SESSION VARIABLES LIKE .*
SHOW SESSION STATUS LIKE .*
SET NAMES .*
SET CHARACTER SET .*
SELECT CONNECTION_ID()
SHOW FULL TABLES FROM .*
SHOW ENGINES
SHOW CHARSET
SHOW COLLATION

I was able to rewrite all of them using ProxySQL's mysql_query_rules.

But there is one which I cannot work around because it seems to be exempt from rewriting (sysown/proxysql#3736):

SET character_set_client=utf8;

Edit: I managed to work around it by hacking ProxySQL.

@jbenguira
Copy link

Any progress on this issue? It would really be lovely to have MySQL tooling working (workbench, PHPMyAdmin)

@alexey-milovidov
Copy link
Member

@jbenguira Hi! There is no progress on this issue.

@devicenull
Copy link

We hit a similar issue with PowerBI - it tries to run this query:

SELECT @@max_allowed_packet, @@character_set_client, @@character_set_connection, @@license, @@sql_mode, @@lower_case_table_names, @@autocommit

When it first opens the connection, which results in clickhouse throwing:

DB::Exception: Syntax error: failed at position 6 ('COLLATION'): COLLATION. Expected one of: CREATE, FULL, DATABASES, CLUSTERS, FILESYSTEM CACHES, CLUSTER, CHANGED, SETTINGS, TABLES, DICTIONARIES, CREATE, PROCESSLIST, ACCESS, USERS, ROLES, SETTINGS PROFILES, PROFILES, ROW POLICIES, POLICIES, QUOTAS, CURRENT ROLES, ENABLED ROLES, CURRENT QUOTA, QUOTA, GRANTS, PRIVILEGES. (SYNTAX_ERROR) (version 22.12.3.5 (official build))

@alexey-milovidov
Copy link
Member

@devicenull the query works:

milovidov@milovidov-desktop:~/work/ClickHouse/utils/changelog$ clickhouse-local 
ClickHouse local version 22.13.1.1.

milovidov-desktop :) SELECT @@max_allowed_packet, @@character_set_client, @@character_set_connection, @@license, @@sql_mode, @@lower_case_table_names, @@autocommit
                     

SELECT
    globalVariable('max_allowed_packet') AS `@@max_allowed_packet`,
    globalVariable('character_set_client') AS `@@character_set_client`,
    globalVariable('character_set_connection') AS `@@character_set_connection`,
    globalVariable('license') AS `@@license`,
    globalVariable('sql_mode') AS `@@sql_mode`,
    globalVariable('lower_case_table_names') AS `@@lower_case_table_names`,
    globalVariable('autocommit') AS `@@autocommit`

Query id: b4bb0656-5c95-4fec-8673-9810f2455629

┌─@@max_allowed_packet─┬─@@character_set_client─┬─@@character_set_connection─┬─@@license─┬─@@sql_mode─┬─@@lower_case_table_names─┬─@@autocommit─┐
│             67108864 │                      0 │                          0 │         0 │          0 │                        0 │            0 │
└──────────────────────┴────────────────────────┴────────────────────────────┴───────────┴────────────┴──────────────────────────┴──────────────┘

1 row in set. Elapsed: 0.002 sec.

@devicenull
Copy link

I agree, it works via clickhouse-client, but doesn't appear to work via the mysql port. Either way, we've moved to OBDC access for PowerBI, which is working fine.

@LUAgam
Copy link

LUAgam commented Jul 4, 2023

Does jdbc's allowMultiQueries = true require support?

dveeden added a commit to dveeden/ClickHouse that referenced this issue Aug 19, 2023
Basically `DATABASE()` and `SCHEMA()` are aliases in MySQL.

This helps with allowing MySQL Shell to get one step closer to a
functioning connection.

To see what MySQL Shell does:

```
mysqlsh --verbose=4 --log-sql=unfiltered mysql://default@127.0.0.1:9004
```

This might also bring other tools a step closer to working with the
MySQL compatibility of ClickHouse.

Ref: ClickHouse#9336

Signed-off-by: Daniël van Eeden <git@myname.nl>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
comp-foreign-db Integrations with other databases comp-mysql feature
Projects
None yet
Development

No branches or pull requests