I have written Glue job using pyspark on AWS UI. The job extracts column names and values from json and writes to MySQL tables. Till yesterday, everything was working fine. Now I am getting error on AWS Glue console which says:
An error occurred while calling o984.pyWriteDynamicFrame. Job aborted due to stage failure: Task 71 in stage 371.0 failed 4 times, most recent failure: Lost task 71.3 in stage 371.0 (TID 25999, ip-10-0-1-5.eu-west-1.compute.internal, executor 7): UnknownReason
In cloudwatch logs, I could see: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
When I ran command on MySQL RDS instance:
**show engine innodb status;**
Output:
LATEST DETECTED DEADLOCK
------------------------
2020-08-11 10:17:57 0x2aca59649700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION
*** TRANSACTION:
TRANSACTION 71219, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 75
MySQL thread id 1771, OS thread handle 47048571524864, query id 38693 10.0.1.175 admin update
INSERT INTO etl_adp_engine_audit_items (`objectid`,`formula`,`date_end`,`decline_reasonid`,`object_name`,`audit_levelid`,`date_start`,`object_no`,`is_exit`,`outcomeid`,`parent_objectid`,`resultid`,`object_levelid`,`application_id`,`object_type`,`etl_adp_audit_id`,`id`) VALUES ('ab072fbc-88ce-4516-b89a-e44a5bb1f19d','ADP_Engine_Logic_Action_Variables.CreateVariableInteger(Convert.ToString(Functions.ParseEquation(Me, \'SC01\')),System.Int32.Parse(Functions.ParseEquation(Me, \'264\')))','2019-03-11 12:44:49.443000',NULL,'5.001 - Create Variable SC01',1,'2019-03-11 12:44:49.443000',253,0,1,'026f2cec-253f-4ac9-a4b9-3195de2a6a11',NULL,2,'1552308273','Action',20317,3365)
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 443 page no 10 n bits 240 index adp_audit_items_index of table `culoansportaldb`.`etl_adp_engine_audit_items` trx id 71219 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 43 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 27; hex 352e3031202d2048617665457870657269616e203d202759455327; asc 5.01 - HaveExperian = 'YES';;
1: len 4; hex 80000108; asc ;;
2: len 4; hex 52756c65; asc Rule;;
3: len 8; hex 8000000000100ea6; asc ;;
4: len 8; hex 800000000000965b; asc [;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 71241
Purge done for trx's n:o < 71238 undo n:o < 0 state: running but idle
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328521077494880, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328521077494016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328521077493152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328521077492288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328521077491424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
Could anyone tell me how can we fix the problem in Glue or the problem has to be resolved on MySQL?
I have explored AWS Glue, There is nothing like auto commit or transaction rollback as of now supported.
Any help would be appreciated!!
