14,982
edits
Troubleshooting of MySQL errors: Difference between revisions
Jump to navigation
Jump to search
m
no edit summary
(Created page with "=== Troubleshooting of MySQL errors === ==== command not found: mysqldump ==== # locate the mysqldump command #* {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} fo...") |
mNo edit summary |
||
| Line 1: | Line 1: | ||
Troubleshooting of MySQL errors | |||
== command not found: mysqldump == | |||
# locate the mysqldump command | # locate the mysqldump command | ||
#* {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}} | #* {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}} | ||
| Line 11: | Line 11: | ||
#* new command {{kbd | key=<nowiki>/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</nowiki>}} | #* new command {{kbd | key=<nowiki>/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</nowiki>}} | ||
== could not access the mysql log == | |||
Version: XAMPP 5.6.15-1 on {{Mac}} | Version: XAMPP 5.6.15-1 on {{Mac}} | ||
| Line 28: | Line 28: | ||
</pre> | </pre> | ||
== could not open single-table tablespace file filename.ibd == | |||
Version: XAMPP 5.6.15-1 on {{Mac}} | Version: XAMPP 5.6.15-1 on {{Mac}} | ||
| Line 40: | Line 40: | ||
# Restart the MySQL service | # Restart the MySQL service | ||
== Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined == | |||
Solutions | Solutions | ||
* Number of question marks is not matched with the number of query values <ref>[https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined php Invalid parameter number: parameter was not defined - Stack Overflow]</ref> | * Number of question marks is not matched with the number of query values <ref>[https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined php Invalid parameter number: parameter was not defined - Stack Overflow]</ref> | ||
* The array of query values should not be the associative array. Use sequential array! | * The array of query values should not be the associative array. Use sequential array! | ||
== ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28) == | |||
Solutions | Solutions | ||
| Line 51: | Line 51: | ||
* More on [http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 mysql - ERROR 1005 (HY000): Can't create table (errno: 150) - Stack Overflow]. | * More on [http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 mysql - ERROR 1005 (HY000): Can't create table (errno: 150) - Stack Overflow]. | ||
== ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28) == | |||
Solutions | Solutions | ||
* 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 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) | Message: [Err] 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument) | ||
| Line 64: | Line 64: | ||
* Check the permission of file DATABASE\TABLE.frm or folder which the file located<ref>[http://stackoverflow.com/questions/12106727/mysql-copying-tables-files-gives-rise-to-error-1017-hy000-cant-find-file MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there - Stack Overflow]</ref><ref>[https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/2762-error-1017-hy000-can-t-find-file ERROR 1017 (HY000): Can't find file - Percona Community]</ref>. ''unverified'' | * Check the permission of file DATABASE\TABLE.frm or folder which the file located<ref>[http://stackoverflow.com/questions/12106727/mysql-copying-tables-files-gives-rise-to-error-1017-hy000-cant-find-file MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there - Stack Overflow]</ref><ref>[https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/2762-error-1017-hy000-can-t-find-file ERROR 1017 (HY000): Can't find file - Percona Community]</ref>. ''unverified'' | ||
== ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' == | |||
Message: | Message: | ||
<pre> | <pre> | ||
| Line 80: | Line 80: | ||
</pre> | </pre> | ||
== ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES) == | |||
Solution: | Solution: | ||
* Check the typo of user name. | * Check the typo of user name. | ||
| Line 87: | Line 87: | ||
* You may need to delete the existing account setting and re-config again. | * You may need to delete the existing account setting and re-config again. | ||
== ERROR 1054 - Unknown column in 'where clause' == | |||
Message: [Err] 1054 - Unknown column 'xxx' in 'where clause' | Message: [Err] 1054 - Unknown column 'xxx' in 'where clause' | ||
| Line 117: | Line 117: | ||
</pre> | </pre> | ||
== ERROR 1690 - BIGINT UNSIGNED value is out of range == | |||
Message: MySQL error #1690 (BIGINT UNSIGNED value is out of range) | Message: MySQL error #1690 (BIGINT UNSIGNED value is out of range) | ||
| Line 125: | Line 125: | ||
</pre> | </pre> | ||
== ERROR 1114 (HY000): The table `TABLE_NAME` is full == | |||
Possible solution | Possible solution | ||
* Because the partition is full or almost full, free some hard-disk space<ref>[http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full mysql - ERROR 1114 (HY000): The table is full - Stack Overflow]</ref>. | * Because the partition is full or almost full, free some hard-disk space<ref>[http://stackoverflow.com/questions/730579/error-1114-hy000-the-table-is-full mysql - ERROR 1114 (HY000): The table is full - Stack Overflow]</ref>. | ||
== ERROR 1205: Lock wait timeout exceeded; try restarting transaction == | |||
Solution: <ref>[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im mysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow]</ref> | Solution: <ref>[https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im mysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow]</ref> | ||
# {{kbd | key=<nowiki>SHOW OPEN TABLES WHERE in_use > 0;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.24 SHOW OPEN TABLES Syntax]</ref> | # {{kbd | key=<nowiki>SHOW OPEN TABLES WHERE in_use > 0;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.24 SHOW OPEN TABLES Syntax]</ref> | ||
| Line 135: | Line 135: | ||
# {{kbd | key=<nowiki>KILL <process id>;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/kill.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.6.4 KILL Syntax]</ref> | # {{kbd | key=<nowiki>KILL <process id>;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/kill.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.6.4 KILL Syntax]</ref> | ||
== 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 | Message: Error Code: 1206. The total number of locks exceeds the lock table size | ||
Solution: Increase {{kbd | key = innodb_buffer_pool_size}} e.g. [http://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os innodb - How to change value for innodb_buffer_pool_size in MySQL on Mac OS? - Stack Overflow]. | Solution: Increase {{kbd | key = innodb_buffer_pool_size}} e.g. [http://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os innodb - How to change value for innodb_buffer_pool_size in MySQL on Mac OS? - 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: | Wrong sql query as follows: | ||
<pre> | <pre> | ||
| Line 167: | Line 167: | ||
* [http://dev.mysql.com/doc/refman/5.7/en/drop-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax] | * [http://dev.mysql.com/doc/refman/5.7/en/drop-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax] | ||
== ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' == | |||
Condition on Cygwin terminal of Windows: | Condition on Cygwin terminal of Windows: | ||
<pre> | <pre> | ||
| Line 185: | Line 185: | ||
</pre> | </pre> | ||
== ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused") == | |||
Solution: | 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 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. | ||
== ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out") == | |||
Solution: | Solution: | ||
* Check the permission of MySQL database user. | * Check the permission of MySQL database user. | ||
| Line 199: | Line 199: | ||
* [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ Connect to a MySQL database remotely] | * [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ Connect to a MySQL database remotely] | ||
== ERROR 2013: Lost connection to MySQL server during query == | |||
Message: Error Code: 2013. Lost connection to MySQL server during query | Message: Error Code: 2013. Lost connection to MySQL server during query | ||
| Line 214: | Line 214: | ||
</pre> | </pre> | ||
== Navicat error: [Exp] OLE error 800A03EC == | |||
error message: | error message: | ||
<pre> | <pre> | ||
| Line 230: | Line 230: | ||
* LIMIT the rows of MySQL query | * LIMIT the rows of MySQL query | ||
== resolve insufficient hard disk space where mysql data located == | |||
[http://errerrors.blogspot.tw/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況] | [http://errerrors.blogspot.tw/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況] | ||
== references == | |||
<references/> | <references/> | ||