forum.coppermine-gallery.net

Support => cpg1.5.x Support => cpg1.5 install => Topic started by: peterleinen on January 22, 2017, 02:13:33 pm

Title: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 22, 2017, 02:13:33 pm
Hi all,

I got responsible for a Galerie (1.5.8) running on an old debian system. the first task to do is to migrate from this server to an new one, running on Ubuntu LTS 16.04.

My knowledge so far:
  1) The version 1.5.8 does not run on this system because it can't connect to the mysql database.
  2) I got the version  v1.6.01 running on that Ubuntu system by a fresh install of v1.6.01, but was not able to include the database into this.

After a day of reading a lot of documents with the topics install, upgrade, update, I can't get it running.

As time is short before the old server will die, any hints are welcome.

Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 22, 2017, 05:47:41 pm
The best bet for you to succeed is to first update the old server gallery to 1.5.44.
http://documentation.coppermine-gallery.net/en/upgrading.htm#upgrade_any
Make sure to run the update.php file at each phase of the update.

Once at a functioning version of 1.5.44, dump (export) the database from there (see help in documentation). Copy the albums folder to the new server and import the database to the new server. You'll probably have to set things correctly in include/config.inc.php. Then run update.php on the new server (CPG 1.6.01) version.

You'll need to make any necessary correction in the config/general section of the new CPG version as to the gallery URL.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 22, 2017, 06:16:39 pm
Hi ron4mac,

thanks for your quick reply.

As far as I understood the version 1.5.44 need the mysql-interface, which is no longer available under php-7. Ubuntu 16.04 comes with php.7.0 as default - no way to go back on version 5 on this server for several reasons.

Deadlock?

Regards
  Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: Phill Luckhurst on January 22, 2017, 06:33:32 pm
What ron4mac is suggesting is you get the version on your old server up to date first. This will help resolve a number of issues before you migrate. There have been quite a few DB changes between the version you have and the latest version in the 1.5.x line.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 23, 2017, 09:47:53 pm
Hi,

sorry my fault, I should read twice before posting.

I hope I got it.

The install.php fill an empty database, the update.php use an existing?

Regards
  Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 24, 2017, 02:43:04 am
The install.php fill an empty database, the update.php use an existing?

1. Dump (export) the database from CPG 1.5.44 running on the old server
2. Copy the albums folder from old server to new server
3. Import database to new server
4. Point CPG 1.6.01 on the new server to the database that was imported by making changes to include/config.inc.php
5. Run update.php in CPG 1.6.01

Once you're up and running, you can remove the database that was created by the install of CPG 1.6.01.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 26, 2017, 09:08:05 pm
Hello.

I'm running into a "500 Internal Server Error" .

What I did:

1) Upgrade to 1.5.44 on the old server - with out any problems.

2) Move the albums and the database dump to the new machine

3) Unpack the v1.6.01.zip into the installation directory.

4) Run the install.php and use a temporary database

5) Move the albums to the right place

6) Installl the old database-dump into the mysql system

7) Edit the include/config.inc.php to point to the database created in step 6

8) Run the upgrade.php (no error)

Is this the recommended way?

Observations:

I could log into the system with the admin/admin-password at the beginning of the upgrade-job stored in the database created in step 6
The versioncheck.php shows the startpage with the hint of the maintenance-mode, where it shows the same entries in the top line menu as on the old system.

But I could not log into the system. The window to enter account/password appear as usual, but after clicking "OK" everything disappear and and the error 500 occur in the browser. No error in the apache error log.

May it be no good idea to put the system into maintenance-mode to transfer? Could I get out off that by alter a database entry?

Any help very welcome.

Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: Phill Luckhurst on January 26, 2017, 09:55:01 pm
A 500 error is a bi of a generic catch all error. To know what is going on and why you must get the error from your servers logs. If a 500 error occurs it wlll be logged on your system somewhere. As we do not know your server setup we cannot help much in locating that.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 27, 2017, 06:52:13 am
Hi,

I have done a reinstall with the galerie not in maintenance-mode. I could see and browse all albums.

