Login issues

Retox92

Hi all,

I have been using osticket for a few months now and turned it in to quite a beast.

Since a couple of days ago i have had major issues with logging in to the Agent accounts.

Theres minimal amount of open items currently, maybe 50 or so.

I have notice the following:

I can login some times with accounts but rarely,
I can go to the mysql database and select the column change password and switch to 1 and then it 100% lets me
in I chant the password and then I can do whatever i want but if i log out, the new password also doesnt work and
it freezes on the login screen and eventually times out.
a couple of users without admin priveledges can log in no issue each and every time.

I am using 1.17.2 version as i used .3 but with the attachment bug it was no good for me so rolled back.
Using MYSQL 5.5.62
PHP 8.1.16
IIS 10

No errors in an log that i can see.

I see this error regularly in the server logs:
'user' entry 'admin@%' has both a password and an authentication plugin specified. The password will be ignored.

Also:
IP address 'xxx.xxx.xxx.xxx' could not be resolved: No such host is known.
So i applied the skip_name_resolve to the my.ini file

Also the CPU keeps getting maxed out 100% even when not doing anything.

4 virtual cores
8GB Ram
129GB Space
DB size about 70Mb
Windows 2019 Server on a VPS

Most of the time the error is 500 - Internal server error. but it works one minute then not the next. The VPS providers are blaming the high mysql CPU usage. I tried all the tricks on the forum which dont remember off the top of my head now but i trawled though all sort for optimization. I have only one DB the osticket..

Im using a custom port on the sql server 4007 and php.ini is configured to know this and 99% of the time it reaches the login screen so i dont think thats the issue.

Any ideas brainiacs?

KevinTheJedi

Retox92

Go back to v1.17.3 and apply this patch for the attachment issue:

https://github.com/osTicket/osTicket/pull/6495

As for the login issues the majority of that was with v1.17.2 and session bugs that were addressed in v1.17.3.

As for the 100% CPU usage it sounds like MySQL is using a lot of the server's resources. You can enable MySQL slow query logs and see if any queries are taking a long time to execute. You can also try repairing all the database tables and ensuring they all using InnoDB. Lastly, you can follow MySQL docs for how to optimize MySQL for optimal performance.

Cheers.

Retox92

KevinTheJedi

OK will give it a go.

i forgot to mention this is appearing all over the logs.

Thanks.

KevinTheJedi

Retox92

Again, issues related to sessions and v1.17.2. Addressed in v1.17.3.

Cheers.

Retox92

Hey,

Sorry to say its not solved my issue, still reaches the login page no issue and if i set column Change password to 1 then it logs in no issue expecting to change the password. Then following wont log in.

I tried clearing cache and cookies etc.. same.

KevinTheJedi

Retox92

That's not really making much sense. Can you share a screenshot of the staff record in the database for the staff having issues?

Cheers.

Retox92

Oh tell me about it :-) I have disected it to find what it is but no idea. Even creating a new user has the same issue.

And the image as requested.

Thanks

KevinTheJedi

Retox92

So your issue might be due to long running MySQL queries. Please enable MySQL slow query logs and see if you have any slow running queries.

I'm thinking this because when Agents login they usually get redirected to the Ticket Queue which has to run queries to display the Tickets but also to generate the Queue Counts. The Queue Count query can be quite expensive especially in a non-optimized MySQL instance. So it is probably running the count query, taking forever, and timing out. However, when you set the change_passwd to 1 it redirects them to their profile page instead which skips the queue queries.

Cheers.

Retox92

OK will have a go, I only have around 50 -60 tickets though would that still cause this headache?

Thanks!!

KevinTheJedi

Retox92

It shouldn’t at all but just an assumption with the given information. When you say you can’t login with accounts are you talking about Agent accounts or User accounts? Also, what exactly does it do when you try to login just spin? Do you notice a CPU spike when attempting to login? Have you tried upgrading to v1.17.3 again and clearing all cache, cookies, etc.?

