Error ascii 0 appeared in the statement

How to Fix Error ASCII while Restoring Database from MySQL Dump

As a sysadmin, it’s our duty to regularly backup production database. In MariaDB or MySQL we can do this easily by using mysqldump utility, like below:

• [ uname ] — MySQL database username
• [ pass ] — MySQL database password for user [ uname ]
• [ dump.sql ] — MySQL database dump target filename

Then we can restore or import the database dump file by using this command:

That should be enough for most of the cases. As a matter of fact, I’ve been using it for so many years. I even published an article in this blog about backup and restore MySQL database.

But a few days ago, I had an issue where the restore process stopped halfway. There was an error message said:

What Happened?

It seems that the file is not what MySQL expected. When I checked the database dump file with file utility:

It says » UTF-8 Unicode text, with very long lines » like below:

From this error message we know that the common and quickest way of dumping a database with mysqldump does not treat UTF-8 encoding right.

The Fix

For this case, we already have the database dump file to be imported, but it’s partially working due to incorrect UTF-8 encoding. We still can use this database dump file using mysql client:

• [ uname ] — MySQL database username
• [ database ] — MySQL database name we’re going to restore

Assuming the database dump filename is dump.sql , MySQL should be able to import it correctly.

The Correct Way

To make sure we have a working database dump for UTF-8 encoded character set, use this command to create a database dump:

Then, to import the file we can use this command:

Final Words

I hope that you now know how to import or restore UTF-8 encoded MySQL / MariaDB database. If you run into any issues or have any feedback feel free to drop a comment below.

Источник

Troubleshooting of MySQL errors

Troubleshooting of MySQL errors

Contents

command not found: mysqldump [ edit ]

1. locate the mysqldump command
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

• the error log only be accessed by the user named mysql [1]

Could not open single-table tablespace file filename.ibd [ edit ]

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 [3] [4] .

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

Could not start MySQL service on Windows [ edit ]

1. MySQL Data Directory was allowed to written by NETWORK SERVICE on Windows Server 2008 [5]
2. Directory secure-file-priv was allowed to written by NETWORK SERVICE on Windows Server 2008

MySQL server has gone away [ edit ]

• Enable the option log_error in MySQL config file e.g. log_error=»file_name_of_error_log» [6] .
• Example error log located at file_name_of_error_log are as following:

• Increase the value of max_allowed_packet [7] if the MySQL user has the SUPER privilege [8] .

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

• Number of question marks is not matched with the number of query values [9]
• The array of query values should not be the associative array. Use sequential array!

Error!: SQLSTATE[HY000]: General error [ edit ]

Message: Error!: SQLSTATE[HY000]: General error

Condition: When I used the PHP: PDO

• «You do not use $result = $stmt->fetchAll(); with update or insert queries» [10] .
• You do not use $result = $stmt->fetchAll(); in the query INTO OUTFILE . [11][12] .

ERROR: ASCII ‘’ appeared in the statement, but this is not allowed unless option —binary-mode is enabled and mysql is run in non-interactive mode [ edit ]

Message: ERROR: ASCII ‘’ 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 ‘’ is expected. Query: ».

Condition: When I import the sql file and I met the above error message.

Solution: Unzip the file and then import the file again [13] . The sql file is a compressed file. You may use file (command) for recognizing the type of file.

Errcode: 13 Permission denied [ edit ]

The message occurred after executed mysqld.exe —datadir=..data —console when I tried to start the service. (Version of MySQL：5.5.5-10.0.12-MariaDB on Win )

Open the command line with administrative privileges. (How to: How to Open the Command Prompt as Administrator in Windows 8 or 10)

Error!: SQLSTATE[HY000]: General error: 3 Error writing file ‘xxxTempxxx.tmp’ (Errcode: 28 — No space left on device) [ edit ]

Example error message: Error!: SQLSTATE[HY000]: General error: 3 Error writing file ‘C:WindowsSERVIC

1AppDataLocalTempMY2713.tmp’ (Errcode: 28 — No space left on device)

Condition: Check the disk free space of mysql tmpdir folder

Solutions: Increase the free space of mysql tmpdir folder. Or change the mysql tmpdir folder with another hard disk drive contains more free space [14] .

• Check the current mysql tmpdir folder. Query the syntax SHOW VARIABLES LIKE ‘tmpdir’; .
• Edit the mysql configuration file

• Restart the MySQL service

• Query the syntax SHOW VARIABLES LIKE ‘tmpdir’; to validate the modification of mysql configuration file.

mysqldump: Got errno 32 on write [ edit ]

met the error message:

• The pv was not installed
• Input the command to check if pv was installed which pv
• install pv by input the command: sudo yum -y install pv [16]

errno 41 — Error dropping database [ edit ]

Message: Error dropping database (can’t rmdir ‘.TABLE_NAME’, errno: 41) occurred when I executed DROP DATABASE `TABLE_NAME`;

