Getting access denied when MemSQL tries to execute init.sql

[mxsoftwarelabs]

Docker compose:

version: '2'

volumes:
  memsql-ciab-data:

services:

db:
    image: memsql/cluster-in-a-box
    restart: always
    volumes:
      - ./init.sql:/init.sql
      - memsql-ciab-data:/var/lib/memsql
    environment:
      - LICENSE_KEY=${MEMSQL_LICENSE_KEY}
      - ROOT_PASSWORD=${MEMSQL_ROOTPASSWORD}
    ports:
      - 3306:3306
      - 9000:9000

Error:

db_1                | 2020-12-13 03:49:57.712151 Running /init.sql...
db_1                | ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
db_1                | Traceback (most recent call last):
db_1                |   File "/startup", line 108, in <module>
db_1                |     init_cluster()

I am able to connect to the MemSQL instance with the password I have set as ROOT_PASSWORD and I can see the value of root password matching one provided when I inspect the running docker using docker dashboard.

[mxsoftwarelabs]

to fix it on my fork, I moved the init.sql execution right below - ctl("add-leaf", "--host", "127.0.0.1", "--port", "3307") in startup file.

I am not sure if this is the right way of fixing the problem, but I couldn't make the it work by sending the password via subprocess in startup file.

[davidgomes]

@mxsoftwarelabs this is a great find!

I think your fix will always work since init.sql would always be executed before setting a password. I will get a Pull Request up with this fix.

I also tried a different solution which is to try to execute init.sql with the password that's passed in:

diff --git a/ciab-assets/startup b/ciab-assets/startup
index 2ab2fc4..ec8ee7c 100755
--- a/ciab-assets/startup
+++ b/ciab-assets/startup
@@ -4,6 +4,7 @@ from __future__ import print_function

 import subprocess
 import signal
+import time
 import os
 import sys
 import json
@@ -59,10 +60,12 @@ def init_cluster():
     subprocess.check_output(["memsql-toolbox-config", "register-host", "--yes", "--localhost", "--host", "127.0.0.1"])
     log("Done.")

+    time.sleep(30)
+
     if os.path.isfile("/init.sql"):
         log("Running /init.sql...")
         init_file = open("/init.sql", "r")
-        subprocess.check_output(["memsql"], stdin=init_file)
+        subprocess.check_output(["memsql", "-p", root_password], stdin=init_file)
         log("Done.")

     if stop_after_init:

However, this fix didn't work:

$ docker run --mount type=bind,source=/home/gomes/temp/init.sql,target=/init.sql --name memsql-ciab5 -e LICENSE_KEY=${LICENSE_KEY} -e ROOT_PASSWORD=password -p 3310:3306 memsql/cluster-in-a-box

2020-12-22 17:19:20.826424 Initializing MemSQL Cluster in a Box
2020-12-22 17:19:20.826478 Creating...
2020-12-22 17:19:21.161491 Done.
2020-12-22 17:19:21.161550 Configuring...
2020-12-22 17:19:21.910731 Done.
2020-12-22 17:19:21.910770 Bootstrapping...
2020-12-22 17:19:27.764266 Done.
2020-12-22 17:19:27.764305 Configuring Toolbox...
2020-12-22 17:19:27.772178 Done.
2020-12-22 17:19:57.802081 Running /init.sql...
Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Initialization failed, please fix any issues and re-initialize the container.

You may need to remove this container before continuing:
    docker rm (CONTAINER_NAME)

Traceback (most recent call last):
  File "/startup", line 111, in <module>
    init_cluster()
  File "/startup", line 68, in init_cluster
    subprocess.check_output(["memsql", "-p", root_password], stdin=init_file)
  File "/usr/lib64/python2.7/subprocess.py", line 575, in check_output
    raise CalledProcessError(retcode, cmd, output=output)
subprocess.CalledProcessError: Command '['memsql', '-p', 'password']' returned non-zero exit status 1

I tried to debug it but I really can't see what could be happening.

[davidgomes]

In the meantime, I've figured out the problem:

+        subprocess.check_output(["memsql", "-p{}".format(root_password)], stdin=init_file)

I had to do that since the mysql CLI accepts -p{password} but not -p {password}.