MariaDB table is broken after failed dump process

Question

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.

Answer 1

XAMPP(V.3.2.2) is really old. As is MariaDB 10.1.10 (Dec 2015) which it includes. The entire 10.1 series is now unsupported by the upstream MariaDB.

Recommend:

A physical copy of C:/xampp/mysql/data to a different directory while MariaDB is shut down. This is in case you need the upgrade doesn't work and you need to revert. If you have time in reverting, pick a XAMPP that includes the latest 10.1 as a check.

Use a fresh install of a NEW XAMPP version (MariaDB-10.4). There are numerous bug fixes included and crudely looking at least 8 might correspond to the fault you have encountered.

Add at least 2G to innodb_buffer_pool_size, at least while you import your existing successful dump file.

If you are unsuccessful then, please create a bug report.