Cheers.

Retox92

KevinTheJedi

Hi,

Sorry for the delay, yes the CPU spikes to around 100% most of the time.
Login is only agents, no users are working with the system, just agents.
Version is 1.17.3 already.
Cache cookies cleared each time.
Even logging in from local hose on the VPS has the same issue.

Thanks,

KevinTheJedi

Retox92

The login coupled with high CPU usage indicates the issue is most likely the Queue Count query using all the resources and taking a long time to execute. There are other threads on this forum that explain what you can do. There is a patch you can apply that will likely help, converting tables to InnoDB, MySQL optimization, adding more resources, etc.

Cheers.

Retox92

Hi, the slow quesry logs report this:

C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.40-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MYSQL5740
Time Id Command Argument
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.40-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MYSQL5740
Time Id Command Argument
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.41-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MYSQL5740
Time Id Command Argument
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.41-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MYSQL5740
Time Id Command Argument
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.41-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MYSQL5740
Time Id Command Argument
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.41-log (MySQL Community Server (GPL)). started with:

All tables are innoDB

I have doubled and doubled again the resources.

and the optimization .. i have done what i can figure out.

Im doing this to push on the company but seems its more hassle that its worth.

TonyO

You may need to increase long_query_time or set it to '0'. The log info isn't showing anything useful after "Time Id Command Argument." One of the lines following this line should have the actual argument, the query statement.

Retox92

Well out of my comfort zone now but:

