Editing Troubleshooting of MySQL errors (section)

== Access and Authentication Errors ==

=== [Warning] Using a password on the command line interface can be insecure ===
Problem Description: 
When exporting or importing MySQL databases via bash commands, you may encounter the following warning:

<pre>
mysql: [Warning] Using a password on the command line interface can be insecure.

or

mysqldump: [Warning] Using a password on the command line interface can be insecure.
</pre>

This typically occurs when using commands like:

<pre>
mysqldump -u USERNAME -p -h 127.0.0.1 --default-character-set=utf8 DATABASE > DATABASE.sql
</pre>

Solution: To resolve this warning and improve security, follow these steps:
# Set up a login path using [https://dev.mysql.com/doc/refman/8.4/en/mysql-config-editor.html mysql_config_editor]: {{kbd | key=<nowiki>mysql_config_editor set --login-path=local --host=localhost --user=root --password</nowiki>}}
# Use the login path when exporting or importing MySQL databases (1) For exporting: {{kbd | key=<nowiki>mysqldump --login-path=local --default-character-set=utf8 DATABASE > DATABASE.sql</nowiki>}} (2) For importing: {{kbd | key=<nowiki>pv DATABASE.sql.gz | gunzip | mysql --login-path=local --default_character_set utf8 --force DATABASE</nowiki>}}
# To view all configured login paths {{kbd | key=<nowiki>mysql_config_editor print --all</nowiki>}}

By using a login path, you avoid exposing your password in the command line, thus resolving the security warning.

=== ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' ===
Message: {{kbd | key=<nowiki>ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME'</nowiki>}}
<pre>
pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME
</pre>

Solution:
* Check the permission of specified user name & database name
<pre>
$ mysql -u USER -p --host=127.0.0.1
Enter password:

mysql> use DATABASE_NAME
</pre>

Message: {{kbd | key=<nowiki>mysqldump: Got error: 1044: Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' when doing LOCK TABLES</nowiki>}}
<pre>
$ mysqldump -h 127.0.0.1 -u USER -p DATABASE_NAME TABLE_NAME > TABLE_NAME.sql
</pre>

Solution:
* Add the mysqldump option {{kbd | key=<nowiki>--skip-lock-tables</nowiki>}} if you cannot grant the user permissions <ref>[https://stackoverflow.com/questions/7415698/skip-lock-tables-and-mysqldump mysql - skip-lock-tables and mysqldump - Stack Overflow]</ref>.
<pre>
$ mysqldump -h 127.0.0.1 -u USER -p --skip-lock-tables DATABASE_NAME TABLE_NAME > TABLE_NAME.sql
</pre>

=== Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied ===
Message: Error!: {{kbd | key=<nowiki>SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'user'@'localhost' (using password: YES)</nowiki>}}

Solution:
* If you executed the query{{kbd | key=INTO OUTFILE}}, you need to grant the file permission e.g. {{kbd | key=GRANT FILE ON *.* TO 'user'@'localhost';}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file MySQL :: MySQL 5.7 Reference Manual :: 6.2.1 Privileges Provided by MySQL]</ref><ref>[https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777 - Stack Overflow]</ref>.


Related issue: "Error!: SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"<ref>[https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql database - How should I tackle --secure-file-priv in MySQL? - Stack Overflow]</ref>

=== ERROR 1049 (42000): Unknown database ===
Message: ERROR 1049 (42000): Unknown database 'MY_DATABASE_p'

Solution:
* Check the database 'MY_DATABASE_p' is exists
* Check there are no {{kbd | key=TAB}} character after database name if you want to connect the database 'MY_DATABASE' when you are using console.

=== ERROR 1045 (28000): Access denied for user ===
Message: ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Solution:
* Check the typo of user name.
* Check the typo of password.
* If you are using the console command, escape the password if it contains special characters e.g. {{kbd | key=<nowiki>mysql -u root -p'PASSWORD'</nowiki>}}<ref>[http://superuser.com/questions/123928/escaping-a-password-using-mysqldump-console escape characters - Escaping a password using mysqldump console - Super User]</ref>
* You may need to delete the existing account setting and re-config again.

=== ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. ===
Wrong sql query as follows:
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';
</pre>

Solution:
(1) Check if the account was created or not
<pre>
SELECT User,Host FROM mysql.user;
</pre>

(2a) If the account was created, set the password for the account.
<pre>
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');
</pre>

(2b) If the account was NOT created, re-create the account.
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';
</pre>

references
* [http://dev.mysql.com/doc/refman/5.7/en/create-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.2 CREATE USER Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/set-password.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.7 SET PASSWORD Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/drop-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax]