I exported the database table structure and data from the company server Mysql (version: 8.0.16) and restored it to my local computer Mysql (version: 8.0.29). Just after starting the project, I was prompted with a SqlSugar database connection error. The prompt error is as follows:

Execute Db.Ado.CheckConnection() reports an error
Connection open error . The given key ‘0’ was not present in the dictionary.

note: the connection string: database=stocks;server=127.0.0.1;port=3306;uid=root;pwd=123;

The following is the error report problem of individual table query.

DB.Queryable().Where(p=>true).ToList() error. ToList() error, after analysis, the main problem is the current table field character set and sorting rules are not uniform. The error is reported as follows.
MySqlException: “Fatal error encountered attempting to read the resultset.”
Internal exception MySqlException: Expected end of data packet

2. Solution

1. Solution to connection error

Add charset=utf8mb4, and the connection will no longer report errors note: if you want utf8mb4, I use utf8 locally and still report an error

database=stocks;server=127.0.0.1;port=3306;uid=root;pwd=123;charset=utf8mb4;

2. Modify the inconsistency of database character set

Execute the following SQL script as required.

(1) Change the encoding (character set) of a table and the encoding (character set) of all fields in the table:

ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
·TABLE_NAME replace to your table name.

(2) Change the encoding (character set) of all tables and the encoding (character set) of all fields in the table:

SELECT
	CONCAT(
		'ALTER TABLE ',
		TABLE_NAME,
		' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
	)
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'DATABASE_NAME';
·DATABASE_NAME replace to your databese name.

3. Other contents

Query all table names of a database:

SHOW FULL COLUMNS FROM TABLE_NAME;
·TABLE_NAME replace to your table name.