SQL import help

[AHarvey]

Hi all,

While I have been off our company Wordpress website has been taken down from the existing host and a new host has been started on Fasthost.

The original hosting company wouldn't allow access to any admin apps including phpmyadmin or even ftp, hence the reason for the move. They sent a a link over allowing access to FTP to download the WP files and sent over the SQL backup.

Trying to import the database backup into the new host and keep getting the same error message:

SQL query:

--
-- Dumping data for table `vires_commentmeta`
--
LOCK TABLES  `vires_commentmeta` WRITE ;


MySQL said: Documentation

#1146 - Table 'viresourcing.vires_commentmeta' doesn't exist

any advice on what i need to look at? Googled and lots of comments about making sure DB names are the same and equally as many saying that it doesn't matter. Need a quick fix so came here instead of spending hours trawling online

 

[AHarvey]

ok so i've done some looking in to it now (I'm completely new to this kind of thing) and I've found an example of an sql dump:

DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And here is the sql file we were sent over:

LOCK TABLES `vires_commentmeta` WRITE;
/*!40000 ALTER TABLE `vires_commentmeta` DISABLE KEYS */;
INSERT INTO `vires_commentmeta` VALUES (405,105,'akismet_result','false'),(406,105,'akismet_history','a:4:{s:4:\"time\";d:1417523869.324507;s:7:\"message\";s:28:\"Akismet cleared this comment\";s:5:\"event\";s:9:\"check-ham\";s:4:\"user\";s:7:\"Sue\";}'),(408,105,'rating','5'),(409,106,'akismet_result','false'),(410,106,'akismet_history','a:4:{s:4:\"time\";d:1417524071.9188819;s:7:\"message\";s:28:\"Akismet cleared this comment\";s:5:\"event\";s:9:\"check-ham\";s:4:\"user\";s:7:\"Sue\";}'),(412,106,'rating','5'),(529,141,'is_customer_note','0'),(530,142,'is_customer_note','0'),(1166,312,'is_customer_note','0');
/*!40000 ALTER TABLE `vires_commentmeta` ENABLE KEYS */;
UNLOCK TABLES;

So it looks like ours isn't creating the tables like the example.

I don't think we're going to be able to get our hands on a correct SQLdump now from the original IT company so is it possible to use this info? I can't see any field names etc so unsure if I can create the tables manually

Any help guys? do i need to start from scratch or can this be saved?

 

[Gornall]

Have they literally sent you scripts or have you got anything else?

 

[AHarvey]

I've got a zip file with an sql file in, then the actual files from the hosting space.

 

[Diddums]

No idea how to do this, but good luck with Fasthosts. I used them for about three years and at first they were impeccable but after two years or so they just went downhill fast. YMMV of course

 

[AHarvey]

Director of the company insisted on going with hosting provided by the IT company we use, despite us branching out into some tech that needs good hosting.

I advised a vidahost unlimited package would be the best option and we got this instead

 

[oneilldo]

You need a couple of parts to the SQL dump. You need to create the required tables and then populate them. If you can find the SQL for the tables required then it should work.
So run the SQL containing the Create Table bits then run the SQL containing the INSERT statements (the data). If the dump they have given you doesn't have the table creation SQL in then either ask them for it or if its for some other CMS that you can still use (wordpress) you could just do an initial install and then run the INSERT statements

The error you are getting makes sense, the create table sql creates a table called 'wp_commentmeta' but the insert statement is looking for an import into the table called 'vires_commentmeta'.
Don't use wordpress but I imagine the previous install was done and perhaps they provided their own prefix ?? hence vires rather than wp_

Couple of fixes
1. Change the create table SQL to create a table/indexes called vires_commentmeta, or...
2. Change the insert statement to insert into wp_commentmeta rather than vires_commentmeta

Obviously depending on the complexity other tables/code/sql might reference the table names so depending on exactly what you have received and what you plan to create (a copy or a new CMS page with the data) will depend on which of the above is the most viable option.

Changing the table creation SQL to be vires_ rather than wp_ could just be done fairly easily with a search and replace (make a backup first!) as could the insert statements (depending on size!)

 

[markeh]

It sounds like someone created the sql dump from phpMyAdmin or something and didn't click the create table field.

Personally I'd go back to the original hosts and ask them for a proper database dump, rather than fixing it yourself.

 

[AHarvey]

well, despite the boss taking it off me so I could work on more important projects and the IT guys who "don't do web dev" spending 2.5 days on it I contacted the original IT guy and he was great.

Explained how he normally does it without the CREATE SQL backup, just the input one and still sent over the one with the CREATE instructions in. Sorted in 30 mins

Thanks for the help everyone