SQL query brutally hangs, errors in logs, any help appreciated
-
(this is a followup from https://forum.kopano.io/topic/3706/mysql_real_connect-too-many-connections-unable-to-get-database-connection-mysql-not-initialized-deserializeobject-failed-disk-error-80000007 but I think it’s better to start fresh off).
I have the problem that synchronizing with kopano just hangs for a LONG time. After a long debug session I found that the mySQL server is always stuck at “Writing to net” with a query. Example:
MariaDB [zarafa]> SHOW PROCESSLIST; +-----+-------------+-----------+--------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----+-------------+-----------+--------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 251 | root | localhost | zarafa | Query | 0 | Init | SHOW PROCESSLIST | 0.000 | | 252 | zarafa | localhost | zarafa | Sleep | 207 | | NULL | 0.000 | | 254 | zarafa | localhost | zarafa | Sleep | 207 | | NULL | 0.000 | | 255 | zarafa | localhost | zarafa | Sleep | 96 | | NULL | 0.000 | | 256 | zarafa | localhost | zarafa | Sleep | 207 | | NULL | 0.000 | | 257 | zarafa | localhost | zarafa | Sleep | 207 | | NULL | 0.000 | | 258 | zarafa | localhost | zarafa | Sleep | 115 | | NULL | 0.000 | | 259 | zarafa | localhost | zarafa | Sleep | 0 | | NULL | 0.000 | | 260 | zarafa | localhost | zarafa | Sleep | 97 | | NULL | 0.000 | | 261 | zarafa | localhost | zarafa | Sleep | 69 | | NULL | 0.000 | | 262 | zarafa | localhost | zarafa | Sleep | 0 | | NULL | 0.000 | | 263 | zarafa | localhost | zarafa | Sleep | 115 | | NULL | 0.000 | | 264 | zarafa | localhost | zarafa | Sleep | 97 | | NULL | 0.000 | | 265 | zarafa | localhost | zarafa | Sleep | 8 | | NULL | 0.000 | | 266 | zarafa | localhost | zarafa | Query | 3 | Writing to net | SELECT 0, tag, properties.type, val_ulong, val_string, val_binary, val_double, val_longint, val_hi, | 0.000 | +-----+-------------+-----------+--------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ 20 rows in set (0.000 sec)Obtaining the full query using
SHOW FULL PROCESSLISTgives the following SQL query:SELECT 0, tag, properties.type, val_ulong, val_string, val_binary, val_double, val_longint, val_hi, val_lo, 0, names.nameid, names.namestring, names.guid FROM properties LEFT JOIN names ON properties.tag-34049=names.id WHERE hierarchyid= NAME_CONST('hid',27211) AND (tag <= 34048 OR names.id IS NOT NULL) AND (tag NOT IN (4105, 4115) OR NAME_CONST('mode',1) = 0 OR ( NAME_CONST('mode',1) = 1 AND tag = NAME_CONST('bestbody',4115))) UNION SELECT count(*), tag, mvproperties.type, group_concat(length(mvproperties.val_ulong),':', mvproperties.val_ulong ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_string),':', mvproperties.val_string ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_binary),':', mvproperties.val_binary ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_double),':', mvproperties.val_double ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_longint),':', mvproperties.val_longint ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_hi),':', mvproperties.val_hi ORDER BY mvproperties.orderid SEPARATOR ''), group_concat(length(mvproperties.val_lo),':', mvproperties.val_lo ORDER BY mvproperties.orderid SEPARATOR ''), 0, names.nameid, names.namestring, names.guid FROM mvproperties LEFT JOIN names ON mvproperties.tag-34049=names.id WHERE hierarchyid= NAME_CONST('hid',27211) AND (tag <= 34048 OR names.id IS NOT NULL) GROUP BY tag, mvproperties.typeThis very same query is executed all the time (except that always a different ID is used).
Interestingly, when I execute this query from SQL prompt I get an error message:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`zarafa`.`properties`.`tag` - 34049'Not sure if this is related or not. Regardless, this goes on for a long time (maybe 10 minutes). This example shows
kopano-stats --topwhile running already for over 5min (it shows that the process in question is in S(leep) state all the time, possibly waiting for the SQL queries):Last update: Thu Apr 22 03:32:11 2021 (1.0s since last) Sess: 4 Sess grp: 2 Users: 2 Hosts: 2 CPU: 0% QLen: 0 QAge: 0.000 RT: 0 ms SQL/s SEL: 0 UPD: 0 INS: 0 DEL: 0 Threads(idle): 2(6) MWOPS: 0 MROPS: 0 SOAP calls: 6 VERSION USERID PEER APP TIME CPUTIME CPU% NREQ STAT TASK 7,2,6,5 user 10.7.102.170 /(OUTLOOK.EXE) 5:23 0:00 0 0 S 11.0.1 SYSTEM *:pid-14289 stats/11.0.1(kopano 0:01 0:01 0 7 P 7,2,6,5 user 10.7.102.170 /(OUTLOOK.EXE) 0:00 0:00 0 0 7,2,6,5 user 10.7.102.170 /(OUTLOOK.EXE) 0:00 0:00 0 0Eventually the queries stop, and the following error is shown in
kopano/server.log:2021-04-22T03:33:27.003020: [error ] SQL [00000266] result failed: Lost connection to MySQL server during query 2021-04-22T03:33:27.004171: [error ] SerializeObject obj 27277 failed: disk error (80000007) 2021-04-22T03:33:27.007035: [error ] SerializeObject obj 26240 failed: disk error (80000007) 2021-04-22T03:33:28.636590: [debug ] gSOAP error on processing request: SOAP_FATAL_ERRORThese are the things I have tried:
- I exported all data (mysqldump), re-created the database and re-imported data (mysql)
- The “Writing to Net” status pointed me that there could be a network issue. Previously my SQL server was on a different host. I installed mariadb from scratch on the same host as kopano and connected via unix domain socket.
- I ran
kopano-fsck - I ran
kopano-dbadm k-1216(with kopano server stopped)
What else could I possibly do?
EDIT: Whoah, is this frustrating. Every step I do reveals more issues. I missed that kopano-fsck did not even fix anything:
# kopano-fsck -u user --calendar --acceptdisclaimer --autofix yes --autodel no [...] Statistics: IPF.Appointment Folders: 3 Entries: 6281 Problems: 578 Fixed: 578 Deleted: 0 # kopano-fsck -u user --calendar --acceptdisclaimer --autofix yes --autodel no [...] Statistics: IPF.Appointment Folders: 3 Entries: 6281 Problems: 578 Fixed: 578 Deleted: 0Any repeated run just shows the same 578 (supposedly) errornous entries. HTF?
-
Hi @divb,
in your other thread you said:
@divb said in mysql_real_connect: Too many connections, Unable to get database connection: MYSQL not initialized, DeserializeObject failed: disk error (80000007):
Also, when I sync via Outlook, synchronization hangs at “Syncing Drafts”.
So this seem to be the culprit here. I would recommend to create a new cached profile in Outlook (you could just delete the data directory of the offline client when Outlook is closed, with the next start it will be recreated).
Regards Felix
Resources:
https://kopano.com/blog/how-to-get-kopano/
https://documentation.kopano.io/
https://kb.kopano.io/Support overview:
https://kopano.com/support/ -
@fbartels said in SQL query brutally hangs, errors in logs, any help appreciated:
So this seem to be the culprit here. I would recommend to create a new cached profile in Outlook (you could just delete the data directory of the offline client when Outlook is closed, with the next start it will be recreated).
I unfortunately tried this many times already. Cached and online.
With the cached one I get errors (followed by the errors described above in the log file), with online, Outlook doesn’t even start.Meanwhile I am pretty sure that the database is corrupt and I’m looking for a way to fix this somehow. As I show above,
kopano-fsckunfortunately does not apply any changes (rerunning it, always shows the same amount of broken entries).kopano-dbadmdoesn’t help either. -
Hi @divb,
kopano-fsckdoes not really do a full database consistency check, rather it looks for certain known cases of nonsensical data and repairs that.If you really suspect a damaged database then unfortunately the best course of action is to identify a sane database dump and restore it.
Further inspection of your database could be done, but this would be a task for our support and something that we cannot provide free of charge through a community forum.
