06 March 2020, 21:41

Hi Robert

I updated mahara 2 days ago (19.04.4) and I still have the CRON problem.

[05-Mar-2020 20:31:15 Europe/Paris] [WAR] fb (lib/errors.php:858) Failed to get a recordset: mysqli error: [2006: MySQL server has gone away] in EXECUTE("DELETE FROM "usr_session" WHERE mtime < '2020-03-04 20:25:02'")Command was: DELETE FROM "usr_session" WHERE mtime < ? and values was (0:2020-03-0420:25:02)

When I launch the command : SELECT COUNT(*) FROM "usr_session" WHERE mtime < '2020-03-04 20:25:02';

I have this syntaxe error : #1064 - Erreur de syntaxe près de '"usr_session" WHERE mtime < '2020-03-04 20:25:02' LIMIT 0, 25' à la ligne 1

Same error with DELETE FROM "usr_session" WHERE mtime < '2020-03-04 20:25:02';

To make it work i have to change the line : SELECT COUNT(*) FROM `usr_session` WHERE mtime < '2020-03-04 20:25:02'

The result is 1.

DELETE FROM `usr_session` WHERE mtime < '2020-03-04 20:25:02'; works fine too.

How can i avoid this error ?

Cheers
Anthony.

10 March 2020, 8:24

Hi Anthony

When using Mysql via Mahara it sets the following SQL_MODES to PIPES_AS_CONCAT, ANSI_QUOTES, and IGNORE_SPACE

So Mysql works in Postgres compatibility mode and so Mahara should be able to use " instead of ` for the table names.

So another thing to try in Mysql itself is to:

1) Connect to Mysql with the values in Mahara config.php file

2) Run: SET SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';

3) Run:  SELECT @@SESSION.sql_mode;

to see that the values can be set

4) Run: SELECT COUNT(*) FROM "usr_session" WHERE mtime < '2020-03-04 20:25:02';

If that fails then there is a problem with setting SQL_MODE and your Mysql - let me know what version of Mysql you are using

Cheers

Robert

13 March 2020, 1:16

Hi Robert

Here is the answer from my webhost :

In a shared hosting context, SQL_MODE is empty for a good functioning of the sites present on the server.

It will therefore not be possible to make any modification to this variable.

The MariaDB version is currently 10.3 on the server.

Cheers, Anthony.

09 May 2020, 23:53

Hello

I would like to create an sql query that deletes records from the config table starting with _cron_lock_ every hour.

My webhost tells me that I can run the query via a CRON task.

Can you tell me how to write such a SQL query?

Cheers, Anthony.

02 June 2020, 14:38

Hi Anthony,

You would only want to clear the _cron_lock_ that is old as the the _cron_lock_ sql lines should be added and removed on every cron run - so you'd only want to clear locks that are old.

In the lib/cron.php file there is the function cron_lock() that cleans up old _cron_lock_ every 24 hours

You could change this to every hour by updating the following lines

// If it's been going for more than 24 hours, remove the lock
if ($started && $started < $start - 60*60*24) {

to

// If it's been going for more than 1 hour, remove the lock
if ($started && $started < $start - 60*60*1) {

That would be the easiest way

But if you are needing t clear long running cron locks it might be worth finding out why they keep staying locked. I suspect the lock is throwing an error

Cheers

Robert