Troubleshooting of MySQL errors: Difference between revisions

Jump to navigation Jump to search

Troubleshooting of MySQL errors (edit)

Revision as of 18:57, 12 May 2024

10,706 bytes removed ,  12 May 2024
m
→‎Handling Common MySQL Error Issues
Tag: Manual revert
Line 3: Line 3:
{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | EN]], [[排除 MySQL 技術疑難問題|漢字]]}}
{{LanguageSwitcher | content = [[Troubleshooting of MySQL errors | EN]], [[排除 MySQL 技術疑難問題|漢字]]}}


== Handling Common MySQL Error Issues ==
== Installation and Configuration Issues ==
=== command not found: mysqldump ===
=== command not found: mysqldump ===
# locate the mysqldump command
# locate the mysqldump command
Line 14: Line 14:
#* 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 ===
Version: XAMPP 5.6.15-1 on {{Mac}}
Cause:
* the error log only be accessed by the user named {{kbd | key=mysql}}<ref>Open the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
<pre>
# The MySQL server
[mysqld]
user = mysql
</pre></ref>
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
=== Could not start MySQL service on Windows ===
checklist
# [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
=== 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 ===
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!: SQLSTATE[HY000]: General error ===
Message: Error!: SQLSTATE[HY000]: General error
Condition: When I used the [http://php.net/manual/en/book.pdo.php PHP: PDO]
Solutions:
* "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>.
=== 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 ===
Message: {{kbd | key=<nowiki>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: ''.</nowiki>}}
Condition: When I import the sql file and I met the above error message.
Solution: Unzip the file and then import the file again <ref>[https://stackoverflow.com/questions/17158367/enable-binary-mode-while-restoring-a-database-from-an-sql-dump mysql - Enable binary mode while restoring a Database from an SQL dump - Stack Overflow]</ref>. The sql file is a compressed file. You may use [https://en.wikipedia.org/wiki/File_(command) file (command)] for recognizing the type of file.
<pre>
$ file compressed.sql
compressed.sql: gzip compressed data
$ file plain_text.sql
plain_text.sql: UTF-8 Unicode text, with very long lines
</pre>
=== Errcode: 13 Permission denied ===
The message occurred after executed {{kbd | key=<nowiki>mysqld.exe --datadir=..\data --console</nowiki>}} when I tried to start the service. (Version of MySQL：5.5.5-10.0.12-MariaDB on {{Win}})
<pre>
> 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
</pre>
Solutions:
Open the command line with administrative privileges. (How to: [https://www.howtogeek.com/194041/how-to-open-the-command-prompt-as-administrator-in-windows-8.1/ How to Open the Command Prompt as Administrator in Windows 8 or 10])
=== Error!: SQLSTATE[HY000]: General error: 3 Error writing file xxx.tmp (Errcode: 28 - No space left on device) ===
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 {{kbd | key=tmpdir}} folder
Solutions: Increase the free space of mysql {{kbd | key=tmpdir}} folder. Or change the mysql {{kbd | key=tmpdir}} folder with another hard disk drive contains more free space<ref>[https://stackoverflow.com/questions/11990887/changing-the-tmp-folder-of-mysql Changing the tmp folder of mysql - Stack Overflow]</ref>.
* Check the current mysql {{kbd | key=tmpdir}} folder. Query the syntax {{kbd | key=<nowiki>SHOW VARIABLES LIKE 'tmpdir';</nowiki>}}.
** On {{Win}} the default temporary folder<ref>[https://answers.microsoft.com/en-us/windows/forum/windows_7-windows_programs/where-is-the-temporary-folder/44a039a5-45ba-48dd-84db-fd700e54fd56 Where is the Temporary folder? - Microsoft Community]</ref> is {{kbd | key=<nowiki>%TMP%</nowiki>}} = {{kbd | key=<nowiki>%USERPROFILE%\AppData\Local\Temp</nowiki>}}.
** On {{Linux}} the default temporary folder maybe is {{kbd | key=<nowiki>/tmp</nowiki>}}
* Edit the [https://dev.mysql.com/doc/refman/5.7/en/option-files.html mysql configuration file]
<pre>
[mysqld]
tmpdir=X:/temp
</pre>
* Restart the MySQL service
Validation
* 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 ===
Message: '''Error dropping database (can't rmdir '.\TABLE_NAME', errno: 41)''' occurred when I executed '''DROP DATABASE `TABLE_NAME`''';
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) ==
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/24710"

Navigation menu