Can't connect to MemSQL via Python

emanuelbesliu · March 8, 2021, 3:09pm

I used Kubernetes deployment and when I try to access http://localhost:30080/ it works just fine.
When I try to connect to the databases through python script using:

from memsql.common import database

conn = database.connect(host="127.0.0.1", port=3306, user="root")
print(conn.query("show databases"))

it gives me: MySQLdb._exceptions.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' (61)")

Here is the YAML file for deployment:

# A deployment ensures pod(s) are restarted on failure

apiVersion: apps/v1
kind: Deployment
metadata:
name: memsql
spec:
replicas: 1 # only create one pod (container)
selector:
matchLabels:
app: memsql
template:
# Here’s the definition of the pod:
metadata:
# The service finds all pods with matching metadata
labels:
app: memsql
spec:
containers:
- name: memsql
resources:
# Cluster-in-a-box image is pulled from Docker Hub
image: memsql/cluster-in-a-box
ports:
- containerPort: 3306 # MemSQL db
- containerPort: 8080 # MemSQL Studio
env:
# ‘Y’ means keep running after cluster init
- name: START_AFTER_INIT
value: ‘Y’
# TODO: set to your desired password
- name: ROOT_PASSWORD
value: ‘pass’
# TODO: paste your license key from portal.memsql.com here:
- name: LICENSE_KEY
value:

A service load-balances across and routes traffic into pods

apiVersion: v1
kind: Service
metadata:
name: memsql
labels:
app: memsql
spec:
type: NodePort

Find all pods

selector:
app: memsql
ports:

MemSQL db port:

name: ‘3306’
nodePort: 30306
port: 3306
targetPort: 3306

MemSQL Studio port:

name: ‘8080’
nodePort: 30080
port: 8080
targetPort: 8080

robrich · March 8, 2021, 11:27pm

Because you’ve mapped the MySQL port (via NodePort service) from 3306 to 30306, you’ll need to modify the code to run on this port too:

conn = database.connect(host="127.0.0.1", port=30306, user="root")

emanuelbesliu · March 9, 2021, 6:47am

Thanks, really helpful.

However, still an error:

MySQLdb._exceptions.OperationalError: (2012, 'Error in server handshake')

robrich · March 9, 2021, 2:45pm

We’re closer. It sounds now like maybe a password failure or we’re using a MySQL driver that encrypts the password by default. I see you’re using the MemSQL driver, so the latter seems less likely. Try this:

conn = database.connect(host="127.0.0.1", port=30306, user="root", password="pass")

Note that I grabbed the password from the environmennt variable passed into the container as ROOT_PASSWORD.

emanuelbesliu · March 9, 2021, 2:52pm

I’ve already tried adding password parameter. I tried another machine and If I run it locally then it works just fine, the connection succeeds. Maybe there is a problem with MySQL on this machine.

Also, another issue came up. I try to access the db from another container which is a k8s deployment and I get: (2003, 'Can\'t connect to MySQL server on \'127.0.0.1\' (111 "Connection refused")')

Any idea on this?

robrich · March 9, 2021, 3:10pm

If you’re connecting from another container, you’ll need to connect to the host memsql since that’s the name of the Kubernetes service. In effect, Kubernetes creates a virtual network with all the containers inside it. They each get unique host names and IPs. Like any network, certain traffic is NATed in from publicly exposed ports. So while inside the network, you connect by the k8 service name, and from outside the network, you can connect on localhost.

