Troubleshooting of MySQL errors: Difference between revisions

Troubleshooting of MySQL errors (edit)

1,146 bytes added , 12 May 2024

15,041

edits

@@ Line 424: / Line 424: @@
 == Access and Authentication Errors ==
+=== 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>
 == SQL and Query Errors ==