WordPress on EOL server -

In WordPress, you can perform a site health check. To do this, go to your WordPress dashboard and select Tools. Finally, choose Site Health. It’s possible that a critical issue is displayed, indicating that your WordPress site is running on an outdated EOL (End of Life) database server. Within Strato’s basic and standard hosting packages, this could be a MariaDB or a MySQL database.

If we look online for the end of life (EOL) information for the Maria database, the following applies.

And for MySQL, the following applies.

We need to scroll through the MySQL website to find the version that Strato had selected for me.

WordPress on an EOL server for both websites under my management

The versions I had for the two websites I manage were, respectively, MariaDB 10.6 for VC Riethoven and MySQL 5.7 for the Digitale Mels website. I gave the Strato support desk a quick call, and the response was that this isn’t something they handle themselves, but the lady said she would send a step-by-step guide. Later that evening, I indeed received the guide. In the meantime, I had already upgraded the MySQL 5.7 server to MySQL 8.0. Nice.

First, you need to check which version of the database server is in use. To do this, go to the Strato portal and select Database Management. On the Database Management page, you’ll find the database type. If it says MySQL 5.7, then it has already been unsupported for years. Not nice!

Installation done by Strato via a script – WordPress on an EOL server

How is it possible that a WordPress site that has only recently gone live is set up by Strato using an outdated EOL database server? This is likely due to the script used to install the Website Apps. To install a Strato Website App, you go to Website Apps within the Strato panel. It appears that the installation randomly selects a database.

Fortunately, ChatGPT comes to the rescue. The procedure, later confirmed by Strato, is as follows.

The newly created file was downloaded from an untrusted location and has been blocked by Windows.
Use phpMyAdmin to export the current database from the current server using the Quick method.
In Database Management, choose Create Database.
- It’s recommended to note down the hostname, database name, username, and password, as you will need these later.
- Use phpMyAdmin to import the newly created SQL file.
- The newly created SQL file can be found in the following location: %userprofile%\Downloads
- The newly created file is named database.sql, where database corresponds to the name of the database as shown on the Database Management page within the Strato portal.

Update the wp-config.php file with the new values

Aanpassen doorvoeren binnen wp-config.php via FTP programma

If everything is working, don’t forget to delete the old database.

Issues that may occur during a WordPress upgrade on an EOL server

Importing the backup from the MariaDB 10.6 version into the new MySQL 8.0 database went smoothly in one go. However, there were some problems when importing the MySQL 5.7 database into the new MySQL 8.0 database. Fortunately, I was able to resolve these with the help of CoPilot. The paid version of CoPilot kept asking the following:
“Would you like me to help clean up your .sql file? You can upload it, and I’ll review and automatically remove the problematic statements.” I could have done that, but personally, I don’t consider it best practice to upload a database backup containing sensitive data to a CoPilot via a website. You also don’t learn anything that way.

First issue – #1227

This is the error:#1227 - Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

The correct answer from CoPilot. The error is usually caused by lines like.

CREATE DEFINER=`root`@`localhost` ...

Using Visual Studio Code, you can open the SQL file and search for the specific error message shown by phpMyAdmin. You will then find the following line.

CREATE ALGORITHM=UNDEFINED DEFINER=`o<<DATABASENAME>>`@`%` SQL SECURITY DEFINER VIEW `xkbe_uap_action_logs_view`  AS SELECT `a`.`automator_action_id` AS `automator_action_id`, `a`.`date_time` AS `action_date`, `a`.`completed` AS `action_completed`, `a`.`error_message` AS `error_message`, `a`.`automator_recipe_id` AS `automator_recipe_id`, `a`.`ID` AS `action_log_id`, `a`.`automator_recipe_log_id` AS `recipe_log_id`, `r`.`date_time` AS `recipe_date_time`, `r`.`completed` AS `recipe_completed`, `r`.`run_number` AS `recipe_run_number`, `pa`.`post_title` AS `action_title`, `am`.`meta_value` AS `action_sentence`, `p`.`post_title` AS `recipe_title`, `u`.`ID` AS `user_id`, `u`.`user_email` AS `user_email`, `u`.`display_name` AS `display_name` FROM (((((`xkbe_uap_action_log` `a` left join `xkbe_uap_recipe_log` `r` on((`a`.`automator_recipe_log_id` = `r`.`ID`))) left join `xkbe_posts` `p` on((`p`.`ID` = `a`.`automator_recipe_id`))) join `xkbe_posts` `pa` on((`pa`.`ID` = `a`.`automator_action_id`))) left join `xkbe_uap_action_log_meta` `am` on(((`a`.`automator_action_id` = `am`.`automator_action_id`) and (`am`.`automator_action_log_id` = `a`.`ID`) and (`am`.`user_id` = `a`.`user_id`) and (`am`.`meta_key` = 'sentence_human_readable_html')))) left join `xkbe_users` `u` on((`a`.`user_id` = `u`.`ID`))) GROUP BY `a`.`ID``ID` ;

After making this adjustment, it looked as follows.

