MariaDB slow query log filled up with many SELECT GET_LOCK('....', 60);

[s681562]

I use Zebra_Session under MariaDB 10.4.26, PHP 8.1. If I don't always interact with my homepage, I usually get logged out. But sometimes the session is timed out. The homepage hangs for 60 seconds and the server publishes the result after 60 seconds. A new entry appears in MariaDB's slow query log. Look here:

// Time: 240611 15:00:37 // User@Host: root[root] @ localhost [] // Thread_id: 25983 Schema: mrs QC_hit: No // Query_time: 6.951232 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 // Rows_affected: 0 Bytes_sent: 121 SET timestamp=1718085637; SELECT GET_LOCK('session_20b1f3ca866d17d17ad7a7fa9884824fbd06c1c0', '60'); // Time: 240613 10:34:18 // User@Host: root[root] @ localhost [] // Thread_id: 735266 Schema: mrs QC_hit: No // Query_time: 5.600158 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 // Rows_affected: 0 Bytes_sent: 121 SET timestamp=1718242458; SELECT GET_LOCK('session_8b134d370928d458f33f5af6230b3260e2231c15', '60'); // Time: 240613 13:13:24 // User@Host: root[root] @ localhost [] // Thread_id: 737402 Schema: mrs QC_hit: No // Query_time: 60.000719 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 // Rows_affected: 0 Bytes_sent: 121 SET timestamp=1718252004; SELECT GET_LOCK('session_8b134d370928d458f33f5af6230b3260e2231c15', '60'); // Time: 240613 13:15:43 // User@Host: root[root] @ localhost [] // Thread_id: 737448 Schema: mrs QC_hit: No // Query_time: 60.000242 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 // Rows_affected: 0 Bytes_sent: 121 SET timestamp=1718252143; SELECT GET_LOCK('session_8b134d370928d458f33f5af6230b3260e2231c15', '60');

Only with an explicit click on Logout on my homepage (after another 60 seconds of waiting) the previous Zebra_Session is released. Now the homepage has been responding normally for some time without timeout and 60 seconds of waiting. Hope there will be a solution.

[stefangabos]

Are you running any actions that take a LONG time from within the browser?

[s681562]

There are no javascript long time actions on client-side and no long time PHP script actions on backend-side. All database request are done in under one second.

But in case I am not looking at the homepage on browser tab for maybe 3 or 4 hours, than either I am guided to logout page or I am experienced an long waiting time of 60 seconds without forwarding to logging out screen. It looks like for example, the session with name 'session_8b134d370928d458f33f5af6230b3260e2231c15' is, for example, still in use and blocking all other processes. Therefore the homepage is responsive after 60 seconds Zebra_Session timeout time again. During this not-logout time I can access all homepage sides but always waiting for 60 seconds Zebra_Session timeout.

Maybe I should implement a javascript counter and call via ajax the Zebra_Session for release and set up new Zebra_Session.

I guess Zebra_Session can not handle propper homepage timeout, if user is long time inactive. I saw that long time ago a user had same problem.

Any ideas?

[stefangabos]

Let's try something.

If you are using the latest version, search for this method _manage_flash_data, and right before the closing braces add this session_write_close();

So basically the method will look like this

public function _manage_flash_data() {

    // if there is flash data to be handled
    if (!empty($this->flash_data)) {

        // iterate through all the entries
        foreach ($this->flash_data as $variable => $counter) {

            // increment counter representing server requests
            $this->flash_data[$variable]++;

            // if this is not the first server request
            if ($this->flash_data[$variable] > 1) {

                // unset the session variable
                unset($_SESSION[$variable]);

                // stop tracking
                unset($this->flash_data[$variable]);

            }

        }

        // if there is any flash data left to be handled
        if (!empty($this->flash_data)) {

            // store data in a temporary session variable
            $_SESSION[$this->flash_data_var] = serialize($this->flash_data);

        }

    }

    // WE ADDED THIS HERE
    session_write_close();

}

See if this improves things. What I think happens is that your PHP that generates the pages somehow dies before having a chance to release the lock. That line we added forces a session write / lock release whenever the PHP stops (for any reason) because the library uses register_shutdown_function to call this method when script execution ends

Let me know

[s681562]

I give it a try. Thank you.

[s681562]

We can close it. This hint maybe solved my problem. Previously, the message “SELECT GET_LOCK” was not again rewritten in the slow query log.

[stefangabos]

Thanks for the feedback! I'll add this path to the library then