Page MenuHomeSoftware Heritage
Create Task
Maniphest T3854

Fix daily dump of mysql database
Closed, MigratedEdits Locked
Actions

Description

The current daily dump of the mysql database of our WordPress website is broken, see error below:

hosting-user@softwareheritage:~$ mysqldump -u root --max_allowed_packet=128M --opt wordpress
-- MySQL dump 10.13  Distrib 8.0.18-9, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: wordpress
-- ------------------------------------------------------
-- Server version       8.0.18-9

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=\'rocksdb_bulk_load\'', 'SELECT 0') */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
mysqldump: Got error: 1709: Index column size too large. The maximum column size is 767 bytes. when using LOCK TABLES

Revisions and Commits

Restricted Diffusion Commit

Related Objects

Event Timeline

anlambert triaged this task as High priority.Jan 17 2022, 12:03 PM
anlambert created this task.
anlambert added a comment.Jan 17 2022, 2:57 PM

Using the --single-transaction=TRUE option of mysqldump helped to get the name of the table with Index column size too large.

hosting-user@softwareheritage:/srv/data$ mysqldump -u root --max_allowed_packet=128M --single-transaction=TRUE --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
mysqldump: Couldn't execute 'show create table `wp_teachpress_authors`': Index column size too large. The maximum column size is 767 bytes. (1709)

So it seems the issue comes from the database tables of the teachPress plugin used to display publications list.
I am tempted to export our publications list, drop plugin tables, reinstall the plugin and reimport publications to
see if it resolves the issue. I will try that on our testbed.

anlambert added a comment.Jan 17 2022, 3:43 PM

So it turns out that we cannot perform any operations on tables whose Index column size too large, sighs ...

15:34 $ ssh 22990@console.sd5.gpaas.net
22990@console.sd5.gpaas.net's password: 
Asking for console, please wait
Connected

Grabbing terminal
Ok

mysql> DROP TABLE wp_teachpress_authors;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> SHOW INDEX FROM wp_teachpress_authors;
+-----------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                 | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| wp_teachpress_authors |          0 | PRIMARY       |            1 | author_id   | A         |          62 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| wp_teachpress_authors |          1 | ind_sort_name |            1 | sort_name   | A         |          55 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

mysql> DROP INDEX `PRIMARY` ON wp_teachpress_authors;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

We are not the only ones to hit that issue, could be related to a bug fixed in mysql 8.0.22 (mysql on our Gandi instance is 8.0.19).

hosting-user@softwareheritage:/srv/data$ mysql --version
mysql  Ver 8.0.18-9 for debian-linux-gnu on x86_64 (Percona)
anlambert added a comment.Jan 17 2022, 4:02 PM

There is two tables with the index too large issue, wp_teachpress_authors and wp_teachpress_tags.

If we ignore them when dumping database to file, the export succeeds:

hosting-user@softwareheritage:/srv/data$ mysqldump -u root --max_allowed_packet=128M --ignore-table=wordpress.wp_teachpress_authors --ignore-table=wordpress.wp_teachpress_tags --single-transaction=TRUE --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
hosting-user@softwareheritage:/srv/data$ echo $?
0
anlambert added a comment.Jan 17 2022, 6:01 PM

It looks like it is not critical if the both tables above are not present in the WordPress database, publications list is still correctly displayed
while list of authors and tags will appear empty in the teachPress admin dashboard.

As both tables are broken since the upgrade to MySQL 8, the only workaround we can use so far to restore database dump is to tell
mysqldump to ignore these tables. I will update the anacrontab file accordingly.

anlambert added a comment.Jan 17 2022, 6:24 PM

I modified the concerned line in the /srv/data/etc/cron/anacrontab file the following way:

# 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
root <root@carnavalet> closed this task as Resolved by committing Restricted Diffusion Commit.Jan 17 2022, 6:25 PM
root <root@carnavalet> added a commit: Restricted Diffusion Commit.
anlambert mentioned this in T3862: Sanitize WordPress production database.Jan 18 2022, 6:55 PM
rdicosmo added a subscriber: rdicosmo.May 15 2022, 10:41 AM

Is this fixed now, or is the issue still open?

anlambert added a comment.May 16 2022, 10:40 AM

It's fixed see details in T3862.

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