Get the most out of your Centmin Mod LEMP stack
Become a Member

MariaDB MySQL server has gone away

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by elargento, May 1, 2017.

  1. elargento

    elargento Member

    293
    16
    18
    Jan 4, 2016
    Ratings:
    +39
    Local Time:
    3:20 PM
    10
    • 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?
     
    Last edited: May 1, 2017
  2. elargento

    elargento Member

    293
    16
    18
    Jan 4, 2016
    Ratings:
    +39
    Local Time:
    3:20 PM
    10
    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?
     
  3. eva2000

    eva2000 Administrator Staff Member

    31,027
    6,928
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,434
    Local Time:
    4:20 AM
    Nginx 1.13.x
    MariaDB 5.5
  4. Matt

    Matt Moderator Staff Member

    697
    322
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +449
    Local Time:
    6:20 PM
    1.7.1
    MariaDB 10
    Any reason why you can't restore the database via the command line?
     
  5. elargento

    elargento Member

    293
    16
    18
    Jan 4, 2016
    Ratings:
    +39
    Local Time:
    3:20 PM
    10
    Yes, I'm doing it in that way by using mysql command and I'm getting same error
     
  6. elargento

    elargento Member

    293
    16
    18
    Jan 4, 2016
    Ratings:
    +39
    Local Time:
    3:20 PM
    10
    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
    
     
    Last edited: May 2, 2017
  7. Matt

    Matt Moderator Staff Member

    697
    322
    63
    May 25, 2014
    Sheffield, UK
    Ratings:
    +449
    Local Time:
    6:20 PM
    1.7.1
    MariaDB 10
    Try actually doing it from inside MySQL. Log into the MySQL command line, select the db, and then do
    Code:
    source /path/to/dump.sql;
    Thats how I do it, and I've never had one fail.
     
    • Informative Informative x 1
  8. eva2000

    eva2000 Administrator Staff Member

    31,027
    6,928
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,434
    Local Time:
    4:20 AM
    Nginx 1.13.x
    MariaDB 5.5
  9. elargento

    elargento Member

    293
    16
    18
    Jan 4, 2016
    Ratings:
    +39
    Local Time:
    3:20 PM
    10
    Matt suggestion worked.

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

    eva2000 Administrator Staff Member

    31,027
    6,928
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,434
    Local Time:
    4:20 AM
    Nginx 1.13.x
    MariaDB 5.5