Home

MySQL

Row # was truncated

Here's the solution when you're importing data into MySQL and get way too many warnings for truncated rows when doing LOAD DATA INFILE or LOAD DATA LOCAL INFILE. In my case, the file I was importing had the ^M Windows line endings. To verify, open your CSV import file in vi.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5925
Server version: 5.0.51a-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> LOAD DATA LOCAL INFILE '/Users/davidnorman/Documents/rpt/129570-MLRPT.csv' INTO TABLE `rpt_project_creator_participants` FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;
Query OK, 345682 rows affected, 65535 warnings (10.52 sec)
Records: 345682  Deleted: 0  Skipped: 0  Warnings: 748180

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 1  |
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 2  |
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 3  |
| Warning | 1262 | Row 3 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 4  |
| Warning | 1262 | Row 4 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 5  |
| Warning | 1262 | Row 5 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 6  |
| Warning | 1262 | Row 6 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 7  |
| Warning | 1262 | Row 7 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 8  |
| Warning | 1262 | Row 8 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 9  |
| Warning | 1262 | Row 9 was truncated; it contained more data than there were input columns    |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 10 |
| Warning | 1262 | Row 10 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 11 |
| Warning | 1262 | Row 11 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 12 |
| Warning | 1262 | Row 12 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 13 |
| Warning | 1262 | Row 13 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 14 |
| Warning | 1262 | Row 14 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 15 |
| Warning | 1262 | Row 15 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 16 |
| Warning | 1262 | Row 16 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 17 |
| Warning | 1262 | Row 17 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 18 |
| Warning | 1262 | Row 18 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 19 |
| Warning | 1262 | Row 19 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 20 |
| Warning | 1262 | Row 20 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 21 |
| Warning | 1262 | Row 21 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 22 |
| Warning | 1262 | Row 22 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 23 |
| Warning | 1262 | Row 23 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 24 |
| Warning | 1262 | Row 24 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 25 |
| Warning | 1262 | Row 25 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 26 |
| Warning | 1262 | Row 26 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 27 |
| Warning | 1262 | Row 27 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 28 |
| Warning | 1262 | Row 28 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 29 |
| Warning | 1262 | Row 29 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 30 |
| Warning | 1262 | Row 30 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 31 |
| Warning | 1262 | Row 31 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'field_participant_stcd_04_zero_b_value' at row 32 |
| Warning | 1262 | Row 32 was truncated; it contained more data than there were input columns   |
+---------+------+------------------------------------------------------------------------------+
64 rows in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '/Users/davidnorman/Documents/rpt/129570-MLRPT.csv' INTO TABLE `rpt_project_creator_participants` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 683835 rows affected, 1771 warnings (19.47 sec)
Records: 683835  Deleted: 0  Skipped: 0  Warnings: 1623


Submitted by deekayen on Tue, 01/25/2011 - 12:59pm
» deekayen's blog · Add new comment Topics:

MySQL 5.1 on MacOS 10.5 filevault

First download MySQL 5.1 and MySQL 5.0. The GA versions are the ones that have the prefpanel for starting MySQL, so you have to get both the GA and RC if you want bleeding edge with the GUI startup.

Note: a configuration of this type is meant for a machine with one user since only that one user will be able to decrypt the filevault and unlock the data file location so MySQL can run.

Unpack the contents of 5.1.22 to /usr/local/mysql.

Mount the 5.0.45 dmg package and drag MySQL.prefPane to /Library/PreferencePanes. This is how I start MySQL. Do not click for MySQL to automatically start, because it won't be able to start until after you login your filevaulted account. MySQL does appear to be able to survive through putting a laptop to sleep, so you can just put your machine to sleep a lot if you don't like starting MySQL a lot.

Alternatively, command line nuts can start MySQL like this:

davidnorman:mysql root# /usr/local/mysql/support-files/mysql.server start

Create a place for the MySQL data files in your filevault. I picked /Users/davidnorman/mysql/data and made a global configuration file to match which I put in /etc/mysql/my.cnf. MySQL 5.0 expects the configuration at /etc/my.cnf and you will find if you use the MySQL 5.0 GUI tools, the configuration section will look there, too. I consider this a harmless penalty for using RC code.

[mysqld_safe]
user = davidnorman
log-error = /Users/davidnorman/mysql/data/davidnorman.local.err
basedir = /usr/local/mysql
datadir = /Users/davidnorman/mysql/data
max_allowed_packet=64M

[mysqld]
user = davidnorman
log-error = /Users/davidnorman/mysql/data/davidnorman.local.err
basedir = /usr/local/mysql
datadir = /Users/davidnorman/mysql/data
max_allowed_packet=64M

Move the contents of /usr/local/mysql/data to your /Users/davidnorman/mysql/data equivalent and run /usr/local/mysql/scripts/mysql_install_db.

Start your MySQL server through Apple menu - System Preferences - MySQL. If the status doesn't turn green, check your /Users/davidnorman/mysql/data/davidnorman.local.err equivalent. My biggest problems were from permissions, which created errors that it either it couldn't find files or they were read-only. Eventually I found

$ sudo chmod -R 700 /Users/davidnorman/mysql
$ sudo chown -R davidnorman:davidnorman /Users/davidnorman/mysql

cleared it up since during other parts of the install process things were created and owned by user root or _mysql and were in a few were in the wheel group.

Set a root password if you want.

$ /usr/local/mysql/bin/mysqladmin -u root password 'newpasswordhere'


Submitted by deekayen on Tue, 11/06/2007 - 10:49am
» deekayen's blog · 2 comments Topics: · · · · ·

Resume for David Kent Norman

Drupal Profile

Drupal Association General Assembly

Education

