Managing Storage Adjustments

Phorge Administrator and User Documentation (Configuration)

Explains how to apply storage adjustments to the MySQL schemata.

Overview

Phorge uses a workflow called storage adjustment to make some minor kinds of changes to the MySQL schema. This workflow compliments the storage upgrade workflow, which makes major changes.

You can perform storage adjustment by running:

phorge/ $ ./bin/storage adjust

This document describes what adjustments are, how they relate to storage upgrades, how to perform them, and how to troubleshoot issues with storage adjustment.

Understanding Adjustments

Storage adjustments make minor changes to the Phorge MySQL schemata to improve consistency, unicode handling, and performance. Changes covered by adjustment include:

• Character set and collation settings for columns, tables, and databases.
• Setting and removing "Auto Increment" on columns.
• Adding, removing, renaming and adjusting keys.

Adjustment does not make major changes to the schemata, like creating or removing columns or tables or migrating data. (Major changes are performed by the upgrade workflow.)

Adjustments are separate from upgrades primarily because adjustments depend on the MySQL version, while upgrades do not. If you update MySQL, better collations may become available, and the adjustment workflow will convert your schemata to use them.

All changes covered by adjustment are minor, and technically optional. However, you are strongly encouraged to apply outstanding adjustments: if you do not, you may encounter issues storing or sorting some unicode data, and may suffer poor performance on some queries.

Reviewing Outstanding Adjustments

There are two ways to review outstanding adjustments: you can use the web UI, or you can use the CLI.

To access the web UI, navigate to Config → Database Status or Config → Database Issues. The Database Status panel provides a general overview of all schemata. The Database Issues panel shows outstanding issues.

These interfaces report Errors, which are serious issues that can not be resolved through adjustment, and Warnings, which are minor issues that the adjustment workflow can resolve.

You can also review adjustments from the CLI, by running:

phorge/ $ ./bin/storage adjust

Before you're prompted to actually apply adjustments, you'll be given a list of available adjustments. You can then make a choice to apply them.

Performing Adjustments

To perform adjustments, run the adjust workflow:

phorge/ $ ./bin/storage adjust

For details about flags, use:

phorge/ $ ./bin/storage help adjust

You do not normally need to run this workflow manually: it will be run automatically after you run the upgrade workflow.

History and Rationale

The primary motivation for the adjustment workflow is MySQL's handling of unicode character sets. Before MySQL 5.5, MySQL supports a character set called utf8. However, this character set can not store 4-byte unicode characters (including emoji). Inserting 4-byte characters into a utf8 column truncates the data.

With MySQL 5.5, a new utf8mb4 character set was introduced. This character set can safely store 4-byte unicode characters.

The adjustment workflow allows us to alter the schema to primarily use binary character sets on older MySQL, and primarily use utf8mb4 character sets on newer MySQL. The net effect is that Phorge works consistently and can store 4-byte unicode characters regardless of the MySQL version. Under newer MySQL, we can also take advantage of the better collation rules the utf8mb4 character set offers.

The adjustment workflow was introduced in November 2014. If your install predates its introduction, your first adjustment may take a long time (we must convert all of the data out of utf8 and into the appropriate character set). If your install was set up after November 2014, adjustments should generally be very minor and complete quickly, unless you perform a major MySQL update and make new character sets available.

If you plan to update MySQL from an older version to 5.5 or newer, it is advisable to update first, then run the adjustment workflow. If you adjust first, you'll need to adjust again after updating, so you'll end up spending twice as much time performing schemata adjustments.

Troubleshooting

When you apply adjustments, some adjustments may fail. Some of the most common errors you may encounter are:

• #1406 Data Too Long: Usually this is caused by a very long object name (like a task title) which contains multibyte unicode characters. When the column type is converted to binary, only the first part of the title still fits in the column. Depending on what is failing, you may be able to find the relevant object in the web UI and retitle it so the adjustment succeeds. Alternatively, you can use --unsafe to force the adjustment to truncate the title. This will destroy some data, but usually the data is not important (just the end of very long titles).
• #1366 Incorrect String Value: This can occur when converting invalid or truncated multibyte unicode characters to a unicode character set. In both cases, the old value can not be represented under the new character set. You may be able to identify the object and edit it to allow the adjustment to proceed, or you can use the --unsafe flag to truncate the data at the invalid character. Usually, the truncated data is not important.

As with most commands, you can add the --trace flag to get more details about what bin/storage adjust is doing. This may help you diagnose or understand any issues you encounter, and this data is useful if you file reports in the upstream.

In general, adjustments are not critical. If you run into issues applying adjustments, it is safe to file a task in the upstream describing the problem you've encountered and continue using Phorge normally until the issue can be resolved.

Surplus Schemata

After performing adjustment, you may receive an error that a table or column is "Surplus". The error looks something like this:

Generally, "Surplus" means that Phorge does not expect the table or column to exist. These surpluses usually exist because you (or someone else with database access) added the table or column manually. Rarely, they can also exist for other reasons. They are usually safe to delete, but because deleting them destroys data and Phorge can not be sure that the table or column doesn't have anything important in it, it does not delete them automatically.

If you recognize the schema causing the issue as something you added and you don't need it anymore, you can safely delete it. If you aren't sure whether you added it or not, you can move the data somewhere else and delete it later.

To move a table, first create a database for it like my_backups. Then, rename the table to move it into that database (use the table name given in the error message):

CREATE DATABASE my_backups;
RENAME TABLE phorge_example.example_table
  TO my_backups.example_table;

Phorge will ignore tables that aren't in databases it owns, so you can safely move anything you aren't sure about outside of the Phorge databases.

If you're sure you don't need a table, use DROP TABLE to destroy it, specifying the correct table name (the one given in the error message):

DROP TABLE phorge_example.example_table;

This will destroy the table permanently.