SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; gives an error

Question

I have problem with mysql database. I can't import a database from my friend. I need some help.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

ERROR:

Unexpected beginning of statement. (near "phpMyAdmin" at position 0)
Unrecognized statement type. (near "SQL" at position 11)

#1064 - Something is wrong in your syntax obok 'phpMyAdmin SQL Dump
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"' w linii 1

Solution 1

There´s nothing wrong with you syntax, but probably with your file: most likely the file was edited and the text-editor (of course Windows notepad.exe) was too clever and added a BOM on saving.

Remove the first 3 bytes (HEX: EF BB BF), save the file without it (either use a hex editor or use PSPad and switch format to UNIX), and the importer should have no problem anymore.

The BOM fools the importer, the first - gets eaten and the importer no longer recognizes the first comment as such. Wikipedia about BOM:

File comparison (w/o BOM)

Solution 2

I encountered exactly the same problem. Apparently, you use a version of phpMyAdmin which has bugs in the import module (in my case it was phpMyAdmin 4.5.5.1 packaged in Wamp 3.0.4). More precisely, it interprets comments (valid syntax with space after --) as SQL code. This is the case at the beginning of a dump created by phpMyAdmin: it typically starts with

-- phpMyAdmin SQL Dump

which explains your error message.

The import module of phpMyAdmin 4.5.5.1 was not able to parse escaped single quotes either (see https://github.com/phpmyadmin/phpmyadmin/issues/11721).

There are many possible workarounds to this problem:

• Update phpMyAdmin
• Use another tool to import your DB dump, for example MySQL Command Line or MySQL workbench
• Less advisable: execute the contents of the .sql file as a query in your current version of phpMyAdmin (it has fewer bugs)
• Less advisable: strip all comments from your .sql file