| Bug #13588 | MysqlDataTruncation | ||
|---|---|---|---|
| Submitted: | 28 Sep 2005 22:23 | Modified: | 31 Mar 2014 9:42 |
| Reporter: | Axel Muench | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | 3.1.10 | OS: | Any (any) |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
[28 Sep 2005 22:34]
Axel Muench
I have changed all db fields from unsigned to signed and the problem vanished as well. So it seems that Connector/J 3.1.10 is not handling unsigned db fields correctly such as version 3.1.6 did.
[28 Sep 2005 22:59]
Mark Matthews
What method are you using to insert the value(s), and _what_ values are you inserting (we need this to be able to repeat your issue).
[28 Sep 2005 23:11]
Axel Muench
I guess the values are visible in the previously posted INSERT statement.
Here are the lines responsible for executing the query:
Let me know if you need more info,
Best
Axel
prepStmt = con.prepareStatement(INSERT_PRODUCT);
prepStmt.setString(1, product.getName());
prepStmt.setString(2, product.getDescription());
prepStmt.setString(3, product.getDescriptionLong());
prepStmt.setString(4, product.getSku());
prepStmt.setLong(5, product.getInventory());
prepStmt.setInt(6, product.getMaintainInventory());
prepStmt.setInt(7, product.getFeatured());
prepStmt.setDouble(8, product.getPrice());
prepStmt.setDouble(9, product.getWholeSalePrice());
prepStmt.setString(10, product.getPixUrl1());
prepStmt.setString(11, product.getPixUrl2());
prepStmt.setString(12, product.getPixUrl3());
prepStmt.setString(13, product.getPixUrl4());
prepStmt.setInt(14, product.getVendorId());
prepStmt.setDouble(15, product.getWeight());
prepStmt.setInt(16, product.getDimX());
prepStmt.setInt(17, product.getDimY());
prepStmt.setInt(18, product.getDimZ());
prepStmt.setInt(19, product.getActive());
prepStmt.setInt(20, product.getHotDeal());
prepStmt.setInt(21, product.getGenForeignKey1());
prepStmt.setInt(22, product.getGenForeignKey2());
prepStmt.setInt(23, product.getTaxExempt());
prepStmt.setInt(24, product.getGiftWrap());
prepStmt.setInt(25, product.getPix1W());
prepStmt.setInt(26, product.getPix1H());
prepStmt.setInt(27, product.getPix2W());
prepStmt.setInt(28, product.getPix2H());
prepStmt.setInt(29, product.getPix3W());
prepStmt.setInt(30, product.getPix3H());
prepStmt.setInt(31, product.getPix4W());
prepStmt.setInt(32, product.getPix4H());
prepStmt.setDouble(33, product.getSalePrice());
prepStmt.setString(34, product.getOutOfStockNote());
success = prepStmt.executeUpdate();
[29 Sep 2005 2:41]
Mark Matthews
Could you please post the _full_ stack trace of the exception you're receiving? I still can't repeat this on my end, so I'd like to be able to see where in the driver this error is coming from, which the stack trace would give us.
[29 Sep 2005 5:11]
Axel Muench
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated; out of range for column 'gen_foreign_key2' at row 1 at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:709) at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3402) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1801) at com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101) at com.specto.ecom.admin.dao.AdminDAO.addNewProduct(AdminDAO.java:2565) at com.specto.ecom.admin.framework.AdminOptions.addProduct(AdminOptions.java:756) at com.specto.ecom.admin.actions.ProductActions.create(ProductActions.java:626) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:276) at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:196) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:421) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:415) at javax.servlet.http.HttpServlet.service(HttpServlet.java:709) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:534)
[1 Oct 2005 13:45]
Valeriy Kravchuk
Can you, please, provide the real text fo statement you are trying to execute? INSERT_PRODUCT, I mean. According to the trace, you may simply passe a wrong value to the column. According to the list of parameters, you do not provide id value (it is reasonable, because this column is auto_increment), but isn't it included itno the INSERT statement itself?
[1 Oct 2005 16:03]
Axel Muench
private static final String INSERT_PRODUCT =
"INSERT INTO Product (name, description, description_long, sku, inventory, " +
"maintain_inventory, featured, price, wholesale_price, pix_url1, pix_url2, pix_url3, pix_url4, " +
"vendor_id, weight, dim_x, dim_y, dim_z, active, hot_deal, gen_foreign_key1, gen_foreign_key2, " +
"tax_exempt, gift_wrap, pix1_w, pix1_h, pix2_w, pix2_h, pix3_w, pix3_h, pix4_w, pix4_h, sale_price, out_of_stock_msg, free_shipping) " +
"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
[1 Oct 2005 18:03]
Axel Muench
The INSERT statement has actually been modified since yesterday - a free_shipping parameter has been added to the original statement. Please remove this parameter for your testing.
[2 Oct 2005 8:56]
Valeriy Kravchuk
Thank you for the prompt reply. And what value does product.getGenForeignKey2() really return? Can you printout it somehow? Looks like it is out of range for int(11) unsigned column.
[2 Oct 2005 15:34]
Axel Muench
It is the default java int value from the transport object, which should be 0.
[18 Oct 2005 7:20]
Vasily Kishkin
I guess we need some test case. Could you please create and attach some test case on java ?
[19 Nov 2005 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[31 Mar 2014 9:42]
Alexander Soklakov
I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.
Description: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated; out of range for column 'dim_y' at row 1 Connector/J release 3.1.10 with mysql version 4.1.7 throws MysqlDataTruncation Exception when inserting a java int into integer db field. There is no rhyme or reason why it is a particular db field at this point. I have switched the Connector/J to version 3.1.6 and the problem vanished. Please advise. How to repeat: CREATE TABLE `Product` ( `id` int(11) unsigned NOT NULL auto_increment, `name` varchar(100) default NULL, `description` varchar(150) default NULL, `description_long` text, `sku` varchar(30) default NULL, `inventory` bigint(20) NOT NULL default '0', `maintain_inventory` tinyint(1) unsigned NOT NULL default '0', `featured` tinyint(1) unsigned NOT NULL default '0', `price` double unsigned NOT NULL default '0', `wholesale_price` double unsigned default NULL, `pix_url1` varchar(100) default NULL, `pix_url2` varchar(100) default NULL, `pix_url3` varchar(100) default NULL, `pix_url4` varchar(100) default NULL, `vendor_id` int(11) default NULL, `weight` double default NULL, `dim_x` smallint(4) unsigned default NULL, `dim_y` smallint(4) unsigned zerofill default NULL, `dim_z` smallint(4) unsigned zerofill default NULL, `active` tinyint(1) unsigned NOT NULL default '0', `hot_deal` tinyint(1) unsigned NOT NULL default '0', `gen_foreign_key1` int(11) unsigned NOT NULL default '0', `gen_foreign_key2` int(11) unsigned NOT NULL default '0', `tax_exempt` tinyint(1) unsigned NOT NULL default '0', `gift_wrap` tinyint(1) unsigned NOT NULL default '0', `category_id` int(11) unsigned default NULL, `pix1_w` smallint(6) default '0', `pix1_h` smallint(6) default '0', `pix2_w` smallint(6) default '0', `pix2_h` smallint(6) default '0', `pix3_w` smallint(6) default '0', `pix3_h` smallint(6) default '0', `pix4_w` smallint(6) default '0', `pix4_h` smallint(6) default '0', `sale_price` double NOT NULL default '-1', `color_scheme` tinyint(2) unsigned default NULL, `has_variants` tinyint(1) unsigned default '0', `out_of_stock_msg` varchar(150) default NULL, PRIMARY KEY (`id`), KEY `name_index` (`name`), KEY `active_index` (`active`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO `Product` (`id`,`name`,`description`,`description_long`,`sku`,`inventory`,`maintain_inventory`,`featured`,`price`,`wholesale_price`,`pix_url1`,`pix_url2`,`pix_url3`,`pix_url4`,`vendor_id`,`weight`,`dim_x`,`dim_y`,`dim_z`,`active`,`hot_deal`,`gen_foreign_key1`,`gen_foreign_key2`,`tax_exempt`,`gift_wrap`,`category_id`,`pix1_w`,`pix1_h`,`pix2_w`,`pix2_h`,`pix3_w`,`pix3_h`,`pix4_w`,`pix4_h`,`sale_price`,`color_scheme`,`has_variants`,`out_of_stock_msg`) VALUES ('','test','test in PDF format.','test','GB-D','0','0','0','20','0','','','','','-1','0','0','0','-1','1','0','0','0','0','0',NULL,'0','0','0','0','0','0','0','0','-1',NULL,'0','');