Importing empty field values into HeidiSQL 6

[expired user #5348]'s profile image [expired user #5348] posted 13 years ago in Import/Export Permalink
Hi

I have just upgraded to HeidiSQL 6 on Win XP. To date I have always been able to upload data with empty fields in the csv file, into columns that allow null values in MYSQL. However, today, after the update and upon importing, I got this error: /* SQL Error (1366): Incorrect integer value: '' for column 'xxxxx' at row 1 */

This is obviously an issue with an empty field value, as the moment I put a value in it, the error moves on to the next empty field it finds in the csv file. This was not a problem in the previous version of HeidiSQL so I'm wondering if there's a setting I need to set somewhere. Any help will be greatly appreciated.

Regards
ansgar's profile image ansgar posted 13 years ago Permalink
Did you select the server or the client parser? (see left bottom group on the import dialog)
[expired user #5348]'s profile image [expired user #5348] posted 13 years ago Permalink
I selected both, and neither worked. On top of that, now my destination table seems to have been corrupted beyond repair. It is being marked as a view, and if I attempt to do anything to it, including dropping it, I get told that the object is not a view. This is not looking good ! unhappy
ansgar's profile image ansgar posted 13 years ago Permalink
Never heard that importing text files can corrupt a table file. Probably related to some other cause, don't know. What does SHOW FULL TABLE STATUS say?
[expired user #5348]'s profile image [expired user #5348] posted 13 years ago Permalink
here you go....

mysql> show table status like 'accounts';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment
|
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------------------------------------+
| accounts | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Table '.\salesforce\accounts' is marked as crashed and last (automatic?) repair failed |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
ansgar's profile image ansgar posted 13 years ago Permalink
Ok, hm. Table file is corrupted and your attempt to repair was unsuccessful?

Crashed tables mostly have different causes than some dumb INSERT. If repairing does not succeed you should log in to that server system and watch for file system issues.
[expired user #5348]'s profile image [expired user #5348] posted 13 years ago Permalink
Well, I had to drop the table from the MySQL command line. That worked without a problem, and then rebuilt the table from my backups. Incidentally I tried the same thing on a different table and the second table got corrupted as well. Ironically when using HeidiSQL v5.0, the problems do not exist. I can upload data into my tables without problem. I just might stick to this version for now. Thank you very much for your advice.

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