Changes

Jump to navigation Jump to search
no edit summary
</source>
==MariaDB Differences between Ubuntu Fixing CHARSET and DebianCOLLATION==Note: When dealing with MariaDB, they have this [https://mariadb.com/kb/en/library/differencescharacter-inset-mariadband-incollation-debian-and-ubuntuoverview/ KB Overview]. For MySQL, there is a dedicated [https://forums.mysql.com/list.php?103 forum for Character Sets, Collation, Unicode]
If 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 Ubuntu vs. Debian<ref>https://mariadb.com/kb/en/library/differences-in-mariadb-in-debian-and-ubuntu/</ref>. 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!! === 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.  "900" is probably not the last Unicode standard. 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, youwill 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're 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).  If your collation and character sets are all <code>latin1_swedish</code>, but you want them to be <code>UTF-8</code>, then you can set it in the configuration file, and restart the database.
<source lang="mysql">
show GLOBAL VARIABLES LIKE 'character_set_%';
*<code>service mariadb restart</code> for Debian/Ubuntu;
*<code>/bin/systemctl restart mariadb.service</code> for Fedora/Centos/RHEL
 
<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.
 
Not to confuse things, but the default server-wide character set changed in MariaDB >= 10.6. The <code>character_set_server</code> changed from <code>utf8</code> to <code>utf8mb3</code> <ref>https://mariadb.com/kb/en/server-system-variables/#character_set_server</ref>
 
 
Show the default collation '''for a given character set''':
 
<code>SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8mb4' ;</code>
 
On my CentOS server, the value is <code>utf8mb4_general_ci</code> and the same for Rocky Linux 8.9
 
For <code>CHARACTER_SET_NAME = 'binary'</code>, the collation is <code>binary</code> as no other collation is available, nor would make sense.
==Working at the Console==
#https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/
#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.
==Footnotes==

Navigation menu