search

dolthub / dolt

Dolt – Git for Data
Apache License 2.0
17.85k stars 508 forks source link

Can't connect to mysql with navicat #1416

Closed 00LT00 closed 3 years ago

00LT00 commented 3 years ago

I start a new server and I can connect to mysql with cli. image But when I create a connect to mysql with navicat, it report an error that image Excuse me, why is this?

oscarbatori commented 3 years ago

This is likely due to some part of the MySQL wire protocol that we are yet to be compliant with.

I will ask our SQL team to take a look.

zachmu commented 3 years ago

Hi @00LT00,

By default, the SQL server only allows one simultaneous connection. This is because with our concurrency model it's possible for two connections to overwrite each other's changes. We're working on a better concurrency model, but we limit simultaneous connections for the time being for this reason.

You can override this behavior by specifying a config.yaml file for the server. Details on doing so can be found here:

https://docs.dolthub.com/interfaces/cli#description-6

Hope that helps!

00LT00 commented 3 years ago

Thanks @zachmu . Now if I click the button to test the connection, it tells me the connection is successful like this image But when I open this connect, it report an other error that image Maybe it is related to the ibdata1 file?

VinaiRachakonda commented 3 years ago

Hi @00LT00! I've been experimenting with Navicat and I believe I have some functionality coming in the next release to make it work with Dolt. I'll ping you when it's ready.

VinaiRachakonda commented 3 years ago

Making a note of some queries that need to be implemented still to make it work much better:

-- SHOW TABLE STATUS -- INSERT INTO test (Column1) VALUES (3)

VinaiRachakonda commented 3 years ago

Hi @00LT00! I just pushed in a change into master that allows you to get started with Navicat. I tested this locally on my mac. We usually do releases on Monday if you want to wait till then.

We may still be missing some compatible statements so feel free to create a new issue to denote them.

When you get a chance to test with Navicat, please followup here.

00LT00 commented 3 years ago

@VinaiRachakonda thanks.It's so cool.

00LT00 commented 3 years ago

same error @VinaiRachakonda

VinaiRachakonda commented 3 years ago

Hi @00LT00. Can you post your current version and installation method? We just did a release yesterday.

Or if you hop into our discord here I can walk you through this pretty quickly. Getting functionality with these editors is pretty important for us: https://discord.com/invite/RFwfYpu

00LT00 commented 3 years ago

Sorry @VinaiRachakonda. I found my version is 0.23.4. Maybe I installed by .sh instead of .msi. So I can't upgrade by .msi. It is working. But it has some new error.

00LT00 commented 3 years ago

@VinaiRachakonda When I first open connection and open a database. I get this error. image image It will be fine after restarting.

And when I view table structure image

VinaiRachakonda commented 3 years ago

Hi @00LT00 this is super weird. I am able to use Navicat with the state populations repo you are using.

Are you sure you are using Navicat for MySQL? If this is a specific repo to you can you push your repo to Dolthub.com so I can reproduce?

00LT00 commented 3 years ago

I'm sure. I just followed the tutorial step by step..... Let me try to upload.

VinaiRachakonda commented 3 years ago

Thanks @00LT00! Feel free to hop into our discord as well

00LT00 commented 3 years ago

@VinaiRachakonda Can you access this repo?

VinaiRachakonda commented 3 years ago

@00LT00 Yup I can. Reproducing now!

00LT00 commented 3 years ago

I have a time difference with you. I'm staying up late now, so I can't discuss it online. 😂

VinaiRachakonda commented 3 years ago

@00LT00 No worries! I just cloned the repo and it's working on my end. I'm going to talk with my team to see what's going on here.

The last thing you can try is running the server with a config like this:

log_level: trace

behavior:
  read_only: false
  autocommit: true

user:
  name: root
  password: ""

listener:
  host: localhost
  port: 3305
  max_connections: 10
  read_timeout_millis: 1000000
  write_timeout_millis: 1000000

databases: []

So the command is then dolt sql-server --config=config.yaml. That way with this trace level I can see which query in the background is breaking

00LT00 commented 3 years ago

Hi @VinaiRachakonda This is all terminal output.