ERROR 1005 (HY000) at line xx: Can’t create table ‘TABLE_NAME’ (errno: 28) [ edit ]

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

• 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: ‘.DATABASETABLE.frm’ (errno: 22 — Invalid argument) [ edit ]

Message: [Err] 1017 — Can’t find file: ‘.DATABASETABLE.frm’ (errno: 22 — Invalid argument)

• Check the existence of file DATABASETABLE.frm. If not, you may need to create the TABLE before executed the MySQL query.
• Check the permission of file DATABASETABLE.frm or folder which the file located [17][18] . unverified

ERROR 1044 (42000): Access denied for user ‘USER’@’localhost’ to database ‘DATABASE_NAME’ [ edit ]

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

• Check the permission of specified user name & database name

Message: mysqldump: Got error: 1044: Access denied for user ‘USER’@’localhost’ to database ‘DATABASE_NAME’ when doing LOCK TABLES

• Add the mysqldump option —skip-lock-tables if you cannot grant the user permissions [19] .

ERROR 1052 — Column ‘column_name’ in field list is ambiguous [ edit ]

Message: Error Code: 1052. Column ‘column_name’ in field list is ambiguous

Cause: Since ‘column_name’ is present in 2 or more tables . [20]

Solution: Remain only one table name ‘column_name’ OR adding the table name alias.

ERROR 1054 — Unknown column in ‘where clause’ [ edit ]

Message: [Err] 1054 — Unknown column ‘xxx’ in ‘where clause’

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.

Enclosed the whole query into another parent derived query.

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

• Because the hard disk of partition where MySQL Data Directory located is full or almost full, free some hard disk space [21] .

ERROR 1170: BLOB/TEXT column ‘url’ used in key specification without a key length [ edit ]

• SQL syntax when tried to create the new table

Solution: BLOB/TEXT column ‘url’ used in key specification with a key length e.g. `url`(500)

ERROR 1205: Lock wait timeout exceeded; try restarting transaction [ edit ]

1. SHOW OPEN TABLES WHERE in_use > 0; [23]
2. SHOW [FULL] PROCESSLIST; [24]
3. KILL

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

• Keywin SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’; . If it returns 8388608 , it means 8388608 bytes ≅ 8MB.

Increase innodb_buffer_pool_size e.g. SET GLOBAL innodb_buffer_pool_size=402653184; (402653184 bytes

400MB. Default value is 8MB.)

ERROR 1235: This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ [ edit ]

Message: ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’.

Solution: Change the syntax of subquery to column_name BETWEEN start_number to end_number

ERROR 1267: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation ‘concat’ [ edit ]

Error condition: Tried to concat different type of data e.g. CONCAT(string, int)

Solution: SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR)) or SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR)) [27] [28]

ERROR 1305 — FUNCTION MY_TABLE.MY_FUNCTION does not exist [ edit ]

Message: MySQL error 1305 — FUNCTION MY_TABLE.MY_FUNCTION does not exist

Solution: Fix the typo in the function name

ERROR 1690 — BIGINT UNSIGNED value is out of range [ edit ]

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

ERROR 1813: Tablespace for table xxx exists [ edit ]

Message: ERROR 1813 Tablespace for table xxx exists.

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:

Solution: (1) Check if the account was created or not

(2a) If the account was created, set the password for the account.

(2b) If the account was NOT created, re-create the account.

ERROR 2002: Cannot connect: SQLSTATE[HY000] [2002] [ edit ]

Condition on Cygwin terminal of Windows:

• Change -h localhost to -h 127.0.0.1

• If still not work, reboot the server and restart the MySQL service.

