Update mariadb GLOBAL defaults for frdbs in preparation for utf8mb4 conversion
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Dwisehaupt
	Feb 10 2021, 10:24 PM

Description

From https://phabricator.wikimedia.org/T273704#6817387 we will need to update some GLOBAL settings. This will need to be done for the utf8mb4 conversion (T270218).

Specifically we will need:

# needed
SET GLOBAL innodb_default_row_format='dynamic';
# needed
SET GLOBAL innodb_file_format='Barracuda';
# already set
SET GLOBAL innodb_file_per_table = 1;
# needed
SET GLOBAL innodb_large_prefix = ON;
# needed
SET GLOBAL innodb_strict_mode = ON;

We need to template these into our my.cnf.erb and apply them to the live systems.

Related Objects
Search...

Status	Assigned	Task
Resolved	Dwisehaupt	T268056 FY2020-21 Q3 maintenance window
Resolved	Dwisehaupt	T270218 Fix our database to support emojis - convert tables to utf8mb4
Resolved	Dwisehaupt	T274438 Update mariadb GLOBAL defaults for frdbs in preparation for utf8mb4 conversion

Event Timeline

Dwisehaupt created this task.Feb 10 2021, 10:24 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 10 2021, 10:24 PM

Dwisehaupt added a parent task: T270218: Fix our database to support emojis - convert tables to utf8mb4.Feb 10 2021, 10:25 PM

Dwisehaupt mentioned this in T273704: Deal with utf8 issues for new install.

https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix

Quite a history there. we are on 10.1 on the primary but 10.4 everywhere else. So this one is probably only an issue until we do an origin swap and then we can remove it.

Looking at the three additional variables, it looks like we may be able to avoid this when we upgrade the current origin server. All of the servers running buster and mariadb 10.4 would have the default values of what we are looking for. Will discuss whether it is worth templating these up or just focusing on the conversion.

Dwisehaupt moved this task from Triage to Up Next on the fundraising-tech-ops board.Feb 17 2021, 1:00 AM

Decided to go ahead and roll this on frdb1002 to make it consistent with the other dbs until the upgrade is complete. The following commands were needed:

set global innodb_large_prefix=on;
set global innodb_default_row_format='dynamic';

Codified in puppet so that the settings will last over restarts.

344efda0 Update mariadb global variables to match 10.4 defaults

We should also enable innodb_strict_mode since we are shifting to barracuda as the default. See T150949 for a little more reference.

Dwisehaupt updated the task description. (Show Details)Feb 19 2021, 12:42 AM

innodb_strict_mode=on has been set for default my.cnf file. It is in place everywhere we are running mariadb 10.4. frdb1002 (still running 10.1) is not currently accepting the adjustment to the global variable.

Closing this out as we will catch frdb1002 into this mix when we have our maintenance window.

hashar mentioned this in T307178: CiviCRM CI jobs fails when migrating from Stretch to Bullseye.May 11 2022, 1:30 PM

Update mariadb GLOBAL defaults for frdbs in preparation for utf8mb4 conversionClosed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

Update mariadb GLOBAL defaults for frdbs in preparation for utf8mb4 conversion
Closed, ResolvedPublic
Actions

Related Objects
Search...