Starting server with Config HP="localhost:3305"|U="root"|P=""|T="1000000"|R="false"|L="trace"
INFO: NewConnection: client 1
INFO: audit trail
TRACE: received query SET NAMES utf8mb4
DEBUG: executing query
TRACE: returning result row []
TRACE: received query SHOW VARIABLES LIKE 'lower_case_%'
DEBUG: executing query
TRACE: received query  SHOW VARIABLES LIKE 'sql_mode'
DEBUG: executing query
TRACE: returning result row [TEXT("sql_mode") TEXT("")]
TRACE: received query  SELECT COUNT(*) AS support_ndb FROM information_schema.ENGINES WHERE Engine = 'ndbcluster'
DEBUG: executing query
TRACE: returning result row [INT64(0)]
TRACE: received query SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA
DEBUG: executing query
TRACE: returning result row [TEXT("state_pops") TEXT("utf8mb4") TEXT("utf8mb4_0900_ai_ci")]
TRACE: returning result row [TEXT("information_schema") TEXT("utf8mb4") TEXT("utf8mb4_0900_ai_ci")]
TRACE: received query SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'state_pops' UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'state_pops' UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'state_pops'
DEBUG: executing query
TRACE: returning result row [INT64(1)]
TRACE: returning result row [INT64(2)]
TRACE: returning result row [INT64(0)]
TRACE: received query SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'state_pops' ORDER BY TABLE_SCHEMA, TABLE_TYPE
DEBUG: executing query
TRACE: returning result row [TEXT("state_pops") TEXT("state_populations") TEXT("BASE TABLE")]
TRACE: received query SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'state_pops' ORDER BY TABLE_SCHEMA, TABLE_NAME
DEBUG: executing query
TRACE: returning result row [TEXT("state_pops") TEXT("state_populations") TEXT("state") TEXT("varchar(14)")]
TRACE: returning result row [TEXT("state_pops") TEXT("state_populations") TEXT("population") TEXT("int")]
TRACE: received query SELECT DISTINCT ROUTINE_SCHEMA, ROUTINE_NAME, PARAMS.PARAMETER FROM information_schema.ROUTINES LEFT JOIN ( SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, GROUP_CONCAT(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) ORDER BY ORDINAL_POSITION SEPARATOR ', ') PARAMETER, ROUTINE_TYPE FROM information_schema.PARAMETERS GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE ) PARAMS ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA AND ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME AND ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE WHERE ROUTINE_SCHEMA = 'state_pops' ORDER BY ROUTINE_SCHEMA
DEBUG: executing query
TRACE: Error running query SELECT DISTINCT ROUTINE_SCHEMA, ROUTINE_NAME, PARAMS.PARAMETER FROM information_schema.ROUTINES LEFT JOIN ( SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, GROUP_CONCAT(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) ORDER BY ORDINAL_POSITION SEPARATOR ', ') PARAMETER, ROUTINE_TYPE FROM information_schema.PARAMETERS GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE ) PARAMS ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA AND ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME AND ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE WHERE ROUTINE_SCHEMA = 'state_pops' ORDER BY ROUTINE_SCHEMA: unsupported syntax: group_concat(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) order by ORDINAL_POSITION asc separator ', ')
INFO: NewConnection: client 2
INFO: audit trail
TRACE: received query SET NAMES utf8mb4
DEBUG: executing query
TRACE: returning result row []
TRACE: received query SHOW FULL TABLES WHERE Table_type != 'VIEW'
DEBUG: executing query
TRACE: returning result row [TEXT("state_populations") TEXT("BASE TABLE")]
TRACE: received query SHOW TABLE STATUS
DEBUG: executing query
TRACE: returning result row [TEXT("state_populations") TEXT("InnoDB") TEXT("10") TEXT("Fixed") UINT64(17) UINT64(64) UINT64(1088) UINT64(0) INT64(0) INT64(0) NULL NULL NULL NULL TEXT("utf8mb4_0900_ai_ci") NULL NULL NULL]
TRACE: received query SHOW TABLE STATUS LIKE 'state\_populations'
DEBUG: executing query
TRACE: returning result row [TEXT("state_populations") TEXT("InnoDB") TEXT("10") TEXT("Fixed") UINT64(17) UINT64(64) UINT64(1088) UINT64(0) INT64(0) INT64(0) NULL NULL NULL NULL TEXT("utf8mb4_0900_ai_ci") NULL NULL NULL]
TRACE: received query SHOW CREATE TABLE `state_populations`
DEBUG: executing query
TRACE: returning result row [TEXT("state_populations") TEXT("CREATE TABLE `state_populations` (\n  `state` varchar(14) NOT NULL,\n  `population` int,\n  PRIMARY KEY (`state`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")]
TRACE: received query SHOW FULL COLUMNS FROM `state_populations`
DEBUG: executing query
TRACE: returning result row [TEXT("state") TEXT("varchar(14)") TEXT("utf8mb4_0900_ai_ci") TEXT("NO") TEXT("PRI") TEXT("") TEXT("") TEXT("") TEXT("")]
TRACE: returning result row [TEXT("population") TEXT("int") NULL TEXT("YES") TEXT("") TEXT("") TEXT("") TEXT("") TEXT("")]
TRACE: received query SHOW INDEX FROM `state_populations`
DEBUG: executing query
TRACE: returning result row [TEXT("state_populations") INT32(0) TEXT("PRIMARY") INT32(1) TEXT("state") NULL INT64(0) NULL NULL TEXT("") TEXT("BTREE") TEXT("") TEXT("") TEXT("YES") NULL]
TRACE: received query SELECT ACTION_ORDER, EVENT_OBJECT_TABLE, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, DEFINER, ACTION_STATEMENT, ACTION_TIMING FROM information_schema.triggers WHERE BINARY event_object_schema = 'state_pops' AND BINARY event_object_table = 'state_populations' ORDER BY event_object_table
DEBUG: executing query
TRACE: Error running query SELECT ACTION_ORDER, EVENT_OBJECT_TABLE, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, DEFINER, ACTION_STATEMENT, ACTION_TIMING FROM information_schema.triggers WHERE BINARY event_object_schema = 'state_pops' AND BINARY event_object_table = 'state_populations' ORDER BY event_object_table: unsupported feature: unary operator: binary
VinaiRachakonda commented 3 years ago

