Updating to Booking 8 : Booking options deleted and error

[monsieurmonsieur]

Hi,

I have recently updated Booking from 7.8.7.02 and Moodle 4.0.7 to Booking 8.0.55, Wunderbyte Table 1.9.2 and Moodle 4.1.8.

After updating, the booking view page in courses show "No records found", and no existing booking sessions are displayed although they are still in the database.

The following message is displayed :

Warning: mysqli::query(): (HY001/1038): Out of sort memory, consider increasing server sort buffer size in /var/www/cinsight/lib/dml/mysqli_native_moodle_database.php on line 1281

When trying to create a new booking by clicking the "New booking option" link, an error occurs : "error reading database" with this info :

Info de débogage  Out of sort memory, consider increasing server sort buffer size
SELECT *
FROM (
SELECT DISTINCT bo.id, bo.bookingid, bo.text, bo.maxanswers, bo.maxoverbooking, bo.minanswers, bo.bookingopeningtime, bo.bookingclosingtime, bo.courseid, bo.coursestarttime, bo.courseendtime, bo.enrolmentstatus, bo.description, bo.descriptionformat, bo.limitanswers, bo.timemodified, bo.addtocalendar, bo.calendarid, bo.pollurl, bo.groupid, bo.sent, bo.location, bo.institution, bo.address, bo.pollurlteachers, bo.howmanyusers, bo.pollsend, bo.removeafterminutes, bo.notificationtext, bo.notificationtextformat, bo.disablebookingusers, bo.sent2, bo.sentteachers, bo.beforebookedtext, bo.beforecompletedtext, bo.aftercompletedtext, bo.shorturl, bo.duration, bo.parentid, bo.semesterid, bo.dayofweektime, bo.invisible, bo.annotation, bo.identifier, bo.titleprefix, bo.priceformulaadd, bo.priceformulamultiply, bo.priceformulaoff, bo.dayofweek, bo.availability, bo.status, bo.responsiblecontact, bo.credits, bo.sortorder, bo.json , GROUP_CONCAT(bt1.teacherobject SEPARATOR ', ') as teacherobjects , f.filename FROM mdl_booking_options bo LEFT JOIN
(
SELECT bt.optionid, CONCAT_WS('', '{"id":', u.id, ', "firstname":"', u.firstname, '", "lastname":"', u.lastname, '", "name":"', u.lastname, ', ', u.firstname, '"}') as teacherobject
FROM mdl_booking_teachers bt
JOIN mdl_user u
ON bt.userid = u.id
) bt1
ON bt1.optionid = bo.id LEFT JOIN mdl_files f
ON f.itemid=bo.id and f.component=?
AND f.filearea=?
AND f.mimetype LIKE 'image%' GROUP BY bo.id , bo.bookingid , bo.text , bo.maxanswers , bo.maxoverbooking , bo.minanswers , bo.bookingopeningtime , bo.bookingclosingtime , bo.courseid , bo.coursestarttime , bo.courseendtime , bo.enrolmentstatus , bo.description , bo.descriptionformat , bo.limitanswers , bo.timemodified , bo.addtocalendar , bo.calendarid , bo.pollurl , bo.groupid , bo.sent , bo.location , bo.institution , bo.address , bo.pollurlteachers , bo.howmanyusers , bo.pollsend , bo.removeafterminutes , bo.notificationtext , bo.notificationtextformat , bo.disablebookingusers , bo.sent2 , bo.sentteachers , bo.beforebookedtext , bo.beforecompletedtext , bo.aftercompletedtext , bo.shorturl , bo.duration , bo.parentid , bo.semesterid , bo.dayofweektime , bo.invisible , bo.annotation , bo.identifier , bo.titleprefix , bo.priceformulaadd , bo.priceformulamultiply , bo.priceformulaoff , bo.dayofweek , bo.availability , bo.status , bo.responsiblecontact , bo.credits , bo.sortorder , bo.json , f.filename
) s1
WHERE 1=1 AND id = 0
[array (
0 => 'mod_booking',
1 => 'bookingoptionimage',
)]
Error code: dmlreadexception

Trace de la pile 
line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
line 1282 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
line 1679 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
line 329 of /mod/booking/classes/booking_option_settings.php: call to moodle_database->get_record_sql()
line 298 of /mod/booking/classes/booking_option_settings.php: call to mod_booking\booking_option_settings->set_values()
line 266 of /mod/booking/classes/singleton_service.php: call to mod_booking\booking_option_settings->__construct()
line 93 of /mod/booking/classes/teachers_handler.php: call to mod_booking\singleton_service::get_instance_of_booking_option_settings()
line 432 of /mod/booking/classes/form/option_form.php: call to mod_booking\teachers_handler->add_to_mform()
line 214 of /lib/formslib.php: call to mod_booking\form\option_form->definition()
line 158 of /mod/booking/editoptions.php: call to moodleform->__construct()

Tampon de sortie <br /> <b>Warning</b>: mysqli::query(): (HY001/1038): Out of sort memory, consider increasing server sort buffer size in <b>/var/www/cinsight/lib/dml/mysqli_native_moodle_database.php</b> on line <b>1281</b><br />

Are there specific settings or configuration needed with that new version that could cause such errors ?

Thanks in advance and best regards.

[bernhard-wunderbyte]

I'm no expert in MySQL but this is most probably a MYSQL problem. Doing some research, I found the following: https://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size

The highest rated comment there suggests to increase the sort_buffer_size by editing the MySQL config file like this:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
sort_buffer_size = **256000000**
sudo service mysql restart

Please keep in mind that this is only an example. On your server, the MySQL config file might be at a different location. Also you might want to use another editor than nano and there might be another way to restart your MySQL server (e.g. if you use Docker).

I'll close this for now. If the problem persists after increasing the buffer size, please let us know and I'll re-open the ticket.

[monsieurmonsieur]

Thanks for your answer, I will look into it. Best regardsn