Trouble with Backup Restore

[iceman]

I am running the same gallery using duplicate EE2.01 installations on two separate severs at two different host. This way I have a backup gallery on a separate server if the primary fails. (just a different URL)

I had both EE2.01 galleries running fine. In an effort to duplicate the primary gallery database, I did a backup using the backup module within EE on the primary gallery and attempted to restore it to the backup gallery and recieved the following message:
Could not retrieve authorization data, can not proceed.
Perhaps 'ee_admin' table is missing (wrong EE database version)? Is database online? Is it EE database?

Any idea what I did wrong, I now get this message with anything I attempt to call up on the backup gallery.
Before I start digging myself a deeper hole, I thought I would ask.

[iceman]

I have a frash install of EE2.01 that works fine with no data in it. I have tried to load a backup file from my primary gallery onto this new backup gallery which is on a different server, and still get the message:
Could not retrieve authorization data, can not proceed.
Perhaps 'ee_admin' table is missing (wrong EE database version)? Is database online? Is it EE database?
I have rebuilt the new EE2.01 with blank data again a would like to know the best way to move the backup file from my primary gallery to this new EE2.01. EE Backup does not seem to work in this situation.
Any suggestions?

[yosemite]

how are you trying to restore? to a local machine you are running or to a hosted account? through phpmyadmin? please provide more details. are you using the right db name, user name and password?

[iceman]

how are you trying to restore? to a local machine you are running or to a hosted account? through phpmyadmin? please provide more details. are you using the right db name, user name and password?

I am trying to restore the backup file that was generated by the DB Backup page within EE2 on the primary host site, to the DB Backup page withing EE2 on the secondary host site Thereby generating a duplicate database on the backup. EE2 is running fine on the secondary server with the same database name prior to an attempted restore. I have not tried PHPMyadmin yet.

I have done this in the past using EE1.5 with no problem. If it cannot be done, than I will just upload the images twice to each site, not a big deal.

The reason that I am doing this is so I have a duplicate EE running if the primary crashes.

thanks

[yosemite]

i would try using phpmyadmin. 1st download the db from your original one then do a db bu on the the one you are trying to restore and then see if you can import the other db in. you may get errors such as "cant create table" etc so you may have to change the settings for your export such as "Structure: Add DROP TABLE, Add IF NOT EXISTS". i have gone as far as deleting all tables in the db and then doing a import. you can also try pasting the sql text into the "Run SQL query/queries on database..." window.

[iceman]

i would try using phpmyadmin. 1st download the db from your original one then do a db bu on the the one you are trying to restore and then see if you can import the other db in. you may get errors such as "cant create table" etc so you may have to change the settings for your export such as "Structure: Add DROP TABLE, Add IF NOT EXISTS". i have gone as far as deleting all tables in the db and then doing a import. you can also try pasting the sql text into the "Run SQL query/queries on database..." window.

Thanks, it's about time I learned more about PHPMyadmin anyway, kinda scarry, but, what the heck.

[Pekka]

I am running the same gallery using duplicate EE2.01 installations on two separate severs at two different host. This way I have a backup gallery on a separate server if the primary fails. (just a different URL)

I had both EE2.01 galleries running fine. In an effort to duplicate the primary gallery database, I did a backup using the backup module within EE on the primary gallery and attempted to restore it to the backup gallery and recieved the following message:
Could not retrieve authorization data, can not proceed.
Perhaps 'ee_admin' table is missing (wrong EE database version)? Is database online? Is it EE database?

Any idea what I did wrong, I now get this message with anything I attempt to call up on the backup gallery.
Before I start digging myself a deeper hole, I thought I would ask.

If you use EE 2's EE Backup on a database without installed EE; you must set in backup/index.php line 2

$with_ee = "1";

to

$with_ee = "0";

This bypasses admin login check (which is not there yet, obviously :) ).

[iceman]

If you use EE 2's EE Backup on a database without installed EE; you must set in backup/index.php line 2

$with_ee = "1";

to

$with_ee = "0";

This bypasses admin login check (which is not there yet, obviously :) ).

OK, great -- Thanks Pekka

[DavidW]

I am running the same gallery using duplicate EE2.01 installations on two separate severs at two different host. This way I have a backup gallery on a separate server if the primary fails. (just a different URL)
If you want to take one server and make a duplicate on another URL, with separate MySQL and FTP servers, there's a bit of patching up to do.


