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

4,505 bytes added ,  Tuesday at 17:47
m
→‎ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)
 
(9 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 278: 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 451: Line 546:
Explanation:
Explanation:
It typically occurs when your application is unable to establish a connection to the specified database server.
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 664: Line 784:
<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/25183...26311"

Navigation menu