MYSQL Client is not showing the correct database

Off-Topic
1

Hello,

My goal is to develop automation using bash on MemSQL database. To do this - I have installed MemSQL through docker desktop on my MAC and to create database and tables I used Single Store Studio. Now to run the bash script, I will use my MAC terminal and mySQL client. I installed MySQL client but when I do try to connect to memsql database, it is connecting to MySQL database by default. How do I connect to MemSQL database through SQL client? Is there any other way to run bash script on MemSQL database?

mysql --default-auth=mysql_native_password -h 127.0.0.1 -u root -p -P 3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.26 Homebrew

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.01 sec)

2

I would guess your not using the right port? Which port is Singlestore running on? Potentially 3307 ?

-Adam

3

In my docker yaml file I used - 3306:3306 port. And I am using the same port while connect to my SQL command line

4

Hi @ankitkothari.18,
It sounds like you have MySQL running on port 3306 on your machine. It’s not possible to bind two different services to the same port on a machine - so please either select a different port for SingleStore (which is the new name for “MemSQL”) or turn off MySQL.

Since you mentioned that you were able to connect to SingleStore Studio to create your schema, it sounds like the docker container running SingleStore was at least able to come up. I suggest checking how you connected to Studio. For example, you may be connecting directly to the container IP (could be something like 172.168…), in which case you can use that same IP when connecting to SingleStore on the command line.

If you are still having trouble please include the yaml files you are using so we can debug further.

Cheers,
Carl

5

I have changed the port to 3307 and I still see mysql database in mysql client.

Yaml file used to spin up the database -

cat docker-compose.yaml
version: ‘2’

services:
memsql:
image: ‘memsql/cluster-in-a-box’
ports:
- 3307:3306
- 8080:8080
environment:
LICENSE_KEY: ${LICENSE_KEY}
ROOT_PASSWORD: ‘’
START_AFTER_INIT: ‘Y’

MYSQL Client connection details -
$ mysql -u root -p -h localhost -P 3307 --default-auth=mysql_native_password
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.26 Homebrew

Copyright © 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> status

mysql Ver 8.0.26 for macos10.15 on x86_64 (Homebrew)

Connection id: 29
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server version: 8.0.26 Homebrew
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 6 days 1 hour 14 min 32 sec

Threads: 2 Questions: 50 Slow queries: 0 Opens: 178 Flush tables: 3 Open tables: 95 Queries per second avg: 0.000

mysql>
mysql>
mysql>
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema | |
| mysql |
| performance_schema |
| sys |
±-------------------+

mysql>

6

Hi @ankitkothari.18! Looks like your docker-compose is correct, as well as your command line do connect to SingleStore. Can you share the output of docker ps and docker-compose ps? Something is off here, as you should normally be able to connect using port 3307 the same way you can connect to studio with port 8080.

7

Here is the output -

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
33540a14f986 memsql/cluster-in-a-box “/startup” 24 hours ago Up 24 hours 3307/tcp, 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp documents_memsql_1

$ docker-compose ps
Name Command State Ports

documents_memsql_1 /startup Up 0.0.0.0:3307->3306/tcp,:::3307->3306/tcp, 3307/tcp, 0.0.0.0:8080->8080/tcp,:::8080->8080/tcp

Also, few questions

  1. Is there specific configuration that we do for single store in my sql client installation?
  2. What are the other options here? What are the other ways I can run bash script on my Single Store database?
8

You’re logging into HeidiSQL as root, so it’s showing you all databases, but you’re logging into mysql.exe as the current Windows user (since that’s the default), so it’s only showing you the databases that that user can see. If you run mysql.exe with --user=root --password=..., it will show you all databases.

9

May I ask you why you use this particular language if there is a perfect one? I’m just taking courses on SQL right now, and we’re studying all its versions and plugins, so I have to determine for myself which version of the program will suit me the most. But I have some problems with this since I have not devoted enough time to all possible options. Therefore, I am interested in the opinion of people who have been in this field for longer and can give me something and give advice. So far, I’m inclined to work in mysql tools for visual studio, as I’ve seen a lot of excellent reviews about this plugin. But I’d be happy to talk to someone here since I think you guys are already professionals in your field.