Disaster strikes
Sunday, August 4, 2024 at 01:40:15
The reason the system(s) were inaccessible was because there had been a forced mysql update to 8.0.39, which did something funky to the servers.
Once they were rebooted and I was able to get on them I found that the mysql servers wouldn’t restart at all.
Actions taken -
1) copied mysql db *.ibd files off for webcollabe5l, since they seemed to be the root of the issues.
2) stopped mysql service
3) completely removed /var/log/mysql, /var/lib/mysql, and /etc/mysql
4) reinstalled mysql server and client: apt-get install mysql-server-8.0 mysql-client-8.0 mysql-server-core-8.0 mysql-client-core-8.0.
Still had to retry a number of times to get mysql server up and running. Also had to redo the secure logins for it.
Then, tried recovering the files for webcollab, all my work notes for the last 6 or 7 years. But mysql wouldn’t recognize the files at all. There were more steps to get them reinstalled.
I had to rerun the setup for webcollab, which then created the database. This part failed repeatedly. I monkeyed with the installer, and finally got that working, with DB installed (new).
Then for each table, I had to do these steps:
1) ALTER TABLE xxxx DISCARD TABLESPACE;
2) copy the original file into /var/lib/mysql/webcollabe5l/xxx.ibd, change the owner to mysql
3) ALTER TABLE xxxx IMPORT TABLESPACE;
But this was failing miserably. The tablespace in originals didn’t match up with the newly created tables.
I finally found the issue, I needed to remove the NO_ZERO_IN_DATE,NO_ZERO_DATE from mysql.
Created file /etc/mysql/conf.d/disable_strict_mode.cnf , with these as contents
[mysqld]
sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
** I found the other stuff that IS needed using this command from mysql command line:
SELECT @@GLOBAL.sql_mode;
So, another round of reinstalling webcollab database from the setup.php, then another round of ALTER TABLE commands, and I had recovered the data. I did an export so I have it as-of 8/1 in sql statements which I can load somewhere else.
I *think* this is the table create statement for the tasks table (largest of them, and the one that kept giving me trouble), so if I ever need to do this again this might be important:
tasks CREATE TABLE `tasks` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`parent` int unsigned NOT NULL,
`task_name` varchar(255) NOT NULL,
`task_text` text,
`created` timestamp NULL DEFAULT NULL,
`edited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`task_owner` int unsigned NOT NULL DEFAULT ‘0′,
`creator` int unsigned NOT NULL,
`finished_time` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`projectid` int unsigned NOT NULL DEFAULT ‘0′,
`deadline` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`priority` tinyint NOT NULL DEFAULT ‘2′,
`task_status` varchar(20) NOT NULL DEFAULT ‘created’,
`taskgroupid` int unsigned NOT NULL,
`lastforumpost` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`usergroupid` int unsigned NOT NULL,
`globalaccess` varchar(5) NOT NULL DEFAULT ‘t’,
`groupaccess` varchar(5) NOT NULL DEFAULT ‘f’,
`lastfileupload` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`completed` tinyint NOT NULL DEFAULT ‘0′,
`completion_time` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`archive` tinyint NOT NULL DEFAULT ‘0′,
`sequence` int unsigned NOT NULL DEFAULT ‘0′,
PRIMARY KEY (`id`),
KEY `task_owner` (`task_owner`),
KEY `parent` (`parent`),
KEY `task_name` (`task_name`),
KEY `projectid` (`projectid`),
KEY `taskgroupid` (`taskgroupid`),
KEY `deadline` (`deadline`),
KEY `task_status` (`task_status`,`parent`)
) ENGINE=InnoDB
Overall, this stupid mistake took me almost 11 hours to recover from.