'database create' fails to create the user with MySQL 8.0.22

[nikhilk12]

Environment: OS: Ubuntu 18.04 LTS openSIPS version: 3.1.1 (x86_64/linux), git revision: bbbff7eef, main.c compiled on 20:50:42 Jan 11 2021 with gcc 9 openSIPS CLI version: 0.1.0 (latest MASTER branch pull) MySQL version: 8.0.22 (community edition)

I create the opensips-cli .cfg file here with the below configuration: /etc/opensips-cli.cfg

[opensips-pre-prod] log_level: WARNING prompt_name: opensips-cli prompt_intro: Welcome to OpenSIPS prompt_emptyline_repeat_cmd: False history_file: ~/.opensips-cli.history history_file_size: 1000 output_type: pretty-print communication_type: fifo fifo_file: /tmp/opensips_fifo database_path: /usr/src/opensips-3.1/scripts database_admin_url: mysql://root:password@192.168.1.145:3306/opensips database_name: opensips

If I point the database_admin_url to a fresh MySQL 5.7 instance, it builds the DB correctly and without any errors. As soon as I point the database_admin_url to a MySQL 8.0.22 instance I receive the following errors from the CLI tool after running database create:

ERROR: failed to create user 'opensips' ERROR: failed to create user on opensips DB

To make sure that it wasn't any configuration issue on my MySQL 8 instance I built a fresh MySQL 5.7 instance on both Ubuntu 20.04 and Debian 10 and pointed it to database_admin_url with a successful openSIPS DB being created. As soon as I switch back to my MySQL 8 instance it throws the error of failing to create the user.

As another point, my 'root' user has been configured in all cases to have full admin privileges across all DB's and tables with read/write configured on all my MySQL setups being tested here.

One final test I ran was remotely connecting to my MySQL 8 box from my openSIPS instance and create a new user with root privileges with success. So this issue should not be permission related one but one potentially within the CLI tool itself??

After running the database create pointing to my MySQL 8 instance and failing to create the user, I end up with an empty 'opensips' DB with of course no tables as it does not get this far in the process.

This is very easy to replicate by compiling the latest openSIPS 3.1.1 and CLI 0.1.0 and simply point the MySQL 8 instance at database_admin_url and run database create. It made zero difference in the results if the DB was running localhost or remotely.

Any suggestions as to why this could be the case with it working flawlessly with MySQL 5.x but 8.x not so much?

[chijinbo]

got the same issue here, any updates on this issue? Thanks

[nikhilk12]

Hi @chijinbo,

Unfortunately I have not heard any updates as of yet and as you are experiencing the issue is still here.

In the meantime I have continued my testing with MySQL 5.7 instances as of course this works as expected.

Thanks

Nikhil

[chijinbo]

Hi @chijinbo,

Unfortunately I have not heard any updates as of yet and as you are experiencing the issue is still here.

In the meantime I have continued my testing with MySQL 5.7 instances as of course this works as expected.

Thanks

Nikhil

Hi @nikhilk12,

I have finally managed opensips-cli working with MySQL 8.0.

In my case, the problem is that the default value for database_url is invalid.

In the default config of opensips-cli , the password for "opensips" is "opensipsrw", which is not a valid password for my db setting. So I just created a custom config file, and use a stronger password for the "opensips", then the problem solved.

Hope this help.

Jinbo

[liviuchircu]

Hey guys, I ran into a similar issue as well while doing some development. However, the issue was on my side, not related to the opensips-cli code. In my case, the root user did not have enough permissions when logging in from IP 10.0.0.10:

GRANT ALL ON opensips_3_1.* TO 'root'@'10.0.0.%' identified by 'liviusmysqlpassword';

This was not enough, believe it or not! Why? Because opensips-cli tries to create the "opensips / opensipsrw" user and, unfortunately, it still does not have permission! To fix this:

GRANT ALL ON opensips_3_1.* TO 'root'@'10.0.0.%' identified by 'liviusmysqlpassword' WITH GRANT OPTION;

Would this fix apply to your cases as well?

[liviuchircu]

Cleaning up this issue for now, most likely the ... WITH GRANT OPTION privilege was missing, so the opensips user could not be created.