InterWorx Can No Longer Connect to MySQL After Replacing MariaDB With MySQL 8

How the Issue Manifests

The symptoms for this issue are the same as the ones listed in this article.

The difference is that they occur immediately after replacing MariaDB with MySQL 8, vs occurring on the default or current MariaDB/MySQL instance.

Cause of the Issue

The common causes for this issue are:

  • The password plugin for the iworx user in the mysql.user table is incorrect. By default, MySQL 8 uses caching_sha2_password however, mysql_native_password is required for InterWorx correctly connect to the system MySQl instance

  • The iworx user is missing in the mysql.user table in the system MySQL instance

  • The password for the iworx user in the system MySQL instance’s mysql.user table does not match the password specified in the internal InterWorx database

How to Resolve

  1. Log in to the server at the CLI as root, either via SSH or from the terminal

  2. At the CLI run the following to locate the expected password for the iworx user:

    grep rootdsn ~iworx/iworx.ini

    • The password is the string of characters between iworx: and @unix. Example:

      [root@server ~]# grep rootdsn ~iworx/iworx.ini
rootdsn="mysqli://iworx:EXAMPLEPASSWORD@unix(/var/lib/mysql/mysql.sock)/mysql"
[root@server ~]#

  3. Make note of this password

  4. Log into the server MariaDB/MySQL instance as the mysql root user with either of the following:

    mysql

    or

    mysql -u root -p

  5. Choose the mysql database

    use mysql;

  6. Check if the iworx user exists in the mysql.user table

    select * from user where user='iworx'\G

    • If the iworx user does not exist, run the following commands to create it, replacing {password from the iworx.ini} with the corresponding information:

      CREATE USER 'iworx'@'localhost' IDENTIFIED BY '{password from the iworx.ini}';
ALTER USER 'iworx'@'localhost' IDENTIFIED WITH mysql_native_password BY '{password from the iworx.ini}';
GRANT ALL PRIVILEGES ON *.* TO 'iworx'@'localhost'  WITH GRANT OPTION ;

    • If the iworx user does exist, run the following the update the password and password plugin, replacing {password from the iworx.ini} with the corresponding information:

      ALTER USER 'iworx'@'localhost' IDENTIFIED BY '{password from the iworx.ini}';
ALTER USER 'iworx'@'localhost' IDENTIFIED WITH mysql_native_password BY '{password from the iworx.ini}';
flush privileges;

  7. Exit MariaDB/MySQL

    exit

  8. Test that the password change worked by logging in to the system MariaDB/MySQL instance as the iworx user, using the password listed in ~iworx/iworx.ini when prompted

    mysql -u iworx -p

The various symptoms mentioned in How the Issue Manifests should now be resolved

  • If the issue is not resolved after following the above steps, the password for the iworx user in the mysql.user table will need to be compared to the information in the internal InterWorx database. Due to the possibility of serious issues that could arise if any mistakes are made in the internal InterWorx database, it should only be accessed by members of the InterWorx support team. For further assistance, please submit a ticket to support.interworx.com.