The 500 error disappear, but not the problem with the login resists.

I have uploaded some information from the apache2 log files and a screenshot from the browser console.

Does this show any hints?

I would like to mention at the moment there is not hostname and no vhost configuration available, so the initial url is
ipadress//hondadeauville-forum.de/login.php. May this cause any error?

Regards
  Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 27, 2017, 02:22:56 pm
Were you able to run the update.php file (it asks you to authenticate)?

I tried to register at the gallery (used the IP address in your screen dump) and it fails with an unknown database error when trying to insert my information.
It refers to /var/www/html/hondadeauville-forum.de/include/functions.inc.php - Line: 253 ... having no host configuration may be contributing to the problem.

Check that the database user has ALL access rights to the database.
Also, check in the logs folder for any information.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 27, 2017, 03:32:27 pm
Hi,

yes I could run the update.php again and I'm able to authenticate.

But when I choose to go to the checkfile-page I go an error saying I'm not allowed to see this page.

Find the errorlogs form apache, mysql and also the debug-output for the not succesful visit to the chekfile-page.

I added also a screenshot.

I have used for the installation.php and update.php the same user, which is again the same inserting the dump from the old server into mysql. And moreover I was able to read all tables, I have not tested in changing anything.

Do you have any hint related to the no host configuration issue?

Regards
  Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 27, 2017, 04:15:35 pm
In the 1.6.01 database, the cpg145_config item fullpath needs to be changed from pointing to the old CPG installation.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 27, 2017, 05:29:43 pm
Also, it will help in your php.ini for you to set display_errors on and error_log to some value ("error_log"?).
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 28, 2017, 10:11:28 am
Hi,

I just tried to register and run also into an error. As the window shows the insert command, I tried this on the mysql-console.

The error is: " ERROR 1054 (42S22): Unknown column 'user_password_salt' in 'field list' "

You will find the command, the error and also the field-lists used in *_users in the original 1.6x (created at the installation) and in the 1.5.44 (prefix: cpg145) created by the update.

I guess, I have to add this column. What is the default value? Does this affect existing user?

Beside this information I also dumped the table *_config in both databases. Please find the sorted files as attachements. Beside some possible security issues I cant find any signifcant difference.

I changed the configuration of the apache and php within apache, but the are no more information, at least at the moment.

Regards
  Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: gmc on January 28, 2017, 02:07:53 pm
The update.php script should take care of any database updates needed - including the addition of new columns... but as ron4mac said:
Quote
Check that the database user has ALL access rights to the database.

The process allows an error - as the SQL will fail if the alter was already done - and update.php can always be rerun. It doesn't differentiate from an authorization failure.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 02:28:08 pm
I believe I have discovered the problem ... I'll have a fix for you in an hour.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 02:50:58 pm
I believe old default time values of '0000-00-00 00:00:00' in your database are causing the problem. When the update tries to add the user_password_salt (and others), mysqli fails because of the bad default datetime value (that is not a valid date in the newer scheme; needs to be '1000-01-01 00:00:00').

In your CPG1.6 install, replace the file sql/update.sql with the one attached here (as update.sql). Run update.php again and see if that makes a difference.

If that doesn't work, you may have to manually change the datetime defaults for CPG145_users, CPG145_comments and CPG145_pictures.

Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 03:20:55 pm
Looking at it further, based on the files you attached ...

It may not be the column definitions at fault (they seem to have correct defaults).  It may be actual values in the data rows.  Any bad time values will keep the table from being updated.  Unfortunately, you'll have to manually look in the tables I referred to above for datetime values that are '0000-00-00 00:00:00' and change them to '1000-01-01 00:00:00'.  Then, run the update.php ... AGAIN!

Once we get it right, you should be able to log in (and register).
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 28, 2017, 03:41:39 pm
Thanks a lot.

Ok, I will go through the tables, i have learned a lot related to mysql today anyway :-)

Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 28, 2017, 03:44:59 pm
I have check the permissions of the user, it tells my

