Troubleshooting of MySQL errors: Difference between revisions
Jump to navigation
Jump to search
m
no edit summary
mNo edit summary |
|||
| Line 194: | Line 194: | ||
</pre> | </pre> | ||
== ERROR 1052 - Column 'column_name' in field list is ambiguous == | == ERROR 1052 - Column 'column_name' in field list is ambiguous == | ||
| Line 450: | Line 381: | ||
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000; | INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000; | ||
</pre> | </pre> | ||
== 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. {{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 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 {{kbd | key=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." <ref>[https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html MySQL :: MySQL 5.6 Reference Manual :: 14.22 InnoDB Limits]</ref> | |||
'''Solution'''<ref>[https://www.opencli.com/mysql/mysql-%E5%95%8F%E9%A1%8C-1071-42000-specified-key-was-too-long MySQL 問題: 1071 (42000): Specified key was too long]</ref><ref>[https://terrytongcc.com/%E5%A6%82%E4%BD%95%E8%A7%A3%E6%B1%BAmysql%E5%87%BA%E7%8F%BEerror-1118-row-size-too-large-8126%E5%95%8F%E9%A1%8C/ 如何解決MySQL出現Error 1118: row size too large (> 8126)問題 | Terry Tong – Full Stack Web Developer]</ref>: | |||
* execute SQL query as the following: | |||
<pre> | |||
SET GLOBAL innodb_file_format = Barracuda; | |||
SET GLOBAL innodb_file_format_max = Barracuda; | |||
</pre> | |||
* modify the MySQL configuration file | |||
<pre> | |||
innodb_file_per_table = 1 | |||
innodb-file-format = BARRACUDA | |||
innodb-large-prefix = ON | |||
innodb_file_format_max = BARRACUDA | |||
</pre> | |||
* restart the MySQL server | |||
* execute SQL query as the following: | |||
<pre> | |||
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED; | |||
</pre> | |||
== 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 {{kbd | key=set names utf8mb4}}<ref>[https://stackoverflow.com/questions/54947392/incorrect-string-value-when-trying-to-pass-emoji-to-the-db-encoded-with-utf8mb4 php - Incorrect string value when trying to pass emoji to the db encoded with utf8mb4 - Stack Overflow]</ref> | |||
* Check the configuration of table | |||
** CHARACTER SETS (aka 字元集、字符集): {{kbd | key=utf8mb4}} | |||
** COLLATION (aka 定序、字元序): {{kbd | key=utf8mb4_unicode_ci}} | |||
== Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied == | |||
Message: Error!: {{kbd | key=<nowiki>SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'user'@'localhost' (using password: YES)</nowiki>}} | |||
Solution: | |||
* If you executed the query{{kbd | key=INTO OUTFILE}}, you need to grant the file permission e.g. {{kbd | key=GRANT FILE ON *.* TO 'user'@'localhost';}}<ref>[https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file MySQL :: MySQL 5.7 Reference Manual :: 6.2.1 Privileges Provided by MySQL]</ref><ref>[https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777 - Stack Overflow]</ref>. | |||
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"<ref>[https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql database - How should I tackle --secure-file-priv in MySQL? - Stack Overflow]</ref> | |||
== Error!: SQLSTATE[42000]: Syntax error or access violation == | |||
Message: {{kbd | key=<nowiki>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 ...</nowiki>}} | |||
Solution: | |||
* [[SQL syntax debug]] | |||
* [https://www.eversql.com/sql-syntax-check-validator/ SQL Syntax Check Online, SQL Validator, Instant SQL Compiler Online – EverSQL] {{exclaim}} Not support the [https://www.php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] which the query syntax contains question marks. | |||
== Navicat error: [Exp] OLE error 800A03EC == | == Navicat error: [Exp] OLE error 800A03EC == | ||