Support

Documentation

Q106 - Uppercase letters in table name prefix
Prev Chapter 1. Warnings Next

Q106 - Uppercase letters in table name prefix

[Important]Important

If your server is running on Windows or macOS using a case-insensitive filesystem (the default for these Operating Systems) your backup should work properly in most cases, but restoring it on a Linux server is likely to fail or cause errors due to a documented MySQL issue.

Please note that the root cause of the restoration issues you may experience is an issue in MySQL / MariaDB / Percona database server itself, not something that can be worked around in our software. Addressing this issue requires correct configuration of the database server (correct lower_case_table_names setting on the Windows / macOS database server, depending on the setting on the corresponding Linux server) BEFORE creating tables with uppercase letters in them. Again, this is documented by MySQL itself!

If your server is running on Linux OR a case-sensitive filesystem (e.g. HFS+ Case-Sensitive on macOS) OR you have properly configured your database server THEN and only then can you safely ignore this message.

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) then and only then the backup WILL DEFINITELY fail with a MySQL error similar to "the table FOO_bar does not exist". This will make backing up your site outright impossible.

Moreover, please do note that in most cases it will be impossible to change the database prefix when restoring the site to a Linux server.

Finally, this will 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, especially if using an older MySQL version. 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

The problem is the way that MySQL handles case-sensitivity of tables and other database elements on case insensitive filesystems (default on Windows and macOS)

Please read MySQL's documentation page explaining the issue of case sensitivity. After all, the root cause of this issue is a documented MySQL behavior that cannot be worked around on our end. It requires configuring the database server BEFORE creating tables with uppercase letters in them, exactly as the MySQL documentation page very clearly explains.

If the site you are backing up from is on a case insensitive filesystem (default on Windows and macOS) and lower_case_table_names is set to 1 (default on Windows) MySQL will report all database tables with lowercase name. Let's say your site's database prefix is Foo_. A table belonging to your site called Foo_bar will be reported as foo_bar. However, Akeeba Backup CORRECTLY considers foo_bar to not begin with the configured site's prefix (Foo_) because database table names are case-sensitive.

This has some important repercussions when taking a backup:

  • Some automated database filters will not work because they expect to only apply to core tables (tables which begin with the site's configured database table prefix).

  • Your manual database filters may not work. In this case you will also be seeing the table reported in the Database Table Filters page as foo_bar instead of #__bar.

  • 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 will only rename tables which were considered to be core tables (tables which begin with the site's configured database table prefix) during backup time

  • 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". This is a documented MySQL issue (see the link given above).

If the site you are backing up from is on a case insensitive filesystem (default on Windows and macOS) and lower_case_table_names is set to 2 (default on macOS) most of these issues are solved as long as this setting had taken effect BEFORE the tables with uppercase letters were created, i.e. before your site was installed. If you try to change it after your site has been installed then at best your backup will face restoration issues, at worst you will be unable to access your site at all.

Solution

Prevention is better than any remedy. NEVER, EVER USE AN UPPERCASE TABLE NAME PREFIX. If you do, you are making it really hard to reliably transfer your site between servers unless you have taken great care configuring the database server on Windows / macOS.

If you are stuck with a site that uses an upper- or mixed-case table name prefix you need to convert it to a lowercase prefix. This is usually possible with Akeeba Backup and Akeeba Solo. How to do that depends on the MySQL database server configuration and the operating system you are on.

[Important]Important

There are certain cases where the process described below will fail. Again, this is a problem with MySQL which is documented in MySQL's own site.

If this happens please do not ask us for support. Instead, undo any my.cnf configuration changes you made, restart the database server, delete your MySQL database, create a new and restore the backup again. This restoration WILL work, however your site cannot be transferred to another server just yet. You will have to apply the complicated, manual fix described near the bottom of the page linked in the paragraph above.

Kindly note that we cannot offer any support beyond what is discussed in this documented about this issue which is ultimately a misconfiguration of your site (something under your sole control) in the context of how your database server works (something objectively not in our or your control).

If you are on Linux OR macOS with a case-sensitive filesystem

  1. Take a backup of your site with Akeeba Backup or Akeeba Solo.

  2. Delete all of your site's tables from your site's database. Alternatively, you can create a new database.

    [Important]Important

    Do NOT leave the old tables in the database.

  3. Restore the backup. When you get to Restore Site's Database page find the Database Table Name Prefix and set that prefix to all lowercase characters. For example, if it was FbZt_ change it to fbzt_.

If you are on Windows / macOS with a case-insensitive filesystem AND lower_case_table_names = 1 in your MySQL's my.cnf configuration file

This is the default situation on Windows.

  1. Edit your site's configuration and set the database prefix to all lowercase letters. For example, if it was FbZt_ change it to fbzt_.

  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.

    [Important]Important

    Do NOT leave the old tables in the database.

  4. 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.

  5. Restore the backup. When you get to Restore Site's Database page find the Database Table Name Prefix and make sure that prefix is in all lowercase characters.

If you are on Windows / macOS with a case-insensitive filesystem AND lower_case_table_names = 2 in your MySQL's my.cnf configuration file

This is the default situation on macOS.

  1. Take a backup of your site with Akeeba Backup or Akeeba Solo.

  2. Delete all of your site's tables from your site's database. Alternatively, you can create a new database.

    [Important]Important

    Do NOT leave the old tables in the database.

  3. Restore the backup. When you get to Restore Site's Database page find the Database Table Name Prefix and set that prefix to all lowercase characters. For example, if it was FbZt_ change it to fbzt_.

Prev Up Next
Q105 - Log directory is the same as the site's root Home Q201 - Obsolete version of PHP