Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-619

Multiple batch update fails after LOAD DATA LOCAL INFILE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.4, 2.2.5
    • Fix Version/s: 2.7.3
    • Component/s: batch
    • Labels:
      None
    • Environment:
      Windows 10 (also reproduced in RHEL 7.1), Java 8, Mysql 5.7.

      Description

      java.sql.Statement.executeBatch() fails when multiple addBatch(..) commands are present following a 'LOAD DATA LOCAL FILE' command.
      To reproduce, use below code. Database queries for table creations and files are provided within the code.

      package com.kausik.temp;
       
      import java.sql.Connection;
      import java.sql.SQLException;
      import java.sql.Statement;
       
      import org.mariadb.jdbc.MySQLDataSource;
      import org.mariadb.jdbc.MariaDbDataSource;
       
      /**
       * Create Table 'infiletest' within the schema 'powertest'
       * 
       *  	CREATE TABLE infiletest (
        		id int(11) NOT NULL AUTO_INCREMENT,
        		name varchar(30) DEFAULT NULL,
        		PRIMARY KEY (`id`)
      		);
      		
      		
       * 
       *
       */
       
      public class MariaDBConnectorBatchUpdateFailTest {
       
      	public static void excuteBatch(){
      		MariaDbDataSource mysqlDS = null;
      		//MySQLDataSource mysqlDS = null; //Reproduced with this data source as well
      		Connection con = null;
      		String DB_URL="jdbc:mysql://localhost:3306/powertest";
      		String DB_USERNAME="root";
      		String batch_update="LOAD DATA LOCAL INFILE 'C:/tmp/insert_infile.dat' INTO TABLE powertest.infiletest CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'";
      	
      		/**
      		 * The content of C:/tmp/insert_infile.dat  is a single line as below
      		 *  "","test_name"
      		 */
      		
      		try {
      			//mysqlDS = new MySQLDataSource(); //Exception is thrown even with this data-source
      			mysqlDS = new MariaDbDataSource();
      			
      			mysqlDS.setUrl(DB_URL);
      			mysqlDS.setUser(DB_USERNAME);
      			
      			//mysqlDS.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));  //The mysql test set up has no password
      			con =mysqlDS.getConnection();
      			con.setAutoCommit(false);
      			
      			Statement smt=con.createStatement();
      			smt.addBatch(batch_update); 
      			smt.addBatch("SET UNIQUE_CHECKS=1");
      			int[] updateStatus=smt.executeBatch();
      			System.out.println("Status[0]="+updateStatus[0]);
      			con.commit();
      		} catch (Exception e) {
      			e.printStackTrace();
      		}finally{
      			try {
      				con.close();
      			} catch (SQLException e) {
      				e.printStackTrace();
      			}
      		}
      	}
      	
      	
      	public static void main(String[] args) {
      		excuteBatch();
      	}
       
      }
      

      The stack trace:

      java.sql.BatchUpdateException: (conn=55) Software caused connection abort: socket write error
      	at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:275)
      	at org.mariadb.jdbc.MariaDbStatement.executeBatch(MariaDbStatement.java:1252)
      	at com.kausik.temp.MariaDBConnectorBatchUpdateFailTest.excuteBatch(MariaDBConnectorBatchUpdateFailTest.java:53)
      	at com.kausik.temp.MariaDBConnectorBatchUpdateFailTest.main(MariaDBConnectorBatchUpdateFailTest.java:69)
      Caused by: java.sql.SQLNonTransientConnectionException: (conn=55) Software caused connection abort: socket write error
      	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:175)
      	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
      	at org.mariadb.jdbc.MariaDbStatement.executeBatchExceptionEpilogue(MariaDbStatement.java:272)
      	... 3 more
      Caused by: java.sql.SQLException: Software caused connection abort: socket write error
      Query is: LOAD DATA LOCAL INFILE 'C:/tmp/insert_infile.dat' INTO TABLE powertest.infiletest CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
      '
      	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
      	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol$2.handleResultException(AbstractQueryProtocol.java:700)
      	at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:140)
      	at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:67)
      	at java.util.concurrent.FutureTask.run(Unknown Source)
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      	at java.lang.Thread.run(Unknown Source)
      Caused by: java.net.SocketException: Software caused connection abort: socket write error
      	at java.net.SocketOutputStream.socketWrite0(Native Method)
      	at java.net.SocketOutputStream.socketWrite(Unknown Source)
      	at java.net.SocketOutputStream.write(Unknown Source)
      	at org.mariadb.jdbc.internal.io.output.StandardPacketOutputStream.flushBuffer(StandardPacketOutputStream.java:110)
      	at org.mariadb.jdbc.internal.io.output.AbstractPacketOutputStream.flush(AbstractPacketOutputStream.java:172)
      	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readLocalInfilePacket(AbstractQueryProtocol.java:1573)
      	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1371)
      	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1328)
      	at org.mariadb.jdbc.internal.protocol.AsyncMultiRead.call(AsyncMultiRead.java:137)
      	... 5 more
      

      ----------------------------------------------------------------------------------------------------
      Note: When the same code is executed in eclipse debug mode in steps, there were no exception is thrown and the batch-updates were successful.

        Attachments

          Activity

            People

            Assignee:
            diego dupin Diego Dupin
            Reporter:
            kausik Kausik
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration