| Bug #7461 | com.mysql.jdbc.MysqlDataTruncation error using load data infile | ||
|---|---|---|---|
| Submitted: | 21 Dec 2004 19:27 | Modified: | 31 Mar 2014 8:04 |
| Reporter: | David Marshall | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 3.1.5-gamma | OS: | Linux (Fedora Core 3) |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
[21 Dec 2004 20:15]
Mark Matthews
Does your code call Statement.getWarnings()? I'm trying to track down the NPE. The DataTruncation exception is as-expected, it's a new feature, and is required for JDBC compliance. You can turn off the DataTruncation functionality by using the connection property "jdbcCompliantTruncation" to 'false' (it's set to 'true' by default).
[22 Dec 2004 15:19]
David Marshall
Yes, I'm calling statement.getWarning() - no warnings logged in the default configuration (jdbcCompliantTruncation=true).
N.B. I don't understand why JDBC truncation rules apply as I'm not using "LOAD DATA LOCAL INFILE", just a "LOAD DATA INFILE" statement.
When I rerun with jdbcCompliantTruncation=false I get warnings returned, and no exception. Eventually I do get the NullPointerException, this time calling getWarning() - see attached log extract and stack trace:
2004-12-22 09:53:23 INFO test: loading CSV tables...
2004-12-22 09:53:23 WARN test:
--- Warning ---
2004-12-22 09:53:23 WARN test: SQLState: 01000
2004-12-22 09:53:23 WARN test: Message : Data truncated for column 'OFFSET_FOR_FIRST_CHECKIN_DATE' at row 16
2004-12-22 09:53:23 WARN test: Vendor : 1265
2004-12-22 09:53:24 WARN test:
--- Warning ---
2004-12-22 09:53:24 WARN test: SQLState: 01000
2004-12-22 09:53:24 WARN test: Message : Data truncated for column 'LOS' at row 2025
2004-12-22 09:53:24 WARN test: Vendor : 1265
2004-12-22 09:54:37 WARN test:
--- Warning ---
2004-12-22 09:54:37 WARN test: SQLState: 01000
2004-12-22 09:54:37 WARN test: Message : Data truncated for column 'FIRST_CHECKIN_DATE_CHAR' at row 138
2004-12-22 09:54:37 WARN test: Vendor : 1265
2004-12-22 09:54:39 WARN test:
--- Warning ---
2004-12-22 09:54:39 WARN test: SQLState: 01000
2004-12-22 09:54:39 WARN test: Message : Data truncated for column 'PROPERTY_ID' at row 7257
2004-12-22 09:54:39 WARN test: Vendor : 1265
2004-12-22 09:54:53 WARN test:
--- Warning ---
2004-12-22 09:54:53 WARN test: SQLState: 01000
2004-12-22 09:54:53 WARN test: Message : Data truncated for column 'PROPERTY_ID' at row 50476
2004-12-22 09:54:53 WARN test: Vendor : 1265
2004-12-22 09:54:53 FATAL test: Fatal error:
java.lang.NullPointerException
at com.mysql.jdbc.Statement.getWarnings(Statement.java:690)
at Test.showWarnings(Test.java:335)
at Test.closeConnection(Test.java:278)
at Test.run(Test.java:258)
at Test.main(Test.java:922)
There is a an extract of the first 50 lines or so in the first file (first truncation occurs on row 16, data looks fine to me).
Thanks.
[22 Dec 2004 15:53]
Mark Matthews
> N.B. I don't understand why JDBC truncation rules apply as I'm not using "LOAD > DATA LOCAL INFILE", just a "LOAD DATA INFILE" statement. Truncation warnings can come from _any_ statement, the 'local' part of infile wouldn't affect that.
[14 Feb 2005 22:54]
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".
[16 Jul 2009 15:37]
Hering Cheng
I was also getting com.mysql.jdbc.MysqlDataTruncation with version 3.1.8 of JDBC driver. After upgrading to the current 5.1.8 version, the error is now more explicit:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2009-06-01 04:00:00 +0000' for column 'start' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3534)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:917)
at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:3433)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2552)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1755)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2165)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1634)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1553)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
So basically the JDBC driver does not like the value '2009-06-01 04:00:00 +0000' for a DATETIME column. However, the same LOAD FILE command and date file works from within EMS MySQL Manager Lite for Windows 3.4.0.4.
[31 Mar 2014 8:04]
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: Current gamma JDBC driver fails with an exception on a load data infile. Substituting the current stable JDBC driver and running the same statement on the same file succeeds. A stack trace is shown below. Note the chained NullPointerException that follows the first trace: 2004-12-21 13:49:20 FATAL rvsim: errorCode: 0 SQLState: 01004 com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'OFFSET_FOR_FIRST_CHECKIN_DATE' at row 16 at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:695) at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3319) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1744) at com.mysql.jdbc.Connection.execSQL(Connection.java:2280) at com.mysql.jdbc.Connection.execSQL(Connection.java:2211) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1262) at net.travelclick.rvsim.Simulator.executeUpdate(Simulator.java:191) at net.travelclick.rvsim.Simulator.executeUpdate(Simulator.java:177) at net.travelclick.rvsim.Simulator.loadCSVtables(Simulator.java:293) at net.travelclick.rvsim.Simulator.run(Simulator.java:135) at net.travelclick.rvsim.Simulator.main(Simulator.java:862) 2004-12-21 13:49:20 FATAL rvsim: Fatal error: java.lang.NullPointerException at com.mysql.jdbc.Statement.getWarnings(Statement.java:690) at net.travelclick.rvsim.Simulator.showWarnings(Simulator.java:210) at net.travelclick.rvsim.Simulator.closeConnection(Simulator.java:167) at net.travelclick.rvsim.Simulator.run(Simulator.java:155) at net.travelclick.rvsim.Simulator.main(Simulator.java:862) How to repeat: Here is the code fragment that produced the error above, along with a partial extract of the CSV file which caused the exception on the gamma JDBC driver, but works fine on the stable driver. /* CSV source data loading - we're going to invoke the MySQL * server-side bulk-data-loader using the following syntax: */ private static String loadDataPattern = "load data infile \"{0}\" ignore into table {1}\n" + "fields terminated by \"{2}\" enclosed by \"\\\"\"\n" + "lines terminated by \"\\r\\n\" ignore 1 lines\n{3}"; /* Rather than use preparedStatement we'll use MessageFormat substitution. * As such, the following static constants are set up as an Object[] * to pass in an invocation to MessageFormat.format(String pattern, Object[] args); */ private static String[][] csvTables = { {"RV_Simulator_Subscriptions.csv", "csv_subscriptions", ",", "(SUBSCRIPTION_ID,CLIENT_ID,START_DATE_CHAR,ON_DEMAND,ON_DEMAND_SITES," + "MAX_REPORTS_PER_DAY,MAX_REPORTS_PER_WEEK,SCHEDULED,SCHEDULED_SITES," + "SCHEDULED_FREQUENCY,DELIVERY_DOW,LOSES,WEEKS_OF_DATA,OFFSET_FOR_FIRST_CHECKIN_DATE," + "CURRENCY_CODE)"} }; /** load the CSV tables using MySQL server-side 'load data infile...' * statements (a lightning-fast bulk-data loader). * <p/> * We're using messageFormat and a pattern here rather than a preparedStatement * as it fits in more cleanly with our logging. MySQL does not support server-side * parameter substitution, so there's no performance gain from using preparedStatement. */ private void loadCSVtables() throws SQLException { logger.info("loading CSV tables..."); for (int i = 0; i < csvTables.length; i++) { // build & substitute full path to data file csvTables[i][0] = tempPath(csvTables[i][0]); String dml = MessageFormat.format(loadDataPattern, csvTables[i]); statement.executeUpdate(dml); } } partial RV_Simulator_Subscriptions.csv file: "SUBSCRIPTION_ID","CLIENT_ID","SUB_START_DATE","ON_DEMAND","ON_DEMAND_SITES","MAX_REPORTS_PER_DAY","MAX_REPORTS_PER_WEEK","SCHEDULED","SCHEDULED_SITES","SCHEDULED_FREQUENCY","DELIVERY_DOW","LOSES","WEEKS_OF_DATA","OFFSET_FOR_FIRST_CHECKIN_DATE","CURRENCY_CODE" "109615","319450","6/24/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "117831","185586","12/10/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "118597","203343","12/13/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "118804","185913","4/22/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "118808","185920","4/15/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "118809","193013","4/15/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "119333","196910","12/5/2003","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "120011","196539","11/17/2003","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "120438","204663","3/22/2004","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "120011","196539","11/17/2003","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "120438","204663","3/22/2004","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "122311","263073","1/28/2004","1","Travelocity,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "122314","273738","1/28/2004","1","Travelocity,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "123065","323057","12/9/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","CAD" "123086","263990","12/4/2003","1","Travelocity,Galileo,Expedia,Orbitz,LastMinute,HRS","50","100","0","","","","","","","EUR" "128524","189345","12/5/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "128667","189734","12/22/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "128748","319451","6/24/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "129409","196117","1/15/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "130987","291122","3/1/2004","1","Travelocity,Galileo,Expedia,Brand,HRS,OctopusTravel,BookingsDotOrg,HotelsDotNl","50","100","0","","","","","","","EUR" "129409","196117","1/15/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "130987","291122","3/1/2004","1","Travelocity,Galileo,Expedia,Brand,HRS,OctopusTravel,BookingsDotOrg,HotelsDotNl","50","100","0","","","","","","","EUR" "132029","323015","1/27/2004","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "132151","319453","6/24/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "132655","194021","12/19/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "133014","193771","11/18/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "133922","319449","6/23/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "134166","188532","11/14/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "134609","186294","3/1/2004","1","Travelocity,Galileo,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "134754","194525","12/10/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "135230","191198","12/17/2003","1","Galileo,HRN,Expedia,LastMinute,HRS","50","100","0","","","","","","","EUR" "134754","194525","12/10/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "135230","191198","12/17/2003","1","Galileo,HRN,Expedia,LastMinute,HRS","50","100","0","","","","","","","EUR" "135308","182859","11/14/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand,HRS","50","100","0","","","","","","","USD" "135445","191470","1/12/2004","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "136296","193412","12/5/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "136458","192155","11/14/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "136805","193747","12/3/2003","1","Galileo,Expedia,Orbitz,LastMinute,HRS","50","100","0","","","","","","","EUR" "137309","185059","11/14/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz,Brand","50","100","0","","","","","","","USD" "137695","190531","11/17/2003","1","Travelocity,Galileo,HRN,Expedia,Orbitz","50","100","0","","","","","","","USD" "137845","199050","12/3/2003","1","Galileo,HRN,Expedia,LastMinute,HRS","50","100","0","","","","","","","EUR" "137930","197389","4/1/2004","1","Travelocity,Galileo,Expedia,HRS,Lodging","50","100","0","","","","","","","EUR"