MySQL commands

From LemonWiki共筆
Jump to: navigation, search

MySQL commands and troubleshooting of MySQL errors.

Contents

Preparation[edit]

install MySQL database client apps contains mysql & mysqldump commands

  • Linux Os linux.png :
  • Win Os windows.png : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
  • Mac Os mac.png : Install XAMPP. Path of mysql executable file is: /Applications/xampp/xamppfiles/bin/mysql

(optional) install the Pipe Viewer (pv) package

  • Linux Os linux.png :
    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 Os windows.png : Install (1) Cygwin, (2) search pv & install pv package
  • Mac Os mac.png : 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

  1. create the db user
  2. 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]
  3. the db user will be able to execute the mysqldump command

Exporting data of database/table into MySql sql file[edit]

Exporting entire data[edit]

method1: Good! 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]:

  1. (optional) install pv if the pv was not installed
  2. (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
  3. ask to enter password and press Enter

optional: Append the Today's date to file name

  • On Linux Os linux.png : (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]:

  1. (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
  2. (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
  3. enter password and press Enter

method4: export the *.sql file

  1. 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. Icon exclaim.gif 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[edit]

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


Potential failure issue[edit]

  • insufficient hard-disk space

Importing data from MySql sql file[edit]

Icon exclaim.gif Notice: existing database will be overwritten

method 1: Good! 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:

  1. (optional) install pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux
  2. 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 Os windows.png or md5sum command for Linux Os linux.png to obtain MD5 checksum of files.
  3. pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [11] and press Enter
  4. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 2: Import *.sql file:

  1. (optional if the .sql file was compressed) unzip data.zip or gzip -d data.gz if the file was compressed by gzip
  2. mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql [12] and press Enter
  3. 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]:

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

method 4: Import ZIP file to MySql directly:

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

Synchronizing databases[edit]

Synchronizing two MySQL databases:

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

Transfer date from MS SQL server to MySQL server:

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

Show the MySQL process list & kill the process[edit]

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

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

Troubleshooting of MySQL errors[edit]

command not found: mysqldump[edit]

  1. locate the mysqldump command
    • sudo find / -iname mysqldump for Mac Os mac.png or Linux Os linux.png
    • If you installed
      • XAMPP for Mac Os mac.png : Entire path is /Applications/XAMPP/xamppfiles/bin/mysqldump
      • XAMPP on X disk for Win Os windows.png : Entire path is X:\xampp\mysql\bin\mysqldump.exe
      • MAMP & MAMP PRO for Mac Os mac.png : Entire path is /Applications/MAMP/Library/bin/mysqldump
  2. 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[edit]

Version: XAMPP 5.6.15-1 on Mac Os mac.png

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[edit]

Version: XAMPP 5.6.15-1 on Mac Os mac.png

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:

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

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

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)[edit]

Solutions

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

Solutions

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

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'[edit]

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)[edit]

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'[edit]

Message: [Err] 1054 - Unknown column 'xxx' in 'where clause'

Solution:

  1. check the column name 'xxx' if exists
  2. 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[edit]

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[edit]

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[edit]

Solution: [29]

  1. SHOW OPEN TABLES WHERE in_use > 0;[30]
  2. SHOW [FULL] PROCESSLIST;[31]
  3. KILL <process id>;[32]

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

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.[edit]

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'[edit]

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")[edit]

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")[edit]

Solution:

References:

ERROR 2013: Lost connection to MySQL server during query[edit]

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.
  • 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[edit]

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[edit]

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

further reading[edit]

references[edit]

  1. Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe
  2. ivarch.com: Pipe Viewer
  3. Pipe Viewer (pv) in Mac OSX
  4. security - Minimum permissions for a user to perform a mysqldump? - Server Fault
  5. mysql - 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. backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow
  8. pv + 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. pv + gzip + mysql — W. Andrew Loe III: Journal
  12. Import MySQL Dumpfile, SQL Datafile Into My Database
  13. mysql - 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. database - 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. mysql - Error: could not open single-table tablespace file .\scrapers\records.ibd - Stack Overflow
  21. MySQL Won't Start - InnoDB Corruption and Recovery. - Chepri
  22. php 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. escape 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. mysql - ERROR 1114 (HY000): The table is full - Stack Overflow
  29. mysql - 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


Troubleshooting

Template