Как исправить Ошибку в Docker?

Question

[isaac-21]

есть два контейнера, один с скриптом python, второй mysql
в логах вижу что, у докера с python есть ошибка подключения к БД, подскажите пожалуйста, что я делаю не так?

version: "3.5"
services:
  db:
    image: mysql:latest
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - ./db_data:/var/lib/mysql
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: fil
      MYSQL_DATABASE: doc
      MYSQL_USER: fil
      MYSQL_PASSWORD: fil
  python-test:
    image: python:3
    build: .
    links:
      - db
volumes:
  db_data:

FROM python:3
WORKDIR /usr/src/app
COPY . .
RUN pip install --no-cache-dir -r requirements.txt

CMD ["python", "main.py"]

from sqlalchemy import create_engine, Table, MetaData, Column, BIGINT, String, Integer, FLOAT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import pymysql

pymysql.install_as_MySQLdb()
engine = create_engine("mysql://fil:fil@db:3306/doc", encoding="utf8")

Base = declarative_base()
Session = sessionmaker(bind=engine)


class Doc(Base):
    __tablename__ = 'Docker'

    def __init__(self, text):
        self.text = text

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    text = Column(String(100))


Base.metadata.create_all(engine)
session = Session()

session.add(Doc('text'))
session.commit()

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 613, in connect
    sock = socket.create_connection(
  File "/usr/local/lib/python3.9/socket.py", line 843, in create_connection
    raise err
  File "/usr/local/lib/python3.9/socket.py", line 831, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 509, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'db' ([Errno 111] Connection refused)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/app/main.py", line 23, in <module>
    Base.metadata.create_all(engine)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 4553, in create_all
    bind._run_visitor(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2094, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "/usr/local/lib/python3.9/contextlib.py", line 117, in __enter__
    return next(self.gen)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2086, in _optional_conn_ctx_manager
    with self._contextual_connect() as conn:
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2302, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2339, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1583, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 509, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'db' ([Errno 111] Connection refused)")
(Background on this error at: http://sqlalche.me/e/13/e3q8)

CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS                     PORTS                               NAMES
84ac8cd290fa   python:3       "python main.py"         6 minutes ago   Exited (1) 6 minutes ago                                       docker_python-test_1
30c6438df401   mysql:latest   "docker-entrypoint.s…"   6 minutes ago   Up 6 minutes               33060/tcp, 0.0.0.0:3307->3306/tcp   docker_db_1

Comments

[smilingcheater]

Возможно контейнер питона стартует намного быстрее контейнера БД. Добавьте параметр, чтобы он дожидался старта БД сначала, параметр depends_on
https://docs.docker.com/compose/compose-file/compo...

[isaac-21]

smilingcheater, не помогло, вылетает точно с такой же ошибкой

[smilingcheater]

isaac-21, после старта контейнера на проброшенный 3307 порт подключиться удаётся?

[isaac-21]

smilingcheater, Да получается

huston@huston:~/PycharmProjects/docker$ docker exec -it 30c6438df401  mysql -ufil -p -P3307
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

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.

mysql>

а если пробую запустить, отдельно контейнер с питоном, то та же ошибка вылетает

Отдельно попробавл подключиться через скрипт питона на порт 33307 (без докера) и получилось, может он видит только localhost ??

engine = create_engine("mysql://fil:fil@localhost:3307/doc", encoding="utf8")

[сергей кузьмин]

isaac-21, когда соединяетесь с localhost то порт 3307 а когда с db то 3306

Answer 1

[dooMoob]

Пробрасываешь порт 3307, а подклчаешься к 3306

Comments

[isaac-21]

dooMoob , при попытке подключения на 3307, выдает ту же ошибку

from sqlalchemy import create_engine, Table, MetaData, Column, BIGINT, String, Integer, FLOAT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import pymysql

pymysql.install_as_MySQLdb()
engine = create_engine("mysql://fil:fil@db:3307/doc", encoding="utf8")

Base = declarative_base()
Session = sessionmaker(bind=engine)


class Doc(Base):
    __tablename__ = 'Docker'

    def __init__(self, text):
        self.text = text

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    text = Column(String(100))


Base.metadata.create_all(engine)
session = Session()

session.add(Doc('text'))
session.commit()

huston@huston:~/PycharmProjects/docker$ docker-compose up -d
Creating network "docker_default" with the default driver
Creating volume "docker_db_data" with default driver
Creating docker_db_1 ... done
Creating docker_python-test_1 ... done
huston@huston:~/PycharmProjects/docker$ docker-compose ps -a
        Name                      Command               State                  Ports              
--------------------------------------------------------------------------------------------------
docker_db_1            docker-entrypoint.sh --def ...   Up       0.0.0.0:3307->3306/tcp, 33060/tcp
docker_python-test_1   python main.py                   Exit 1   
huston@huston:~/PycharmProjects/docker$ docker ps -a
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS                      PORTS                               NAMES
babc7a3ac663   python:3       "python main.py"         35 seconds ago   Exited (1) 34 seconds ago                                       docker_python-test_1
4db8ecdee029   mysql:latest   "docker-entrypoint.s…"   36 seconds ago   Up 35 seconds               33060/tcp, 0.0.0.0:3307->3306/tcp   docker_db_1
huston@huston:~/PycharmProjects/docker$ docker logs babc7a3ac663
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 613, in connect
    sock = socket.create_connection(
  File "/usr/local/lib/python3.9/socket.py", line 843, in create_connection
    raise err
  File "/usr/local/lib/python3.9/socket.py", line 831, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 509, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'db' ([Errno 111] Connection refused)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/app/main.py", line 23, in <module>
    Base.metadata.create_all(engine)

[dooMoob]

После 20 попытки удалось запустить, судя по всему проблема в command. Итоговые файлы:
compose:

version: "3.5"
services:
  db:
    image: mysql:latest
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: fil
      MYSQL_DATABASE: doc
      MYSQL_USER: fil
      MYSQL_PASSWORD: fil
  python-test:
    image: python:3
    build: .
    depends_on:
      - db

main:

from sqlalchemy import create_engine, Table, MetaData, Column, BIGINT, String, Integer, FLOAT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
import pymysql

pymysql.install_as_MySQLdb()
engine = create_engine("mysql://fil:fil@db/doc", encoding="utf8", echo=True)

Base = declarative_base()
Session = sessionmaker(bind=engine)


class Doc(Base):
    __tablename__ = 'Docker'

    def __init__(self, text):
        self.text = text

    id = Column(BIGINT, primary_key=True, autoincrement=True)
    text = Column(String(100))


Base.metadata.create_all(engine)
session = Session()

session.add(Doc('text'))
session.commit()

не забудь убить старый контейнер бд с его вольюмом, каким-то образом сама бд сыпала ошибки при старте

[dooMoob]

еще одно замечание: база стартует на самом деле позже, чем говорит об этом компоузу, поэтому в питон скрипте надо поставить слип секунд 30 перед подключением к бд

[isaac-21]

dooMoob, Спасибо тебе большое!!
скажи пожалуйста, ты volume специально убрал ?
как я понимаю, он не играл роли особой, там в основном из за времени старта БД было да ?
и еще два вопроса буквально, ращве команда " depends_on" не ждет пока бд запуститься?
и если нужно выставить что бы скрипт python постоянно обновлялся, то я так понимаю, это лучше делать тоже в докере в компосе да ?? (к примеру через каждые n часов обновляй базу), если вдруг команду подскажешь, буду очень признателен

[dooMoob]

1) вольум убрал не специально, просто не хотел кешировать базу во время теста, тебе надо будет его вернуть
2) примерно такая последовательность: сервис бд стартует, компос получает калбэк и запускает питон скрипт. НО при этом сама СУБД в контейнере еще не готова принимать запросы, она создает БД, юзера и занимается еще какой-то ерундой.
3) не совсем понял, что ты хочешь. Если ты хочешь, чтобы у тебя этот скрипт постоянно запускался, то у тебя 2 варианта:
1. ставишь в крон задачу docker-compose build python-test docker-compose run --rm python-test
2. в самом скрипте делаешь слип на нужный тебе период