MySQL connection errors

Infos:

  • Used Zammad version: 4.1.x
  • Used Zammad installation type: package
  • Operating system: Ubuntu 16.04.7
  • Browser + version: Chrome 92.0.4515.131

Expected behavior:

Zammad should load overview data (tickets)
*

Actual behavior:

Zammad doesn’t load any overview data (tickets) and gives 500 server error. Logs show various MySQL errors:
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
Lost connection to MySQL server during query
MySQL server has gone away
Can't reconnect to database
*

Steps to reproduce the behavior:

None
*

Zammad stopped working after a power outage caused the server it runs on to ungracefully shut down.

All the errors seem to be related to Zammad not being able to complete any MySQL requests:

E, [2021-09-08T09:34:03.726287 #18625-47116445214100] ERROR -- : thread_client 47322444742460 exited with error #<ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query: SELECT  `activity_streams`.* FROM `activity_streams` WHERE ((permission_id IN (35,37,40,41,42,45,50,55) AND (group_id IS NULL OR group_id IN (4))) OR (permission_id IS NULL AND group_id IN (4))) ORDER BY `activity_streams`.`created_at` DESC LIMIT 1>
E, [2021-09-08T09:34:03.726362 #18625-47116445214100] ERROR -- : /opt/zammad/vendor/bundle/ruby/2.6.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `_query'
E, [2021-09-08T09:34:03.726596 #18716-46957983579280] ERROR -- : Mysql2::Error: Lost connection to MySQL server during query: SELECT  `users`.* FROM `users` WHERE `users`.`id` = 19225 LIMIT 1 (ActiveRecord::StatementInvalid)
E, [2021-09-08T09:34:03.727081 #18625-47116437931440] ERROR -- : thread_client 47322444927080 exited with error #<ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query: SELECT  `users`.* FROM `users` WHERE `users`.`id` = 19225 LIMIT 1>
E, [2021-09-08T09:34:03.727146 #18625-47116437931440] ERROR -- : /opt/zammad/vendor/bundle/ruby/2.6.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `_query'
E, [2021-09-08T09:34:03.727218 #18716-46957983571380] ERROR -- : Mysql2::Error: Lost connection to MySQL server during query: SELECT  `users`.* FROM `users` WHERE `users`.`id` = 19225 LIMIT 1 (ActiveRecord::StatementInvalid)
I, [2021-09-08T09:34:03.728792 #18716-46957983571380]  INFO -- : Completed 500 Internal Server Error in 98122ms (ActiveRecord: 97618.2ms)
F, [2021-09-08T09:34:03.729292 #18716-46957983571380] FATAL -- :
F, [2021-09-08T09:34:03.730021 #18716-46957983571380] FATAL -- : ActiveRecord::StatementInvalid (Mysql2::Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): SELECT  1 AS one FROM `permissions` INNER JOIN `permissions_roles` ON `permissions`.`id` = `permissions_roles`.`permission_id` INNER JOIN `roles` ON `permissions_roles`.`role_id` = `roles`.`id` INNER JOIN `roles_users` ON `roles`.`id` = `roles_users`.`role_id` WHERE `roles_users`.`user_id` = 3 AND `roles`.`active` = TRUE AND `permissions`.`active` = TRUE AND `permissions`.`name` = 'admin' LIMIT 1):
F, [2021-09-08T09:34:03.730324 #18716-46957983571380] FATAL -- :
F, [2021-09-08T09:34:03.730365 #18716-46957983571380] FATAL -- : app/models/concerns/can_be_authorized.rb:29:in `permissions?'
[3df6460b-4dbc-45d7-8ea4-51783b35e8f4] app/controllers/application_controller/handles_errors.rb:103:in `humanize_error'
[3df6460b-4dbc-45d7-8ea4-51783b35e8f4] app/controllers/application_controller/handles_errors.rb:67:in `block (2 levels) in respond_to_exception'
[3df6460b-4dbc-45d7-8ea4-51783b35e8f4] app/controllers/application_controller/handles_errors.rb:66:in `respond_to_exception'
[3df6460b-4dbc-45d7-8ea4-51783b35e8f4] app/controllers/application_controller/handles_errors.rb:26:in `unprocessable_entity'
I, [2021-09-08T09:34:03.730263 #18716-46957983579280]  INFO -- : Completed 500 Internal Server Error in 108690ms (ActiveRecord: 108227.4ms)
F, [2021-09-08T09:34:03.730842 #18716-46957983579280] FATAL -- :
F, [2021-09-08T09:34:03.730862 #18716-46957983579280] FATAL -- : ActiveRecord::StatementInvalid (Mysql2::Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2): SELECT  1 AS one FROM `permissions` INNER JOIN `permissions_roles` ON `permissions`.`id` = `permissions_roles`.`permission_id` INNER JOIN `roles` ON `permissions_roles`.`role_id` = `roles`.`id` INNER JOIN `roles_users` ON `roles`.`id` = `roles_users`.`role_id` WHERE `roles_users`.`user_id` = 3 AND `roles`.`active` = TRUE AND `permissions`.`active` = TRUE AND `permissions`.`name` = 'admin' LIMIT 1):
F, [2021-09-08T09:34:03.730872 #18716-46957983579280] FATAL -- :
F, [2021-09-08T09:34:03.731054 #18716-46957983579280] FATAL -- : app/models/concerns/can_be_authorized.rb:29:in `permissions?'
[65f2be2f-d556-4864-a40d-d13b25c6a61d] app/controllers/application_controller/handles_errors.rb:103:in `humanize_error'
[65f2be2f-d556-4864-a40d-d13b25c6a61d] app/controllers/application_controller/handles_errors.rb:67:in `block (2 levels) in respond_to_exception'
[65f2be2f-d556-4864-a40d-d13b25c6a61d] app/controllers/application_controller/handles_errors.rb:66:in `respond_to_exception'
[65f2be2f-d556-4864-a40d-d13b25c6a61d] app/controllers/application_controller/handles_errors.rb:26:in `unprocessable_entity'

MySQL is running fine, I can connect to it using MySQL Workbench both as root user and the zammad user as configured in database.yml

netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     446932   /var/run/mysqld/mysqld.sock

My max_allowed_packet is set to 256MB as I’ve read some threads that this could cause similar errors.

I simply cannot figure our why these errors are occurring or how to solve them, please help!

Just to add to the fact that MySQL is running fine, I can connect to MySQL via socket fine as well:

dentelle@dentelle-commander:~$ mysql -u root -p -S /var/run/mysqld/mysqld.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.33-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

I’ve tried to su - zammad to see what would happen if I tried connecting as the zammad user to test if there is perhaps some permissions issues, but I’m unable to switch to zammad user.

Apparently no password is set and I thus cannot switch to the user and I’m not comfortable fiddling with that any further.

I’ve figured out what is causing my problems and documenting it here for others to find should they ever encounter that same.

I realised that my MySQL server was actually crashing the whole time, but never when I tested it.

I then ran a mysqlcheck on my database and realised that the users table is what is causing MySQL to crash. I can’t even dump the table, the dump also causes it to crash, even with innodb_force_recovery=1.

I can do a select of around 82% of the users in the table, the dump would always fail at a specific row, which according to other posts and forums means my data is now corrupted.

I’m going to open a new thread on what I can do to salvage what I can:

1 Like