![[Warning]](/media/com_docimport/admonition/warning.png) | Warning |
|---|---|
|
These features are only available on sites using the MySQL database server. |
Do note that these tools can be both found in Admin Tools' Control Panel page since Admin Tools 1.0 Stable. Previous versions used to have them in a separate page.
The database is the most important part of our websites. It holds all the data and most configuration options, i.e. everything which makes our site what it is. However, since data is being written to and deleted from the database, the database table are becoming slow or even corrupted. It's the same thing as what happens with hard drives. One table notorious for becoming very fragmented too fast is the sessions table. In fact, every time a guest user visits your site or a user logs in and logs out from your site this table starts becoming bloated until, one day, nobody can log in to your site, not even yourself. This is a very common issue, especially on high-traffic sites.
On a hard drive you know that you can always defragment it and run chkdisk or fsck (depending on your Operating System). For databases you have to go through a tedious process using a database administration tool, such as phpMyAdmin, to repair and optimize each and every table. Admin Tool's Database Tools are here to automate this tedious process for you!
There are three tools available:
-
will run the repair and optimisation process on all of your site's tables. If the process hangs for a long time after the first time you use it, run it again. The usual problem is that the Joomla! sessions table is so bloated that PHP times out waiting for your database server to optimise this table.
-
will purge (completely empty) and optimize only the sessions table. Doing so will log everybody out of the site, including yourself. Use this option sparingly and only when you observe severe problem when users are trying to log into the site.
-
Change Database Collation will let you change the character encoding for your database data. See the Information on Database Collation below for details.
A cut-down version of the optimisation process, addressing only the sessions table, can be scheduled to run on a timely basis by using the parameters of the "System - Admin Tools" plugin of the Professional release.
Information on Database Collation
What is the collation and why you need to change it?
Joomla! stores all your data, including your articles, tags and third party extensions' data, in the database. The database needs to know what the raw binary data represents in order to do basic operations such as searching for information, ordering information and so on. This is called the collation of the database.
Joomla! is optimally designed to use the UTF-8 character set with a "generic" (multilingual) collation. In short, the UTF-8 character set allows you to store the characters of most written languages on the planet. Technically, UTF-8 is an encoding format for Unicode, the universal character representation format supported by all modern software and operating systems. UTF-8 can store each character in one to three bytes. For instance English characters are typically stored using one byte, Greek and Cyrillic characters using two bytes each, Simplified Chinese and Japanese Hiragana and Katakana characters with three bytes. Most database servers are configured to use UTF-8 by default and Joomla! will work trouble-free on them.
However, some database servers are configured to use a different collation, latin1_swedish_ci. This may sound strange, but it's the default setting of MySQL. Basically, some server administrators were too lazy to change a single line in a single configuration file before putting the server on-line. This could lead to problems running Joomla!, e.g. accented or non-latin characters (Cyrillic, Greek, Hindu, ...) could end up as garbled text or question marks. The solution to this problem is using the Change Database Collation feature of Admin Tools to change the collation of your database to UTF-8.
Different UTF-8 collations, multibyte characters (Emoji, Chinese, ...) and security
The default UTF-8 collation used by Joomla! can only store characters consisting of up to three bytes each. This means that some characters cannot be represented at all. The most frequently used 4-byte characters are Emoji (the faces and symbols you get on your iPhone!), and some of the less often used Traditional Chinese and Japanese Kanji characters.
At the time of this writing (May 2016) Joomla! only supports 4-byte characters in Joomla! 3.5.0 and later. Earlier versions do not. If you try to enter a 4-byte character, e.g. an Emoji, all text following this character will be silently lost. This is both a nuisance and a security issue. Since the text following a 4-byte character is lost you can end up with corrupt HTML. This corruption takes place after the data has been sanitised by Joomla!'s code, essentially tricking Joomla! into inserting invalid and possibly dangerous data into the database. While no exploit is known at the time of this writing this issue is very similar to a WordPress security flaw affecting all WordPress releases up to and including 4.2.0. Therefore we consider the lack of 4-byte character support as a POTENTIAL security issue. Please note that this is a POTENTIAL and UNPROVEN security issue. There may actually not be a security issue at all. But since the possibility exists we provide you with a way to work around it.
Using the Change Database Collation change your database's collation to UTF-8 Multibyte. This uses the utf8mb4_general_ci collation which tells MySQL to add support for 4-byte characters. On top of that, the System - Admin Tools plugin tells Joomla!'s database driver to add support for 4-byte characters.
| Warning |
|---|---|
|
If you enable the UTF-8 Multibyte collation you MUST have the System - Admin Tools plugin enabled. Otherwise the 4-byte characters (e.g. Emoji) will be saved and displayed as a series of four question marks, like this: ???? This will no longer be necessary if Joomla! itself adds support for multibyte characters anytime in the future. |
Things to keep in mind when changing the collation
Always take a backup BEFORE changing the collation. This process actively modifies all your database content. Such an operation has an inherent risk, in that MySQL may corrupt your data in the process. While rare, it's not unheard of. A fresh, locally kept, tested backup is the only thing standing between you and a catastrophic failure.
Though not strictly necessary, we've found that on some servers you MAY have to use the Repair & Optimise feature in Admin Tools right after changing the database collation. This is especially true if you have tables with several thousands of rows or more.
Depending on your database server privileges and the nature of the extensions you have installed you MAY have to repeat the change collation process after installing new extensions or updating existing ones. How can you know? It's simple. If you get funny looking characters, truncated text or question marks instead of text you know you need to redo the Change Database Collation process.
Also note that changing the collation MAY result in a blank page if you have too many and / or too big tables. This is simply PHP timing out while MySQL processes large sets of data. Don't panic. Repeating the process a few more times will eventually let it complete successfully.