Jump to content
Peter Knight

Error exporting / importing database

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

Share this post


Link to post
Share on other sites

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?
  • Like 1

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By jom
      Chäs & Co is a b2b cheese trader. They love good cheese and are proud of their excellent knowledge und connections to small cheese producers. They maintain a custom made database and used to copy/paste (!) the cheese infos to the former CMS (Contao). Since substituting the database with ProcessWire was no option, they export now the database to a csv file and import it to the new website (expanded version of module ImportPagesCSV).
      From there, the pw magic begins. The staff works with the admin interface: They search and find products in notime using the build-in search field (using cheese names or id. Using ids only possible by adding hook, see this forum thread). They generate price lists easily based on a template, providing checkboxes and option buttons for some restrictions, choose i.e. the cheese origin and price audience (all implemented as page selectors). By saving the page a PDF list gets generated (using mpdf, a php pdf library).
      The visitors can sort and filter the products using a form by shuffle.js. Product details are shown in a popup (fancybox). There's also the option for generating a nice pdf data sheet. No CSS framework being used.
      Other modules used: ProcessJumpLinks, ProcessProMailer, ProcessWireUpgrade.
      Visual design: Nicole Haller


    • By Juergen
      Hello @ all,
      I am creating a new inputfield/fieldtype to store opening hours, but I am struggeling to save values from multiple dynamic created inputfields in 1 column of the database.
      Scenario:
      The user can enter one or more opening times per day in a UI.
      Fe:
      Monday open from 08:00 to 12:00 and from 14:00 to 17:00 Tuesday open from 08:00 to 12:00 and from 14:00 to 19:00 and so on
      Via a little JavaScript you can add as much opening times as you need per day - the additional inputfield will be created dynamically.
      After form submission all the values are in the POST array -> this works (see example below):
      ProcessWire\WireInputData Object ( [openinghours_mo-0-start] => 09:00 [openinghours_mo-0-finish] => 13:00 [openinghours_mo-1-start] => 14:00 [openinghours_mo-1-finish] => 18:00 [openinghours_mo-2-start] => 21:00 [openinghours_mo-2-finish] => 23:00 [openinghours_tu-0-start] => 09:00 [openinghours_tu-0-finish] => 13:00 [openinghours_tu-1-start] => 14:00 [openinghours_tu-1-finish] => 18:00 [openinghours_we-0-start] => 09:00 [openinghours_we-0-finish] => 13:00 [openinghours_we-1-start] => 14:00 [openinghours_we-1-finish] => 18:00 [openinghours_th-0-start] => 09:00 [openinghours_th-0-finish] => 13:00 [openinghours_th-1-start] => 14:00 [openinghours_th-1-finish] => 18:00 [openinghours_fr-0-start] => 09:00 [openinghours_fr-0-finish] => 13:00 [openinghours_fr-1-start] => 14:00 [openinghours_fr-1-finish] => 18:00 [openinghours_sa-0-start] => [openinghours_sa-0-finish] => [openinghours_so-0-start] => [openinghours_so-0-finish] => ) The property name is always the name attribute of the field 😉 . If the property is empty means closed on that day.
      Now I need to combine all those values into 1 array (or json array) and store it in the database in 1 column called 'hours' in my case (see screenshot below):

      In my ___processInput(WireInputData $input) method I have tried to make it work like this:
      public function ___processInput(WireInputData $input): self { $name = $this->attr('name'); $value = $this->attr('value'); //input object includes always every input on the page, so lets filter out only inputs from this field //we need to do this, because the number of values is variable - so extract only values that starts with $name.'_' $nameAttributes = []; foreach($input as $key=>$value){ if(substr($key, 0, strlen($name.'_')) === $name.'_'){ $nameAttributes[$key] = $value; } } // loop through all inputfields of this fieldtype $time_values = []; foreach($nameAttributes as $nameAttr => $value) { $time_values[$nameAttr] = $value; } } //save it in the database $input->set('hours', serialize($time_values)); return $this; } The only important part of this code is the last part with the serialize function.
      After saving it will create a record in the database, but the value is always NULL (default value) (see below).

      Checking $time_values returns all the values, but printing out "$this" shows me that the property "hours" inside the Openinghours object is empty (see below) - so the mistake must be there, but I dont know where?!?!?!?
      [title] => Home [openinghours] => ProcessWire\OpeningHours Object ( [data] => Array ( [hours] => ) ) If I check the sleepValue() method or the sanitizeValue() - they are also empty. So it seems that the values will not reach these methods. I havent found a clear documentation of whats going on behind the saving process of an inputfield.
      As far as I know the saving process starts with the form submission. The values are in the POST array and will be processed by the processInput() method. Before they will be saved in the database they will be sanitized by the sanitizeValue() mehtod and afterwards they will be prepared for storage in the sleepValue() method.  The last step is the storage itself.
      Has someone an idea what is missing by storing values from multiple fields into 1 database column or has someone a working example of such a scenario on github to help me out.
      A clear explanation of the storage process will be also helpful.
      Thanks and best regards
    • By Pip
      Hi Everyone 
      I've been working on Processwire for two months now. Structuring the website as needed. Unsure why but I'm getting this one now. Seems my fields has crashed. 
      I've tried googling some answers but can't seem to find a step by step guide on how to rectify this. Any advise? Practically new on this. 
      TIA. 

    • By Rodd
      Hi everyone!
      I have a website in a production environment and I want to duplicate it in a local environment. I exported the content of the website (with the 'Site Profile Exporter' module) but I cannot use it actually. I've got an issue with the database. I imported this one in MAMP then.

      I also exported the pages (with the 'ProcessPagesExportImport' module), but I cannot import it to my local website because the fields don't exist. So I created this fields, but I have this error :
      How can I use the elements that already exist and are presents in my database? How can I duplicate correctly the templates, fields and pages?
      Thanks by advance
      PS: Sorry if my english is bad
       
    • By jds43
      Hello,
      Does anyone have experience with migrating content from Django to Processwire? Or are there any suggestions for achieving this?
×
×
  • Create New...