Update from 1.4 to 2.3 error on pre_upgrade.sql

Install and configure FormaLMS and DoceboCE
Mikolson
FormaLms User
Posts: 26
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 »

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
Senior Boarder
Posts: 1985
Joined: Fri Nov 24, 2017 8:45 am

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

Post by alfa24 »

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?
Per supporto GRATUITO contattatemi in privato qui
Mikolson
FormaLms User
Posts: 26
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 »

yes, I will try to post the log if possible, in the meantime solutions?
alfa24
Senior Boarder
Posts: 1985
Joined: Fri Nov 24, 2017 8:45 am

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

Post by alfa24 »

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.
Per supporto GRATUITO contattatemi in privato qui
Mikolson
FormaLms User
Posts: 26
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 »

ok i'll keep you updated. thanks for now
Mikolson
FormaLms User
Posts: 26
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 »

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
Senior Boarder
Posts: 1985
Joined: Fri Nov 24, 2017 8:45 am

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

Post by alfa24 »

I would suggest to increase mysql timeout...
https://stackoverflow.com/questions/147 ... through-py
Per supporto GRATUITO contattatemi in privato qui
Mikolson
FormaLms User
Posts: 26
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 »

Thanks you, i will try this solution.
Mikolson
FormaLms User
Posts: 26
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 »

Aggiornamento !!
abbiamo fatto spostare da Aruba il sito su un nuovo server abbiamo rifatto la procedura ma niente solito errore
ecco gli screen degli errori:
https://campus.istitutovolta.eu/files/e ... amento.txt
Image
Image
Image

soluzioni?
alfa24
Senior Boarder
Posts: 1985
Joined: Fri Nov 24, 2017 8:45 am

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

Post by alfa24 »

Please provide this data from your MySql server:
SHOW VARIABLES LIKE '%timeout%'
SHOW VARIABLES LIKE '%max_allowed_packet%'
Per supporto GRATUITO contattatemi in privato qui
Post Reply