Pitfalls encountered when installing MySQL 5.7.17 compressed version under Windows

First download the latest MySQL 5.7.17 Community compressed version for Windows 64-bit:

Official download address: http://dev.mysql.com/downloads/mysql/

Then unzip it to the installation directory (such as C:\Prog\MySQL\). Next, copy my-default.ini to my.ini and modify my.ini as follows:

[mysql]
default-character-set=utf8mb4

[mysqld]
basedir = C:\Prog\MySQL
datadir = C:\Prog\MySQL\data
port = 3306
max_connections=200
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-storage-engine=INNODB
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Then open cmd as "Administrator" - "Administrator" is very important, enter the installation directory and install the MySQL service:

C:\Prog\MySQL\bin>mysqld install
Service successfully installed.

Then start the MySQL service:

net start mysql

At first I thought it was that simple, but it turned out to be an error:

If it is started through the "Service" of the Windows system, the prompt is:

The problem was really frustrating. After searching for a long time, it turned out to be:

If you installed MySQL using the Noinstall package, you may need to initialize the data directory:

• Windows distributions prior to MySQL 5.7.7 include a data directory with a set of preinitialized accounts in the MySQL database.
• As of 5.7.7, Windows installation operations performed using the Noinstall package do not include a data directory. To initialize the data directory, use the instructions at Section 2.10.1.1, “Initializing the Data Directory Manually Using mysqld”.
  

Please refer to these two links for details:

2.3.5.4 Initializing the Data Directory

2.10.1.1 Initializing the Data Directory Manually Using mysqld

Now that we have found the reason, let's manually Initialize Data Directory:

mysqld --defaults-file=C:\Prog\MySQL\my.ini --initialize-insecure 

Then in turn:

net start mysql
mysql -u root -p

The familiar mysql> should appear.

I hope this helps people who encounter similar problems. The reason is that the compressed package version 5.7.7 and later has been changed to require manual Initialize Data Directory.

There is no one-size-fits-all technique, and you have to fill in the holes along the way.

My environment:

• Windows 10 64-bit
• MySQL Community Server 5.7.17 for Windows (x86, 64-bit), ZIP Archive
  

(Dividing line, the above MySQL 5.7.17 is installed.)

Finally, I made a SQLAlchemy to test MySQL:

"""SQLAlchemy operation MySQL test"""

from sqlalchemy import create_engine, Table, Column, Integer, MetaData
from sqlalchemy.dialects.mysql import CHAR
from sqlalchemy.sql import select

ENGINE = create_engine('mysql+pymysql://root:@127.0.0.1:3306/test?charset=utf8mb4')

CONN = ENGINE.connect()

USERINFO = Table('userinfo',
  MetaData(),
  Column('id', Integer, primary_key=True, autoincrement=True),
  Column('name', CHAR(24, charset='utf8mb4')),
  mysql_charset = 'utf8mb4')

USER = select([USERINFO])

RESULT = CONN.execute(USER)

for row in RESULT:
 print(row.name)

RESULT.close()
CONN.close()

It turns out that there is an alarm when the results are output:

Warning: (1366, "Incorrect string value: '\xD6\xD0\xB9\xFA\xB1\xEA...' for column 'VARIABLE_VALUE' at row 480")

What is going on? If you check all character set settings n times, there shouldn't be any problem...

What did you discover after countless thoughts and experiments? What did you find? I found that as long as I show variables like '% charac%';, an alarm will appear!

Let’s take a look at this Warning:

Isn't that exactly it? Is this a MySQL bug? ! OMG!

All right! Back to MySQL 5.6.35!

The alarm is gone!

Then rebuild the database, create the table, and test the program:

This is OK, and finally I came back to MySQL 5.6.35.

Write Python quietly, no one quarrels, and no quarrels like the front-end developers - the years are peaceful, and Python is peaceful.

Finally, I would like to give a thumbs up to Visual Studio Code:

Summarize

The above is the full content of this article. I hope that some of my experience can help friends who encounter the same problems. If you have any questions, you can also leave a message to communicate.