Ph.D. Instructional Technology
University of Central Florida
Orlando, FL; August 2008; GPA 3.86/4.0
  • Dissertation title: "Predicting the performance of interpreting instruction based on Digital Propensity Index score in text and graphic formats"

M.S. Management Information Systems
University of Central Florida
Orlando, FL; August 2005; GPA 3.568/4.0

B.B.A. General Business
The University of Texas at Tyler
Tyler, TX; December 2002; GPA 3.216/4.0

  • Study abroad in Beijing, China for marketing

Work Experience

CLASSIC GRAPHICS
9/2007 - present

Development Manager

GOOGLE
12/2006 - 12/2007

Quality Rater

ADVANCED AUTOMATION INC.
8/2003 - 6/2007

Information Technology Director 8/2003 - 4/2006
Senior Consultant 4/2006 - 6/2007

THE UNIVERSITY OF TEXAS AT TYLER
2/2002 - 6/2003

Computer Technician

UNIVERSITY PINES APARTMENTS
4/2001 - 8/2001

Senior Resident Assistant

Professional Certifications

Honors and Awards

  • 2006 Recognition of Merit for Service at University of Central Florida
  • 2006 Nominated by faculty for membership in Pi Lambda Theta, an International Honor Society and Professional Association in Education
  • 2005 - 2007 Qualified for membership in Kappa Delta Pi, an International Education Honor Society
  • 1999 Texas A&M University at Galveston honorary Sea Aggie science research award
  • 1999 Lucky 13 Award for 13 years of consecutive science fair participation
  • Boy Scouts of America: Eagle Scout, Order of the Arrow, Arrow of Light

Publications

Peer Reviewed Journal

  1. Norman, D. (2006, Summer). Argument against MD5 authentication. 2600: The Hacker Quarterly, 23(2), 57.

Poster Presentations

  1. Norman, D. K., & Blasi, L. (2006, November 28). A pilot study: Instructional technology students’ perceptions of how age impacts proficiency with electronics. Poster session presented at the final meeting of Qualitative Research in Education, Orlando, FL.

  2. Norman, D. K., Hogg, J., Head, M., Weber, T., & Hirumi, A. (2006, April). Hospice comforters. Poster session presented at the annual meeting of the University of Central Florida Service-Learning Showcase, Orlando, FL.

  3. Norman, D. K. (1999, March). Ask the macroinvertebrates. Poster session presented at the annual meeting of the Science Engineering Fair of Houston, Houston, TX.

  4. Norman, D. K. (1998, March). Top to bottom: Armand Bayou. Poster session presented at the annual meeting of the Science Engineering Fair of Houston, Houston, TX.

  5. Norman, D. K. (1997, March). Moon over the creek. Poster session presented at the annual meeting of the Science Engineering Fair of Houston, Houston, TX.

Software

  1. Norman, D. K. (2006, May). ISACware (Version 2.0) [Computer software]. Retrieved from http://deekayen.net/isacware

  2. Norman, D. K. (2004, November). myBackware (Version 1.0b1) [Computer software]. Retrieved from http://deekayen.net/mybackware

  3. Norman, D. K. (2001, December). Thatware (Version 0.5.3) [Computer software]. Retrieved from http://sourceforge.net/projects/thatware/

Notable Internet Postings

  1. Norman, D. (2002, November 3). Re: problems configuring root on raid. Message posted to openbsd-misc electronic mailing list, archived at http://marc.theaimsgroup.com/?l=openbsd-misc&m=103635776223483

  2. Deekayen. (2001, September 18). A guide to OpenBSD custom kernel compilation. Hack in the Box, 21. Retrieved February 20, 2006, from http://www.hackinthebox.org/modules.php?op=modload&name=News&file=article&sid=3663

Work in Progress

  1. Norman, D. K., DaCosta, B., Nasah, A., Prickett, C., Henderson, K., White, J., et. al. (2007). Differences between Digital Natives and Digital Immigrants: A pilot study of the Digital Propensity Index questionnaire. Unpublished manuscript.

  2. Meli, P., Massiah, C., Norman, D. K., & Witta, E. L. (2006). Analysis of survey data: Impact of new technologies, employment and social affairs, and disabilities. Unpublished manuscript.

  3. Marquez, R., Norman, D. K., & Hogg, J. (2005). Changes in grade 3 population and free lunch versus national average. Unpublished manuscript.

Consulting

Xomba

Various customizing of Drupal modules.

NASA Inspired Education

Recruited as a PHP implementation expert to deploy a web portal for education related to NASA's Virtual Lab. The portal was collaboration between NASA, Saint Leo University, University of Central Florida, and the Orlando Science Center. My role primarily involved integrating HTML templates with PHP, creating a web-based lesson plan database, and a miscellaneous resource center, including blogging, forum, search, comment, and revision abilities for teachers to collaborate on lesson ideas.

Pearl Harbor Survivors Project

Featured on CNN and Good Morning America. Designed a phone tree and interface for the phone service to interact with the website software and database so survivors could leave information through a toll-free phone system and have the website grab it. Created submission system for survivors to donate photos, text, or audio on the project website.

Congress for the New Urbanism

Prepared data translation tables for data storage conversions. Wrote custom import scripts for data translation from Excel to CiviCRM

Active Living Network

3D Security (Eastern Mennonite University)

CHL Marketing
  • Migrated a Microsoft Access customer management system to a web-based platform using Linux, Apache, MySQL, and PHP.
  • Drew data entity relationship diagrams in Microsoft Visio
  • Wrote software documentation
  • Diagnosed computer hardware problems
  • Recommended software front-end packages
  • Wrote data conversion scripts and web interface

Affiliations/Memberships

Professional Development

Technical editor for Packt Publishing

University of Texas Medical Branch at Galveston Summer Science Camp participant

Conferences Attended
 Topics: · · · · · · · · · ·
Syndicate content