Time                 Id Command    Argument
# Time: 2023-03-27T21:39:30.152854Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000295  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
set autocommit=1;
# Time: 2023-03-27T21:39:30.153171Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000231  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953170;
SELECT current_user();
# Time: 2023-03-27T21:39:30.153400Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000100  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
SET CHARACTER SET utf8;
# Time: 2023-03-27T21:39:30.153520Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000072  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
SET NAMES utf8;
# Time: 2023-03-27T21:39:30.159019Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.005415  Lock_time: 0.000514 Rows_sent: 1  Rows_examined: 1038
SET timestamp=1679953170;
SHOW SESSION VARIABLES LIKE 'sql_mode';
# Time: 2023-03-27T21:39:30.159560Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000199  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953170;
SELECT CONNECTION_ID();
# Time: 2023-03-27T21:39:30.160072Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000392  Lock_time: 0.000170 Rows_sent: 1  Rows_examined: 41
SET timestamp=1679953170;
show character set where charset = 'utf8mb4';
# Time: 2023-03-27T21:39:30.160306Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000141  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
SET NAMES 'utf8mb4';
# Time: 2023-03-27T21:39:30.163643Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.003149  Lock_time: 0.000240 Rows_sent: 1  Rows_examined: 714
SET timestamp=1679953170;
SHOW SESSION STATUS LIKE 'Ssl_cipher';
# Time: 2023-03-27T21:39:30.163869Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     2
# Query_time: 0.000112  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
set autocommit=1;
# Time: 2023-03-27T21:39:30.172879Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000144  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
set autocommit=1;
# Time: 2023-03-27T21:39:30.173170Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000143  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953170;
SELECT current_user();
# Time: 2023-03-27T21:39:30.173396Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000078  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
SET CHARACTER SET utf8;
# Time: 2023-03-27T21:39:30.173515Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000073  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
SET NAMES utf8;
# Time: 2023-03-27T21:39:30.173722Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000099  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953170;
SELECT CONNECTION_ID();
# Time: 2023-03-27T21:39:30.174089Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000286  Lock_time: 0.000123 Rows_sent: 1  Rows_examined: 41
SET timestamp=1679953170;
show character set where charset = 'utf8mb4';
# Time: 2023-03-27T21:39:30.174244Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000066  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
SET NAMES 'utf8mb4';
# Time: 2023-03-27T21:39:30.175799Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.001419  Lock_time: 0.000162 Rows_sent: 1  Rows_examined: 714
SET timestamp=1679953170;
SHOW SESSION STATUS LIKE 'Ssl_cipher';
# Time: 2023-03-27T21:39:30.175963Z
# User@Host: admin[admin] @ localhost [127.0.0.1]  Id:     3
# Query_time: 0.000075  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953170;
set autocommit=1;
# Time: 2023-03-27T21:45:17.969592Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000130  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
set autocommit=1;
# Time: 2023-03-27T21:45:17.969759Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000105  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953517;
SELECT current_user();
# Time: 2023-03-27T21:45:17.969971Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000089  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
SET CHARACTER SET utf8;
# Time: 2023-03-27T21:45:17.970105Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000074  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
SET NAMES utf8;
# Time: 2023-03-27T21:45:17.975541Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.005360  Lock_time: 0.000246 Rows_sent: 1  Rows_examined: 1038
SET timestamp=1679953517;
SHOW SESSION VARIABLES LIKE 'sql_mode';
# Time: 2023-03-27T21:45:17.975991Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000197  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953517;
SELECT CONNECTION_ID();
# Time: 2023-03-27T21:45:17.976642Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000552  Lock_time: 0.000352 Rows_sent: 1  Rows_examined: 41
SET timestamp=1679953517;
show character set where charset = 'utf8mb4';
# Time: 2023-03-27T21:45:17.976838Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000095  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
SET NAMES 'utf8mb4';
# Time: 2023-03-27T21:45:17.979355Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.002340  Lock_time: 0.000275 Rows_sent: 1  Rows_examined: 714
SET timestamp=1679953517;
SHOW SESSION STATUS LIKE 'Ssl_cipher';
# Time: 2023-03-27T21:45:17.980425Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000972  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use osticket;
SET timestamp=1679953517;
USE `osticket`;
# Time: 2023-03-27T21:45:17.980567Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000089  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
set autocommit=1;
# Time: 2023-03-27T21:45:17.996334Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000260  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
set autocommit=1;
# Time: 2023-03-27T21:45:17.998231Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000125  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953517;
SELECT current_user();
# Time: 2023-03-27T21:45:17.998543Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000156  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
SET CHARACTER SET utf8;
# Time: 2023-03-27T21:45:17.998758Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000167  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
SET NAMES utf8;
# Time: 2023-03-27T21:45:17.998921Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000089  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1679953517;
SELECT CONNECTION_ID();
# Time: 2023-03-27T21:45:17.999296Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000295  Lock_time: 0.000115 Rows_sent: 1  Rows_examined: 41
SET timestamp=1679953517;
show character set where charset = 'utf8mb4';
# Time: 2023-03-27T21:45:17.999478Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000096  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953517;
SET NAMES 'utf8mb4';
# Time: 2023-03-27T21:45:18.002085Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.002453  Lock_time: 0.000253 Rows_sent: 1  Rows_examined: 714
SET timestamp=1679953518;
SHOW SESSION STATUS LIKE 'Ssl_cipher';
# Time: 2023-03-27T21:45:18.002391Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000187  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953518;
USE `osticket`;
# Time: 2023-03-27T21:45:18.002519Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000083  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953518;
set autocommit=1;
# Time: 2023-03-27T21:45:41.821658Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000063  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953541;
# administrator command: Ping;
# Time: 2023-03-27T21:45:41.828314Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.006558  Lock_time: 0.000523 Rows_sent: 4  Rows_examined: 4
SET timestamp=1679953541;
SHOW FULL COLUMNS FROM `osticket`.`ost_staff_dept_access`;
# Time: 2023-03-27T21:45:41.829695Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000032  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
SET timestamp=1679953541;
# administrator command: Ping;
# Time: 2023-03-27T21:45:41.829995Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000226  Lock_time: 0.000105 Rows_sent: 3  Rows_examined: 3
SET timestamp=1679953541;
SHOW INDEXES FROM `osticket`.`ost_staff_dept_access`;
# Time: 2023-03-27T21:45:45.803004Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000062  Lock_time: 0.000000 Rows_sent: 3  Rows_examined: 3
SET timestamp=1679953545;
# administrator command: Ping;
# Time: 2023-03-27T21:45:45.803790Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000704  Lock_time: 0.000431 Rows_sent: 4  Rows_examined: 4
SET timestamp=1679953545;
SHOW COLUMNS FROM `osticket`.`ost_staff_dept_access`;
# Time: 2023-03-27T21:45:48.255520Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000048  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4
SET timestamp=1679953548;
# administrator command: Ping;
# Time: 2023-03-27T21:45:48.258028Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.002430  Lock_time: 0.000331 Rows_sent: 34  Rows_examined: 34
SET timestamp=1679953548;
SHOW FULL COLUMNS FROM `osticket`.`ost_staff`;
# Time: 2023-03-27T21:45:48.258959Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000032  Lock_time: 0.000000 Rows_sent: 34  Rows_examined: 34
SET timestamp=1679953548;
# administrator command: Ping;
# Time: 2023-03-27T21:45:48.259187Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000179  Lock_time: 0.000075 Rows_sent: 6  Rows_examined: 6
SET timestamp=1679953548;
SHOW INDEXES FROM `osticket`.`ost_staff`;
# Time: 2023-03-27T21:45:52.220485Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000049  Lock_time: 0.000000 Rows_sent: 6  Rows_examined: 6
SET timestamp=1679953552;
# administrator command: Ping;
# Time: 2023-03-27T21:45:52.221234Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000669  Lock_time: 0.000318 Rows_sent: 34  Rows_examined: 34
SET timestamp=1679953552;
SHOW COLUMNS FROM `osticket`.`ost_staff`;
# Time: 2023-03-27T21:45:54.552921Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000059  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953554;
# administrator command: Ping;
# Time: 2023-03-27T21:45:54.559964Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.001867  Lock_time: 0.001649 Rows_sent: 49  Rows_examined: 49
SET timestamp=1679953554;
SELECT `ost_staff_dept_access`.`staff_id`,
    `ost_staff_dept_access`.`dept_id`,
    `ost_staff_dept_access`.`role_id`,
    `ost_staff_dept_access`.`flags`
