How to enable Mysql 8 Support on Drupal 7

For some of your legacy applications in enterprise, you have to support old Drupal 7 web apps. With changing technologies like MySQL upgrading to version 8 or say your linux OS upgrade or PHP upgrade, you have to always be alert of such changes when you talk about supporting legacy applications.

Today, we will quickly review what happens to your Drupal 7 website when you upgrade Mysql to version 8 & then how to fix errors and also learn about how to apply fixes with Drupal .patch files.

At first, you may feel that everything is working for your Drupal 7 website. But it’s not. May be it’s cache files not letting you see errors on the front end. But what you need to do is, login to admin panel in your Drupal instance, and go to any configuration on top. Say “Administration”. You will see the error like below:

The website encountered an unexpected error. Please try again later.1 array ( '%type' => 'PDOException', '!message' => 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system WHERE status = 1 AND bootstrap = 1 AND type = 'module' ORDER BY weight AS' at line 1: SELECT name, filename FROM {system} WHERE status = 1 AND bootstrap = 1 AND type = 'module' ORDER BY weight ASC, name ASC; Array ( ) ', '%function' => 'system_list()', '%file' => '../includes/module.inc', '%line' => 136, 'severity_level' => 3, )

Now you will ask, if there is SQL error then how come my website is perfectly working on Front-end? Well, that is because of the cache files. You have an old cache data in your Drupal database. You will have to TRUNCATE all the cache table and then you will see the same errors above on your Front-end. Well! Now your website is down.

So you decide, what to do next? You will google. And hopefully you will find this web page. 🙂 So let’s fix it now.

Drupal dev team has actively worked on supporting legacy apps and specially helping Drupal 7. They have released the fix for this issue here. Problem/Motivation: MySQL 8 has been release and includes performance enhancements. It would be nice to integrate those enhancements with Drupal 7.

The fixes provided by them are in the form of “patches“. What are Drupal Patches?

Drupal patches are updates or fixes provided by the Drupal community to address security vulnerabilities, bugs, or other issues in the Drupal. Patches are small pieces of code changes that can be applied to an existing Drupal installation to resolve specific problems. Drupal follows a patch-based development model, where the community identifies issues, develops patches to fix them, and makes them available for users to apply. Patches are typically created in the form of unified diff files (.patch files) that contain the differences between the original code and the updated code.

To apply a Drupal patch, you typically need to download the patch file, navigate to the root directory of your Drupal installation, and use the git apply or patch command to apply the patch to the affected files. The patch modifies the affected files to fix the identified issue.

So now, you know that you need to apply the patch. Which one? Usually you select the latest one, the one on top. But do read about the comments added the issue page. So let’s just say you decided to apply this patch #298. Follow the instructions below to apply this patch:

1. Download the patch file on your linux machine.
   1. sudo curl -o 2978575-298.patch https://www.drupal.org/files/issues/2020-11-24/2978575-298.patch
2. Dry Run the patch file to make sure you don’t have any errors.
   1. patch --verbose --dry-run --ignore-whitespace --fuzz 3 -p1 < 2978575-298.patch
      1. You can also simply run patch -p1 --dry-run < 2978575-298.patch  but following options helps.
      2. --ignore-whitespace : It ignores whitespace differences (indentation etc).
      3. --fuzz 3 : The “–fuzz X” option ignores up to X lines while looking for the place to install a hunk.
      4. In the context of patch files in Drupal, a “hunk” refers to a section of the patch file that contains changes to a specific set of lines within a file. When you apply a patch to modify a file, the patch file breaks down the changes into chunks or “hunks” to indicate which lines need to be added, modified, or removed.
   2. Observer the output of above command. Don’t proceed if you see any errors like Reversed (or previously applied) patch detected or 1 out of 2 hunks FAILED. Your Drupal installation is modified by someone and the patch file will not apply correctly. 
3. If you don’t see any error, then finally apply the patch file by running –
   1. patch -p1 < 2978575-298.patch
4. Restart your MySQL / Apache.

This should make your Drupal 7 site up & running smoothly with Mysql 8!

Now, it may happen that your website is not up and running. It may throw 500 error in console in a browser. All of these errors are due to your custom modules which are crying out because of Mysql 8. You will clearly find what is the error in your apache error log. Some common modules are Rules & Webforms that might give error. Below are the errors and solutions.

1. Error with Webform module
   1. In log file you may find error like PHP Fatal error: Cannot use lexical variable $value as a parameter name in /sites/all/modules/webform/components/select.inc on line 753
   2. To fix this, you apply patch here. The original issue thread is here.
2. Error with Rules module
   1. In log file you may find error like PHP Fatal error: Declaration of RulesRuleUI::form(&$form, &$form_state, $options = Array) must be compatible with RulesActionContainerUI::form(&$form, &$form_state, $options = Array, $iterator = NULL) in /sites/all/modules/rules/ui/ui.plugins.inc on line 20
   2. To fix this, you apply patch here. The original issue thread is here.

Hope this article has been useful to you. If yes, add your comments below and follow us on LinkedIn to stay connected and get more updates about enterprise software development and on technology.

Cheers!