Errors while facing alembic for asterisk realtime

sachin

Overview

This tutorial describes the configuration of Asterisk's PJSIP channel driver with the "realtime" database storage backend. The realtime interface allows storing much of the configuration of PJSIP, such as endpoints, auths, aors and more, in a database, as opposed to the normal flat-file storage of pjsip.conf.

Installing Dependencies

For the purposes of this tutorial, we will assume a base Ubuntu 12.0.4.3 x86_64 server installation, with the OpenSSH server and LAMP server options, and that Asterisk will use its ODBC connector to reach a back-end MySQL database.

Beyond the normal packages needed to install Asterisk 12 on such a server (build-essential, libncurses5-dev, uuid-dev, libjansson-dev, libxml2-dev, libsqlite3-dev) as well as the Installation of pjproject, you will need to install the following packages:

• unixodbc and unixodbc-dev
  • ODBC and the development packages for building against ODBC
• libmyodbc
  • The ODBC to MySQL interface package
• python-dev and python-pip
  • The Python development package and the pip package to allow installation of Alembic
• python-mysqldb
  • The Python interface to MySQL, which will be used by Alembic to generate the database tables

So, from the CLI, perform:

# apt-get install unixodbc unixodbc-dev libmyodbc python-dev python-pip python-mysqldb

Once these packages are installed, check your Asterisk installation's make menuconfig tool to make sure that the res_config_odbc and res_odbc resource modules, as well as the res_pjsip_xxx modules are selected for installation. If they are, then go through the normal Asterisk installation process: ./configure; make; make install

And, if this is your first installation of Asterisk, be sure to install the sample files: make samples

Creating the MySQL Database

Use the mysqladmin tool to create the database that we'll use to store the configuration. From the Linux CLI, perform:

# mysqladmin -u root -p create asterisk

This will prompt you for your MySQL database password and then create a database named asterisk that we'll use to store our PJSIP configuration.

Installing and Using Alembic

Alembic is a full database migration tool, with support for upgrading the schemas of existing databases, versioning of schemas, creation of new tables and databases, and a whole lot more. A good guide on using Alembic with Asterisk can be found on the Managing Realtime Databases with Alembic wiki page. A shorter discussion of the steps necessary to prep your database will follow.

First, install Alembic:

# pip install alembic

Then, move to the Asterisk source directory containing the Alembic scripts:

# cd contrib/ast-db-manage/

Next, edit the config.ini.sample file and change the sqlalchemy.url option, e.g.

sqlalchemy.url = mysql://root:password@localhost/asterisk

such that the URL matches the username and password required to access your database.

Then rename the config.ini.sample file to config.ini

# cp config.ini.sample config.ini

Finally, use Alembic to setup the database tables:

# alembic -c config.ini upgrade head

You'll see something similar to:

# alembic -c config.ini upgrade head
INFO  [alembic.migration] Context impl MySQLImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 4da0c5f79a9c, Create tables
INFO  [alembic.migration] Running upgrade 4da0c5f79a9c -> 43956d550a44, Add tables for pjsip
#

You can then connect to MySQL to see that the tables were created:

# mysql -u root -p -D asterisk

mysql> show tables;
+--------------------+
| Tables_in_asterisk |
+--------------------+
| alembic_version    |
| iaxfriends         |
| meetme             |
| musiconhold        |
| ps_aors            |
| ps_auths           |
| ps_contacts        |
| ps_domain_aliases  |
| ps_endpoint_id_ips |
| ps_endpoints       |
| sippeers           |
| voicemail          |
+--------------------+
12 rows in set (0.00 sec)
mysql> quit

sachin

while installing `pip3 install alembic`

    error: command 'gcc' failed with exit status 1
    ----------------------------------------
Command "/usr/bin/python3 -u -c "import setuptools, tokenize;__file__='/tmp/pip-build-3j618a__/greenlet/setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record /tmp/pip-qnn1nwes-record/install-record.txt --single-version-externally-managed --compile" failed with error code 1 in /tmp/pip-build-3j618a__/greenlet/

Solved through this -> and have to run multiple times

pip3 install --upgrade pip

sachin

alembic -c config.ini upgrade head

  File "/usr/local/lib64/python3.6/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 163, in dbapi
    return __import__("MySQLdb")
ModuleNotFoundError: No module named 'MySQLdb'

Solved by

yum install MySQL-python
pip3 install mysqlclient

But after this running alembic -c config.ini upgrade head

  File "/usr/local/lib64/python3.6/site-packages/MySQLdb/__init__.py", line 24, in <module>
    version_info, _mysql.version_info, _mysql.__file__
NameError: name '_mysql' is not defined

Solved by making changes in /usr/src/asterisk/contrib/ast-db-manage/config/env.py Add following line

import pymysql
pymysql.install_as_MySQLdb()

It will look like:

from __future__ import with_statement
from alembic import context
from alembic.script import ScriptDirectory
from alembic.operations import Operations
from sqlalchemy import engine_from_config, pool, MetaData
from sqlalchemy.ext.declarative import declarative_base
from logging.config import fileConfig
import logging
import pymysql

pymysql.install_as_MySQLdb()


# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

And then finally you can run alembic -c config.ini upgrade head