Cannot connect to MySQL database
Hope I am not asking this on the wrong forum, but I have no idea where to look elsewhere (and this forum has been my recue many, many times).
I am trying to develop a new VB.net application with Visual Studio 2019. I cannot add a connection to my application. When I connect (local host and root) it creates a connection, but if I want to open the table folder I get an error "character set 'utf8mb3' is not supported by .Net. However my Mysql database does not use that (it is utf8mb4). What am I missing. I recently updated to a new MYQLconnector (8.0.33.) I am using MYSQL 8.
Any ideas would be much appreciated
Re: Cannot connect to MySQL database
What are the Character-Set Values in the server-config (my.ini)?
Re: Cannot connect to MySQL database
Thank you very much for replying.
I include part of the my.ini file
Code:
[mysqld]
local_infile=ON
secure-file-priv = ""
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
Re: Cannot connect to MySQL database
Do you specify a Characterset in the connection itself (what was ODBC Connection-String)?
and have you checked each table/column, what character-set is applied there? You do know, that you can specify different charactersets to tables during creation, irrespective of what is the default for the Server (my.ini)?
And don't forget the Collation, too. Those can even be set per Column
If that doesn't help, i'm out of ideas
Re: Cannot connect to MySQL database
Quote:
Originally Posted by
Zvoni
And don't forget the Collation, too. Those can even be set per Column
That has gotten the best of me before.
Re: Cannot connect to MySQL database
All the caractersets and collation for the database, tables and columns are the same.
Re: Cannot connect to MySQL database
Yes, looks like Microsoft is putting pressure on us to use SQL in stead of MySql.
Even hours spent on chatgbt could not solve this.
Re: Cannot connect to MySQL database
Well, last idea: does it work with ODBC?
Re: Cannot connect to MySQL database
Another idea is to use an ORM to handle the database connection and commands for you.
For example, PetaPoco (GitHub) supports MySQL (documentation).
If you go to Project > {project name} Properties, open the Application tab, click on View Application Events, you can do the following:
- Create a new property of type Database named ApplicationDatabase
- Generate the MyApplication.Startup event
- Inside of the event handler, instantiate PetaPoco using the MySqlConnector provider
- Create a POCO class that represents one of your tables (e.g. class named UserModel with UserId, Username, etc. properties)
- For testing purposes, run the following code to see if it works:
Code:
Dim records = My.Application.ApplicationDatabase.Query(Of UserModel)("SELECT * FROM `user`;")
It is definitely overkill but I'm curious to see if that works.
Re: Cannot connect to MySQL database
Hi Zvoni. Thank you for making the effort to reply.
I have tried to connect via Microsoft ODBC Data Source (ODBC).
I have set a SYSTEM DNS on my ODBS Data Sources (64bit) and created a data source name (using Microsoft ODBC 8.0 Unicode), tested it and it connects. I used server : localhost, user :root and my password. The databasis show up in the dropdown and it tests OK.
BUT when i try to add a connection via server explorer in visual studio choosing Microsoft ODBC Data Source (ODBC), after i click OK no data source names are listed. Am I doing it right? I cannot find any tutorials online.
Re: Cannot connect to MySQL database
sometimes clearing the appdata can have a positive result, especially after updating a connector such as this, it seems to me that a cached setting can often lead to confusing results. It is worth a shot
Run: %LocalAppData%
this will bring you to your local app data, find your apps and delete the folders.
failing everything else i would roll back the connector.
Re: Cannot connect to MySQL database
Ok so I have given up. To anyone reading this - if you want to create database applications with Visual studio : DO NOT USE MYQL.
Even if you use Visual Studio 2019.
Also it seems vb.net is with its one foot in the grave.
Re: Cannot connect to MySQL database
You're not even going to bother with my suggestion?
Re: Cannot connect to MySQL database
Quote:
Also it seems vb.net is with its one foot in the grave.
nothing could be further from the truth.
MySQL is a fine choice as well, no need to discourage people from using it.
It sounds to me like youve botched an installation somewhere. What ever the problem is you are having it is due to the choices you have made
Re: Cannot connect to MySQL database
Hi dday9. I have managed to use ODBC. Seems like Visual studio used 32 bit. So when i made the sure the platform in my ODBC Datasources is a 32 bit I could add and ODBC connections after many,many hours of research. Thank you for putting me on the ODBC route.
Re: Cannot connect to MySQL database
Hi vbdotnut. Apologies that was frustration getting the hold of me. I agree that I must have " botched' up somewhere. Finding that issue connection to a Mysql database using MySQL Database (MySQL Data Provider) got the best of me, even worst is that I used it in the past and trying to reproduce it is what got me. Regards.