Basic Information

MySQL is a database management system for a special kind of SQL databases. SQL databases basically contain a data model and then the data itself. The data model defines the structure for the stored data.

SQL is also the name for the programming language.

The SQL Syntax of the programming language is basically distinguished between the Data Definition Language (DDL) and the Data Manipulation Language (DML). SQL for MySQL is well documented in the internet: https://dev.mysql.com/doc/

MySQL is widespread and a base for many web pages and web systems in the internet. MySQL is open source although some module are closed source. In Linux MySQL can be easily installed and used. There is also an installer for Windows available.

The Problem

Before I upgraded from an older MySQL version to a newer one the following command could be executed successfully:

mysql> GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost IDENTIFIED BY 'secpasswd';

Executing the command after I upgraded MySQL gave me the following error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'secpasswd'' at line 1

The command should basically create a user “testuser” and give this user full access to the database “testdb”. It’s really frustrating because the command above worked for me long time without problems.

But after short trails I found the new way to create a user and to grant access to the database.

The Fix

The complete procedure is listed here:

CREATE database testdb;

The command above creates the databases with the name “testdb”.

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'secpasswd';

The second command above creates a user (“testuser”) and sets a password.

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

The last step gives the user “testuser” full access to the database “testdb”. The procedure works for me on MySQL version 8.0.31-0ubuntu0.20.04.2.

Conclusion

Often syntax and commands are changing after system upgrades. Software is often replaced by forks or software which fits better to the users needs. Professionals invest a lot of time to find out how the new versions or the new software works. But on the other side updated and new software comes with new features and bug fixes.

Feel free to comment my post.