Troubleshooting of MySQL errors

Troubleshooting of MySQL errors

🌐 Switch language: EN, 漢字

Installation and Configuration Issues

command not found: mysqldump

1. locate the mysqldump command
   • Key-in the command sudo find / -iname mysqldump for macOS or Linux
   • If you installed
     • XAMPP for macOS : Entire path is /Applications/XAMPP/xamppfiles/bin/mysqldump
     • XAMPP on X disk for Win : Entire path is X:\xampp\mysql\bin\mysqldump.exe
     • MAMP & $ MAMP PRO for macOS : Entire path is /Applications/MAMP/Library/bin/mysqldump
2. input the complete path of mysqldump command
   • old command which caused error mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz
   • new command /Applications/XAMPP/xamppfiles/bin/mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz

Could not start MySQL service on Windows

checklist

1. MySQL Data Directory was allowed to written by NETWORK SERVICE on Windows Server 2008
2. Directory secure-file-priv was allowed to written by NETWORK SERVICE on Windows Server 2008

ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28)

Solutions

• Check if the disk space where mysql data folder located is enough. e.g. Input df -h on Linux
• More on mysql - ERROR 1005 (HY000): Can't create table (errno: 150) - Stack Overflow.

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

Solutions

• Check if the disk space where mysql data folder located is enough. e.g. Input df -h on Linux
• More on ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow.

ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)

Message: [Err] 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)

Solutions

• Check the existence of file DATABASE\TABLE.frm. If not, you may need to create the TABLE before executed the MySQL query.
• Check the permission of file DATABASE\TABLE.frm or folder which the file located. unverified

ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME'

Message: ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME'

pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME

Solution:

• Check the permission of specified user name & database name

$ mysql -u USER -p --host=127.0.0.1
Enter password:

mysql> use DATABASE_NAME

Message: mysqldump: Got error: 1044: Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' when doing LOCK TABLES

$ mysqldump -h 127.0.0.1 -u USER -p DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

Solution:

• Add the mysqldump option --skip-lock-tables if you cannot grant the user permissions .

$ mysqldump -h 127.0.0.1 -u USER -p --skip-lock-tables DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

ERROR 1052 - Column 'column_name' in field list is ambiguous

Message: Error Code: 1052. Column 'column_name' in field list is ambiguous

Cause: Since 'column_name' is present in 2 or more tables ...

Solution: Remain only one table name 'column_name' OR adding the table name alias.

ERROR 1054 - Unknown column in 'where clause'

Message: [Err] 1054 - Unknown column 'xxx' in 'where clause'

Solution:

1. check the column name 'xxx' if exists
2. if the column name 'xxx' was computed by the User-Defined Variables. Enclosed the whole query into another parent derived query.

-- The query which met the error message: [Err] 1054 - Unknown column 'rank' in 'where clause'
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
FROM
(
        ...
) semi
WHERE semi.rank <= 10

Enclosed the whole query into another parent derived query.

SELECT final.*
FROM
(
        SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
        FROM
        (
                ...
        ) semi
) final
WHERE final.rank <= 10

ERROR 1114 (HY000): The table `TABLE_NAME` is full

Possible solution

• Because the hard disk of partition where MySQL Data Directory located is full or almost full, free some hard disk space.

ERROR 1170: BLOB/TEXT column 'url' used in key specification without a key length

Condition

• SQL syntax when tried to create the new table

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 */;

Solution: BLOB/TEXT column 'url' used in key specification with a key length e.g. `url`(500)

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 */;

ERROR 1205: Lock wait timeout exceeded; try restarting transaction

Solution:

1. SHOW OPEN TABLES WHERE in_use > 0;
2. SHOW [FULL] PROCESSLIST;
3. KILL <process id>;

Related article

• 解決 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction

ERROR 1206: The total number of locks exceeds the lock table size

Message: Error Code: 1206. The total number of locks exceeds the lock table size

Current condition:

• Keywin SHOW VARIABLES LIKE 'innodb_buffer_pool_size';. If it returns 8388608, it means 8388608 bytes ≅ 8MB.

Solution:

• Increase innodb_buffer_pool_size e.g. SET GLOBAL innodb_buffer_pool_size=402653184; (402653184 bytes ~ 400MB. Default value is 8MB.)
• Reduce the size of query data

Further reading:

• innodb - How to change value for innodb_buffer_pool_size in MySQL on Mac OS? - Stack Overflow.
• MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.2 Configuring InnoDB Buffer Pool Size

ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Message: ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

Solution: Change the syntax of subquery to column_name BETWEEN start_number to end_number

Further reading:

• MySQL :: MySQL Restrictions and Limitations :: 4 Restrictions on Subqueries

ERROR 1267: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'

Error condition: Tried to concat different type of data e.g. CONCAT(string, int)

Solution: SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR)) or SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))

ERROR 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist

Message: MySQL error 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist

Solution: Fix the typo in the function name

ERROR 1690 - BIGINT UNSIGNED value is out of range

Message: MySQL error #1690 (BIGINT UNSIGNED value is out of range)

Solution :

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

ERROR 1813: Tablespace for table xxx exists

Message: ERROR 1813 Tablespace for table xxx exists.

Solution

• mysql - Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT - Stack Overflow

ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

Wrong sql query as follows:

CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';

Solution: (1) Check if the account was created or not

SELECT User,Host FROM mysql.user;

(2a) If the account was created, set the password for the account.

SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');

(2b) If the account was NOT created, re-create the account.

CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';

references

• MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.2 CREATE USER Syntax
• MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.7 SET PASSWORD Syntax
• MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax

ERROR 2002: Cannot connect: SQLSTATE[HY000] [2002]

Condition on Cygwin terminal of Windows:

$ 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)

Solution:

• Change -h localhost to -h 127.0.0.1

$ mysql -h 127.0.0.1 -u root -p

• If still not work, reboot the server and restart the MySQL service.

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: MySQL/PHP Error:(2002) Only one usage of each socket address (protocol/network address/port) is normally permitted - Stack Overflow

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. 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 bind-address = 127.0.0.1 or set to *
  • comment out 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 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 What Is My IP Address? services.
• (optional) Monitor the firewall activity. More on How to Track Firewall Activity with the Windows Firewall Log on Win

References:

• Connect to a MySQL database remotely
• Error 2003 - Navicat Wiki
• SQLyog MySQL Admin FAQ - Error no. 2003: Can't connect...

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'.

INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;

Solution:

• Increase the settings of (1) DBMS connection keep-alive interval (in seconds) & (2) DBMS connection read time out (in seconds) on MySQL Workbench . 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 LIMIT clause (2) or by splitting the query size e.g. MOD(column, 2) = 0 & MOD(column, 2) > 0 if the column is numeric.

INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;

ERROR 1045 (28000): Access denied for user

Message: ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Solution:

• Check the typo of user name.
• Check the typo of password.
• If you are using the console command, escape the password if it contains special characters e.g. mysql -u root -p'PASSWORD'
• You may need to delete the existing account setting and re-config again.

ERROR 1049 (42000): Unknown database

Message: ERROR 1049 (42000): Unknown database 'MY_DATABASE_p'

Solution:

• Check the database 'MY_DATABASE_p' is exists
• Check there are no TAB character after database name if you want to connect the database 'MY_DATABASE' when you are using console.

ERROR 1070 (42000): Specified key was too long; max key length is 767 bytes

Envoronment: MySQL 5.6

Root cause: "By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Statement”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format."

Solution:

• execute SQL query as the following:

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;

• modify the MySQL configuration file

innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA

• restart the MySQL server

• execute SQL query as the following:

ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;

ERROR 1366: Incorrect string value

Message: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x87\xAF\xF0\x9F...' for column 'XXX'

Solution:

• Check the charset of PHP PDO. AND execute set names utf8mb4
• Check the configuration of table
  • CHARACTER SETS (aka 字元集、字符集): utf8mb4
  • COLLATION (aka 定序、字元序): utf8mb4_unicode_ci

Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied

Message: Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'user'@'localhost' (using password: YES)

Solution:

