Sample Header Ad - 728x90

MariaDB table is broken after failed dump process

2 votes
1 answer
369 views
I need to dump MariaDB InnoDB-database consists of several tables. One table that causes problems has nearly 13 Million rows. A fresh install of XAMPP(V.3.2.2) dump process was successful, after that, dump process always failed with the error message "mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table gv_faktur_header_history at row: 2623629". At this point here's the status : - Can not insert any value (Error 2013: Lost connection to MySQL) - Can not issue "CHECK TABLE" command (Error 2013: Lost connection to MySQL) - Can not alter this table (add column) - Can select data from this table - Can select row 2623629 (select * from table limit 2623629 ,1) - Can run "show table status" command I repeat this process several times like this : 1. Reinstalling xampp 2. Importing database using this method
> set global net_buffer_length = 1000000; 
> set global max_allowed_packet = 1000000000; 
> SET foreign_key_checks = 0;
> SET UNIQUE_CHECKS = 0; 
> SET AUTOCOMMIT = 0; 
> use db_name; 
> source backup-file.sql SET
> foreign_key_checks = 1; 
> SET UNIQUE_CHECKS = 1; 
> SET AUTOCOMMIT = 1;
3. Dump the database w/o --skip-extending-insert (success within 4 Minutes, 3,4 GB dump file) 4. Dump the database w/o --skip-extending-insert (failed within 1 Minute, 9xx MB dump file) 5. Dump the database w/o --skip-extending-insert (failed within 1 Minute, 9XX MB dump file ) mysqldump command : mysqldump -u root -p --skip-extended-insert --max-allowed-packet=1G --net-buffer-length=32704 rent_scaff header_history > D:\dobol Environment specifications : - i5 8 gen - Ram 8 GB (3 Gb unuse as seen in task manager) - SSD Storage 512G - mysqldump Ver 10.16 Distrib 10.1.10-MariaDB here's my.ini configuration [client] # password = your_password port = 3306 socket = "C:/xampp/mysql/mysql.sock" [mysqld] port= 3306 socket = "C:/xampp/mysql/mysql.sock" basedir = "C:/xampp/mysql" tmpdir = "C:/xampp/tmp" datadir = "C:/xampp/mysql/data" pid_file = "mysql.pid" key_buffer = 16M max_allowed_packet = 1G sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log_error = "mysql_error.log" plugin_dir = "C:/xampp/mysql/lib/plugin/" server-id = 1 innodb_data_home_dir = "C:/xampp/mysql/data" innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = "C:/xampp/mysql/data" innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 2M innodb_log_file_size = 250M innodb_log_buffer_size = 250M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 1G [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout enter code here Mysql Log : Server version: 10.1.10-MariaDB key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=2 max_threads=1001 thread_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 787099 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x3eee2178 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... mysqld.exe!my_parameter_handler() mysqld.exe!my_mb_ctype_mb() mysqld.exe!??2Geometry@@SAPAXIPAX@Z() mysqld.exe!??2Geometry@@SAPAXIPAX@Z() mysqld.exe!?propagate_equal_fields@Item_func_expr_str_metadata@@UAEPAVItem@@PAVTHD@@ABVContext@Value_source@@PAVCOND_EQUAL@@@Z() mysqld.exe!??2Geometry@@SAPAXIPAX@Z() mysqld.exe!??2Geometry@@SAPAXIPAX@Z() mysqld.exe!??2Geometry@@SAPAXIPAX@Z() mysqld.exe!??0Alter_table_prelocking_strategy@@QAE@XZ() mysqld.exe!?mysql_alter_table@@YA_NPAVTHD@@PAD1PAUHA_CREATE_INFO@@PAUTABLE_LIST@@PAVAlter_info@@IPAUst_order@@_N@Z() mysqld.exe!?execute@Sql_cmd_alter_table@@UAE_NPAVTHD@@@Z() mysqld.exe!?mysql_execute_command@@YAHPAVTHD@@@Z() mysqld.exe!?mysql_parse@@YAXPAVTHD@@PADIPAVParser_state@@@Z() mysqld.exe!?dispatch_command@@YA_NW4enum_server_command@@PAVTHD@@PADI@Z() mysqld.exe!?do_command@@YA_NPAVTHD@@@Z() mysqld.exe!?threadpool_process_request@@YAHPAVTHD@@@Z() mysqld.exe!?tp_end@@YAXXZ() KERNEL32.DLL!SetUserGeoName() ntdll.dll!TpCheckTerminateWorker() ntdll.dll!TpCallbackIndependent() KERNEL32.DLL!BaseThreadInitThunk() ntdll.dll!RtlGetAppContainerNamedObjectPath() ntdll.dll!RtlGetAppContainerNamedObjectPath() Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Please help me how to Dump (backup) a large database, at least how to safely dump the database, so when the process failed, the table remains usable.
Asked by Albert (21 rep)
Oct 20, 2021, 03:03 AM
Last activity: Oct 20, 2021, 06:16 AM