Update from 1.4 to 2.3 error on pre_upgrade.sql

Install and configure FormaLMS and DoceboCE
Post Reply
Mikolson
Newbie
Posts: 22
Joined: Thu May 07, 2015 6:44 pm
Version: forma.lms 1.4.2

Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by Mikolson » Fri Nov 08, 2019 7:18 pm

Hello to all,
are grappling in the update from version 1.4.3 to 2.3 without success, in the passage where it starts to update the DB after a while an error appears in the pre_upgrade.sql script loses the connection with the server giving a repeated series of errors where I remember this phrase "MySQL .... gone away" or something like that interrupting everything.

Aruba PHPH 5.5 server and the Dabase is about 700MB

any help?
thanks

alfa24
FormaLms Master
Posts: 490
Joined: Fri Nov 24, 2017 8:45 am
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by alfa24 » Fri Nov 08, 2019 7:46 pm

There is a heavy query somewhere during the upgrade process that seems to be blamed of mysql crash.
Difficult to say which is the responsible query... I would put a slow query logger to find it out.
Mysql "goes away" if it tries to allocate more memory than available or if MySQL process goes over 100% of CPU usage. Are you running Forma on a fully managed server with root privileges?
I'm Jasmines, the One. If you need, you can contact me.

Mikolson
Newbie
Posts: 22
Joined: Thu May 07, 2015 6:44 pm
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by Mikolson » Fri Nov 08, 2019 8:18 pm

yes, I will try to post the log if possible, in the meantime solutions?

alfa24
FormaLms Master
Posts: 490
Joined: Fri Nov 24, 2017 8:45 am
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by alfa24 » Fri Nov 08, 2019 8:22 pm

The solution may come after the investigation... If the process hangs systematically at the same point, you have to found out what is causing it.
I bet on the query (I have also an idea of what query to search for), but I'm afraid you won't fix the upgrader or find a workaround within the code. The only hope is it would work with a tune of mysql or the provisioning of the server.
I'm Jasmines, the One. If you need, you can contact me.

Mikolson
Newbie
Posts: 22
Joined: Thu May 07, 2015 6:44 pm
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by Mikolson » Fri Nov 08, 2019 8:30 pm

ok i'll keep you updated. thanks for now

Mikolson
Newbie
Posts: 22
Joined: Thu May 07, 2015 6:44 pm
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by Mikolson » Mon Nov 11, 2019 11:16 am

Abbiamo individuato con ogni probabilità la causa dell'errore che abbiamo in fase di aggiornamento della piattaforma dalla versione 1.4 alla 2.3.

L'errore riguarda l'aggiornamento del database, in particolare nella fase di pre-update e riporta, tra le altre cose, la frase "MySQL server has gone away", indicando che il server va in timeout dopo l'esecuzione di un passo del processo di aggiornamento.
Il database è su server gestiti da Aruba e non possiamo intervenire sui suoi parametri di base.

Il problema si presenta durante l'esecuzione dello script: upgrade\data\upg_data\200000_db.sql nelle righe dalla 1115 alla 1175:

-- ForEach course add a view_all role (if view exists)
INSERT ignore INTO core_role_members
select ra.idst, g.idst idstmember
from
learning_course c
join (select 1 lvl union select 2 lvl union select 3 lvl union select 4 lvl union select 5 lvl union select 6 lvl union select 7 lvl) core_lvl
join core_group g on g.groupid like concat('/lms/course/',idcourse,'/subscribed/',lvl)
join core_role r on r.roleid like concat('/lms/course/private/',idcourse,'/statistic/view')
join core_role_members rm on r.idst = rm.idst and g.idst = rm.idstMember
join core_role ra on ra.roleid like concat('/lms/course/private/',idcourse,'/statistic/view_all');

....


La query di join, se eseguita da PhpMyAdmin, va in timeout e blocca tutto il sistema fino a quando, attraverso la vista "Processi" si abbatte il processo che la gestisce.

Nel nostro database, le tabelle coinvolte nel join sono piuttosto corpose e questo potrebbe essere la causa del problema:

select count(1) from learning_course -> 1507 records
select count(1) from core_group -> 12078 records
select count(1) from core_role -> 68000 records
select count(1) from core_role_members -> 315340 records

Facciamo presente che esistono anche altre tabelle che hanno raggiunto dimensioni ragguardevoli e non sappiamo se il processo di update, nei passi successivi, possa bloccarsi anche su queste:
Image


Come possiamo risolvere la questione e aggiornare la nostra piattaforma?
Last edited by Mikolson on Mon Nov 11, 2019 2:48 pm, edited 1 time in total.

alfa24
FormaLms Master
Posts: 490
Joined: Fri Nov 24, 2017 8:45 am
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by alfa24 » Mon Nov 11, 2019 12:31 pm

I would suggest to increase mysql timeout...
https://stackoverflow.com/questions/147 ... through-py
I'm Jasmines, the One. If you need, you can contact me.

Mikolson
Newbie
Posts: 22
Joined: Thu May 07, 2015 6:44 pm
Version: forma.lms 1.4.2

Re: Update from 1.4 to 2.3 error on pre_upgrade.sql

Post by Mikolson » Mon Nov 11, 2019 12:33 pm

Thanks you, i will try this solution.

Post Reply