InterWorx Cannot Connect to MySQL/MariaDB ========================================= .. note:: If this issue occurs after replacing MariaDB with MySQL 8, please refer first to :doc:`this troubleshooting documentation `. .. contents:: How the Issue Manifests ----------------------- Symptoms could be any, or all, of the following: - Setting fields are blank in NodeWorx under System Services > MySQL Server > Overview - MySQL Version field also says "Unknown" .. image:: /images/nw-blank-mysql-settings.png :alt: blank mysql settings - PHPMyAdmin cannot be accessed in NodeWorx, under System Services > MySQL Server > PHPMyAdmin - Generic server error in NodeWorx, under System Services >MySQL Server > Manage Servers - Small text above pink error message says "getSystemDatabasesUsersLike db connect failed getSystemDatabasesLike db connect failed " .. image:: /images/nw-remote-mysql-error.png :alt: mysql remote page error - Generic server error in SiteWorx when accessing a SiteWorx account with MySQL databases - Small text above pink error message says "Get database array db connect failed" .. image:: /images/sw-database-generic-error.png :alt: siteworx page error - 1045 error when attempting to access MySQL/MariaDB at the CLI as the iworx user when using the password listed in ``~iworx/iworx.ini`` .. code-block:: [root@server ~]# mysql -u iworx -p Enter password: ERROR 1045 (28000): Access denied for user 'iworx'@'localhost' (using password: YES) [root@server ~]# - The following stacktrace in ``~iworx/var/log/iworx.log`` when accessing any of the above pages that show a generic server error in the browser: .. code-block:: 2023-11-03 13:55:11.34859 [35zli8-m2lz-shfr-WEB] [EMERG] : ERROR: /usr/local/interworx/include/View/Smarty.php(267): Cannot modify header information - headers already sent by (output started at /usr/local/interworx/include/IWorxConsole.php:116) : /siteworx/overview 2023-11-03 13:55:11.34879 [35zli8-m2lz-shfr-WEB] [EMERG] : BACKTRACE LOGGING START: line 224 of include/ErrorHandler/Abstract.php : /siteworx/overview 2023-11-03 13:55:11.34887 [35zli8-m2lz-shfr-WEB] [EMERG] : ----- DEBUG STACKTRACE BEGIN ----- : /siteworx/overview 2023-11-03 13:55:11.34901 [35zli8-m2lz-shfr-WEB] [EMERG] : /usr/local/interworx/include/IWorxDebug.php getStackTrace (174) : /siteworx/overview 2023-11-03 13:55:11.34908 [35zli8-m2lz-shfr-WEB] [EMERG] : /usr/local/interworx/include/View/Smarty.php errorHandler (267) : /siteworx/overview 2023-11-03 13:55:11.34914 [35zli8-m2lz-shfr-WEB] [EMERG] : /usr/local/interworx/include/FrontController.php render (149) : /siteworx/overview 2023-11-03 13:55:11.34921 [35zli8-m2lz-shfr-WEB] [EMERG] : /usr/local/interworx/include/FrontController.php _route (98) : /siteworx/overview 2023-11-03 13:55:11.34928 [35zli8-m2lz-shfr-WEB] [EMERG] : /usr/local/interworx/html/index.php route (37) : /siteworx/overview 2023-11-03 13:55:11.34934 [35zli8-m2lz-shfr-WEB] [EMERG] : ----- DEBUG STACKTRACE END ----- : /siteworx/overview - The following messages in ``~iworx/var/log/iworx.log``: .. code-block:: db_connect failed... sleeping 5 seconds... Potential Causes ---------------- The common causes for this issue are: - The iworx user is missing in the ``mysql.user`` table in the system MariaDB/MySQL instance - The password for the iworx user in the system MariaDB/MySQL instance's ``mysql.user`` table does not match the password specified in the internal InterWorx database - The permissions on ``/var/lib/mysql`` are incorrect, so the iworx unixuser does not have permission to access the folder How to Resolve -------------- .. note:: If the issue is not resolved after following all of the steps, below, 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 `__. To Check the MySQL iworx User ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ #. 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}'; GRANT ALL PRIVILEGES ON *.* TO 'iworx'@'localhost' WITH GRANT OPTION ; - If the iworx user does exist, run the following the update the password, replacing {password from the iworx.ini} with the corresponding information: .. code-block:: ALTER USER 'iworx'@'localhost' IDENTIFIED 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 To Check the Permissions on ``/var/lib/mysql`` ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ #. Log in to the server at the CLI as root, either via SSH or from the terminal #. Use ``ls -ld`` to check the permissions on the ``/var/lib/mysql`` directory .. code-block:: ls -ld /var/lib/mysql The correct permissions allow read and execute permissions to both group and other (755): .. code-block:: [root@server ~]# ls -ld /var/lib/mysql/ drwxr-xr-x 5 mysql mysql 4096 Nov 28 08:57 /var/lib/mysql/ [root@server ~]# The most commonly seen incorrect permissions deny access to all users other than root (700): .. code-block:: [root@server ~]# ls -ld /var/lib/mysql/ drwx------. 5 mysql mysql 4096 Nov 28 08:40 /var/lib/mysql/ [root@server ~]# #. If the permissions are anything other than what is seen in the correct permission example, above, run the following. This will update the permissions on ``/var/lib/mysql`` to the correct settings: .. code-block:: chmod 755 /var/lib/mysql