Unable to login to ACP got Error SQL ERROR [ mysqli ] Unknown column 'user_email_hash' in 'where clause' [1054]

WebDoc121 · Post by **WebDoc121** » Thu Jan 09, 2020 4:36 pm

Hi, I have recently upgraded my clients board from 3.2.8 to 3.2.9 by adopting the official Guide. Everything seems fine, but when I tried to login the board ACP, then the login screen appears again and again. When I try to use the forget password option, then I got this error.

SQL ERROR [ mysqli ]

Unknown column 'user_email_hash' in 'where clause' [1054]

An SQL error occurred while fetching this page. Please contact the Board Administrator if this problem persists.

I tried to register as a new user, then I also got the same error.

Board URL is

https://forum.PAKDEFENSE.com

Please try to solve this problem....

Regards,

EA117 · Post by **EA117** » Thu Jan 09, 2020 6:04 pm

The user_email_hash column was indeed being intentionally removed, as part of https://tracker.phpbb.com/browse/PHPBB3-16167. But that change was ultimately released in phpBB 3.3.0, not phpBB 3.2.9. Taking a quick look at the official phpBB 3.2.9 release code, I don't see where anything about that change "leaked" into the phpBB 3.2.9 release. The column is not removed by phpBB 3.2.9 migration.

What is the phpBB version showing as in your ACP? (e.g. just "3.2.9", or maybe "3.2.9-RC1", etc.) Was there possibly an attempt to go to phpBB 3.3.0 first, and somehow later decided to use phpBB 3.2.9 instead? Just trying to picture reasons why the column would now be missing on a phpBB 3.2.9 board.

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 4:46 am

I cannot login to ACP and I have uploaded the 3.2.9 files in the board, so I am sure its version is 3.2.9.

I performed the clean installation via FZ FTP. Everything was fine during the install and I don't see any error. Now, this problem comes from nowhere.

I have also checked by uploading the cookie.php file, but cookie settings are same as before.

Now, I don't know what to do...

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 4:56 am

WebDoc121 wrote: ↑
Fri Jan 10, 2020 4:46 am
I cannot login to ACP and I have uploaded the 3.2.9 files in the board, so I am sure its version is 3.2.9.

I performed the clean installation via FZ FTP. Everything was fine during the install and I don't see any error. Now, this problem comes from nowhere.

I have also checked by uploading the cookie.php file, but cookie settings are same as before.

I have also try to repair the database tables in mysql, but problem stays where it was before

Now, I don't know what to do...

EA117 · Post by **EA117** » Fri Jan 10, 2020 5:22 am

If you don't think you've had significant activity on the board since the update, I'd probably just use the file system and database backup to return the board to it's original phpBB 3.2.8 state, and then probably also look at the phpbb_users table to confirm that the user_email_hash column really was there prior to the update. Before then performing the phpBB 3.2.9 update again, since I have no reason to think that installing phpBB 3.2.9 will remove that column. Maybe download and extract the phpBB 3.2.9 files for upload again, just as a safety measure.

If users have been at the board for a while, there could be something more surgical you could do. But it's a bit risky since who knows what else is actually wrong right now besides the one thing you've noticed. But you could make a database backup of the phpBB 3.2.9 board, and use a good comparison program (BeyondCompare, or your favorite diff tool) to compare that backup to the pre-upgrade phpBB 3.2.8 database backup. To get a sense of whether it's really just "that one column got dropped for some reason, but everything else is just expected user activity since the update." Versus whether you're seeing a lot of other columns and/or rows missing from important tables, and this happens to be "the first one we've noticed."

In the extremely lucky event you find that it really is just this one column missing, it looks like if you put the empty column back using phpMyAdmin or similar, there appears to be a phpBB /bin/phpbbcli.php fixup:recalculate-email-hash CLI option that would let you regenerate the data for specifically this column.

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 6:06 am

This method is very much complicated. Is there any simple way around to solve the issue.

warmweer · Post by **warmweer** » Fri Jan 10, 2020 8:23 am

WebDoc121 wrote: ↑
Fri Jan 10, 2020 6:06 am
This method is very much complicated. Is there any simple way around to solve the issue.

???
I think EA117 explained a) a possible cause and b) a solution quite well.
The bottom line is that there is a problem and in order to fix that you'll either have to retry the update (with the risk that the problem persists) or follow EA117's advice and risk identifying the "origin" of the cause and being able to solving it permanently.
The method described is not complicated at all, in fact I had a different method in mind, involving a different (perhaps slightly more thorough) comparison method which would probably yield the same results but take 20x longer.
And if you think EA117's method is complicated, then you certainly don't want to try my alternative

(which is a lot more intrusive).

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 9:51 am

I have also saved the backup before installing the update. How can I restore the backup?

janus_zonstraal · Post by **janus_zonstraal** » Fri Jan 10, 2020 10:17 am

WebDoc121 wrote: ↑
Fri Jan 10, 2020 9:51 am
I have also saved the backup before installing the update. How can I restore the backup?

Did you backup the files and the database?

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 11:11 am

Yes, I have backup the database before updating the board via ACP backup and restore option and I have also made backup of the files such as config.php, store, images, files, etc

janus_zonstraal · Post by **janus_zonstraal** » Fri Jan 10, 2020 12:08 pm

Sorry I don't know if you can restore a backup from a other version in the ACP.
I always restore the database in phpmyadmin.
Maybe someone else knows this.

Otherwise you have to download the backup, you find him in the store folder.
and upload them in phpmyadmin.
Also restore all the files from your old version (not only the config.php, store, images, files, etc)

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 12:21 pm

Let's say I restore the backup using phpmyadmin. Will this resolve the missing database table issue permanently?

janus_zonstraal · Post by **janus_zonstraal** » Fri Jan 10, 2020 12:24 pm

No it only restore the board to the last version, keep in mind that you also loose all the post made since the update!

WebDoc121 · Post by **WebDoc121** » Fri Jan 10, 2020 12:41 pm

The all issue is missing one column database table user_hash_email. will Restoring the backup resolve the issue?

david63 · Post by **david63** » Fri Jan 10, 2020 12:51 pm

WebDoc121 wrote: ↑
Fri Jan 10, 2020 12:41 pm
will Restoring the backup resolve the issue?

It might. It will also depend on whether that column in the user table is in the backup that you use.

advertisements