DaemonForums  

Go Back   DaemonForums > Miscellaneous > General software and network

General software and network General OS-independent software and network questions, X11, MTA, routing, etc.

Reply
 
Thread Tools Display Modes
  #1   (View Single Post)  
Old 8th December 2011
J65nko J65nko is offline
Administrator
 
Join Date: May 2008
Location: Budel - the Netherlands
Posts: 3,116
Thanked 182 Times in 149 Posts
Default MySQL database import not helpful

I am busy sanitizing a 250,000 record tab delimited database file. Each record has more than 50 fields.

After importing the file with the mysql util, the following is shown;
Code:
Query OK, 231369 rows affected, 65535 warnings (6.12 sec)
Records: 231369  Deleted: 0  Skipped: 0  Warnings: 5392
After some digging in the online documentation I found the show warnings statement:
Code:
 show warnings ;
[snip]
| Warning | 1366 | Incorrect integer value: '' for column 'xxxx_yyy' at row 1  |
| Warning | 1265 | Data truncated for column 'first_name' at row 1                |
| Warning | 1366 | Incorrect integer value: '' for column 'zzz' at row 1          |
[snip]
| Warning | 1366 | Incorrect integer value: '' for column 'id2' at row 13         |
+---------+------+----------------------------------------------------------------+
64 rows in set (0.01 sec)
The Incorrect integer value: can be ignored, because those fields is empty. But I really would like to know which fields have been truncated. From those 5392 warnings only 64 are shown.
But as stated at http://dev.mysql.com/doc/refman/5.1/...-warnings.html

Quote:
The max_error_count system variable controls the maximum number of error, warning, and note messages for which the server stores information, and thus the number of messages that SHOW WARNINGS displays. By default, max_error_count is 64. To change the number of messages the server can store, change the value of max_error_count.

The value of warning_count is not limited by max_error_count if the number of messages generated exceeds max_error_count.
Code:
mysql> show variables like 'max_error_count' ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+

mysql> SET max_error_count=6000;
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT @@max_error_count;
+-------------------+
| @@max_error_count |
+-------------------+
|              6000 |
+-------------------+
1 row in set (0.00 sec)
So now I should be able to check all those 5392 warnings ......
__________________
You don't need to be a genius to debug a pf.conf firewall ruleset, you just need the guts to run tcpdump
Reply With Quote
  #2   (View Single Post)  
Old 9th December 2011
Carpetsmoker's Avatar
Carpetsmoker Carpetsmoker is offline
Real Name: Martin
Old man from scene 24
 
Join Date: Apr 2008
Location: Eindhoven, Netherlands
Posts: 2,051
Thanked 198 Times in 156 Posts
Default

The first_name columns is probably defined as VARCHAR(n). Just increase the value of n and try again. Repeat if necessary.
__________________
UNIX was not designed to stop you from doing stupid things, because that would also stop you from doing clever things.
Reply With Quote
  #3   (View Single Post)  
Old 10th December 2011
J65nko J65nko is offline
Administrator
 
Join Date: May 2008
Location: Budel - the Netherlands
Posts: 3,116
Thanked 182 Times in 149 Posts
Default

Yes, that was exactly the case and that is how I fixed it.

The other error Incorrect integer value: '' for column 'id2' at row 13 was caused by importing an empty string into a int field. It should have been NULL..
The CSV used for import had a lot of empty fields like ,,,,.
__________________
You don't need to be a genius to debug a pf.conf firewall ruleset, you just need the guts to run tcpdump
Reply With Quote
Reply

Tags
mysql data import

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Read Excel Data and store into Mysql Database using PHP cksraj Programming 2 3rd June 2009 12:09 PM
zfs import terryd FreeBSD General 1 23rd November 2008 09:26 PM
Roundcube Mail Can't Connect to MYSQL Database jrs665 OpenBSD Packages and Ports 1 17th November 2008 03:54 PM
import physical freeBSD into VMWARE (ESX) server as a vServer ccc FreeBSD General 6 3rd October 2008 07:04 AM
Changing encoding of text in MySQL database stukov Programming 5 15th July 2008 09:48 PM


All times are GMT. The time now is 08:36 PM.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content copyright © 2007-2010, the authors
Daemon image copyright ©1988, Marshall Kirk McKusick