MySQL commands: Difference between revisions

Jump to navigation Jump to search

MySQL commands (edit)

Revision as of 11:24, 23 January 2018

13,836 bytes removed ,  23 January 2018
m
→‎Troubleshooting of MySQL errors
Line 104: Line 104:


=== Troubleshooting of MySQL errors ===
=== Troubleshooting of MySQL errors ===
==== command not found: mysqldump ====
[[Troubleshooting of MySQL errors]]
# locate the mysqldump command
#* {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}}
#* If you installed
#** [https://www.apachefriends.org/index.html XAMPP] for {{Mac}}: Entire path is {{kbd | key=<nowiki>/Applications/XAMPP/xamppfiles/bin/mysqldump</nowiki>}}
#** [https://www.apachefriends.org/index.html XAMPP] on X disk for {{Win}}: Entire path is {{kbd | key=<nowiki>X:\xampp\mysql\bin\mysqldump.exe</nowiki>}}
#** [https://www.mamp.info/en/ MAMP & MAMP PRO] for {{Mac}}: Entire path is {{kbd | key=<nowiki>/Applications/MAMP/Library/bin/mysqldump</nowiki>}}
# input the complete path of mysqldump command
#* old command which caused error {{kbd | key=<nowiki>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}}
 
Cause:
* the error log only be accessed by the mysql user
 
Solution <ref>[http://computerplumber.com/2009/01/using-the-chmod-command-effectively/ Using the CHMOD command effectively @ Computer Plumber]</ref>:
<pre>
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
</pre>
 
==== could not open single-table tablespace file filename.ibd ====
Version: XAMPP 5.6.15-1 on {{Mac}}
 
Condition: The Mac was shutdown accidentally and the database was not shutdown normally. After reboot the Mac, unable to start the MySQL service<ref>[http://stackoverflow.com/questions/35184367/error-could-not-open-single-table-tablespace-file-scrapers-records-ibd mysql - Error: could not open single-table tablespace file .\scrapers\records.ibd - Stack Overflow]</ref><ref>[http://chepri.com/our-blog/mysql-innodb-corruption-and-recovery/ MySQL Won't Start - InnoDB Corruption and Recovery. - Chepri]</ref>.
 
Possible solution:
# Edit the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
# Add this line: {{kbd | key=<nowiki>innodb_force_recovery = 1</nowiki>}}
# Try to start the MySQL service
# If the MySQL service started successfully, edit the MySQL configuration file and mark this line : {{kbd | key=<nowiki>#innodb_force_recovery = 1</nowiki>}}
# 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 <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!
 
==== 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 {{kbd | key=df -h}} on {{Linux}}
* 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
* 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].
 
==== 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<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:
<pre>
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'
</pre>
 
Solution:
* Check the permission of specified user name & database name
<pre>
$ mysql -u USER -p --host=127.0.0.1
Enter password:
 
mysql> use DATABASE_NAME
</pre>
 
==== 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. {{kbd | key=<nowiki>mysql -u root -p'PASSWORD'</nowiki>}}<ref>[http://superuser.com/questions/123928/escaping-a-password-using-mysqldump-console escape characters - Escaping a password using mysqldump console - Super User]</ref>
* 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'
 
Solution:
# check the column name 'xxx' if exists
# if the column name 'xxx' was computed by the [http://dev.mysql.com/doc/refman/5.7/en/example-user-variables.html User-Defined Variables]. Enclosed the whole query into another parent derived query.
<pre>
-- 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
</pre>
 
Enclosed the whole query into another parent derived query.
<pre>
SELECT final.*
FROM
(
        SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank` 
        FROM
        (
                ...
        ) semi
) final
WHERE final.rank <= 10
</pre>
 
==== ERROR 1690 - BIGINT UNSIGNED value is out of range ====
Message: MySQL error #1690 (BIGINT UNSIGNED value is out of range)
 
Solution <ref>[https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html MySQL :: MySQL 8.0 Reference Manual :: 11.2.6 Out-of-Range and Overflow Handling]</ref><ref>[https://stackoverflow.com/questions/34115917/mysql-error-1690-bigint-unsigned-value-is-out-of-range-for-unix-timestamp MySQL error #1690 (BIGINT UNSIGNED value is out of range) for UNIX_TIMESTAMP() - Stack Overflow]</ref>:
<pre>
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
</pre>
 
==== ERROR 1114 (HY000): The table `TABLE_NAME` is full ====
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>.
 
==== 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>
# {{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 [FULL] PROCESSLIST;</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.29 SHOW PROCESSLIST 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
 
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:
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';
</pre>
 
Solution:
(1) Check if the account was created or not
<pre>
SELECT User,Host FROM mysql.user;
</pre>
 
(2a) If the account was created, set the password for the account.
<pre>
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');
</pre>
 
(2b) If the account was NOT created, re-create the account.
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';
</pre>
 
references
* [http://dev.mysql.com/doc/refman/5.7/en/create-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.2 CREATE USER Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/set-password.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.7 SET PASSWORD 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:
<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>
 
==== ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (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.
 
==== ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out") ====
Solution:
* Check the permission of MySQL database user.
* Check the firewall rules. 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.
References:
* [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
 
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>
 
==== Navicat error:  [Exp] OLE error 800A03EC ====
error message:
<pre>
[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
</pre>
 
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 ====
[http://errerrors.blogspot.tw/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]


=== further reading ===
=== further reading ===
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/18971"

Navigation menu