Devart Forums

FransBouma

Fixed. Thanks!

FransBouma

Thanks :)

From the docs, as described earlier in the thread, it should be automatic tho so no connection string element is required? So if the account uses cached_sha2_password, that's chosen by the connection otherwise the default?

FransBouma

What I did in mysql workbench 8: Users and Privileges -> Add account -> Add account for '%' (as we're accessing it over the network!), give it a name and password and specify caching_sha2_password as authentication type. So e.g. add the user 'Frans', password abc, for %, choose caching_sha2_password and that's it.

Perhaps you have multiple connection specifications for a given test user and you connect over one which doesn't have that caching_sha2_password set? (e.g. ::1 instead of %)

Pinturiccio wrote: ↑Tue 22 Jan 2019 18:36 We still can't reproduce the issue.

FransBouma wrote:I create the connection through DbProviderFactories.GetFactory(), and that's the only way this code will work (as we don't have hard-coded references to ADO.NET providers).
1. What dotConnect for MySQL version is installed on your computer?

8.12.1307. There's no other dotConnect installed, as the installer uninstalls the old one.

The machine.config file of Framework64 for .NET 4 contains 1 line for dotconnect:

Code: Select all

      <add name="dotConnect for MySQL" invariant="Devart.Data.MySql"
        description="Devart dotConnect for MySQL" type="Devart.Data.MySql.MySqlProviderFactory, Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />

2. What versions of Devart.Data.MySql.dll are present in GAC?

Just 8.12.1307

Code: Select all

C:\Windows\assembly>dir /s deva*
 Volume in drive C has no label.
 Volume Serial Number is 1C2A-8A0E

 Directory of C:\Windows\assembly\GAC_MSIL

10-01-2019  10:00    <DIR>          Devart.Data
10-01-2019  10:00    <DIR>          Devart.Data.MySql
               0 File(s)              0 bytes

 Directory of C:\Windows\assembly\GAC_MSIL\Devart.Data\5.0.2099.0__09af7300eec23701

10-01-2019  10:00           287.912 Devart.Data.dll
               1 File(s)        287.912 bytes

 Directory of C:\Windows\assembly\GAC_MSIL\Devart.Data.MySql\8.12.1307.0__09af7300eec23701

10-01-2019  10:00           602.280 Devart.Data.MySql.dll
               1 File(s)        602.280 bytes

     Total Files Listed:
               2 File(s)        890.192 bytes
               2 Dir(s)  36.012.822.528 bytes free

C:\Windows\assembly>

3. The edition of dotConnect for MySQL.

The express one

4. Please create a new simple console application with the following code:

Code: Select all

DbProviderFactory fact = DbProviderFactories.GetFactory("Devart.Data.MySql");
DbConnection conn = fact.CreateConnection();

conn.ConnectionString = "Server=windows2008r2.sd.local; Port=3308;Database=mysql; User ID=xxxxxxx; Password=xxxxxxxxx; Default Auth Plugin=cached_sha2_password;";
Console.WriteLine(conn.GetType().Assembly.FullName);
conn.Open();
Console.WriteLine(conn.ServerVersion);
conn.Close();

What is the results of execution of this code?

I moved the line to obtain the type name above the connect, as it crashes:

Code: Select all

Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701

Unhandled Exception: System.NotSupportedException: Keyword not supported: 'default auth plugin'.
   at Devart.Data.MySql.MySqlConnectionStringBuilder.a(String A_0)
   at Devart.Data.MySql.MySqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at Devart.Data.MySql.MySqlConnectionStringBuilder..ctor(String connectionString)
   at Devart.Data.MySql.MySqlConnection.a(String A_0)
   at Devart.Data.MySql.MySqlConnection.set_ConnectionString(String value)
   at MySQLTester.Program.Main(String[] args) in C:\Users\frans\Documents\Visual Studio 2017\Projects\MySQLTester\MySQLTester\Program.cs:line 17
Press any key to continue . . .

I've looked at the Debug -> Modules window, and it lists 1 dll from DevArt: Devart.Data.MySql.dll, from the GAC, version 8.12.1307, timestamp 9-1-2019, 22:53.

When I remove the 'default auth plugin' name-value pair from the connectionstring (as according to your docs, that shouldn't be necessary!) I get (after a delay of 10 seconds or so)

Code: Select all

Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701

Unhandled Exception: Devart.Data.MySql.MySqlException: Can't connect to MySQL server on 'windows2008r2.sd.local' (10061): Authentication failed. ---> System.Inv
alidOperationException: Unknown authentication plugin 'caching_sha2_password'.
   at Devart.Data.MySql.l.a(c A_0)
   at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)
   --- End of inner exception stack trace ---
   at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)
   at Devart.Data.MySql.q.a(MySqlConnection A_0, String A_1, String A_2, String A_3, String A_4, Int32 A_5, Int32 A_6, MySqlProtocol A_7, Boolean A_8, Boolean A
_9)
   at Devart.Data.MySql.q..ctor(ao A_0, MySqlConnection A_1)
   at Devart.Data.MySql.am.a(DbConnectionOptions A_0, Object A_1, DbConnectionBase A_2)
   at Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, DbConnectionOptions A_1, DbConnectionBase A_2)
   at Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
   at Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
   at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
   at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
   at Devart.Common.DbConnectionBase.Open()
   at Devart.Data.MySql.MySqlConnection.j()
   at Devart.Data.MySql.MySqlConnection.Open()
   at MySQLTester.Program.Main(String[] args) in C:\Users\frans\Documents\Visual Studio 2017\Projects\MySQLTester\MySQLTester\Program.cs:line 18
Press any key to continue . . .

When I look at the Modules window when the exception above is thrown (wrapped the code with a try/catch) I see 2 DevArt dlls loaded this time, one the aforementioned Devart.Data.MySql.dll and now also Devart.Data.dll, from the GAC, v 5.00.2099.0, timestamp 9-1-2019 22:53, so looks like the correct one.

FransBouma wrote:(Using 8.12.1307, download an hour ago. .NET Full)
Where did you get this dotConnect for MySQL? Did you download and install the dcmysql812pro.exe file or did you just get assemblies from NuGet packages?

From the download page of mysql connect on the devart site, I don't use nuget for dbproviderfactory related material as it makes no sense: I need these providers be reachable from machine.config (as we load the ado.net providers on the fly using DbProviderFactories.GetFactory). I already have specified this a couple of times.

I don't know what else is going on, I can't debug it further from here as you use an obfuscated dll, so this is the info I can dig up for you. The repro is as simple as your own piece of code above, so that's not it.

FransBouma

I create the connection through DbProviderFactories.GetFactory(), and that's the only way this code will work (as we don't have hard-coded references to ADO.NET providers). I assume that's working OK in your tests? If so, could you check how it's possible that when I use the factory of the latest version, it can load assemblies from an earlier version? I ask this as the amount of steps you post is pretty big, and if those steps are the same amount of steps our users have to go through, we'll reconsider using MySQL connector in the future.

For the record, the issue I posted here was brought forward by a customer of ours, we tested using the default connection settings hence it worked. So even I get it working the customer also has to do these steps and other customers who run into this as well. I'm sorry but isn't it better to have mysql connector work with the assemblies it should work with, namely the ones coming with the same version?

If the connection, created by the DbProviderFactory instance returned from DbProviderFactories.GetFactory() is from the new assembly, how can it still be the case 'old' assemblies are used? I've never had any problems like this with any ADO.NET provider where version mismatches occurred when the factory was used. Even with ODP.NET. Also, really, I have to cleanse the registry for all 'devart' data? Isn't it more appropriate for you to simply load the right assemblies belonging to the right version? If I get a reference to the DbProviderFactory of the new version, it creates instances of objects from the new version's assembly, not from the old version?

Anyway, I'll uninstall everything and re-install the latest and see what happens.

FransBouma

Hi

Here's some answers that might help finding what's going on. We use DbProviderFactories.GetFactory() to obtain the factory instance for the ADO.NET provider and create objects using that. It works fine if I switch the user's authentication type on the MySQL server to 'standard'. having it at 'cachingZ_sha2_password' fails with the exception below. The connection string given below does contain the right username/password (replaced with xxx) and if I add the Default Auth Plugin it gives the other error I mentioned so I removed that for this test.

1. I use Rider, this is the code to open the connection,

Code: Select all

DbConnection toReturn = this.GetDbProviderFactory().CreateConnection();
toReturn.ConnectionString = GetConnectionStringToUse();

When I then evaluate 'toReturn' when the execution point is on the second line:
toReturn.GetType().Assembly.FullName
gives:
Devart.Data.MySql, Version=8.12.1307.0, Culture=neutral, PublicKeyToken=09af7300eec23701

So I can only conclude, it uses the latest version.

2. Server=windows2008r2.sd.local;Port=3308;Database=mysql;User ID=xxxxxxx;Password=xxxxxxxxx

3. Exception message:
-------------------------------
Exception type: MySqlException
Can't connect to MySQL server on 'windows2008r2.sd.local' (10061): Authentication failed.

Inner exception message:
-------------------------------
Exception type: InvalidOperationException
Unknown authentication plugin 'caching_sha2_password'.

LLBLGen Pro version 5.5. Build 5.5.1
-----[Core exception]--------------------
at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)
at Devart.Data.MySql.q.a(MySqlConnection A_0, String A_1, String A_2, String A_3, String A_4, Int32 A_5, Int32 A_6, MySqlProtocol A_7, Boolean A_8, Boolean A_9)
at Devart.Data.MySql.q..ctor(ao A_0, MySqlConnection A_1)
at Devart.Data.MySql.am.a(DbConnectionOptions A_0, Object A_1, DbConnectionBase A_2)
at Devart.Common.DbConnectionFactory.a(DbConnectionPool A_0, DbConnectionOptions A_1, DbConnectionBase A_2)
at Devart.Common.DbConnectionPoolGroup.a(DbConnectionPool A_0, DbConnectionBase A_1)
at Devart.Common.DbConnectionPool.a(DbConnectionBase A_0)
at Devart.Common.DbConnectionPool.GetObject(DbConnectionBase owningConnection)
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.MySql.MySqlConnection.j()
at Devart.Data.MySql.MySqlConnection.Open()
at SD.LLBLGen.Pro.Gui.Controls.WizardPages.MetaDataRetrievalWizard_Step_ConnectionData.TestConnectionData(Boolean showSuccess) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.5\Designer\Gui\Controls\WizardPages\MetaDataRetrievalWizard_Step_ConnectionData.cs:line 245
-----[InnerException]--------------------
at Devart.Data.MySql.l.a(c A_0)
at Devart.Data.MySql.l.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, Int32 A_7)

4.# The default authentication plugin to be used when connecting to the server
default_authentication_plugin=mysql_native_password

FransBouma

Hi. (Using 8.12.1307, download an hour ago. .NET Full)

I'm the lead dev of LLBLGen Pro and we use devart dotconnect for mysql as the ado.net provider for MySQL. A customer of ours needs to connect to a MySQL 8.0 database using an account which has 'cached_sha2_password'. We don't specify an authentication plugin in the connection string (just server, user id, port nr). This results in:

Exception type: InvalidOperationException
Unknown authentication plugin 'caching_sha2_password'.

This is odd, considering https://www.devart.com/dotconnect/mysql ... nType.html

This option may be omitted because the client may receive the name of the authentication plugin when performing handshake, but you may use this option to shorten client/server negotiation and avoid a round trip in the protocol.

So I tried to reproduce this and I can with a default user having 'cached_sha2_password' as authentication type set in MySQL 8. I see there's a connection string argument 'Default Auth Plugin' (https://www.devart.com/dotconnect/mysql ... nType.html), so we tried to specify ";Default Auth Plugin=cached_sha2_password" in the connectionstring, but this gives:

Exception type: NotSupportedException
Keyword not supported: 'default auth plugin'.

so I'm at a loss how to use this at all. Is this even supported? Using 'Standard' as Authentication Type on the MySQL 8 user works fine.

FransBouma

Thanks :)

FransBouma

Thanks, but I can also reproduce it with a DESCRIBE query (see repro above), so the bug is elsewhere too, not only in the Procedures schema retrieval.

FransBouma

Any news on this? It's been a while and we can't proceed with testing.

FransBouma

Hi,

(.NET full, 4.7.1, windows 8.1 x64, mysql: latest, dotconnect for mysql: latest, express edition. MySQL db uses defaults when installed and v5.x connection scheme. Examples used are the v8.x sakila db)

I'm the lead dev of LLBLGen Pro and we use DevArt's dotConnect for MySQL to support MySQL. Back in 2011 you had a bug (see ticket nr. 34339, or a thread on our support forums: https://www.llblgen.com/TinyForum/Messa ... adID=20412) where the 'Comment' column in the 'Procedures' schema view returned by DbConnection.GetSchema("Procedures"..) has an empty array (byte[0]) instead of NULL/DBNull.Value when the value is null.

This bug is now reappearing with the latest dotConnect for MySQL (v8) the express version, using latest MySQL build, on the Sakila database.

As you correct it before in 2011, I assume this is a bug in your ADO.NET connector. We can of course introduce a workaround but I think it's best if this was fixed where it actually goes wrong, as the column is supposed to contain a string (or null), not a byte array.

TIA

(edit) The problem also occurs when fetching a simple resultset using a DbDataAdapter in a datatable, e.g. DESCRIBE `actor` gives a byte[] array for 'Type' while it should be a string. However, I've enabled 'Unicode=true;' in the connection string (or charset=utf8mb4;), but that doesn't have any effect it seems... Assembly used is:
Devart.Data.MySql, Version=8.12.1229.0, Culture=neutral, PublicKeyToken=09af7300eec23701

Using the connector on a mysql 5.6 DB (also sakila), works fine. So it's related to mysql 8 and the latest connector. Same code is used on both mysql dbs so that can't be it.

Small repro:

Code: Select all

using System;
using System.Data;
using System.Data.Common;
using Devart.Data.MySql;

namespace mysqltester
{
	internal class Program
	{
		public static void Main(string[] args)
		{
			using(var con = new MySqlConnection("Server=YOURSERVERHERE;Port=3308;Database=Sakila;User ID=root;Password=YOURROOTPWHERE;Unicode=true;"))
			{
				con.Open();
				Console.WriteLine("Assembly: {0}", con.GetType().AssemblyQualifiedName);
				var results = new DataTable();
				var adapter = new MySqlDataAdapter("DESCRIBE `actor`", con);
				adapter.Fill(results);
				for(int i=0;i<results.Rows.Count;i++)
				{
					var r = results.Rows[i];
					Console.WriteLine("Field: {0}. Type: {1}", r[0], r[1]);
				}
			}
		}
	}
}

gives: (The 'System.Byte[]' is the ToString() on the value (so it returns its type) as the value has to be a string, but that's not the case).
Assembly: Devart.Data.MySql.MySqlConnection, Devart.Data.MySql, Version=8.12.122
9.0, Culture=neutral, PublicKeyToken=09af7300eec23701
Field: actor_id. Type: System.Byte[]
Field: first_name. Type: System.Byte[]
Field: last_name. Type: System.Byte[]
Field: last_update. Type: System.Byte[]

Expected (running DESCRIBE `actor` in Mysql workbench 8)
actor_id smallint(5) unsigned
first_name varchar(45)
last_name varchar(45)
last_update timestamp

FransBouma

The problem is that our code is compiled against CoreLab's v4.x assembly, which has a different strong name ('CoreLab' instead of 'DevArt'). So redirecting an assembly won't work, the names don't match.

Do you have v4.x still available, or assemblies which have the original names? The problem is that the name change made our software incompatible with your software and new users can't buy the v4.x provider anymore as it seems, and we are now faced with yet another upgrade problem, which isn't the first with your software.

FransBouma

nb wrote:I'm using LLBLGenPro 2.5 and mySQLdirect 4.0.13 under VS2005. After building the project, version conflict with CoreLab.MySql are found, that can only be resumed by the following entry in app.config.

I'm not sure whether there is any negative impact with this. Anyway this behavior is new and did not exist in previous LLBLGenPro 2.0 and mySQLdirect 3.55.18.

Any idea?

You use the normal MySql driver ? (which is build against v4.x of core lab's provider) ?

@Core lab: didn't you include a policy file with the installer so all v4 references are redirected to the installed version? We build the mysql driver against v4.0.9.0

Also, with the new release of 4.20, will all reference hell and demo protection misery start all over again or is this covered and is transition smooth?

Frans Bouma, LLBLGen Pro lead developer

FransBouma

Core Lab wrote:
FransBouma wrote:I checked, but no email yet.
Don't you use an automated system?
We have already sent you upgrade information. Please check and confirm receiving.

I received it an hour ago. Thanks

FransBouma

Alexey wrote:By the way, Frans, it is possible that we have already sent you upgrade information. Make sure it was not blocked by your anti-spam filter or so.

I checked, but no email yet.

Don't you use an automated system?

FransBouma

Alexey wrote:ryans, this problem is going to be solved by building a special edition of LLBLGenPro for MySQLDirect .NET 4.0.

_driver_

.

We'll resolve this shortly, as soon as Core Lab sents us the license so we can build our driver against v4.

Devart Forums

Search found 29 matches

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Re: cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

cached_sha2_password support on MySQL 8 is broken in latest connector (8.12.1307)

Re: Old bug from 2011 re-appears in latest dotConnect for MySQL

Re: Old bug from 2011 re-appears in latest dotConnect for MySQL

Re: Old bug from 2011 re-appears in latest dotConnect for MySQL

Old bug from 2011 re-appears in latest dotConnect for MySQL

Re: Version conflicts with CoreLab.MySql: LLBLGenPro 2.5 and mySQLdirect 4.0.13