Connect to MariaDB Database on Azure

Hello Everyone,
I have been trying to deploy ERPNext on Azure using a Linux VM and a MariaDB server, I have Installed bench on the VM and can connect to the database using

mysql -h Server_name.mariadb.database.azure.com --user server admin login name -p

I have set the “db_host” key/value pair as the server name in common_site_config.json file

I have set the database to listen to the VM’s public IP and also added the database port in both inbound and outbound port rules


Yet anytime i try bench new-site site1.remote i get this error

Traceback (most recent call last):
File “/usr/lib/python3.5/runpy.py”, line 184, in _run_module_as_main
main”, mod_spec)
File “/usr/lib/python3.5/runpy.py”, line 85, in _run_code
exec(code, run_globals)
File “/home/talleyrand333/1/apps/frappe/frappe/utils/bench_helper.py”, line 97, in
main()
File “/home/talleyrand333/1/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/talleyrand333/1/apps/frappe/frappe/commands/site.py”, line 32, in new_site
db_type=db_type)
File “/home/talleyrand333/1/apps/frappe/frappe/commands/site.py”, line 69, in _new_site
source_sql=source_sql, force=force, reinstall=reinstall, db_type=db_type)
File “/home/talleyrand333/1/apps/frappe/frappe/installer.py”, line 34, in install_db
setup_database(force, source_sql, verbose)
File “/home/talleyrand333/1/apps/frappe/frappe/database/init.py”, line 16, in setup_database
return frappe.database.mariadb.setup_db.setup_database(force, source_sql, verbose)
File “/home/talleyrand333/1/apps/frappe/frappe/database/mariadb/setup_db.py”, line 39, in setup_database
if force or (db_name not in dbman.get_database_list()):
File “/home/talleyrand333/1/apps/frappe/frappe/database/db_manager.py”, line 61, in get_database_list
return [d[0] for d in self.db.sql(“SHOW DATABASES”)]
File “/home/talleyrand333/1/apps/frappe/frappe/database/database.py”, line 122, in sql
self.connect()
File “/home/talleyrand333/1/apps/frappe/frappe/database/database.py”, line 75, in connect
self._conn = self.get_connection()
File “/home/talleyrand333/1/apps/frappe/frappe/database/mariadb/database.py”, line 90, in get_connection
local_infile = frappe.conf.local_infile)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/init.py”, line 94, in Connect
return Connection(*args, **kwargs)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/connections.py”, line 325, in init
self.connect()
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/connections.py”, line 599, in connect
self._request_authentication()
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/connections.py”, line 861, in _request_authentication
auth_packet = self._read_packet()
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/connections.py”, line 684, in _read_packet
packet.check_error()
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/talleyrand333/1/env/lib/python3.5/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (9002, ‘The connection string may not be right. Please visit portal for references.\x00’)

I can’t seem to find what i am doing wrong, any sort of help would be really great .

Post the db details from common_site_config.json (without the usename and password)

Hi @crafter ,

{
“auto_update”: false,
“background_workers”: 1,
“db_host”: “azure_servername.mariadb.database.azure.com”,
“file_watcher_port”: 6787,
“frappe_user”: “ebuka333”,
“gunicorn_workers”: 2,
“rebase_on_pull”: false,
“redis_cache”: “redis://localhost:13000”,
“redis_queue”: “redis://localhost:11000”,
“redis_socketio”: “redis://localhost:12000”,
“restart_supervisor_on_update”: false,
“restart_systemd_on_update”: false,
“serve_default_site”: true,
“shallow_clone”: true,
“socketio_port”: 9000,
“update_bench_on_update”: true,
“webserver_port”: 8000
}

There are no fields for username or password set, Thanks for your help.

Did you try the public IP address of the DNS name? Can you create a Database from the ERPNext server using my sql command to the Azure Database server using the same username and password using are using to connect to database from ERPNext?

Yes i can, i have created a database called erpnext and even tried to install a site directly into that database but i keep getting the same error. i have connected to the database through the VM and also through mysqlworkbench and both connected properly.

Some ideas here.

Frappe may need to be modified to pass the connection string in the way Azure wants it.

2 Likes

I got hit with this: (bench 5.8.1, frappe 13.26.1)

frappe@X-app-staging:~/frappe-bench$ bench new-site --db-root-username X@X-mariadb --db-name X_dash_staging X-dash-staging.X.com
MySQL root password: 
Traceback (most recent call last):
  File "/usr/lib/python3.8/runpy.py", line 194, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 110, in <module>
    main()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 74, in new_site
    _new_site(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 62, in _new_site
    install_db(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 132, in install_db
    setup_database(force, source_sql, verbose, no_mariadb_socket)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/__init__.py", line 20, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 39, in setup_database
    if force or (db_name not in dbman.get_database_list()):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/db_manager.py", line 64, in get_database_list
    return [d[0] for d in self.db.sql("SHOW DATABASES")]
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 148, in sql
    self.connect()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 84, in connect
    self._conn = self.get_connection()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 92, in get_connection
    conn.select_db(self.user)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 496, in select_db
    self._read_ok_packet()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 443, in _read_ok_packet
    pkt = self._read_packet()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1049, "Unknown database 'X@X-mariadb'")

Culprit is here:

  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 92, in get_connection
    conn.select_db(self.user)

meaning it is trying to use the database named the same as the username

which isn’t possible as the username has “@” character which invalid database name even if you try to create it.

My workaround was to patch /home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py at line 91, and add the “customized root user” as the exception there.

However this only passed the “first layer of pain” as with Azure MariaDB, you’re in the world of further pains :frowning: due to Azure insisting on non-standard username syntax:

Traceback (most recent call last):
  File "/usr/lib/python3.8/runpy.py", line 194, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 110, in <module>
    main()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 74, in new_site
    _new_site(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 62, in _new_site
    install_db(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 132, in install_db
    setup_database(force, source_sql, verbose, no_mariadb_socket)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/__init__.py", line 20, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 67, in setup_database
    bootstrap_database(db_name, verbose, source_sql)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 99, in bootstrap_database
    if not check_database_settings():
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 132, in check_database_settings
    versions = get_mariadb_versions()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 25, in get_mariadb_versions
    mariadb_variables = frappe._dict(frappe.db.sql("""show variables"""))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 148, in sql
    self.connect()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 84, in connect
    self._conn = self.get_connection()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 76, in get_connection
    conn = pymysql.connect(
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 907, in _request_authentication
    auth_packet = self._read_packet()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (9002, 'FATAL: Invalid Username specified. Please check the Username and retry connection. The Username should be in <username@hostname> format.\x00')
1 Like