search

francoisjacquet / rosariosis

RosarioSIS Student Information System for school management.
https://www.rosariosis.org
GNU General Public License v2.0
505 stars 347 forks source link

MySQL 100% CPU Usage #287

Closed victorjc639 closed 10 months ago

victorjc639 commented 10 months ago

I seem to only be able to add two students every 24 hours. Whenever I try to add a third student, my CPU usage goes up to 100% due to a MySQL process, and I must restart PHP to get Rosario running again. The issue seems to resolve itself after a day, but after I add another 2 students successfully, the application gets stuck loading. I have attached a screenshot of what happens when I stop MySQL during the add student process. My only guess is that some SQL command is not executing properly the third time until it somehow resets after 24 hours. Screenshot 2023-10-23 123010

francoisjacquet commented 10 months ago

Hello @victorjc639

Please specify RosarioSIS, MySQL and OS version, plus exact steps to reproduce the issue ("add a student" is too vague, please at least provide some screenshots before saving).

victorjc639 commented 10 months ago

RosarioSIS: [11.2.3 (upgraded from 11.2.1 to try to fix issue)] MySQL: [8.0.34-0ubuntu0.22.04.1 - (Ubuntu)] OS: [Description: Ubuntu 22.04.3 LTS Release: 22.04 Codename: jammy] PHP: [8.2 (changed from 7.4 to try to fix issue)]

Screenshot of MySQL usage before adding a student: sql-before

Screenshot while attempting to add student and click "Save" (notice the loading icon on the top right that loops forever until I restart PHP and MySQL): test-add-student

Screenshot of MySQL usage while that process is running: sql-after

Steps to reproduce:

  1. Click on "Students" in the menu
  2. Click on "Add Students" in the dropdown
  3. Input student first name, last name, username, and password.
  4. Click on "Save" to add student
  5. Repeat two times until issue comes up on the third time.

(EDIT) Notice: I can add two more students after I wait 24 hours after adding the first two. This might be a clue as to what is happening.

francoisjacquet commented 10 months ago

Thank you for the details.

I was able to reproduce the error with MySQL 8.1 on Docker.

This is a cache issue: https://stackoverflow.com/questions/51283195/wrong-auto-increment-value-on-select

This causes an infinite loop.

francoisjacquet commented 10 months ago

Thank you for helping make RosarioSIS better.

Fix https://gitlab.com/francoisjacquet/rosariosis/-/commit/d742e2e85a5b6d10fc81410a7838f43ca1b66330

Will come in release v11.2.4