Troubleshooting of MySQL errors

From LemonWiki共筆
Jump to navigation Jump to search

Troubleshooting of MySQL errors

command not found: mysqldump

  1. locate the mysqldump command
    • sudo find / -iname mysqldump for Mac icon_os_mac.png or Linux Os linux.png
    • If you installed
      • XAMPP for Mac icon_os_mac.png : Entire path is /Applications/XAMPP/xamppfiles/bin/mysqldump
      • XAMPP on X disk for Win Os windows.png : Entire path is X:\xampp\mysql\bin\mysqldump.exe
      • MAMP & MAMP PRO for Mac icon_os_mac.png : 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 access the mysql log

Version: XAMPP 5.6.15-1 on Mac icon_os_mac.png

Cause:

  • the error log only be accessed by the user named mysql[1]

Solution [2]:

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 Mac icon_os_mac.png

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

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

Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Solutions

  • Number of question marks is not matched with the number of query values [5]
  • The array of query values should not be the associative array. Use sequential array!

Error!: SQLSTATE[HY000]: General error

Message: Error!: SQLSTATE[HY000]: General error

Condition: When I used the PHP: PDO

Solutions:

  • "You do not use $result = $stmt->fetchAll(); with update or insert queries" [6].
  • You do not use $result = $stmt->fetchAll(); in the query INTO OUTFILE ... [7][8].

Error!: SQLSTATE[HY000]: General error: 3 Error writing file 'xxx\Temp\xxx.tmp' (Errcode: 28 - No space left on device)

Example error message: Error!: SQLSTATE[HY000]: General error: 3 Error writing file 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\MY2713.tmp' (Errcode: 28 - No space left on device)

Condition: Check the disk free space of mysql tmpdir folder

Solutions: Increase the free space of mysql tmpdir folder. Or change the mysql tmpdir folder with another hard disk drive contains more free space[9].

  • Check the current mysql tmpdir folder. Query the syntax SHOW VARIABLES LIKE 'tmpdir';.
    • On Win Os windows.png the default temporary folder[10] is %TMP% = %USERPROFILE%\AppData\Local\Temp.
    • On Linux Os linux.png the default temporary folder maybe is /tmp
  • Edit the mysql configuration file
[mysqld]
tmpdir=X:/temp
  • Restart the MySQL service

Validation

  • Query the syntax SHOW VARIABLES LIKE 'tmpdir'; to validate the modification of mysql configuration file.


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

Solutions

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

Solutions

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[11][12]. unverified

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

Message:

pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME
ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database '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

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'[13]
  • You may need to delete the existing account setting and re-config again.

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';[14][15].


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"[16]

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 partition is full or almost full, free some hard-disk space[17].

ERROR 1205: Lock wait timeout exceeded; try restarting transaction

Solution: [18]

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

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

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:

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:

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))[22][23]

ERROR 1690 - BIGINT UNSIGNED value is out of range

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

Solution [24][25]:

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

ERROR 1813: Tablespace for table xxx exists

Message: ERROR 1813 Tablespace for table xxx exists.

Solution


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

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock'

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

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[26]. 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:

References:

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 [27][28]. 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;

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

resolve insufficient hard disk space where mysql data located

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

References

  1. Open the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
    # The MySQL server
    [mysqld]
    user = mysql
    
  2. Using the CHMOD command effectively @ Computer Plumber
  3. mysql - Error: could not open single-table tablespace file .\scrapers\records.ibd - Stack Overflow
  4. MySQL Won't Start - InnoDB Corruption and Recovery. - Chepri
  5. php Invalid parameter number: parameter was not defined - Stack Overflow
  6. php - PDO error: " SQLSTATE[HY000: General error " When updating database - Stack Overflow]
  7. Save MySQL query results into a text or CSV file
  8. mysql - Can't create/write to file (Errcode: 22) - Stack Overflow
  9. Changing the tmp folder of mysql - Stack Overflow
  10. Where is the Temporary folder? - Microsoft Community
  11. MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there - Stack Overflow
  12. ERROR 1017 (HY000): Can't find file - Percona Community
  13. escape characters - Escaping a password using mysqldump console - Super User
  14. MySQL :: MySQL 5.7 Reference Manual :: 6.2.1 Privileges Provided by MySQL
  15. MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777 - Stack Overflow
  16. database - How should I tackle --secure-file-priv in MySQL? - Stack Overflow
  17. mysql - ERROR 1114 (HY000): The table is full - Stack Overflow
  18. mysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow
  19. MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.24 SHOW OPEN TABLES Syntax
  20. MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.29 SHOW PROCESSLIST Syntax
  21. MySQL :: MySQL 5.7 Reference Manual :: 13.7.6.4 KILL Syntax
  22. mysql - Cast int to varchar - Stack Overflow
  23. mysql字符集问题:Illegal mix of collations-每天让自己进步一点-51CTO博客
  24. MySQL :: MySQL 8.0 Reference Manual :: 11.2.6 Out-of-Range and Overflow Handling
  25. MySQL error #1690 (BIGINT UNSIGNED value is out of range) for UNIX_TIMESTAMP() - Stack Overflow
  26. HowTo: Find out If MySQL Is Running On Linux Or Not
  27. How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow
  28. Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow


Troubleshooting of ...

Template