Page 1 of 2
SQLgrey encountered an SQL error ...
Posted: 12 Feb 2019 19:32
by tesme33
Hi
im getting since some time the following errors:
Code: Select all
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"
Re: SQLgrey encountered an SQL error ...
Posted: 12 Feb 2019 20:10
by henk
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
find /var/lib/mysql/ -maxdepth 1 -type d ! -name '.' -exec du -sh {} \;
Code: Select all
ls -l /var/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l
Re: SQLgrey encountered an SQL error ...
Posted: 13 Feb 2019 05:58
by tesme33
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
Re: SQLgrey encountered an SQL error ...
Posted: 13 Feb 2019 11:29
by henk
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.
If you do not use webmin
Disable cron in
#*/5 * * * * munin test -x /usr/bin/munin-cron && /usr/bin/munin-cron
Disable monitored services in
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
Check services
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
Re: SQLgrey encountered an SQL error ...
Posted: 17 Feb 2019 06:11
by tesme33
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
Re: SQLgrey encountered an SQL error ...
Posted: 17 Feb 2019 10:15
by henk
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
Re: SQLgrey encountered an SQL error ...
Posted: 17 Feb 2019 14:53
by tesme33
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}'`
Re: SQLgrey encountered an SQL error ...
Posted: 25 Feb 2019 18:42
by tesme33
After now some days of hope i can just confirm.
It didnt help. So still searching for the issue.
Mmmh
Re: SQLgrey encountered an SQL error ...
Posted: 25 Feb 2019 23:49
by henk
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.
Re: SQLgrey encountered an SQL error ...
Posted: 26 Feb 2019 18:23
by tesme33
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
Re: SQLgrey encountered an SQL error ...
Posted: 26 Feb 2019 18:41
by tesme33
i forgot to mention that im on EFA-3.0.2.6.
Re: SQLgrey encountered an SQL error ...
Posted: 26 Feb 2019 22:08
by henk
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)
Re: SQLgrey encountered an SQL error ...
Posted: 02 Mar 2019 12:25
by elfranko
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
Re: SQLgrey encountered an SQL error ...
Posted: 02 Mar 2019 12:42
by henk
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.
Re: SQLgrey encountered an SQL error ...
Posted: 02 Mar 2019 13:31
by elfranko
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
total used free shared buffers cached
Mem: 15950 11503 4446 6 265 7246
-/+ buffers/cache: 3991 11959
Swap: 4095 249 3846
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/spool/MailScanner/incoming/SpamAssassin-Temp | wc -l
These are the outputs from the commands - I will get the MySQL logs later.
Cheers
Elfrank0
Re: SQLgrey encountered an SQL error ...
Posted: 02 Mar 2019 14:41
by henk
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)
Re: SQLgrey encountered an SQL error ...
Posted: 02 Mar 2019 15:15
by elfranko
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
Re: SQLgrey encountered an SQL error ...
Posted: 10 Mar 2019 10:04
by tesme33
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 ~]#
Re: SQLgrey encountered an SQL error ...
Posted: 10 Mar 2019 17:02
by henk
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.
Re: SQLgrey encountered an SQL error ...
Posted: 10 Mar 2019 17:28
by tesme33
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
Re: SQLgrey encountered an SQL error ...
Posted: 10 Mar 2019 20:40
by henk
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
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
Update
: Just found this by one single google search:
viewtopic.php?t=2894
Re: SQLgrey encountered an SQL error ...
Posted: 13 Mar 2019 09:21
by tesme33
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.
Re: SQLgrey encountered an SQL error ...
Posted: 13 Mar 2019 18:45
by tesme33
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
Re: SQLgrey encountered an SQL error ...
Posted: 13 Mar 2019 19:01
by tesme33
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 ?
Re: SQLgrey encountered an SQL error ...
Posted: 13 Mar 2019 19:43
by henk
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.