search

unee-t / lambda2sqs

Relays SQL triggered payloads to MEFE via a queue
https://ap-southeast-1.console.aws.amazon.com/lambda/home?region=ap-southeast-1#/applications/lambda2sqs
GNU Affero General Public License v3.0
0 stars 4 forks source link

MEFE API - Unit creation #9

Open kaihendry opened 5 years ago

kaihendry commented 5 years ago

We need to know the JSON payloads and the SQL queries itemised step by step.

franck-boullier commented 5 years ago

The lambda generated from the SQL will look like this:

CALL mysql.lambda_asyncarn:aws:lambda:ap-southeast-1:192458993663:function:alambda_simple{"actionType": "CREATE_UNIT", "creatorId": "NXnKGEdEwEvMgWQtG", "name": "SG_SIN_LUM_36-01_05R", "moreInfo": null, "streetAddress": "Lumiere \n #36-01", "city": null, "state": "", "zipCode": null, "country": null, "ownerId": "NXnKGEdEwEvMgWQtG"}

@kaihendry this is most likely missing the id for the record from the origin. Suggestion: name this unitCreationRequestId

kaihendry commented 5 years ago

I believe I need to POST to **POST Create a new unit by MEFE internal API*** with MEFE internal API.

An example payload is:

{
    "actionType": "CREATE_UNIT",
    "creatorId": "R4vBD6BZRCNx8JwnM",
    "name": "Unit test 128288",
    "type": "Villa",
    "moreInfo": "lasdl",
    "streetAddress": "asdads",
    "city": "asdasd",
    "state": "",
    "zipCode": "292929",
    "country": "Singapore"
}

So I will check for actionType for posting to /api/process-api-payload othewise it will post by default to /api/db-change-message/process

nbiton commented 5 years ago

An example request and response from MEFE API to a request with an "actionType" of "CREATE_UNIT":

{
    "actionType": "CREATE_UNIT",
    "creatorId": "R4vBD6BZRCNx8JwnM",
    "name": "Unit test 128288",
    "type": "Villa",
    "moreInfo": "lasdl",
    "streetAddress": "asdads",
    "city": "asdasd",
    "state": "",
    "zipCode": "292929",
    "country": "Singapore"
}
{
    "unitMongoId": "m3KTgSW2m1FSLSCnp",
    "timestamp": "2019-03-18T03:37:03.179Z"
}
franck-boullier commented 5 years ago

The SQL that will need to be run to update the SQL after the Unit is created (i.e: the MEFE API Returns a success):

SET @unit_creation_request_id = 'unitCreationRequestId'; SET @mefe_unit_id = 'unitMongoId (from_API_response)'; SET @creation_datetime = 'timestamp (from_API_response)'; CALL ut_creation_success_mefe_unit_id;

kaihendry commented 5 years ago

Time stamp is just a string right? No special sql Type?

No environment variables, right?

franck-boullier commented 5 years ago

Time stamp is just a string right? No special sql Type?

Timestamp is SQL datetime

No environment variables, right?

Not in v1.0

kaihendry commented 5 years ago

@franck-boullier I'm getting Error 1305: PROCEDURE bugzilla.ut_creation_success_mefe_unit_id does not exist in the dev environment.

nbiton commented 5 years ago

timestamp is an iso string date format (in the API response)

franck-boullier commented 5 years ago

In order to capture the information 'was this unit there in Unee-T before or not?' we need to change the SQL as such:

SET @unit_creation_request_id = 'unitCreationRequestId'; SET @mefe_unit_id = 'unitMongoId (from_API_response)'; SET @creation_datetime = 'timestamp (from_API_response)'; SET @is_created_by_me = 0; CALL ut_creation_success_mefe_unit_id;

SET @unit_creation_request_id = 'unitCreationRequestId'; SET @mefe_unit_id = 'unitMongoId (from_API_response)'; SET @creation_datetime = 'timestamp (from_API_response)'; SET @is_created_by_me = 1; CALL ut_creation_success_mefe_unit_id;

kaihendry commented 5 years ago

Still the "Error 1305: PROCEDURE bugzilla.ut_creation_success_mefe_unit_id does not exist" issue.

kaihendry commented 5 years ago

