search

stats4sd / aec_portfolio

A proof of concept for the AEC Consortium Project Management / Assessment System
GNU General Public License v3.0
0 stars 0 forks source link

Fix Region Issue #192

Closed dan-tang-ssd closed 1 year ago

dan-tang-ssd commented 1 year ago

Note: This is a status update rather than a PR.

Update: This is now ready for review. This PR fixes #153

The region issue is being investigated with below items:

  1. Reviewed Project, Continent, Region, Country model and their relationships. They look normal.

  2. Some or all regions may disappear after editing an existing initiative. Number of regions removed is not fixed each time.

  3. Problem occurs only when editing existing initiative. Problem does not occur when creating new initiative.

  4. Similar issue also happened for continent, it is easier to replicate when we have multiple continents.

  1. Issue can be replicated with below testing procedure:
    • Edit an existing initiative, which has region A already
    • Do not change anything, save
    • Edit this initiative again
    • Region A is gone
    • Add region A, save
    • Edit this initiative again
    • Region A is here
    • It looks like "newly added regions" will be kept, while "existing regions" will be removed (this explains why creating initative can keep region because region is newly added)
  1. I added log message in Project model events retrieved, creating, created, updating, updated, saving, saved. After clicking Save button in front end, log messages showed that region is removed somehow between saved event and retrieved event. P.S. As all portfolio and project models will be retrieved in initiative page, I have removed all other portfolios and projects to keep one portfolio and one project for clearer log messages.

[2023-07-28 09:13:42] local.DEBUG: ProjectCrudController.setupUpdateOperation()
[2023-07-28 09:13:42] local.DEBUG: Project (573) - retrieved(): 1
[2023-07-28 09:13:42] local.DEBUG: Eastern Asia
[2023-07-28 09:13:42] local.DEBUG: 1 region(s)
[2023-07-28 09:13:42] local.DEBUG: Eastern Asia
[2023-07-28 09:13:43] local.DEBUG: Project (573) - retrieved(): 1
[2023-07-28 09:13:43] local.DEBUG: Eastern Asia
[2023-07-28 09:13:43] local.DEBUG: Project (573) - retrieved(): 1
[2023-07-28 09:13:43] local.DEBUG: Eastern Asia
[2023-07-28 09:13:43] local.DEBUG: Project (573) - saving(): 1
[2023-07-28 09:13:43] local.DEBUG: Project (573) - updating(): 1
[2023-07-28 09:13:43] local.DEBUG: Project (573) - updated(): 1

[2023-07-28 09:13:43] local.DEBUG: Project (573) - saved(): 1
[2023-07-28 09:13:43] local.DEBUG: Eastern Asia
[2023-07-28 09:13:43] local.DEBUG: Project (573) - retrieved(): 0

[2023-07-28 09:13:43] local.DEBUG: Portfolio (1008) - retrieved()

  1. There is a ProjectRegion model. I also added log message in model events. It has log message for retrieved event only. It is strange that project_region record with id 1 is always retrieved. (Which is not related to project model)

[2023-07-28 09:23:02] local.DEBUG: ProjectRegion (1) - retrieved():
[2023-07-28 09:23:02] local.DEBUG: {"id":"1","project_id":1,"region_id":154,"created_at":null,"updated_at":null}

  1. I removed ProjectRegion model then perform testing, I got same testing result.

  2. Checked that there is no database level trigger defined. SQL: show triggers;

dave-mills commented 1 year ago

Interesting that it loses continents too, but not always...

I wonder if it's a mismatch between ID types. The IDs in the continents and regions tables are strings, because some are 0-padded. (E.g. South America is "005", not "5"). Could that be an issue, if the relationship tables are ints?

One test - does Central Asia and Western Asia get kept in regions? (Their IDs are 143 and 145).

dan-tang-ssd commented 1 year ago

Interesting that it loses continents too, but not always...

I wonder if it's a mismatch between ID types. The IDs in the continents and regions tables are strings, because some are 0-padded. (E.g. South America is "005", not "5"). Could that be an issue, if the relationship tables are ints?

One test - does Central Asia and Western Asia get kept in regions? (Their IDs are 143 and 145).

Wow, you found the root cause!

Performed testing and proved that your suspection is correct.

The solution is to change column type from big unsigned to string for continent_id and region_id columns in mapping tables. We also need to run UPDATE SQL to add padding zero for these two columns in existing mapping table records.

dan-tang-ssd commented 1 year ago

Deployment in staging env is completed. Performed testing with positive result.

Continent, region, country can be saved and retrieved properly. It works properly when I edit an existing initiative and creating a new initiative.

Also checked database tables contenient_project, country_project, project_region. The migration file has updated contentient_id, country_id, region_id with padding 0 format.

Screen shots:

Edit an existing initiative image

Create a new initiative image

Check database existing records image

image

image