MySQL commands

MySQL commands and troubleshooting of MySQL errors.

Preparation

install MySQL database client apps contains mysql & mysqldump commands

Linux :
Win : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
macOS : Install XAMPP. Path of mysql executable file is: /Applications/xampp/xamppfiles/bin/mysql

(optional) install the Pipe Viewer (pv) package

Linux :
1. How to Enable RPMForge Repository in RHEL/CentOS 7.x/6.x/5.x/4.x
2. (1)yum install pv for RHEL / CentOS / SL / Fedora Linux^[1]; (2) apt-get install pv for Debian / Ubuntu^[2];
Win : Install (1) Cygwin, (2) search pv & install pv package
macOS : Install (1) brew install pv to get the latest version of pv package^[3] and (2) MySQL database clients from XAMPP. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql

create the db user

create the db user
grant the minimum permission: SELECT, LOCK TABLES to the db user. SQL query: GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';^[4]
the db user will be able to execute the mysqldump command

Exporting data of database/table into MySql sql file

Exporting entire data

method1: export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump^[5]^[6]^[7]:

(optional) install pv if the pv was not installed
(for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.gz ^[8]^[9] and press Enter
ask to enter password and press Enter

optional: Append the Today's date to file name

On Linux : (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.$(date +"%y%m%d").gz

method2: Bulk export individual tables into the multiple compressed *.sql files

mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_1 | pv | gzip -c > table_1.sql.gz
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_2 | pv | gzip -c > table_2.sql.gz

note: the last line of above part is return symbol. The last command will not be executed automatically without the return symbol.

method3: export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump ^[10]:

(optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
(for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql and press Enter
enter password and press Enter

method4: export the *.sql file

mysqldump -h localhost -u username -p --single-transaction --default-character-set=utf8 DATA-BASE-NAME > /path/to/database.sql

method5: Using phpmyadmin to export the database. But I found the number rows of table after imported was not the same with the number rows of original table once.

Exporting selected data

mysqldump - Append MYSQL dump to a table - Stack Overflow [Last visited: 2016-06-29]

Potential failure issue

insufficient hard-disk space

Importing data from MySql sql file

Notice: existing database will be overwritten

method 1: 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 pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux
Check if the file is gzip format (test the compressed file integrity) ex: (1) gunzip -t database.sql.gz If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using WinMD5 Free for Win or md5sum command for Linux to obtain MD5 checksum of files.
pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME ^[11] and press Enter
enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 2: Import *.sql file:

(optional if the .sql file was compressed) unzip data.zip or gzip -d data.gz if the file was compressed by gzip
mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql ^[12] and press Enter
enter password and press Enter

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^[13]:

(optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME ^[14] and press Enter
enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 4: Import ZIP file to MySql directly:

(optional if the 7Zip was not installed) yum install p7zip
7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME ^[15]^[16] and press Enter
enter password and press Enter

Synchronizing databases

Synchronizing two MySQL databases:

HeidiSQL - MySQL, MSSQL and PostgreSQL made easy v.9.4.0.5125 for Win , macOS & Linux
- Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection
- Scheduler: Not available^[17] . Manually synchronizing databases between two different servers.
Replication
- Steps:
- Scheduler: Auto .
$ SQLyog v.12.4.0 for Win .
- Steps: Menu --> Powertools --> Database Synchronization Wizard
- Scheduler: Available . Save the job file & Schedule it using Windows scheduler.

Transfer date from MS SQL server to MySQL server:

$ SQLyog for Win . Use a query to specify the data to transfer from MS SQL server into MySQL server.

Show the MySQL process list & kill the process

Show the MySQL process list & kill the process (especial for SLOW query command)^[18]

mysql -h HOST -u ACCOUNT -p
And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to mysql> from $ or #
mysql>
- SHOW FULL PROCESSLIST\G to show the process list
- SHOW PROCESSLIST\G to show the brief process list
mysql> kill 101; to kill the process with Id number: 101
mysql> exit; to leave the MySQL command.

Troubleshooting of MySQL errors

command not found: mysqldump

locate the mysqldump command
- sudo find / -iname mysqldump for macOS or Linux
- If you installed
  - XAMPP for macOS : Entire path is /Applications/XAMPP/xamppfiles/bin/mysqldump
  - XAMPP on X disk for Win : Entire path is X:\xampp\mysql\bin\mysqldump.exe
  - MAMP & MAMP PRO for macOS : Entire path is /Applications/MAMP/Library/bin/mysqldump
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

Version: XAMPP 5.6.15-1 on macOS

Cause:

the error log only be accessed by the mysql user

Solution ^[19]:

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

Version: XAMPP 5.6.15-1 on macOS

Condition: The Mac was shutdown accidentally and the database was not shutdown normally. After reboot the Mac, unable to start the MySQL service^[20]^[21].

Possible solution:

Edit the MySQL configuration file located: /Applications/XAMPP/xamppfiles/etc/my.cnf
Add this line: innodb_force_recovery = 1
Try to start the MySQL service
If the MySQL service started successfully, edit the MySQL configuration file and mark this line : #innodb_force_recovery = 1
Restart the MySQL service

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 ^[22]
The array of query values should not be the associative array. Use sequential array!

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 df -h on Linux
More on 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 df -h on Linux
More on ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow.

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

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^[23]^[24]. unverified

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

Message:

pv file.sql.gz | gunzip | mysql -u USER -p --host=127.0.0.1 --default_character_set utf8 DATABASE_NAME
ERROR 1044 (42000): Access denied for user 'USER'@'localhost' to database '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

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

ERROR 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 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 1690 - BIGINT UNSIGNED value is out of range

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

Solution ^[26]^[27]:

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

ERROR 1114 (HY000): The table `TABLE_NAME` is full

Possible solution

Because the partition is full or almost full, free some hard-disk space^[28].

ERROR 1205: Lock wait timeout exceeded; try restarting transaction

Solution: ^[29]

SHOW OPEN TABLES WHERE in_use > 0;^[30]
SHOW [FULL] PROCESSLIST;^[31]
KILL <process id>;^[32]

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

Solution: Increase innodb_buffer_pool_size e.g. innodb - How to change value for innodb_buffer_pool_size in MySQL on Mac OS? - Stack Overflow.

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:

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 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock'

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

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused")

Solution:

Check if the MySQL service is running or not^[33]. If not, start the MySQL service.

ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out")

Solution:

Check the permission of MySQL database user.
Check the firewall rules. More on Install MySQL on CentOS 7 | ProfitBricks DevOps Central
- Check if your IP address was included in the allowed IP address list of firewall rules.
- Check if your IP address was changed by using What Is My IP Address? services.

References:

Connect to a MySQL database remotely

ERROR 2013: Lost connection to MySQL server during query

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 ^[34]^[35]. 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;

Navicat error: [Exp] OLE error 800A03EC

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

resolve insufficient hard disk space where mysql data located

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

references

Troubleshooting of ...

Troubleshooting of Excel errors

PHP, cUrl, Python, selenium, HTTP status code errors

Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors

Troubleshooting of regular expression

HTML/Javascript: Troubleshooting of javascript, XPath

Software: Mediawiki, Docker, FTP problems, online conference software

Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting

Template

Bug report template

[1] Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe

[2] varch.com: Pipe Viewer

[3] Pipe Viewer (pv) in Mac OSX

[4] security - Minimum permissions for a user to perform a mysqldump? - Server Fault

[5] ysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange

[6] MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

[7] up - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow

[8] v + gzip + mysql — W. Andrew Loe III: Journal

[9] (if have permission to LOCK TABLES)mysqldump -h localhost -u username -p DATA_BASE_NAME | pv | gzip -c > database.sql.gz (if have no permission to LOCK TABLES)mysqldump -h localhost -u username -p --lock-tables=false DATA_BASE_NAME | pv | gzip -c > database.sql.gz via mysql - Run MySQLDump without Locking Tables - Stack Overflow

[10] MySQL Import/Export Progress Bar - Kevin Warrington

[11] v + gzip + mysql — W. Andrew Loe III: Journal

[12] Import MySQL Dumpfile, SQL Datafile Into My Database

[13] ysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange

[14] Import MySQL Dumpfile, SQL Datafile Into My Database

[15] tabase - Importing zipped files in Mysql using command line - Stack Overflow

[16] Install 7Zip on CentOS 5.5

[17] Schedule backups/exports

[18] MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax

[19] Using the CHMOD command effectively @ Computer Plumber

[20] ysql - Error: could not open single-table tablespace file .\scrapers\records.ibd - Stack Overflow

[21] MySQL Won't Start - InnoDB Corruption and Recovery. - Chepri

[22] Invalid parameter number: parameter was not defined - Stack Overflow

[23] MySQL, copying tables files gives rise to "ERROR 1017 (HY000): Can't find file:" even though its there there - Stack Overflow

[24] ERROR 1017 (HY000): Can't find file - Percona Community

[25] scape characters - Escaping a password using mysqldump console - Super User

[26] MySQL :: MySQL 8.0 Reference Manual :: 11.2.6 Out-of-Range and Overflow Handling

[27] MySQL error #1690 (BIGINT UNSIGNED value is out of range) for UNIX_TIMESTAMP() - Stack Overflow

[28] ysql - ERROR 1114 (HY000): The table is full - Stack Overflow

[29] ysql - Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction - Stack Overflow

[30] MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.24 SHOW OPEN TABLES Syntax

[31] MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.29 SHOW PROCESSLIST Syntax

[32] MySQL :: MySQL 5.7 Reference Manual :: 13.7.6.4 KILL Syntax

[33] HowTo: Find out If MySQL Is Running On Linux Or Not

[34] How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow

[35] Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]

[17]

[18]

[19]

[20]

[21]

[22]

[23]

[24]

[25]

[26]

[27]

[28]

[29]

[30]

[31]

[32]

[33]

[34]

[35]

MySQL commands

Contents

Preparation

Exporting data of database/table into MySql sql file

Exporting entire data

Exporting selected data

Potential failure issue

Importing data from MySql sql file

Synchronizing databases

Show the MySQL process list & kill the process

Troubleshooting of MySQL errors

command not found: mysqldump

could not access the mysql log

could not open single-table tablespace file filename.ibd

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

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

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

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

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

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

ERROR 1054 - Unknown column in 'where clause'

ERROR 1690 - BIGINT UNSIGNED value is out of range

ERROR 1114 (HY000): The table `TABLE_NAME` is full

ERROR 1205: Lock wait timeout exceeded; try restarting transaction

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

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.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock'

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused")

ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out")

ERROR 2013: Lost connection to MySQL server during query

Navicat error: [Exp] OLE error 800A03EC

resolve insufficient hard disk space where mysql data located

further reading

references

Navigation menu

MySQL commands

Preparation

Exporting data of database/table into MySql sql file

Exporting entire data

Exporting selected data

Potential failure issue

Importing data from MySql sql file

Synchronizing databases

Show the MySQL process list & kill the process

Troubleshooting of MySQL errors

command not found: mysqldump

could not access the mysql log

could not open single-table tablespace file filename.ibd

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

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

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

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

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

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

ERROR 1054 - Unknown column in 'where clause'

ERROR 1690 - BIGINT UNSIGNED value is out of range

ERROR 1114 (HY000): The table `TABLE_NAME` is full

ERROR 1205: Lock wait timeout exceeded; try restarting transaction

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

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.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock'

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused")

ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out")

ERROR 2013: Lost connection to MySQL server during query

Navicat error: [Exp] OLE error 800A03EC

resolve insufficient hard disk space where mysql data located

further reading

references

Navigation menu

Search