chamilo-lms
chamilo-lms copied to clipboard
chamilo
c_lp_item_view column total_time INT not enough...?
Describe MySQL error 1264: Out of range value for column 'total_time' at row 407;
To Reproduce
Upgrade to Chamilo 1.11.10, query:
ALTER TABLE c_lp_item_view CHANGE lp_item_id lp_item_id INT NOT NULL, CHANGE lp_view_id lp_view_id INT NOT NULL, CHANGE view_count view_count INT NOT NULL, CHANGE start_time start_time INT NOT NULL, CHANGE total_time total_time INT NOT NULL, CHANGE score score DOUBLE PRECISION NOT NULL, CHANGE status status VARCHAR(32) DEFAULT 'not attempted' NOT NULL, CHANGE max_score max_score VARCHAR(8) DEFAULT NULL;
**Server
- OS: Debian Buster
- Version of Chamilo: 1.11.10
- Version of PHP: 7.3
Additional context What is stored in c_lp_item_view.total_time? There are several entries with value "4294967295" which clearly are not INT, but even setting to BIGINT unsigned doesn't change a thing...
Time in LP is bugged for a long time. Maybe it will be fixed on a next release.
The funny thing is, Chamilo is seeing this itself, and in the logfile:
fixAbusiveTime: Total time is too big: 5641 replaced with: 300, referer: https://www.domain.tld/main/newscorm/lp_controller.php?...
@jmontoyaa could you give us a bit of information regarding why such large times are registered and if this could be avoided with the extended tracking system of track_e_access_complete code?
This is fixed with the track_e_access_complete code, the problem happens when a portal have big session_lifetime values. Then the calculated value is something very big.
OK, so 2 things here:
- there can be huge values in total_time because not all exits from the learning path are registered correctly (this is a browser stuff and comes down to issues between JavaScript and the HTTP protocol). In this case, the total_time is "continued" when a user comes back and takes the following action, at which point Chamilo looks for when the previous action was registered and then "completes" the hole by setting a bit total_time
- since after 1.11.10, @nosolored has developed a new tracking mechanism, which requires database changes (which is why this is not upstream yet) and registers much more actions by the user. This enables this new system to be more precise in the time tracking. However, enabling this requires a series of technical changes (which you can find below and in the main/install/configuration.dist.php of 1.11.12 to be published soon) but might have a considerable effect on the portal efficiency, because it registers many more events. This is a bit like an old feature in D0keos that we had disabled, whereby it registered the browser user agent in a table track_c_browser (some of you with very old-lived systems might still see this table in their databases). So... something that works but that should be watched over, in particular on high-usage portals.
The explanations for the changes required:
// Add a minimum time limit to be in the learning path
// in order to get the last item completed
// Requires a DB change:
// ALTER TABLE c_lp ADD accumulate_work_time INT NOT NULL;
// CREATE TABLE track_e_access_complete (id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, date_reg datetime NOT NULL, tool varchar(255) NOT NULL, tool_id int(11) NOT NULL, tool_id_detail int(11) NOT NULL, action varchar(255) NOT NULL, action_details varchar(255) NOT NULL, current_id int(11) NOT NULL, ip_user varchar(255) NOT NULL, user_agent varchar(255) NOT NULL, session_id int(11) NOT NULL, c_id int(11) NOT NULL, ch_sid varchar(255) NOT NULL, login_as int(11) NOT NULL, info longtext NOT NULL, url text NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=13989 DEFAULT CHARSET=utf8;
// CREATE INDEX user_course_session ON track_e_access_complete (user_id, c_id, session_id);
// Add course checkbox extra field "new_tracking_system"
// Add session checkbox extra field "new_tracking_system"
// Only applied for courses/sessions with extra field "new_tracking_system" to "1"
//$_configuration['lp_minimum_time'] = false;
The last line is not mandatory. This is to add a feature requiring a minimum usage of some time before the learning path can be considered fulfilled at 100% (regulatory stuff).
I think that, with that, you have an alternative to the total_time issue, and I can unlink this issue from 1.11.12, for which we are not able to provide a solution (because of the impossibility for us to modify the database by default between minor versions).
Please note that the tracking system above is not compatible with the current tracking system, so any tracking already registered in a learning path will not be availabe if enabling the new one. To avoid this, you can simply copy the learning path and enable the new tracking system on the copy, then make the original invisible. This will, however, cause time issues for learners that have already partially progressed through the learning path.
Changes required are added in chamilo 2.0 and migration too.
It is fixed and added for migration in this commit https://github.com/chamilo/chamilo-lms/commit/c83bf1fcd15612cb0af3b2c11ef01b1d23eb0aa3#diff-94c336124f426795b0818d7a563a4be9d697ea52d40b7e30229a8d7b4e80a799 file Version20180904175500.php
