Editing Troubleshooting of MySQL errors (section)

== Connection and Network Errors ==

=== Error: SQLSTATE[HY000] [2002] No such file or directory ===
Error message: SQLSTATE[HY000] [2002] No such file or directory

Solutions:
* Modify the host of database from {{kbd | key=localhost}} to {{kbd | key=127.0.0.1}}

=== ERROR 2013: Lost connection to MySQL server during query ===
Message: Error Code: 2013. Lost connection to MySQL server during query

Condition: After executed the following query contains number of rows which exceed 1,000,000 rows, I met the error message 'Error Code: 2013. Lost connection to MySQL server during query'.
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;
</pre>

Solution:
* Increase the settings of (1) '''DBMS connection keep-alive interval (in seconds)''' & (2)  '''DBMS connection read time out (in seconds)''' on [https://www.mysql.com/products/workbench/ MySQL Workbench] <ref>[http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer-99-999-seconds-on-mysql-workbench How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow]</ref><ref>[http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow]</ref>. And remember to restart the MySQL Workbench after the settings were modified. e.g. The default setting of '''DBMS connection read time out (in seconds)''' is 30 seconds, you may increase to 6000 seconds (100 minutes).
* Reduce the number of rows to reduce the execution time (1) by using {{kbd | key=LIMIT}} clause (2) or by splitting the query size e.g. {{kbd | key=<nowiki>MOD(column, 2) = 0</nowiki>}} & {{kbd | key=<nowiki>MOD(column, 2) > 0</nowiki>}} if the column is numeric.
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;
</pre>

=== ERROR 2003 (HY000): Can't connect to MySQL server on 'ip' ===
'''ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' '''

Solution:
* Check if the IP is alive

'''ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (111 "Connection refused")'''

Solution:
* Check if the MySQL service is running or not<ref>[https://www.cyberciti.biz/faq/how-to-find-out-if-mysql-is-running-on-linux/ HowTo: Find out If MySQL Is Running On Linux Or Not]</ref>. If not, start the MySQL service.
* Check the firewall rules

'''ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out")'''

Solution:
* Check the configuration of MySQL
** comment out [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address bind-address] = 127.0.0.1 or set to *
** comment out [https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_skip-networking skip-networking]
* Check the permission of MySQL database user.
* Check the firewall rules at (1) your personal computer (2) the server where MySQL service located (3) ISP/VM providers. More on [https://devops.profitbricks.com/tutorials/install-mysql-on-centos-7/#firewall-rules Install MySQL on CentOS 7 | ProfitBricks DevOps Central] 
** Check if your IP address was included in the allowed IP address list of firewall rules.
** Check if your IP address was changed by using [https://whatismyipaddress.com/ What Is My IP Address?] services.
* (optional) Monitor the firewall activity. More on [https://www.howtogeek.com/220204/how-to-track-firewall-activity-with-the-windows-firewall-log/ How to Track Firewall Activity with the Windows Firewall Log] on {{Win}}

References:
* [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ Connect to a MySQL database remotely]
* [http://wiki.navicat.com/wiki/index.php/Error_2003 Error 2003 - Navicat Wiki]
* [http://faq.webyog.com/content/23/15/en/error-no-2003-can_t-connect.html SQLyog MySQL Admin FAQ - Error no. 2003: Can't connect...]

=== ERROR 2002: SQLSTATE[HY000] [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted ===

Message: (1) [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted (2) "SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。" in Chinese


Solution: [https://stackoverflow.com/questions/10317974/mysql-php-error2002-only-one-usage-of-each-socket-address-protocol-network-a MySQL/PHP Error:(2002) Only one usage of each socket address (protocol/network address/port) is normally permitted - Stack Overflow]

=== ERROR 2002: Cannot connect: SQLSTATE[HY000] [2002] ===
Condition on Cygwin terminal of Windows:
<pre>
$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)

$ mysql -h localhost -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)
</pre>

Solution:
* Change {{kbd | key=<nowiki>-h localhost</nowiki>}} to {{kbd | key=<nowiki>-h 127.0.0.1</nowiki>}}
<pre>
$ mysql -h 127.0.0.1 -u root -p
</pre>
* 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.