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)
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)
> 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
> 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