Home › Documentation › Kohana › Moto-trials project › Updating moto-trials schema

Updating moto-trials schema

JohnPosted on Posted in Moto-trials project, MySQL

“The hour of departure has arrived, and we go our separate ways, I to die, and you to live. Which of these two is better only God knows.” – Socrates

Changes being made:

  • Going from each entrant being in only one class, to an entrant being able to be in a multiple classes. This means a new many-to-many relationship and a minor data migration to the new format.
  • Add series tables.
  • Standardized all auto-increment primary keys as INT(11) UNSIGNED.

Live Server (LS): phpMyAdmin – 2.9.1.1, MySQL – 5.0.27
Development Machine (DM): phpMyAdmin – 4.4.13.1, MySQL – 5.6.26

  1. Export from LS with foreign key checks enabled.
  2. Import into DM.
  3. Rename DM table “event_has_class_competitor” to “event_has_competitor”
    1. See #8 below if you want to look ahead to a minor problem that this causes.
  4. Synchronise workbench (6.3) model with DM (For this step to work with the least mucking around, read #5 below FIRST, and then Synch)
    Synching moto-trials database

    1. ERROR: Error 1062: Duplicate entry ‘0-0’ for key ‘PRIMARY’
      SQL Code: ALTER TABLE `mototrials_co_nz_-_site`.`club_has_user`….

      1. Ignore club_has_user and go again.
    2. ERROR: Error 1062: Duplicate entry ‘0-0’ for key ‘PRIMARY’
      SQL Code: ALTER TABLE `mototrials_co_nz_-_site`.`roles_users`

      1. Ignore roles_users and go again
    3. Success
    4. Now sync again… and it all works.
  5. SO, JUST BACK-UP ONE COTTON-PICKING MOMENT HERE… Problem was that keys had been changed to UNSIGNED and Workbench was doing a drop column, add column… and that was causing the problem. So, before synching, manually alter the offending columns in these two tables (on DM db) to unsigned, and then Synch works perfectly.
  6. Copy INSERTS for class_colours from Workbench to DM
  7. Copy INSERTS for classes from Workbench to DM. Only need inserts for rows 14-18.
    1. Do some manual UPDATES for classes to fill in new columns class_colour_id, rank, vcs for records 1-13
  8. Now need to migrate some data from event_has_competitor to event_competitor_has_class
    1. insert into `event_competitor_has_class` (class_id,event_competitor_id,event_id) select class_id,id,event_id from event_has_competitor
    2. OUCH! class_id has disappeared from event_has_competitor after synching with Workbench.
    3. Back to LM. Export just this table, and then import into DM. Error!
      duplicate-key-error
    4. ADD CONSTRAINT `fk_competitor_has_event_entry` was causing the problem. This is the same name as a constraint in event_has_competitor.  All that is needed is to change this name to something else before importing the file. e.g. ADD CONSTRAINT `fk_competitor_has_event_entry1`
    5. Back to step 1… insert into `event_competitor_has_class` (class_id,event_competitor_id,event_id) select class_id,id,event_id from `event_has_class_competitor`
      1. NB: Obviously we’re now inserting FROM the old event_has_class_competitor, not the new event_has_competitor (which has no class_id)
    6. Now tidy up… and DROP event_has_class_competitor from the DM before we get confused :-}
  9. FINAL TOUCH: Assign all Ixion ‘Presidents’ entries to the new ‘Old Foggies’ class…
    update event_competitor_has_class echc set echc.class_id=18 where echc.class_id=6 and echc.event_id in (select id from events where events.club_id=118)