Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Massively Problems importing csv-Files

cibmagic's profile image cibmagic posted 2 weeks ago in Import/Export Permalink

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

ansgar's profile image ansgar posted 2 weeks ago Permalink

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).

cibmagic's profile image cibmagic posted 2 weeks ago Permalink

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

1 attachment(s):
  • 2022-05-10-10_52_18-Greenshot
Mitchell Lee's profile image Mitchell Lee posted 2 weeks ago Permalink

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.

cibmagic's profile image cibmagic posted 1 week ago Permalink

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

ansgar's profile image ansgar posted 1 week ago Permalink

@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';
  1. Increase it in the server's my.ini or my.cnf:
[mysqld]
max_allowed_packet=500M

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

Description

cibmagic's profile image cibmagic posted 6 days ago Permalink

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

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.