ERROR 2002: SQLSTATE[HY000] [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted [ edit ]

Message: (1) [2002] Only one usage of each socket address (protocol/network address/port) is normally permitted (2) «SQLSTATE[HY000] [2002] 一次只能用一個通訊端位址 (通訊協定/網路位址/連接埠)。» in Chinese

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘ip’ [ edit ]

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘IP’

• Check if the IP is alive

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘IP’ (111 «Connection refused»)

• Check if the MySQL service is running or not [31] . If not, start the MySQL service.
• Check the firewall rules

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘IP’ (116 «Connection timed out»)

• Check the configuration of MySQL
  • comment out bind-address = 127.0.0.1 or set to *
  • comment out skip-networking
• Check the permission of MySQL database user.
• Check the firewall rules at (1) your personal computer (2) the server where MySQL service located (3) ISP/VM providers. 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.
• (optional) Monitor the firewall activity. More on How to Track Firewall Activity with the Windows Firewall Log on Win

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

• Increase the settings of (1) DBMS connection keep-alive interval (in seconds) & (2) DBMS connection read time out (in seconds) on MySQL Workbench[32][33] . 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.

ERROR 1045 (28000): Access denied for user [ edit ]

Message: ERROR 1045 (28000): Access denied for user ‘user’@’localhost’ (using password: YES)

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

ERROR 1049 (42000): Unknown database [ edit ]

Message: ERROR 1049 (42000): Unknown database ‘MY_DATABASE_p’

• Check the database ‘MY_DATABASE_p’ is exists
• Check there are no TAB character after database name if you want to connect the database ‘MY_DATABASE’ when you are using console.

ERROR 1070 (42000): Specified key was too long; max key length is 767 bytes [ edit ]

Envoronment: MySQL 5.6

Root cause: «By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Statement”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format.» [35]

• execute SQL query as the following:

• modify the MySQL configuration file

• restart the MySQL server

• execute SQL query as the following:

ERROR 1366: Incorrect string value [ edit ]

Message: SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘xF0x9Fx87xAFxF0x9F. ‘ for column ‘XXX’

• Check the charset of PHP PDO. AND execute set names utf8mb4 [38]
• Check the configuration of table
  • CHARACTER SETS (aka 字元集、字符集): utf8mb4
  • COLLATION (aka 定序、字元序): utf8mb4_unicode_ci

Message: Error!: SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user ‘user’@’localhost’ (using password: YES)

• If you executed the query INTO OUTFILE , you need to grant the file permission e.g. GRANT FILE ON *.* TO ‘user’@’localhost’; [39][40] .

Related issue: «Error!: SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the —secure-file-priv option so it cannot execute this statement» [41]

Error!: SQLSTATE[42000]: Syntax error or access violation [ edit ]

Message: Error!: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near .

• SQL syntax debug
• SQL Syntax Check Online, SQL Validator, Instant SQL Compiler Online – EverSQL Not support the PHP: PDO::prepare which the query syntax contains question marks.

Navicat error: [Exp] OLE error 800A03EC [ edit ]

• Rows count of results exceed the limit of Microsoft Excel Worksheet size: 1,048,576 rows

• LIMIT the rows of MySQL query

PHP Fatal Error: Allowed Memory Size Exhausted when import SQL.GZ file using adminer [ edit ]

solution: Using the naive mysqldump command to generate the backup file. And import the backup by using mysql command.

Источник

As a sysadmin, it’s our duty to regularly backup production database. In MariaDB or MySQL we can do this easily by using mysqldump utility, like below:

$ mysqldump --opt -u [uname] -p[pass] --all-databases > [dump.sql]

Where:

• [uname] — MySQL database username
• [pass] — MySQL database password for user [uname]
• [dump.sql] — MySQL database dump target filename

Then we can restore or import the database dump file by using this command:

$ mysql -u [uname] -p[pass] < [dump.sql]

That should be enough for most of the cases. As a matter of fact, I’ve been using it for so many years. I even published an article in this blog about backup and restore MySQL database.

But a few days ago, I had an issue where the restore process stopped halfway. There was an error message said:

ERROR: ASCII '' 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 '' is expected.

What Happened?

It seems that the file is not what MySQL expected. When I checked the database dump file with file utility:

$ file dump.sql

It says «UTF-8 Unicode text, with very long lines» like below:

From this error message we know that the common and quickest way of dumping a database with mysqldump does not treat UTF-8 encoding right.

The Fix

For this case, we already have the database dump file to be imported, but it’s partially working due to incorrect UTF-8 encoding. We still can use this database dump file using mysql client:

$ mysql -u [uname] -p --default-character-set=utf8 [database]
mysql>  SET names 'utf8'
mysql>  SOURCE dump.sql
...

Where:

• [uname] — MySQL database username
• [database] — MySQL database name we’re going to restore

Assuming the database dump filename is dump.sql, MySQL should be able to import it correctly.

The Correct Way

To make sure we have a working database dump for UTF-8 encoded character set, use this command to create a database dump:

$ mysqldump -u [uname] -p [database] -r dump.sql

Then, to import the file we can use this command:

$ mysql -u [uname] -p --default-character-set=utf8 [database]
mysql>  SET names 'utf8'
mysql>  SOURCE dump.sql

Final Words

I hope that you now know how to import or restore UTF-8 encoded MySQL / MariaDB database. If you run into any issues or have any feedback feel free to drop a comment below.

Server version: 5.6.24 — MySQL

Receiving this error when trying to import a mysql dump from Advanced Backup & Migrate:

ERROR: ASCII » 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 » is expected.

I’m trying this at the command line:
mysql -u root -D datbasename < dump.mysql
and I get the above error

adding —binary-mode=0, —binary-mode=1, —binary-mode -0, —binary-mode -1 do not work.

Any suggestions on how to do deal with this so I can import via the command line?

Thanks,
K

==================================

mysql options state:
(format) —binary-mode
(description) Disable rn — to — n translation and treatment of as end-of-query

—binary-mode
This option helps when processing mysqlbinlog output that may contain BLOB values. By default, mysql translates rn in statement strings to n and interprets as the statement terminator. —binary-mode disables both features. It also disables all mysql commands except charset and delimiter in non-interactive mode (for input piped to mysql or loaded using the source command).