I’m curious about the connection error. What are the logs from the memsql/cluster-in-a-box container? Perhaps it doesn’t have the license key set? I’m assuming you’re following the tutorial from SingleStore Blog Are you able to connect to the cluster from SingleStore Studio? (http://localhost:30080/)

emanuelbesliu · March 9, 2021, 3:17pm

Here are the memsql/cluster-in-a-box logs, even if tried to call up the db, seems it doesn’t reach out. Yes, I use that tutorial which, by the way, has its gists expired or something.

The SingleStore Studio works just fine.

2021-03-09 15:14:19.881172 Initializing MemSQL Cluster in a Box

2021-03-09 15:14:19.881306 Creating…
2021-03-09 15:14:20.561907 Done.
2021-03-09 15:14:20.562047 Configuring…
2021-03-09 15:14:21.373856 Done.
2021-03-09 15:14:21.373938 Bootstrapping…
2021-03-09 15:14:27.323917 Done.
2021-03-09 15:14:27.323983 Configuring Toolbox…
2021-03-09 15:14:27.401909 Done.
==> /var/lib/memsql/1be2d3f2-753c-4290-a61f-73126f87263a/tracelogs/memsql.log <==
05300778 2021-03-09 15:14:27.040 INFO: ADD LEAF _SYNC_PARTITION 11 started
05464635 2021-03-09 15:14:27.204 INFO: ADD LEAF _SYNC_PARTITION 11 finished
05479045 2021-03-09 15:14:27.218 INFO: No action taken for Leaf ‘127.0.0.1’:‘3307’ with ID 2
05479149 2021-03-09 15:14:27.219 INFO: Thread 100000: operator(): Done Adding new leaf node distributed@127.0.0.1:3307
05556288 2021-03-09 15:14:27.296 INFO: Query information_schema.‘SELECT HOST FROM information_schema.USERS where USER=^ AND IS_DELETED=@’ submitted for asynchronous compilation
05745343 2021-03-09 15:14:27.485 INFO: Query information_schema.‘SELECT node_id FROM INFORMATION_SCHEMA.lmv_nodes’ submitted for asynchronous compilation
05759460 2021-03-09 15:14:27.499 INFO: Query information_schema.‘SELECT @@memsql_version’ submitted for asynchronous compilation
05810022 2021-03-09 15:14:27.549 INFO: Query information_schema.‘SELECT HOST FROM information_schema.USERS where USER=^ AND IS_DELETED=@’ submitted 267 milliseconds ago, queued for 15 milliseconds, compiled asynchronously in 252 milliseconds
06013623 2021-03-09 15:14:27.753 INFO: Query information_schema.‘SELECT node_id FROM INFORMATION_SCHEMA.lmv_nodes’ submitted 280 milliseconds ago, queued for 13 milliseconds, compiled asynchronously in 267 milliseconds
06017079 2021-03-09 15:14:27.756 INFO: Query information_schema.‘SELECT @@memsql_version’ submitted 268 milliseconds ago, queued for 13 milliseconds, compiled asynchronously in 255 milliseconds

==> /var/lib/memsql/78849865-fa63-4f3e-b1a7-b691c720063b/tracelogs/memsql.log <==
05290482 2021-03-09 15:14:27.036 INFO: Thread 99997: WaitForLSNWithTimeout: _SYNC_PARTITIONS success sync to lsn 0x9. ReplicationManagement and AdjustReferenceDatabases threads at 0x9.
05457506 2021-03-09 15:14:27.202 INFO: Thread 99997: WaitForLSNWithTimeout: _SYNC_PARTITIONS success sync to lsn 0xb. ReplicationManagement and AdjustReferenceDatabases threads at 0xb.
05570134 2021-03-09 15:14:27.316 INFO: Query information_schema.‘SELECT HOST FROM information_schema.USERS where USER=^ AND IS_DELETED=@’ submitted for asynchronous compilation
05805800 2021-03-09 15:14:27.551 INFO: Query information_schema.‘SELECT node_id FROM INFORMATION_SCHEMA.lmv_nodes’ submitted for asynchronous compilation
05832793 2021-03-09 15:14:27.578 INFO: Query information_schema.‘SELECT @@memsql_version’ submitted for asynchronous compilation
05838622 2021-03-09 15:14:27.584 INFO: Query information_schema.‘SELECT HOST FROM information_schema.USERS where USER=^ AND IS_DELETED=@’ submitted 278 milliseconds ago, queued for 12 milliseconds, compiled asynchronously in 266 milliseconds
05926987 2021-03-09 15:14:27.672 INFO: Query information_schema.‘SELECT AVAILABILITY_GROUP FROM information_schema.LEAVES
JOIN information_schema.LMV_NODES
ON information_schema.LEAVES.NODE_ID=information_schema.LMV_NODES.NODE_ID’ submitted for asynchronous compilation
06027746 2021-03-09 15:14:27.773 INFO: Query information_schema.‘SELECT @@memsql_version’ submitted 210 milliseconds ago, queued for 17 milliseconds, compiled asynchronously in 193 milliseconds

==> /var/lib/singlestoredb-studio/studio.log <==

==> /var/lib/memsql/memsql_exporter.log <==

==> /var/lib/memsql/78849865-fa63-4f3e-b1a7-b691c720063b/tracelogs/memsql.log <==
06145183 2021-03-09 15:14:27.891 INFO: Query information_schema.‘SELECT node_id FROM INFORMATION_SCHEMA.lmv_nodes’ submitted 384 milliseconds ago, queued for 47 milliseconds, compiled asynchronously in 337 milliseconds
06452018 2021-03-09 15:14:28.197 INFO: Query information_schema.‘SELECT AVAILABILITY_GROUP FROM information_schema.LEAVES
JOIN information_schema.LMV_NODES
ON information_schema.LEAVES.NODE_ID=information_schema.LMV_NODES.NODE_ID’ submitted 605 milliseconds ago, queued for 82 milliseconds, compiled asynchronously in 523 milliseconds

==> /var/lib/singlestoredb-studio/studio.log <==
2021/03/09 15:14:28 env.go:90 Log Opened
2021/03/09 15:14:28 server.go:74 Listening on 0.0.0.0:8080
2021/03/09 15:14:28 server.go:93 HTTPS configuration was not detected, serving with HTTP

==> /var/lib/memsql/memsql_exporter.log <==
time=“2021-03-09T15:14:27Z” level=info msg=“Arguments: [@/opt/memsql-server-7.3.4-d596a2867a/memsql_exporter/conf/memsql_exporter_ma.args]” source=“memsql_exporter.go:519”
time=“2021-03-09T15:14:27Z” level=info msg=“Starting memsql_exporter (version=7.3.4, branch=, revision=d596a28)” source=“memsql_exporter.go:534”
time=“2021-03-09T15:14:27Z” level=info msg=“Build context (go=go1.13.6, user=, date=)” source=“memsql_exporter.go:535”
time=“2021-03-09T15:14:27Z” level=info msg=“Read DSN from /etc/memsql/memsql_exporter.cnf” source=“memsql_exporter.go:554”
time=“2021-03-09T15:14:27Z” level=info msg=“Enabled scrapers:” source=“memsql_exporter.go:558”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.show_status_extended" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.events" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.tables" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.processlist" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.tablestats" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.show_variables" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.memory.counters" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.distributed.partitions" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.system_info" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.pipeline_batches" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.global_status_gauge_vars" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.mv_nodes" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.mv_activities" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.info_schema.mv_activities_extended_cumulative" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.show_workload_management_status" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=" --collect.global_status_counting_vars" source=“memsql_exporter.go:562”
time=“2021-03-09T15:14:27Z” level=info msg=“Enabled cluster scrapers:” source=“memsql_exporter.go:570”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_sysinfo_net" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_workload_management_status" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.events" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.distributed.partitions" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_global_status" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_activities" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_activities_extended_cumulative" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.tables" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_processlist" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.tablestats" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_global_variables" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.system_info" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.pipeline_batches" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_nodes" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_sysinfo_cpu" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_sysinfo_disk" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=" --cluster-collect.info_schema.mv_sysinfo_mem" source=“memsql_exporter.go:574”
time=“2021-03-09T15:14:27Z” level=info msg=“Enabled samplers:” source=“memsql_exporter.go:581”
time=“2021-03-09T15:14:27Z” level=info msg=" --sample.events" source=“memsql_exporter.go:585”
time=“2021-03-09T15:14:27Z” level=info msg=" --sample.node" source=“memsql_exporter.go:585”
time=“2021-03-09T15:14:27Z” level=info msg=" --sample.activity" source=“memsql_exporter.go:585”
time=“2021-03-09T15:14:27Z” level=info msg=" --sample.activity_extended" source=“memsql_exporter.go:585”
time=“2021-03-09T15:14:27Z” level=info msg=" --sample.pipeline_batches" source=“memsql_exporter.go:585”
time=“2021-03-09T15:14:27Z” level=info msg=" --sample.cluster_info" source=“memsql_exporter.go:585”
time=“2021-03-09T15:14:27Z” level=info msg=“Listening on :9104” source=“memsql_exporter.go:642”

robrich · March 9, 2021, 3:26pm

Thanks for the bug report on the gists not loading. I’ve filed a ticket.

Nothing stands out in the logs, and if you can connect to SingleStore studio website but not the database itself, the container isn’t down. I’ve had trouble when my password wasn’t long or complex enough. Perhaps try changing it from pass to something weirder and recreating the deployment?

emanuelbesliu · March 9, 2021, 3:38pm

Thanks! Seems it’s not reaching out the Mem connection, I’ve already tried with more complex passwords, nothing.

mysql --version

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

Maybe there is something here. Are there any other packages I should check?

robrich · March 9, 2021, 3:47pm

Well that wasn’t it. There was also a problem with the container some time ago where it wouldn’t correctly run init.sql but that doesn’t seem the case here either. Do you have a GitHub repo of these files so I can reproduce the concern?

robrich · March 11, 2021, 3:15am

I forked the “Getting Started with Python” sample repo and created GitHub - singlestore-labs/start-with-singlestore-python-kubernetes that does standard CRUD methods from Python. Try this out and see if it works as expected, then let’s diff this with your content and see if we can trap the error and document this better.

emanuelbesliu · March 12, 2021, 10:09am

Using HOST = 'localhost' it gives me:

MySQLdb._exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)")

