MySQL 8.0 and Magento

Posted by lefred on December 12, 2019December 12, 2019

In my road trip of the Open Source projects using MySQL, after having tested WordPress, Drupal and Joomla, let’s try to install Magento using MySQL 8.0 !

In Magento’s manual, we can see that the project requires MySQL 5.6 and supports 5.7.x since versoin 2.1.2.

In my test, I will use Magento 2.3.3, the latest stable when writing this article.

The manual stipulates that we should use ROW based replication but not GTID because Magento 2 is using CREATE TEMPORARY TABLE inside transactions. In fact, this limitation doesn’t exist anymore since MySQL 8.0.13.

From MySQL 8.0.13, when binlog_format is set to ROW or MIXED, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are allowed inside a transaction, procedure, function, or trigger when GTIDs are in use. The statements are not written to the binary log and are therefore not replicated to slaves. The use of row-based replication means that the slaves remain in sync without the need to replicate temporary tables. If the removal of these statements from a transaction results in an empty transaction, the transaction is not written to the binary log. See the MySQL Manual.

So this is not a limitation anymore. And for this installation I will of course use the latest version of MySQL 8.0 available right now, MySQL 8.0.18.

Setup the database

So MySQL 8.0.18 is installed and now we need to create the database/schema and setup the credentials to install Magento:

mysql> CREATE DATABASE magento2;

mysql> CREATE USER magento2 IDENTIFIED BY 'magento2';

mysql> GRANT ALL ON magento2.* TO magento2;

We can see that we are using MySQL 8.0 and the default authenticatio plugin:

mysql> SELECT Host, User, plugin, @@version FROM mysql.user 
       WHERE user='magento2';
 +------+----------+-----------------------+-----------+
 | Host | User     | plugin                | @@version |
 +------+----------+-----------------------+-----------+
 | %    | magento2 | caching_sha2_password | 8.0.18    |
 +------+----------+-----------------------+-----------+

As in the future we will use any kind of replication, we will also enable GTID:

mysql> SET persist enforce_gtid_consistency=on;
 
mysql> SET persist_only gtid_mode=on;

mysql> RESTART;

Magento Installation

We can now start the installation wizard of Magento:

The first checks are not related to MySQL and all necessary packages are installed. FYI, this is Oracle Linux 8:

Now it’s time to insert the MySQL information:

And when we press on Next, we have our first small issue:

This is exactly what the error message is telling. The new authentication plugin is now supported. We can also see that in the php error log:

[11-Dec-2019 20:30:49 UTC] PHP Fatal error:  Uncaught PDOException: PDO::__construct(): 
The server requested authentication method unknown to the client [caching_sha2_password]
in /var/www/html/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:128

The PHP version is 7.2.11 which doesn’t support MySQL 8’s new default secure authentication plugin: caching_sha2_password.

It was supported in 7.2.8 but removed on 7.2.11, for more info check:

So now, we just need to modify the authentication method of our user:

mysql> ALTER USER magento2 IDENTIFIED 
       WITH 'mysql_native_password' BY 'magento2';

And we can press Next again in Magento’s installation wizard and fill all the next Steps until Step 6:

Let’s go an press Install Now !

The installation process seems to hang at 58%… and when we check the Console Log we can see the following:

We followed the manual, but it seems our user needs more privileges. But let’s try to not provide that and try the second option:

mysql> SET PERSIST log_bin_trust_function_creators=1;

We can refresh the page and click again on Install Now.

The process continues to 91% and then another strange error:

I checked the status of the table and nothing seems wrong, so I refreshed the table and restart the process that resumes and goes to the end successfully:

And of course, we can now access the Site we just deployed:

Summary

In summary, Magento works fine with MySQL 8.0, you have very few changes to perform, they are related to the user Magento that connect to MySQL and one global variable:

mysql> CREATE USER magento2 IDENTIFIED 
       WITH 'mysql_native_password' BY 'magento2';
mysql> GRANT ALL ON magento2.* TO magento2;
mysql> SET PERSIST log_bin_trust_function_creators=1;

And of course, there is no more reason to not use GTIDs !

Facebook

Twitter

Posted in 8.0, General, Magento, MySQL, MySQL 8.0 ·

One thought on “MySQL 8.0 and Magento”

Rick Schippers says:

April 22, 2020 at 12:25

There were issues with the partial indexing as well. I believe these are not fixed yet in 2.3.4. So this can cause some issues. Officially magento will support MySQL 8 from version 2.4.

Switching the indexes to realtime will work around this, but probably not something you want on production.

Reply