Support

Documentation

Q106 - Uppercase letters in table name prefix

[Important]Important

If your server is running on Windows or macOS your backup MAY work properly in most cases, but restoring it on Linux will be impossible or cause errors.

Akeeba Backup / Akeeba Solo detected that at least one of your databases has one or more uppercase letters in the database table name prefix.

The backup restoration even on the same server may fail if you have tables with foreign keys / relations since MySQL does not let Akeeba Backup to detect the relations between the tables correctly. You can use the restoration script's Suppress foreign key checks option to restore the backup but data integrity IS NOT guaranteed in this case.

If you have tables using the same prefix in different letter cases (e.g. FOO_bar and foo_bar) the backup will fail with a MySQL error similar to "the table FOO_bar does not exist". This will make backing up your site outright impossible.

Also note that in most cases it will be impossible to change the database prefix when restoring the site.

Finally, this will ALWAYS cause restoration problems when transferring the site from Windows / macOS to Linux or from Windows / macOS to certain macOS servers with a case-sensitive filesystem. This is a MySQL issue, documented in MySQL's own site. Please do not contact us for support if you choose to ignore this warning and your site restoration fails.

Nature of the problem

Due to the internal workings of MySQL, the table name in the database is closely linked to the names of the files that store the information to that table on your server's disk. MySQL was originally designed only with Linux in mind where files are case-sensitive, i.e. FOO, Foo and foo are three different files. However, MySQL can run on case-insensitive filesystems like those typically used by Windows and macOS. In these systems the file names FOO, Foo and foo refer to the same file. This creates an inconsistency within MySQL.

MySQL does have a workaround flag called lower_case_table_names which is set to 1 on Windows. This value poses some major challenges of its own. Even though the use of that flag allows MySQL to find the file where the data of a table is stored in it has the side-effect that the table names reported by MySQL are wrong because it turns all of their names into lowercase. Even though your table is called "FOO_bar", MySQL reports that the table "foo_bar" exists on your server. This causes several issues:

  • It makes it impossible for Akeeba Backup to detect relations between tables which may cause restoration issues.

  • It makes it impossible to change the database prefix on restoration since Akeeba Backup cannot see tables with the configure prefix at backup time. Remember that if the prefix you are using is FOO_ the prefix MySQL reports on all tables is foo_. Therefore the tables, as far as Akeeba Backup knows, do NOT start with the configured prefix and are therefore not eligible for changing their prefix on restoration.

  • If you had tables with the uppercase prefix, e.g. FOO_bar, and renamed them to lowercase OR created lowercase names with the same prefix (e.g. foo_bar) MySQL ends up with TWO table definitions (FOO_bar and foo_bar) but ONE set of files for these tables. This will cause the backup to fail with a MySQL error similar to "the table FOO_bar does not exist".

On macOS servers the same flag lower_case_table_names has a value of 2 which, superficially, seems to solve these issues. However, this is NOT the entire story. If you are using InnoDB tables (all modern PHP CMS, e-commerce applications and scripts do) this will cause the restoration to fail on Linux: MyISAM tables will be created in the correct uppercase form but InnoDB tables will be lowercase, breaking your site.

Solution

Prevention is better than any remedy. NEVER, EVER USE AN UPPERCASE TABLE NAME PREFIX. If you do, you are making it impossible to transfer your site between servers.

If you are stuck with a site that uses an upper- or mixed-case table name prefix (such as FOO_, Foo_ or FoO_) you need to rename all tables to lowercase and update your CMS' / application's configuration to reflect that change.

If you are using Joomla!, WordPress or any other PHP CMS / script supported by our backup software you MAY BE ABLE to use Akeeba Backup or Akeeba Solo to do that relatively easily. Please note that if you are on a Linux server you must skip steps 1 and 6.

[Warning]Warning

This process may fail because of MySQL's limitations. If it does fail we have no way to help you. We'd like to, but it's outright impossible because that's a MySQL (server) issue that has nothing to do with how our software works. You would have the same issues using phpMyAdmin or any other database backup / restoration tool, even MySQL's own mysqldump. If you feel nervous about this process DO NOT USE OUR SOFTWARE and seek professional assistance on renaming your tables to all lowercase and reconfiguring your site. We accept no responsibility and no liability for the results of anyone following these instructions.

  1. Set lower_case_table_names = 2 in your MySQL's my.cnf configuration file. If you don't know what that means please ask your host or, if you are on a local host, the developers of your local server environment. Restart the MySQL server.

  2. Take a backup of your site with Akeeba Backup.

  3. Delete all of your site's tables from your site's database. Alternatively, you can create a new database. VERY IMPORTANT! Do NOT leave the old tables in the database, that would cause the restoration to fail.

  4. Restore the backup. When you get to Restore Site's Database page make sure the right hand column is displayed. If not, click on the Show Advanced Options button towards the bottom of the left hand column (only applies to certain older versions of Akeeba Backup). Find the Database Table Name Prefix line box and set that prefix to all lowercase characters. This is important! Moreover check the "Suppress foreign key checks" checkbox.

  5. After the restoration is over your site's database is in all lowercase letters and the database prefix in your site's configuration is also in all lowercase.

  6. If you are on a local server, set lower_case_table_names = 1 in your MySQL's my.cnf configuration file. Restart the MySQL server.