Unable to run migrations using alembic using sqlalchemy with mysql ndb cluster 8.0.25

[anandakella]

I'm unable to run the alembic migration using this command alembic upgrade head against newly installed mysql server cluster version 8.0.25 with ndb. Whenever I'm trying to run the alembic upgrade head from kubernetes pod I'm seeing the following error

alembic upgrade head
db url: mysql+mysqlconnector://root:<db password>@<ip address of mysql db server>:3306/Redfish
Traceback (most recent call last):
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 419, in checkout
    rec = pool._do_get()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 259, in _do_get
    return self._create_connection()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 362, in __init__
    self.__connect()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/venv/lib/python3.9/site-packages/mysql/connector/__init__.py", line 273, in connect
    return MySQLConnection(*args, **kwargs)
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 107, in __init__
    self.connect(**kwargs)
  File "/venv/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 1003, in connect
    self._open_connection()
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 352, in _open_connection
    self._do_auth(self._user, self._password,
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 219, in _do_auth
    self._auth_switch_request(username, password)
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 318, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1049 (42000): Unknown database 'Redfish'

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

Traceback (most recent call last):
  File "/venv/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/venv/lib/python3.9/site-packages/alembic/config.py", line 559, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/venv/lib/python3.9/site-packages/alembic/config.py", line 553, in main
    self.run_cmd(cfg, options)
  File "/venv/lib/python3.9/site-packages/alembic/config.py", line 530, in run_cmd
    fn(
  File "/venv/lib/python3.9/site-packages/alembic/command.py", line 294, in upgrade
    script.run_env()
  File "/venv/lib/python3.9/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/venv/lib/python3.9/site-packages/alembic/util/compat.py", line 182, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "alembic/env.py", line 95, in <module>
    run_migrations_online()
  File "alembic/env.py", line 81, in run_migrations_online
    with connectable.connect() as connection:
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3166, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3245, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3215, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2068, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3212, in _wrap_pool_connect
    return fn()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 301, in connect
    return _ConnectionFairy._checkout(self)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 761, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 419, in checkout
    rec = pool._do_get()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 259, in _do_get
    return self._create_connection()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 247, in _create_connection
    return _ConnectionRecord(self)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 362, in __init__
    self.__connect()
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 605, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/venv/lib/python3.9/site-packages/mysql/connector/__init__.py", line 273, in connect
    return MySQLConnection(*args, **kwargs)
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 107, in __init__
    self.connect(**kwargs)
  File "/venv/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 1003, in connect
    self._open_connection()
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 352, in _open_connection
    self._do_auth(self._user, self._password,
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 219, in _do_auth
    self._auth_switch_request(username, password)
  File "/venv/lib/python3.9/site-packages/mysql/connector/connection.py", line 318, in _auth_switch_request
    raise errors.get_exception(packet)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1049 (42000): Unknown database 'Redfish'

The following are the version of software

Python 3.9.3
alembic==1.5.5
mysql-connector-python==8.0.25
PyMySQL==1.0.2
SQLAlchemy==1.4.14
Mysql server 8.0.25 ndb cluster

Mysql server

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74
Server version: 8.0.25-cluster MySQL Cluster 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> show engines ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| clustertest        |
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | %         |
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
5 rows in set (0.01 sec)

mysql> 

NDB cluster status

$ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 10.xx.xx.144:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2	@10.xx.xx.142  (mysql-8.0.25 ndb-8.0.25, Nodegroup: 0, *)
id=3	@10.xx.xx.143  (mysql-8.0.25 ndb-8.0.25, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.xx.xx.144  (mysql-8.0.25 ndb-8.0.25)

[mysqld(API)]	1 node(s)
id=4	@10.xx.xx.144  (mysql-8.0.25 ndb-8.0.25)

ndb_mgm> 

I tried different connector pymysql migration didnt succeed. Earlier I was using mysql 5.7 alembic migration worked fine due to scale issues I had to move to vm based mysql server cluster from then onwards I'm seeing above deployment issues. Kindly let me know what could be the issue here. Thanks a lot in advance.

[CaselIT]

Hi,

From the error: sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1049 (42000): Unknown database 'Redfish' and the database list:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| clustertest        |
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
+--------------------+

you seem to have the wrong connection string: mysql+mysqlconnector://root:<db password>@<ip address of mysql db server>:3306/Redfish

[anandakella]

Table and schemas are created by alembic migration script. In the env.py this how the url is defined.

mysql_hostname = os.environ.get('MYSQL_HOSTNAME', "0.0.0.0")
mysql_database = os.environ.get('MYSQL_DATABASE', 'Redfish')
mysql_user = os.environ.get('MYSQL_USER', 'root')
mysql_port = os.environ.get('MYSQL_PORT', "3306")
mysql_password = os.environ.get('MYSQL_PASSWORD', '<password>')
connector = 'mysqlconnector'
url = 'mysql+%s://%s:%s@%s:%s/%s' % (connector,
                                     mysql_user,
                                     mysql_password,
                                     mysql_hostname,
                                     mysql_port,
                                     mysql_database)

print(url)
config.set_main_option("sqlalchemy.url", url)

Not sure what is wrong here.

[anandakella]

Read couple of posts in github and stackoverflow figured that alembic doesnt create databases. We need to do it by hand.

Solved this problem adding this logic in models.py

from sqlalchemy_utils import create_database, database_exists

def validate_database(url):
    """Method for validating database url exists or not

    :raises: None
    :returns: None
    """
    if not database_exists(url):
        create_database(url)

added this line in env.py

from sql_models.models import Base, validate_database

validate_database(url)

[CaselIT]

I just noticed that this in the wrong repository. Moved to alembic