DaemonForums  

Go Back   DaemonForums > Miscellaneous > General software and network

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

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1   (View Single Post)  
Old 8th December 2011
J65nko J65nko is offline
Administrator
 
Join Date: May 2008
Location: Budel - the Netherlands
Posts: 4,128
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
 

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 01:23 PM.


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