SHOW GRANTS FOR CURRENT_USER;
GRANT ALL PRIVILEGES ON *.* TO 'XXX'@'localhost' WITH GRANT OPTION

and checked the config-file twice.

Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 03:59:05 pm
Because you started with 1.5.8, I think the problem is a cpg145_user row that has '0000-00-00 00:00:00' in user_lastvisit or user_regdate.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: gmc on January 28, 2017, 04:26:56 pm
That is a strict standards change on MySQL:
Quote
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation

MySQL Documentation 5.7 says:

    Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

To Check MYSQL mode

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Disabling STRICT_TRANS_TABLES mode

However to allow the format 0000-00-00 00:00:00you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command

Sounds like we should be changing any datetime of 0000-00-00 00:00:00 for any of our datetime fields to an accepted default as part of update.php to prevent such a problem?? If we are going to support strict standards...
Users can be upgrading from any 1.5.x release.

But if this is in an ALTER statement to add the column - are we specifying the default?  Not at my computer with access to the source currently...
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 05:13:12 pm
Sounds like we should be changing any datetime of 0000-00-00 00:00:00 for any of our datetime fields to an accepted default as part of update.php to prevent such a problem?? If we are going to support strict standards...
Users can be upgrading from any 1.5.x release.

But if this is in an ALTER statement to add the column - are we specifying the default?  Not at my computer with access to the source currently...
Yes, we'll probably need to make that change to the update process. The ALTER TABLE command fails because of the bad date in any of the table rows. This is probably an isolated incident where a user registered and never completed or visited.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 05:38:03 pm
Here is an sql/update.sql file that will (hopefully) fix any bad datetime values in the database.  Put the file in place and run update.php.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 28, 2017, 06:39:23 pm
Hi all,

Iḿ sorry, the update.php wont't be successfully add the right values and fields.

Here is what I have done:

alter table cpg145_users modify column user_lastvisit datetime not null default '1970-01-01 00:00:00';
alter table cpg145_users modify column user_regdate datetime not null default '1970-01-01 00:00:00';

update cpg145_users set user_lastvisit = replace(user_lastvisit,'0000-00-00 00:00:00','1970-00-00 00:00:00');

ALTER TABLE cpg145_users add user_password_salt varchar(255) after user_password;
ALTER TABLE cpg145_users add user_password_hash_algorithm varchar(25) after user_password_salt;
ALTER TABLE cpg145_users add user_password_iterations varchar(25) after user_password_hash_algorithm;

INSERT INTO cpg145_users (user_regdate, user_active, user_actkey, user_name, user_password, user_password_sa ..... as shown in the error field of the registration process.

AND: It works, at least I could login, I could register (except sending a mail), I see the administration interface and all pictures.

BUT now I'm no longer be able to login in at the update.php authentification window :-)

Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 28, 2017, 07:17:31 pm
Glad you got it working!  Sorry for the trouble.

We'll use the information we got from this problem to try make future versions more trouble free.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: Phill Luckhurst on January 28, 2017, 07:39:20 pm
Nice work sir. Maybe we should start to open up the 1.6.x forums for testing and bug reports such as this.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 29, 2017, 05:09:12 pm
Peter,

An additional problem for you to resolve is that your image URLs (in the picture info) point to the old server ... likely solved in config/general settings.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 29, 2017, 05:17:45 pm
Ron,

thanks for the hint. I guess I have fixed it already, but who knows.

You have seen this actually in my gallery?

Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 29, 2017, 07:44:17 pm
You have seen this actually in my gallery?

Yes.  Look at the intermediate view of any photo.
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: peterleinen on January 29, 2017, 08:00:13 pm
Hi,

do you mean the information like: http://www.hondadeauville-forum.de/displayimage.php?pid=12122

You are right, this looks like the old one, there was an additional "/copper" in the URL. I have transferred the domain-name to the new server, too.

Thanks a lot for your careful look
  Peter
Title: Re: Migrate to an Ubuntu 16.04 server
Post by: ron4mac on January 29, 2017, 08:44:04 pm
I was accessing it by IP ... didn't know you had transferred domain.

[:thumbs-up:]