Tool recommendations for connecting to SingleStore

Off-Topic
1

(Ported from memsql-public-chat Slack channel)

Amir [12:26 PM]
Hi guys,
What is the best tool you recommend for connecting to MemSQL 6.0. I tried MySQL Workbench 8.0 and I receive a handshake error. MySQL Workbench 6.3 connects, however, I have some other incompatibility issues with it when working in Amazon workspace.

jack [12:33 PM]
@Amir mysql changed the default authentication protocol in MySQL 8, so to connect to memsql with the MySQL 8 connector you will need to set the client configuration default-auth=mysql_native_password in some fashion, or you can use any version older than 8.

The same issue exists using the MySQL 8 C connector and connecting to an older version of MySQL (see https://bugs.mysql.com/bug.php?id=90994).

From Error connecting to memsql with mysql C connector - Stack Overflow

John Toups [3:17 PM]
@Amir I use Querious (mac mysql client) and Navicat (is mac/win) with no issues, except that the table-sync tools don’t work. everything else is peachy.

2

After continuous problems with MySQL workbench, tools, etc. I decided to just use a good tool that just uses JDBC for connectivity. I tried a couple and ended up using DBeaver. Eclipse based and pretty stable.

1 Like
3

What problems did you run in to with MySQL workbench?

4

it used to crash quite often on my mac. plus since it didnt use jdbc, i wasnt sure the behavior would be the same as sql executed in my code or scripts.

5

On Windows we are using Toad, on Mac we are using Sequel Pro.
And to test performance, we are using either the MemSQL Studio or the CLI.
Hope this helps

1 Like
6

Has anyone got MySQL Workbench 8 to work with MemSQL? I tried specifying “default-auth=mysql_native_password” as an option=value pair on the connection but it stills says “error in server handshake”. Our users generally connect to MemSQL using Toad Data Point but it’d be nice to have a free alternative. Using older software versions is not ideal because it won’t be guaranteed to work on future operating system versions, and it won’t receive fixes or enhancements. Are there other free clients recommended for Windows?

7

I can highly recommend DBeaver, as @adama also suggested. There’s a free community version, the paid version is very reasonable and worth supporting if you are able, a lot cheaper than the Toad.

8

Did anyone ever get MySQL Workbench 8.0 connecting to MemSQL? We use MySQL Workbench a lot and it has been very convenient to be able to use for both our MySQL and MemSQL. Setting the default-auth setting does not seem to be sufficient.

9

Hey @jlevin2

Since MemSQL is MySQL compatible, it should “just work”. What errors or issues are you seeing?

You should only need to specify the hostname, username, and password.

10

This isn’t my area of expertise, but I see MySQL Workbench 8.0.18 CE working with 7.0 RC1.

The main issue is that you should specify defaultAuth=mysql_native_password instead of default-auth=mysql_native_password. The option=value pairs in the advanced config are Connector/C++ options (https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-connect-options.html), so the right option name is defaultAuth.

However, in 6.8 you’ll likely hit a second connection issue related to a show character set syntax error. The issue is fixed in 7.0 RC1 and will also be fixed in the upcoming 6.8.14.

11

I was able to make it work as follows

mysql --default-auth mysql_native_password -h MY_HOST -u MY_USER -pMY_PWD

12

I use TablePlus. It is very simple and stable.

13

Dear @adama ,

But in Dbeaver there is issue .

Scenario : If we are trying to export data from table and by mistake if we select data tables as transfer type it will add the selected aggregated columns to the table itself without warning pop up .

Then events rejections will start because of extra column .
@jack

14

Thanks for sharing your insights mate.