SQLgrey encountered an SQL error ...

tesme33 · Post by **tesme33** » 12 Feb 2019 19:32

Hi
im getting since some time the following errors:

SQLgrey encountered an SQL error and triggered a reconnection to: DBI:mysql:database=sqlgrey;host=localhost

and after some Minutes to half an hour

Code: Select all

SQLgrey established connection to: DBI:mysql:database=sqlgrey;host=localhost

This is sent to postmaster from sqlgrey.

Does anybody have the same issue ?

P.S.: The system is updated regularly via "yum update"

henk · Post by **henk** » 12 Feb 2019 20:10

If sqlgrey is having issues, there must be more errors, when this pops up. The var/log messages is a good start.
As I have some ideas why, can you post:

Code: Select all

free -m

Code: Select all

df -h

Code: Select all

find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;

Code: Select all

ls -l /var/dcc/log |wc -l

Code: Select all

ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l

tesme33 · Post by **tesme33** » 13 Feb 2019 05:58

Hi
thanks for the ideas.
Below you find the output put i dont see anomalies, perhaps as i dont have any comparision possibility.

Code: Select all

[root@efa ~]# free -m
             total       used       free     shared    buffers     cached
Mem:          3832       3241        590          1        258       1293
-/+ buffers/cache:       1689       2142
Swap:            0          0          0
[root@efa ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root
                      7.8G  2.9G  4.6G  39% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/sda1             485M  165M  296M  36% /boot
/dev/mapper/vg_00-lv_tmp
                      976M  1.7M  924M   1% /tmp
/dev/mapper/vg_00-lv_var
                       21G  3.5G   16G  19% /var
none                  1.9G   68K  1.9G   1% /var/spool/MailScanner/incoming
[root@efa ~]# find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
390M	/var/lib/mysql/
1.3M	/var/lib/mysql/mysql
32K	/var/lib/mysql/efa
46M	/var/lib/mysql/sa_bayes
8.0K	/var/lib/mysql/performance_schema
4.0K	/var/lib/mysql/temp
20M	/var/lib/mysql/mailscanner
280K	/var/lib/mysql/sqlgrey
[root@efa ~]# ls -l /var/dcc/log |wc -l
603
[root@efa ~]# ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l
1

henk · Post by **henk** » 13 Feb 2019 11:29

Looks fine to me, but as you have only 4Gb memory, you can try 2 things.
1. Add memory ( 8GB is recommended)
2. Stop or disable services.

Code: Select all

service munin-node stop

If you do not use webmin

Code: Select all

service webmin stop

Disable cron in

Code: Select all

/etc/cron.d/munin

#*/5 * * * * munin test -x /usr/bin/munin-cron && /usr/bin/munin-cron

Disable monitored services in

Code: Select all

/usr/sbin/EFA-Monitor-cron

MonitoredServices=("mysqld=mysqld" "MailScanner=mailscanner" "master=postfix" "httpd=httpd" "clamd=clamd" "unbound=unbound" "saslauthd=saslauthd" "dccifd=adcc")

If the issue is solved by these modifications, you could disable the Webmin and or munin services
Disable service

Code: Select all

chkconfig Webmin  off

Check services

Code: Select all

chkconfig --list

My memory ( As I'm also using only 4 GB)

Code: Select all

free -m
             total       used       free     shared    buffers     cached
Mem:          3961       2940       1020          1        163       1284
-/+ buffers/cache:       1492       2468
Swap:         4095          0       4095

tesme33 · Post by **tesme33** » 17 Feb 2019 06:11

Hi
i have changed the config to 8GB . but no result. I now even have the feeling that the error comes more often.

Is there a script to check the mysql database ? Or some maintenance action which could be done ?

Code: Select all

[admin@efa ~]$ free
             total       used       free     shared    buffers     cached
Mem:       8061260    3136144    4925116       1612     269864    1182640
-/+ buffers/cache:    1683640    6377620
Swap:            0          0          0

henk · Post by **henk** » 17 Feb 2019 10:15

You need to learn how to search this forum, as it contains a lot of usefull information viewtopic.php?t=2974
The logs will provide usefull info in most cases, so always check them for errors. ( /var/log/)
To check mysql issues have a look at viewtopic.php?t=2938

tesme33 · Post by **tesme33** » 17 Feb 2019 14:53

Hi
after searching around the only way was to check the mysql.

Code: Select all

more /var/lib/mysql/efa.xxx.de.err | grep ERROR

And yes some tables, incl. sqlgray, need repair.

Now going a repair via mysqlcheck repaired the tables.

Table check: --> behind all tables you should see an OK
· --check, -c

Check the tables for errors. This is the default operation.

Code: Select all

mysqlcheck -c -A -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'`

Table analyze:
· --analyze, -a

Analyze the tables.

Code: Select all

mysqlcheck --analyze -A -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'`

Table repair:
· --repair, -r

Perform a repair that can fix almost anything except unique keys that are not unique.

Code: Select all

mysqlcheck -r -A -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'`

tesme33 · Post by **tesme33** » 25 Feb 2019 18:42

After now some days of hope i can just confirm.
It didnt help. So still searching for the issue.

Mmmh

henk · Post by **henk** » 25 Feb 2019 23:49

tesme33,
Did you read the topics mentioned?
You did mention "I now even have the feeling that the error comes more often." after you increased memory to 8GB.
As feelings and facts are two different things, you could try to explain that feeling by posting the error messages and the efa version used.
You also mentioned that "some tables, incl. sqlgray, need repair." Can you explain why?

About mysql: Install mysqltuner as mentioned in the above topics and post the mysqltuner output and the errors from the mysql err file.

tesme33 · Post by **tesme33** » 26 Feb 2019 18:23

Hi
yes i was reading them. Feeling means i didnt meassure the exact time between the emails.
Before the Memory upgrade i had the issue perhaps once per day and yesterday i had the email 4 times.
According to the .err log the db is regularly shutting down.

Why some tables needed a repiar is not know to me but it might be that there was an undetected reboot.

Below you can see the behaviour which is happening quite often.

Shutdown and then a restart of the DB.
---------

Code: Select all

2019-02-16  1:19:39 139788540311584 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.38-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2019-02-23  1:00:08 139788521999104 [Note] /usr/sbin/mysqld: Normal shutdown
2019-02-23  1:00:08 139788521999104 [Note] Event Scheduler: Purging the queue. 0 events
2019-02-23  1:00:08 139787776263936 [Note] InnoDB: FTS optimize thread exiting.
2019-02-23  1:00:08 139788521999104 [Note] InnoDB: Starting shutdown...
2019-02-23  1:00:09 139788521999104 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2019-02-23  1:00:11 139788521999104 [Note] InnoDB: Shutdown completed; log sequence number 21801137359
2019-02-23  1:00:11 139788521999104 [Note] /usr/sbin/mysqld: Shutdown complete

190223 01:00:11 mysqld_safe mysqld from pid file /var/lib/mysql/efa.stuebiland.de.pid ended

And then immediatly:

Code: Select all

190223 01:20:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2019-02-23  1:20:02 139914126166048 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 1605 ...
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: The InnoDB memory heap is disabled
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Using Linux native AIO
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Using SSE crc32 instructions
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Completed initialization of buffer pool
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Highest supported file format is Barracuda.
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: 128 rollback segment(s) are active.
2019-02-23  1:20:03 139914126166048 [Note] InnoDB: Waiting for purge to start
2019-02-23  1:20:03 139914126166048 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 21801137359
2019-02-23  1:20:03 139913309710080 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-02-23  1:20:03 139914126166048 [Note] Plugin 'FEEDBACK' is disabled.
2019-02-23  1:20:03 139914126166048 [Note] Server socket created on IP: '::'.
2019-02-23  1:20:03 139914126166048 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.38-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

------

below the output of mysqltuner
--------

Code: Select all

[root@efa ~]# perl mysqltuner.pl --buffers
 >>  MySQLTuner 1.7.14 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.1.38-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/efa.xxx.de.err(828K)
[OK] Log file /var/lib/mysql/efa.xxx.de.err exists
[OK] Log file /var/lib/mysql/efa.xxx.de.err is readable.
[OK] Log file /var/lib/mysql/efa.xxx.de.err is not empty
[OK] Log file /var/lib/mysql/efa.xxx.de.err is smaller than 32 Mb
[!!] /var/lib/mysql/efa.xxx.de.err contains 163 warning(s).
[!!] /var/lib/mysql/efa.xxx.de.err contains 180 error(s).
[--] 103 start(s) detected in /var/lib/mysql/efa.xxx.de.err
[--] 1) 2019-02-23  1:20:03 139914126166048 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-02-16  1:19:39 139788540311584 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-02-14 16:41:22 139737773471776 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-02-10  8:34:03 140479839934496 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-02-09  1:19:38 140630948083744 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-02-02  1:19:44 139817901455392 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-01-26  1:19:55 139916138141728 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2019-01-19  1:19:16 140548929013792 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-01-12  1:19:14 139802294544416 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-01-05  1:19:13 140665433307168 [Note] /usr/sbin/mysqld: ready for connections.
[--] 71 shutdown(s) detected in /var/lib/mysql/efa.stuebiland.de.err
[--] 1) 2019-02-23  1:00:11 139788521999104 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2019-02-16  1:00:11 139737772411648 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2019-02-14 16:39:21 140479838710528 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2019-02-10  8:34:02 140630929476352 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2019-02-09  1:00:11 139817901026048 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2019-02-02  1:00:11 139916119534336 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2019-01-26  1:00:12 140548091509504 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2019-01-19  1:00:11 139802275937024 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2019-01-12  1:00:12 140665432386304 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2019-01-05  1:00:12 140518087629568 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 232.2K (Tables: 9)
[--] Data in InnoDB tables: 40.6M (Tables: 21)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 17h 43m 35s (72K q [0.226 qps], 1K conn, TX: 7M, RX: 11M)
[--] Reads / Writes: 48% / 52%
[--] Binary logging is disabled
[--] Physical Memory     : 7.7G
[--] Max MySQL memory    : 864.0M
[--] Other process memory: 0B
[--] Total buffers: 425.0M global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[--] Global Buffers
[--]  +-- Key Buffer: 128.0M
[--]  +-- Max Tmp Table: 16.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: OFF - DISABLED
[--]  +-- Query Cache Size: 1.0M
[--] Per Thread Buffers
[--]  +-- Read Buffer: 128.0K
[--]  +-- Read RND Buffer: 256.0K
[--]  +-- Sort Buffer: 2.0M
[--]  +-- Thread stack: 289.0K
[--]  +-- Join Buffer: 256.0K
[OK] Maximum reached memory usage: 442.4M (5.62% of installed RAM)
[OK] Maximum possible memory usage: 864.0M (10.98% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/72K)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.20%  (3/1493)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 32K selects)
[OK] Query cache prunes per day: 0
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 29 total)
[!!] Thread cache is disabled
[OK] Open file limit used: 0% (5/4K)
[OK] Table locks acquired immediately: 100% (66K immediate / 66K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.1.38-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/245.0K
[OK] Read Key buffer hit rate: 99.9% (149K cached / 151 reads)
[!!] Write Key buffer hit rate: 69.3% (15K cached / 10K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--]  +-- InnoDB Buffer Pool: 128.0M
[--]  +-- InnoDB Buffer Pool Instances: 8
[--]  +-- InnoDB Additional Mem Pool: 8.0M
[--]  +-- InnoDB Log File Size: 48.0M
[--]  +-- InnoDB Log File In Group: 2
[--]  +-- InnoDB Total Log File Size: 96.0M(75 % of buffer pool)
[--]  +-- InnoDB Log Buffer: 16.0M
[--]  +-- InnoDB Log Buffer Free: 2.6K
[--]  +-- InnoDB Log Buffer Used: 8.0K
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/40.6M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.94% (3775450 hits/ 3777647 total)
[!!] InnoDB Write Log efficiency: 89.02% (265748 hits/ 298527 total)
[OK] InnoDB log waits: 0.00% (0 waits / 32779 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/efa.xxx.de.err file
    Control error line(s) into /var/lib/mysql/efa.xxx.de.err file
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Set thread_cache_size to 4 as a starting value
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    thread_cache_size (start at 4)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

-----

ERROR in .err

----

Code: Select all

170326 14:08:25 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
170326 14:08:25 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
170326 14:08:25 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
170326 14:08:25 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
170326 14:08:25 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2017-03-26 14:12:37 140464932476960 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
2017-03-26 14:16:29 140336478096128 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-03-26 14:16:29 140336478096128 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-03-26 14:16:29 140336478096128 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-03-26 14:16:30 140336478096128 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-03-26 14:45:11 140336477793024 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-03-31 17:42:59 140648105954048 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-03-31 17:43:00 140648105954048 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-03-31 17:43:00 140648105954048 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-03-31 17:43:01 140648105954048 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-03-31 18:20:30 140648105650944 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-04-14 19:19:26 140693330893568 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-04-14 19:19:27 140693330893568 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-04-14 19:19:27 140693330893568 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-04-14 19:19:27 140693330893568 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-04-14 19:25:40 140693331196672 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-04-28 19:23:02 140510100003584 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-04-28 19:23:03 140510100003584 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-04-28 19:23:03 140510100003584 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-04-28 19:23:03 140510100003584 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-04-28 22:18:06 140510100609792 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-05-12 20:10:27 140515427748608 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-05-12 20:10:27 140515427748608 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-05-12 20:10:27 140515427748608 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-05-12 20:10:27 140515427748608 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-05-12 20:44:07 140515427445504 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-05-26 20:08:59 139628532660992 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-05-26 20:09:00 139628532660992 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-05-26 20:09:00 139628532660992 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-05-26 20:09:00 139628532660992 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-05-26 20:28:17 139628532054784 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-06-09 21:44:35 139630541961984 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-06-09 21:44:36 139630541961984 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-06-09 21:44:36 139630541961984 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-06-09 21:44:36 139630541961984 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-06-09 21:51:51 139630541658880 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-06-23 22:32:04 140396144810752 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-06-23 22:32:05 140396144810752 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-06-23 22:32:05 140396144810752 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-06-23 22:32:06 140396144810752 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-06-23 22:33:38 140396144507648 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-07-07 23:19:25 139859971853056 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-07-07 23:19:26 139859971853056 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-07-07 23:19:26 139859971853056 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-07-07 23:19:26 139859971853056 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-07-08  2:54:03 139859971549952 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-07-22  0:06:14 140207638194944 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-07-22  0:06:15 140207638194944 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-07-22  0:06:15 140207638194944 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-07-22  0:06:15 140207638194944 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-07-22  1:15:07 140207638498048 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-08-05  0:53:58 140028170189568 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-08-05  0:53:59 140028170189568 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-08-05  0:53:59 140028170189568 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-08-05  0:53:59 140028170189568 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-08-05  3:14:01 140028170795776 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-08-19  1:40:47 140100628957952 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-08-19  1:40:48 140100628957952 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-08-19  1:40:48 140100628957952 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-08-19  1:40:49 140100628957952 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-08-19  3:00:37 140100629261056 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-09-02  2:28:15 139823726934784 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-09-02  2:28:15 139823726934784 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-09-02  2:28:15 139823726934784 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-09-02  2:28:16 139823726934784 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-09-02  2:54:23 139823726328576 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-09-16  3:15:47 139666399963904 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-09-16  3:15:47 139666399963904 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-09-16  3:15:47 139666399963904 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-09-16  3:15:48 139666399963904 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-09-16  3:22:56 139666399660800 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-09-30  4:03:24 140334564993792 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-09-30  4:03:25 140334564993792 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-09-30  4:03:26 140334564993792 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-09-30  4:21:19 140334564690688 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-10-14  4:50:36 140199296629504 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-10-14  4:50:37 140199296629504 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-10-14  4:50:37 140199296629504 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-10-14  4:50:38 140199296629504 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-10-14  6:08:05 140199296326400 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-10-28  5:37:14 139644172790528 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-10-28  5:37:15 139644172790528 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-10-28  5:37:15 139644172790528 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-10-28  5:37:16 139644172790528 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-10-28  6:08:59 139644172487424 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-11-11  5:24:28 140064131599104 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-11-11  5:24:28 140064131599104 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-11-11  5:24:28 140064131599104 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-11-11  5:24:29 140064131599104 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-11-11  6:07:39 140064130992896 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-11-25  5:28:26 139852443978496 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-11-25  5:28:27 139852443978496 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-11-25  5:28:27 139852443978496 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-11-25  5:28:27 139852443978496 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-11-25  6:06:01 139852444281600 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-12-09  6:15:42 140352427752192 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-12-09  6:15:43 140352427752192 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-12-09  6:15:43 140352427752192 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-12-09  6:15:44 140352427752192 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-12-09  7:02:36 140352427449088 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2017-12-23  7:02:47 140219395824384 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2017-12-23  7:02:48 140219395824384 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2017-12-23  7:02:48 140219395824384 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2017-12-23  7:02:48 140219395824384 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2017-12-23  7:06:28 140219395521280 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-01-06  7:01:36 139684808022784 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-01-06  7:01:36 139684808022784 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-01-06  7:01:36 139684808022784 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-01-06  7:01:37 139684808022784 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-01-06  7:02:06 139684807719680 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-01-20  7:49:24 140257093176064 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-01-20  7:49:24 140257093176064 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-01-20  7:49:25 140257093176064 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-01-20  7:49:25 140257093176064 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-01-20  8:08:51 140257092569856 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-02-03  8:35:37 140089961061120 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-02-03  8:37:01 140089961061120 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-02-03  8:37:01 140089961061120 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-02-03  8:37:02 140089961061120 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-02-03  8:37:02 140089961061120 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-02-17  9:25:43 140610730400512 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-02-17  9:25:43 140610730400512 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-02-17  9:25:44 140610730400512 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-02-17  9:25:44 140610730400512 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-02-17 10:09:31 140610730097408 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-03-03 10:16:25 140675356687104 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-03-03 10:16:26 140675356687104 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-03-03 10:16:26 140675356687104 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-03-03 10:16:26 140675356687104 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-03-03 11:18:25 140675356080896 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-03-17 11:03:40 139883142490880 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-03-17 11:03:40 139883142490880 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-03-17 11:03:41 139883142490880 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-03-17 11:03:41 139883142490880 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-03-17 11:36:23 139883142187776 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-03-31 12:46:50 139896396401408 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-03-31 12:46:51 139896396401408 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-03-31 12:46:51 139896396401408 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-03-31 12:46:52 139896396401408 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-03-31 14:13:11 139896396098304 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-04-14 13:32:00 140621161290496 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-04-14 13:32:00 140621161290496 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-04-14 13:32:01 140621161290496 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-04-14 13:32:01 140621161290496 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-04-14 15:00:00 140621161593600 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-04-28 14:19:47 140107271125760 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-04-28 14:19:48 140107271125760 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-04-28 14:19:49 140107271125760 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-04-28 14:19:49 140107271125760 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-04-28 18:48:10 140107270822656 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-05-12 15:06:57 139903362689792 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-05-12 15:06:57 139903362689792 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-05-12 15:06:58 139903362689792 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-05-12 15:06:58 139903362689792 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-05-12 15:28:46 139903362386688 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired
2018-05-28 15:47:11 139746730797824 [ERROR] mysqld: Table './sqlgrey/from_awl' is marked as crashed and should be repaired
2018-05-28 15:47:12 139746730797824 [ERROR] mysqld: Table './sqlgrey/domain_awl' is marked as crashed and should be repaired
2018-05-28 15:47:12 139746730797824 [ERROR] mysqld: Table './sqlgrey/connect' is marked as crashed and should be repaired
2018-05-28 15:47:12 139746730797824 [ERROR] mysqld: Table './sqlgrey/config' is marked as crashed and should be repaired
2018-05-28 16:02:39 139746730494720 [ERROR] mysqld: Table './efa/tokens' is marked as crashed and should be repaired

tesme33 · Post by **tesme33** » 26 Feb 2019 18:41

i forgot to mention that im on EFA-3.0.2.6.

henk · Post by **henk** » 26 Feb 2019 22:08

spontaneously rebooting mysql..

The errors mentioned in ERROR in .err are from 2017-03-26 till 2018-05-28. You had some serious issues back then. Upgrade went wrong?

In the log: 2019-02-23 1:20:03 139914126166048 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

And Mysqltuner:
[OK] InnoDB buffer pool / data size: 128.0M/40.6M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).

First lets start to fix your mysql conf

Stop mail flow.
Stop mailscanner service
Stop mysql service

/etc/my.cnf.d/server.cnf

Add the settings below. Watch the sections [mysqld] and [mariadb-10.1] !!

Code: Select all

# this is only for the mysqld standalone daemon
[mysqld]

thread_cache_size = 4
query_cache_type = 0
query_cache_size = 0M
max_heap_table_size = 32M
tmp_table_size = 32M
sort_buffer_size = 2M
tmpdir = /tmp

[mariadb-10.1]
innodb-defragment = 1
innodb_file_per_table = 1
innodb_buffer_pool_size= 256M
innodb_log_file_size = 32M
innodb_buffer_pool_instances = 1

Start mysql service This mysql version will resize the logs automatic. Just watch the log.
Start mailscanner service
Start mail flow.

To be able to use mysql without typing passwords.

cat /etc/EFA-Config |grep MYSQLROOTPWD

MYSQLROOTPWD:>>>>this is your mysqlpwd<<<

create .my.cnf file as user root ( notice the dot in the file name)

vi ~/.my.cnf

Code: Select all

[client]
user=root
password=< your mysqlpwd>

Also check

Code: Select all

myisamchk --check /var/lib/mysql/*/*.MYI

run mysqltuner again
and check all databases
mysqlcheck --analyze -A

P.S.
The upgrade logs could help to understand what went wrong

update:
There is a difference in the number of tables between your mysql and mine???

your mysql
[--] Data in MyISAM tables: 232.2K (Tables: 9)
[--] Data in InnoDB tables: 40.6M (Tables: 21)

my mysql
[--] Data in MyISAM tables: 65K (Tables: 1)
[--] Data in InnoDB tables: 442M (Tables: 29)

elfranko · Post by **elfranko** » 02 Mar 2019 12:25

I have been having these for months. - only on one of the two EFA's I use. Can sometimes get 60+ at a time.

If you want some logs I can probably find some

henk · Post by **henk** » 02 Mar 2019 12:42

I have been having these for months. - only on one of the two EFA's I use. Can sometimes get 60+ at a time.

You mean error messages? The to Efa's are the same (Efa-version/OS/packages) version?

You could provide the output for the same tests asked to tesme33.

Some recent log entries would help and sure the the mysqltuner ouput.

elfranko · Post by **elfranko** » 02 Mar 2019 13:31

Will do, Yes I have been getting them - to be fair the box with the errors is probably 100 times busier than the one that doesn't. If I recall correctly it happened after an update. It's quite random and more than anything it's just annoying - the volume of email it generates
One email contains:
SQLgrey established connection to: DBI:mysql:database=sqlgrey;host=localhost
The other is:
SQLgrey encountered an SQL error and triggered a reconnection to: DBI:mysql:database=sqlgrey;host=localhost

Code: Select all

Free -m

Code: Select all

             total       used       free     shared    buffers     cached
Mem:         15950      11503       4446          6        265       7246
-/+ buffers/cache:       3991      11959
Swap:         4095        249       3846

Code: Select all

df -h

Code: Select all

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root
                      7.8G  2.5G  5.0G  33% /
tmpfs                 7.8G     0  7.8G   0% /dev/shm
/dev/sda1             485M  108M  353M  24% /boot
/dev/mapper/vg_00-lv_tmp
                      9.8G   40M  9.2G   1% /tmp
/dev/mapper/vg_00-lv_var
                      168G   77G   82G  49% /var
none                  7.8G  4.9M  7.8G   1% /var/spool/MailScanner/incoming

Code: Select all

find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;

Code: Select all

5.6G    /var/lib/mysql/
4.0K    /var/lib/mysql/temp
8.0K    /var/lib/mysql/performance_schema
5.1G    /var/lib/mysql/mailscanner
245M    /var/lib/mysql/sa_bayes
44M     /var/lib/mysql/sqlgrey
3.0M    /var/lib/mysql/efa
736K    /var/lib/mysql/opendmarc
1.2M    /var/lib/mysql/mysql

Code: Select all

ls -l /var/dcc/log |wc -l

Code: Select all

ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l

Code: Select all

These are the outputs from the commands - I will get the MySQL logs later.

Cheers
Elfrank0

henk · Post by **henk** » 02 Mar 2019 14:41

The massive amount ( 147148 ) off dcc logiles show the dcc cleanup cronjob in not in the daily cron, but in the montly cron.

You need to move the montly dcc cronjob to the daily cron.

to remove most logfiles exec:

Code: Select all

find /var/dcc/log -mtime +1 -print | xargs rm -f

info viewtopic.php?t=2610

Somehow I try to understand why you use 77G in /var (168G 77G 82G 49% /var)
And use swap?? (Swap: 4095 249 3846)

elfranko · Post by **elfranko** » 02 Mar 2019 15:15

henk wrote: ↑
02 Mar 2019 14:41
The massive amount ( 147148 ) off dcc logiles show the dcc cleanup cronjob in not in the daily cron, but in the montly cron.

You need to move the montly dcc cronjob to the daily cron.

to remove most logfiles exec:
Code: Select all
find /var/dcc/log -mtime +1 -print | xargs rm -f
info viewtopic.php?t=2610

Somehow I try to understand why you use 77G in /var (168G 77G 82G 49% /var)
And use swap?? (Swap: 4095 249 3846)

I have copied the cron job - we shall see if that number is a touch lower. The system receives in the order of 20,000 emails per day M-F and approx. 5000 on the weekend days.

Will update you in the morning.

Thank you for you input so far

Frank

tesme33 · Post by **tesme33** » 10 Mar 2019 10:04

henk wrote: ↑
26 Feb 2019 22:08
spontaneously rebooting mysql..

The errors mentioned in ERROR in .err are from 2017-03-26 till 2018-05-28. You had some serious issues back then. Upgrade went wrong?

P.S.
The upgrade logs could help to understand what went wrong

update:
There is a difference in the number of tables between your mysql and mine???

your mysql
[--] Data in MyISAM tables: 232.2K (Tables: 9)
[--] Data in InnoDB tables: 40.6M (Tables: 21)

my mysql
[--] Data in MyISAM tables: 65K (Tables: 1)
[--] Data in InnoDB tables: 442M (Tables: 29)

Hi
today i had time to implement the mentioned steps.
Now lets see if the behaviour will change. But anyhow thanks for the good and helpful feedback.

I have not done any manual change in the EFA VM, expect the normal configuration and SASL auth for external mailservers, and i started with EFA3.0.0.? This might be the difference between your amount of tables and mine.

But isn't it dangerous to set /tmp as tempdir for the database ? Especially as i only have 1GB there ?

Looking into /var/EFA/backup i see that the ERRORS start dates match the the upgrade to 3.0.1.9 in March 2017. But the restart messages i only get since august 2018. Might be that some tables got full.

Thx

mysqllog:

Code: Select all

2019-03-10 10:25:09 140415135800064 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2019-03-10 10:25:10 140415135800064 [Note] InnoDB: Shutdown completed; log sequence number 22228532036
2019-03-10 10:25:10 140415135800064 [Note] /usr/sbin/mysqld: Shutdown complete

190310 10:25:10 mysqld_safe mysqld from pid file /var/lib/mysql/efa.xxx.de.pid ended
190310 10:30:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2019-03-10 10:30:37 140406826125344 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 14945 ...
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: The InnoDB memory heap is disabled
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Using Linux native AIO
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Using SSE crc32 instructions
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Completed initialization of buffer pool
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Highest supported file format is Barracuda.
2019-03-10 10:30:37 140406826125344 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*2048 pages, LSN=22228532036
2019-03-10 10:30:37 140406826125344 [Warning] InnoDB: Starting to delete and rewrite log files.
2019-03-10 10:30:37 140406826125344 [Note] InnoDB: Setting log file ./ib_logfile101 size to 32 MB
2019-03-10 10:30:38 140406826125344 [Note] InnoDB: Setting log file ./ib_logfile1 size to 32 MB
2019-03-10 10:30:38 140406826125344 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2019-03-10 10:30:38 140406826125344 [Warning] InnoDB: New log files created, LSN=22228532236
2019-03-10 10:30:38 140406826125344 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-10 10:30:38 140406826125344 [Note] InnoDB: Waiting for purge to start
2019-03-10 10:30:38 140406826125344 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 22228532036
2019-03-10 10:30:38 140405857318656 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-10 10:30:38 140406826125344 [Note] Plugin 'FEEDBACK' is disabled.
2019-03-10 10:30:38 140406826125344 [Note] Server socket created on IP: '::'.
2019-03-10 10:30:38 140406826125344 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.38-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

mysqltuner:

Code: Select all

[root@efa ~]# perl mysqltuner.pl --buffers
 >>  MySQLTuner 1.7.14 - Major Hayden <major@mhtx.net>                                  
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/             
 >>  Run with '--help' for additional options and output filtering                      
                                                                                        
[--] Skipped version check for MySQLTuner script                                        
Please enter your MySQL administrative login: root                                      
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 10.1.38-MariaDB
[OK] Operating on 64-bit architecture                                                   
                                                                                        
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/efa.stuebiland.de.err(836K)                               
[OK] Log file /var/lib/mysql/efa.xxx.de.err exists                               
[OK] Log file /var/lib/mysql/efa.xxx.de.err is readable.                         
[OK] Log file /var/lib/mysql/efa.xxx.de.err is not empty                         
[OK] Log file /var/lib/mysql/efa.xxx.de.err is smaller than 32 Mb                
[!!] /var/lib/mysql/efa.xxx.de.err contains 166 warning(s).                      
[!!] /var/lib/mysql/efa.xxx.de.err contains 180 error(s).                        
[--] 106 start(s) detected in /var/lib/mysql/efa.stuebiland.de.err                      
[--] 1) 2019-03-10 10:30:38 140406826125344 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2019-03-09  1:20:32 140415137163296 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2019-03-02  1:20:43 139743736408096 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2019-02-23  1:20:03 139914126166048 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2019-02-16  1:19:39 139788540311584 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2019-02-14 16:41:22 139737773471776 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2019-02-10  8:34:03 140479839934496 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2019-02-09  1:19:38 140630948083744 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2019-02-02  1:19:44 139817901455392 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2019-01-26  1:19:55 139916138141728 [Note] /usr/sbin/mysqld: ready for connections.
[--] 74 shutdown(s) detected in /var/lib/mysql/efa.stuebiland.de.err                    
[--] 1) 2019-03-10 10:25:10 140415135800064 [Note] /usr/sbin/mysqld: Shutdown complete  
[--] 2) 2019-03-09  1:00:11 139742889999104 [Note] /usr/sbin/mysqld: Shutdown complete  
[--] 3) 2019-03-02  1:00:12 139914126154496 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2019-02-23  1:00:11 139788521999104 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2019-02-16  1:00:11 139737772411648 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2019-02-14 16:39:21 140479838710528 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2019-02-10  8:34:02 140630929476352 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2019-02-09  1:00:11 139817901026048 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2019-02-02  1:00:11 139916119534336 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2019-01-26  1:00:12 140548091509504 [Note] /usr/sbin/mysqld: Shutdown complete
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 257.1K (Tables: 9)
[--] Data in InnoDB tables: 40.9M (Tables: 21)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8m 3s (173 q [0.358 qps], 58 conn, TX: 163K, RX: 17K)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 7.7G
[--] Max MySQL memory    : 991.0M
[--] Other process memory: 0B
[--] Total buffers: 552.0M global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[--] Global Buffers
[--]  +-- Key Buffer: 128.0M
[--]  +-- Max Tmp Table: 16.0M
[--] Query Cache Buffers
[--]  +-- Query Cache: OFF - DISABLED
[--]  +-- Query Cache Size: 0B
[--] Per Thread Buffers
[--]  +-- Read Buffer: 128.0K
[--]  +-- Read RND Buffer: 256.0K
[--]  +-- Sort Buffer: 2.0M
[--]  +-- Thread stack: 289.0K
[--]  +-- Join Buffer: 256.0K
[OK] Maximum reached memory usage: 563.6M (7.16% of installed RAM)
[OK] Maximum possible memory usage: 991.0M (12.59% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/173)
[OK] Highest usage of available connections: 2% (4/151)
[!!] Aborted connections: 5.17%  (3/58)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[!!] Joins performed without indexes: 6
[OK] Temporary tables created on disk: 4% (1 on disk / 25 total)
[OK] Thread cache hit rate: 93% (4 created / 58 connections)
[OK] Table cache hit rate: 94% (113 open / 119 opened)
[OK] Open file limit used: 1% (79/4K)
[OK] Table locks acquired immediately: 100% (75 immediate / 75 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.1.38-MariaDB)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/261.0K
[!!] Read Key buffer hit rate: 86.0% (178 cached / 25 reads)
[OK] Write Key buffer hit rate: 100.0% (9 cached / 9 writes)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--]  +-- InnoDB Buffer Pool: 256.0M
[--]  +-- InnoDB Buffer Pool Instances: 1
[--]  +-- InnoDB Additional Mem Pool: 8.0M
[--]  +-- InnoDB Log File Size: 32.0M
[--]  +-- InnoDB Log File In Group: 2
[--]  +-- InnoDB Total Log File Size: 64.0M(25 % of buffer pool)
[--]  +-- InnoDB Log Buffer: 16.0M
[--]  +-- InnoDB Log Buffer Free: 15.5K
[--]  +-- InnoDB Log Buffer Used: 16.0K
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 256.0M/40.9M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 32.0M * 2/256.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 77.47% (1589 hits/ 2051 total)
[!!] InnoDB Write Log efficiency: 0% (4 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 98.2% (57 cached / 1 reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/efa.xxx.de.err file
    Control error line(s) into /var/lib/mysql/efa.xxx.de.err file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    performance_schema = ON enable PFS

mysqlcheck:

Code: Select all

[root@efa ~]# mysqlcheck --analyze -A -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'`
efa.tokens                                         OK
mailscanner.audit_log                              OK
mailscanner.autorelease                            OK
mailscanner.blacklist                              OK
mailscanner.inq                                    OK
mailscanner.maillog                                OK
mailscanner.mcp_rules                              OK
mailscanner.mtalog                                 OK
mailscanner.mtalog_ids                             OK
mailscanner.outq                                   OK
mailscanner.sa_rules                               OK
mailscanner.saved_filters                          OK
mailscanner.user_filters                           OK
mailscanner.users                                  OK
mailscanner.whitelist                              OK
mysql.column_stats                                 Table is already up to date
mysql.columns_priv                                 Table is already up to date
mysql.db                                           OK
mysql.event                                        Table is already up to date
mysql.func                                         Table is already up to date
mysql.gtid_slave_pos                               OK
mysql.help_category                                Table is already up to date
mysql.help_keyword                                 Table is already up to date
mysql.help_relation                                Table is already up to date
mysql.help_topic                                   Table is already up to date
mysql.host                                         Table is already up to date
mysql.index_stats                                  Table is already up to date
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             Table is already up to date
mysql.plugin                                       Table is already up to date
mysql.proc                                         OK
mysql.procs_priv                                   Table is already up to date
mysql.proxies_priv                                 Table is already up to date
mysql.roles_mapping                                Table is already up to date
mysql.servers                                      Table is already up to date
mysql.table_stats                                  Table is already up to date
mysql.tables_priv                                  Table is already up to date
mysql.time_zone                                    Table is already up to date
mysql.time_zone_leap_second                        Table is already up to date
mysql.time_zone_name                               Table is already up to date
mysql.time_zone_transition                         Table is already up to date
mysql.time_zone_transition_type                    Table is already up to date
mysql.user                                         OK
sa_bayes.awl                                       OK
sa_bayes.bayes_expire                              OK
sa_bayes.bayes_global_vars                         OK
sa_bayes.bayes_seen                                OK
sa_bayes.bayes_token                               OK
sa_bayes.bayes_vars                                OK
sa_bayes.txrep                                     OK
sqlgrey.config                                     OK
sqlgrey.connect                                    OK
sqlgrey.domain_awl                                 OK
sqlgrey.from_awl                                   OK
sqlgrey.optin_domain                               Table is already up to date
sqlgrey.optin_email                                Table is already up to date
sqlgrey.optout_domain                              Table is already up to date
sqlgrey.optout_email                               Table is already up to date
[root@efa ~]#

henk · Post by **henk** » 10 Mar 2019 17:02

today i had time to implement the mentioned steps.

Could you mention the steps done?

isn't it dangerous to set /tmp as tempdir for the database ? Especially as i only have 1GB there ?

the tempdir is for temp tables ( used for grouping/sorting..) Your db is very small , do not worry about that

Code: Select all

[--] Data in MyISAM tables: 257.1K (Tables: 9)
[--] Data in InnoDB tables: 40.9M (Tables: 21)

Besides the --analyze, a --optimize can run if mysqltuner shows defragmentation. ( on a quiet moment )

Code: Select all

mysqlcheck --optimize -A -uroot -p`grep ROOT /etc/EFA-Config | awk -F: '{print $2}'`

Tip. Save the mysql log file and then clear the file. ( or use the default log location in /var/log, so logrotate manages the log file)

As for the difference in the number of MYISAM/INNODB tables. Seems I use more Innodb tables than you. No issue, forget it.

tesme33 · Post by **tesme33** » 10 Mar 2019 17:28

henk wrote: ↑
10 Mar 2019 17:02

today i had time to implement the mentioned steps.
Could you mention the steps done?

Hi
sorry. I ment the steps you proposed to update the server configuration. And now the db seems to be stable.
At least i dont see shutdowns. But nevertheless i get the reconnect error emails. Just had 3 reconnects at 16:37 (local time) within one minute.

Optimazation is done, as the system is not under heavy load.
I would assume thats the reason why my reconnects are not so often as for efranko.

I realized that the error (sqlgray reconnects) happened exactly when a message came in and mailScanner was running:

Mar 10 16:37:18 efa clamd[1482]: /var/spool/MailScanner/incoming/20871/2F8CCA056
F.AE245/nmsg-20871-1.html: Sanesecurity.Scam.12561.UNOFFICIAL FOUND

henk · Post by **henk** » 10 Mar 2019 20:40

At least your mysql is running fine now

The last check I can think off:

Could you compair

Code: Select all

grep SQLGREYSQLPWD /etc/EFA-Config | awk -F: '{print $2}'

with db_pass in

Code: Select all

/etc/sqlgrey/sqlgrey.conf

db_pass = <<<must be the same as in efa config >>>>>

This must work

Code: Select all

mysql  -usqlgrey -p`grep SQLGREYSQLPWD /etc/EFA-Config | awk -F: '{print $2}'` -D sqlgrey

Check service is running

Code: Select all

service sqlgrey status

Update

: Just found this by one single google search: viewtopic.php?t=2894

tesme33 · Post by **tesme33** » 13 Mar 2019 09:21

Hi
the passwords match. And since 3 days no reconnect emails.

Looks like the defragmentation made the difference. Strange. This system is not realy under load.

What i see that the connections are sleeping, which is OK. But who decides to reconnect ? I normaly know from other systems is that the conenctions idle for more then 5min get disconnected.

Code: Select all

+------+-----------+-----------+-------------+---------+------+-------+------------------+----------+
| Id   | User      | Host      | db          | Command | Time | State | Info             | Progress |
+------+-----------+-----------+-------------+---------+------+-------+------------------+----------+
| 212  | sqlgrey   | localhost | sqlgrey     | Sleep   | 303  |       |                  | 0.000    |
| 2106 | mailwatch | localhost | mailscanner | Sleep   | 409  |       |                  | 0.000    |
| 2126 | root      | localhost |             | Query   | 0    | init  | show processlist | 0.000    |
+------+-----------+-----------+-------------+---------+------+-------+------------------+----------+

Thx for your help.

tesme33 · Post by **tesme33** » 13 Mar 2019 18:45

I should have written that i have no issue. 5h after my post again DB reconnects.
But in the efa.xxx.de.err no log entries.
But found this in maillog at this point in time.

Mar 13 15:07:14 efa sqlgrey: warning: Use of uninitialized value $DBI::errstr in concatenation (.) or string at /usr/sbin/sqlgrey line 1213.
Mar 13 15:07:14 efa sqlgrey: dbaccess: error: couldn't access from_awl table:
Mar 13 15:07:14 efa sqlgrey: grey: from awl match: updating 78.136.243.5(78.136.243.5), arthur<feff>smith@strelatelecom.ru(arthur<feff>smith@strelatelecom.ru)
Mar 13 15:07:14 efa sqlgrey: dbaccess: warning: couldn't do query:#012UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE sender_name = 'arthur<feff>smith' AND sender_domain = 'strelatelecom.ru' AND src = '78.136.243.5':#012Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=', reconnecting to DB

Now i checked:

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+

Now the question is what is the right direction. Can somebody check how his DB is configured ?

Code: Select all

MariaDB [(none)]> use sqlgrey;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sqlgrey]> show table status \G
*************************** 1. row ***************************
           Name: config
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 24
    Data_length: 48
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-03-13 19:19:58
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: connect
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 299
 Avg_row_length: 87
    Data_length: 27240
Max_data_length: 281474976710655
   Index_length: 32768
      Data_free: 1140
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-03-13 19:36:00
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: domain_awl
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 169
 Avg_row_length: 39
    Data_length: 6696
Max_data_length: 281474976710655
   Index_length: 13312
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-03-13 17:40:24
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 4. row ***************************
           Name: from_awl
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 632
 Avg_row_length: 54
    Data_length: 34560
Max_data_length: 281474976710655
   Index_length: 58368
      Data_free: 112
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:15
    Update_time: 2019-03-13 19:20:17
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 5. row ***************************
           Name: optin_domain
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-02-17 15:47:33
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 6. row ***************************
           Name: optin_email
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-02-17 15:47:33
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 7. row ***************************
           Name: optout_domain
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-02-17 15:47:33
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 8. row ***************************
           Name: optout_email
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-21 20:34:16
    Update_time: 2019-02-17 15:47:33
     Check_time: 2019-03-10 18:13:46
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
8 rows in set (0.00 sec)

I believ this exactly the same error which is mentioned here: viewtopic.php?f=13&t=3216&p=12941&hilit ... _ci#p12941

I will now check if this helps: https://airbladesoftware.com/notes/fixi ... ollations/

below the efa.xxx.de.err.

Code: Select all

2019-03-10 10:30:38 140406826125344 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2019-03-10 10:30:38 140406826125344 [Warning] InnoDB: New log files created, LSN=22228532236
2019-03-10 10:30:38 140406826125344 [Note] InnoDB: 128 rollback segment(s) are active.
2019-03-10 10:30:38 140406826125344 [Note] InnoDB: Waiting for purge to start
2019-03-10 10:30:38 140406826125344 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequence number 22228532036
2019-03-10 10:30:38 140405857318656 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-03-10 10:30:38 140406826125344 [Note] Plugin 'FEEDBACK' is disabled.
2019-03-10 10:30:38 140406826125344 [Note] Server socket created on IP: '::'.
2019-03-10 10:30:38 140406826125344 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.1.38-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server

tesme33 · Post by **tesme33** » 13 Mar 2019 19:01

Mmmh thinking further the solution should be easy.
This is sqlgrey db. So no content which is not auomatically handeld.

Shouldnt it work to throw away the db, recreate with correct values . and thats it ?

henk · Post by **henk** » 13 Mar 2019 19:43

Glad you have an error visible now.
Truncate and modify the sqlgrey db is one option, but to bypass your problem , why not change from AWL to TXREP?

Just need a few changes viewtopic.php?t=3334

The main reason why in the link you mentioned:

The caveats come with the data you have in the DB and the charset it was put in as and what the charset is when you take it out and frankly it's a minefield. This extends to the newer versions as well and when you make the database and connection charset changes all sorts of weird things may happen when the DB tries to compare pre-change records with post-change ones; what was valid UTF8 may now be read as ASCII single-byte garbage and will lead to trouble.

Efa4 is using TxRep by default.

SQLgrey encountered an SQL error ...

SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...

Re: SQLgrey encountered an SQL error ...