This is pending some lambda permissions setup (case id #5900089011) on UNEE-T_ENTERPRISE_RDS IIUC. @franck-boullier can you confirm I need to talk to this separate RDS instead instead of another database in auroradb.dev.unee-t.com?

kaihendry commented 5 years ago

@franck-boullier could you please test? Perhaps a different payload to my https://github.com/unee-t/lambda2sns/blob/master/tests/events/create_unit.json

kaihendry commented 5 years ago

AFAICT this works. If it doesn't please re-open and tell me how you tested it.

franck-boullier commented 5 years ago

@kaihendry This does not seem to work as intended ---> re-opening this:

The Test:

In the DEV/Staging environment: Create a new unit in the Unee-T Enterprise database

Expected result:

Actual result:

@kaihendry any idea what might be happening?

kaihendry commented 5 years ago

You need to examine to logs or subscribe to the SNS. The failure seems to be payload failing with MEFE https://media.dev.unee-t.com/2019-03-24/log.png

kaihendry commented 5 years ago

IIUC null values are choking the MEFE API with "Match error: Expected string, got null"

Currently I do not parse the JSON message, I just relay it. For me to filter the JSON from null values, e.g.: https://play.golang.org/p/KuCfLW-o0GV

I would need to pedantically define the structure. If the structure changes, it will be a problem, since Golang is strongly typed. If the structure won't change, then fine, happy to implement it.

It's just that I think Javascript is better position to filter null values, since it's easier to do here and it wouldn't mean complexity is spread across two stacks (lambda & MEFE).

WDYT @nbiton ?

kaihendry commented 5 years ago

unitType was missing from the payload. NULL values error message was a red herring. Hopfully @nbiton will improve the error messages returned by the MEFE API. Thanks!

franck-boullier commented 5 years ago

@nbiton in the payload the ownerId seems to be missing. Is the creatorId the same as the MEFE concept ownerId?

I'd prefer to have something like

{
    "actionType": "CREATE_UNIT",
    "creatorId": "R4vBD6BZRCNx8JwnM",
    "name": "Unit test 128288",
    "type": "Villa",
    "moreInfo": "lasdl",
    "streetAddress": "asdads",
    "city": "asdasd",
    "state": "",
    "zipCode": "292929",
    "country": "Singapore",
        "ownerId": "R4vBD6BZRCNx8JwnM"
}
nbiton commented 5 years ago

"ownerId" can also be provided. It is an optional param. The "creatorId" is uswd as default

kaihendry commented 5 years ago

I got in the logs

Error: 400 Bad Request from MEFE: https://case.dev.unee-t.com/api/process-api-payload?accessToken=xxxxxxxx, Response: "Match error: Expected string, got null" from Request: {"city": null, "name": "TEST_B_1", "type": "Condominium", "state": null, "country": null, "ownerId": "YYeAutqzDY3MeqbNC", "zipCode": null, "moreInfo": "Test Building 1", "creatorId": "YYeAutqzDY3MeqbNC", "actionType": "CREATE_UNIT", "streetAddress": null, "unitCreationRequestId": 1}

You can also subscribe to the arn:aws:sns:ap-southeast-1:812644853088:process-api-payload to receive these errors to your inbox.

franck-boullier commented 5 years ago

Still unable to create a unit. I know it went through (lambda was fired correctly) but nothing happened on the MEFE side.

The payload:

''' {"city": null, "name": "TEST_B_1", "type": "Condominium", "state": null, "country": null, "ownerId": "YYeAutqzDY3MeqbNC", "zipCode": null, "moreInfo": "Test Building 1", "creatorId": "YYeAutqzDY3MeqbNC", "actionType": "CREATE_UNIT", "streetAddress": null, "unitCreationRequestId": 1} '''

@kaihendry and @nbiton any idea on how we can fix this?

franck-boullier commented 5 years ago

I got in the logs

What's the fix then?

You can also subscribe to the arn:aws:sns:ap-southeast-1:812644853088:process-api-payload to receive these errors to your inbox.

There's no real point really: it'll be massively inefficient for me to try to debug this TBH...

kaihendry commented 5 years ago

Isn't unitType missing from your payload again? https://github.com/unee-t/lambda2sns/issues/9#issuecomment-476035471

franck-boullier commented 5 years ago

Isn't unitType missing from your payload again? #9 (comment)

@kaihendry I have specified the Unit Type as described by @nbiton here: https://github.com/unee-t/lambda2sns/issues/9#issuecomment-474168618 i.e I used type (as specified) and not unitType

What is the correct syntax? type or unitType?

nbiton commented 5 years ago

I updated the request in Postman "Create a new unit by MEFE internal API*" to include the "ownerId" param. "type" was already there.

franck-boullier commented 5 years ago

I updated my code to use unitType instead of type

Still no luck ---> unit does not seem to have been created...

The payload:

{"city": null, "name": "TEST_B_2", "state": null, "country": null, "ownerId": "YYeAutqzDY3MeqbNC", "zipCode": null, "moreInfo": "Test 2 Building created with API.", "unitType": "Condominium", "creatorId": "YYeAutqzDY3MeqbNC", "actionType": "CREATE_UNIT", "streetAddress": null, "unitCreationRequestId": 2}
franck-boullier commented 5 years ago

I updated my code again to use type instead of unitType

Still no luck ---> unit does not seem to have been created...

The payload:

{"city": null, "name": "TEST_B_3", "type": "Condominium", "state": null, "country": null, "ownerId": "YYeAutqzDY3MeqbNC", "zipCode": null, "moreInfo": "Test 3 - Building - created with MEFE API.", "creatorId": "YYeAutqzDY3MeqbNC", "actionType": "CREATE_UNIT", "streetAddress": null, "unitCreationRequestId": 3}
franck-boullier commented 5 years ago

Replacing NULL values with '' (empty)

The payload is now 

{"city": "", "name": "TEST_B_4", "type": "Condominium", "state": "", "country": "", "ownerId": "YYeAutqzDY3MeqbNC", "zipCode": "", "moreInfo": "Test 4 - replace NULL with '' if needed - MEFE API to create unit.", "creatorId": "YYeAutqzDY3MeqbNC", "actionType": "CREATE_UNIT", "streetAddress": "", "unitCreationRequestId": 4}

This seem to have been working as intended. Thanks for the help @kaihendry!

franck-boullier commented 5 years ago

@kaihendry This does not seem to work as intended ---> re-opening this:

The Test:

In the Demo environment: Create 3 new units in the Unee-T Enterprise database

Expected Result:

Actual result:

The payloads:

Unit 1

{"city": "Singapore ", "name": "Loft @ Holland - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": null, "zipCode": "278580", "moreInfo": "Loft@Holland is a freehold condominium development located at 151 Holland Road, Singapore 278580, in district 10. Expected to be completed in 2015, it stands 5 storeys tall and comprises a total of 41 units. Loft@Holland is relatively close to Holland Village MRT Station.\r\n\r\nCondo Facilities at Loft@Holland\r\n\r\nFacilities at Loft@Holland include swimming pool, pool deck and gym.\r\n\r\nCondo Amenities near Loft@Holland\r\n\r\nThe schools in the vicinity are New Town Primary School, Dunman High School and Clementi Town Secondary School.\r\n\r\nFor daily necessities and shopping needs, residents can head down to several shopping centres located within walking distance, such as Holland Village Shopping Mall.\r\n\r\nFor vehicle owners, driving from Loft@Holland to Orchard Road or Central Business District (CBD) takes about 5-10 minutes, via the Central Expressway (CTE). ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "151 Holland Road \n", "unitCreationRequestId": 1046}

Unit 2

{"city": "Singapore", "name": "Seahill - 119", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": null, "zipCode": "126777", "moreInfo": "Seahill is a 99-Year Leasehold Condominium located at 119, West Coast Crescent, 126777 in District 05. It comprises of 338 Units. Seahill is close to Clementi MRT (EW23). It is completed in 2016. Schools close to Seahill include Farrer Park Primary School, Saint Joseph’s Institution Junior and Stamford Primary School.\r\n\r\nFacilities in Seahill\r\n\r\nFacilities at Seahill include Sky Pool, Water Feature with Sunken Planter, 50M Lap Pool, Spa Island, Kids Pool, Dining Pavilion, Spa Pool, Massage Pavilion and Lounge Deck, Aqua Gym, Sea Mist Spa Pool, Function Deck, Meeting Pavilion, Jogging Track, Outdoor Fitness, Children Playground, Viewing Pavilion, Fitness Balcony and Plunge Pool.\r\n\r\nAmenities near Seahill\r\n\r\nResidents at Seahill can get to nearby supermarkets or shopping mall within the area for an array of amenities such as grocery and retail shopping, bank, eateries and more.\r\n\r\nSeahill is near to NTUC Fairprice, Cold Storage and Sheng Siong Supermarket. It is also close to West Coast Plaza.\r\n\r\nVehicle owners can take West Coast Highway, Clementi Road and Ayer Rajah Expressway (AYE) to get to the business hub or shopping district in the city. ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "119 West Coast Crescent  \n", "unitCreationRequestId": 1047}

Unit 3:

{"city": "Singapore", "name": "The Estuary - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": null, "zipCode": null, "moreInfo": "The Estuary\r\n\r\nA 99-year leasehold development located at 85 Yishun Avenue 1 Singapore 769132, in district 27\r\n\r\n* Completed in 2013, comprise of 608 units\r\n* Located near Khatib MRT Station\r\n* Located close to North point shopping centreand Yishun sports centre\r\n\r\nFacilitiles at The Estuary\r\n\r\n* Facilities include BBQ pit, clubhouse, fitness corner, gym, lap pool, fun pool, multi-purpose hall, steambath, playground and tennis court\r\n\r\nAmenities near The Estuary\r\n\r\n* Yishun Junior College and Chung Cheng High school", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": " \n", "unitCreationRequestId": 1050}

@kaihendry any idea what might be happening?

@nbiton how is it even possible that the units are created in the BZ Database and NOT created in the Mongo database??

franck-boullier commented 5 years ago

More information

I just spotted that the ownerId was null in the payload ---> this might be why things are broken.

I'll fix this

BUT we still need to plug that issue anyway: if an ownerId is set to null it seems that the MEFE API still partially creates Unee-T unit in a apparently very random manner:

franck-boullier commented 5 years ago

More information - New Test:

In the Demo environment: Create 3 new units in the Unee-T Enterprise database

Expected Result:

Actual result:

The payloads:

Unit 1

{"city": "Singapore ", "name": "Loft @ Holland - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "278580", "moreInfo": "Loft@Holland is a freehold condominium development located at 151 Holland Road, Singapore 278580, in district 10. Expected to be completed in 2015, it stands 5 storeys tall and comprises a total of 41 units. Loft@Holland is relatively close to Holland Village MRT Station.\r\n\r\nCondo Facilities at Loft@Holland\r\n\r\nFacilities at Loft@Holland include swimming pool, pool deck and gym.\r\n\r\nCondo Amenities near Loft@Holland\r\n\r\nThe schools in the vicinity are New Town Primary School, Dunman High School and Clementi Town Secondary School.\r\n\r\nFor daily necessities and shopping needs, residents can head down to several shopping centres located within walking distance, such as Holland Village Shopping Mall.\r\n\r\nFor vehicle owners, driving from Loft@Holland to Orchard Road or Central Business District (CBD) takes about 5-10 minutes, via the Central Expressway (CTE). ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "151 Holland Road \n", "unitCreationRequestId": 1046}

Unit 2

{"city": "Singapore", "name": "Seahill - 119", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "126777", "moreInfo": "Seahill is a 99-Year Leasehold Condominium located at 119, West Coast Crescent, 126777 in District 05. It comprises of 338 Units. Seahill is close to Clementi MRT (EW23). It is completed in 2016. Schools close to Seahill include Farrer Park Primary School, Saint Joseph’s Institution Junior and Stamford Primary School.\r\n\r\nFacilities in Seahill\r\n\r\nFacilities at Seahill include Sky Pool, Water Feature with Sunken Planter, 50M Lap Pool, Spa Island, Kids Pool, Dining Pavilion, Spa Pool, Massage Pavilion and Lounge Deck, Aqua Gym, Sea Mist Spa Pool, Function Deck, Meeting Pavilion, Jogging Track, Outdoor Fitness, Children Playground, Viewing Pavilion, Fitness Balcony and Plunge Pool.\r\n\r\nAmenities near Seahill\r\n\r\nResidents at Seahill can get to nearby supermarkets or shopping mall within the area for an array of amenities such as grocery and retail shopping, bank, eateries and more.\r\n\r\nSeahill is near to NTUC Fairprice, Cold Storage and Sheng Siong Supermarket. It is also close to West Coast Plaza.\r\n\r\nVehicle owners can take West Coast Highway, Clementi Road and Ayer Rajah Expressway (AYE) to get to the business hub or shopping district in the city. ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "119 West Coast Crescent  \n", "unitCreationRequestId": 1047}

Unit 3:

{"city": "Singapore", "name": "The Estuary - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": null, "moreInfo": "The Estuary\r\n\r\nA 99-year leasehold development located at 85 Yishun Avenue 1 Singapore 769132, in district 27\r\n\r\n* Completed in 2013, comprise of 608 units\r\n* Located near Khatib MRT Station\r\n* Located close to North point shopping centreand Yishun sports centre\r\n\r\nFacilitiles at The Estuary\r\n\r\n* Facilities include BBQ pit, clubhouse, fitness corner, gym, lap pool, fun pool, multi-purpose hall, steambath, playground and tennis court\r\n\r\nAmenities near The Estuary\r\n\r\n* Yishun Junior College and Chung Cheng High school", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": " \n", "unitCreationRequestId": 1050}

The problems:

Fixed issue:

kaihendry commented 5 years ago

Pulled out the logs from the demo env and interestingly it mentions "Error: failed [400] Error 1213: Deadlock found when trying to get lock; try restarting transaction [Unit creation API Lambda error]

https://media.dev.unee-t.com/2019-03-29/deadlock.txt

franck-boullier commented 5 years ago

More information - New Test:

In the Demo environment: Logged as the SQL user lambda_invoker (previous tests were done logged in the SQL as root) Create 3 new units in the Unee-T Enterprise database

Expected Result:

Actual result:

The payloads:

Unit 1

{"city": "Singapore ", "name": "Loft @ Holland - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "278580", "moreInfo": "Loft@Holland is a freehold condominium development located at 151 Holland Road, Singapore 278580, in district 10. Expected to be completed in 2015, it stands 5 storeys tall and comprises a total of 41 units. Loft@Holland is relatively close to Holland Village MRT Station.\r\n\r\nCondo Facilities at Loft@Holland\r\n\r\nFacilities at Loft@Holland include swimming pool, pool deck and gym.\r\n\r\nCondo Amenities near Loft@Holland\r\n\r\nThe schools in the vicinity are New Town Primary School, Dunman High School and Clementi Town Secondary School.\r\n\r\nFor daily necessities and shopping needs, residents can head down to several shopping centres located within walking distance, such as Holland Village Shopping Mall.\r\n\r\nFor vehicle owners, driving from Loft@Holland to Orchard Road or Central Business District (CBD) takes about 5-10 minutes, via the Central Expressway (CTE). ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "151 Holland Road \n", "unitCreationRequestId": 1046}

Unit 2

{"city": "Singapore", "name": "Seahill - 119", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "126777", "moreInfo": "Seahill is a 99-Year Leasehold Condominium located at 119, West Coast Crescent, 126777 in District 05. It comprises of 338 Units. Seahill is close to Clementi MRT (EW23). It is completed in 2016. Schools close to Seahill include Farrer Park Primary School, Saint Joseph’s Institution Junior and Stamford Primary School.\r\n\r\nFacilities in Seahill\r\n\r\nFacilities at Seahill include Sky Pool, Water Feature with Sunken Planter, 50M Lap Pool, Spa Island, Kids Pool, Dining Pavilion, Spa Pool, Massage Pavilion and Lounge Deck, Aqua Gym, Sea Mist Spa Pool, Function Deck, Meeting Pavilion, Jogging Track, Outdoor Fitness, Children Playground, Viewing Pavilion, Fitness Balcony and Plunge Pool.\r\n\r\nAmenities near Seahill\r\n\r\nResidents at Seahill can get to nearby supermarkets or shopping mall within the area for an array of amenities such as grocery and retail shopping, bank, eateries and more.\r\n\r\nSeahill is near to NTUC Fairprice, Cold Storage and Sheng Siong Supermarket. It is also close to West Coast Plaza.\r\n\r\nVehicle owners can take West Coast Highway, Clementi Road and Ayer Rajah Expressway (AYE) to get to the business hub or shopping district in the city. ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "119 West Coast Crescent  \n", "unitCreationRequestId": 1047}

Unit 3:

{"city": "Singapore", "name": "The Estuary - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": null, "moreInfo": "The Estuary\r\n\r\nA 99-year leasehold development located at 85 Yishun Avenue 1 Singapore 769132, in district 27\r\n\r\n* Completed in 2013, comprise of 608 units\r\n* Located near Khatib MRT Station\r\n* Located close to North point shopping centreand Yishun sports centre\r\n\r\nFacilitiles at The Estuary\r\n\r\n* Facilities include BBQ pit, clubhouse, fitness corner, gym, lap pool, fun pool, multi-purpose hall, steambath, playground and tennis court\r\n\r\nAmenities near The Estuary\r\n\r\n* Yishun Junior College and Chung Cheng High school", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": " \n", "unitCreationRequestId": 1050}

The problems:

kaihendry commented 5 years ago

To summarize:

  1. we have a smallint problem again Error 1062: Duplicate entry '32767' for key 'PRIMARY'
  2. this created incomplete "zombie Units" which causes all sorts of fail, when ideally the "zombie Unit" would be deleted in a transaction when something failed.

How (could) we fix this?

  1. I thought we fixed this before, so I am curious how it came up again. Probably should check/lint for smallint in tables.
  2. Fix the schema and track it's deployed
  3. Improve "unit creation" procedure to handle failure
  4. Remove "zombie Units" or at least figure out what makes the zombies, so we can not use them.
franck-boullier commented 5 years ago

I opened the following issue

franck-boullier commented 5 years ago

More information - New Test:

In the Demo environment: Logged as the SQL user root Create 3 new units in the Unee-T Enterprise database

Expected Result:

Actual result:

The payloads:

Unit 1

{"city": "Singapore ", "name": "Loft @ Holland - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "278580", "moreInfo": "Loft@Holland is a freehold condominium development located at 151 Holland Road, Singapore 278580, in district 10. Expected to be completed in 2015, it stands 5 storeys tall and comprises a total of 41 units. Loft@Holland is relatively close to Holland Village MRT Station.\r\n\r\nCondo Facilities at Loft@Holland\r\n\r\nFacilities at Loft@Holland include swimming pool, pool deck and gym.\r\n\r\nCondo Amenities near Loft@Holland\r\n\r\nThe schools in the vicinity are New Town Primary School, Dunman High School and Clementi Town Secondary School.\r\n\r\nFor daily necessities and shopping needs, residents can head down to several shopping centres located within walking distance, such as Holland Village Shopping Mall.\r\n\r\nFor vehicle owners, driving from Loft@Holland to Orchard Road or Central Business District (CBD) takes about 5-10 minutes, via the Central Expressway (CTE). ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "151 Holland Road \n", "unitCreationRequestId": 1046}

Unit 2

{"city": "Singapore", "name": "Seahill - 119", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "126777", "moreInfo": "Seahill is a 99-Year Leasehold Condominium located at 119, West Coast Crescent, 126777 in District 05. It comprises of 338 Units. Seahill is close to Clementi MRT (EW23). It is completed in 2016. Schools close to Seahill include Farrer Park Primary School, Saint Joseph’s Institution Junior and Stamford Primary School.\r\n\r\nFacilities in Seahill\r\n\r\nFacilities at Seahill include Sky Pool, Water Feature with Sunken Planter, 50M Lap Pool, Spa Island, Kids Pool, Dining Pavilion, Spa Pool, Massage Pavilion and Lounge Deck, Aqua Gym, Sea Mist Spa Pool, Function Deck, Meeting Pavilion, Jogging Track, Outdoor Fitness, Children Playground, Viewing Pavilion, Fitness Balcony and Plunge Pool.\r\n\r\nAmenities near Seahill\r\n\r\nResidents at Seahill can get to nearby supermarkets or shopping mall within the area for an array of amenities such as grocery and retail shopping, bank, eateries and more.\r\n\r\nSeahill is near to NTUC Fairprice, Cold Storage and Sheng Siong Supermarket. It is also close to West Coast Plaza.\r\n\r\nVehicle owners can take West Coast Highway, Clementi Road and Ayer Rajah Expressway (AYE) to get to the business hub or shopping district in the city. ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "119 West Coast Crescent  \n", "unitCreationRequestId": 1047}

Unit 3:

{"city": "Singapore", "name": "The Estuary - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": null, "moreInfo": "The Estuary\r\n\r\nA 99-year leasehold development located at 85 Yishun Avenue 1 Singapore 769132, in district 27\r\n\r\n* Completed in 2013, comprise of 608 units\r\n* Located near Khatib MRT Station\r\n* Located close to North point shopping centreand Yishun sports centre\r\n\r\nFacilitiles at The Estuary\r\n\r\n* Facilities include BBQ pit, clubhouse, fitness corner, gym, lap pool, fun pool, multi-purpose hall, steambath, playground and tennis court\r\n\r\nAmenities near The Estuary\r\n\r\n* Yishun Junior College and Chung Cheng High school", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": " \n", "unitCreationRequestId": 1050}

The problem:

Fixed issue:

franck-boullier commented 5 years ago

Update from @kaihendry in another issue ---> copied there as it belongs here:

We seem to have a failure Error 1213: Deadlock found when trying to get lock; try restarting transaction

This happens when the lambda is trying to call the procedure https://github.com/unee-t/unit

The most logical explanation for this is that the process who is trying to trigger the BZ procedure to create a unit is not patient enough ---> it does not give the previous run enough time to release the lock before it tries to create another unit ---> we see the error message Error 1213: Deadlock found when trying to get lock; try restarting transaction

kaihendry commented 5 years ago

I adjusted the timeout on https://github.com/unee-t/unit in the demo env to 30s. But I didn't see any evidence of a timeout in the logs.

franck-boullier commented 5 years ago

More information - New Test:

After timeout extended by @kaihendry In the Demo environment: Logged as the SQL user root Create 3 new units in the Unee-T Enterprise database

Expected Result:

Actual result:

The payloads:

Unit 1

{"city": "Singapore ", "name": "Loft @ Holland - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "278580", "moreInfo": "Loft@Holland is a freehold condominium development located at 151 Holland Road, Singapore 278580, in district 10. Expected to be completed in 2015, it stands 5 storeys tall and comprises a total of 41 units. Loft@Holland is relatively close to Holland Village MRT Station.\r\n\r\nCondo Facilities at Loft@Holland\r\n\r\nFacilities at Loft@Holland include swimming pool, pool deck and gym.\r\n\r\nCondo Amenities near Loft@Holland\r\n\r\nThe schools in the vicinity are New Town Primary School, Dunman High School and Clementi Town Secondary School.\r\n\r\nFor daily necessities and shopping needs, residents can head down to several shopping centres located within walking distance, such as Holland Village Shopping Mall.\r\n\r\nFor vehicle owners, driving from Loft@Holland to Orchard Road or Central Business District (CBD) takes about 5-10 minutes, via the Central Expressway (CTE). ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "151 Holland Road \n", "unitCreationRequestId": 1046}

Unit 2

{"city": "Singapore", "name": "Seahill - 119", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": "126777", "moreInfo": "Seahill is a 99-Year Leasehold Condominium located at 119, West Coast Crescent, 126777 in District 05. It comprises of 338 Units. Seahill is close to Clementi MRT (EW23). It is completed in 2016. Schools close to Seahill include Farrer Park Primary School, Saint Joseph’s Institution Junior and Stamford Primary School.\r\n\r\nFacilities in Seahill\r\n\r\nFacilities at Seahill include Sky Pool, Water Feature with Sunken Planter, 50M Lap Pool, Spa Island, Kids Pool, Dining Pavilion, Spa Pool, Massage Pavilion and Lounge Deck, Aqua Gym, Sea Mist Spa Pool, Function Deck, Meeting Pavilion, Jogging Track, Outdoor Fitness, Children Playground, Viewing Pavilion, Fitness Balcony and Plunge Pool.\r\n\r\nAmenities near Seahill\r\n\r\nResidents at Seahill can get to nearby supermarkets or shopping mall within the area for an array of amenities such as grocery and retail shopping, bank, eateries and more.\r\n\r\nSeahill is near to NTUC Fairprice, Cold Storage and Sheng Siong Supermarket. It is also close to West Coast Plaza.\r\n\r\nVehicle owners can take West Coast Highway, Clementi Road and Ayer Rajah Expressway (AYE) to get to the business hub or shopping district in the city. ", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": "119 West Coast Crescent  \n", "unitCreationRequestId": 1047}

Unit 3:

{"city": "Singapore", "name": "The Estuary - 1", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "hF4AxDx6r6ue2TgFD", "zipCode": null, "moreInfo": "The Estuary\r\n\r\nA 99-year leasehold development located at 85 Yishun Avenue 1 Singapore 769132, in district 27\r\n\r\n* Completed in 2013, comprise of 608 units\r\n* Located near Khatib MRT Station\r\n* Located close to North point shopping centreand Yishun sports centre\r\n\r\nFacilitiles at The Estuary\r\n\r\n* Facilities include BBQ pit, clubhouse, fitness corner, gym, lap pool, fun pool, multi-purpose hall, steambath, playground and tennis court\r\n\r\nAmenities near The Estuary\r\n\r\n* Yishun Junior College and Chung Cheng High school", "creatorId": "hF4AxDx6r6ue2TgFD", "actionType": "CREATE_UNIT", "streetAddress": " \n", "unitCreationRequestId": 1050}

The problem:

franck-boullier commented 5 years ago

More information:

Looking at the BZ side we can see that Unit 3 exists in the table ut_data_to_create_units

The Test:

Manually running the BZ script to create the unit in BZ (the script is available here: https://github.com/unee-t/unit/blob/master/sql/unit_create_new.sql

# The unit: What is the id of the unit in the table 'ut_data_to_create_units'
    SET @mefe_unit_id = 'o8eywFtpiUG3nDrgR';
#
# Environment: Which environment are you creating the unit in?
#   - 1 is for the DEV/Staging
#   - 2 is for the prod environment
#   - 3 is for the Demo environment
    SET @environment = 3;
#
########################################################################
#
# ALL THE VARIABLES WE NEED HAVE BEEN DEFINED, WE CAN RUN THE SCRIPT 
#
########################################################################

# We have everything, we need. We can call the script now
CALL `unit_create_with_dummy_users`;

Result:

The problem:

We still don't understand why is the Unee-T sub-component https://github.com/unee-t/unit is not able to run the script... @kaihendry I'll need your insights on that as all seems OK on the SQL side AFAICT.

kaihendry commented 5 years ago

IIUC this is due to a deadlock issue in the procedure diagnosed with SHOW ENGINE INNODB STATUS \G & not problem per se in https://github.com/unee-t/lambda2sns or https://github.com/unee-t/unit.

franck-boullier commented 5 years ago

More information:

Based on findings from @kaihendry The object holding the lock is the procedure update_log_count_enabled_units This is a procedure that is called by the trigger update_the_log_of_enabled_units_when_unit_is_created which is activated each time a new unit is added to the products table

The test:

Disable the trigger update_the_log_of_enabled_units_when_unit_is_created

In the Demo environment: Logged as the SQL user root Create 6 new units in the Unee-T Enterprise database

Check if we still have the deadlock issue

Expected Result:

Removing the trigger update_the_log_of_enabled_units_when_unit_is_created in the BZ database fixed the deadlock issue.

This is a workaround that we will use for now until we can find a way to re-enable the trigger update_the_log_of_enabled_units_when_unit_is_created without creating deadlocks.

franck-boullier commented 5 years ago

This doesn't work in the DEV/Staging environment :triumph:

franck-boullier commented 5 years ago

The Test:

Create 6 units in the Unee-T Enterprise DB.

Expected result:

All 6 units are created in the MEFE - FAILED

Actual Result:

Paylod for unit 1:

{"city": "Singapore", "name": "Heritage View - A", "type": "Condominium", "state": null, "country": "Singapore", "ownerId": "YYeAutqzDY3MeqbNC", "zipCode": "138679", "moreInfo": null, "creatorId": "YYeAutqzDY3MeqbNC", "actionType": "CREATE_UNIT", "streetAddress": "8 Dover Rise \n", "unitCreationRequestId": 1048}
franck-boullier commented 5 years ago

Problem is fixed. Source of the issue was that the procedure unit_create_with_dummy_users in the BZ database had been emptied for some reason :scream: ...

Creating a GH issue in the bz DB repo about that.

franck-boullier commented 5 years ago

More tests done in the DEV/Staging uncovered new issues:

The Test:

Create 497 units with the Une-T Enterprise database

Expected result:

Actual result:

---> 422 units were NOT properly created in Unee-T ---> 75 units are created in the BZ database (there seem to be no zombie units)

@kaihendry this looks like another timeout issue again :thinking: