SQL Errors When Running Daily Cronjob

pradeep sharma

pradeep sharma

Active Member
Hi @twisted1919

We are getting the following error when running the daily cron job. Our DATABASE is on a very high-performance Server with plenty of RAM and tuned up for high load and scalability.

Code: 
[2024-05-16 00:54:14] - Loading 50 campaigns to delete their click logs...
[2024-05-16 00:54:14] - No campaign found for deleting its click logs!
[2024-05-16 00:54:14] - Done!
[2024-05-16 00:54:14] - Deleting expired sessions...
[2024-05-16 00:54:24] - 848: CDbCommand failed to execute the SQL statement: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
[2024-05-16 00:54:24] - Checking license after 3.46555 seconds...
[2024-05-16 00:54:29] - Response status code: 200 and body: {"status":"success"}

Can you suggest, what could be the root cause of this error?


Regards

Pradeep
 
Most likely it is too much data in that table, you could truncate it from mysql directly to be sure data gets removed.
As you noted, this runs each day, at midnight, in the daily cron. However, you can set a new cron, to run each 6 hours or so, to delete data from the sessions table.
That cron job is called "delete-expired-sessions"
 
twisted1919 said:
Most likely it is too much data in that table, you could truncate it from mysql directly to be sure data gets removed.
As you noted, this runs each day, at midnight, in the daily cron. However, you can set a new cron, to run each 6 hours or so, to delete data from the sessions table.
That cron job is called "delete-expired-sessions"
Click to expand...

I investigated and optimized the MYSQL

by
[mysqld]
innodb_lock_wait_timeout=120 ##default was 50 Sec
innodb_print_all_deadlocks=1 ## this will print all deadlock in mysql error log for further investigation


1. But i found that there is no Index on expire Coloum ?? is this OK as i am not aware how the delete queries are going in mailwizz if these are based on id then its fine but it if it has expire in where clause then we need to add index..

2. After this i run the corn job but still this didn't solve the problem, then i truncated the table.

3. my session tables are getting almost 20 new raws every second and the application is not used by much as we are still in development, the application is monitored by few systems..

4. all these raws have NO data Screen shot attached!! This is something wired!!

1715867252885.png


Please investigate as i m suspecious about this

Regards

Pradeep
 
twisted1919 said:
Most likely it is too much data in that table, you could truncate it from mysql directly to be sure data gets removed.
As you noted, this runs each day, at midnight, in the daily cron. However, you can set a new cron, to run each 6 hours or so, to delete data from the sessions table.
That cron job is called "delete-expired-sessions"
Click to expand...

How can we know what are other commands that is the part of daily cron!! it will help us to decide when to run and how often!!
 
pradeep sharma said:
the application is monitored by few systems..
Click to expand...
This most likely creates all the sessions.

pradeep sharma said:
How can we know what are other commands that is the part of daily cron!! it will help us to decide when to run and how often!!
Click to expand...
You can check the daily cron class, and look for things like:
PHP: 
$argv = [
            $_SERVER['argv'][0],
            'delete-mutexes',
        ];

        foreach ($_SERVER['argv'] as $arg) {
            if (preg_match('/--(verbose|stdout_format)=/i', $arg)) {
                $argv[] = $arg;
            }
        }

        try {
            $this->getCommandRunnerClone()->run($argv);
        } catch (Exception $e) {
            $this->stdout(__LINE__ . ': ' . $e->getMessage());
            Yii::log($e->getMessage(), CLogger::LEVEL_ERROR);
        }

The above calls the "delete-mutexes" command, from daily. But you can also call delete-mutexes on your own at your own schedule.
 
twisted1919 said:
This most likely creates all the sessions.
Click to expand...
is this ok to get the No data in session record as it inserted even if we are monitoring just the customer login page without a login access supplied

i.e https://app.domain.com/customer/guest/index

I want to get this investigated for any misconfiguration.

We are running application distributed behind HA proxy and HAProxy is behind the Cloudflare Proxy. all instances of an application can be accessed by IP as well to make sure it follows the tracking domain requirement.

We have enabled proxy option in the application too.

Regards

Pradeep
 
pradeep sharma said:
is this oK?
Click to expand...
You can add an index, sure, make sure you give it a custom name so it doesn't conflict with what we add, if we ever add one.

pradeep sharma said:
is this ok to get the No data in session record
Click to expand...
Sure, a session just connects a visitor with your site, it doesn't have to contain any data until we put some data in it, in various cases.

pradeep sharma said:
I also want to know is there any setting to control session time or what is the session time duration for an expired session?
Click to expand...
Sure, feel free to do this. If you open apps/frontend/config/main.php you will see:
PHP: 
'session' => [
            'class'                  => 'system.web.CDbHttpSession',
            'connectionID'           => 'db',
            'sessionName'            => 'mwsid',
            'timeout'                => 7200,
            'sessionTableName'       => '{{session}}',
            'autoCreateSessionTable' => false,
            'cookieParams'           => [
                'httponly' => true,
            ],
        ],
which means you can create your own main-custom.php file and overwrite the session component:
PHP: 
<?php declare(strict_types=1);
if (!defined('MW_PATH')) {
    exit('No direct script access allowed');
}

return [
    'components' => [
       
        'session' => [
            'class'                  => 'system.web.CDbHttpSession',
            'connectionID'           => 'db',
            'sessionName'            => 'mwsid',
            'timeout'                => 7200,
            'sessionTableName'       => '{{session}}',
            'autoCreateSessionTable' => false,
            'cookieParams'           => [
                'httponly' => true,
            ],
        ],
    ],
];
You can find docs related to session at https://www.yiiframework.com/doc/api/1.1/CDbHttpSession to see what's possible and what not.
Also, this change only applies for frontend. For customer/backend you need to do same change, but generally, you should not need to change session configuration there as those areas don't get much traffic.
 
@twisted1919 ,

I think if the session duration is for 7200 Seconds that's fair enough . The Challenge is when delete-expired-sessions runs in 6 Hrs
So if you have 20 Records inserted per second so it will delete records in 1 Go so approx 432,000 Entries need to get deleted in single sQL statement. its a BIG Number for DELETE SQL Statement.

I ll prefer to run delete session in every 10 minute so it will delete approx 12k records.

But i can see you have coded other delete jobs in a loop of baches on 10K , it will be good to implement this in this table as well so that every one else also get rid of this problem.


does tracking clicks create a session ?? if so then deleting batch job for the session is must.

Regards

Pradeep
 
You can have the cronjob set to run when you feel it is the right time, and you can adjust things for your use case. Instead of 7200 you can use a lower session ttl and the run the cron each hour, if that's something you need, again, it is really up to you and your use case.
pradeep sharma said:
does tracking clicks create a session
Click to expand...
Yes,everything that hits the site creates a session, that's how things work.
 
You must log in or register to reply here.
Back
Top