Cannot connect to MemSQL through SQLApi++

Question

I'm trying to connect to MemSQL database from c++ code and I'm using SQLApi++ library. MySQL client is compatible with MemSQL and SQLApi++ has support of MySQL. However I can't connect and get different errors.

For example, I'm calling connect with the following parameters

Connect("tcp://localhost:3307", "root", "", SA_MySQL_Client)

and get an error saying Access denied for user 'root'@'localhost' (using password: NO).

I have searched on the internet and tried several versions of specifying DB hostname, however non of them have worked. What am I doing wrong? What is the correct version? Any Ideas?

NOTE: I can successfully connect to MemSQL with command-line by just executing memsql -P 3307.

Answer 1

When I provided local IP address and port, for some reason SQLApi++ ignored it and always tried to connect to MySQL's socket in default path (i.e. /var/lib/mysql/mysql.sock).

However, after reading documentation once more, it turned out that it is possible to pass socket's path as a host name. So I passed MemSQL's socket path and it worked!

Answer 2

Do you have MySQL installed on the same machine as MemSQL? localhost can redirect to MySQL in some clients, but 127.0.0.1 won't (see https://docs.memsql.com/v5.7/docs/how-to-connect-to-memsql)

Does Connect("tcp://127.0.0.1:3307", "root", "", "SA_MySQL_Client") work?

Answer 3

I faced the same issue, and nabroyan has already mentioned one solution. Just providing some details on it and adding another possible solution as well.

Seems like when SQLAPI figures out that the server is running on local host, then it goes directly to the socket file, and unfortunately it always goes to the MySQL's socket file i.e. /var/lib/mysql/mysql.sock

Now there can be two possible solutions here:

1- Update your MemSQL instance to use the same socket file as that of MySQL (it may only be a choice if you are not running MySQL as well on that same box)

(haven't tested it myself, so maybe missing some steps here) You can probably do this by updating the value of "socket" in memsql.cnf file, so it should look like:

socket = /var/lib/mysql/mysql.sock

You can find the location of memsql.cnf file using the command that is used to figure out the socket file location, memsql.cnf file will be in the same directory:

memsql -u root --password=YOUR_PASSWORD_HERE -P 3306 -e "show variables like 'socket'"

Once done, you should restart the memsql:

memsql-ops memsql-restart

---

2- OR Modify your connection string to provide the MemSQL socket path.

You can figure out memsql socket file using the command mentioned under first option.

In my case the file was : /app/memsql/master-3306-NJG846fb0e/data/memsql.sock so I modified my connection call to use this file:

Connect("/app/memsql/master-3306-NJG846fb0e/data/memsql.sock@", "myuser", "mypassword", SA_MySQL_Client);