Kamailio v5.3 and MySQL 8

As installed on Ubuntu 19.10, Kamailio v5.3 will not work out of the box with MySQL 8 due to changes in the way in which users are created and privileges granted between MySQL 5.x and 8.

To fix this, edit /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql as follows:

# diff /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql.orig  /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql
163,164c163,166
<       sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
<               GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';"
---
>       sql_query "" "CREATE USER '$DBRWUSER'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
>                     CREATE USER '$DBROUSER'@'$DBHOST' IDENTIFIED BY '$DBROPW';
>               GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST';
>               GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST';"
172,173c174,177
<               sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost' IDENTIFIED  BY '$DBRWPW';
<                       GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';"
---
>               sql_query "" "CREATE USER '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
>                               CREATE USER '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';
>                       GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost';
>                       GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost';"
181,182c185,188
<               sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED  BY '$DBRWPW';
<                       GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';"
---
>               sql_query "" "CREATE USER '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
>                             CREATE USER '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';
>                       GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST';
>                       GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST';"

The above worked fine for me but do note:

  • Make sure the database and users do not already exist on the database (or delete them if they do).
  • Use a different username for the read-only and read-write users.
  • MySQL 8 has a bug so issue FLUSH PRIVILEGES if you have trouble manually removing a user.