Troubleshooting of MySQL errors: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
mNo edit summary
 
(28 intermediate revisions by the same user not shown)
Line 1: Line 1:
Troubleshooting of MySQL errors
Troubleshooting of MySQL errors
{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | EN]], [[排除 MySQL 技術疑難問題|漢字]]}}
== command not found: mysqldump ==
== command not found: mysqldump ==
# locate the mysqldump command
# locate the mysqldump command
#* {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}}
#* Key-in the command {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}}
#* If you installed  
#* 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] 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.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>}}
#** [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
# 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>}}
#* 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>}}
Line 50: Line 53:
# [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] was allowed to written by {{kbd | key=NETWORK SERVICE}} on Windows Server 2008<ref>[https://errerrors.blogspot.com/2016/12/mysql-windows-server-2008-r2.html 如何修改 MySQL 伺服器的資料目錄 (Windows Server 2008 R2)]</ref>
# [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] was allowed to written by {{kbd | key=NETWORK SERVICE}} on Windows Server 2008<ref>[https://errerrors.blogspot.com/2016/12/mysql-windows-server-2008-r2.html 如何修改 MySQL 伺服器的資料目錄 (Windows Server 2008 R2)]</ref>
# Directory {{kbd | key=secure-file-priv}} was allowed to written by {{kbd | key=NETWORK SERVICE}} on Windows Server 2008
# Directory {{kbd | key=secure-file-priv}} was allowed to written by {{kbd | key=NETWORK SERVICE}} on Windows Server 2008
== MySQL server has gone away ==
steps
* Enable the option {{kbd | key = log_error}} in MySQL config file e.g. {{kbd | key = <nowiki>log_error="file_name_of_error_log"</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/error-log.html MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log]</ref>.
* Example error log located at {{kbd | key = <nowiki>file_name_of_error_log</nowiki>}} are as following:
<pre>
2019-05-23T08:52:19.989876Z 99980 [Note] Aborted connection 99980 to db: 'DB_NAME' user: 'DB_USER' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
</pre>
* Increase the value of {{kbd | key = <nowiki>max_allowed_packet</nowiki>}}<ref>[https://stackoverflow.com/questions/5688403/how-to-check-and-set-max-allowed-packet-mysql-variable php - how to check and set max_allowed_packet mysql variable - Stack Overflow]</ref> if the MySQL user has the {{kbd | key = <nowiki>SUPER</nowiki>}} privilege<ref>[https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL]</ref>.


== Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined ==
== Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined ==
Line 64: Line 79:
* "You do not use {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} with update or insert queries" <ref>[https://stackoverflow.com/questions/12979510/pdo-error-sqlstatehy000-general-error-when-updating-database php - PDO error: " SQLSTATE[HY000]: General error " When updating database - Stack Overflow]</ref>.
* "You do not use {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} with update or insert queries" <ref>[https://stackoverflow.com/questions/12979510/pdo-error-sqlstatehy000-general-error-when-updating-database php - PDO error: " SQLSTATE[HY000]: General error " When updating database - Stack Overflow]</ref>.
* You do not use {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} in the query {{kbd | key=<nowiki>INTO OUTFILE ...</nowiki>}} <ref>[http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/ Save MySQL query results into a text or CSV file]</ref><ref>[https://stackoverflow.com/questions/13369164/cant-create-write-to-file-errcode-22 mysql - Can't create/write to file (Errcode: 22) - Stack Overflow]</ref>.
* You do not use {{kbd | key=<nowiki>$result = $stmt->fetchAll();</nowiki>}} in the query {{kbd | key=<nowiki>INTO OUTFILE ...</nowiki>}} <ref>[http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/ Save MySQL query results into a text or CSV file]</ref><ref>[https://stackoverflow.com/questions/13369164/cant-create-write-to-file-errcode-22 mysql - Can't create/write to file (Errcode: 22) - Stack Overflow]</ref>.
== Error: SQLSTATE[HY000] [2002] No such file or directory ==
Error message: SQLSTATE[HY000] [2002] No such file or directory
Solutions:
* Modify the host of database from {{kbd | key=localhost}} to {{kbd | key=127.0.0.1}}


== ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode ==
== ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode ==
Line 130: Line 151:
* Query the syntax {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}} to validate the modification of mysql configuration file.
* Query the syntax {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}} to validate the modification of mysql configuration file.


== mysqldump: Got errno 32 on write ==
Error condition
<pre>
$ mysqldump -h localhost -u root -p --force --single-transaction --default-character-set=utf8 --quick mytable  | pv | gzip -c > mytable.sql.gz
</pre>
met the error message:
<pre>
-bash: pv: command not found
...
mysqldump: Got errno 32 on write
</pre>
Root cause
* The pv was not installed
* Input the command to check if pv was installed {{kbd | key=which pv}}
* install pv by input the command: {{kbd | key=sudo yum -y install pv}} <ref>[https://installati.one/centos/7/pv/ How To Install pv on CentOS 7 | Installati.one]</ref>
References
* [https://mysqldump.guru/mysqldump-got-errno-32-on-write.html mysqldump: Got errno 32 on write | mysqldump.guru]


== errno 41 - Error dropping database ==
== errno 41 - Error dropping database ==
Line 135: Line 178:


Solution: [https://stackoverflow.com/questions/17947255/error-in-dropping-a-database-in-mysql-cant-rmdir-oro-errno-41/19888293 phpmyadmin - Error in dropping a database in MySQL (can't rmdir '.\oro', errno: 41) - Stack Overflow]
Solution: [https://stackoverflow.com/questions/17947255/error-in-dropping-a-database-in-mysql-cant-rmdir-oro-errno-41/19888293 phpmyadmin - Error in dropping a database in MySQL (can't rmdir '.\oro', errno: 41) - Stack Overflow]


== ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28) ==
== ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28) ==
Line 182: Line 224:
</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!: 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.


== ERROR 1052 - Column 'column_name' in field list is ambiguous ==
== ERROR 1052 - Column 'column_name' in field list is ambiguous ==
Line 247: Line 265:
Possible solution
Possible solution
* Because the hard disk of partition where [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] located 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 hard disk of partition where [https://dev.mysql.com/doc/refman/5.7/en/data-directory.html MySQL Data Directory] located 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 1170: BLOB/TEXT column 'url' used in key specification without a key length ==
Condition
* SQL syntax when tried to create the new table
<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `url` (`url`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>
Solution: BLOB/TEXT column 'url' used in key specification with a key length e.g. {{kbd | key=`url`(500)}}
<pre>
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`(500))
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
</pre>


== ERROR 1205: Lock wait timeout exceeded; try restarting transaction ==
== ERROR 1205: Lock wait timeout exceeded; try restarting transaction ==
Line 253: Line 307:
# {{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>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>
# {{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>
Related article
* [https://errerrors.blogspot.com/2022/10/how-to-fix-lock-wait-timeout-exceeded-try-restarting-transaction-on-mysql.html 解決 MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction]


== ERROR 1206: The total number of locks exceeds the lock table size ==
== ERROR 1206: The total number of locks exceeds the lock table size ==
Line 280: Line 337:


Solution: {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR))</nowiki>}} or {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))</nowiki>}}<ref>[https://stackoverflow.com/questions/15368753/cast-int-to-varchar mysql - Cast int to varchar - Stack Overflow]</ref><ref>[http://blog.51cto.com/bian5399/1092772 mysql字符集问题:Illegal mix of collations-每天让自己进步一点-51CTO博客]</ref>
Solution: {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR))</nowiki>}} or {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))</nowiki>}}<ref>[https://stackoverflow.com/questions/15368753/cast-int-to-varchar mysql - Cast int to varchar - Stack Overflow]</ref><ref>[http://blog.51cto.com/bian5399/1092772 mysql字符集问题:Illegal mix of collations-每天让自己进步一点-51CTO博客]</ref>
== ERROR 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist ==
Message: MySQL error 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist
Solution: Fix the typo in the function name


== ERROR 1690 - BIGINT UNSIGNED value is out of range ==
== ERROR 1690 - BIGINT UNSIGNED value is out of range ==
Line 341: Line 403:
</pre>
</pre>
* If still not work, reboot the server and restart the MySQL service.
* If still not work, reboot the server and restart the MySQL service.
== ERROR 2002: SQLSTATE[HY000] [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted ==
Message: (1) [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted (2) "SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。" in Chinese
Solution: [https://stackoverflow.com/questions/10317974/mysql-php-error2002-only-one-usage-of-each-socket-address-protocol-network-a MySQL/PHP Error:(2002) Only one usage of each socket address (protocol/network address/port) is normally permitted - Stack Overflow]


== ERROR 2003 (HY000): Can't connect to MySQL server on 'ip' ==
== ERROR 2003 (HY000): Can't connect to MySQL server on 'ip' ==
Line 385: Line 455:
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 ==
Line 402: Line 543:
* LIMIT the rows of MySQL query
* LIMIT the rows of MySQL query


== resolve insufficient hard disk space where mysql data located ==
== Navicat error: Import data mismatch ==
Error:
 
When importing an Excel file into a MySQL database using Navicat, there is an issue with the total number of records not matching. Solution: Delete blank rows and check for any duplicate primary key data.
 
Solution:
* [https://errerrors.blogspot.com/2023/11/blog-post.html 解決 NaviCat 匯入 Excel 檔案到資料庫,出現資料總筆數不一致的問題] (written in Mandarin)
 
 
== Navicat error: Field doesn't have a default value ==
<pre>
[ERR] 1364 - Field 'source' doesn't have a default value
</pre>
 
Solution:
* When importing from Excel to a database, if the 'source' field is empty or not specified, it's necessary to assign a value to the 'source' field. Alternatively, modify the field definition to allow the field value to be NULL.
 
== PHP Fatal Error: Allowed Memory Size Exhausted when import SQL.GZ file using adminer ==
 
solution: Using the naive mysqldump command to generate the backup file. And import the backup by using mysql command.
 
== Resolve insufficient hard disk space where mysql data located ==
[http://errerrors.blogspot.com/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]
[http://errerrors.blogspot.com/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]


Line 411: Line 573:


[[Category:MySQL]]
[[Category:MySQL]]
[[Category:Database]]
[[Category:Data Science]]
[[Category:Data Science]]

Latest revision as of 19:57, 13 January 2024

Troubleshooting of MySQL errors

🌐 Switch language: EN, 漢字


command not found: mysqldump[edit]

  1. locate the mysqldump command
    • Key-in the 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[edit]

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[edit]

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


Could not start MySQL service on Windows[edit]

checklist

  1. MySQL Data Directory was allowed to written by NETWORK SERVICE on Windows Server 2008[5]
  2. Directory secure-file-priv was allowed to written by NETWORK SERVICE on Windows Server 2008


MySQL server has gone away[edit]

steps

  • Enable the option log_error in MySQL config file e.g. log_error="file_name_of_error_log"[6].
  • Example error log located at file_name_of_error_log are as following:
2019-05-23T08:52:19.989876Z 99980 [Note] Aborted connection 99980 to db: 'DB_NAME' user: 'DB_USER' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
  • Increase the value of max_allowed_packet[7] if the MySQL user has the SUPER privilege[8].

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

Solutions

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

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

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" [10].
  • You do not use $result = $stmt->fetchAll(); in the query INTO OUTFILE ... [11][12].

Error: SQLSTATE[HY000] [2002] No such file or directory[edit]

Error message: SQLSTATE[HY000] [2002] No such file or directory

Solutions:

  • Modify the host of database from localhost to 127.0.0.1

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode[edit]

Message: ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.

Condition: When I import the sql file and I met the above error message.

Solution: Unzip the file and then import the file again [13]. The sql file is a compressed file. You may use file (command) for recognizing the type of file.

$ file compressed.sql
compressed.sql: gzip compressed data

$ file plain_text.sql
plain_text.sql: UTF-8 Unicode text, with very long lines

Errcode: 13 Permission denied[edit]

The message occurred after executed mysqld.exe --datadir=..\data --console when I tried to start the service. (Version of MySQL:5.5.5-10.0.12-MariaDB on Win Os windows.png )

> mysqld.exe --datadir=..\data --console
180430 10:33:38 [ERROR] mysqld.exe: File 'C:\MariaDB_10.0\bin\..\data\aria_log_control' not found (Errcode: 13 "Permission denied")
180430 10:33:38 [ERROR] mysqld.exe: Got error 'Can't open file' when trying to use aria control file 'C:\MariaDB_10.0\bin\..\data\aria_log_control'
180430 10:33:38 [ERROR] Plugin 'Aria' init function returned error.
180430 10:33:38 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
180430 10:33:38 [Note] InnoDB: Using mutexes to ref count buffer pool pages
180430 10:33:38 [Note] InnoDB: The InnoDB memory heap is disabled
180430 10:33:38 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
180430 10:33:38 [Note] InnoDB: Compressed tables use zlib 1.2.3
180430 10:33:38 [Note] InnoDB: Not using CPU crc32 instructions
180430 10:33:38 [Note] InnoDB: Initializing buffer pool, size = 4.0G
180430 10:33:38 [Note] InnoDB: Completed initialization of buffer pool
180430 10:33:38 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
180430 10:33:38 [ERROR] InnoDB: The system tablespace must be writable!
180430 10:33:38 [ERROR] Plugin 'InnoDB' init function returned error.
180430 10:33:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180430 10:33:38 [Note] Plugin 'FEEDBACK' is disabled.
180430 10:33:38 [ERROR] Unknown/unsupported storage engine: innodb
180430 10:33:38 [ERROR] Aborting

180430 10:33:38 [Note] mysqld.exe: Shutdown complete

Solutions:

Open the command line with administrative privileges. (How to: How to Open the Command Prompt as Administrator in Windows 8 or 10)

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

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

  • Check the current mysql tmpdir folder. Query the syntax SHOW VARIABLES LIKE 'tmpdir';.
    • On Win Os windows.png the default temporary folder[15] 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.


mysqldump: Got errno 32 on write[edit]

Error condition

$ mysqldump -h localhost -u root -p --force --single-transaction --default-character-set=utf8 --quick mytable  | pv | gzip -c > mytable.sql.gz

met the error message:

-bash: pv: command not found
...
mysqldump: Got errno 32 on write

Root cause

  • The pv was not installed
  • Input the command to check if pv was installed which pv
  • install pv by input the command: sudo yum -y install pv [16]


References

errno 41 - Error dropping database[edit]

Message: Error dropping database (can't rmdir '.\TABLE_NAME', errno: 41) occurred when I executed DROP DATABASE `TABLE_NAME`;

Solution: phpmyadmin - Error in dropping a database in MySQL (can't rmdir '.\oro', errno: 41) - Stack Overflow

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

Solutions

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

Solutions

ERROR 1017 - Can't find file: '.\DATABASE\TABLE.frm' (errno: 22 - Invalid argument)[edit]

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[17][18]. unverified

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

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

pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 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

Message: mysqldump: Got error: 1044: Access denied for user 'USER'@'localhost' to database 'DATABASE_NAME' when doing LOCK TABLES

$ mysqldump -h 127.0.0.1 -u USER -p DATABASE_NAME TABLE_NAME > TABLE_NAME.sql

Solution:

  • Add the mysqldump option --skip-lock-tables if you cannot grant the user permissions [19].
$ mysqldump -h 127.0.0.1 -u USER -p --skip-lock-tables DATABASE_NAME TABLE_NAME > TABLE_NAME.sql


ERROR 1052 - Column 'column_name' in field list is ambiguous[edit]

Message: Error Code: 1052. Column 'column_name' in field list is ambiguous

Cause: Since 'column_name' is present in 2 or more tables ...[20]

Solution: Remain only one table name 'column_name' OR adding the table name alias.

ERROR 1054 - Unknown column in 'where clause'[edit]

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[edit]

Possible solution

  • Because the hard disk of partition where MySQL Data Directory located is full or almost full, free some hard disk space[21].


ERROR 1170: BLOB/TEXT column 'url' used in key specification without a key length[edit]

Condition

  • SQL syntax when tried to create the new table
DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `url` (`url`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

Solution: BLOB/TEXT column 'url' used in key specification with a key length e.g. `url`(500)

DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(2) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`(500))
) ENGINE=InnoDB AUTO_INCREMENT=3573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

ERROR 1205: Lock wait timeout exceeded; try restarting transaction[edit]

Solution: [22]

  1. SHOW OPEN TABLES WHERE in_use > 0;[23]
  2. SHOW [FULL] PROCESSLIST;[24]
  3. KILL <process id>;[25]

Related article

ERROR 1206: The total number of locks exceeds the lock table size[edit]

Message: Error Code: 1206. The total number of locks exceeds the lock table size

Current condition[26]:

  • Keywin SHOW VARIABLES LIKE 'innodb_buffer_pool_size';. If it returns 8388608, it means 8388608 bytes ≅ 8MB.

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'[edit]

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'[edit]

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

ERROR 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist[edit]

Message: MySQL error 1305 - FUNCTION MY_TABLE.MY_FUNCTION does not exist

Solution: Fix the typo in the function name

ERROR 1690 - BIGINT UNSIGNED value is out of range[edit]

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

Solution [29][30]:

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

ERROR 1813: Tablespace for table xxx exists[edit]

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.[edit]

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: Cannot connect: SQLSTATE[HY000] [2002][edit]

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
  • If still not work, reboot the server and restart the MySQL service.


ERROR 2002: SQLSTATE[HY000] [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted[edit]

Message: (1) [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted (2) "SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。" in Chinese


Solution: MySQL/PHP Error:(2002) Only one usage of each socket address (protocol/network address/port) is normally permitted - Stack Overflow

ERROR 2003 (HY000): Can't connect to MySQL server on 'ip'[edit]

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[31]. 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[edit]

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 [32][33]. 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;

ERROR 1045 (28000): Access denied for user[edit]

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

ERROR 1049 (42000): Unknown database[edit]

Message: ERROR 1049 (42000): Unknown database 'MY_DATABASE_p'

Solution:

  • Check the database 'MY_DATABASE_p' is exists
  • Check there are no 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[edit]

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." [35]

Solution[36][37]:

  • execute SQL query as the following:
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;
  • modify the MySQL configuration file
innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA
  • restart the MySQL server
  • execute SQL query as the following:
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;

ERROR 1366: Incorrect string value[edit]

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 set names utf8mb4[38]
  • Check the configuration of table
    • CHARACTER SETS (aka 字元集、字符集): utf8mb4
    • COLLATION (aka 定序、字元序): utf8mb4_unicode_ci

Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied[edit]

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';[39][40].


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

Error!: SQLSTATE[42000]: Syntax error or access violation[edit]

Message: 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 ...

Solution:


Navicat error: [Exp] OLE error 800A03EC[edit]

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

Navicat error: Import data mismatch[edit]

Error:

When importing an Excel file into a MySQL database using Navicat, there is an issue with the total number of records not matching. Solution: Delete blank rows and check for any duplicate primary key data.

Solution:


Navicat error: Field doesn't have a default value[edit]

[ERR] 1364 - Field 'source' doesn't have a default value

Solution:

  • When importing from Excel to a database, if the 'source' field is empty or not specified, it's necessary to assign a value to the 'source' field. Alternatively, modify the field definition to allow the field value to be NULL.

PHP Fatal Error: Allowed Memory Size Exhausted when import SQL.GZ file using adminer[edit]

solution: Using the naive mysqldump command to generate the backup file. And import the backup by using mysql command.

Resolve insufficient hard disk space where mysql data located[edit]

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

References[edit]

  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. 如何修改 MySQL 伺服器的資料目錄 (Windows Server 2008 R2)
  6. MySQL :: MySQL 8.0 Reference Manual :: 5.4.2 The Error Log
  7. php - how to check and set max_allowed_packet mysql variable - Stack Overflow
  8. MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL
  9. php Invalid parameter number: parameter was not defined - Stack Overflow
  10. php - PDO error: " SQLSTATE[HY000: General error " When updating database - Stack Overflow]
  11. Save MySQL query results into a text or CSV file
  12. mysql - Can't create/write to file (Errcode: 22) - Stack Overflow
  13. mysql - Enable binary mode while restoring a Database from an SQL dump - Stack Overflow
  14. Changing the tmp folder of mysql - Stack Overflow
  15. Where is the Temporary folder? - Microsoft Community
  16. How To Install pv on CentOS 7 | Installati.one
  17. MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there - Stack Overflow
  18. ERROR 1017 (HY000): Can't find file - Percona Community
  19. mysql - skip-lock-tables and mysqldump - Stack Overflow
  20. mysql - Error Code: 1052 Column 'admin_id' in field list is ambiguous - Stack Overflow
  21. mysql - ERROR 1114 (HY000): The table is full - Stack Overflow
  22. mysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow
  23. MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.24 SHOW OPEN TABLES Syntax
  24. MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.29 SHOW PROCESSLIST Syntax
  25. MySQL :: MySQL 5.7 Reference Manual :: 13.7.6.4 KILL Syntax
  26. MySQL :: MySQL 8.0 Reference Manual :: 13.7.6.39 SHOW VARIABLES Syntax
  27. mysql - Cast int to varchar - Stack Overflow
  28. mysql字符集问题:Illegal mix of collations-每天让自己进步一点-51CTO博客
  29. MySQL :: MySQL 8.0 Reference Manual :: 11.2.6 Out-of-Range and Overflow Handling
  30. MySQL error #1690 (BIGINT UNSIGNED value is out of range) for UNIX_TIMESTAMP() - Stack Overflow
  31. HowTo: Find out If MySQL Is Running On Linux Or Not
  32. How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow
  33. Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow
  34. escape characters - Escaping a password using mysqldump console - Super User
  35. MySQL :: MySQL 5.6 Reference Manual :: 14.22 InnoDB Limits
  36. MySQL 問題: 1071 (42000): Specified key was too long
  37. 如何解決MySQL出現Error 1118: row size too large (> 8126)問題 | Terry Tong – Full Stack Web Developer
  38. php - Incorrect string value when trying to pass emoji to the db encoded with utf8mb4 - Stack Overflow
  39. MySQL :: MySQL 5.7 Reference Manual :: 6.2.1 Privileges Provided by MySQL
  40. MYSQL into outfile "access denied" - but my user has "ALL" access.. and the folder is CHMOD 777 - Stack Overflow
  41. database - How should I tackle --secure-file-priv in MySQL? - Stack Overflow


Troubleshooting of ...

Template