I'm trying to import some csv files into my mysql mariadb database, but I'm having problems with some files. Importing csv with a line with only spaces (2MB of space character) the database stops for hours without giving an answer. I then created a csv file with only spaces, copying it exactly from one of the many files that cause the problem: 1.888.653 byte of spaces and when I run the query by importing this file the server no longer responds.

MariaDB version: 10.1.31-MariaDB - mariadb.org binary distribution Protocol version: 10

This is the query I run:

LOAD DATA LOCAL INFILE 'x:/storage/sizetest.txt' IGNORE INTO TABLEdatatest LINES TERMINATED BY "\n" (@line) SET email= REGEXP_SUBSTR(@line, "^[^;:,]*"), password= REGEXP_SUBSTR(@line, "[^;:,]+$");

This is the server structure: email: varchar(255) utf8_bin password: varchar(255) utf8_bin

My.ini configuration:

key_buffer = 64M max_allowed_packet = 1M sort_buffer_size = 5M net_buffer_length = 8M read_buffer_size = 5M read_rnd_buffer_size = 5M myisam_sort_buffer_size = 8M log_error = "mysql_error.log" innodb_buffer_pool_size = 2650M innodb_additional_mem_pool_size = 64M innodb_log_file_size = 2048M innodb_log_buffer_size = 512M bulk_insert_buffer_size = 2048M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50

Log files do not report any errors while waiting. I waited more than 2 hours and then I stopped loading, and every time I encounter a file like that the server no longer responds. How can I solve this problem?

  • Please post your sizetest.txt AND text RESULTS of SHOW CREATE TABLE datatest; for analysis. Do you mean there are 2MB of spaces before the first \n for a new line? By posting your test data, we will be able to look at your input data. How much RAM is on your server? Consider posting results of MySQLTuner.pl, please. – Wilson Hauck Feb 12 at 9:49
  • @WilsonHauck The server have 16gb of Ram ddr4 2666mhz, this is sizetest.txt this is sizetest.txt: drive.google.com/file/d/1KDwYtO2sftFvn5I1taasg7OSUDkw5Jxg/… – tom580933 Feb 12 at 20:14
  • Thanks for your sizetest.txt data. Only the last 9 lines have any data of the 15,361 lines of data. All the lines appear to be 124 characters of spaces until the last 9 lines. Any chance you could post text RESULTS of SHOW CREATE TABLE datatest for analysis? Thanks – Wilson Hauck Feb 13 at 13:21
  • @WilsonHauck of course, this is the result of the show create table datatest: CREATE TABLE 'datatest' ( 'email' varchar(255) CHARACTER SET utf16 COLLATE utf16_bin NOT NULL, 'password' varchar(255) CHARACTER SET utf16 COLLATE utf16_bin NOT NULL, 'source' mediumint(8) unsigned DEFAULT '1181' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin (I had to replace the quotes for stackexchange formatting) – tom580933 Feb 13 at 14:17

Either there is a bug, or the system is ferociously swapping.

Do not blindly increase settings. How much RAM do you have? It sounds like you have over-committed RAM and are in the throws of heavy swapping.

innodb_buffer_pool_size = 2650M  -- OK if you have at least 6GB of RAM
innodb_log_buffer_size = 512M    -- go back to default
bulk_insert_buffer_size = 2048M  -- go back to default

All of these could be allocated simultaneously, adding up to 7GB, which would be tight in 8GB of RAM. If you had multiple connections, running, the last one could be allocated multiple times, thereby needing even more RAM.

As a rule, anything named "buffer" or "cache" does not need to be set to huge values. Large values help, but there are "diminishing returns". When large values lead to swapping, performance suffers severely.

These may be directly relevant to loading a CSV file:

max_allowed_packet = 1M  -- change to 4M (due to the nature of your data)
net_buffer_length = 8M 
read_buffer_size = 5M 
bulk_insert_buffer_size = 2048M  -- go back to default (8M)

If you expect 1.8MB of spaces to be put into varchar(255), well, it will be truncated.

Which Engine is the table?

  • hello Rick James, thanks for the reply: I disabled the ram swap. The ram memory amounts to 16GB (of which less than 2 are occupied by the operating system and other open programs). I followed your advice but in the query you still do not respond. The Engine is InnoDB – tom580933 Feb 9 at 17:22
  • @tom580933 - Please (1) provide a small test case to reproduce the problem so we can play with it, then (2) file a bug report with MariaDB. We can probably test it on MySQL to see if it is specific to MariaDB. – Rick James Feb 10 at 0:06
  • this is sizetest.txt : drive.google.com/file/d/1KDwYtO2sftFvn5I1taasg7OSUDkw5Jxg/… – tom580933 Feb 12 at 20:16
  • Chrome choked on that file. Can you trim it down to a minimal test case? – Rick James Feb 13 at 5:24
  • No because the case that crashes the database is just that, but I can reduce it in a zip file in the hope that chrome does not crash. Zip file: drive.google.com/file/d/1jyeyP6pzcFl_K4Dqor8nx8LvWqvC5MMK/… – tom580933 Feb 13 at 13:19

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.