Mysql Error 1262
Contents |
solution Posted on August 13, 2009 by Alan You're importing some data into MySQL and using LOAD DATA INFILE so you can do a SHOW WARNINGS afterwards. Every line of your import file has a warning: | Warning | 1262 | Row 1 was truncated; it contained more mysql error 1262 row 1 was truncated data than there were input columns | Warning | 1262 | Row 2 was truncated; it contained mysql error 1261 more data than there were input columns …etc. The fix is easy, and you'd think of it eventually, but you thought you'd try google
Load Data Local Infile
instead. So I'm posting this so that when you do there is something to find. There wasn't much when I looked. It's the line endings. MySQL isn't getting what it expects, so specify the format of the file using LINES TERMINATED
Load Data Infile
BY ‘\r\n' or whatever is appropriate for you: ‘\r\n' for files that came from Windows systems ‘\r' for files from VMS ‘\n' for every other source. Hope that helps. This entry was posted in Geekery and tagged mysql. Bookmark the permalink. ← ★ Ninjawords: iPhone Dictionary, Censored by Apple 5th Place finisher Richard Osullivan (1474) → 26 Responses to MySQL: Row N was truncated; a solution Carla says: August 20, 2009 at 15:58 I spent too much time trying to find out why MySQL didn't recognize ‘\n' as a new line. So, I tried your suggestion ‘\r\n' and it worked. Thank you so much! huges, Carla. Reply pierig says: May 10, 2010 at 08:46 Also spent long time for this stupid error. Thanks a lot for posting and explaining so easy. Stefan Reply Stan Schwertly says: February 3, 2011 at 22:26 Wow, I can't believe how simple that was -- here I was beginning to think it was a problem with my source CSV. I love you :P! Reply obyelect says: August 17, 2011 at 16:20 you cannot believe the stress i was going through trying to figure out what the problem was, dude you my friend are awesome Reply Aritra says: April 8, 2012 at 19:24 o thanks a LOHT!!!! Reply Manu says: April 12, 2012 at 02:38 thanks a lot!! Reply francois says: October 10, 2012 at 21:55 THANK YOU ! (and google too) 😉 Reply TimK says: October 17, 2012 at 14:55 Thank you for this solution I have been trying to fix my CSV's Reply Manisha says: November 29, 2012 at 14:44 I am using \r\n also ..but still getting an error LOAD DATA LOCAL INFILE ‘C:/abc.csv' IGNORE INTO TABLE abc FIELDS TERMINATED BY ‘,' lines terminated by ‘\r\n' IGNORE 1 LINES; 311347 row(s) affected,64 warning(s): 1262 row 26 was truncated;it contained more data than there were input columns.. Can anyone help? Reply Alan says: November 29, 2012 at 15:31
tsubasa tanaka (OCA) Email Updates: Status: Verified Impact on me: None Category:MySQL Server: DML Severity:S3 (Non-critical) Version:5.6.20 OS:Linux (CentOS 6.3) Assigned to: Tags: strict_trans_tables, warning View Add Comment Files Developer Edit Submission View Progress Log Contributions [20 Aug 2014 9:57] tsubasa tanaka Description: LOAD DATA LOCAL INFILE without REPLACE keyword *ignores* data-interpretation error even under STRICT_TRANS_TABLES sql_mode. But LOAD DATA LOCAL INFILE with REPLACE *doesn't ignore* http://www.alanjames.org/2009/08/mysql-row-n-was-truncated-a-solution/ data-interpretation error but raises warning instead of error, this doesn't work under STRICT_TRANS_TABLES sql_mode. Document, http://dev.mysql.com/doc/refman/5.6/en/load-data.html , says " With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key errors become warnings". I think "LOAD DATA LOCAL INFILE with REPLACE"'s behavier is correct, "without REPLACE"'s behavier is incorrect. How to repeat: $ echo -e "1\tone" https://bugs.mysql.com/bug.php?id=73654 > /tmp/test mysql56> SELECT @@sql_mode; +---------------------+ | @@sql_mode | +---------------------+ | STRICT_TRANS_TABLES | +---------------------+ 1 row in set (0.00 sec) mysql56> CREATE TABLE t1 (num int); Query OK, 0 rows affected (0.07 sec) mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql56> show warnings; +---------+------+---------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------+ | Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns | +---------+------+---------------------------------------------------------------------------+ 1 row in set (0.00 sec) * This behavier is ignoring data-interpretation error. * This doesn't raise an error even in STRICT_TRANS_TABLES) mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1; ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns * This behavier is not ignoring data-iterpretation error. This differences come from following lines in sql/sql
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get https://www.experts-exchange.com/questions/28482254/MySQL-1262-Error.html Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > MySQL 1262 Error Want to Advertise Here? Solved MySQL 1262 Error Posted on 2014-07-23 MySQL Server Databases Web Development 1 Verified Solution 4 Comments 1,638 Views Last Modified: 2014-07-25 Hi, I get this error when I import a CSV. Warning (1262): Row 1 was mysql error truncated; it contained more data than there were input columns */ See attached screenshot for row 1 See attached screenshot of my Structure Also, what is the best collation to use? Row-1.png Structure.png 0 Question by:Computer Guy Facebook Twitter LinkedIn Google LVL 25 Active today Best Solution bySStory What is the delimiter set to? If it were a space that would cause the problem. If it is a comma, why don't mysql error 1262 you show part of the CSV? If the fields have spaces longer than 50 they could be truncating Go to Solution 4 Comments LVL 25 Overall: Level 25 Web Development 5 Databases 4 MySQL Server 3 Message Active today Accepted Solution by:SStory2014-07-23 What is the delimiter set to? If it were a space that would cause the problem. If it is a comma, why don't you show part of the CSV? If the fields have spaces longer than 50 they could be truncating inside, but it sounds like it is getting more columns that in your database which leads me to suspect the delimiter is a space. Sample below from link below that. LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, filed2, field3); Select all Open in new window (source: http://support.modwest.com/content/6/253/en/how-do-i-import-delimited-data-into-mysql.html) 0 LVL 3 Overall: Level 3 Web Development 1 Message Author Comment by:Computer Guy2014-07-23 Enya,A Day Without Rain,Processional Songs See screen shot of how I import it. data.png 0 LVL 25 Overall: Level 25 Web Development 5 Databases 4 MySQL Server 3 Message Active today Expert Comment by:SStory2014-07-23 I wonder if the AUTO_INCREMENT column is throwing you off. Does it work if there is an ID in