Hacking FRM files to switch data types

By Harrison Fisk on Wednesday, March 23, 2011 at 11:39am

Problem

We recently ran into a problem with a legacy MySQL database with a TINYINT column being used for storing flags.  A developer wanted to store > 127 in the field, but naturally MySQL doesn't allow this.  Instead, it just puts 127 into the field which is pretty bad.  On a side note, strict mode would have given a nice error, rather than storing 127 in there.

 

Solution

The solution was to change the datatype from TINYINT to TINYINT UNSIGNED.  The problem is that this is a very large dataset, ~14TB across a bunch of systems.  We could use our online schema change tool (OSC) to do this change, but it would still take a week or two to run.

 

So instead of doing this, we did an FRM hack.  We created the same table, but with the TINYINT UNSIGNED instead and copied it's FRM over the existing one.  Once we did a FLUSH TABLES on it, it now allowed larger values to be inserted!

 

Why does it work?

 To ensure this would work, we reviewed the code to see what is going on.  InnoDB does store the fact it is an unsigned field in its own table metadata, so MySQL and InnoDB actually interpret the data differently (ie. 160 in MySQL is -33 in InnodB).  

 

InnoDB uses the signed vs. unsigned for doing internal comparisons.   However, these only matter for index lookups and this field is not indexed.  Otherwise, MySQL does most comparisons external to the storage engine.

 

Even with this knowledge, we still only decided to do it since it is a legacy database which will go away soon.

 

Still, we reduced a 1-2 week operation into ~30 seconds plus an hour or so of code review and verification.