Massively Problems importing csv-Files

Hi Community,

i use Heidi for many, many years (10, 12+) and had never probs with importing csv.

Now, for the last weeks, i am UNABLE to import ANY File.

Heidi, respectively the db-servers, react with an error: Lost server-connection during query

For real, the connection has gone - i have to reconnect.

The ONLY fact that had changed between: "works - doesn't work", is, that our db-servers changed the WHOLE IP-Segment. There are no further changes !!!

The Probs appear on MySQL as on MariaDB, also the used differend Heidi-Versions or FullClients don't make any difference.

So, im my opinion, it mut be a Heidi-Problem (in combination with the IP-Segment-Change)

?????

Any ideas ? Thanks for any helping answer

Stephan Kleve/Germany

Did you probably also update to HeidiSQL v12.0?

If yes, there was a significant change in the MySQL LOAD DATA command used in the CSV import. The filename is converted to its 8.3 format in the query. Please watch out in the log panel for that LOAD DATA command, try the same command with the natural filename (not 8.3).

Hello Ansgar,

i just have installed HS 12.0, using Win 10.x

However, the prob is the same (i didn't expect anything else by re-installation)

In the annex is the actual screenshot, as given since we changed our ip-segment. Occurence of this prob and the change of server-ip MUST BE the prob. The timeframe is SOOO close (we talk about hours)

Our servers are running on a ms-hyper-v-server, which was set to an absolute different ip-segment. Our apps are running great, HS works great, even the csv-import doesn't work.

it may be, that our company-guidelines (policies) affect this prob, but is it possible, that HS stores "old pathes" or "old rights", which crashes against new policies ????

Stephan

For u guys information, importing is very painful work until set the data format correctly. Once it fits, works fine so far that several million rows as utf-8.

Prepare data file that comma separation and use quote or double quote as field termination, and assign encoding type. Of course assign separation and field termination on hiedisql as well. Than see what happen. This is important to use importing on heidisql.

For the test purpose, spilt the source text file as some groups to find which line has the problem. (or just put one row in the text file) And let the Ansger know the problem line/data.

Hello Mitchell,

it doesn't depend on one ore mor special line(s).

It happens with EVERY CSV-file i like to import (different sources, different lines or columns) Thanks for your answer, but it seems, you didn't realize the real problem - by clicking "import", the server goes away, without importing any row !!!!

I imported thousends of csv from many sources (also from MS-Excel German, where using utf8 isn't usefull)

I had no probs since 2008 - just the last 2 weeks

Thnx for regards

@cibmagic most likely the file is bigger than the allowed packet size in your MySQL server.

1. Check the allowed packet size:

SHOW VARIABLES LIKE 'max_allowed_packet';

2. Increase it in the server's my.ini or my.cnf:

[mysqld]
max_allowed_packet=500M

Alternatively, you can use the "Client parses" method:

Hi Ansgar,

FileSize doesn't matter, connection crashes even with one-line-files.

METHOD: that's the clue, import works - however, i never worked with this option and had, as said, never probs. Seems, that our servicecenter did more changes on our servers than only changing the ip.

today i found the next massive error - but not with sql. my (very) long php-scripts crash with an 504 after 55 seconds, while the setup allows 6 minutes exec time

i get mad ...

Thanks for your help

best regards Stephan

Good afternoon - has there been any resolution / explanation? I am experiencing a similar problems in the last couple of weeks. The DBA confirmed nothing has changed on the server recently, and I also have been using HeidiSQL import csv files for the past 4 years with no issue, until last 2 weeks. I also tried upgrading to the latest HeidiSQL version 12.0 but the problem still persists. I tried checking max_allowed_packet on the server as indicated but it is big enough. I manage to import small files after a while but when trying larger files (3 mil records) it never finishes.... and this is something new - I have never had a problem with it before even with similar size of files. Thanks

@mlhandel you should post a screen of the import dialog how you are using it, and probably a small chunk of the file (without sensitive information)

@ansgar thanks. see attached - ce_sample is a snippet of the csv I am trying to import (the real file has 3 mil records like these). The other file is a screenshot - as you can see there is no error message - after reading the txt file it just freezes and does nothing - no progress, no error message, nothing - just appears idle. Is this something to do with that SQL_MODE parameter? This is the only message but it does not look like an error

if I import smaller files, it works usually, but painfully slow and this used to me much faster. For example, a file with 90.000 records takes 70 seconds to import !! Very unusual given my past experience

I meant a screenshot of the import dialog, with the settings you are using to import.

@ansgar sorry for the confusion - see attached - nothing unusual, and have always used the same historically

I see the "Fields enclosed by" is set to double quote, but the text file only surrounds empty values with double quotes. That should not cause the slowness, but:

If you select "Server parsing" in "Method", you can make the encloser optional. Also, the server-side parsing is in many cases faster than the client parsing. However, that may be forbidden on your server. Just try it.

Another cause of the slowness may be the "REPLACE" duplicates option. Truncating the table before the import can speed this up massively.

Thanks a lot - I will try that and revert

The change to server parse helped speed this up, thanks for the advice