Jump to content

Error exporting / importing database


Peter Knight
 Share

Recommended Posts

Hi guys

I'm trying to duplicate a ProcessWire site on a different server so I can work on it.

The good news is the live and the test servers are identical VPS. I already have multiple PW sites running on both.

I can export the DB from the Live server but when I try to import to Test server I get the following error.

#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 'DROP TABLE IF EXISTS `fieldtype_options`;
CREATE TABLE IF NOT EXISTS `fieldtype_' at line 48

Am I correct in thnking this error in the SQL syntax is being generated at the export stage? 

IE my Live PW site wouldn't be working with a syntax error so I must be messing up the export of the .sql file

There is nothing at line 48 but a find command shows

CREATE TABLE `fieldtype_options` (
  `fields_id` int(10) UNSIGNED NOT NULL,
  `option_id` int(10) UNSIGNED NOT NULL,
  `title` text,
  `value` varchar(255) DEFAULT NULL,
  `sort` int(10) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

BTW, my server setup is:

Version Plesk v12.5.30_build1205150826.19 os_CentOS 6 OS CentOS 6.3 (Final)

MySQL: 5.5.41

PHP: 5.6.19

Link to comment
Share on other sites

You mean, if my DBs are different, I cannot use it for syncing without caution?

But is it safe if I want to replicate, for example, my local DB to a public site? Or would this only be safe, if I insert the sql dump after I have completly emptied the target DB? That's (something) what you mean, right?

Link to comment
Share on other sites

First thoughts:

  • Have you exported the tables or the database itself?
  • Are you importing the tables or the database itself?
  • Is the database you are importing too really empty?

1 the database I think. I select all the tables in phpmyadmin and export as a sql file

2 database. I create a new blank database and the import my SQLite file into that blank database

3 yes. When an import fails I drop the tables that partially imported

4. What tool are you using for the export/import?... (btw)

4 . Phpmyadmin

Link to comment
Share on other sites

@horst 

If you want to make sure everything is exactly the same (excess tables wiped) you simply shouldn't use the gui of the db module. You'd need to remove all tables beforehand, but this would at the same time make the gui unusable :D

Link to comment
Share on other sites

I've never had problems using the command-line mysqldump database_name > dumpfile.sql to create a dump sql file, then on the target machine mysql new_database_name < dumpfile.sql

In the past phpmyadmin had given me problems with large sql files, probably because of php configured limits to the size of an http post.

  • Like 1
Link to comment
Share on other sites

I had some success and then another issue.

I used the module Horst mentioned to create a backup and export of the database from my live site.

When I tried to import this database via phpMyAdmin, I had a slightly different error 

#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 'INSERT INTO `caches` (`name`, `data`, `expires`) VALUES('Modules.wire/modules/',' at line 2

Within the Plesk UI, I can import a database dump.

post-1166-0-24257100-1459415321_thumb.pn

Doing it this way didn't throw any errors at import  :)

New issue

I'm unsure if this is related and wether it's an issue at the export or import stage.

When I try to edit a page on my test site, I get this error regarding ProcessAccessOverview.module

Parse Error: syntax error, unexpected '[', expecting ')' (line 172 of /var/www/vhosts/websitedomain.com/httpdocs/site/modules/ProcessAccessOverview/ProcessAccessOverview.module) 


This error message was shown because you are logged in as a Superuser. Error has been logged.
Link to comment
Share on other sites

Just a quick update on the error above I crossed out.

This seems to be an unrelated error caused by

1. The proper URL being redirected to a www URL due to .htaccess settings that are appropriate to the live site but won't work on the test site

2. The actual error message is not related to the test domain. When it says "/var/www/vhosts/websitedomain.com/httpdocs ...", that domain (websitedomain.com) is my plesk Hostname.

Beats me why it throws this up but tweaking the .htaccess file to not direct to www works and I can edit my site etc.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...