I finally implemented the debugging server I've been talking about in the forums today - well, all apart from getting a debugging extension into PHP, which remains to be done. I'd previously set up an ezjail on my FreeBSD box, then installed and configured MySQL 5, PHP 5 and Apache 2.2 in that jail. I added proftpd 1.3 today, then set to work on a system automagically to make a copy of my 'live' gallery on that server.


The solution I settled on was a UNIX shell script that copied the directory structure and files using rsync over SSH (which moves not just the files, but permissions and ownership as well). rsync is custom designed for this job - but you can only use it if the remote server is set up to accept rsync connections. My hosting provider is sensible enough to support rsync, though you can only rsync over SSH, which means you need a shell account. Using SSH is wise anyway from a security point of view.

The script continues by patching basecode/SETUP_settings.php and [management_folder]/backup/ee_backup_prefs/preferences.php for the change in MySQL server and (for the sake of being completely tidy) the cookie string as well. For this task, I'm using perl -i -p -e's/from_regex/to_regex/g' filename (sed would work instead of perl, but you'll have to go via a temporary file if you use sed).


The next job is to copy the database. This is easily done using mysqldump piped into mysql - that is mysqldump --host=oldserver.invalid --user=user --no_create_db databasename | mysql -h newserver.invalid -u user databasename

That particular command line assumes that the database is already created, the user's password is set and appropriate permissions have GRANTed to that user (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, LOCK TABLES) for the database. You also need the password to both servers available - ideally by sticking it in ~/.my.cnf (well, that's where it goes on UNIX, anyway). It's important not to include the password on the command line of a shared UNIX box, or, for that matter, put it in an environment variable.

Finally, the database needs patching up for the change of servers, which I did with a file of SQL commands.


The shell script is custom to my system and rather scrappily written; it's a load of commands thrown together in a file rather than a model of good programming. If anyone wants to know how to set up rsync over SSH and authenticate the SSH connection using a public key, I'll happily share that, but I don't think the rest is that much use to anyone else as it stands.

However, the SQL commands are a little more elegant, and I'm happy to share that here, not least for Pekka's comments in case he thinks I've missed anything or should change anything.

# Patch the database to move EE to a different server
#
# Edit the lines below, then, at a shell prompt
#
# mysql -h server -u username -p databasename < sqlfile
# (drop the -p if you have the password in ~/.my.cnf)
#
# David Wood, October 2006
# DavidW on POTN forums

# Host names to update HTTP URLs
SET @oldserver := 'photos.oldserver.invalid';
SET @newserver := 'photos.newserver.invalid';

# Host names to update FTP servers
SET @oldftp := 'ftp.oldserver.invalid';
SET @newftp := 'ftp.newserver.invalid';

# DON'T EDIT ANYTHING BELOW HERE
# Wipe various tables that maybe shouldn't be carried over
DELETE FROM `ee_backup`;
DELETE FROM `ee_backup_info`;
DELETE FROM `ee_message`;
DELETE FROM `ee_performance`;
DELETE FROM `ee_uncookied`;

# Update the global settings
UPDATE `ee_settings` SET
`ee_galleryroot` = INSERT(`ee_galleryroot`,
LOCATE(@oldserver, `ee_galleryroot`),
LENGTH(@oldserver), @newserver);

# Update the FTP servers
UPDATE `ee_settings` SET
`ee_ftp_servername` = INSERT(`ee_ftp_servername`,
LOCATE(@oldftp, `ee_ftp_servername`),
LENGTH(@oldftp), @newftp)
WHERE LOCATE(@oldftp, `ee_ftp_servername`);

UPDATE `ee_ftp` SET
`ee_ftp_server` = INSERT(`ee_ftp_server`,
LOCATE(@oldftp, `ee_ftp_server`),
LENGTH(@oldftp), @newftp)
WHERE LOCATE(@oldftp, `ee_ftp_server`);

# Update the path URLs
UPDATE `ee_size` SET
`ee_size_url` = INSERT(`ee_size_url`,
LOCATE(@oldserver, `ee_size_url`),
LENGTH(@oldserver), @newserver)
WHERE LOCATE(@oldserver, `ee_size_url`);

UPDATE `ee_thumb_path` SET
`ee_thumb_path_url` = INSERT(`ee_thumb_path_url`,
LOCATE(@oldserver, `ee_thumb_path_url`),
LENGTH(@oldserver), @newserver)
WHERE LOCATE(@oldserver, `ee_thumb_path_url`);