FROM `osticket`.`ost_staff_dept_access`
LIMIT 0, 1000;
# Time: 2023-03-27T21:45:54.625296Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000390  Lock_time: 0.000191 Rows_sent: 3  Rows_examined: 3
SET timestamp=1679953554;
SHOW INDEX FROM `osticket`.`ost_staff_dept_access`;
# Time: 2023-03-27T21:45:54.653416Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.008300  Lock_time: 0.007938 Rows_sent: 9  Rows_examined: 9
SET timestamp=1679953554;
SELECT `ost_staff`.`staff_id`,
    `ost_staff`.`dept_id`,
    `ost_staff`.`role_id`,
    `ost_staff`.`username`,
    `ost_staff`.`firstname`,
    `ost_staff`.`lastname`,
    `ost_staff`.`passwd`,
    `ost_staff`.`backend`,
    `ost_staff`.`email`,
    `ost_staff`.`phone`,
    `ost_staff`.`phone_ext`,
    `ost_staff`.`mobile`,
    `ost_staff`.`signature`,
    `ost_staff`.`lang`,
    `ost_staff`.`timezone`,
    `ost_staff`.`locale`,
    `ost_staff`.`notes`,
    `ost_staff`.`isactive`,
    `ost_staff`.`isadmin`,
    `ost_staff`.`isvisible`,
    `ost_staff`.`onvacation`,
    `ost_staff`.`assigned_only`,
    `ost_staff`.`show_assigned_tickets`,
    `ost_staff`.`change_passwd`,
    `ost_staff`.`max_page_size`,
    `ost_staff`.`auto_refresh_rate`,
    `ost_staff`.`default_signature_type`,
    `ost_staff`.`default_paper_size`,
    `ost_staff`.`extra`,
    `ost_staff`.`permissions`,
    `ost_staff`.`created`,
    `ost_staff`.`lastlogin`,
    `ost_staff`.`passwdreset`,
    `ost_staff`.`updated`
