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

.. contents::

How the Issue Manifests
-----------------------

The symptoms for this issue are the same as the ones listed in :doc:`this article </troubleshooting/mysql/connection-issue-mariadb>`.

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

#. Log in to the server at the CLI as root, either via SSH or from the terminal
#. At the CLI run the following to locate the expected password for the iworx user: 

   .. code-block::
      
      grep rootdsn ~iworx/iworx.ini

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

     .. code-block::

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

#. Make note of this password
#. Log into the server MariaDB/MySQL instance as the mysql root user with either of the following:
   
   .. code-block::

      mysql 
   
   or

   .. code-block::

      mysql -u root -p
  
#. Choose the ``mysql`` database

   .. code-block::

      use mysql;

#. Check if the iworx user exists in the ``mysql.user`` table
 
   .. code-block::

      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: 

     .. code-block::

        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: 

     .. code-block::

        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;

#. Exit MariaDB/MySQL

   .. code-block::

      exit
      
#. 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 

   .. code-block::

      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 <https://appendix.interworx.com/current/nodeworx/general/support/interworx-support-information.html#how-to-submit-a-support-ticket>`__
     to `support.interworx.com <https://support.interworx.com>`__.