UPDATE `ee_microthumb_path` SET
`ee_microthumb_path_url` = INSERT(`ee_microthumb_path_url`,
LOCATE(@oldserver, `ee_microthumb_path_url`),
LENGTH(@oldserver), @newserver)
WHERE LOCATE(@oldserver, `ee_microthumb_path_url`);

If I've missed an easier way to replace a substring within a string in MySQL, I now have egg on my face in public. This was written and used under MySQL 5.0 - I don't think I've used anything particularly 5.0 specific, but if I have maybe someone will let me know what isn't supported on older MySQL versions.


Copy and paste that into a file, edit the lines under the comments for the old and new servers, then follow the instructions in the comments to run the resulting commands.

You will need a command line mysql program. You can get a binary for Windows, assuming, of course, that you have the appropriate permissions over the MySQL database from your machine. I simply ran this at the shell prompt on my FreeBSD box.

I could recast it into PHP, but I really can't see the point, especially as this is the sort of thing that for a backup server you may well want to run from a crontab. It's going to finish up as a series of SQL commands in any case - so I figured that I may as well code it as a series of SQL commands. I've used variables to save too much messing around when adapting it for different situations, as well as for clarity.



David

[DavidW]

Just as a few further thoughts on that SQL code - if you want to change user names or passwords for the FTP server, you can do that by adding extra statements before WHERE LOCATE(@oldftp...

There's an argument that I should match outright on the FTP server names (but the code as it is allows you to change a sub-string - for example, you can have ftp1.test.invalid and ftp2.test.invalid - as written you can change test.invalid to production.invalid).

There's also one or two assumptions that I haven't listed out explicitly, but I doubt they'll apply to many people. Feel free to modify the SQL as written or share modified versions if that's useful.



David

[evdh]

Sometimes I've the same problem :
Sometimes I've the same problem :
Could not retrieve authorization data, can not proceed.
Perhaps 'ee_admin' table is missing (wrong EE database version)? Is database online? Is it EE database?
This is what I do :
- Login --> no problem
- Creating an Exhibition --> no problem
- Uploading photo by FTP --> no problem
- Adding these photos to the database in the exhibition (the first 20) --> no problem
- An perhaps uploading the next 20 --> no problem

And then I get that error. While I'm adding photos to the database I get that error. But when I wait some time (30min - 60min) I can start adding photos again. No actions taken. Just wait and all is back to normal...

[evdh]

I still have this problem. Any update/solution ?

[Pekka]

Sometimes I've the same problem :

This is what I do :
- Login --> no problem
- Creating an Exhibition --> no problem
- Uploading photo by FTP --> no problem
- Adding these photos to the database in the exhibition (the first 20) --> no problem
- An perhaps uploading the next 20 --> no problem

And then I get that error. While I'm adding photos to the database I get that error. But when I wait some time (30min - 60min) I can start adding photos again. No actions taken. Just wait and all is back to normal...

My guess is that you have a query or connection limiter on (for the user EE logs with to database) in your MySQL. It works per "user", and limits number of queries per hour. MySQL/server admin can set up this limit in e.g. phpMyAdmin.

Limits are:

MAX QUERIES PER HOUR
MAX UPDATES PER HOUR
MAX CONNECTIONS PER HOUR
MAX USER_CONNECTIONS

[evdh]

When I can access the database again with that user I will take a look at it. Thanks for the feedback...

[evdh]

Setting or parameter not found...;-(
Can you help me...

[evdh]

This is the error I get in myPHPadmin :

Server: custsql-pow02
Error
SQL query:

SET NAMES utf8;

MySQL said:

#1226 - User 'xxx' has exceeded the 'max_questions' resource (current value: 75000)

[evdh]

This come back from support :

Our records show that that the MySQL queries are exceeding our limits. This is the reason you are receiving error message as 'exceeded the 'max_questions' resource' while accessing phpMyAdmin tool. On our server the maximum number of database queries per user is 75,000/hr.

As a temporary fix, you can delete and recreate your user however this error will still come back if you do not condense the amount of queries your script uses by optimizing your SQL
usage. Below is a link that should help, show you how to optimize your MySQL usage:
http://www.sitepoint.com/article/optimizing-mysql-application . Currently, I was able to access the phpMyAdmin tool for the database user 'xxx'.

Pekka, can you please take a look at this one because 75000 SQL queries is a lot just for uploading 20 photos.
If you need more info from my side, please let me know...

More feedback in this thread... http://photography-on-the.net/forum/showthread.php?p=11182814