Can't get into admin after upgrade - "WARNING: An Error occurred, please refresh"

Hey Zen Stars,

HyosungSource.com
1.5.5f
No Plugins
PHP 7.1
Ubuntu 18.04.1

After upgrading (from 1.3.9 I think), I can't log into my admin.

"WARNING: An Error occurred, please refresh the page and try again." when I enter my password at the Admin login screen.

I made sure that both auto_loaders folders are complete, they match the 1.5.5f .zip file.

The class.zcPassword.php file is present and seems OK.

Has anyone seen this problem before?

I'm really at my wits end.

The error message I'm getting is this:

[25-Sep-2018 09:51:33 UTC] Request URI: /admin/login.php?camefrom=index.php, IP address: 120.17.22.188
#1 trigger_error() called at [/srv/w/hyosungsource.com/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/srv/w/hyosungsource.com/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/srv/w/hyosungsource.com/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/srv/w/hyosungsource.com/includes/classes/class.zcPassword.php:206]
#5 zcPassword->confirmDbSchema() called at [/srv/w/hyosungsource.com/includes/classes/class.zcPassword.php:186]
#6 zcPassword->updateNotLoggedInAdminPassword() called at [/srv/w/hyosungsource.com/admin/includes/functions/admin_access.php:339]
#7 zen_validate_user_login() called at [/srv/w/hyosungsource.com/admin/login.php:36]

[25-Sep-2018 09:51:33 UTC] PHP Fatal error: 1067:Invalid default value for 'pwd_last_change_date' :: ALTER TABLE admin MODIFY admin_pass VARCHAR( 255 ) NOT NULL DEFAULT '' ==> (as called by) /srv/w/hyosungsource.com/includes/classes/class.zcPassword.php on line 206 <== in /srv/w/hyosungsource.com/includes/classes/db/mysql/query_factory.php on line 171

Any help is so much appreciated!

Did you run zc_install to upgrade the database?

Yes, I did. It had to go through multiple steps, coming from 1.3.9 to 1.5.5.

But everything seemed to be successful in that.

Does this look right?

mysql> describe admin;
+----------------------+----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------------------+----------------+
| admin_id | int(11) | NO | PRI | NULL | auto_increment |
| admin_name | varchar(32) | NO | MUL | | |
| admin_email | varchar(96) | NO | MUL | | |
| admin_profile | int(11) | NO | MUL | 0 | |
| admin_pass | varchar(40) | NO | | | |
| prev_pass1 | varchar(40) | NO | | | |
| prev_pass2 | varchar(40) | NO | | | |
| prev_pass3 | varchar(40) | NO | | | |
| pwd_last_change_date | datetime | NO | | 0000-00-00 00:00:00 | |
| reset_token | varchar(60) | NO | | | |
| last_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| last_login_date | datetime | NO | | 0000-00-00 00:00:00 | |
| last_login_ip | varchar(15) | NO | | | |
| failed_logins | smallint(4) unsigned | NO | | 0 | |
| lockout_expires | int(11) | NO | | 0 | |
| last_failed_attempt | datetime | NO | | 0000-00-00 00:00:00 | |
| last_failed_ip | varchar(15) | NO | | | |
+----------------------+----------------------+------+-----+---------------------+----------------+
17 rows in set (0.00 sec)

As relates to this issue, no. Should have:

pwd_last_change_date datetime NOT NULL default '0001-01-01 00:00:00'

All date/time type fields with a default should look similar to above...

The upgrade script should have altered the date fields when it did the step from 1.3.9 to 1.5.0.

If have a backup of the database to try on, could load the old database and then again run zc_install on it and as design75 stated, when mysql_upgrade_zencart_150.sql is executed, that field and several others should get/have been modified.

Shouldn't this query do it?

ALTER TABLE admin MODIFY pwd_last_change_date DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00';

It keeps saying "ERROR 1067 (42000): Invalid default value for 'last_modified'", what a weird error message, I'm tearing my hair out!

I'd rather not run the zc_install again if one query might do it, not least because I ran it before, so why would it produce different results?

Is there a chance that a proper ALTER TABLE query could fix this?

That error message is different and is yet another one of the fields that should have been identified to get upgraded. Problem is/can be that if there had been sufficient queries run to update database related fields in order to support continued operation of older software, then the upgrade process may not have identified the need to execute say the 1.5.0 upgrade script.

Might recommend reviewing the queries of the 1.5.0 update (can be found in zc_install/sql/update) and likely would need to run each... now before going and blindly doing that, there is the possibility that one of those changes gets modified in a later upgrade script. So may need to look at the later script(s) as well to validate the end state of the database table(s).

Alternatively, a new database could be created using the ZC 1.5.5 tables and the end state of the tables/fields could be applied to your current database so that at least the existing fields would match regarding default values. (not to remove any existing field/table and suggested to compare so as not to truncate any field that was purposefully made longer.)

The other reason to "run" the zc_install for upgrade was so that could know for certain the sequence of actions considering the uncertainty of where/how things started out.

Besides, if you're following along with the recommended instruction for upgrading by rebuilding: http://www.zen-cart.com/entry.php?3-How-do-I-rebuild-my-site-on-the-new-version-instead-of-upgrading

You're going to be running the zc_install to upgrade the database more than once. As many times as necessary on a test installation to get it right, and then once on the live site applying all the things necessary to do it once with the live site.

Great, thanks so much!

I didn't roll the database back, I just reuploaded the zc_install folder and ran it again.

I thought every step of the database upgrade had done, but for some reason it hadn't.

Just doing zc_install again fixed it. You guys are absolute legends, I can't believe how fast you answered. Thank you so much.

Glad that it was an easy fix. Part of being in the community is to help out where/when can. Anyone can do that to the extent they have an idea of how to help. Maybe you'll be able to help someone that comes across the same issue or one you've encountered before.