• If you executed the queryINTO OUTFILE, you need to grant the file permission e.g. GRANT FILE ON *.* TO 'user'@'localhost';.

Related issue: "Error!: SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

Error!: SQLSTATE[42000]: Syntax error or access violation

Message: Error!: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...

Solution:

• SQL syntax debug
• SQL Syntax Check Online, SQL Validator, Instant SQL Compiler Online – EverSQL Not support the PHP: PDO::prepare which the query syntax contains question marks.

Navicat error: [Exp] OLE error 800A03EC

error message:

[Msg] [Exp] Export to - test.xlsx
[Err] [Row1048576] [Exp] OLE error 800A03EC
[Err] [Row1048577] [Exp] OLE error 800A03EC
[Err] [Row1048578] [Exp] OLE error 800A03EC
[Err] [Row1048579] [Exp] OLE error 800A03EC

cause:

• Rows count of results exceed the limit of Microsoft Excel Worksheet size: 1,048,576 rows

solution:

• LIMIT the rows of MySQL query

Navicat error: Import data mismatch

Error:

When importing an Excel file into a MySQL database using Navicat, there is an issue with the total number of records not matching. Solution: Delete blank rows and check for any duplicate primary key data.

Solution:

• 解決 NaviCat 匯入 Excel 檔案到資料庫，出現資料總筆數不一致的問題 (written in Mandarin)

Navicat error: Field doesn't have a default value

[ERR] 1364 - Field 'source' doesn't have a default value

Solution:

• When importing from Excel to a database, if the 'source' field is empty or not specified, it's necessary to assign a value to the 'source' field. Alternatively, modify the field definition to allow the field value to be NULL.

PHP Fatal Error: Allowed Memory Size Exhausted when import SQL.GZ file using adminer

solution: Using the naive mysqldump command to generate the backup file. And import the backup by using mysql command.

Resolve insufficient hard disk space where mysql data located

Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況

How to resolve "zsh: operation not permitted: xxx.sql"

Error condition

/applications/MAMP/library/bin/mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]

zsh: operation not permitted: PATH_TO_SQL_FILE.sql

Solution: Check Security & Privacy Settings: macOS may block certain actions from the terminal due to security settings. You can try allowing full disk access to the terminal:

• Open System Preferences.
• Go to Security & Privacy.
• Select the Privacy tab.
• Scroll down to Full Disk Access.
• Click the lock icon to make changes (you might need your administrator password).
• Find the Terminal app or iTerm2 or whatever terminal you are using and check the box to allow full disk access.
• Restart your terminal.

Resource and Environmental Errors

could not access the mysql log

Version: XAMPP 5.6.15-1 on macOS

Cause:

• the error log only be accessed by the user named mysql

Solution :

find the path to the mysql log
$ ls /Applications/XAMPP/xamppfiles/var/mysql/*.local.err

find the file name of mysql log ex: XXXMacBook-Pro.local.err

set the permission of log
$ sudo chmod 774 /Applications/XAMPP/xamppfiles/var/mysql/XXXMacBook-Pro.local.err

Could not open single-table tablespace file filename.ibd

Version: XAMPP 5.6.15-1 on macOS

Condition: The Mac was shutdown accidentally and the database was not shutdown normally. After reboot the Mac, unable to start the MySQL service.

Possible solution:

1. Edit the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
2. Add this line: innodb_force_recovery = 1
3. Try to start the MySQL service
4. If the MySQL service started successfully, edit the MySQL configuration file and mark this line : #innodb_force_recovery = 1
5. Restart the MySQL service

MySQL server has gone away

steps

• Enable the option log_error in MySQL config file e.g. log_error="file_name_of_error_log".
• Example error log located at file_name_of_error_log are as following:

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)

• Increase the value of max_allowed_packet if the MySQL user has the SUPER privilege.

References

Troubleshooting of ...

• Troubleshooting of Excel errors

• PHP, cUrl, Python, selenium, HTTP status code errors

• Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors

• Troubleshooting of regular expression

• HTML/Javascript: Troubleshooting of javascript, XPath

• Software: Mediawiki, Docker, FTP problems, online conference software

• Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting

Template

• Bug report template