FROM `osticket`.`ost_staff`
LIMIT 0, 1000;
# Time: 2023-03-27T21:45:54.708790Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000380  Lock_time: 0.000183 Rows_sent: 6  Rows_examined: 6
SET timestamp=1679953554;
SHOW INDEX FROM `osticket`.`ost_staff`;
# Time: 2023-03-27T21:46:21.883111Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000068  Lock_time: 0.000000 Rows_sent: 9  Rows_examined: 9
SET timestamp=1679953581;
# administrator command: Ping;
# Time: 2023-03-27T21:46:21.883233Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000034  Lock_time: 0.000000 Rows_sent: 9  Rows_examined: 9
SET timestamp=1679953581;
# administrator command: Ping;
# Time: 2023-03-27T21:46:21.883404Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000132  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953581;
set autocommit=0;
# Time: 2023-03-27T21:46:23.370747Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.001081  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953583;
# administrator command: Prepare;
# Time: 2023-03-27T21:46:23.371293Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000489  Lock_time: 0.000069 Rows_sent: 0  Rows_examined: 1
SET timestamp=1679953583;
UPDATE `osticket`.`ost_staff` SET `change_passwd` = '1' WHERE (`staff_id` = '1');
# Time: 2023-03-27T21:46:23.375796Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.004366  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953583;
commit;
# Time: 2023-03-27T21:46:23.375959Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000033  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953583;
# administrator command: Close stmt;
# Time: 2023-03-27T21:46:23.379006Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000492  Lock_time: 0.000221 Rows_sent: 9  Rows_examined: 9
SET timestamp=1679953583;
SELECT `ost_staff`.`staff_id`,
    `ost_staff`.`dept_id`,
    `ost_staff`.`role_id`,
    `ost_staff`.`username`,
    `ost_staff`.`firstname`,
    `ost_staff`.`lastname`,
    `ost_staff`.`passwd`,
    `ost_staff`.`backend`,
    `ost_staff`.`email`,
    `ost_staff`.`phone`,
    `ost_staff`.`phone_ext`,
    `ost_staff`.`mobile`,
    `ost_staff`.`signature`,
    `ost_staff`.`lang`,
    `ost_staff`.`timezone`,
    `ost_staff`.`locale`,
    `ost_staff`.`notes`,
    `ost_staff`.`isactive`,
    `ost_staff`.`isadmin`,
    `ost_staff`.`isvisible`,
    `ost_staff`.`onvacation`,
    `ost_staff`.`assigned_only`,
    `ost_staff`.`show_assigned_tickets`,
    `ost_staff`.`change_passwd`,
    `ost_staff`.`max_page_size`,
    `ost_staff`.`auto_refresh_rate`,
    `ost_staff`.`default_signature_type`,
    `ost_staff`.`default_paper_size`,
    `ost_staff`.`extra`,
    `ost_staff`.`permissions`,
    `ost_staff`.`created`,
    `ost_staff`.`lastlogin`,
    `ost_staff`.`passwdreset`,
    `ost_staff`.`updated`
FROM `osticket`.`ost_staff`
LIMIT 0, 1000;
# Time: 2023-03-27T21:46:23.379688Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000283  Lock_time: 0.000115 Rows_sent: 6  Rows_examined: 6
SET timestamp=1679953583;
SHOW INDEX FROM `osticket`.`ost_staff`;
# Time: 2023-03-27T21:46:25.266680Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000206  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953585;
set autocommit=1;
# Time: 2023-03-27T21:47:41.669596Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000064  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1679953661;
# administrator command: Ping;
# Time: 2023-03-27T21:47:41.672449Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.000726  Lock_time: 0.000164 Rows_sent: 49  Rows_examined: 49
SET timestamp=1679953661;
SELECT `ost_staff_dept_access`.`staff_id`,
    `ost_staff_dept_access`.`dept_id`,
    `ost_staff_dept_access`.`role_id`,
    `ost_staff_dept_access`.`flags`
