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@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
_______________________________________________
Kamailio (SER) - Users Mailing List
[email protected]
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
