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?
for 413 errors Fixing 413 Request Entity Too Large Errors - KeyCDN Support so check /usr/local/nginx/conf/nginx.conf's setting for client_max_body_size As to restore, why are you only restoring a single table ? or you restoring entire database from individual table files ? As to MySQL server gone away see https://dev.mysql.com/doc/refman/5.7/en/gone-away.html and remember settings mentioned can be for both MySQL server end and MySQL client command end.
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
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.
Matt suggestion worked. Eva, I already increased wait_timeout. What am I missing that you said I didn't pick up your hint?
Check the migration guide at https://community.centminmod.com/threads/centmin-mod-site-data-migration-guide.10382/ under mysql database backup and restore look at $CLIENTOPT variable i apply to remote server mysql command line restore options :;