HI @00LT00. Thank you for these logs! I''m surprised navicat is crashing on this query. We'll implement the necessary functions and get back to you. Apologies for the delay!

00LT00 commented 3 years ago

Thanks @VinaiRachakonda. Looking forward to your reply

gorpher commented 3 years ago

I can't show engines @VinaiRachakonda mysql> show engines; ERROR 1105 (HY000): unknown error: unsupported feature: SHOW engines and create a table syntax error id CHAR(36) BINARY this syntax is not supported ? my dolt version is 0.22.8 . How can I know the version of SQL server that I use ?

gorpher commented 3 years ago

now,I can't create tables using the following syntax.

CREATE TABLE IF NOT EXISTS `budgets` (
        `id` CHAR ( 36 ) BINARY,
        `uid` INTEGER NOT NULL,
        `name` VARCHAR ( 225 ) NOT NULL UNIQUE,
        `money` INTEGER,
        `created_at` DATETIME NOT NULL,
      `updated_at` DATETIME  NOT NULL,
    PRIMARY KEY ( `id` )) ENGINE = INNODB COMMENT 'budget' DEFAULT CHARSET = utf8mb4;

here is my myql version 

mysql  Ver 14.14 Distrib 5.7.32, for osx10.16 (x86_64) using  EditLine wrapper

Connection id:      9
Current database:   
Current user:       root
SSL:            Not in use
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.9-Vitess 
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    
Db     characterset:    
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306

mysql> select version();
+-----------+
| VERSION() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

this document tell me that binary dataType is supported. https://docs.dolthub.com/interfaces/sql/sql-support/data-description image

VinaiRachakonda commented 3 years ago

Hi @gorpher. You need to upgrade to at least 0.24.3 to get baseline compatibility with Navicat. This includes the SHOW ENGINES statement. Then with sql-server you should be able to connect via dolt sql-server

VinaiRachakonda commented 3 years ago

@gorpher Regarding your CREATE TABLE statement, that looks like a parse bug on our end. We do support BINARY. I filed an issue here #1488 and will followup with you promptly

gorpher commented 3 years ago

Hi @gorpher. You need to upgrade to at least 0.24.3 to get baseline compatibility with Navicat. This includes the SHOW ENGINES statement. Then with sql-server you should be able to connect via dolt sql-server

I upgrade dolt version to 0.24.3, it's work with Navicat. I install bolt By brew in my mac, but dolt version is 0.22.8. Can you update the official version number of brew?

VinaiRachakonda commented 3 years ago

@gorpher Yes will do.

@oscarbatori Can you look into the brew update please?

VinaiRachakonda commented 3 years ago

