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

BulkCopy inserts binary data incorrectly #816

Closed
MaceWindu opened this issue May 10, 2020 · 4 comments
Closed

BulkCopy inserts binary data incorrectly #816

MaceWindu opened this issue May 10, 2020 · 4 comments
Assignees

Comments

@MaceWindu
Copy link
Contributor

MaceWindu commented May 10, 2020

During implementation of BulkCopy support in linq2db we found out that binary types inserted incorrectly using BulkCopy API (other types seems to work).

Looks like inserted data inserted like binary -> hex string -> binary:

insert: byte[3] { 1, 2, 3 }
in db: byte[6] { 48, 49, 48, 50, 48, 51 } // "010203"
@bgrainger
Copy link
Member

bgrainger commented May 10, 2020

This is covered in the documentation: https://mysqlconnector.net/api/mysql-bulk-copy/

Columns containing binary data must be mapped using an expression that uses the UNHEX function.

The underlying reason is that MySqlBulkCopy just uses the LOAD DATA command, which accepts string data, and I don't think there's a way to safely escape arbitrary binary data (e.g., that contains newlines.

@MaceWindu
Copy link
Contributor Author

MaceWindu commented May 10, 2020

Thanks, will see what we can do on our side

@bgrainger
Copy link
Member

bgrainger commented May 10, 2020

This is definitely a usability problem for the API, though. At the (small) cost of reading the destination table schema, MySqlConnector could detect destination columns that are binary, and automatically set up the UNHEX expression. This would also be helpful for BIT columns, that need a custom expression, too.

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

bgrainger commented May 30, 2020

This is fixed 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