@recaptime-dev's working patches + fork for Phorge, a community fork of Phabricator. (Upstream dev and stable branches are at upstream/main and upstream/stable respectively.)
hq.recaptime.dev/wiki/Phorge
phorge
phabricator
1@title Managing Storage Adjustments
2@group config
3
4Explains how to apply storage adjustments to the MySQL schemata.
5
6Overview
7========
8
9Phorge uses a workflow called //storage adjustment// to make some minor
10kinds of changes to the MySQL schema. This workflow compliments the //storage
11upgrade// workflow, which makes major changes.
12
13You can perform storage adjustment by running:
14
15 phorge/ $ ./bin/storage adjust
16
17This document describes what adjustments are, how they relate to storage
18upgrades, how to perform them, and how to troubleshoot issues with storage
19adjustment.
20
21
22Understanding Adjustments
23===================
24
25Storage adjustments make minor changes to the Phorge MySQL schemata to
26improve consistency, unicode handling, and performance. Changes covered by
27adjustment include:
28
29 - Character set and collation settings for columns, tables, and databases.
30 - Setting and removing "Auto Increment" on columns.
31 - Adding, removing, renaming and adjusting keys.
32
33Adjustment does not make major changes to the schemata, like creating or
34removing columns or tables or migrating data. (Major changes are performed by
35the upgrade workflow.)
36
37Adjustments are separate from upgrades primarily because adjustments depend on
38the MySQL version, while upgrades do not. If you update MySQL, better collations
39may become available, and the adjustment workflow will convert your schemata to
40use them.
41
42All changes covered by adjustment are minor, and technically optional. However,
43you are strongly encouraged to apply outstanding adjustments: if you do not,
44you may encounter issues storing or sorting some unicode data, and may suffer
45poor performance on some queries.
46
47
48Reviewing Outstanding Adjustments
49=================================
50
51There are two ways to review outstanding adjustments: you can use the web UI,
52or you can use the CLI.
53
54To access the web UI, navigate to {nav Config > Services > Database Status} or
55{nav Config > Services > Schemata Issues}. The //Database Status// panel
56provides a general overview of all schemata. The //Schemata Issues// panel
57shows outstanding issues.
58
59These interfaces report //Errors//, which are serious issues that can not be
60resolved through adjustment, and //Warnings//, which are minor issues that the
61adjustment workflow can resolve.
62
63You can also review adjustments from the CLI, by running:
64
65 phorge/ $ ./bin/storage adjust
66
67Before you're prompted to actually apply adjustments, you'll be given a list of
68available adjustments. You can then make a choice to apply them.
69
70
71Performing Adjustments
72======================
73
74To perform adjustments, run the `adjust` workflow:
75
76 phorge/ $ ./bin/storage adjust
77
78For details about flags, use:
79
80 phorge/ $ ./bin/storage help adjust
81
82You do not normally need to run this workflow manually: it will be run
83automatically after you run the `upgrade` workflow.
84
85
86History and Rationale
87=====================
88
89The primary motivation for the adjustment workflow was MySQL's handling of
90unicode character sets.
91MySQL 5.5.3 (released March 2010) introduced a new `utf8mb4` character set.
92This character set allows to safely store 4-byte unicode characters.
93
94The adjustment workflow allows us to primarily use `utf8mb4` character sets.
95
96If your install was set up after November 2014, adjustments should generally
97be very minor and complete quickly, unless you perform a major MySQL update and
98make new character sets available.
99
100Only if your Phorge install predates November 2014 and you have not updated
101ever since, your first adjustment may take a long time (we must convert all of
102the data out of the `utf8` character set).
103
104If you plan to update MySQL from an old version (5.5.3 or older) to a recent
105version, it is advisable to update first, then run the adjustment workflow.
106
107
108Troubleshooting
109===============
110
111When you apply adjustments, some adjustments may fail. Some of the most common
112errors you may encounter are:
113
114 - **#1406 Data Too Long**: Usually this is caused by a very long object name
115 (like a task title) which contains multibyte unicode characters. When the
116 column type is converted to `binary`, only the first part of the title still
117 fits in the column. Depending on what is failing, you may be able to find
118 the relevant object in the web UI and retitle it so the adjustment succeeds.
119 Alternatively, you can use `--unsafe` to force the adjustment to truncate
120 the title. This will destroy some data, but usually the data is not
121 important (just the end of very long titles).
122 - **#1366 Incorrect String Value**: This can occur when converting invalid
123 or truncated multibyte unicode characters to a unicode character set.
124 In both cases, the old value can not be represented under the new character
125 set. You may be able to identify the object and edit it to allow the
126 adjustment to proceed, or you can use the `--unsafe` flag to truncate the
127 data at the invalid character. Usually, the truncated data is not important.
128
129As with most commands, you can add the `--trace` flag to get more details about
130what `bin/storage adjust` is doing. This may help you diagnose or understand any
131issues you encounter, and this data is useful if you file reports in the
132upstream.
133
134In general, adjustments are not critical. If you run into issues applying
135adjustments, it is safe to file a task in the upstream describing the problem
136you've encountered and continue using Phorge normally until the issue can
137be resolved.
138
139Surplus Schemata
140================
141
142After performing adjustment, you may receive an error that a table or column is
143"Surplus". The error looks something like this:
144
145| Target | Error |
146| --- | --- |
147| phorge_example.example_table | Surplus |
148
149Generally, "Surplus" means that Phorge does not expect the table or column
150to exist. These surpluses usually exist because you (or someone else
151with database access) added the table or column manually. Rarely, they can
152also exist for other reasons. They are usually safe to delete, but because
153deleting them destroys data and Phorge can not be sure that the table or
154column doesn't have anything important in it, it does not delete them
155automatically.
156
157If you recognize the schema causing the issue as something you added and you
158don't need it anymore, you can safely delete it. If you aren't sure whether
159you added it or not, you can move the data somewhere else and delete it later.
160
161To move a table, first create a database for it like `my_backups`. Then, rename
162the table to move it into that database (use the table name given in the error
163message):
164
165```lang=sql
166CREATE DATABASE my_backups;
167RENAME TABLE phorge_example.example_table
168 TO my_backups.example_table;
169```
170
171Phorge will ignore tables that aren't in databases it owns, so you can
172safely move anything you aren't sure about outside of the Phorge databases.
173
174If you're sure you don't need a table, use `DROP TABLE` to destroy it,
175specifying the correct table name (the one given in the error message):
176
177```lang=sql
178DROP TABLE phorge_example.example_table;
179```
180
181This will destroy the table permanently.