Error importing MySQL database

Question

Yesterday, I exported a MySQL database of fully functional website. Today I deleted the database and trying to import.

I don't understand this error because I am not a MySQL expert. But I have done the same (exporting, deleting and importing again) many times before and never face any problem.

I am getting below error:

Static analysis:

9 errors were found during analysis.

Unexpected character. (near ":" at position 664)
Unexpected character. (near ":" at position 732)
Unexpected character. (near ":" at position 735)
Unexpected character. (near ":" at position 755)
Unexpected character. (near ":" at position 758)
Unexpected character. (near "[" at position 765)
Unexpected character. (near "]" at position 767)
Unexpected character. (near "[" at position 788)
Unexpected character. (near "]" at position 790)

SQL query:

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 's comments. There you will have the option to edit or delete them.', 0, '1', '',' at line 2

The first few lines are....

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: pdb14.websitehostingmart.com
-- Generation Time: Feb 25, 2017 at 06:00 PM
-- Server version: 5.7.17-log
-- PHP Version: 5.5.38

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `135184_562`
--

-- --------------------------------------------------------

--
-- Table structure for table `wp_commentmeta`
--

@Bert sorry. i forgot to mention that. i am so tense because the site is live and running and everything is mess at this moment. i have updated the question. — NehaAgra, Feb 25 '17 at 18:01
Well, you learned a hard lesson today. Import to a fresh database, make sure it works, THEN delete the old one! :-/ There are no `]` nor `[` in what you've shown. Can you show the part of the dump that has brackets in it? My guess is you're not importing the file you think you are. — Schwern, Feb 25 '17 at 18:19
Like I said, we need to see more of that file to know. You've shown us up to position 656, but the problem starts at 664. Buuuut, what about this? http://stackoverflow.com/questions/34550401/mysql-stored-procedure-error-unexpected-character#34550784 — Schwern, Feb 25 '17 at 18:50
@Schwern i have already edited and saved the exported sql file in a desperate attempt to restore the database. there is no way of CTRL+X. so i can't shows you what exactly was there on line 664. but POSITIVE thing is i have all the content of the website and i will bring it to normal. — NehaAgra, Feb 25 '17 at 19:14
@Schwern The problem is editing sql file. The importation works fine with unedited file. Because the client is using most popular theme i want to change the theme name. For which i am using notepad and doing find /old-theme-name/ replace with /new-theme-name/ (needless to say i changed theme folder name also) — NehaAgra, Feb 26 '17 at 14:57
In the future, please mention important details like "I edited the dump file". The answer I linked yesterday mentioned that tabs can be a problem. Your editor is probably adding tabs or in some other way your edits have corrupted the file. But you shouldn't be doing simple changes like that with a dump and restore! Make those changes in SQL with an `update` command. — Schwern, Feb 26 '17 at 17:13
@Schwern as i said i have done replacing job in dump file many times. Today i did the same, with notepad, on another desktop pc. and importation done successfully. This is weird. — NehaAgra, Feb 27 '17 at 16:06
The encoding used by the editor can be a problem too (which might explain why it works when done from some other PC). However, finding and replacing the contents in a Wordpress database dump file directly using an editor could break the integrity of serialized data (resulting in broken images and alike even after a successful import) - see [here](http://stackoverflow.com/q/15138893/2298301). Personally, I like to use a utility such as [this](https://interconnectit.com/products/search-and-replace-for-wordpress-databases/), although be very careful to never have it on a Live URL. — Dhruv Saxena, Feb 27 '17 at 20:18
@DhruvSaxena "break the integrity of serialized data" i believe that was the problem and it was with .sql file. I used xml dump file and everything worked well. — NehaAgra, Mar 13 '17 at 08:29

score 2 · Accepted Answer · answered Mar 13 '17 at 08:30

2

Export in xml format, make change (replace all or anything) and then import. As @DhruvSaxena pointed out that seialized data of .sql file could disrupt.

answered Mar 13 '17 at 08:30

NehaAgra

39
1
9

Error importing MySQL database

1 Answers1