2

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;
  1. Dump the database w/o --skip-extending-insert (success within 4 Minutes, 3,4 GB dump file)
  2. Dump the database w/o --skip-extending-insert (failed within 1 Minute, 9xx MB dump file)
  3. 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.

2

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.

4
  • Hi Danblack, thank you for your reply. Honestly, I wish there is a couple of mistakes from my setting, because if I have to reinstall with the latest xampp, I need to re-checking the deprecated method in my code to comply with the latest PHP. I'm still curious about my problem. I'm ok if the dump process failed, the annoying thing is, why the big table that causes the problem becomes broken.
    – Albert
    Oct 21 at 3:24
  • 1
    Any crash is a server problem. In this case its something related to Geometry and ALTER TABLE. Geometry is one of the areas of the code that has more problems and less developer attention. Choosing a XAMPP that has the latest MariaDB-10.1 and whatever PHP major/minor version is in 3.2.2.
    – danblack
    Oct 21 at 4:24
  • 1
    Another option is to edit the dump file and put the ALTER TABLE commands straight after the CREATE TABLE for the same table name. The crash you have is something to do with ALTER TABLE on a specific piece of data. If the table is altered before the data is inserted it shouldn't occur. It will be a bit slower to import however.
    – danblack
    Oct 21 at 23:08
  • I'm sorry for the late response. I've decided to follow your recommendation, Using xampp only for apache and PHP, I've installed the latest standalone MariaDB and tested it for two days doing multiple dump and restore. Now, I no longer encountered that problem. I hope no other upcoming problem except the "sql_mode", I need to modify my PDO connection to set sql_mode as traditional. Thank you for your help.
    – Albert
    Oct 22 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.