Docker Swarm - ERROR 2002 (HY000): Can't connect to MYSQL server (115)

whoisbarry · September 27, 2023, 3:31pm

Hey there,

I used to run XIBo in a previous version on a standalone docker machine, but the project never really kicked off. Right now I need to run it within docker swarm but can’t solve the MYSQL connection.

CMS Version

4.03

All container services are up and running with this docker-compose.yml code.

version: "3.1"

services:
  cms-db:
    image: mysql:8.0
    volumes:
      - "mysql:/var/lib/mysql"
    environment:
      - MYSQL_DATABASE=cms
      - MYSQL_USER=cms
      - MYSQL_ROOT_PASSWORD=supersecurerootpassword
    deploy:
      resources:
        limits:
          memory: 1g
    env_file: config.env
    restart: always
    networks:
      default:
        aliases:
          - cms-db
    labels:
      - "com.docker.swarm.service.name=cms-db"

  cms-xmr:
    image: ghcr.io/xibosignage/xibo-xmr:0.11
    ports:
      - "9505:9505"
    restart: always
    env_file: config.env
    deploy:
      placement:
        constraints: [node.hostname == vm-dockernode02]
      resources:
        limits:
          memory: 256M
    networks:
      default:
        aliases:
          - cms-xmr
    labels:
      - "com.docker.swarm.service.name=cms-xmr"

  cms-web:
    image: ghcr.io/xibosignage/xibo-cms:release-4.0.3
    volumes:
      - /mnt/nfs/xibo/custom:/var/www/cms/custom:rw
      - /mnt/nfs/xibo/backup:/var/www/backup:rw
      - /mnt/nfs/xibo/theme:/var/www/cms/web/theme/custom:rw
      - /mnt/nfs/xibo/library:/var/www/cms/library:rw
      - /mnt/nfs/xibo/userscripts:/var/www/cms/web/userscripts:rw
      - /mnt/nfs/xibo/certs:/var/www/cms/ca-certs:rw
    restart: always
    environment:
      - XMR_HOST=cms-xmr
      - CMS_USE_MEMCACHED=true
      - MEMCACHED_HOST=cms-memcached
    env_file: config.env
    ports:
      - "8380:80"
    deploy:
      resources:
        limits:
          memory: 1g
    networks:
      default:
        aliases:
          - cms-web
    labels:
      - "com.docker.swarm.service.name=cms-web"

  cms-memcached:
    image: memcached:alpine
    command: memcached -m 15
    restart: always
    deploy:
      resources:
        limits:
          memory: 100M
    networks:
      default:
        aliases:
          - cms-memcached
    labels:
      - "com.docker.swarm.service.name=cms-memcached"

  cms-quickchart:
    image: ianw/quickchart
    restart: always
    networks:
      default:
        aliases:
          - cms-quickchart
    labels:
      - "com.docker.swarm.service.name=cms-quickchart"

networks:
  default:
    driver: overlay

volumes:
  mysql:
    driver_opts:
      type: "nfs"
      o: "addr=10.10.10.10,nolock,soft,rw"
      device: ":/docker-vol/xibo/db"

I had to remove the links option, since it’s not supported for docker swarm and replaced with some kind of double kill nerworks-aliases as well as labels.

Anyhow service cms-web still keeps failing with an MYSQL connection error:

ERROR 2002 (HY000): Can't connect to MYSQL server 'cms-db' (115)

This is what my config.env file looks like:

MYSQL_HOST=cms-db
MYSQL_PORT=3306
MYSQL_DATABASE=cms
MYSQL_USER=cms
MYSQL_PASSWORD=obliouslymysqlpassword

Any idea of what to check or how to get the connection working?

dan · September 29, 2023, 2:32pm

I don’t think you need to alias the networks - as long as all containers share the same network (which they should) then the container name should be accessible between containers.

You could remove the aliases and try again (probably after down to clear up)

whoisbarry · October 2, 2023, 1:41pm

I think there must be an other issue somewhere.
I killed the stack and redeployed it without the network alias, what causes XIBO-WEB to state:

ERROR 2005 (HY000): Unknown MySQL server host 'mysql' (-2)

Since I don’t realy care about where the DB runs I tried to set up an external DB an configure the config.env file according to that:

MYSQL_HOST=[IP_OF_MYSQL_HOST]
MYSQL_PORT=3306
MYSQL_DATABASE=xibo
MYSQL_USER=xibo
MYSQL_PASSWORD=[MYSQL_PASSWORD]

Seems to work a little better, but not perfect yet

XIBO-WEB log:

AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 10.0.16.9. Set the 'ServerName' directive globally to suppress this message

dan · October 2, 2023, 7:56pm

ERROR 2005 (HY000): Unknown MySQL server host ‘mysql’ (-2)

Odd - your config.env was set to MYSQL_HOST=cms-db so it shouldn’t be trying to connect to mysql.

Once your containers are up, can you docker-compose exec cms-web sh -c "echo $MYSQL_HOST" and see what is output?

I wonder if the config.env file isn’t being mapped in somehow.

The CMS shows this when there are migrations to run, which should be taken care of by the cms-web container on start up.

What is the output of docker-compose logs cms-web ?

whoisbarry · October 4, 2023, 8:14am

Sorry, my bad! I removed the MYSQL_HOST=cms-db since it wasn’t mentioned in the config.env template.

For some reason nothing at all:

I did set MYSQL_HOST=cms-db in the config.env file again, cleared the stack and redeployed it. The command docker-compose exec cms-web sh -c "echo $MYSQL_HOST" still outputs an empty value, but now cms-web logs ERROR 2002 (HY000): Can't connect to MySQL server on 'cms-db' (115) again.

I checked the cms-db log to see whether the connection is refused somewhere on db side, but doesn’t seem so.

2023-10-04T07:29:08.542146Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.34'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2023-10-04T07:29:08.542288Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
```

whoisbarry · October 6, 2023, 11:24am

So I’m feeling like going with an external MYSQL-DB since I can already connect to it…
Bad thing that I’m still stuck on the “upgrade in progress” screen.

I wasn’t able to find anything on the docs regarding the requirements of the MYSQL DB. Currently my DB has “utf8_general_ci” collation - is this correct?

DB user obviously can connect and alter the DB.

Any idea what’s killing it?

dan · October 9, 2023, 4:33pm

Yeah that’s fine.

The offline upgrade message you’re seeing gets shown because the output of phinx status shows that there are migrations to run.

Please can you try running this and get the output?

docker-compose exec cms-web sh -c "cd /var/www/cms; php vendor/bin/phinx status"

whoisbarry · October 10, 2023, 6:27am

Output looks like phinx is not running at all, status of every single migration ID is down.

whoisbarry · October 11, 2023, 2:39pm

Just ran a blank installation to see whether I missed out on something. I recognized one line in the xibo-web log:

 == 20180130073838 InstallMigration: migrating 
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in /var/www/cms/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:192

The phinx status is still shown as down on all migrations, even after starting migration manually by

docker-compose exec cms-web sh -c "cd /var/www/cms; php vendor/bin/phinx migrate"

How can the mentioned key be modified?

dan · October 12, 2023, 4:50pm

Ah yes, if the first migration fails then all bets are off.

Is your external MySQL 5.6 by any chance?

767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).

We could find and fix that key, but I think you’ll hit that multiple times as you progress through the migrations.

I think it will be this table causing the issue:

        $widgetOption = $this->table('widgetoption', ['id' => false, 'primary_key' => ['widgetId', 'type', 'option']]);
        $widgetOption
            ->addColumn('widgetId', 'integer')
            ->addColumn('type', 'string', ['limit' => 50])
            ->addColumn('option', 'string', ['limit' => 254])
            ->addColumn('value', 'text', ['null' => true])
            ->addForeignKey('widgetId', 'widget', 'widgetId')
            ->save();

The primary key is on two string fields = varchar(50) and varchar(254) with utf8_general_ci using 3 bytes per character, for 921 bytes in total.

I don’t think this would have been a problem in v3, because we used utf8mb3 instead of uft8mb4.

whoisbarry · October 13, 2023, 10:38am

Ah, seeams like it’s the same problem with MariaDB.

I installed MYSQL 8.1 on the remote server and Xibo started right away!
Still can’t understand why Xibo won’t find the db when hosted in an own container on the same docker network - but anyhow it works now with the remote db.

Thank you very much for your help!

system · January 12, 2024, 4:38pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.