CREATE ALGORITHM=UNDEFINED VIEW `xkbe_uap_api_logs_view`  AS SELECT `api`.`ID` AS `ID`, `api`.`date_time` AS `date`, `u`.`user_email` AS `user_email`, `u`.`display_name` AS `display_name`, `u`.`ID` AS `user_id`, `pr`.`post_title` AS `recipe_title`, `rl`.`automator_recipe_id` AS `automator_recipe_id`, `al`.`automator_recipe_log_id` AS `recipe_log_id`, `rl`.`date_time` AS `recipe_date_time`, `rl`.`completed` AS `recipe_completed`, `rl`.`run_number` AS `recipe_run_number`, `pa`.`post_title` AS `title`, `asen`.`meta_value` AS `sentence`, `al`.`automator_action_id` AS `item_id`, `al`.`completed` AS `completed`, `al`.`error_message` AS `error_message`, `api`.`type` AS `type`, `api`.`item_log_id` AS `item_log_id`, `api`.`status` AS `status`, `api`.`params` AS `params`, `api`.`price` AS `price`, `api`.`balance` AS `balance`, `api`.`notes` AS `notes`, `api`.`time_spent` AS `time_spent`, `api`.`endpoint` AS `endpoint` FROM ((((((`xkbe_uap_api_log` `api` left join `xkbe_uap_action_log` `al` on((`al`.`ID` = `api`.`item_log_id`))) left join `xkbe_uap_recipe_log` `rl` on((`al`.`automator_recipe_log_id` = `rl`.`ID`))) left join `xkbe_posts` `pr` on((`pr`.`ID` = `al`.`automator_recipe_id`))) join `xkbe_posts` `pa` on((`pa`.`ID` = `al`.`automator_action_id`))) left join `xkbe_uap_action_log_meta` `asen` on(((`asen`.`automator_action_log_id` = `al`.`ID`) and (`asen`.`meta_key` = 'sentence_human_readable_html')))) left join `xkbe_users` `u` on((`al`.`user_id` = `u`.`ID`))) WHERE (`api`.`type` = 'action') union select `api`.`ID` AS `ID`,`api`.`date_time` AS `date`,`u`.`user_email` AS `user_email`,`u`.`display_name` AS `display_name`,`u`.`ID` AS `user_id`,`pr`.`post_title` AS `recipe_title`,`rl`.`automator_recipe_id` AS `automator_recipe_id`,`tl`.`automator_recipe_log_id` AS `recipe_log_id`,`rl`.`date_time` AS `recipe_date_time`,`rl`.`completed` AS `recipe_completed`,`rl`.`run_number` AS `recipe_run_number`,`pt`.`post_title` AS `title`,`tsen`.`meta_value` AS `sentence`,`tl`.`automator_trigger_id` AS `item_id`,`tl`.`completed` AS `completed`,'' AS `error_message`,`api`.`type` AS `type`,`api`.`item_log_id` AS `item_log_id`,`api`.`status` AS `status`,`api`.`params` AS `params`,`api`.`price` AS `price`,`api`.`balance` AS `balance`,`api`.`notes` AS `notes`,`api`.`time_spent` AS `time_spent`,`api`.`endpoint` AS `endpoint` from ((((((`xkbe_uap_api_log` `api` left join `xkbe_uap_trigger_log` `tl` on((`tl`.`ID` = `api`.`item_log_id`))) left join `xkbe_uap_recipe_log` `rl` on((`tl`.`automator_recipe_log_id` = `rl`.`ID`))) left join `xkbe_posts` `pr` on((`pr`.`ID` = `tl`.`automator_recipe_id`))) join `xkbe_posts` `pt` on((`pt`.`ID` = `tl`.`automator_trigger_id`))) left join `xkbe_uap_trigger_log_meta` `tsen` on(((`tsen`.`automator_trigger_log_id` = `tl`.`ID`) and (`tsen`.`meta_key` = 'sentence_human_readable')))) left join `xkbe_users` `u` on((`tl`.`user_id` = `u`.`ID`))) where (`api`.`type` = 'trigger') ;

Second error – #1054

#1054 - Unknown column '187definer' in 'field list'

The SQL file contained the following line.

(6451, 'https://www.digitalemels.nl/controleren-spam-binnen-outlook/', 1915, 1934, 'internal', 187definer, 195, NULL, NULL, NULL, NULL, NULL),

CoPilot’s response was to define 187 definer as a text value by putting it in single quotes, or to treat it as a number. The corrected line looks as follows.

(6451, 'https://www.digitalemels.nl/controleren-spam-binnen-outlook/', 1915, 1934, 'internal', 187, 195, NULL, NULL, NULL, NULL, NULL),

Third error – #1054

#1054 - Unknown column 'a.ID`ID' in 'group statement'

According to CoPilot, this was due to a syntax error in the GROUP BY clause. I supposedly used a duplicate column name or an incorrect quote by mistake. But I didn’t write this code myself—it came from the backup.

The line in question was the following.

... left join `xkbe_users` `u` on((`a`.`user_id` = `u`.`ID`))) GROUP BY `a`.`ID``ID`

Corrected version from CoPilot.

... left join `xkbe_users` `u` ON (`a`.`user_id` = `u`.`ID`))
GROUP BY `a`.`ID`;

Fourth issue – #1064

The fourth issue was an error message based on the corrections from CoPilot. CoPilot couldn’t solve this, which is why I later switched to ChatGPT. The new error message was the following.

#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 '' at line 1

The part of the line it was about.

... ON ((`p`.`ID` = `r`.`automator_recipe_id`)))) ;

Correction from CoPilot.

ON (`p`.`ID` = `r`.`automator_recipe_id`);

And then we keep going like that for a while. Time for ChatGPT to come to the rescue. According to ChatGPT, the corrected line was the following.

FROM
    ((`xkbe_uap_recipe_log` `r`
    LEFT JOIN `xkbe_users` `u` ON (`u`.`ID` = `r`.`user_id`))
    JOIN `xkbe_posts` `p` ON (`p`.`ID` = `r`.`automator_recipe_id`));

in the end, it worked out.

Dropping tables after a failed import

At one point, the tables had been imported, but the views had not, so everything had to be reset after correcting the import file. This can easily be done via phpMyAdmin. Simply select all tables, choose With selected: Drop, and finally click Yes. Repeat this process for both Tables and Views.

If you’d like to get in touch with the author of this blog post, you can do so using the contact form on this site.