MySQL - MariaDB - error in your SQL syntax near ';

Error Message:
Error Code: 1064. You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to use near '; INSERT CITY (..... at line 1

The case:
INSERT CITY (CITY_CODE, CITY_DESCRIPTION) VALUES (N'TT1', N'test1');;
INSERT CITY (CITY_CODE, CITY_DESCRIPTION) VALUES (N'TT2', N'test2');

The cause:
The ';' without a statement.

Solution:
;; --> ;

MySQL - Operation ALTER USER failed for 'root'

Error Message:

Operation ALTER USER failed for 'root'

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

or

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

The case:

root password was damaged and login with root user is failed.

Solution:

1. Connect to MySQL with another user
2. Update root set authentication_string=null
3. FLUSH PRIVILEGES;
4. Logout (quit)
5. Connect to MySQL using "mysql -u root"
6. Set root password
7. Logout (quit)

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysql -uusername -ppassword

 

mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.31 sec)

 

mysql> quit

 

Bye

 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root

 

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'newrootpassword';

Query OK, 0 rows affected (0.26 sec)

mysql> quit

Bye

Can't connect to MySQL 8.0 because a plugin error

The case:
Can't connect to MySQL 8.0 because a plugin error

Error message:

FATAL: unable to connect to MySQL server on host '10.4.117.147', port 3306, aborting...
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
FATAL: 'sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed

Cause:
The main cause is that the user has password plugin as "mysql_native_password" but authentication_string contains different hash in mysql.user table.

Solution:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'USERNAME';
FLUSH PRIVILEGES;
UPDATE mysql.user
SET authentication_string=concat('*',upper(sha1(unhex(sha1('USERNAME')))))
WHERE user='USERNAME';
FLUSH PRIVILEGES;

MySQL service is in the status “starting” on windows but the service doesn't work

The case:

MySQL service is in the status “starting” on windows but the service doesn't work.

No error message, but MySQL doesn't work

Cause: 

Encoding issue

Solution: 

Save the configuration file "my.ini" using "UTF8

Windows - Find which program uses a port

netstat -ano -p tcp

(form cmd)

Windows - Concatenate multiple text files into one

In cmd:

type *.txt > OneFileName.txt

MySQL - Get the data of the mysql process

 select * from performance_schema.threads where THREAD_ID=1

MySQL - date parts

SELECT SECOND("2020-06-29 09:41:42"); 
#-->42
SELECT SECOND("23:59:59");
#-->59
SELECT SECOND(CURRENT_TIME());

SELECT MINUTE("2020-06-29 09:41:42"); 
#-->41

SELECT HOUR("2020-06-29 15:41:42"); 
#-->15

SELECT DAY("2020-06-29 09:41:42"); 
#-->29
SELECT DAYOFMONTH("2020-06-29");
#-->29
SELECT DAYOFWEEK("2020-06-29");
#-->2 = Monday
SELECT DAYOFYEAR("2020-06-29");

#-->181 (count by yourself... :) )

SELECT MONTH("2020-06-29 09:41:42"); 
#-->6
SELECT MONTHNAME("2020-06-29 09:41:42"); 
#-->June

SELECT YEAR("2020-06-29 09:41:42"); 
#-->2020

SELECT DATE("2020-06-29 09:41:42"); 
#-->2020-06-29

SELECT TIME("2020-06-29 09:41:42"); 
#-->09:41:42