Re: [SR-Users] "kamdbctl create" failing with MySQL 8

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: 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