Migrate to an Ubuntu 16.04 server

[ron4mac]

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.

[gmc]

That is a strict standards change on MySQL:

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

[ron4mac]

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.

[ron4mac]

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.

[peterleinen]

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

[ron4mac]

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.

[phill104]

Nice work sir. Maybe we should start to open up the 1.6.x forums for testing and bug reports such as this.

[ron4mac]

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.

[peterleinen]

Ron,

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

You have seen this actually in my gallery?

Peter

[ron4mac]

You have seen this actually in my gallery?

Yes.  Look at the intermediate view of any photo.

[peterleinen]

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

[ron4mac]

I was accessing it by IP ... didn't know you had transferred domain.

[:thumbs-up:]