Troubleshooting of MySQL errors - Revision history

Planetoid: /* SQLSTATE[HY000] [2002] Can't assign requested address */

2026-09-17T02:47:59Z

SQLSTATE[HY000] [2002] Can't assign requested address

← Older revision		Revision as of 10:47, 17 September 2026
Line 547:		Line 547:
	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.

			=== Resolving "Host 'xx.xx.xxx.xxx' is blocked because of many connection errors" ===

			Error Message

			<pre>
			Host 'xx.xx.xxx.xxx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
			</pre>

			Understanding the Issue

			(1) Check the threshold value

			MySQL's mechanism works as follows: once the same source host (identified by IP or hostname) accumulates a number of "connection errors" reaching the value configured in `max_connect_errors`, MySQL automatically blocks that source and refuses further connections from it.

			<pre>
			SHOW VARIABLES LIKE 'max_connect_errors';
			</pre>

			The threshold found was 100.

			(2) Check which hosts are currently recorded/blocked

			<pre>
			SELECT IP, HOST, SUM_CONNECT_ERRORS, COUNT_HOST_BLOCKED_ERRORS
			FROM performance_schema.host_cache
			ORDER BY SUM_CONNECT_ERRORS DESC;
			</pre>

			Confirmed that the only blocked IP is `xx.xx.xxx.xxx`, with `SUM_CONNECT_ERRORS` exactly matching the threshold of 100, and no other IPs were affected.


			Resolving the Issue

			⚠️ Note: `flush-hosts` / `FLUSH HOSTS` clears the entire host cache, not just a single IP. If the query results show records for other hosts as well, they will all be reset at the same time. For a shared production environment, it's recommended to confirm with other administrators before running this.

			(1) In a terminal or command prompt, run:

			<pre>
			mysqladmin -u root -p flush-hosts
			</pre>

			or

			<pre>
			mysql -u root -p
			Once inside the MySQL shell, run: FLUSH HOSTS;
			</pre>

			Note: The `FLUSH HOSTS` syntax has been deprecated since MySQL 8.0.23 and removed in 8.4<ref>[https://dev.mysql.com/doc/refman/8.4/en/performance-schema-host-cache-table.html MySQL :: MySQL 8.4 Reference Manual :: 29.12.22.3 The host_cache Table]</ref><ref>[https://oneuptime.com/blog/post/2026-03-31-mysql-fix-max-connect-errors-blocked-hosts/view How to Fix max_connect_errors and Blocked Hosts in MySQL]</ref>. If your environment is on a newer version, use the officially recommended alternative instead:
			<pre>
			TRUNCATE TABLE performance_schema.host_cache;
			</pre>

			(2) Verify after execution

			<pre>
			SELECT IP, HOST, SUM_CONNECT_ERRORS, COUNT_HOST_BLOCKED_ERRORS
			FROM performance_schema.host_cache
			ORDER BY SUM_CONNECT_ERRORS DESC;
			</pre>

			Result: no rows returned (0 rows), indicating the host cache has been cleared — the previous connection error counts and blocked status have been reset, and the IP should now be able to connect normally.

	=== Transfer Data from MySQL to MSSQL ===		=== Transfer Data from MySQL to MSSQL ===

Planetoid: /* errno 41 - Error dropping database */

2026-06-19T15:26:39Z

errno 41 - Error dropping database

← Older revision		Revision as of 23:26, 19 June 2026
Line 649:		Line 649:
	Validation		Validation
	* Query the syntax {{kbd \| key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}} to validate the modification of mysql configuration file.		* Query the syntax {{kbd \| key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}} to validate the modification of mysql configuration file.

			=== errno 11 - Resource temporarily unavailable ===
			Message: '''Can't create a new thread (errno 11 "Resource temporarily unavailable")'''

			Solution: [https://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit How to permanently raise ulimit 'open files' and MySQL 'open_files_limit']

	=== errno 41 - Error dropping database ===		=== errno 41 - Error dropping database ===

Planetoid: /* ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28) */

2026-04-14T09:47:16Z

ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)

← Older revision		Revision as of 17:47, 14 April 2026
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) ===

Planetoid: /* Transfer Data from MySQL to MSSQL */

2025-08-29T10:52:22Z

Transfer Data from MySQL to MSSQL

@@ Line 545: / Line 545: @@
 * (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.
 Related issues:

Planetoid: /* Transfer Data from MySQL to MSSQL */

2025-08-29T10:49:54Z

Transfer Data from MySQL to MSSQL

← Older revision		Revision as of 18:49, 29 August 2025
Line 544:		Line 544:
	Solution:		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>		* (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://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.		* (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.

	Related issues:		Related issues:

Planetoid at 03:32, 15 July 2025

2025-07-15T03:32:46Z

← Older revision		Revision as of 11:32, 15 July 2025
Line 762:		Line 762:
	<references/>		<references/>

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

	[[Category: MySQL]]		[[Category: MySQL]]

Planetoid: /* Transfer Data from MySQL to MSSQL */

2025-03-20T03:55:33Z

Transfer Data from MySQL to MSSQL

← Older revision		Revision as of 11:55, 20 March 2025
Line 543:		Line 543:

	Solution:		Solution:
	* (Manually method) Export MySQL data in a format compatible with Microsoft SQL Server: {{kbd \| key=<nowiki>mysqldump --compatible=mssql [database_name]</nowiki>}}		* (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://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.		* (Automated method) Use ''$'' [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.

Planetoid: /* Connection and Network Errors */

2025-03-20T03:40:04Z

Connection and Network Errors

@@ Line 537: / Line 537: @@
 Explanation:
 It typically occurs when your application is unable to establish a connection to the specified database server.
 == Resource and Environmental Errors ==

Planetoid: /* References */

2024-12-18T03:06:47Z

References

← Older revision		Revision as of 11:06, 18 December 2024
Line 752:		Line 752:
	{{Template:Troubleshooting}}		{{Template:Troubleshooting}}

	[[Category:MySQL]]		[[Category: MySQL]]
	[[Category:Database]]		[[Category: Database]]
	[[Category:Data Science]]		[[Category: Data Science]]
			[[Category: Revised with LLMs]]

Planetoid: /* Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column */

2024-12-18T03:04:52Z

Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

@@ Line 281: / Line 281: @@
-=== Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
+=== ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
 Message
 <pre>
@@ Line 304: / Line 304: @@
 (2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'),
 (3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03');
 </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: Add all columns to GROUP BY
+-- Method 1: disable `sql_mode=only_full_group_by`
 SELECT
     id,
@@ Line 318: / Line 355: @@
 GROUP BY post_id, author, id, content, likes;
--- Method 2: Use aggregate functions for non-GROUP BY columns
+-- Method 3: Use aggregate functions for non-GROUP BY columns
 SELECT
     MAX(id) as id,