Data too long for column
Posted by: Rebecca Vest
Date: March 21, 2006 04:34PM

I am using PHP to do an INSERT statement but it comes back with the error “Data too long for column 'DataText' at row 2”. I’ve attached the PHP code and the create statement of the table it uses. My table has the character set of UTF8. I tried “SET NAMES utf8” before the insert. I’ve also tried setting in mysql ini file the default-character-set=utf8 and then restarting the MySQL service. I get the same error for either result.

CREATE TABLE `documents` (
`DocumentID` int(11) NOT NULL auto_increment,
`ParentDocumentID` int(11) default NULL,
`Version` int(11) default NULL,
`RefNumPre` varchar(50) character set latin1 default NULL,
`RefNumInt` int(11) default NULL,
`RefNumPost` varchar(50) character set latin1 default NULL,
`ReviewInterval` int(11) default NULL,
`ReadInterval` int(11) default NULL,
`Title` varchar(255) character set latin1 default NULL,
`DataText` longtext character set latin1,
`DataIsPopulated` tinyint(4) default '0',
`DataType` varchar(255) character set latin1 default NULL,
`DataLength` varchar(50) character set latin1 default NULL,
`DataFilenameExt` varchar(50) character set latin1 default NULL,
`DataFilename` varchar(255) character set latin1 default NULL,
`UseFileForData` tinyint(4) NOT NULL default '0',
`DateCreated` datetime default NULL,
`EffectiveDate` datetime default NULL,
`DateSubmit` datetime default NULL,
`DateStateChanged` datetime default NULL,
`DateExpire` datetime default NULL,
`DateApproved` datetime default NULL,
`ViewType` varchar(25) character set latin1 default NULL,
`Status` varchar(50) character set latin1 default NULL,
`CurrentProcessLevel` int(11) default NULL,
`Inactive` tinyint(4) NOT NULL default '0',
`CreatedByUserID` int(11) default NULL,
`OwnerUserID` int(11) default NULL,
`CheckedOutUserID` int(11) default '0',
`CheckedOutDateTime` datetime default NULL,
`CheckedOutType` varchar(50) character set latin1 default NULL,
`VersionChangeSummary` varchar(2000) character set latin1 default NULL,
`TemplateID` int(11) default NULL,
`IsTemplate` tinyint(4) NOT NULL default '0',
`Deleted` tinyint(4) NOT NULL default '0',
`InternalProcessNumber` int(11) default '0',
`PDFUpdated` datetime default NULL,
`ReminderEmailSent` datetime default NULL,
`Keywords` longtext character set latin1,
`DateArchived` datetime default NULL,
`UpdateWord` tinyint(4) default NULL,
PRIMARY KEY (`DocumentID`),
KEY `FK_Documents_ParentDocumentID_Documents_DocumentID` (`ParentDocumentID`),
KEY `FK_Documents_CreatedByUserID_Users_UserID` (`CreatedByUserID`),
KEY `FK_Documents_OwnerUserID_Users_UserID` (`OwnerUserID`),
KEY `FK_Documents_TemplateID_Documents_DocumentID` (`TemplateID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

<?php
$dbHost = "localhost";
$dbPort = "5356";
$dbName = "wksp4_0";
$dbUsername = "root";
$dbPass = "ppm";

$objConn = new mysqli($dbHost,$dbUsername,$dbPass,$dbName,$dbPort);
$objConn->query("SET NAMES utf8");

$strSql = "INSERT INTO Documents (DOCUMENTID,PARENTDOCUMENTID,VERSION,REFNUMPRE,REFNUMINT,REFNUMPOST,REVIEWINTERVAL,READINTERVAL,TITLE,DATATEXT,DATAISPOPULATED,DATATYPE,DATALENGTH,DATAFILENAMEEXT,DATAFILENAME,USEFILEFORDATA,DATECREATED,EFFECTIVEDATE,DATESUBMIT,DATESTATECHANGED,DATEEXPIRE,DATEAPPROVED,VIEWTYPE,STATUS,CURRENTPROCESSLEVEL,INACTIVE,CREATEDBYUSERID,OWNERUSERID,CHECKEDOUTUSERID,CHECKEDOUTDATETIME,CHECKEDOUTTYPE,VERSIONCHANGESUMMARY,TEMPLATEID,ISTEMPLATE,DELETED,INTERNALPROCESSNUMBER,PDFUPDATED,REMINDEREMAILSENT,KEYWORDS,DATEARCHIVED)
VALUES
(1,1,1,_utf8' ',1,_utf8' ',12,0,_utf8'Dress Code',_utf8'Dress code is such that you have to wear business casual clothing everyday except for Friday.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-20 16:29:04',null,null,null,null,null,'specified','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2006-01-23 22:26:39',null,_utf8' ',null),
(2,2,1,_utf8' ',2,null,12,0,_utf8'Game Policy',_utf8'Gaming usually consists of Age of Empires. It’s on an as-needed basis usually Friday.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-20 16:32:14',null,null,null,null,null,'specified','draft',NULL,0,1,1,1,'2005-11-21 19:11:40',null,null,NULL,0,0,0,'2005-10-20 17:59:25',null,_utf8' ',null),
(3,3,1,_utf8' ',3,_utf8' ',12,0,_utf8'Friday Document',_utf8'This document will contain the word Friday just to make it show up in the search.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-24 17:03:40',null,null,null,null,null,'private','draft',NULL,0,1,1,1,'2006-02-27 17:18:35',null,null,NULL,0,0,0,'2005-10-25 15:49:37',null,_utf8' ',null),
(4,4,1,_utf8' ',4,null,12,0,_utf8'Final Work Day',_utf8'Friday will be observed as the last day of the week.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:40:46',null,null,null,null,null,'private','draft',NULL,0,1,1,1,'2006-01-13 16:34:28',null,null,NULL,0,0,0,'2005-11-28 17:15:24',null,_utf8' ',null),
(5,5,1,_utf8' ',5,_utf8' ',12,0,_utf8'Free Popcorn',_utf8'Friday will be popcorn day. Please be prepared to purchase if you want extra.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:42:35',null,null,null,null,null,'private','draft',NULL,0,10,10,1,'2006-02-27 17:18:50',null,null,NULL,0,0,0,'2005-11-16 23:35:51',null,_utf8' ',null),
(6,6,1,_utf8' ',6,null,12,0,_utf8'Payday',_utf8'Paydal will be the last Friday of the month.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:44:42',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-10-25 15:46:35',null,_utf8' ',null),
(7,7,1,_utf8' ',7,null,12,0,_utf8'Meaning of \'Viernes\'',_utf8'The word ‘Viernes’ is commonly known as “Friday” in the English-speaking language. \r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:49:54',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-10-25 15:50:51',null,_utf8' ',null),
(8,8,1,_utf8' ',8,null,12,0,_utf8'Spouse Date Night',_utf8'Date Night, the night when you go out with your Spouse, is generally practiced on Friday or Saturday; however, it can be any day of the week desired by the couple.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:51:41',null,null,null,null,null,'private','draft',NULL,0,1,1,1,'2005-12-12 23:54:15',null,null,NULL,0,0,0,'2005-10-25 15:52:40',null,_utf8' ',null),
(9,9,1,_utf8' ',9,null,12,0,_utf8'Black Friday',_utf8'The day following Thanksgiving is commonly known as “Black Friday” because it is widely accepted as the biggest shopping day of the year. Hence, the ink used that day to write the profit in is usually black.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:53:17',null,'2005-12-12 23:54:12',null,'2006-12-12 23:54:12','2005-12-12 23:54:12','private','approved',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-11-21 21:39:02',null,_utf8' ',null),
(10,10,1,_utf8' ',10,null,12,0,_utf8'Chocolate Milk Day',_utf8'Though most days white milk is given out, Friday will be the day that Chocolate milk is given.\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-25 15:56:44',null,'2005-11-18 20:12:53','2005-11-18 20:12:55','2006-11-18 20:12:55','2005-11-18 20:12:55','private','approved',2,0,1,1,1,'2005-12-13 21:49:09',null,null,NULL,0,0,1,'2005-10-25 15:57:49',null,_utf8' ',null),
(11,11,1,_utf8' ',11,_utf8' ',12,0,_utf8'High Morale Day',_utf8'Friday is the day that will be officially recognized as the High Employee Mo',1,'application/msword','20992',_utf8'doc',null,0,'2005-10-25 15:58:37',null,null,null,null,null,'private','draft',NULL,0,1,1,1,'2006-02-28 14:52:42',null,null,NULL,0,0,0,'2005-10-25 16:52:31',null,_utf8' ',null),
(12,12,1,_utf8' ',1,null,1,0,_utf8'Test document in AD mode',_utf8'This is a test – and changed by the test user account who does not have very much access to stuff\r\n\r\nHere’s the 2nd set of stuff I’ve changed….\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-31 18:24:41',null,null,null,null,null,'private','draft',NULL,0,10,10,10,'2005-10-31 19:19:26',null,null,NULL,0,0,0,'2005-10-31 18:38:33',null,_utf8' ',null),
(13,13,1,_utf8' ',2,null,3,0,_utf8'test user ad account',_utf8'test\r\n',1,'application/msword','20992',_utf8'doc',null,0,'2005-10-31 18:30:54',null,null,null,null,null,'private','draft',NULL,0,1,1,10,'2005-10-31 19:39:20',null,null,NULL,0,0,0,'2005-10-31 19:19:15',null,_utf8' ',null),
(14,14,1,_utf8' ',5,null,5,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 18:51:46',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(15,15,1,_utf8' ',5,null,1,0,_utf8'testasdf',_utf8'Test saved using the done key\r\n\r\nThis is a test – add3ed new stuff\r\n',1,'application/msword','21504',_utf8'doc',null,0,'2005-10-31 18:54:38',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-10-31 19:18:37',null,_utf8' ',null),
(16,16,1,_utf8' ',54,null,5,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 19:03:30',null,null,null,null,null,'private','draft',NULL,0,10,10,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(17,17,1,_utf8' ',222,null,5,0,_utf8'asdf',_utf8'test\r\n',1,'application/msword','20992',_utf8'doc',null,0,'2005-10-31 19:35:59',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-10-31 19:38:02',null,_utf8' ',null),
(18,18,1,_utf8' ',111,null,12,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 19:50:21',null,null,null,null,null,'private','draft',NULL,0,1,NULL,10,'2005-11-10 20:08:00',null,null,NULL,0,0,0,null,null,null,null),
(19,19,1,_utf8' ',111111,null,2,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 19:52:05',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(20,20,1,_utf8' ',222222,null,12,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 19:57:01',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(21,21,1,_utf8' ',1231,null,12,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 20:00:17',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(22,22,1,_utf8' ',12,null,12,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 20:02:00',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(23,23,1,_utf8' ',11111,null,21,0,_utf8'sdf',null,0,null,null,null,null,0,'2005-10-31 20:03:48',null,null,null,null,null,'private','draft',NULL,0,1,NULL,1,'2006-02-07 22:24:07',null,null,NULL,0,0,0,null,null,null,null),
(24,24,1,_utf8' ',43,null,4,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 20:05:16',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(25,25,1,_utf8' ',111213,null,1,0,_utf8'test',null,0,null,null,null,null,0,'2005-10-31 20:20:25',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(26,26,1,_utf8' ',1212,null,2,0,_utf8'asdf',null,0,null,null,null,null,0,'2005-10-31 20:50:48',null,null,null,null,null,'private','draft',NULL,0,10,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(27,27,1,_utf8' ',212,null,12,0,_utf8'yooooo',_utf8'This is the AD logged in as rscott\r\nTestasdfkTestasdfkTestasdfkeasdfasdfsadf lsakdjf l;ksadjf lkajd fla\r\nEflawgjsasddsfskldf ;sldjkf lasdjf;aslkdf jlksdjf laskdjf sldjfow iegstasdfkestasdfk\r\nTestasdfkestasdfkTestasdfkTestasdfkTestasdfkTestasdfk\r\nTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfk\r\nTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkTestasdf\r\nTestasdfkTestasdfkTestasdfkTestasdfkTestasdfkdf\r\n',1,'application/msword','22528',_utf8'doc',null,0,'2005-10-31 20:59:26',null,null,null,null,null,'private','draft',NULL,0,10,10,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(28,28,1,_utf8' ',1000,null,1,0,_utf8'test through built in ad',null,1,'application/msword','20992',_utf8'doc',null,0,'2005-10-31 21:08:46',null,null,null,null,null,'private','draft',NULL,0,10,10,1,'2005-11-16 23:27:36',null,null,NULL,0,0,0,null,null,_utf8' ',null),
(29,29,1,_utf8' ',12093,null,121,0,_utf8'test file',null,0,null,null,null,null,0,'2005-10-31 23:08:17',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-10-31 23:22:19',null,_utf8' ',null),
(30,30,88,_utf8' ',88,null,88,0,_utf8'boooo',null,0,null,null,null,null,0,'2005-10-31 23:41:14',null,null,null,null,null,'private','draft',NULL,0,10,10,1,'2005-12-08 14:33:07',null,null,NULL,0,0,0,null,null,_utf8' ',null),
(31,31,1,_utf8' ',1111,null,1111,0,_utf8'1 - asdf',null,1,'application/msword','21504',_utf8'doc',null,0,'2005-10-31 23:45:25',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(32,32,1,_utf8' ',1,_utf8'; delete * from documens',2,0,_utf8'test',null,0,null,null,null,null,0,'2005-11-03 16:37:26',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(33,33,1,null,NULL,null,22,0,_utf8'test',null,0,null,null,null,null,0,'2005-11-03 16:50:45',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(34,34,1,null,NULL,null,2,0,_utf8'hacked document',null,0,null,null,null,null,0,'2005-11-03 17:17:57',null,null,null,null,null,'private','draft',NULL,0,1,NULL,1,'2006-01-23 22:25:40',null,null,NULL,0,0,0,'2005-11-16 15:29:29',null,null,null),
(35,35,1,_utf8' ',1,null,1,0,_utf8'\'',null,0,null,null,null,null,0,'2005-11-03 17:30:57',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(36,36,1,_utf8' ',11123,null,123,0,_utf8'asdfasdf',null,0,null,null,null,null,0,'2005-11-04 19:06:58',null,null,null,null,null,'private','draft',NULL,0,10,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(37,37,1,_utf8' ',123,null,2,0,_utf8'test',null,0,null,null,null,null,0,'2005-11-04 19:08:48',null,null,null,null,null,'private','draft',NULL,0,10,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(38,38,1,_utf8' ',12,null,12,0,_utf8'test',null,0,null,null,null,null,0,'2005-11-08 21:02:53',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(39,39,1,null,NULL,null,NULL,NULL,_utf8'Tempate',null,0,null,null,null,null,0,'2005-11-21 21:22:26',null,null,null,null,null,null,'draft',NULL,0,1,NULL,0,null,null,null,NULL,1,0,0,null,null,null,null),
(40,40,1,null,NULL,null,NULL,NULL,_utf8'test',null,0,null,null,null,null,0,'2005-11-21 21:22:50',null,null,null,null,null,null,'draft',NULL,0,1,NULL,0,null,null,null,NULL,1,0,0,null,null,null,null),
(41,41,1,_utf8' ',13,null,12,0,_utf8'Sample XML Document',null,1,'text/xml','8203',_utf8'xml',null,0,'2005-11-22 19:12:39',null,null,null,null,null,'private','draft',NULL,0,1,1,1,'2005-12-06 19:52:32',null,null,NULL,0,0,0,null,null,_utf8' ',null),
(42,42,1,_utf8' ',14,null,12,0,_utf8'First Day of Week',null,1,'application/msword','21504',_utf8'doc',null,0,'2005-11-28 17:51:38',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,'2005-11-30 23:29:52',null,_utf8' ',null),
(43,43,1,_utf8' ',15,null,12,0,_utf8'test',null,1,'text/xml','8203',_utf8'xml',null,0,'2005-12-07 18:53:34',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(44,44,1,_utf8' ',16,null,12,0,_utf8'document named with number',null,1,'application/msword','23040',_utf8'doc',null,0,'2005-12-08 14:00:04',null,'2005-12-08 14:42:52','2005-12-08 14:53:27',null,null,'private','draft',1,0,1,1,0,null,null,null,NULL,0,0,1,null,null,_utf8' ',null),
(45,45,1,_utf8' ',17,_utf8' ',12,0,_utf8'New Document',_utf8'This is a',1,'application/msword','21504',_utf8'doc',null,0,'2006-02-07 22:20:19',null,null,null,null,null,'private','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(46,46,1,_utf8' ',18,_utf8' ',12,0,_utf8'PesterCat Test',_utf8'tes',1,'application/msword','21504',_utf8'doc',null,0,'2006-02-07 22:24:21',null,null,null,null,null,'specified','draft',NULL,0,1,1,0,null,null,null,NULL,0,0,0,null,null,_utf8' ',null),
(47,47,1,_utf8' ',19,_utf8' ',12,0,_utf8'PesterCat 2 Test',null,0,null,null,null,null,0,'2006-02-07 22:29:50',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(48,48,1,_utf8' ',20,_utf8' ',12,0,_utf8'asdf',null,0,null,null,null,null,0,'2006-02-07 22:31:16',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(49,49,1,_utf8' ',21,_utf8' ',12,0,_utf8'asdf',null,0,null,null,null,null,0,'2006-02-07 22:31:46',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null),
(50,50,1,_utf8' ',22,_utf8' ',12,0,_utf8'Pester Doc 2',null,0,null,null,null,null,0,'2006-02-07 22:32:53',null,null,null,null,null,'private','draft',NULL,0,1,NULL,0,null,null,null,NULL,0,0,0,null,null,null,null)";

$objConn->query($strSql);
echo $objConn->errno.": ".$objConn->error."\n";
echo $objConn->character_set_name();
?>



Running the following:
IIS 5 on Windows XP Professional
PHP 5.0.4
MySQL 5.0.18

Options: ReplyQuote


Subject
Views
Written By
Posted
Data too long for column
3703
March 21, 2006 04:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.