Page MenuHomeSoftware Heritage
Create Task
Maniphest T3862

Sanitize WordPress production database
Closed, MigratedEdits Locked
Actions

Description

I was too optimistic that the cause of the mysql dump issue detailed in T3854 (two broken tables that cannot be dropped)
did not have critical side effects, turns out the WordPress customizer fails to load now due to these errors (requests on
the broken tables). This means page layouts cannot be edited anymore, so pretty critical.

[18-Jan-2022 10:50:03 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 12:19:50 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 12:52:20 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 14:25:05 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 15:04:04 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 15:33:23 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 16:29:46 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:33:34 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:35:05 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:38:58 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:39:08 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:41:53 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:42:03 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:51:16 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:51:30 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by do_action('toplevel_page_publications'), WP_Hook->do_action, WP_Hook->apply_filters, tp_show_publications_page, TP_Publications_Page::get_tab, TP_Tags::get_tags
[18-Jan-2022 16:51:30 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by do_action('toplevel_page_publications'), WP_Hook->do_action, WP_Hook->apply_filters, tp_show_publications_page, TP_Publications_Page::get_tab, TP_Publications_Page::get_tag_filter, TP_Tags::get_tags
[18-Jan-2022 16:51:45 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id WHERE  ( r.pub_id = '25' )  ORDER BY t.name ASC  made by do_action('publications_page_teachpress/addpublications'), WP_Hook->do_action, WP_Hook->apply_filters, tp_add_publication_page, TP_Publication_Page::get_tags_box, TP_Publication_Page::get_current_tags, TP_Tags::get_tags
[18-Jan-2022 16:51:45 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by do_action('publications_page_teachpress/addpublications'), WP_Hook->do_action, WP_Hook->apply_filters, tp_add_publication_page, TP_Publication_Page::get_tags_box, TP_Tags::get_tags
[18-Jan-2022 16:51:45 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT a.name, r.author_id, r.pub_id, r.con_id, r.is_author, r.is_editor FROM wp_teachpress_rel_pub_auth r INNER JOIN wp_teachpress_authors a ON a.author_id = r.author_id GROUP BY a.name ORDER BY a.sort_name ASC, a.name ASC  made by do_action('publications_page_teachpress/addpublications'), WP_Hook->do_action, WP_Hook->apply_filters, tp_add_publication_page, TP_Publication_Page::print_scripts, TP_Authors::get_authors
[18-Jan-2022 16:55:11 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:56:11 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:56:44 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:57:52 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:58:40 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 16:58:50 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 17:00:15 UTC] WordPress database error Table 'wordpress_dev.wp_teachpress_tags' doesn't exist for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags
[18-Jan-2022 17:15:26 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 17:15:29 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 17:20:22 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id ORDER BY t.name ASC  made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/layerswp/builder.php'), dynamic_sidebar, WP_Widget->display_callback, WP_Widget_Text->widget, apply_filters('widget_text_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, tp_list_shortcode, tp_publist_shortcode, TP_Tags::get_tags
[18-Jan-2022 17:25:32 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query SELECT DISTINCT t.name, r.tag_id, r.pub_id, r.con_id FROM wp_teachpress_relation r INNER JOIN wp_teachpress_tags t ON t.tag_id = r.tag_id GROUP BY t.name ORDER BY t.name ASC  made by require('wp-admin/edit-form-advanced.php'), require_once('wp-admin/admin-header.php'), do_action('admin_head'), WP_Hook->do_action, WP_Hook->apply_filters, tp_write_data_for_tinymce, TP_Tags::get_tags

The broken tables comes from the teachPress plugin used to display publication list.
They were created using mysql 5 at the time the plugin was installed with a compact row format which is the cause of the error.
To fix the issue, we need to recreate those tables with mysql 8 and fill them back with a bibtex export.

As those broken tables cannot be dropped, the only way to sanitize the database is to:

  1. create a new one
  2. fill it with a dump of the old one without the teachPress tables
  3. switch database name in WordPress configuration
  4. recreate teachPress tables from the WordPress settings
  5. reimport publications from a previous export in BibTex format

I will test that procedure on our testbed first.

Related Objects

Event Timeline

anlambert triaged this task as High priority.Jan 18 2022, 6:55 PM
anlambert created this task.
anlambert lowered the priority of this task from High to Normal.Jan 18 2022, 8:36 PM

So turns out the customizer issue was due to the recent upgrade of the wp-extra-file-types plugin.

I reverted that upgrade to unblock the customizer use.

Critical issue is fixed so I am lowering the task priority to Normal.

Nevertheless, database sanitation must still be performed. I have successfully tested the procedure above on our testbed.
I will post the detailed steps here and switch production database tomorrow.

anlambert added a comment.EditedJan 19 2022, 12:58 PM

This is what I have done to sanitize the production database and plug it into our testbed for testing.

  1. Create a new wordpress_prod database.
hosting-user@softwareheritage:~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14390
Server version: 8.0.18-9 Percona

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database wordpress_prod;
Query OK, 1 row affected (0.23 sec)
  1. Grant all privileges on the database for the wordpress_dev user.
hosting-user@softwareheritage:~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14523
Server version: 8.0.18-9 Percona

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL PRIVILEGES ON wordpress_prod.* TO 'wordpress_dev'@'localhost';
Query OK, 0 rows affected (0.03 sec)
hosting-user@softwareheritage:~$ mysql -u wordpress_dev -p wordpress_prod
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14530
Server version: 8.0.18-9 Percona

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Bye
  1. Dump current production database without the teachPress tables
hosting-user@softwareheritage:~$ mysqldump -u root --max_allowed_packet=128M --ignore-table=wordpress.wp_teachpress_artefacts --ignore-table=wordpress.wp_teachpress_assessments --ignore-table=wordpress.wp_teachpress_authors --ignore-table=wordpress.wp_teachpress_courses --ignore-table=wordpress.wp_teachpress_course_capabilities --ignore-table=wordpress.wp_teachpress_course_documents --ignore-table=wordpress.wp_teachpress_course_meta --ignore-table=wordpress.wp_teachpress_pub --ignore-table=wordpress.wp_teachpress_pub_capabilities --ignore-table=wordpress.wp_teachpress_pub_documents --ignore-table=wordpress.wp_teachpress_pub_imports --ignore-table=wordpress.wp_teachpress_pub_meta --ignore-table=wordpress.wp_teachpress_relation --ignore-table=wordpress.wp_teachpress_rel_pub_auth --ignore-table=wordpress.wp_teachpress_settings --ignore-table=wordpress.wp_teachpress_signup --ignore-table=wordpress.wp_teachpress_stud --ignore-table=wordpress.wp_teachpress_stud_meta --ignore-table=wordpress.wp_teachpress_tags --ignore-table=wordpress.wp_teachpress_user --opt wordpress | gzip -c > dump.wordpress.no.tp.sql.gz
  1. Fill the new wordpress_prod database with dumped data
hosting-user@softwareheritage:~$ zcat dump.wordpress.no.tp.sql.gz | mysql -u root wordpress_prod
  1. Change the testbed database in WordPress configuration
(swh) ✔ ~/swh/www.softwareheritage.org-gandi [master ↑·2|…1⚑ 2] 
12:27 $ git diff
diff --git a/htdocs/config-www-dev.softwareheritage.org.php b/htdocs/config-www-dev.softwareheritage.org.php
index c7a9e867..68db13da 100644
--- a/htdocs/config-www-dev.softwareheritage.org.php
+++ b/htdocs/config-www-dev.softwareheritage.org.php
@@ -7,7 +7,7 @@ define('WP_HOME','https://www-dev.softwareheritage.org');
 define('WP_SITEURL','https://www-dev.softwareheritage.org');
 
 /** The name of the database for WordPress */
-define('DB_NAME', 'wordpress_dev');
+define('DB_NAME', 'wordpress_prod');
 
 /** MySQL database username */
 define('DB_USER', 'wordpress_dev');
  1. Push the change above on the git server of our gandi simple hosting instance and deploy the testbed website
  1. Recreate teachPress database from the admin dashboard and import SWH publications from that bibtex export
    SWH publications28 KBDownload

Everything went fine and there is no more Index column size too large. The maximum column size is 767 bytes. errors.

What remains to do to switch production database:

  1. Do a new dump of the old production database as some changes may have happened since yersteday
  2. Feed wordpress_prod database again with that dump
  3. Grant all privileges on the wordpress_prod database for the wordpress user.
  4. Remove all privileges on the wordpress_prod database for the wordpress_dev user.
  5. Switch production database in WordPress configuration
  6. Replace reference of wordpress database by wordpress_prod one in anacrontab file
  7. Push to git and deploy
anlambert closed this task as Resolved.Jan 19 2022, 6:46 PM
anlambert claimed this task.

Time to switch production database to a new sanitized one.

First let's make a fresh dump of the current production db.

hosting-user@softwareheritage:~$ mysqldump -u root --max_allowed_packet=128M --ignore-table=wordpress.wp_teachpress_artefacts --ignore-table=wordpress.wp_teachpress_assessments --ignore-table=wordpress.wp_teachpress_authors --ignore-table=wordpress.wp_teachpress_courses --ignore-table=wordpress.wp_teachpress_course_capabilities --ignore-table=wordpress.wp_teachpress_course_documents --ignore-table=wordpress.wp_teachpress_course_meta --ignore-table=wordpress.wp_teachpress_pub --ignore-table=wordpress.wp_teachpress_pub_capabilities --ignore-table=wordpress.wp_teachpress_pub_documents --ignore-table=wordpress.wp_teachpress_pub_imports --ignore-table=wordpress.wp_teachpress_pub_meta --ignore-table=wordpress.wp_teachpress_relation --ignore-table=wordpress.wp_teachpress_rel_pub_auth --ignore-table=wordpress.wp_teachpress_settings --ignore-table=wordpress.wp_teachpress_signup --ignore-table=wordpress.wp_teachpress_stud --ignore-table=wordpress.wp_teachpress_stud_meta --ignore-table=wordpress.wp_teachpress_tags --ignore-table=wordpress.wp_teachpress_user --opt wordpress | gzip -c > dump.wordpress.no.tp.sql.gz

Then let's update previously created wordpress_prod db with the fresh dump.

hosting-user@softwareheritage:~$ zcat dump.wordpress.no.tp.sql.gz | mysql -u root wordpress_prod

Let's grant all privileges on that db for the wordpress user.
Let's revoke all privileges on that db for the wordpress_dev user.

osting-user@softwareheritage:~$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34731
Server version: 8.0.18-9 Percona

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL PRIVILEGES ON wordpress_prod.* TO 'wordpress'@'localhost';
Query OK, 0 rows affected (0.04 sec)

mysql> REVOKE ALL PRIVILEGES ON wordpress_prod.* FROM 'wordpress_dev'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Switch production database in WordPress configuration
Replace reference of wordpress database by wordpress_prod one in anacrontab file

(swh) ✔ ~/swh/www.softwareheritage.org-gandi [master ↑·2|…1⚑ 2] 
18:21 $ git diff
diff --git a/etc/anacrontab b/etc/anacrontab
index 2d66d27c..dc1e323b 100644
--- a/etc/anacrontab
+++ b/etc/anacrontab
@@ -7,7 +7,7 @@
 # 1@daily  0 backupdb  zcat /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz | mysql -u root wordpress_dev ; mysqldump -u root --max_allowed_packet=128M --opt wordpress | gzip -c > /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new && mv /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz
 
 # backup to disk current production DB
-1@daily  0 backupdb  mysqldump -u root --max_allowed_packet=128M --ignore-table=wordpress.wp_teachpress_authors --ignore-table=wordpress.wp_teachpress_tags --opt wordpress | gzip -c > /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new && mv /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz
+1@daily  0 backupdb  mysqldump -u root --max_allowed_packet=128M --opt wordpress_prod | gzip -c > /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new && mv /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz
 
 # download local cache of archive object counters, for homepage and /archive web pages
 4@hourly 0 statcache wget -qO /srv/data/web/vhosts/www.softwareheritage.org/htdocs/stat-counters.json.new https://archive.softwareheritage.org/api/1/stat/counters/ && mv /srv/data/web/vhosts/www.softwareheritage.org/htdocs/stat-counters.json.new /srv/data/web/vhosts/www.softwareheritage.org/htdocs/stat-counters.json
diff --git a/htdocs/config-www.softwareheritage.org.php b/htdocs/config-www.softwareheritage.org.php
index 1d81d274..1b7c1fbe 100644
--- a/htdocs/config-www.softwareheritage.org.php
+++ b/htdocs/config-www.softwareheritage.org.php
@@ -1,7 +1,7 @@
 <?php
 
 /** The name of the database for WordPress */
-define('DB_NAME', 'wordpress');
+define('DB_NAME', 'wordpress_prod');

After pushing those changes to the git server of our Gandi instance and triggering a new deployment of the website,
the switch to the new sanitized database was correctly handled, no particular issues detected.

Let's not forget to copy the updated anacrontab file to the adequate directory.

hosting-user@softwareheritage:~$ cd /srv/data/web/vhosts/www.softwareheritage.org/
hosting-user@softwareheritage:/srv/data/web/vhosts/www.softwareheritage.org$ cp etc/anacrontab /srv/data/etc/cron/
hosting-user@softwareheritage:/srv/data/web/vhosts/www.softwareheritage.org$ cat /srv/data/etc/cron/anacrontab 
# num@period  delay(unused) name script...

# purge temp files older than 7 days
1@daily  0 purgetmp  find /srv/data/tmp -type f -mtime +7 -delete > /dev/null

# restore old production DB to testbed DB ; then backup to disk current production DB
# 1@daily  0 backupdb  zcat /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz | mysql -u root wordpress_dev ; mysqldump -u root --max_allowed_packet=128M --opt wordpress | gzip -c > /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new && mv /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz

# backup to disk current production DB
1@daily  0 backupdb  mysqldump -u root --max_allowed_packet=128M --opt wordpress_prod | gzip -c > /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new && mv /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz.new /srv/data/web/vhosts/www.softwareheritage.org/dump.wordpress.sql.gz
...
anlambert mentioned this in T3854: Fix daily dump of mysql database.May 16 2022, 10:40 AM
gitlab-migration changed the task status from Resolved to Migrated.Jan 8 2023, 4:36 PM
gitlab-migration claimed this task.
gitlab-migration added a subscriber: gitlab-migration.

This task has been migrated to GitLab.

About · Developer information · Legal