I don't get a lot of traffic on my installation, mainly because it's more for logging.
I tried to invite someone to use the platform and when they create a password, I get DB Error #1205

[INSERT INTO `ost_session` SET `session_id` = '3098kvn2irou0nrl23p7h9j9sc', `session_data` = 'csrf|N;', `session_expire` = (NOW() + INTERVAL 86400 SECOND), `user_ip` = '192.168.1.1']

Lock wait timeout exceeded; try restarting transaction<br />
<br />
---- Backtrace ----<br /> 
#0 (root)/include/mysqli.php(201): osTicket->logDBError()<br />
#1 (root)/include/class.orm.php(3466): db_query()<br />
#2 (root)/include/class.orm.php(658): MySqlExecutor->execute()<br />
#3 (root)/include/class.ostsession.php(227): VerySimpleModel->save()<br />
#4 (root)/include/class.ostsession.php(158): DbSessionBackend->update()<br />
#5 [internal function]: SessionBackend->write()<br />
#6 [internal function]: session_write_close()<br />
#7 {main}

The server sits behind HA-Proxy, which handles certs. So user_ip will always = 192.168.1.1 (the gateway and HAProxy server)
I tried setting innodb_wait_timeout = 600 in /etc/mysql/mariadb.cnf and my.cnf, didn't seem to do anything. In fact, before I restarted mariadb, I got and error about the setting. I restarted anyway and it started without an issue. I removed the setting and restarted before posting this message.

Also note, I tried creating a dummy user for testing, all successful until 'set password' page. I let the the page sit for a good long while before I hit refresh. All the while I was receiving this error over and over again (around 60 times).

OSTicket v1.15.1
OSTicket-1.15.1-Awesome-101
Debian 10
Apache 2.4.38
MySQL (MariaDB) 10.3.27
PHP 7.4.13

cgi.fix_pathinfo -- this is set to '1' in /etc/php/7.4/apache2/php.ini, however OSTicket still shows that it is not set.

@maxtim

Seems like a query is exceeding the lock wait timeout set in MariaDB. In all my years I've never seen this. Seems to be environment related. I'd contact your hosting provider/DBA for further assistance.

Cheers.

    I successfully edited /etc/mysql/mariadb.cnf to inclue the lines:

    [mysqld]
    innodb_lock_wait_timeout = 120

    while this does work to stop the errors showing up on the logs, the problem persists wherein that the user is stuck watching the page load after setting a password.

    What I'm currently doing to test this is to sign-in as a user -> profile -> and then attempt to change the password. The page hangs seemingly indefinitely (I've never waited to see if it ever finishes as I don't think a user would wait either).
    Then I can click 'cancel' or refresh the page and the password has stuck. But the page hanging would most certainly be an issue if I try to invite any other user.

    What are some steps I can take to remedy this? Bear in mind that I do have full access to the server, database, conf files, everything.

    Another note* I have tried adding other users in the past and other agents. No one has tried to use my platform, and I never really investigated why since it's not that important for my operation here. But I see now why no one has stuck around. It's worth noting that I have migrated this from a physical server to a VM. But this problem has persisted since my first installation about two years ago.

    Out of curiosity what happens if you try to run the query manually?

      13 days later

      I'm still having this issue and I can't add Agents because of it.

      ntozier Out of curiosity what happens if you try to run the query manually?

      Also did you restart MariaDB after you edited the config file?

        ntozier Out of curiosity what happens if you try to run the query manually?

        Also did you restart MariaDB after you edited the config file?

        How do I run that query manually? Isn't the session_id a randomly generated thing? Yes, of course I reset MariaDB, it's been a while since I made that change. I've even reset the VM since posting the original post.

        Run the MariaDB command line client.
        Connect to the database.
        paste the query into the command prompt, hit enter.

        MariaDB [osticket_db]> INSERT INTO `ost_session` SET `session_id` = '3098kvn2irou0nrl23p7h9j9sc', 
        `session_data` = 'csrf|N;', 
        `session_expire` = (NOW() + INTERVAL 86400 SECOND), `user_ip` = '192.168.1.1';
        ERROR 1364 (HY000): Field 'user_agent' doesn't have a default value

        Did you edit the error stuff in the OP to make it smaller / fit better? the user_agent (web browser information) appears to be missing.

        I would think that:
        INSERT INTOost_sessionSETsession_id= '3098kvn2irou0nrl23p7h9j9sc',session_data= 'csrf|N;',session_expire= (NOW() + INTERVAL 86400 SECOND),user_ip= '192.168.1.1'

        should be more like:
        INSERT INTOost_sessionSETsession_id= '3098kvn2irou0nrl23p7h9j9sc',session_data= 'csrf|N;',session_expire= (NOW() + INTERVAL 86400 SECOND),user_ip= '192.168.1.1',user_agent= 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.101 Safari/537.36'

          ntozier Did you edit the error stuff in the OP to make it smaller / fit better? the user_agent (web browser information) appears to be missing.

          No, I literally just copy/pasta'd.

          I added the user_agent (copy/pasta'd your code):

          MariaDB [osticket_db]> INSERT INTO ost_session SET session_id= '3098kvn2irou0nrl23p7h9j9sc', 
          session_data = 'csrf|N;',
          session_expire = (NOW() + INTERVAL 86400 SECOND), 
          user_ip = '192.168.1.1', user_agent = 'Mozilla/5.0 (Windows NT 10.0;
          Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.101 Safari/537.36';
          Query OK, 1 row affected (0.011 sec)

          So then you are not getting the same error when you run the query manually.. Hmm. Okay I'm out of ideas then.

          Write a Reply...