How safe is it to widen a column in MySQL?

Question

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot.

A MySQL database I volunteered to help maintain has a table that looks like this:

CREATE TABLE person_per (
   [...]
   per_EnteredBy smallint(5) unsigned NOT NULL default '0',
   per_EditedBy smallint(5) unsigned default '0',
   [...]
) TYPE=MyISAM;

This works fine, except that recently our user IDs started to be larger than 65535, which caused the values stored into the above fields to be incorrect (i.e. large values get capped at 65535 rather than the expected value).

What I'd like to do is 'widen' those two columns of the table so that user IDs greater than 65535 can be correctly stored in them. I think the commands to do it are:

ALTER TABLE person_per MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0';
ALTER TABLE person_per MODIFY per_EditedBy MEDIUMINT unsigned default '0';

My questions are:

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)

2. Will this alteration correctly preserve the data that is already stored in those two columns? I don't want to lose it.

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?

Answer 1

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

• key_buffer_size
• join_buffer_size
• sort_buffer_size
• read_buffer_size
• read_rnd_buffer_size
• tmp_table_size

to support data pages, index pages, and temp tables that involve per_EnteredBy and per_EditedBy