Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bulk Import - throw exception in case column mapping is wrong? #814

Closed
chester89 opened this issue May 8, 2020 · 7 comments
Closed

Bulk Import - throw exception in case column mapping is wrong? #814

chester89 opened this issue May 8, 2020 · 7 comments
Assignees

Comments

@chester89
Copy link

@chester89 chester89 commented May 8, 2020

Hello.
I just set up bulk copy in one of our projects.
Did it all according to documentation, code runs without errors - but nothing gets written to DB.
I triple checked - it turned out I had column mappings SourceOrdinal wrong (thought it started at 1).
Is it possible to throw exception in this case?
Read initial issue - and realised it reports the number of rows as if it's actually written them (so if you ask to insert 10k, it'll say 10k, even if it actually inserted none).

I use server 5.7.29 with 0.66.0 library version

@bgrainger
Copy link
Member

@bgrainger bgrainger commented May 8, 2020

According to the MySQL documentation:

With LOAD DATA LOCAL, data-interpretation and duplicate-key errors become warnings and the operation continues because the server has no way to stop transmission of the file in the middle of the operation.

There may be no way for MySqlConnector to detect that problem (which unfortunately could be a significant data loss issue); more investigation is probably needed.

it turned out I had column mappings SourceOrdinal wrong (thought it started at 1).

Did you end up specifying a SourceOrdinal that was greater than (or equal to) the number of columns in the source? That should be possible to detect and throw an exception for.

@chester89
Copy link
Author

@chester89 chester89 commented May 8, 2020

Did you end up specifying a SourceOrdinal that was greater than (or equal to) the number of columns in the source? That should be possible to detect and throw an exception for.

I'll check

There may be no way for MySqlConnector to detect that problem (which unfortunately could be a significant data loss issue); more investigation is probably needed.

Rather alarming, but I see

@chester89
Copy link
Author

@chester89 chester89 commented May 12, 2020

@bgrainger at some amount of rows (less than 100k) the library throws exception saying connection is closed (I open it before starting import and it's is the only operation I do)

EXCEPTION: MySql.Data.MySqlClient.MySqlException (0x80004005): Failed to read the result set.
 ---> System.InvalidOperationException: Connection must be Open; current state is Closed
   at MySql.Data.MySqlClient.MySqlConnection.get_Session() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlConnection.cs:line 488
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 118
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 116
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 62
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlCommand.cs:line 220
   at MySql.Data.MySqlClient.MySqlBulkLoader.LoadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlBulkLoader.cs:line 116
   at MySql.Data.MySqlClient.MySqlBulkCopy.WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlBulkCopy.cs:line 206
   at MySql.Data.MySqlClient.MySqlBulkCopy.WriteToServerAsync(IDataReader dataReader, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlBulkCopy.cs:line 119

@bgrainger
Copy link
Member

@bgrainger bgrainger commented May 12, 2020

That's a duplicate of #780; I haven't been able to find a repro yet.

@chester89
Copy link
Author

@chester89 chester89 commented May 12, 2020

That's a duplicate of #780; I haven't been able to find a repro yet.

Thanks, I subscribed to that

@bgrainger
Copy link
Member

@bgrainger bgrainger commented May 14, 2020

There may be no way for MySqlConnector to detect that problem (which unfortunately could be a significant data loss issue); more investigation is probably needed.

Rather alarming, but I see

The final "OK packet" sent by the server from the LOAD DATA command will have a count of warnings; however:

  1. This will be hard to surface in the current code.
  2. I don't know that having warnings is always exceptional

However, if MySqlBulkLoader.Load(Async) returns the number of rows that were inserted, and MySqlBulkCopy knows the number of rows that were sent, it seems like we could throw an exception if these aren't equal.

@bgrainger bgrainger self-assigned this May 15, 2020
@bgrainger
Copy link
Member

@bgrainger bgrainger commented May 30, 2020

This is improved in 0.67.0.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

No branches or pull requests

2 participants