Using HOST = '127.0.0.1' it gives me:

MySQLdb._exceptions.OperationalError: (2012, 'Error in server handshake')

One question regarding Single Store implementation: is it possible to save ML models into memsql table? Couldn’t figure it out to make it working.

robrich · March 16, 2021, 2:02am

127.0.0.1’s answer sounds beter. How does your solution compare to the GitHub repo? Or can you create a minimal repro and post it to GitHub or BitBucket or similar? I’d love to be able to reproduce your error.

ngarg · March 16, 2022, 10:47pm

Hi,

I also facing the “handshake” error. Here is my config:

def main():

  # TODO: pull from config
  HOST = '127.0.0.1'
  PORT = 3306
  USER = 'root'
  PASSWORD = 'test'
  DATABASE = 'test_db'

  conn = database.connect(host=HOST, port=PORT, database=DATABASE, user=USER, password=PASSWORD)

The error I am getting is:

Traceback (most recent call last):
  File "/Users/user/PycharmProjects/memsqlKafka/startup_try.py", line 72, in <module>
    main()
  File "/Users/user/PycharmProjects/memsqlKafka/startup_try.py", line 16, in main
    conn = database.connect(host=HOST, port=PORT, database=DATABASE, user=USER, password=PASSWORD)
  File "/usr/local/lib/python3.8/site-packages/memsql/common/database.py", line 19, in connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/memsql/common/database.py", line 62, in __init__
    self.reconnect()
  File "/usr/local/lib/python3.8/site-packages/memsql/common/database.py", line 93, in reconnect
    conn = _mysql.connect(**self._db_args)
MySQLdb._exceptions.OperationalError: (2012, 'Error in server handshake')

I am running cluster-in-a-box as docker container on my local. Here are the details:

 memsql/cluster-in-a-box:centos-7.6.5-018454f4e3-4.0.1-1.13.0   "/startup"               4 weeks ago   Up 3 weeks   0.0.0.0:3306->3306/tcp, 3307/tcp, 0.0.0.0:8090->8080/tcp

I am able to connect to it successfully via tableplus.

Please let me know if any further info is required. Any help is appreciated.

ankit.aryson · March 17, 2022, 6:13am

Seeking for the same. Does any help please?

ngarg · March 18, 2022, 5:19pm

@ankit.aryson Quick question here: Are you able to connect to your DB via sql command line (not via any client). I figured that I am not able to do so. Either its ssl or something else, looking into it. Will update here if I am able to make it work.