Troubleshooting of MySQL errors: Difference between revisions

Jump to navigation Jump to search

Troubleshooting of MySQL errors (edit)

Revision as of 17:47, 14 April 2026

7,064 bytes added ,  Tuesday at 17:47
m
→‎ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)
 
(15 intermediate revisions by the same user not shown)
Line 1: Line 1:
Troubleshooting of MySQL errors
Troubleshooting of MySQL errors


{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | EN]], [[排除 MySQL 技術疑難問題|漢字]]}}
{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | English]], [[排除 MySQL 技術疑難問題|漢字]]}}




Line 67: Line 67:
* Check if the disk space where mysql data folder located is enough. e.g. Input {{kbd | key=df -h}} on {{Linux}}
* Check if the disk space where mysql data folder located is enough. e.g. Input {{kbd | key=df -h}} on {{Linux}}
* More on [http://stackoverflow.com/questions/18719748/error-1006-hy000-cant-create-database-errno-13-mysql-5-6-12 ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow].
* More on [http://stackoverflow.com/questions/18719748/error-1006-hy000-cant-create-database-errno-13-mysql-5-6-12 ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow].
=== ERROR 1010: Error dropping database (can't rmdir) ===
Message: <pre>Error dropping database (can't rmdir '.\<db_name>\', errno: 17)</pre>
Solution
* Manually delete the leftover non-MySQL files (e.g. .sql files) inside the database directory, then retry DROP DATABASE — or simply delete the entire folder manually.
Root Cause
* MySQL's DROP DATABASE only removes files it manages. Foreign files left in the data directory (such as .sql dumps) prevent rmdir from removing the folder, causing errno 17 (ENOTEMPTY).


=== ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument) ===
=== ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument) ===
Line 139: Line 148:


== Access and Authentication Errors ==
== 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' ===
=== ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' ===
Line 253: Line 287:
) final
) final
WHERE final.rank <= 10
WHERE final.rank <= 10
</pre>
=== ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
Message
<pre>
Query Error (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</pre>
Query with Error
<pre>
-- Create sample table
CREATE TABLE posts (
  id INT,
  post_id VARCHAR(10),
  author VARCHAR(50),
  content TEXT,
  likes INT,
  post_time DATETIME
);
-- Insert sample data
INSERT INTO posts VALUES
(1, 'A123', 'Alice', 'First post', 10, '2024-01-01'),
(2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'),
(3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03');
-- This will cause an error
SELECT
  id,          -- Problem: Not in GROUP BY
  post_id,
  author,
  GROUP_CONCAT(content SEPARATOR "\n") AS content,    -- This is OK as it uses aggregate function
  likes        -- Problem: Not in GROUP BY
FROM posts
GROUP BY post_id, author;
</pre>
Solution: Fix the query by either (1) including all columns in GROUP BY or (2) using aggregate functions (like MAX, MIN, SUM, COUNT, GROUP_CONCAT)
<pre>
-- Method 1: disable `sql_mode=only_full_group_by`
```
There are two ways to disable `sql_mode=only_full_group_by`:
1. Temporary Change (Only affects current session)
```sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```
2. Permanent Change (Modify MySQL configuration file)
- Locate MySQL configuration file (usually `my.cnf` or `my.ini`)
- Add or modify in the `[mysqld]` section:
```ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```
Important Notes:
1. After modifying the configuration file, MySQL service needs to be restarted to take effect
2. Disabling this setting is not recommended because:
  - May lead to unpredictable query results
  - Violates SQL standards
  - May cause compatibility issues in future versions
The recommended approach is to fix the SQL query rather than changing the SQL mode.
```
-- Method 2: Add all columns to GROUP BY
SELECT
  id,
  post_id,
  author,
  content,
  likes
FROM posts
GROUP BY post_id, author, id, content, likes;
-- Method 3: Use aggregate functions for non-GROUP BY columns 
SELECT
  MAX(id) as id,
  post_id,
  author,
  GROUP_CONCAT(content) as contents,
  SUM(likes) as total_likes
FROM posts
GROUP BY post_id, author;
</pre>
</pre>


Line 323: Line 443:
=== Error: MySQL Incorrect datetime value: '0000-00-00 00:00:00' ===
=== Error: MySQL Incorrect datetime value: '0000-00-00 00:00:00' ===


Workaround solution<pre>[https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 MySQL Incorrect datetime value: '0000-00-00 00:00:00' - Stack Overflow]</pre>:
Workaround solution<ref>[https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 MySQL Incorrect datetime value: '0000-00-00 00:00:00' - Stack Overflow]</ref>:
<pre>
<pre>
SET SQL_MODE='ALLOW_INVALID_DATES';
SET SQL_MODE='ALLOW_INVALID_DATES';
</pre>
</pre>
=== Optimization Solutions for Query Performance ===
Originally, product_id (VARCHAR, 200) was used as the primary key. When the data volume reached approximately 600,000, data processing became very slow. Adding a field id (int) as the primary key can solve the problem of slow query speed.


== Connection and Network Errors ==
== Connection and Network Errors ==
Line 405: Line 529:
</pre>
</pre>
* If still not work, reboot the server and restart the MySQL service.
* If still not work, reboot the server and restart the MySQL service.
=== SQLSTATE[HY000] [2002] Can't assign requested address ===
Error message
<pre>
Caught PDOException: /path/to/script.php ERROR: Cannot connect: SQLSTATE[HY000] [2002] Can't assign requested address
utf8: SQLSTATE[HY000] [2002] Can't assign requested address
Caught PDOException: /path/to/script.php ERROR: Cannot connect: SQLSTATE[HY000] [2002] Operation timed out
utf8: SQLSTATE[HY000] [2002] Operation timed out
</pre>
Walkaround Solution:
* You can extend the interval between each MySQL save operation.
Explanation:
It typically occurs when your application is unable to establish a connection to the specified database server.
=== Transfer Data from MySQL to MSSQL ===
Condition: Need to synchronize or transfer data from MySQL database to Microsoft SQL Server.
Solution:
* (Manually method) Export MySQL data in a format compatible with Microsoft SQL Server: {{kbd | key=<nowiki>mysqldump --compatible=mssql [database_name]</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html#option_mysqldump_compatible MySQL :: MySQL 8.4 Reference Manual :: 6.5.4 mysqldump — A Database Backup Program]</ref>
* (Automated method) Use [https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver17 Microsoft SQL Server Migration Assistant] or ''$'' [https://www.webyog.com/product/sqlyog SQLyog] tool (for {{Win}}). Use query commands to specify the data to be transferred from MS SQL server to MySQL server.
Example mysqldump command
<pre>
mysqldump -h 127.0.0.1 -u root -p \
  --skip-lock-tables \
  --single-transaction \
  --default-character-set=utf8 \
  --compatible=mssql \
  --skip-extended-insert \
  --no-auto-rehash \
  --skip-comments \
  database_name > database_name.sql
</pre>
Related issues:
* For large data volumes, connection timeout issues may occur. In such cases, refer to Error 2013 handling methods by increasing connection timeout settings or processing data in batches.
* The two database systems have differences in data types, so data compatibility should be verified before transfer.


== Resource and Environmental Errors ==
== Resource and Environmental Errors ==
Line 443: Line 609:


=== MySQL server has gone away ===
=== MySQL server has gone away ===
steps
Error conditions:
* Enable the option {{kbd | key = log_error}} in MySQL config file e.g. {{kbd | key = <nowiki>log_error="file_name_of_error_log"</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/error-log.html MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log]</ref>.
* Enable the option {{kbd | key = log_error}} in MySQL config file e.g. {{kbd | key = <nowiki>log_error="file_name_of_error_log"</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/error-log.html MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log]</ref>.
* Example error log located at {{kbd | key = <nowiki>file_name_of_error_log</nowiki>}} are as following:
* Example error log located at {{kbd | key = <nowiki>file_name_of_error_log</nowiki>}} are as following:
Line 450: Line 616:
2019-05-23T08:52:19.989876Z 99980 [Note] Aborted connection 99980 to db: 'DB_NAME' user: 'DB_USER' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
2019-05-23T08:52:19.989876Z 99980 [Note] Aborted connection 99980 to db: 'DB_NAME' user: 'DB_USER' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
</pre>
</pre>
Solution:


* Increase the value of {{kbd | key = <nowiki>max_allowed_packet</nowiki>}}<ref>[https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable php - how to check and set max_allowed_packet mysql variable - Stack Overflow]</ref> if the MySQL user has the {{kbd | key = <nowiki>SUPER</nowiki>}} privilege<ref>[https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL]</ref>.
* Increase the value of {{kbd | key = <nowiki>max_allowed_packet</nowiki>}}<ref>[https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable php - how to check and set max_allowed_packet mysql variable - Stack Overflow]</ref> if the MySQL user has the {{kbd | key = <nowiki>SUPER</nowiki>}} privilege<ref>[https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL]</ref>.
Line 609: Line 777:
solution:
solution:
* LIMIT the rows of MySQL query
* LIMIT the rows of MySQL query
== Further Reading ==
* If you cannot find the answer you're looking for, you can visit [https://stackoverflow.com/questions/tagged/mysql Unanswered 'mysql' Questions - Stack Overflow]


== References ==
== References ==
<references/>
<references/>


{{Template:Troubleshooting}}
{{Template:Data factory flow}}


[[Category:MySQL]]
[[Category: MySQL]]
[[Category:Database]]
[[Category: Database]]
[[Category:Data Science]]
[[Category: Data Science]]
[[Category: Revised with LLMs]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/24791...26311"

Navigation menu