title: MySQL Load Data error
body: <p>Part of a Car Park database system i am developing involves loading data from number plate reading cameras into the database tables. Using the Bulk Loader object in the .net connector (using to experiment with single and multi-line files), I have managed to do this, however there is a regular, but intermittent error that pops up, and I am hoping somebody here can help me work out what is wrong and provide a solution</p>
  
  <p>I am using a pair of filesystemwatcher objects to monitor locations where the cameras (entrance and exit) output their CSV data. on the "file created" event, another subroutine is triggered that then loads the contents of the file into the database. Code for this subroutine listed below:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Public Sub sqlloaddata_in(ByVal sqlfilepath As String)
  
          loaddatainsqlconn = New MySqlConnection
          loaddatainsqlconn.ConnectionString = "Server=localhost;user id=root;password=W1nd0ws;database=hystest"
  
          Try
              'instanciate a mysqlbulkloader, feed in the parameters
              Dim sqlbulkin As New MySqlBulkLoader(loaddatainsqlconn)
              Dim insertedin As Long
              sqlbulkin.TableName = "hystest.tblin_dupebuffer"
              sqlbulkin.FieldTerminator = ","
              sqlbulkin.LineTerminator = "\n"
              sqlbulkin.FileName = sqlfilepath
              'sqlbulkin.ConflictOption = MySqlBulkLoaderConflictOption.Ignore
              sqlbulkin.NumberOfLinesToSkip = 1
              'load the data into the database. return the number of rows inserted into the variable
              insertedin = sqlbulkin.Load
              Debug.Print(insertedin &amp; " rows inserted.")
  
              ' refreshdatagrid_in()
              'close and get rid of the connection
              loaddatainsqlconn.Close()
              loaddatainsqlconn.Dispose()
              'transferdata_in()
          Catch ex As MySqlException
              'if bad stuff happens, do this
              'MessageBox.Show("Database Error: " &amp; ex.Message &amp; vbCrLf &amp; ex.InnerException.Message)
              'Debug.Print(ex.Message &amp; ex.InnerException.Message &amp; ex.InnerException.InnerException.Message)
              Debug.Print(ex.ToString)
              'Debug.Print("Retrying insert")
              'Try
              '    sqlloaddata_in(sqlfilepath)
              'Catch ex2 As Exception
  
              'End Try
          End Try
          'dedupe_in()
      End Sub
  </code></pre>
  
  <p>this subroutine will run every time a new file is created (camera outputs a file every 10 seconds) however when running, it produces the following error (taken from the debug window):</p>
  
  <pre><code>1 rows inserted.
  A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
  MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---&gt; MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset. ---&gt; MySql.Data.MySqlClient.MySqlException (0x80004005): Error during LOAD DATA LOCAL INFILE ---&gt; System.IO.IOException: The process cannot access the file 'C:\HYS Database\Raw\IN\INLIST_HYS,HN03KTG,1970-01-01,03-36-01-582.CSV' because it is being used by another process.
     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
     at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath)
     at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
     at MySql.Data.MySqlClient.NativeDriver.SendFileToServer(String filename)
     at MySql.Data.MySqlClient.NativeDriver.SendFileToServer(String filename)
     at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp; affectedRow, Int64&amp; insertedId)
     at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp; affectedRows, Int64&amp; insertedId)
     at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
     at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
     at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
     at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
     at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
     at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
     at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
     at MySql.Data.MySqlClient.MySqlBulkLoader.Load()
     at SmartPark_Data_Loader.mysql.sqlloaddata_in(String sqlfilepath) in C:\Users\&lt;name&gt;\Dropbox\Visual Studio 2010\Projects\SmartPark Data Loader\SmartPark Data Loader\mysql.vb:line 201
  1 rows inserted.
  1 rows inserted.
  1 rows inserted.
  1 rows inserted.
  1 rows inserted.
  1 rows inserted.
  </code></pre>
  
  <p>in the example above, one file was successfully inserted, then on the second, the error popped up. this takes about a minute to appear, and in that time, several other files have been created, creating a backlog of events to be handled, which then get inserted properly.</p>
  
  <p>This happens constantly, and i'm not really sure why. The ultimate layout of the debug window alternates between an error and then the resulting backlog of file events.</p>
  
  <p>what can i do about this?</p>