14,953
edits
Troubleshooting of MySQL errors: Difference between revisions
Jump to navigation
Jump to search
m
→ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)
| (7 intermediate revisions by the same user not shown) | |||
| 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 281: | Line 290: | ||
=== | === ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column === | ||
Message | Message | ||
<pre> | <pre> | ||
| Line 304: | Line 313: | ||
(2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'), | (2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'), | ||
(3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03'); | (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> | </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) | 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> | <pre> | ||
-- Method 1: Add all columns to GROUP BY | -- 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 | SELECT | ||
id, | id, | ||
| Line 318: | Line 364: | ||
GROUP BY post_id, author, id, content, likes; | GROUP BY post_id, author, id, content, likes; | ||
-- Method | -- Method 3: Use aggregate functions for non-GROUP BY columns | ||
SELECT | SELECT | ||
MAX(id) as id, | MAX(id) as id, | ||
| Line 500: | 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 713: | Line 784: | ||
<references/> | <references/> | ||
{{Template: | {{Template:Data factory flow}} | ||
[[Category:MySQL]] | [[Category: MySQL]] | ||
[[Category:Database]] | [[Category: Database]] | ||
[[Category:Data Science]] | [[Category: Data Science]] | ||
[[Category: Revised with LLMs]] | |||