MariaDB MySQL server has gone away

• CentOS Version: CentOS 7 64bit
• Centmin Mod Version Installed: 123.09beta01
• Nginx Version Installed: 1.11.10
• PHP Version Installed: 7.0.15
• MariaDB MySQL Version Installed: 10.1.21

I'm getting this error when I try to restore a 1gb database (it seems it happens when xf_post table is being restored) through mysql command. I already increased max_allowed_packet to 256mb and wait_timeout to 6000 and restarted mysql.

I don't know where should I start looking to find what is causing this error. Is there any error log I should check?

 

Tried to import a smaller sql file through phpmyadmin and got the following error:

413 Request Entity Too Large
cloudflare-nginx

Then I tried again and I got this error:

Code:

1 errors were found during analysis.

An opening bracket followed by a set of values was expected. (near "" at position 0)
SQL query:

#    TD`xf_user_confirmation`utf8_general_ci    ; INSERT INTO `xf_user_confirmation` VALUES

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2

What's going on? Two different errors with the same .sql file import?

 

Yes, I'm doing it in that way by using mysql command and I'm getting same error

 

There must be something else, I've set wait_timeout = 26000000 for [mysqld] and I'm getting same error:

ERROR 2006 (HY000) at line 3988368: MySQL server has gone away
It's a 1gb xenforo database and it seems problem happens when xf_post table is going to be imported

The command is mysql -u user -p dbname < dbfile.sql
my.cnf:

Code:

[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 256M

[mysqld]
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
##skip-pbxt
##skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 512
max_connections = 500
key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_definition_cache = 8192
table_open_cache = 4096
thread_cache_size = 256
#wait_timeout = 1800
wait_timeout = 26000000
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 256M
#max_seeks_for_key = 4294967295
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 80M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 512M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=1

innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
#innodb_thread_concurrency = 8
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 400
innodb_io_capacity_max = 800
innodb_read_io_threads = 2
innodb_write_io_threads = 2
innodb_flush_neighbors = 0

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 768M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 768M
aria_sort_buffer_size = 192M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 256M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 256M
sort_buffer = 32M
read_buffer = 32M
write_buffer = 32M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.1]
innodb_file_format = Barracuda
innodb_file_per_table = 1

## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
# default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

 

Matt suggestion worked.

Eva, I already increased wait_timeout. What am I missing that you said I didn't pick up your hint?