Error exporting / importing database

[Peter Knight]

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

[arjen]

First thoughts:

1. Have you exported the tables or the database itself?
2. Are you importing the tables or the database itself?
3. Is the database you are importing too really empty?

[kongondo]

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

[horst]

Recommendation: http://modules.processwire.com/modules/process-database-backups/

[LostKobrakai]

@horst

The module is very nice for backups, but it's to be used with caution for importing / restoring via the gui, as it doesn't remove tables of e.g. fields created in the meantime.

[horst]

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?

[Peter Knight]

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

[LostKobrakai]

@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

[Doug G]

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.

[Peter Knight]

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.

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.

[Peter Knight]

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.