distal-attribute
distal-attribute
distal-attribute
distal-attribute

SQL Import does not work in release 6.0.0 !!

jhoubart posted 4 years ago in General
Après la mise à jour à la version 6.0.0.3657, l'importation de fichier SQL de grande taille ne fonctionne plus.
L'import fonctionne seulement avec des fichiers de petite taille.

After updating to version 6.0.0.3657, the importation of large SQL file does not work.
The import function only works with small files.
ansgar posted 4 years ago
Please be more detailed - what means "does not work"?
lemon_juice posted 4 years ago
I experienced the same problem - dumps 40-100MB. However, they were not made by Heidi but by Sqlyog and mysqldump. Heidi can import large dumps created by itself but not by those external tools. It's hard to say what caused the problem - possibly some obsure bug in detecting query boundaries? Heidi's dumps are less compact because they contain a space between values whereas mysqldump and sqlyog don't output spaces. I'm sure the dumps were okay because sqlyog processed them fine, even phpmyadmin coped with them.

At a certain point heidi would simply stop the import and report a syntax error in a query. Oddly, when I extract the problematic query from the dump in a text editor and paste it in the query box then it gets processed without any problem. This could imply a problem with detecting query delimiters or something like that.
ansgar posted 4 years ago
> Heidi can import large dumps created by itself but not by those external tools

That depends 100% on how the dump looks like - not on which application created the dump, however.

Could someone please post some error message or is that a secret?
lemon_juice posted 4 years ago
45.8MB dump, Heidi stopped at position 20MB, 204 executed queries:

Error while reading file C:\Temp\np-y.sql

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b247708f8b3048d6',19,394,3,2,1,NULL,NULL,0,NULL,'',''),(324,0,0,2,'',0,'83.24.45' at line 1



The strange thing is that when I look at the log, the last successfully executed query comes from line 1156 in the dump file. However, there is no 'b247708f8b3048d6',19,394... in that line. The query that the error refers to is in line 1974. Both are insert queries. After the error occurs, manually running the insert from line 1156 is successful, however I cannot run query from line 1974 because it tries to insert data to inexistent table, which means that the necessary CREATE TABLE in the file before hasn't been executed at all. Something wrong with query order, or perhaps Heidi treats all data from line 1156 to 1974 as one big query whereas in fact there are many separate ones there. I think the latter must be the case.

Each of these queries is almost 100KB. The data are in UTF-8 and contain mostly Polish letters apart from the standard western set. The query at 1974 also contains some special characters like bullets of various sizes, etc., maybe Heidi chokes on those characters. These are mostly data from phpbb forum so users might have inserted some really weird characters in some places.

lemon_juice posted 4 years ago
Sorry, one clarification: query from line 1156 is not the last executed query but the last query reported in the log - so it's actually the query that heidi stopped at.
lemon_juice posted 4 years ago
Some more information: I disabled truncating queries in the log and the problematic query seems to be some weird mixture of the query from line 1156 and 1974. And during import up to query 203 heidi reports 5MB (more of less) as the position of the file, then at 204 it suddenly goes to 20MB and stops with error. So it jumps forward in the file for some unknown reason skipping 15MB of content and makes a hybrid query that cannot be valid in any way.
ansgar posted 4 years ago
I will need that file if I should analyze that. Or does it contain sensible data?
lemon_juice posted 4 years ago
Unfortunately, it contains sensitive data. I tried deleting confidential data from it but as soon as I did, the import started working fine... When it happens on data I can easily share I'll let you know.
ansgar posted 4 years ago
Ok - thanks a lot for the effort!

jhoubart posted 4 years ago
vous pourrez trouver des données de test sur :
you can find test data on :

http://www.cabeka.be/test_km.sql
ansgar posted 4 years ago
Your url returns HTTP status 403.
jhoubart posted 4 years ago
Oops, files. sql can not be downloaded directly ...
To allow the download, I just rename it.
The new address is: http://www.cabeka.be/test_km_sql.txt
ansgar posted 4 years ago
Ok, I downloaded and ran your SQL file here on a test database using the latest HeidiSQL build. Now, what's the problem?
jhoubart posted 4 years ago
Importing SQL file does not work for all files and especially for this file.
No data is imported and the file content is not even loaded into the query window.
HeidiSQL displays just a comment line in the bottom window.

See the video in the zip file: http://www.cabeka.be/test_km.zip

ansgar posted 4 years ago
That's an encoding problem - Heidi tries its best to discover the file encoding, and fails in this case. If you load the file and select "Ansi" this works. However, the content looks corrupted in that case. What encoding does a good text editor tell you?
jhoubart posted 4 years ago
The SQL file was created by exporting data from phpmyadmin.
In phpmyadmin, charsets are displayed as "latin1_general_ci"
ansgar posted 4 years ago
Well the file looks broken also in PSpad. I guess it's already broken in the database itself.
jhoubart posted 4 years ago
A sujestion, in addition to my post yesterday:

I understand HeidiSQL tries to translate the imported data, but it would be useful to add the ability to import (even manually without automatic detection) other character sets accepted by MySQL, which are used in other countries.

And also to allow the user to import any such data without transcoding (this to be useful for binary data)
ansgar posted 4 years ago
What I tried to explain above is that Heidi already attempts to detect the file encoding. Only in this case the detection fails for some reason I don't know exactly. That's why I guess your database content has the wrong charset (latin1_general is not capable for all french characters I think). The file then has ANSI encoding but Heidi is confused by its content. That's why selecting ANSI works, and "Automatic detection" fails.

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