Small change

Sunday, August 4, 2024 at 09:33:43

I’m too lazy to convert UTC system time to local in my head. So I’m going to set it correctly.

timedatectl set-timezone America/Los_Angeles

Additional issues from mysql updates

Sunday, August 4, 2024 at 09:03:09

There was a system hang AGAIN this morning, appears due to memory full issues. The primary thing using memory on the system is mysqld.

System was restarted via VPS console. mysqld was using 47% memory after reboot.

I dug around, found a mysqld setting that should reduce the memory usage. I put the following into /etc/mysql/my.cnf

performance_schema=0

and then performed a restart

systemctl restart mysql

After restarting the process, mysql is using about 18% memory.

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.

Top of Page