I encountered a problem when using zabbix4.2, I tried to modify it several times and did not solve the problem.
I am using docker-compose orchestration and using elasticsearch
Configuration information:
```
mariadb:10.2.15
elasticsearch:5.6.16-alpine
zabbix/zabbix-server-mysql:alpine-4.2-latest
zabbix/zabbix-web-nginx-mysql:alpine-4.2-latest
```
- zabbix-web-nginx-mysql
```
environment:
- DB_SERVER_HOST=127.0.0.1
- MYSQL_DATABASE=zabbix
- MYSQL_USER=zabbix
- MYSQL_PASSWORD=password
- ZBX_SERVER_HOST=127.0.0.1
- ZBX_HISTORYSTORAGEURL=http://127.0.0.1:9200
# - ZBX_HISTORYSTORAGETYPES=['str', 'text', 'log'] # uint,dbl,str,log,text
- ZBX_HISTORYSTORAGETYPES=['dbl','uint','str', 'text', 'log'] # uint,dbl,str,log,text
```
- zabbix/zabbix-server-mysql:alpine-4.2-latest
```
environment:
- DB_SERVER_HOST=127.0.0.1
- MYSQL_DATABASE=zabbix
- MYSQL_USER=zabbix
- MYSQL_PASSWORD=password
- MYSQL_ROOT_PASSWORD=abc123
- ZBX_HISTORYSTORAGEURL=http://127.0.0.1:9200 # elasticsearch
- ZBX_HISTORYSTORAGETYPES=uint,dbl,str,log,text # stor add elasticsearch type
# zabbix_server configure
- ZBX_CACHESIZE=1024M
- ZBX_CACHEUPDATEFREQUENCY=60
- ZBX_STARTDBSYNCERS=4
- ZBX_HISTORYCACHESIZE=1024M
- ZBX_HISTORYINDEXCACHESIZE=512M
- ZBX_TRENDCACHESIZE=2046M
- ZBX_VALUECACHESIZE=2048M
- ZBX_TRAPPERTIMEOUT=30
- ZBX_UNREACHABLEPERIOD=120
- ZBX_UNAVAILABLEDELAY=80
- ZBX_STARTTRAPPERS=50
- ZBX_STARTPOLLERS=50
- ZBX_STARTPOLLERSUNREACHABLE=50
- ZBX_STARTPINGERS=10
- ZBX_STARTDISCOVERERS=10
- ZBX_STARTHTTPPOLLERS=1
```
- mariadb conf
```
[client]
port = 3306
socket = /run/mysqld/mysqld.sock
[mysqld]
relay_log = relay-bin
datadir = /data/mariadb
pid-file = /data/mariadb/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
init-connect = 'SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300
max_connections = 3993
max_connect_errors = 1024000
open_files_limit = 65535
table_open_cache = 2048
max_allowed_packet = 4M
max_heap_table_size = 16M
tmp_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 512M
thread_cache_size = 128
query_cache_type = 2
query_cache_size = 128M
query_cache_limit = 5M
thread_stack = 192k
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
log_error = /data/mariadb/mysql-error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /data/mariadb/mysql-slow.log
performance_schema = 0
skip-external-locking
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 256M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2M
innodb_log_file_size = 16M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 30
innodb_autoinc_lock_mode = 2
innodb_doublewrite = 1
innodb_rollback_on_timeout = 1
innodb_force_recovery=0
innodb_locks_unsafe_for_binlog = 1
interactive_timeout = 28800
wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
```
mysql-error.log
```
2019-04-19 15:20:56 140057019730664 [Warning] Aborted connection 519 to db: 'zabbix' user: 'zabbix' host: '127.0.0.1' (Got timeout reading communication packets)
```
Creating users and libraries seems normal
```
********************
* DB_SERVER_HOST: 127.0.0.1
* DB_SERVER_PORT: 3306
* DB_SERVER_DBNAME: zabbix
* DB_SERVER_ROOT_USER: root
* DB_SERVER_ROOT_PASS: abc123
* DB_SERVER_ZBX_USER: zabbix
* DB_SERVER_ZBX_PASS: password
********************
**** MySQL server is not available. Waiting 5 seconds...
**** MySQL server is not available. Waiting 5 seconds...
**** MySQL server is not available. Waiting 5 seconds...
** Creating 'zabbix' user in MySQL database
** Database 'zabbix' does not exist. Creating...
** Creating 'zabbix' schema in MySQL
** Preparing Zabbix server configuration file
** Updating '/etc/zabbix/zabbix_server.conf' para
```
error
```
1:20190419:150614.255 using configuration file: /etc/zabbix/zabbix_server.conf
1:20190419:150614.265 current database version (mandatory/optional): 04020000/04020000
1:20190419:150614.265 required mandatory version: 04020000
1:20190419:150614.283 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
```
error
```
194:20190419:150714.312 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:150814.335 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:150914.357 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:151014.382 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:151114.408 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
```
web : monitoring ->overview
```
mysqli_query(): (HY000/1036): Table '/var/tmp/#sql_168_0.MAI' is read only [overview.php:208 → getTriggersOverviewData() → getTriggersWithActualSeverity() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTrigger->get() → CApiService->customFetch() → DBselect() → mysqli_query() in include/db.inc.php:364]
Error in query [SELECT DISTINCT t.triggerid,t.expression,t.description,t.value,t.p riority,t.lastchange,t.flags,t.comments FROM triggers t,functions f,items i WHERE i.hostid='10084' AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN ('0','4') AND ((t.value=1) OR ((t.value=0) AND (t.lastchange>1555657927))) ORDER BY t.description] [Table '/var/tmp/#sql_168_0.MAI' is read only]
```
thanks
I am using docker-compose orchestration and using elasticsearch
Configuration information:
```
mariadb:10.2.15
elasticsearch:5.6.16-alpine
zabbix/zabbix-server-mysql:alpine-4.2-latest
zabbix/zabbix-web-nginx-mysql:alpine-4.2-latest
```
- zabbix-web-nginx-mysql
```
environment:
- DB_SERVER_HOST=127.0.0.1
- MYSQL_DATABASE=zabbix
- MYSQL_USER=zabbix
- MYSQL_PASSWORD=password
- ZBX_SERVER_HOST=127.0.0.1
- ZBX_HISTORYSTORAGEURL=http://127.0.0.1:9200
# - ZBX_HISTORYSTORAGETYPES=['str', 'text', 'log'] # uint,dbl,str,log,text
- ZBX_HISTORYSTORAGETYPES=['dbl','uint','str', 'text', 'log'] # uint,dbl,str,log,text
```
- zabbix/zabbix-server-mysql:alpine-4.2-latest
```
environment:
- DB_SERVER_HOST=127.0.0.1
- MYSQL_DATABASE=zabbix
- MYSQL_USER=zabbix
- MYSQL_PASSWORD=password
- MYSQL_ROOT_PASSWORD=abc123
- ZBX_HISTORYSTORAGEURL=http://127.0.0.1:9200 # elasticsearch
- ZBX_HISTORYSTORAGETYPES=uint,dbl,str,log,text # stor add elasticsearch type
# zabbix_server configure
- ZBX_CACHESIZE=1024M
- ZBX_CACHEUPDATEFREQUENCY=60
- ZBX_STARTDBSYNCERS=4
- ZBX_HISTORYCACHESIZE=1024M
- ZBX_HISTORYINDEXCACHESIZE=512M
- ZBX_TRENDCACHESIZE=2046M
- ZBX_VALUECACHESIZE=2048M
- ZBX_TRAPPERTIMEOUT=30
- ZBX_UNREACHABLEPERIOD=120
- ZBX_UNAVAILABLEDELAY=80
- ZBX_STARTTRAPPERS=50
- ZBX_STARTPOLLERS=50
- ZBX_STARTPOLLERSUNREACHABLE=50
- ZBX_STARTPINGERS=10
- ZBX_STARTDISCOVERERS=10
- ZBX_STARTHTTPPOLLERS=1
```
- mariadb conf
```
[client]
port = 3306
socket = /run/mysqld/mysqld.sock
[mysqld]
relay_log = relay-bin
datadir = /data/mariadb
pid-file = /data/mariadb/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
init-connect = 'SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300
max_connections = 3993
max_connect_errors = 1024000
open_files_limit = 65535
table_open_cache = 2048
max_allowed_packet = 4M
max_heap_table_size = 16M
tmp_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 512M
thread_cache_size = 128
query_cache_type = 2
query_cache_size = 128M
query_cache_limit = 5M
thread_stack = 192k
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
log_error = /data/mariadb/mysql-error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /data/mariadb/mysql-slow.log
performance_schema = 0
skip-external-locking
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 128M
myisam_repair_threads = 1
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 256M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2M
innodb_log_file_size = 16M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 30
innodb_autoinc_lock_mode = 2
innodb_doublewrite = 1
innodb_rollback_on_timeout = 1
innodb_force_recovery=0
innodb_locks_unsafe_for_binlog = 1
interactive_timeout = 28800
wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
```
mysql-error.log
```
2019-04-19 15:20:56 140057019730664 [Warning] Aborted connection 519 to db: 'zabbix' user: 'zabbix' host: '127.0.0.1' (Got timeout reading communication packets)
```
Creating users and libraries seems normal
```
********************
* DB_SERVER_HOST: 127.0.0.1
* DB_SERVER_PORT: 3306
* DB_SERVER_DBNAME: zabbix
* DB_SERVER_ROOT_USER: root
* DB_SERVER_ROOT_PASS: abc123
* DB_SERVER_ZBX_USER: zabbix
* DB_SERVER_ZBX_PASS: password
********************
**** MySQL server is not available. Waiting 5 seconds...
**** MySQL server is not available. Waiting 5 seconds...
**** MySQL server is not available. Waiting 5 seconds...
** Creating 'zabbix' user in MySQL database
** Database 'zabbix' does not exist. Creating...
** Creating 'zabbix' schema in MySQL
** Preparing Zabbix server configuration file
** Updating '/etc/zabbix/zabbix_server.conf' para
```
error
```
1:20190419:150614.255 using configuration file: /etc/zabbix/zabbix_server.conf
1:20190419:150614.265 current database version (mandatory/optional): 04020000/04020000
1:20190419:150614.265 required mandatory version: 04020000
1:20190419:150614.283 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
```
error
```
194:20190419:150714.312 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:150814.335 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:150914.357 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:151014.382 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
194:20190419:151114.408 [Z3005] query failed: [1036] Table '/var/tmp/#sql_168_0.MAI' is read only [select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.s tep,i.hostid,pp.error_handler,pp.error_handler_par ams from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid is null and h.status in (0,1) and i.flags<>2 order by pp.itemid]
```
web : monitoring ->overview
```
mysqli_query(): (HY000/1036): Table '/var/tmp/#sql_168_0.MAI' is read only [overview.php:208 → getTriggersOverviewData() → getTriggersWithActualSeverity() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTrigger->get() → CApiService->customFetch() → DBselect() → mysqli_query() in include/db.inc.php:364]
Error in query [SELECT DISTINCT t.triggerid,t.expression,t.description,t.value,t.p riority,t.lastchange,t.flags,t.comments FROM triggers t,functions f,items i WHERE i.hostid='10084' AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN ('0','4') AND ((t.value=1) OR ((t.value=0) AND (t.lastchange>1555657927))) ORDER BY t.description] [Table '/var/tmp/#sql_168_0.MAI' is read only]
```
thanks