[SR-Users] "kamdbctl create" failing with MySQL 8
Daniel-Constantin Mierla
miconda at gmail.com
Mon Nov 26 11:14:37 CET 2018
Can you make a pull request on github.com/kamailio/kamailio with the
changes to kamctl/kamdbctl so we can merge in an easy fashion?
Cheers,
Daniel
On 21.11.18 19:34, Jonathan Tyler wrote:
> Confirmed for 5.7.
>
> I ran the following on a local MySQL 5.7 instance I have on my machine, with no errors in syntax.
>
> CREATE SCHEMA kamailio;
> CREATE USER 'kamailio'@'localhost' IDENTIFIED BY 'kamailiorw';
> GRANT ALL PRIVILEGES ON kamailio.* TO 'kamailio'@'localhost';
>
> Thanks,
> Jonathan
> ------------
>
> Hello,
>
> indeed, it looks like a change in supported grant statements by the
> newer version of the mysql server.
>
> Do you know if the syntax works on older versions 5.x, at least 5.5?
>
> If works, then maybe you can make a pull request with your changes, in
> this way we can merge easily to master branch and then backport.
>
> If does not work, we should detect the version, or make it configurable
> in kamctlrc to execute one way or the other.
>
> Cheers,
> Daniel
>
> On 21.11.18 17:12, Jonathan Tyler wrote:
>> Thank you Henning :)
>>
>> After looking at the output, I noticed it was ignoring $DBACCESSHOST, so I modified kamdbctl.mysql moving it up to the top as follows...
>>
>> kamailio_db_grant () # pars: <database name>
>> {
>> if [ $# -ne 1 ] ; then
>> merr "kamailio_db_grant function takes one param"
>> exit 1
>> fi
>>
>> minfo "granting privileges to database $1 ..."
>>
>> # Users: kamailio is the regular user, kamailioro only for reading
>> if [ ! -z "$DBACCESSHOST" ] ; then
>> sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
>> GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';"
>> if [ $? -ne 0 ] ; then
>> merr "granting access host privileges to database $1 failed!"
>> exit 1
>> fi
>> elif [ "$DBHOST" != "localhost" ] ; then
>> sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
>> GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';"
>> if [ $? -ne 0 ] ; then
>> merr "granting localhost privileges to database $1 failed!"
>> exit 1
>> fi
>> else
>> sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
>> GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';"
>>
>> if [ $? -ne 0 ] ; then
>> merr "granting privileges to database $1 failed!"
>> exit 1
>> fi
>> fi
>>
>> }
>>
>> But, I'm still getting the same error.
>>
>> ## INFO: granting privileges to database kamailio ...
>> ## + '[' '!' -z % ']'
>> ## + sql_query '' 'GRANT ALL PRIVILEGES ON kamailio.* TO '\''_kamailio'\''@'\''%'\'' IDENTIFIED BY '\''<rw-password>'\'';
>> ## GRANT SELECT ON kamailio.* TO '\''_kamailio-ro'\''@'\''%'\'' IDENTIFIED BY '\''<ro-password>'\'';'
>> ## + '[' 2 -gt 1 ']'
>> ## + '[' -n '' ']'
>> ## + DB=
>> ## + shift
>> ## + '[' -n <admin-password> ']'
>> ## + mysql -h <db-host> -P 3306 -u<user> -p<admin-password> -e 'GRANT ALL PRIVILEGES ON kamailio.* TO '\''_kamailio'\''@'\''%'\'' IDENTIFIED BY '\''<rw-password>'\'';
>> ## GRANT SELECT ON kamailio.* TO '\''_kamailio-ro'\''@'\''%'\'' IDENTIFIED BY '\''<ro-password>'\'';'
>> ## mysql: [Warning] Using a password on the command line interface can be insecure.
>> ## ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '<rw-password>'' at line 1
>> ## + '[' 1 -ne 0 ']'
>> ## + merr 'granting access host privileges to database kamailio failed!'
>> ## + '[' -t 1 -a -z '' ']'
>> ## + echo -e '\E[37;31m\033[1mERROR: granting access host privileges to database kamailio failed!\033[0m'
>>
>> I loaded the query into Workbench and it told me "'_kamailio' is not valid at this position for this server version, expecting an identifier".
>> That lead me to read up on the correct syntax for GRANT and apparently you can't have "IDENTIFIED BY" in the GRANT query with MySQL 8. The account must be created first.
>>
>> Here's my changes to kamdbctl.mysql that are now working...
>>
>> kamailio_db_create () # pars: <database name>
>> {
>> if [ $# -ne 1 ] ; then
>> merr "kamailio_db_create function takes one param"
>> exit 1
>> fi
>>
>> if [ "$CHARSET" = "" ]; then
>> minfo "test server charset"
>> db_charset_test
>> fi
>>
>> minfo "creating database $1 and users $DBRWUSER, $DBROUSER ..."
>>
>> if [ ! -z "$DBACCESSHOST" ] ; then
>> sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;
>> CREATE USER '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
>> CREATE USER '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';
>> FLUSH PRIVILEGES;"
>> if [ $? -ne 0 ] ; then
>> merr "Creating database $1 on access host failed!"
>> exit 1
>> fi
>> elif [ "$DBHOST" != "localhost" ] ; then
>> sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;
>> CREATE USER '$DBRWUSER'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
>> CREATE USER '$DBROUSER'@'$DBHOST' IDENTIFIED BY '$DBROPW';
>> FLUSH PRIVILEGES;"
>> if [ $? -ne 0 ] ; then
>> merr "Creating database $1 on localhost failed!"
>> exit 1
>> fi
>> else
>> sql_query "" "CREATE DATABASE $1 CHARACTER SET $CHARSET;
>> CREATE USER '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
>> CREATE USER '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';
>> FLUSH PRIVILEGES;"
>> if [ $? -ne 0 ] ; then
>> merr "Creating database $1 failed!"
>> exit 1
>> fi
>> fi
>> }
>>
>> kamailio_db_grant () # pars: <database name>
>> {
>> if [ $# -ne 1 ] ; then
>> merr "kamailio_db_grant function takes one param"
>> exit 1
>> fi
>>
>> minfo "granting privileges to database $1 ..."
>>
>> # Users: kamailio is the regular user, kamailioro only for reading
>> if [ ! -z "$DBACCESSHOST" ] ; then
>> sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST';
>> GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST';
>> FLUSH PRIVILEGES;"
>> if [ $? -ne 0 ] ; then
>> merr "granting access host privileges to database $1 failed!"
>> exit 1
>> fi
>> elif [ "$DBHOST" != "localhost" ] ; then
>> sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost';
>> GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost';
>> FLUSH PRIVILEGES;"
>> if [ $? -ne 0 ] ; then
>> merr "granting localhost privileges to database $1 failed!"
>> exit 1
>> fi
>> else
>> sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST';
>> GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBHOST';
>> FLUSH PRIVILEGES;"
>> if [ $? -ne 0 ] ; then
>> merr "granting privileges to database $1 failed!"
>> exit 1
>> fi
>> fi
>>
>> }
>>
>> Am Mittwoch, 21. November 2018, 03:16:04 CET schrieb Jonathan Tyler:
>>> I just had the same thing happen to me. I installed Kamailio via Apt, and
>>> I'm using a dedicated MySQL server, with non-root admin credentials.
>>> Previously today I installed it using a local MySQL 5.7 database with no
>>> issues.
>>>
>>> Kamailio Host: Kamailio 5.1.2 (Ubuntu 18.04)
>>> MySQL Host: MySQL 8.0.13 (Windows Server 2012 R2)
>>>
>>> It creates the database, then fails at granting permissions. Accounts were
>>> created and privileges flushed before executing.
>>>> root at kamailio:~# kamdbctl create
>>>> INFO: creating database kamailio ...
>>>> mysql: [Warning] Using a password on the command line interface can be
>>>> insecure. INFO: granting privileges to database kamailio ...
>>>> mysql: [Warning] Using a password on the command line interface can be
>>>> insecure. ERROR 1064 (42000) at line 1: You have an error in your SQL
>>>> syntax; check the manual that corresponds to your MySQL server version
>>>> for the right syntax to use near 'IDENTIFIED BY '<password>'' at line 1
>>> I turned on query logging on the database host but didn't see any errors
>>> there, or in the error log itself.
>>>> 2018-11-21T01:58:15.045586Z 8 Connect <user>@kamailio on using SSL/TLS
>>>> 2018-11-21T01:58:15.046239Z 8 Query select @@version_comment limit 1
>>>> 2018-11-21T01:58:15.046764Z 8 Query CREATE DATABASE kamailio CHARACTER SET
>>>> utf8mb4 2018-11-21T01:58:15.189109Z 8 Quit
>>>> 2018-11-21T01:58:15.208476Z 9 Connect <user>@kamailio on using SSL/TLS
>>>> 2018-11-21T01:58:15.209019Z 9 Query select @@version_comment limit 1
>>>> 2018-11-21T01:58:15.210114Z 9 Quit
>>> I've tried with the default MySQL 5.7 Client on Ubuntu, and upgrading it to
>>> 8.0.13, but got the same result.
>>>
>>> Unfortunately I don't know how to see the output of the SQL Statement being
>>> sent (without installing 5.7), or I'd try to run it manually.
>> Hello Jonathan,
>>
>> just an idea on how to debug this further:
>>
>> Try to execute the kamdbctl with bash -x, like this "bash -x kamdbctl" (adding
>> the correct path). Then it should print all the executed commands.
>>
>> Best regards,
>>
>> Henning
>>
>>
--
Daniel-Constantin Mierla -- www.asipto.com
www.twitter.com/miconda -- www.linkedin.com/in/miconda
Kamailio World Conference -- www.kamailioworld.com
Kamailio Advanced Training, Nov 12-14, 2018, in Berlin -- www.asipto.com
More information about the sr-users
mailing list