HI @00LT00. The BINARY function is now released to master. This should take you a little further in your connection process. We are still working on writing Group Concat and hope it have it out soon.

VinaiRachakonda commented 3 years ago

GROUP_CONCAT is now in mister

00LT00 commented 3 years ago

@VinaiRachakonda Sorry for the long time no reply, I get a new error when I view struct image Terminal output is

.............
TRACE: returning result row [VARCHAR("Virginia") INT32(691937)]
INFO: NewConnection: client 2
INFO: audit trail
TRACE: received query SET NAMES utf8mb4
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row []
TRACE: received query SHOW COLUMNS FROM `state_pops`.`state_populations`
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state") TEXT("varchar(14)") TEXT("NO") TEXT("PRI") TEXT("") TEXT("")]
TRACE: returning result row [TEXT("population") TEXT("int") TEXT("YES") TEXT("") TEXT("") TEXT("")]
TRACE: received query SHOW TABLE STATUS LIKE 'state_populations'
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state_populations") TEXT("InnoDB") TEXT("10") TEXT("Fixed") UINT64(17) UINT64(64) UINT64(1088) UINT64(0) INT64(0) INT64(0) NULL NULL NULL NULL TEXT("utf8mb4_0900_ai_ci") NULL NULL NULL]
TRACE: received query SHOW CREATE TABLE `state_pops`.`state_populations`
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state_populations") TEXT("CREATE TABLE `state_populations` (\n  `state` varchar(14) NOT NULL,\n  `population` int,\n  PRIMARY KEY (`state`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")]
TRACE: received query SHOW TABLE STATUS LIKE 'state\_populations'
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state_populations") TEXT("InnoDB") TEXT("10") TEXT("Fixed") UINT64(17) UINT64(64) UINT64(1088) UINT64(0) INT64(0) INT64(0) NULL NULL NULL NULL TEXT("utf8mb4_0900_ai_ci") NULL NULL NULL]
TRACE: received query SHOW CREATE TABLE `state_populations`
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state_populations") TEXT("CREATE TABLE `state_populations` (\n  `state` varchar(14) NOT NULL,\n  `population` int,\n  PRIMARY KEY (`state`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")]
TRACE: received query SHOW FULL COLUMNS FROM `state_populations`
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state") TEXT("varchar(14)") TEXT("utf8mb4_0900_ai_ci") TEXT("NO") TEXT("PRI") TEXT("") TEXT("") TEXT("") TEXT("")]
TRACE: returning result row [TEXT("population") TEXT("int") NULL TEXT("YES") TEXT("") TEXT("") TEXT("") TEXT("") TEXT("")]
TRACE: received query SHOW INDEX FROM `state_populations`
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: returning result row [TEXT("state_populations") INT32(0) TEXT("PRIMARY") INT32(1) TEXT("state") NULL INT64(0) NULL NULL TEXT("") TEXT("BTREE") TEXT("") TEXT("") TEXT("YES") NULL]
TRACE: received query SELECT ACTION_ORDER, EVENT_OBJECT_TABLE, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, DEFINER, ACTION_STATEMENT, ACTION_TIMING FROM information_schema.triggers WHERE BINARY event_object_schema = 'state_pops' AND BINARY event_object_table = 'state_populations' ORDER BY event_object_table
DEBUG: executing query
DEBUG: Connection checker exiting, connection isn't TCP
TRACE: received query SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME FROM information_schema.PARTITIONS WHERE NOT ISNULL(PARTITION_NAME) AND TABLE_SCHEMA LIKE 'state_pops' AND TABLE_NAME LIKE 'state_populations' ORDER BY TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION
DEBUG: executing query
TRACE: Error running query SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME FROM information_schema.PARTITIONS WHERE NOT ISNULL(PARTITION_NAME) AND TABLE_SCHEMA LIKE 'state_pops' AND TABLE_NAME LIKE 'state_populations' ORDER BY TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION: table not found: PARTITIONS

Thanks for your reply And this mistake occurs occasionally: image

VinaiRachakonda commented 3 years ago

Hi @00LT00! I added a fix to the above error that will hit master in a couple of days. The other error "the lost connection" seems to be a problem with your config file. Do you max_connections > 1? CC here: https://docs.dolthub.com/integrations/sql-editors#config-customization

00LT00 commented 3 years ago

@VinaiRachakonda Thanks. I will try the new version and reply this issue