com.mysql.jdbc.MysqlDataTruncation

Thread view:

cmk128@hotmail.com - 28 Oct 2006 05:35 GMT

Hi
I am trying to insert my file content to the Mysql 5.0, i am trying
to insert 1000 files, but when inserting some of the file, i got an
exception, why?

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for
column 'content' at row 1
    at
com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:709)
    at
com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3461)
    at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1198)
    at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
    at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
    at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
    at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)

The file that cause the exception is arround 85KB. and here is the
table structure:

CREATE TABLE `articles` (
`id` int(11) NOT NULL auto_increment,
`major` text NOT NULL,
`minor` text NOT NULL,
`author` text NOT NULL,
`url` text NOT NULL,
`topic` text NOT NULL,
`content` text NOT NULL,
`filename` text NOT NULL,
`filesize` int(11) NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`rank` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312

thanks
from Peter (cmk128@hotmail.com)

Reply to this Message

David Harper - 28 Oct 2006 07:44 GMT

> Hi
> I am trying to insert my file content to the Mysql 5.0, i am trying
[quoted text clipped - 34 lines]
> `rank` int(11) NOT NULL default '0',\n PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=gb2312

The online manual,

http://dev.mysql.com/doc/refman/4.1/en/index.html

is your friend.

The manual explains the limitations of the different data types:

http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html

A TEXT column can store no more than 65534 bytes (that's 2^16 minus the
two-byte length field).

The manual page which describes BLOB and TEXT types,

http://dev.mysql.com/doc/refman/4.1/en/blob.html

says the following:

"If you assign a value to a BLOB or TEXT column that exceeds the data
type's maximum length, the value is truncated to fit. If the
truncated characters are not spaces, a warning is generated."

85K is larger than 65534, so you get an exception.

The solution is to declare the "content" column as a MEDIUMTEXT if you
can be sure that your files will never exceed ~ 16 megabytes in size, or
as a LONGTEXT if you think you may need to store files larger than 16
megabytes at some point in the future.

David Harper
Cambridge, England

Reply to this Message

com.mysql.jdbc.MysqlDataTruncation

Free Magazines