13 August 2015, 8:57

when I was doing performance testing on Mahara, I had an SQL error in the log file

[23-Jun-2015 10:08:58 America / Montreal] [WAR] 09 (lib / dml.php 1046) Failed to get a recordset: mysqli error: [1213: Deadlock found When trying to get lock; try restarting transaction] in adodb_throw (INSERT INTO "mhr_notification_internal_activity" ("type", "usr", "ctime", "subject", "message", "url", "urltext", "read") VALUES ('11' '1409', '2015-06-23 10:08:58', 'New comment on Kanban', 'Commentary testuser0267 load test.', 'view / view.php? id = 5503 & showcomment = 32376', ' Kanban ', 0))

the problem is detected at the trigger `mhr_update_unread_insert_trigger`

*** (2) TRANSACTION:
272937978 TRANSACTION, ON 0 sec starting index read
mysql tables in use 4 locked 4
15 struct lock (s), heap size 2936 7 row lock (s), undo log entries 5
MySQL thread id 8646768, OS thread handle 0x7f1a12fc9700, query id 222182586 servername mahara updating
UPDATE "mhr_usr" SET unread = unread + 1 WHERE id = NEW.usr
*** (2) HOLDS THE LOCK (S):
RECORD LOCKS space 74663 page id No. 34 n bits 136 index "PRIMARY" of table "ppmahara." "Mhr_usr" trx id 272937978 lock mode S locks but not rec gap
Record lock, heap No. 34 PHYSICAL RECORD: n_fields 35; compact size; info bits 0

Does someone has already had this problem?
Do do you have a solution or suggestion for this?

Environnmnt:

MySQL 5.5
Mahara 1.10


Thank you

13 August 2015, 9:36

Were you running these tests on a running instance with other users in the system?

Be good to share a little more on the nature of the tests if you can and what the symptom is beyond what is logged

cheers

14 August 2015, 1:37

it is a performance test JMeter, with 100 system users, to be more specific, there is a test case where users post a comment on a page. as a result of this, a notification will be inserted into the table "mhr_notification_internal_activity" to notify the owner of the page, this insertion will trigger "mhr_update_unread_insert_trigger", so the situation is as follows:

 Severals triggers want to update the same row  on the table "mh_usr" at the same time. This causes the deadlock on the trigger, and if the trigger fails, the insert fails.

 The architecture we use is a load balancer in front of two web servers in PreProd environnemnt.
Let me know if you want more détails.

12 September 2015, 2:27

Hi,

I'm trying to run some JMeter test on our Mahara installation.

We have trouble extracting parameters like the page or collection id to feed them into subsequent requests.

I think I manage to catch some redirects and ajax calls without being sure whether I've missed any.

I'm interested in how you did your test plan and what you did test.

Could you give us more information about how you created your test plan.

Thanks

Sam