How to create a database

Off-Topic
1

i’m running cluster-in-a-box (cliab) in docker-compose. I have created and account and I have a license key. I have 2 containers: 1) cliab 2) a container with a python script connects to the cliab and tries to create a database.

i used this for my cliab entry in docker-compose.yaml

the python script successfully connects but then I see this:

compose-env-setup | [WARN] MySQLError during execute statement CREATE DATABASE IF NOT EXISTS mydevdb;
compose-env-setup |
compose-env-setup | Args: ‘(1752, ‘Creating a database on a leaf is forbidden. Instead call this on the master aggregator.’)’

i have read this

that topic says “Run SHOW AGGREGATORS and make sure your connecting to those hosts/ports when running queries.” and that is what I am doing, as far as I can tell.

and my show aggregrators output:

memsql> show aggregators;
±----------±-----±-------±-------------------±-----------------------------±------------------±-------+
| Host | Port | State | Opened_Connections | Average_Roundtrip_Latency_ms | Master_Aggregator | NodeId |
±----------±-----±-------±-------------------±-----------------------------±------------------±-------+
| 127.0.0.1 | 3306 | online | 1 | NULL | 1 | 1 |
±----------±-----±-------±-------------------±-----------------------------±------------------±-------+
1 row in set (0.00 sec)

I read in the docs that I need to run BOOTSTRAP AGGREGATOR to elevate to master aggregator.

compose-env-setup | BOOTSTRAP AGGREGATOR memsql;
compose-env-setup |
compose-env-setup |  Args: '(1706, 'A valid license is required to run BOOTSTRAP.  Please run SET LICENSE or contact sales@memsql.com.')'

But I am already telling it the license.

Any suggestions for how to connect and create a database?

my docker-compose.yaml:

version: "3.7"

services:
  memsql:
    image: memsql/cluster-in-a-box
    container_name: memsql
    ports:
      - "3306:3306"
      - "8080:8080"
    environment:
        LICENSE_KEY: "KEY_THAT_I_GOT_FROM_THE_WEBSITE_in_Base64"
        START_AFTER_INIT: "Y"

  compose-env-setup:
    build:
      context: utils/compose-env-setup
    container_name: compose-env-setup
    depends_on:
    - memsql
2

Hi There,

A couple of questions first. Did you follow our easy-to-follow guide to install MemSQL in a docker container. Here is the link for that https://docs.memsql.com/v7.0/guides/deploy-memsql/alternative-methods/docker/step-1/

Also the output of the ‘show aggregators’ indicates a 1 under Master Aggregator which means you have connected to the Master aggregator.
Can you also see what ‘show leaves’ output is?

I would recommend you go through the entire guide, there are steps about using MemSQL first party tools like memsqlctl to investigate further.

Hope this helps you to move forward, feel free to post back with your progress.

Thanks,

3

Thanks for the response. I did follow the cluster-in-a-box tutorial and all the steps work fine.

Seems like it’s a race condition. It works if the memsql container is started first, then the compose-env-setup container which runs the python script is started.

$ docker-compose up -d memsql
$ docker-comose up

The error message ‘Creating a database on a leaf is forbidden. Instead call this on the master aggregator.’ was pretty misleading, really sent me on the wrong track.

The python script was written for memsql/quickstart container. Now we updated to ciab. Maybe ciab takes a little longer to start.

The python MySQLdb connect method succeeds, but then the next command which is ‘CREATE DATABASE’, hits the “creating on leaf is forbidden” error. However if there is a bit of delay after ciab starts, connect succeeds and then ‘CREATE DATABASE’ is called on master aggregator.

4

Hi int32bit,

Thank you so much for posting your detailed response. I am glad that it finally worked for you.

You have provided some valuable feedback to our engineering team and also helped the larger MemSQL Forum Community.

Enjoy MemSQL!

Thanks,

5

I’m glad you enjoyed the docker-compose.yaml from the blog post. It’s a really cool way to get MemSQL up and running fast.

One thought: since you’ve identified that starting MemSQL first and the Python app second works, you may want to look at depends_on in the docker-compose.yaml. See Docker’s docs at Overview | Docker Documentation It ensures that the first container is fully running before starting the second container.

The other thing I’ve done in this scenario is to loop in my initialization script, continuing to run the query until I don’t get the weird error or a really long timeout – a few minutes. It’s definitely a hacky solution, but it’s a really simple solve if that’s what you need.

The cool part is once you get to production, the MemSQL cluster will already be running by the time the app container arrives, so this developer-time race condition won’t affect you later on.

6

Thanks for the info. Yes ‘depends_on’ is set. My docker-compose.yaml is posted above just fyi. I think ‘depends_on’ just affects start up order. It doesn’t check if the first container has finished starting or is “ready”.

The python script does have an init loop. Thing is, the loop retries the connect call. After a couple or three tries, connect succeeds. It’s the next call ‘CREATE DATABASE’ that gets the ‘create on leaf’ error.

The race is between the time the connect call succeeds and master aggregator becomes available.

We could add a retry loop to the ‘CREATE DATABASE’ call, or around every database function in our script. A better design might be for ciab to wait til master aggregator is ready before allowing any connects.

7

Good call. I didn’t see the depends_on in your setup. Yeah, I usually put both the connect, the create database (if not exists), and often a seed insert (if db is empty) inside a retry loop. If any of them fail, I restart until they do or until 10 minutes elapses. (If my network connection is off, I don’t want to infinite retry forever.) A more robust container setup would use an image with a readiness probe that could validate the database was fully ready, allowing subsequent containers to wait more carefully.