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

10,105 bytes added ,  Tuesday at 17:47
m
→‎ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)
 
(20 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 技術疑難問題|漢字]]}}
 
 
In this document, we explore various common issues encountered while working with MySQL, categorizing them into seven distinct groups based on their nature and impact. These categories help in identifying the underlying problems and facilitate efficient troubleshooting. The issues are classified as follows:
 
# '''Installation and Configuration Issues''': Problems related to setting up and configuring MySQL.
# '''Database and Table Operation Errors''': Issues during operations such as creating, dropping, or accessing databases and tables.
# '''Access and Authentication Errors''': Errors dealing with user permissions and authentication.
# '''SQL and Query Errors''': Issues related to the syntax and execution of SQL queries.
# '''Connection and Network Errors''': Problems involving connectivity between clients and the MySQL server.
# '''Resource and Environmental Errors''': Errors caused by system resources like disk space, memory limits, or file system permissions.
# '''Miscellaneous Tool-Specific Errors''': Errors specific to database tools like Navicat or issues from scripting environments like PHP.
 
Each category not only delineates common errors but also aids in the direct application of specific solutions tailored to each type of issue.


== Installation and Configuration Issues ==
== Installation and Configuration Issues ==
Line 41: Line 54:
References
References
* [https://mysqldump.guru/mysqldump-got-errno-32-on-write.html mysqldump: Got errno 32 on write | mysqldump.guru]
* [https://mysqldump.guru/mysqldump-got-errno-32-on-write.html mysqldump: Got errno 32 on write | mysqldump.guru]
== ERROR 1114 (HY000): The table `TABLE_NAME` is full ==
Possible solution
* Because the hard disk of partition where [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] located is full or almost full, free some hard disk space<ref>[http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full mysql - ERROR 1114 (HY000): The table is full - Stack Overflow]</ref>.


== Database and Table Operation Errors ==
== Database and Table Operation Errors ==
Line 59: 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 94: Line 111:


Solution: Fix the typo in the function name
Solution: Fix the typo in the function name
=== ERROR 1170: BLOB/TEXT column 'url' used in key specification without a key length ===
Condition
* SQL syntax when tried to create the new table
<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `url` (`url`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>
Solution: BLOB/TEXT column 'url' used in key specification with a key length e.g. {{kbd | key=`url`(500)}}
<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`(500))
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>


== 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 210: 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 277: Line 440:
Further reading:
Further reading:
* [https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/subquery-restrictions.html MySQL :: MySQL Restrictions and Limitations :: 4 Restrictions on Subqueries]
* [https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/subquery-restrictions.html MySQL :: MySQL Restrictions and Limitations :: 4 Restrictions on Subqueries]
=== Error: MySQL Incorrect datetime value: '0000-00-00 00:00:00' ===
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>
SET SQL_MODE='ALLOW_INVALID_DATES';
</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 355: 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 393: 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 400: 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 484: Line 702:
Related article
Related article
* [https://errerrors.blogspot.com/2022/10/how-to-fix-lock-wait-timeout-exceeded-try-restarting-transaction-on-mysql.html 解決 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction]
* [https://errerrors.blogspot.com/2022/10/how-to-fix-lock-wait-timeout-exceeded-try-restarting-transaction-on-mysql.html 解決 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction]
=== ERROR 1114 (HY000): The table `TABLE_NAME` is full ===
Possible solution
* Because the hard disk of partition where [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] located is full or almost full, free some hard disk space<ref>[http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full mysql - ERROR 1114 (HY000): The table is full - Stack Overflow]</ref>.


== Miscellaneous Tool-Specific Errors ==
== Miscellaneous Tool-Specific Errors ==
Line 555: 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/24786...26311"

Navigation menu