Here are the answers to your questions
1.Are the ALTER TABLE commands above correct? (In particular I'm not sure if I should append a number-in-parenthesis to MEDIUMINT, and if so, what number it should be)
ALTER TABLE person_per MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0';
ALTER TABLE person_per MODIFY per_EditedBy MEDIUMINT unsigned default '0';
Yes, they are correct. You could also do this in a singe command:
ALTER TABLE person_per
MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0',
MODIFY per_EditedBy MEDIUMINT unsigned default '0'
;
The parentheses are not needed. In fact, the number will become 8 or 9.
2.Will this alteration correctly preserve the data that is already stored in those two columns? I don't want to lose it.
The data will be preserved. In effect the alter table will behave like this:
CREATE TABLE person_per_new LIKE person_per;
ALTER TABLE person_per_new
MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0',
MODIFY per_EditedBy MEDIUMINT unsigned default '0'
;
ALTER TABLE person_per_new DISABLE KEYS;
INSERT INTO person_per_new SELECT * FROM person_per;
ALTER TABLE person_per_new ENABLE KEYS;
ALTER TABLE person_per RENAME person_per_old;
ALTER TABLE person_per_new RENAME person_per;
DROP TABLE person_per_old;
Under the hood, these are the steps that are executed. I trust that MySQL will perform your ALTER TABLE in like manner.
3.Are there any obvious 'gotchas' that I might run into if I widen these columns? AFAICT SQL queries don't depend too much on integer columns being a particular width, but perhaps there is some non-obvious dependency that I should be aware of?
AFAICT you would only need to consider the size of the .MYD and .MYI files. Since SMALLINT is 2 bytes, and MEDIUMINT is 3 bytes, look for the files to increase byte 1 byte for each record. Thus, 1,000,000 rows in a 10MB MYD will now be about 11MB.
MyISAM tables with a wider row may need some adjustments (increases) in things like
to support data pages, index pages, and temp tables that involve per_EnteredBy and per_EditedBy
To make online changes to a table you can use pt-online-schema-change
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
The table will be blocked for a few seconds when it switches new and old tables. The table though must have a primary keys and not have triggers.
Best Answer
Adding a column in MySQL 5.6 requires the full table to be rebuilt. This becomes online in MySQL 8.0 though, provided the column is at the end of the table! The MySQL manual covers this under 14.13.1 Online DDL Operations (search in page for "Adding a column"):
Adding secondary indexes (i.e. not the primary key) should always be delayed until the last step so they can be sorted and optimally created. In the manual page I linked to, any operation that says
In Place=Noit will make sense to drop indexes first as well.