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
andDROP 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.
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;
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:
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 !
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.