system log not reporting (mysql error)

[iitywygms]

Distributor ID: Ubuntu
Description: Ubuntu 14.04.4 LTS
Release: 14.04

mysql Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.3

Zoneminder v1.29.0. Everything is operating as it should except the log.

When I go to check the logs I get the following error.

2016-04-25 20:38:55.197010 zmfilter 12993 FAT Can't execute 'SELECT * FROM Filters WHERE Background = 1 and( AutoArchive = 1 or AutoVideo = 1 or AutoUpload = 1 or AutoEmail = 1 or AutoMessage = 1 or AutoExecute = 1 zmfilter.pl
2016-04-25 20:37:34.508480 zmwatch 13006 FAT Can't execute: MySQL server has gone away zmwatch.pl
2016-04-22 11:45:10.092120 zmaudit 2383 FAT Can't execute: MySQL server has gone away zmaudit.pl


Ideas?

[iitywygms]

Can I offer more information?
I want to add a few cameras and will be needing the ability to check logs while adding them.

[iitywygms]

Anyone? Bueller, Bueller.

[SteveGilvarry]

No idea obviously something with mysql connection as opposed to ZM itself. Can you add a monitor OK when this error is occurring? Is it a new build so never worked or just stopped at some point?
I am sure you can google it, but some things to tweak here.
http://stackoverflow.com/questions/1242 ... e-sql-file

[iitywygms]

Well I tried some of the suggestions linked above. Nothing helped.
This has been a running system for a few years now.
I can add monitors, change filters, and everything else. Its just I dont get any logs reported. Just the database has gone away message.

I have several months of mysql backups. Guess I could try one from a month ago or so.?

Its just that this server also have mythtv backend running, and I need to be careful when fn with the database. Everything seems to work fine, just now information in the error logs?????
Where else should I look for errors??

[iitywygms]

Can zoneminder run if it does not have a database? (database has gone away message)
Because zoneminder is running just fine. Everything.

[bbunge]

If you are running other databases you could be running out of resources with MySQL. Use mysqltuner to see if you need to adjust a setting in my.cnf.

bb

[iitywygms]

Still same error.

2016-05-02 18:54:51.040420 zmwatch 2402 FAT Can't execute: MySQL server has gone away zmwatch.pl
2016-05-02 18:43:09.637390 zmaudit 3232 FAT Can't execute: MySQL server has gone away zmaudit.pl


Notes.

I ran this.

mysqlcheck -uroot -p --optimize --databases zm

Gor a bunch of this. All checked okay.

zm.Config
note : Table does not support optimize, doing recreate + analyze instead
status : OK
zm.ControlPresets
note : Table does not support optimize, doing recreate + analyze instead
status : OK


I ran this.

sudo mysqltuner

Got this.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MyISAM tables: 93M (Tables: 106)
[--] Data in InnoDB tables: 109M (Tables: 556)
[!!] Total fragmented tables: 42

-------- Security Recommendations -------------------------------------------
[!!] User '@kapok' has no password set.
[!!] User '@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3m 35s (1K q [7.149 qps], 397 conn, TX: 7M, RX: 264K)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 280.0M global + 48.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 7.5G (48% of installed RAM)
[OK] Slow queries: 2% (44/1K)
[OK] Highest usage of available connections: 35% (54/151)
[OK] Key buffer size / total MyISAM indexes: 64.0M/94.0M
[OK] Key buffer hit rate: 99.5% (7K cached / 43 reads)
[OK] Query cache efficiency: 32.9% (252 cached / 767 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 46 sorts)
[!!] Temporary tables created on disk: 35% (204 on disk / 581 total)
[OK] Thread cache hit rate: 85% (57 created / 397 connections)
[OK] Table cache hit rate: 25% (256 open / 998 opened)
[OK] Open file limit used: 25% (262/1K)
[OK] Table locks acquired immediately: 100% (742 immediate / 742 locks)
[OK] InnoDB data size / buffer pool: 109.2M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 1G)

[iitywygms]

I dunno.

Today I was poking around and decided to stop and start zoneminder from the webpage instead of using the command line.
After that the log reporting is fine.

I had restarted the entire computer several times, along with several restarts of both mysql and zoneminder from command lines.


So I dunno????

But its all good now

[bbunge]

You need to adjust settings in my.cnf. See recommendations from your output:

Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 1G)

Make sure you have the RAM to cover the 1G setting...

[iitywygms]

It has been setup like recommended.
I dont know why mysqltuner is telling me to change things.

Here is my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/serve ... ables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

# Kevin edited next 2 lines
innodb_file_per_table
wait_timeout=600
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#
# * Fine Tuning
#
key_buffer = 48M
max_allowed_packet = 128M
thread_stack = 192K
thread_cache_size = 4
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
table_cache = 4096
#thread_concurrency = 10
#
#I added
tmp_table_size = 512M
max_heap_table_size = 1024M
sort_buffer_size = 48M
net_buffer_length = 1M
query_cache_type = 1
query_cache_size = 4M
join_buffer_size = 256K
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/