The Elearning Community • Update from 1.4 to 2.3 error on pre_upgrade.sql
Page 1 of 2

Update from 1.4 to 2.3 error on pre_upgrade.sql

Posted: Fri Nov 08, 2019 7:18 pm
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

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

Posted: Fri Nov 08, 2019 7:46 pm
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?

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

Posted: Fri Nov 08, 2019 8:18 pm
by Mikolson
yes, I will try to post the log if possible, in the meantime solutions?

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

Posted: Fri Nov 08, 2019 8:22 pm
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.

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

Posted: Fri Nov 08, 2019 8:30 pm
by Mikolson
ok i'll keep you updated. thanks for now

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

Posted: Mon Nov 11, 2019 11:16 am
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?

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

Posted: Mon Nov 11, 2019 12:31 pm
by alfa24
I would suggest to increase mysql timeout...
https://stackoverflow.com/questions/147 ... through-py

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

Posted: Mon Nov 11, 2019 12:33 pm
by Mikolson
Thanks you, i will try this solution.

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

Posted: Fri Nov 22, 2019 2:50 pm
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?

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

Posted: Fri Nov 22, 2019 3:32 pm
by alfa24
Please provide this data from your MySql server:
SHOW VARIABLES LIKE '%timeout%'
SHOW VARIABLES LIKE '%max_allowed_packet%'

This site uses cookies.

Some of the cookies we use are essential for parts of the site to operate and have already been set. We also use Google Analytics scripts, which all use cookies.
You may delete or block all cookies from this site in your browser options.