search

OpenSIPS / opensips-cli

OpenSIPS CLI tool - an interactive command line tool that can be used to control and monitor OpenSIPS servers.
GNU General Public License v3.0
85 stars 48 forks source link

Database creation fails with opensips-cli and MariaDB 10.1 #57

Closed hamid-elaosta closed 4 years ago

hamid-elaosta commented 4 years ago

I'm running this inside a Docker container running Ubuntu 18.04 and OpenSIPs 3.0.2-1 from apt.opensips.org. opensips-cli is from master.

Why is it trying to connect to the local mysqld.sock?

After this error, if I inspect the database I have an opensips user and an opensips database with no tables.

My goal is to replace opensipsdbctl create which I was running from an automated script, and using the credentials from ENV.

opensips@sip:/$ opensips-cli -o database_admin_url='mysql://root:Y_Op=6>O6Qv27FLb?4xg@192.168.1.201:3306/' -x database create
INFO: creating access user for opensips ...
INFO: created user 'opensips'@'localhost'
INFO: set password for 'opensips'@'localhost'
INFO: granted access to 'opensips'@'localhost' on 'opensips'
INFO: flushed privileges
ERROR: 
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 453, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/connections.py", line 208, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2006, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/db.py", line 136, in __init__
    self.__conn = self.__engine.connect().\
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2193, in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 103, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2293, in raw_connection
    self.pool.unique_connection, _connection
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2266, in _wrap_pool_connect
    e, dialect, self
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1536, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 453, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/connections.py", line 208, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
(Background on this error at: http://sqlalche.me/e/e3q8)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/modules/database.py", line 428, in ensure_user
    db = self.get_db(db_url, db_name, check_access=True)
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/modules/database.py", line 551, in get_db
    return osdb(db_url, db_name)
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/db.py", line 149, in __init__
    raise osdbAccessDeniedError
opensipscli.db.osdbAccessDeniedError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 453, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/connections.py", line 208, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2006, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/db.py", line 136, in __init__
    self.__conn = self.__engine.connect().\
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2193, in connect
    return self._connection_cls(self, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 103, in __init__
    else engine.raw_connection()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2293, in raw_connection
    self.pool.unique_connection, _connection
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2266, in _wrap_pool_connect
    e, dialect, self
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1536, in _handle_dbapi_exception_noconnection
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 2262, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 303, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 760, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 492, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 139, in _do_get
    self._dec_overflow()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/impl.py", line 136, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 308, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 437, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/pool/base.py", line 639, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.3.3-py3.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 453, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/__init__.py", line 85, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/mysqlclient-1.3.14-py3.6-linux-x86_64.egg/MySQLdb/connections.py", line 208, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
(Background on this error at: http://sqlalche.me/e/e3q8)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/modules/database.py", line 437, in ensure_user
    db = self.get_db(db_url, db_name, check_access=True)
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/modules/database.py", line 551, in get_db
    return osdb(db_url, db_name)
  File "/usr/local/lib/python3.6/dist-packages/opensipscli-0.1.0-py3.6.egg/opensipscli/db.py", line 149, in __init__
    raise osdbAccessDeniedError
opensipscli.db.osdbAccessDeniedError
ERROR: failed to connect to {} with non-admin user
hamid-elaosta commented 4 years ago

Upon further investigation, it appears that the tool is creating my user in the database as "opensips@192.168.1.201".

I ran the exact same command as before, after dropping the user and database manually, but the output this time was different, I've just included the pertinent part;

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, "Access denied for user 'opensips'@'172.31.31.1' (using password: YES)")
(Background on this error at: http://sqlalche.me/e/e3q8)

This of course is incorrect, I'm connecting to the database remotely, so I need to be able to specify the 'user@host' which is created for opensips user otherwise I cannot connect remotely.

liviuchircu commented 4 years ago

hey, @hamid-elaosta ! Thanks for the report, this should now be fixed by c7b3a2f

hamid-elaosta commented 4 years ago

@liviuchircu Amazing, thanks! :partying_face: