Editing Troubleshooting of MySQL errors (section)

== Miscellaneous Tool-Specific Errors ==

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

=== 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:  [Exp] OLE error 800A03EC ===
error message:
<pre>
[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
</pre>

cause:
* Rows count of results exceed the limit of [[Microsoft Excel]] Worksheet size: 1,048,576 rows

solution:
* LIMIT the rows of MySQL query