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

collation connection set to utf8mb4_bin #585

Closed
FireInWinter opened this issue Nov 20, 2018 · 6 comments
Closed

collation connection set to utf8mb4_bin #585

FireInWinter opened this issue Nov 20, 2018 · 6 comments
Assignees
Labels

Comments

@FireInWinter
Copy link

FireInWinter commented Nov 20, 2018

Is there a reason that the default connection collation is utf8mb4_bin? I've been using the Oracle .MySql Connector/NET and it seems to default to utf8mb4_general_ci. I'm trying to switch over to MySqlConnector, but this makes it more difficult. This is causing issues where I have items that compare data I pass in to a string. This is run against MySQL 5.6.
An easy way to see this to do a simple query like:

select 'All'='all';
returns 0
vs
select 'All'='All';
returns 1

With the Oracle provided query it returns 1 in both cases.

If I run the following on the connections I am making, I get different results
show session variables like '%collation%'"

Using 0.47.1.0 mysqlconnector
Variable_name        Value
-------------        -----
collation_connection utf8mb4_bin
collation_database   utf8mb4_general_ci
collation_server     utf8mb4_general_ci
Using MySQL.Data, Version=6.10.7.0
Variable_name        Value             
-------------        -----             
collation_connection utf8mb4_general_ci
collation_database   utf8mb4_general_ci
collation_server     utf8mb4_general_ci

I might be able to work around the issue, by submitting "SET NAMES DEFAULT; " before every query, but it doesn't seem like the best way to handle it.

@bgrainger
Copy link
Member

bgrainger commented Nov 20, 2018

The default is utf8mb4_bin because I personally prefer to have string comparisons consistent between .NET and the database; normally this wouldn't be a problem because a column's collation takes precedence over the connection collation (e.g., when running SELECT * FROM table WHERE col = @param).

Are you comparing two literal strings in your SQL? I'm trying to understand the exact use case here.

A workaround is to specify the collation you want to use in your SQL:

select 'All'='all' collate utf8mb4_general_ci; -- returns 1 with any connector

@FireInWinter
Copy link
Author

FireInWinter commented Nov 21, 2018

I have some more complex queries that use parameters and in some cases the query does end up comparing the passed in parameter to a literal string.
For example:
select * from MyTable where IsActive=1 and (Type = @Type or @Type = 'All')
Of course I was able to fix this specific case, but I'm worried about other bugs in other scripts. The other issue is that I'm making a general tool (Invoke-MySQL for PowerShell Core). Which means I can't verify what other will be doing and I'd like it to match what it was doing in the past ( on the non Core version of PowerShell) as closely as possible to reduce confusion and bugs for people at my company who are using it. I'll probably be able to release it as open source at some point, but I have to jump through some hoops at my work first.

Here's an example of using what I'm working on. Hopefully it will be clear why I'd prefer a different default.
Invoke-MySql -Server MyServer -Database MyDatabase -Query "select * from MyTable where IsActive=1 and (Type = @Type or @Type = 'All')" -Parameters @{'@Type'='all'}
As a side note, PowerShell is by default case insensitive, so I prefer not to do anything to add more case sensitivity than necessary.

@bgrainger
Copy link
Member

bgrainger commented Nov 21, 2018

This seems reasonable change to make for compatibility; most SQL users are probably used to case-insensitive comparison as the default utf8mb4 collation is utf8mb4_general_ci or utf8mb4_0900_ai_ci .

Unfortunately, when connecting we have to specify a precise collation, not just use the server's default for utf8mb4, so we'd have to pick utf8mb4_general_ci for backwards compatibility (although utf8mb4_0900_ai_ci might be preferred for MySQL 8.0).

If this is a significant problem, a CharacterSet (or Collation?) connection string option might need to be added.

@bgrainger bgrainger added the bug label Nov 21, 2018
@bgrainger bgrainger self-assigned this Nov 21, 2018
@FireInWinter
Copy link
Author

FireInWinter commented Nov 21, 2018

Thank you for the quick action. I'm very impressed by this project and how quickly issues are being addressed.

@bgrainger
Copy link
Member

bgrainger commented Nov 22, 2018

@caleblloyd Will this cause any problems for Pomelo.EntityFrameworkCore.MySql? Are there integration tests that could be run with MySqlConnector 0.48.0-beta2 just to ensure it doesn't introduce any issues?

@bgrainger
Copy link
Member

bgrainger commented Dec 8, 2018

Fixed in 0.48.0.

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

No branches or pull requests

2 participants