unability to insert into sys_log is an iterative tipping point, stalling systems
Issue:
(Note: We have master/slave config only, but a single master installation probably would be affected too) When ispconfig tries to insert into sys_log sql table and fails for whatever reason, it iteratively tries to also enter that very error into the sys_log table. The error then exponentially grows in length, also due to quoting slashes for the mysql query. The logging in parallel to the ispconfig.log file leads to the logfile rapidly growing and hilarious line (and file) lengths are reached soon. This behavour leads to mysql, cron (php and sh, e.g. "tail -n100") consuming excessive amounts of memory sooner or later, leading any system prone to stall (which, btw, in turn may stall mysql even more until it would refuse further connections while still processing preceding under high load condition - hence this gets a real tipping point - "of no return")
reproduce
force slaves mysql into stall using excessive high-profile queries until it shuts down new connections( or possibly simply shut mysql down) and force any error to be logged (or wait for the cron and "unable to connect to local server" warning to occur)
proposal
Break the iteration by not logging failed attempts to log something. (when the query for the database log fails, ispconfig should at most log this very issue into ispconfig.log, but never into the database again).
(Note: Happened on diffferent servers in master/slave configuration under extremely high RAM and CPU load conditions every now and then - started with the "Unable to connect to local server." warning. 99% of ispconfig.log were slashes in the end after we finally managed to kill all processes involved in the stall, like php-fpm, apache, mysql, cronjobs, mails created by crons and so on... the usual massive overload situation)
example ispconfig.log excerpt
29.01.2018-15:45 - WARNING - Unable to connect to local server.
29.01.2018-15:45 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0 , 1, UNIX_TIMESTAMP(), 'Unable to connect to local server.') -> 0 ()
29.01.2018-15:45 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0 , 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_TIMESTAMP(), 'Unable to connect to local server.') -> 0 ()') -> 0 ()
29.01.2018-15:45 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0 , 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX _TIMESTAMP(), \'Unable to connect to local server.\') -> 0 ()') -> 0 ()') -> 0 ()
29.01.2018-15:45 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0 , 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX _TIMESTAMP(), \'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_T IMESTAMP(), \\\'Unable to connect to local server.\\\') -> 0 ()\') -> 0 ()') -> 0 ()') -> 0 ()
29.01.2018-15:45 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0 , 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX _TIMESTAMP(), \'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_T IMESTAMP(), \\\'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX _TIMESTAMP(), \\\\\\\'Unable to connect to local server.\\\\\\\') -> 0 ()\\\') -> 0 ()\') -> 0 ()') -> 0 ()') -> 0 ()
[...]
29.01.2018-15:45 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0
, 1, UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1,
UNIX_TIMESTAMP(), 'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX
_TIMESTAMP(), \'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_T
IMESTAMP(), \\\'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX
_TIMESTAMP(), \\\\\\\'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5,
0, 1, UNIX_TIMESTAMP(), \\\\\\\\\\\\\\\'Falsche Anfrage / Wrong QuerySQL-Query = INSERT INTO sys_log (server_id,datalog_id,loglevel,t
stamp,message) VALUES (5, 0, 1, UNIX_TIMESTAMP(), \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'Falsche Anfrage / Wrong QuerySQL-Qu
ery = INSERT INTO sys_log (server_id,datalog_id,loglevel,tstamp,message) VALUES (5, 0, 1, UNIX_TIMESTAMP(), \\\\\\\\\\\\\\\\\\\\
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'Unable to connect to local server.\\\\\\\\\\\\\\
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\') -> 0 ()\\\\\\\\\\\\\\\\\\\\
\\\\\\\\\\\') -> 0 ()\\\\\\\\\\\\\\\') -> 0 ()\\\\\\\') -> 0 ()\\\') -> 0 ()\') -> 0 ()') -> 0 ()') -> 0 (
)
[ ... continues boundless... ]