Closed
Bug 1194226
Opened 9 years ago
Closed 9 years ago
Change the primary keys of the reference data tables to be normal integers
Categories
(Tree Management :: Treeherder, defect)
Tree Management
Treeherder
Tracking
(Not tracked)
RESOLVED
FIXED
People
(Reporter: mdoglio, Assigned: wlach)
References
Details
Attachments
(2 files, 1 obsolete file)
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).
Reporter | ||
Comment 1•9 years ago
|
||
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?
Flags: needinfo?(scabral)
Assignee | ||
Comment 3•9 years ago
|
||
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.
Attachment #8651154 -
Flags: review?(mdoglio)
Reporter | ||
Updated•9 years ago
|
Attachment #8651154 -
Flags: review?(mdoglio) → review+
Comment 4•9 years ago
|
||
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
Assignee | ||
Updated•9 years ago
|
Assignee: nobody → wlachance
Assignee | ||
Comment 5•9 years ago
|
||
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.
Attachment #8651193 -
Flags: feedback?(scabral)
Comment 6•9 years ago
|
||
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!
Attachment #8651193 -
Flags: feedback?(scabral) → feedback+
Assignee | ||
Comment 7•9 years ago
|
||
Modified the schema on both stage and prod, seems to be working fine.
Status: NEW → RESOLVED
Closed: 9 years ago
Resolution: --- → FIXED
Comment 8•9 years ago
|
||
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.
Assignee | ||
Comment 9•9 years ago
|
||
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.
Attachment #8651193 -
Attachment is obsolete: true
Assignee | ||
Updated•9 years ago
|
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
Assignee | ||
Updated•9 years ago
|
Attachment #8655670 -
Flags: feedback?(scabral)
Comment 10•9 years ago
|
||
Comment on attachment 8655670 [details]
Updated SQL to run which sets autoincrement correctly
Looks good to me, ship it!
Attachment #8655670 -
Flags: feedback?(scabral) → feedback+
Assignee | ||
Comment 11•9 years ago
|
||
Applied the updated SQL to prod, hopefully we're done here now.
Status: REOPENED → RESOLVED
Closed: 9 years ago → 9 years ago
Resolution: --- → FIXED
You need to log in
before you can comment on or make changes to this bug.
Description
•