Editing Troubleshooting of MySQL errors (section)

== Resource and Environmental Errors ==
=== 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


=== MySQL server has gone away ===
Error conditions:
* 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>

Solution:

* 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>.

=== 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]: 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.

=== 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]

=== How to resolve "zsh: operation not permitted: xxx.sql" ===
Error condition
<pre>
/applications/MAMP/library/bin/mysql -u [USERNAME] -p [DATABASE_NAME] < [PATH_TO_SQL_FILE]

zsh: operation not permitted: PATH_TO_SQL_FILE.sql
</pre>

Solution: Check Security & Privacy Settings: macOS may block certain actions from the terminal due to security settings. You can try allowing full disk access to the terminal:

* Open System Preferences.
* Go to Security & Privacy.
* Select the Privacy tab.
* Scroll down to Full Disk Access.
* Click the lock icon to make changes (you might need your administrator password).
* Find the Terminal app or iTerm2 or whatever terminal you are using and check the box to allow full disk access.
* Restart your terminal.

=== Resolve insufficient hard disk space where mysql data located ===
[http://errerrors.blogspot.com/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]

=== 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.

=== 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

Current condition<ref>[https://dev.mysql.com/doc/refman/8.0/en/show-variables.html MySQL :: MySQL 8.0 Reference Manual :: 13.7.6.39 SHOW VARIABLES Syntax]</ref>:
* Keywin {{kbd | key = <nowiki>SHOW VARIABLES LIKE 'innodb_buffer_pool_size';</nowiki>}}. If it returns {{kbd | key=8388608}}, it means {{kbd | key=8388608}} bytes ≅ 8MB.

Solution: 
* Increase {{kbd | key = innodb_buffer_pool_size}} e.g. {{kbd | key =<nowiki>SET GLOBAL innodb_buffer_pool_size=402653184;</nowiki>}} (402653184 bytes ~ 400MB. Default value is 8MB.)
* Reduce the size of query data

Further reading:
* [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].
* [https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.2 Configuring InnoDB Buffer Pool Size]

=== 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>

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 1114 (HY000): The table `TABLE_NAME` is full ===
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>.