Site down, need help. Issues with sql import.

That sort of error happens when phpMyAdmin adds in all of it’s “extra” code and that’s what causes the problems.

The way to get around this is to open the SQL file in a text editor (not Word or anything similar) and edit it. You’ll pretty much take out everything that’s not creating a new table or inderting data. Lines like:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

are what’s causing that issue. They are not valid SQL and when the importer tries to import them the MySQL program comes back with that error.

Ok, I’ll go back to the sql file, start by taking that line out.

Its odd that one instance of 44 times where “ENGINE=MyISAM” is, (the error I pasted above) the one case there it somehow came out “ENGINE = M– MySQL dump 10.11” as seen in the error. I Edited that back, but it still is giving me the same error in import, but now with “MYISAM” –> no lower case y, not sure the relevancy there, instead of the “M– ….”

Do I delete all of these? : (144 instances like this)

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Basically, yes. Those shouldn’t matter when you do your import.

As far as the error coming from the ENGINE= part, that’s an error with the export. If you look at the other tables in the SQL file you’ll see what they are supposed to look like, but it looks like that one’s been truncated to early and it’s dropped off the ending of the table definition.

I deleted about 160 of those lines and was getting more errors on import. I then started over with a fresh backup of that sql.
I thought about what you said, dropped off code, and thought Id look over that first part with the engine code that fell off:
) ENGINE = M– MySQL dump 10.11
—
And changed it to:
) ENGINE=MyISAM DEFAULT CHARSET=utf8; <–added this, let the rest follow on next

— MySQL dump 10.11
—
— Host: localhost Database: fra1301704504244
— ——————————————————
— Server version 5.0.96-log

So now it moved past that first error, and I now have 5 tables instead of 4, and a new error:
SQL query:
INSERT INTO wp_commentmeta VALUES (1,2,’akismet_result’,’false’),(2,2,’akismet_history’,’a:4:{s:4:\”time\”;d:1359068264.60383892059326171875;s:7:\”message\”;s:28:\”Akismet cleared this comment\”;s:5:\”event\”;s:9:\”check-ham\”;s:4:\”user\”;s:0:\”\”;}’),(4,2,’akismet_history’,’a:4:{s:4:\”time\”;d:1359071391.7405779361724853515625;s:7:\”message\”;s:47:\”fran9635 changed the comment status to approved\”;s:5:\”event\”;s:15:\”status-approved\”;s:4:\”user\”;s:8:\”fran9635\”;}’),(5,3,’akismet_result’,’false’),(6,3,’akismet_history’,’a:4:{s:4:\”time\”;d:1359071974.7687320709228515625;s:7:\”message\”;s:28:\”Akismet cleared this comment\”;s:5:\”event\”;s:9:\”check-ham\”;s:4:\”user\”;s:8:\”fran9635\”;}’),(83,30,’akismet_result’,’false’),(84,30,’akismet_history’,’a:4:{s:4:\”time\”;d:1360971420.6763589382171630859375;s:7:\”message\”;s:28:\”Akismet cleared this comment\”;s:5:\”event\”;s:9:\”check-ham\”;s:4:\”user\”;s:0:\”\”;}’),(86,30,’akismet_history’,’a:4:{s:4:\”time\”;d:1360971460.042884111404[…]
MySQL said:
#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 ‘0’,
usage int(10) unsigned NOT NULL default ‘1’,
PRIMARY KEY (refID)
) ‘ at line 1

That’s becuase you’re column name is causing problems. usage is a reserved keyword in MySQL, so if you’re going to use that for a column name in your DB it needs to be escaped with backticks. You can see an example below the ‘Reply’ box here.

THis is the way the code looks in the sql file before getting imported. Four instances of “usage” and all have the backticks.

CREATE TABLE wp_netblog_bibrefs (
refID int(10) unsigned NOT NULL auto_increment,
typeID smallint(5) unsigned NOT NULL,
style varchar(30) NOT NULL,
name varchar(50) NOT NULL,
userID int(10) unsigned NOT NULL,
time int(10) unsigned NOT NULL,
excerpt text,
hide_inline tinyint(4) default ‘0’,
usage int(10) unsigned NOT NULL default ‘1’,
PRIMARY KEY (refID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

hmm this posting took off all the back ticks… um, anyway they are there in the sql, and shows backticks on the error message in phpmyadmin

I also delete the test database and created a new one, reimported, and same problem happens:

Error
SQL query:

INSERT INTOwp_commentmeta` VALUES (1,2,’akismet_result’,’false’),(2,2,’akismet_history’,’a:4:{s:4:\”time\”;d:1359068264.60383892059326171875;s:7:\”message\”;s:28:\”Akismet cleared this comment\”;s:5:\”event\”;s:9:\”check-ham\”;s:4:\”user\”;s:0:\”\”;}’),(4,2,’akismet_history’,’a:4:{s:4:\”time\”;d:1359071391.7405779361724853515625;s:7:\”message\”;s:47:\”fran9635 changed the comment status to approved\”;s:5:\”event\”;s:15:\”status-approved\”;s:4:\”user\”;s:8:\”fran9635\”;}’),(5,3,’akismet_result’,’false’),(6,3,’akismet_history’,’a:4:{s:4:\”time\”;d:1359071974.7687320709228515625;s:7:\”message\”;s:28:\”Akismet cleared this comment\”;s:5:\”event\”;s:9:\”check-ham\”;s:4:\”user\”;s:8:\”fran9635\”;}’),(83,30,’akismet_result’,’false’),(84,30,’akismet_history’,’a:4:{s:4:\”time\”;d:1360971420.6763589382171630859375;s:7:\”message\”;s:28:\”Akismet cleared this comment\”;s:5:\”event\”;s:9:\”check-ham\”;s:4:\”user\”;s:0:\”\”;}’),(86,30,’akismet_history’,’a:4:{s:4:\”time\”;d:1360971460.042884111404[…]

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 ‘0’,
usage int(10) unsigned NOT NULL default ‘1’,
PRIMARY KEY (refID)
) ‘ at line 1`

the backticks are there though, they just drop when I post to this forum