There's no need to use unsigned int in the reference data tables, the length of a signed int is more than enough and the disk space saved is nothing compared to the rest of the database. Also, this doesn't play very well with the django orm (see https://code.djangoproject.com/ticket/56).

I think we can't do a single ALTER to changethe column type because of eventual foreign keys referencing those columns. The only safe solution seems to be: 1- failover table/db offline 2- drop the foreign key constraints 3- alter the column data type 4- add the foreign constraints :sheeri can you please confirm my plan is reasonable?

That sounds good, yes.

This is an interim patch to make the reference data schema use normal integers, which lets me progress on my work. I believe this should not affect what's in production, we still need to fix that.

Commit pushed to master at https://github.com/mozilla/treeherder https://github.com/mozilla/treeherder/commit/a8c0348245ffa6eca374832898ebcef1a96f1b4e Bug 1194226 - Use unsigned int in reference data tables

This SQL does what mdoglio suggests in comment 1. I verified by importing a dump of the stage database and applying that the columns were altered and constraints were recreated.

Comment on attachment 8651193 [details] SQL to modify table Looks good, and if it worked on a copy of stage, I say let's test it out on the real stage!

Modified the schema on both stage and prod, seems to be working fine.

I don't suppose you could also apply this to the RDS instance we use on Heroku? Instructions in bug 1165259 comment 1 + bug 1165259 comment 2.

The sql script above didn't set id's to auto_increment, which meant no new idea could be added to the db. This is bad, we need to fix it. After a lot of trial and error with mdoglio and sheeri, I think I came up with something that basically works (in addition to changing the auto_increment property, we also need to reset where the auto increment should start). It seems to work ok against stage, but I'm not really comfortable applying this to production without another review. Sheeri, could you look this over when you have a spare moment and let me know? It would be nice to fix this by tomorrow if possible.

Comment on attachment 8655670 [details] Updated SQL to run which sets autoincrement correctly Looks good to me, ship it!

Applied the updated SQL to prod, hopefully we're done here now.