Proposed: support DBeaver connect Databend

[wubx]

Developers most use GUI apps connect Database and OPS data. So we need to support one GUI app connect.

dbeaver is mutli-platfrom database tool for developers(free).

Statements that need support:

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, @@collation_connection AS collation_connection, @@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_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@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;

SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED=1 ORDER BY WORD

SHOW CHARSET;

SHOW COLLATION;

SELECT @@GLOBAL.character_set_server,@@GLOBAL.collation_server;

SHOW VARIABLES LIKE 'lower_case_table_names';

SELECT * FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'information_schema' AND t.TABLE_NAME = 'CHECK_CONSTRAINTS';

SELECT @@session.transaction_read_only;

I think we'd better support information_schema， support MySQL protocol better.

Tasks:

• DROP SCHEMA book_db

[BohuTANG]

We have federated_server_setup_set_or_jdbc_command:

databend/query/src/servers/mysql/mysql_interactive_worker.rs

Lines 254 to 268 in d95adc1

	fn federated_server_setup_set_or_jdbc_command(&mut self, query: &str) -> bool {
	let expr = RegexSet::new(&[
	"(?i)^(SET NAMES(.*))",
	"(?i)^(SET character_set_results(.*))",
	"(?i)^(SET FOREIGN_KEY_CHECKS(.*))",
	"(?i)^(SET AUTOCOMMIT(.*))",
	"(?i)^(SET sql_mode(.*))",
	"(?i)^(SET @@(.*))",
	"(?i)^(SET SESSION TRANSACTION ISOLATION LEVEL(.*))",
	// Just compatibility for jdbc
	"(?i)^(/\\* mysql-connector-java(.*))",
	])
	.unwrap();
	expr.is_match(query)
	}

@wubx , please provide the server log error here, it's easy to check which sql not supportted.

[TCeason]

Developers most use GUI apps connect Database and OPS data. So we need to support one GUI app connect.

dbeaver is mutli-platfrom database tool for developers(free).

Statements that need support:

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, @@collation_connection AS collation_connection, @@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_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@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;

SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED=1 ORDER BY WORD

SHOW CHARSET;

SHOW COLLATION;

SELECT @@GLOBAL.character_set_server,@@GLOBAL.collation_server;

SHOW VARIABLES LIKE 'lower_case_table_names';

SELECT * FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'information_schema' AND t.TABLE_NAME = 'CHECK_CONSTRAINTS';

SELECT @@session.transaction_read_only;

I think we'd better support information_schema， support MySQL protocol better.

Some ide software need sql result to do some config. Maybe at first we can judge why we not support these sql?

[TCeason]

We have federated_server_setup_set_or_jdbc_command:

databend/query/src/servers/mysql/mysql_interactive_worker.rs

Lines 254 to 268 in d95adc1

	fn federated_server_setup_set_or_jdbc_command(&mut self, query: &str) -> bool {
	let expr = RegexSet::new(&[
	"(?i)^(SET NAMES(.*))",
	"(?i)^(SET character_set_results(.*))",
	"(?i)^(SET FOREIGN_KEY_CHECKS(.*))",
	"(?i)^(SET AUTOCOMMIT(.*))",
	"(?i)^(SET sql_mode(.*))",
	"(?i)^(SET @@(.*))",
	"(?i)^(SET SESSION TRANSACTION ISOLATION LEVEL(.*))",
	// Just compatibility for jdbc
	"(?i)^(/\\* mysql-connector-java(.*))",
	])
	.unwrap();
	expr.is_match(query)
	}

@wubx , please provide the server log error here, it's easy to check which sql not supportted.

This func return empty result but the ide needs set some bar value according to these sql result.

So could we support some mock functions to suitable these queries or we can support these var in datafuse?

[wubx]

@TCeason Your approach may also work. Let's try your way.

[TCeason]

I try to do some test , and maybe your driver is com.mysql.jdbc.Driver that is mysql jdbc 5.1.

And if you use com.mysql.cj.jdbc.Driver it will not occur this err.

[TCeason]

dbeaver will inner execute

/* ApplicationName=DBeaver 22.0.1 - SQLEditor <Script.sql> */ SET SQL_SELECT_LIMIT=200

when execute show tables

[TCeason]

LINK ISSUE: #4697, #4698, #4709, #4710

[wubx]

link issue: #4708

commit;
rollback;
SHOW VARIABLES LIKE 'sql_mode'

Test:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://root:@192.168.2.10:3307/default', encoding="utf-8")
con = engine.connect()
rs = con.execute('select * from ontime limit 1')

[TCeason]

Hi, @BohuTANG @wubx Now we can use DBeaver select data.

[BohuTANG]

From my test, databend main branch is working with DBeaver on my ubuntu now 🚀
@wubx Would like your test on your macos

[wubx]

Using user root connect is ok.
If you create user like:

create user 'wubx'@'%' identified by 'wubxwubx';
grant all privileges on *.* to 'wubx'@'%';

use DBeaver connection Databend will have worry:

Authenticate failed, user: "wubx", auth_plugin: "mysql_native_password"

use mysql client cli:

mysql -h 192.168.2.10 -P3307 -uwubx -pwubxwubx

It's ok.

[TCeason]

Link To ISSUE: can not create a database on dbeaver. more info inside: #4813

[TCeason]

Link To ISSUE: can not create a database on dbeaver. more info inside: #4813

Also link to #4828

[BohuTANG]

Drop database has some issues:

Normal query: /* ApplicationName=DBeaver 22.0.2 - Main */ DROP SCHEMA `book_db`:
OnQuery Error: Code: 1005, displayText = sql parser error: Expected drop statement, found: SCHEMA.

[TCeason]

Drop database has some issues:

Normal query: /* ApplicationName=DBeaver 22.0.2 - Main */ DROP SCHEMA `book_db`:
OnQuery Error: Code: 1005, displayText = sql parser error: Expected drop statement, found: SCHEMA.

yes, this is a known problem.

We should support

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
  | READ ONLY [=] {DEFAULT | 0 | 1}
}


CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Link to #4854

[BohuTANG]

Databend has support DBeaver GUI 🚀