13,468
edits
MySQL commands: Difference between revisions
Jump to navigation
Jump to search
→Troubleshooting of MySQL
| Line 81: | Line 81: | ||
#* 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>}} | #* 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>}} | #* 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 for XAMPP on Mac ==== | |||
version: XAMPP 5.6.15-1 | |||
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> | |||
==== Err 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 combined 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> | |||
-- query which met he problem: [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> | |||
fixed with the parent 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 2002 (HY000): Can't connect to local MySQL server ==== | ==== ERROR 2002 (HY000): Can't connect to local MySQL server ==== | ||
| Line 115: | Line 162: | ||
solution: | solution: | ||
* LIMIT the rows of MySQL query | * LIMIT the rows of MySQL query | ||
=== further reading === | === further reading === | ||