FROM `osticket`.`ost_staff_dept_access`
LIMIT 0, 1000;
# Time: 2023-03-27T21:47:41.689665Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000379  Lock_time: 0.000186 Rows_sent: 3  Rows_examined: 3
SET timestamp=1679953661;
SHOW INDEX FROM `osticket`.`ost_staff_dept_access`;
# Time: 2023-03-27T21:47:41.772106Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 0.001141  Lock_time: 0.000780 Rows_sent: 9  Rows_examined: 9
SET timestamp=1679953661;
SELECT `ost_staff`.`staff_id`,
    `ost_staff`.`dept_id`,
    `ost_staff`.`role_id`,
    `ost_staff`.`username`,
    `ost_staff`.`firstname`,
    `ost_staff`.`lastname`,
    `ost_staff`.`passwd`,
    `ost_staff`.`backend`,
    `ost_staff`.`email`,
    `ost_staff`.`phone`,
    `ost_staff`.`phone_ext`,
    `ost_staff`.`mobile`,
    `ost_staff`.`signature`,
    `ost_staff`.`lang`,
    `ost_staff`.`timezone`,
    `ost_staff`.`locale`,
    `ost_staff`.`notes`,
    `ost_staff`.`isactive`,
    `ost_staff`.`isadmin`,
    `ost_staff`.`isvisible`,
    `ost_staff`.`onvacation`,
    `ost_staff`.`assigned_only`,
    `ost_staff`.`show_assigned_tickets`,
    `ost_staff`.`change_passwd`,
    `ost_staff`.`max_page_size`,
    `ost_staff`.`auto_refresh_rate`,
    `ost_staff`.`default_signature_type`,
    `ost_staff`.`default_paper_size`,
    `ost_staff`.`extra`,
    `ost_staff`.`permissions`,
    `ost_staff`.`created`,
    `ost_staff`.`lastlogin`,
    `ost_staff`.`passwdreset`,
    `ost_staff`.`updated`
FROM `osticket`.`ost_staff`
LIMIT 0, 1000;
# Time: 2023-03-27T21:47:41.849057Z
# User@Host: root[root] @ localhost [::1]  Id:     5
# Query_time: 0.000387  Lock_time: 0.000178 Rows_sent: 6  Rows_examined: 6
SET timestamp=1679953661;
SHOW INDEX FROM `osticket`.`ost_staff`;

KevinTheJedi

Retox92

Strange, I don’t see any issues from what you just posted.

Cheers.

TonyO

Retox92 Thanks. So, MySQL isn't the bottleneck.
To find out exactly where the issue is, on Windows Server start Task Manager, Processes, and sort processes by CPU. Watch the processes while you log in with your agent account.
I suspect limits in php.ini, eg. max_memory too low. If it's php, create a phpinfo.php <?php phpinfo( ); in the root of osTicket to check the settings.

Daedalus01

Retox92 For what its worth, I have

4 vCPU
8GB RAM
80GB Disk
3000+ tickets

I am running mine on uBuntu 20 in VMWare. I wish I had something more for you to try, but I was just trying to say that even that with as many tickets ( and I know more people have more) my system doesn't max out.

KevinTheJedi

Retox92

Also, have you tried upgrading MySQL? MySQL 8.0 (and above) had introduced some performance improvements and other useful features. I feel like this might help in your current situation. Definitely wouldn't hurt to try, just make sure you take a full backup of everything beforehand so you can easily rollback if needed.

Cheers.