Collation is a big word that we don't use everyday. It helps to remember that it basically means 'sorting'. Throwback: when printing documents in the office was a big deal (before the Internet), you'd have to pay extra to get huge sorting trays attached to the printer so that you could print '20 copies of this 5-page document' and each set of 5 pages would be printed into it's own tray. Now collating copiers are the norm and they do it without the massive tray finishers.
=== OS-dependent defaults ===MariaDB is different on RedHat distros and in Ubuntu vs. Debian<ref>https://mariadb.com/kb/en/library/differences-in-mariadb-in-debian-and-ubuntu/</ref>(and CentOS / Rocky Linux seem more like Ubuntu than Debian). Debian does the 'right thing' and sets defaults to be UTF-8 compatible, whereas others are still lagging with defaults of <code>latin1</code> for character set (and <code>latin1_swedish_ci</code> collation)!!
Debian also enables the <code>unix_socket</code> authentication plugin by default for MariaDB <= 10.4.This allows passwordless login. See the reference for more details. === Best practice ===
{{Ambox|text=Get into the habit of specifying CHARACTER SET and COLLATION on all connections and CREATE TABLEs. MySQL and MariaDB are gradually changing from latin1_swedish_ci to utf8mb4_0900_ai_ci.
By explicitly specifying the charset and collation, you maintain control and consistency, even if it is an out-dated pair.}}
=== Changing Character Sets and Collations ===
Character sets and collations can be set from the server level right down to the column level, as well as for client-server communication. For example, the <code>[https://mariadb.com/kb/en/server-system-variables/#collation_connection collation_connection]</code> '''server system variable'''. There is another KB page for [https://mariadb.com/kb/en/setting-character-sets-and-collations/ Setting Character Sets and Collations]. Keep in mind that there a many possible collations per character set, but only one default per character set. So, if you specify a character set, but are silent on the collation, you will receive the default. Character sets and collations always cascade down, so a column without a specified collation will look for the table default, the table for the database, and the database for the server. It's therefore possible to have extremely fine-grained control over all the character sets and collations used in your data. Collation names always start with the character set they correspond to (e.g. latin1 charset and latin1_bin collation).
<br />
=== Changing defaults ===
Depending on what version of MySQL or MariaDB you are using, it can be difficult at best to change the default collation '''for a given character set'''. <ref>https://dba.stackexchange.com/questions/239975/change-default-collation-for-character-set-utf8mb4-to-utf8mb4-unicode-ci</ref> The default relationship between character set and the collation for it will affect you when you use 'CREATE' or 'ALTER' with a 'CHARACTER SET' clause and the collation clause is left out. The admonition is to always specify both the character set and the collation in Connections as well as CREATE statements.
#https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/
== MediaWiki ==
As of 2024-04-23 MediaWiki only requires MariaDB 10.3.x or MySQL 5.7.x for installation<ref>https://www.mediawiki.org/wiki/Manual:Installation_requirements#Database_server</ref> However, these are out of support. MariaDB is available in versions ranging from [https://mariadb.com/kb/en/changes-and-improvements-in-mariadb-10-4/ 10.4] - 10.11 and 11.0 - 11.5 The latest release in the 10.4 series is 10.4.33, released on 2024-02-07
Practically, this means that even if the database doesn't show errors, it could become vulnerable to attack or corruption at any time. And, tools like MySQLTuner, VSCode, PHPMyAdmin, etc. may not support the old version any longer.
Starting in REL1_35, MediaWiki uses the [https://www.doctrine-project.org/projects/dbal.html Doctrine Database Abstraction Layer] (DBAL)
<br />
==Footnotes==
