CODEWITHSUNDEEP
mysqldatabasephpmyadmin
afrooz hashemzadegani
afrooz hashemzadegani

Reputation: 23

can't import database to the server

I always import the database file to the server with the way that I'm using again. But this time when I want to do this I get this following error :

SQL query:

phpMyAdmin SQL Dump SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"

MySQL said: Documentation

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 'phpMyAdmin SQL Dump

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"' at line 1

Does anyone can help me to solve this problem? I always use this method but this time I have this error.

Thanks in advance

Upvotes: 2

Views: 18309

Answers (5)

Harald Peki
Harald Peki

Reputation: 41

Most likely the import file was edited with a text editor which discretely added a BOM (byte order mark) on saving. Windows´ Notepad does this for example, and since you work with Windows this a highly likely scenario.

Solution: Get a professional text editor (PSPad, Notepad++, whatever, but not Windows Notepad) or Hex editor, check for and remove the first 3 bytes (HEX: EF BB BF), save the file without these BOM bytes - importer should have no problem anymore.

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

Similar question here:

Upvotes: 1

Erbe
Erbe

Reputation: 41

I don't know if it's gonna help someone anymore, but I have face the same problem and found a solution.

@afroozhashemzadegani - as You can see, there's an additional character after the "NO_AUTO_VALUE_ON_ZERO" string in SQL error message.

@afroozhashemzadegani error message

I had the same character in my error message:

My error message

So I opened it in hex:

copied error in hex

When I deleted this guy, everything worked fine. Honestly, I don't know where it came from, as it was already there when I exported database. Anyway, it didn't look like if it mattered on which version of target MySQL server I tried to import it. The error was always the same. It didn't also look like if it mattered what was the first line of SQL code, as when I deleted some of the code on the top, the same error occured for the next portion of executable code.

Upvotes: 4

Ali Vojdanian
Ali Vojdanian

Reputation: 2111

In this case it seems the problem is because of your MYSQL version. As I asked you in the comment below of your question, it seems you are using WAMPSERVER. So because of your WAMPSERVER version it has different version for your MYSQL.

The server you're trying to import may has newer version of MYSQL. So you can simply update your WAMP and then you have updated your MYSQL version. Then try to import them to the server. so you can fix this problem.

Hope it helps

Always try to be update

Upvotes: 0

Vasim Shaikh
Vasim Shaikh

Reputation: 4512

NO_AUTO_VALUE_ON_ZERO is off by default. it is very rare to actually have it turned on at the server level, and is usually used only in SQL dumps.

NO_AUTO_VALUE_ON_ZERO only affects what happens during an insert. turning the mode off does not affect rows that are already in a table.

Problem: the mySQL version I was importing into was older - mySQLversion I exported from was 5.0, mySQL version I was importing into was 3.23.56 so in phpMyAdmin's export tab I selected from the "SQL compatibility mode" drop down list "MYSQL323" and the import then work sucessfully.

Upvotes: 0

safin chacko
safin chacko

Reputation: 1390

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; happens when different version of mysql is being used. When you are transfering from one server to another you should keep in mind the versions of database use in a new environment especially the mysql.

The fix is as follow. simply delete the following lines from your database and you are done

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

visit https://www.drupal.org/node/164401

Upvotes: 1

Related Questions