MySQL commands: Difference between revisions

Jump to navigation Jump to search

MySQL commands (edit)

Revision as of 10:34, 10 July 2023

5,279 bytes removed ,  10 July 2023
→‎Preparation
mNo edit summary
 
(57 intermediate revisions by the same user not shown)
Line 1: Line 1:
MySQL commands and troubleshooting of MySQL errors.


=== Preparation ===
=== Preparation ===
Line 4: Line 5:
* {{Linux}}:  
* {{Linux}}:  
* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package
* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package
* {{Mac}}: Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd | key=mysql}} executable file is: /Applications/xampp/xamppfiles/bin/mysql
* {{Mac}}:  
** Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd | key=mysql}} executable file is: {{kbd | key=/Applications/xampp/xamppfiles/bin/mysql}}
** MAMP: path of mysql command: {{kbd | key=/Applications/MAMP/Library/bin/mysql}}<ref>[https://dev.to/arbaoui_mehdi/how-to-access-the-mysql-cli-with-mamp-25m How to access the MySQL CLI With MAMP - DEV Community]</ref>


(optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package
(optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package
Line 19: Line 22:


=== Exporting data of database/table  into MySql sql file ===
=== Exporting data of database/table  into MySql sql file ===
==== mysqldump Options ====
Default option without any options specified
* {{kbd | key=<nowiki>drop table</nowiki>}} & insert new data
Replace (overwrite) the current data
* Run {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--replace --no-create-info</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_replace --replace]</ref><ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_no-create-info --no-create-info]</ref>: (1) {{kbd | key=<nowiki>--replace</nowiki>}}: "Write REPLACE statements rather than INSERT statements." (2) {{kbd | key=<nowiki>--no-create-info</nowiki>}}: "Do not write CREATE TABLE statements that create each dumped table." & "Do not add a DROP TABLE statement before each CREATE TABLE statement"
Append the current data
* Run {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--insert-ignore --no-create-info</nowiki>}}<ref>[https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_insert-ignore --insert-ignore]</ref>: (1) {{kbd | key=<nowiki>mysqldump</nowiki>}} with the option {{kbd | key=<nowiki>--insert-ignore</nowiki>}}:  "Write INSERT IGNORE statements rather than INSERT statements."
Schema only
* [https://www.electrictoolbox.com/mysqldump-schema-only/ Use mysqldump to get the schema only - Electric Toolbox]
==== Exporting entire data ====
==== Exporting entire data ====
'''method1''': {{Gd}} export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref><ref>[https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]</ref><ref>[http://stackoverflow.com/questions/5666784/how-can-i-slow-down-a-mysql-dump-as-to-not-affect-current-load-on-the-server backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow]</ref>:  
'''method1''': {{Gd}} export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref><ref>[https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]</ref><ref>[http://stackoverflow.com/questions/5666784/how-can-i-slow-down-a-mysql-dump-as-to-not-affect-current-load-on-the-server backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow]</ref>:  
Line 49: Line 65:
[http://stackoverflow.com/questions/17397661/append-mysql-dump-to-a-table mysqldump - Append MYSQL dump to a table - Stack Overflow] {{access | date = 2016-06-29}}
[http://stackoverflow.com/questions/17397661/append-mysql-dump-to-a-table mysqldump - Append MYSQL dump to a table - Stack Overflow] {{access | date = 2016-06-29}}


==== Potential failure issue ====
* insufficient hard-disk space


=== Importing data from MySql sql file ===
=== Importing data from MySql sql file ===
{{exclaim}} Notice: existing database will be overwritten
{{exclaim}} Notice: existing database will be overwritten
Optional
# (optional) install {{kbd | key =pv}} if the {{kbd | key =pv}} was not installed for RHEL / CentOS / SL / Fedora Linux or {{kbd | key =brew install pv}} for {{Mac}}<ref>[https://formulae.brew.sh/formula/pv pv — Homebrew Formulae]</ref>
# (optional if the 7Zip was not installed) {{kbd | key =yum install p7zip}} for RHEL / CentOS / SL / Fedora Linux


'''method 1''': {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:  
'''method 1''': {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:  
# (optional) install {{kbd | key =pv}} if the {{kbd | key =pv}} was not installed for RHEL / CentOS / SL / Fedora Linux
# Install {{kbd | key =pv}}
# Check if the file is gzip format (test the compressed file integrity) ex: (1) {{kbd | key =  <nowiki>gunzip -t database.sql.gz</nowiki>}} If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using [http://www.winmd5.com/ WinMD5 Free] for {{Win}} or [http://linux.die.net/man/1/md5sum md5sum] command for {{Linux}} to obtain MD5 checksum of files.
# Check if the file is gzip format (test the compressed file integrity) ex: (1) {{kbd | key =  <nowiki>gunzip -t database.sql.gz</nowiki>}} If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using [http://www.winmd5.com/ WinMD5 Free] for {{Win}} or [http://linux.die.net/man/1/md5sum md5sum] command for {{Linux}} to obtain MD5 checksum of files.
# {{kbd | key =  <nowiki> pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# {{kbd | key =  <nowiki> pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
Line 65: Line 88:


'''method 3''': Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref>:  
'''method 3''': Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref>:  
# (optional if the {{kbd | key =pv}} was not installed) {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux
# Install {{kbd | key =pv}}
# {{kbd | key =  <nowiki>pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/ Import MySQL Dumpfile, SQL Datafile Into My Database]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# {{kbd | key =  <nowiki>pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/ Import MySQL Dumpfile, SQL Datafile Into My Database]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival)
# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival)


'''method 4''': Import ZIP file to MySql directly:
'''method 4''': Import ZIP file to MySql directly:
# (optional if the 7Zip was not installed) {{kbd | key =yum install p7zip}}
# Install the 7Zip
# {{kbd | key = <nowiki>7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://stackoverflow.com/questions/11267309/importing-zipped-files-in-mysql-using-command-line database - Importing zipped files in Mysql using command line - Stack Overflow]</ref><ref>[http://needs-be.blogspot.tw/2011/01/install-7zip-on-centos-55.html Install 7Zip on CentOS 5.5]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# {{kbd | key = <nowiki>7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://stackoverflow.com/questions/11267309/importing-zipped-files-in-mysql-using-command-line database - Importing zipped files in Mysql using command line - Stack Overflow]</ref><ref>[http://needs-be.blogspot.tw/2011/01/install-7zip-on-centos-55.html Install 7Zip on CentOS 5.5]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
Line 76: Line 99:
=== Synchronizing databases ===
=== Synchronizing databases ===
Synchronizing two MySQL databases:
Synchronizing two MySQL databases:
* [http://www.heidisql.com/ HeidiSQL - MySQL, MSSQL and PostgreSQL made easy] v.9.4.0.5125 for {{Win}}, {{Mac}} & {{Linux}}: Manually synchronizing databases between two different servers. {{exclaim}} No schedules.
* [http://www.heidisql.com/ HeidiSQL - MySQL, MSSQL and PostgreSQL made easy] v.9.4.0.5125 for {{Win}}, {{Mac}} & {{Linux}}
** Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection
** Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection
** Scheduler: Not available<ref>[http://www.heidisql.com/forum.php?t=22040 Schedule backups/exports]</ref> {{exclaim}}. Manually synchronizing databases between two different servers.
* [https://dev.mysql.com/doc/refman/5.7/en/replication.html Replication]
* [https://dev.mysql.com/doc/refman/5.7/en/replication.html Replication]
** Steps:
** Scheduler: Auto {{Gd}}.
* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] v.12.4.0 for {{Win}}.
** Steps: Menu --> Powertools --> Database Synchronization Wizard
** Scheduler: Available {{Gd}}. Save the job file & Schedule it using Windows scheduler.
* ''$'' [https://www.devart.com/dbforge/mysql/studio/ MySQL GUI Tool - MariaDB and MySQL Front End Client for Windows]
** Steps:
*** Menu: Comparison -> New Data Comparison -> Choose the connection: Next ->
*** Mapping: Select objects that you want to compare ->
*** After hours or minutes ...
*** Menu: Comparison -> Synchronize -> Open the synchronization script in the internal editor -> Synchronize
*** Menu: SQL -> Execute
** Scheduler:


Transfer date from MS SQL server to MySQL server:  
Transfer date from MS SQL server to MySQL or MariaDB server<ref>[https://mariadb.com/kb/en/library/monyog-sqlyog/ MONyog & SQLyog - MariaDB Knowledge Base]</ref>:  
* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] for {{Win}}. Use a query to specify the data to transfer from MS SQL server into MySQL server.
* ''$'' [https://www.webyog.com/product/sqlyog SQLyog] for {{Win}}. Use a query to specify the data to transfer from MS SQL server into MySQL server.


Line 90: Line 128:
#* {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list
#* {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list
#* {{kbd | key=SHOW PROCESSLIST\G}} to show the brief process list
#* {{kbd | key=SHOW PROCESSLIST\G}} to show the brief process list
# mysql> {{kbd | key=kill 101;}} to kill the process with Id number: 101
# mysql> {{kbd | key=kill 101;}} to kill the process with Id number: {{kbd | key=101}}
# mysql> {{kbd | key=exit;}} to leave the MySQL command.
# mysql> {{kbd | key=exit;}} to leave the MySQL command.


=== Troubleshooting of MySQL ===
=== Troubleshooting of MySQL errors ===
==== command not found: mysqldump ====
[[Troubleshooting of MySQL errors]]
# locate the mysqldump command
#* {{kbd | key=<nowiki>sudo find / -iname mysqldump</nowiki>}} for {{Mac}} or {{Linux}}
#* And find {{kbd | key=<nowiki>/Applications/XAMPP/xamppfiles/bin/mysqldump</nowiki>}} from [https://www.apachefriends.org/index.html XAMPP] for {{Mac}}
# 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>}}
#* 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 mysql user
 
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
 
==== ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28) ====
 
Solutions
* Check if the disk space where mysql data folder located is enough. e.g. Input {{kbd | key=df -h}} on {{Linux}}
* More on [http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 mysql - ERROR 1005 (HY000): Can't create table (errno: 150) - Stack Overflow].
 
==== ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28) ====
Solutions
* Check if the disk space where mysql data folder located is enough. e.g. Input {{kbd | key=df -h}} on {{Linux}}
* More on [http://stackoverflow.com/questions/18719748/error-1006-hy000-cant-create-database-errno-13-mysql-5-6-12 ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow].
 
==== ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES) ====
Solution:
* Check the typo of user name.
* Check the typo of password.
* 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>
 
 
==== Err 1054 - Unknown column in 'where clause' ====
Message: [Err] 1054 - Unknown column 'xxx' in 'where clause'
 
Solution:
# check the column name 'xxx' if exists
# if the column name 'xxx' was computed by the [http://dev.mysql.com/doc/refman/5.7/en/example-user-variables.html User-Defined Variables]. Enclosed the whole query into another parent derived query.
<pre>
-- 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
</pre>
 
Enclosed the whole query into another parent derived query.
<pre>
SELECT final.*
FROM
(
        SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank` 
        FROM
        (
                ...
        ) semi
) final
WHERE final.rank <= 10
</pre>
 
==== 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. ====
Wrong sql query as follows:
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';
</pre>
 
Solution:
(1) Check if the account was created or not
<pre>
SELECT User,Host FROM mysql.user;
</pre>
 
(2a) If the account was created, set the password for the account.
<pre>
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');
</pre>
 
(2b) If the account was NOT created, re-create the account.
<pre>
CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';
</pre>
 
references
* [http://dev.mysql.com/doc/refman/5.7/en/create-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.2 CREATE USER Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/set-password.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.7 SET PASSWORD Syntax]
* [http://dev.mysql.com/doc/refman/5.7/en/drop-user.html MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax]
 
==== ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' ====
Condition on Cygwin terminal of Windows:
<pre>
$ 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)
</pre>
 
Solution:
* Change {{kbd | key=<nowiki>-h localhost</nowiki>}} to {{kbd | key=<nowiki>-h 127.0.0.1</nowiki>}}
<pre>
$ mysql -h 127.0.0.1 -u root -p
</pre>
 
==== ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) ====
 
Solution:
* Check if the MySQL service is running or not. If not, start the MySQL service.
 
==== ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out") ====
 
Solution:
* Check the firewall rules. More on [https://devops.profitbricks.com/tutorials/install-mysql-on-centos-7/#firewall-rules Install MySQL on CentOS 7 | ProfitBricks DevOps Central] & [https://support.rackspace.com/how-to/mysql-connect-to-your-database-remotely/ Connect to a MySQL database remotely]
 
==== 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'.
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;
</pre>
 
Solution:
* Increase the setting of '''DBMS connection read time out (in seconds)''' on [https://www.mysql.com/products/workbench/ MySQL Workbench]. <ref>[http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer-99-999-seconds-on-mysql-workbench How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow]</ref><ref>[http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow]</ref>
* Reduce the number of rows to reduce the execution time (1) by using {{kbd | key=LIMIT}} clause (2) or by splitting the query size e.g. {{kbd | key=<nowiki>MOD(column, 2) = 0</nowiki>}} & {{kbd | key=<nowiki>MOD(column, 2) > 0</nowiki>}} if the column is numeric.
<pre>
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;
</pre>
 
==== 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:
* exceeds the [[Microsoft Excel]] Worksheet size: 1,048,576 rows
 
solution:
* LIMIT the rows of MySQL query
 
==== resolve insufficient hard disk space where mysql data located ====
[http://errerrors.blogspot.tw/2016/07/mysql.html Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況]
 
 


=== further reading ===
=== Further reading ===
* [https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]
* [http://dev.mysql.com/doc/refman/5.7/en/error-handling.html MySQL :: MySQL 5.7 Reference Manual :: B Errors, Error Codes, and Common Problems]
* [http://dev.mysql.com/doc/refman/5.7/en/error-handling.html MySQL :: MySQL 5.7 Reference Manual :: B Errors, Error Codes, and Common Problems]
* [http://eosrei.net/articles/2013/03/forcing-mysqldump-always-be-nice-cpu-and-io Forcing a mysqldump to always be nice to CPU and I/O | eosrei.net]: using [http://www.computerhope.com/unix/unice.htm nice] or [http://www.tutorialspoint.com/unix_commands/ionice.htm ionice] commands {{exclaim}}  
* [http://eosrei.net/articles/2013/03/forcing-mysqldump-always-be-nice-cpu-and-io Forcing a mysqldump to always be nice to CPU and I/O | eosrei.net]: using [http://www.computerhope.com/unix/unice.htm nice] or [http://www.tutorialspoint.com/unix_commands/ionice.htm ionice] commands {{exclaim}}  
Line 275: Line 142:
* [https://dev.mysql.com/doc/refman/5.5/en/backup-methods.html MySQL :: MySQL 5.5 Reference Manual :: 7.2 Database Backup Methods]  {{access | date = 2016-06-29}}
* [https://dev.mysql.com/doc/refman/5.5/en/backup-methods.html MySQL :: MySQL 5.5 Reference Manual :: 7.2 Database Backup Methods]  {{access | date = 2016-06-29}}
* [[SQL syntax debug]]
* [[SQL syntax debug]]
* [https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server SQL Modes]
* [https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options]


=== references ===
=== References ===
<references/>
<references/>




{{Template:Troubleshooting}}


[[Category:MySQL]]
[[Category:MySQL]]
[[Category:Data Science]]
[[Category:Data Science]]
Anonymous user
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/17805...23761"

Navigation menu