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

Importing empty field values into HeidiSQL 6

moh posted 4 years ago in Import/Export
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 posted 4 years ago
Did you select the server or the client parser? (see left bottom group on the import dialog)
moh posted 4 years ago
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 posted 4 years ago
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?
moh posted 4 years ago
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 posted 4 years ago
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.
moh posted 4 years ago
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.