Windows 10 odbc connection

Help Connectors and Pipelines
1

our marketing team operates on windows 10. we would like to add an odbc connection into memsql. we are able to add various other databases but can’t seem to do it for memsql. always getting error.

we use the mysql odbc 8.0 ANSI and Unicode driver (8.00.16.00). but nothing works…

any advise?

2

We might be able to help if you can provide some more information about what errors you are seeing.

What version of MemSQL are you using? What errors does the MySQL connector show?

3

Hi.

Version of MEMSQL: MemSQL Community Edition (Single VM) version 5.0.6 on AWS

Error: "[MySQL][ODBC 8.0(a) Driver] Error in server handshake

Replicating error:

  1. on windows task bar search; open “odbc data source”.
  2. Click Add
  3. choose MySQL ODBC 8.0 ANSI Driver
  4. Enter any data source name/description
  5. for TCP/IP Server; enter AWS path name
  6. Enter User and password
  7. Confirm by “test”
4

I would highly advise you to upgrade to MemSQL 6.8, the latest version of the product.
It is available on the main website: SingleStore: Get the World's Fastest Database Free

MemSQL 5.0 is no longer supported. You can find resources on how to upgrade here: SingleStoreDB Cloud · SingleStore Documentation

The document below may be useful for you. When connecting to MemSQL, you have to pass --default-auth=mysql_native_password to the connector.

5

Thanks for the recommendations. We’ll definitely start upgrading.

Regarding the odbc, and passing the string. Can you tell me where do i have to add the string?

When i open the windows 10 odbc screen and select MySql 8.x driver, i have the following options:

Data Source Name: << i am guessing this is my own choice and i don’t pass here>>

Description: << i am guessing this is my own choice and i don’t pass here>>

TCP/IP Server: For AWS hosted databases, I just put the AWS connection address here and it works. do i append “–default-auth=MYMEMSQLPASSWORD” at the end here?

so if my aws string is “ec2-123.456.com”, does it become “ec2-123.456.com–default-auth=mysql_native_password” or does it become “ec2-123.456.com–default-auth=MYPASSWORD”

User: <>

Password: Is it here?

I tried a couple of combinations so i am probably not appending it right. Appreciate your help.
6

This option is used to pick an authentication mechanism. the value --default-auth=mysql_native_password specifies that the MySQL client that is connecting to the MemSQL server.

mysql_native_password is an authentication mechanism that MemSQL supports. You have to configure the client (the ODBC Connector) to use that authentication plugin. Passing the flag works if you use the command line mysql client.

7

thanks. is there a step of instructions with images I could follow to get the odbc connection set up? I am not sure where i have to pass the password, the flag etc. Or alternatively, can we set up a remote meeting for 10 mins? we are able to connect to all of other db’s (mysql, sql server, etc), but we seem to be having trouble with memsql.

thanks!

8

Hey , let me know if you are still facing the ODBC conenctivity issue with MemSQL

9

will do. We are upgrading to version 6.5 and check if we have the issue.

10

Earlier I provided a command line argument that can avoid this issue in some cases:
--default-auth=mysql_native_password

In the Windows ODBC Configuration window, there is a Connection tab. Inside there is a text field called Authentication. Try providing the string mysql_native_password as a value for the Authentication field. This is another thing that might resolve the handshake issue.

11

Yes! That worked on my MEMSQL 5.x version.Thanks! We are still upgrading to 6.

thanks again.

for anyone curious…for windows 10

  1. Add ODBC Data Sources
  2. on tab “User DSN” click on “Add”
  3. Select mysql ODBC 8.0. I chose ANSI driver
  4. For Connection Parameters; name your own data source and description; for TCP/IP choose what it needs to be. User: enter your regular memsql user name; Password: enter your memsql password
  5. There is a “Details >>” option on the bottom. In that, there is a ‘connection’ tab. Its the left most one for me. on the bottom, you will see “authentication”. In that you need to copy/paste “mysql_native_password” (without the quotation marks).
  6. Click Test to confirm.

thanks.