Update 3.5.2 link-click subscribe confirmation result in error optedin column missing

OK. So I updated to 3.2.5 - and user sign-up was broken. It would not confirm by clicking the link in the subscription response email. Worse - a user would not know it had NOT, and was told it HAD been confirmed.

On trying to confirm a subscription by clicking the link given in the email, the online confirmation page SAID it had, but at the top was a message “database error 1054 while doing query Unknown column ‘optedin’ in field list.” Which meant that the displayed confirmation was false - it had NOT been registered in the database for that user. OH NO!

Here is a change that is important if you have somehow - as I discovered - do not have the the column “optedin” in the phplist_user_user table. It had worked fine until the update. It just left the “Did this subscriber manually confirm” line in on the User profile blank, and I was none the wiser for a long time past.

Here’s why the confirmation bombed.

Compare the index.php file in the updated 3.2.5 with an earlier one for the subscription click-confirmation action. A subtle change took place. Whereas the earlier version did two sql_queries, they were combined into one for the latest version. Fortunately the earlier version had a comment drawing attention to this pending change. Earlier, confirmed and blacklisted were set in their own sql_query. But earlier optedin had its own separate sql_query to set it. Presumably I ran ok in the past - none the wiser - because the separate query for optedin was intended to allow earlier versions to cope without bombing if that column was missing for cases when some earlier database update may not have happened. I don’t know why it wasn’t there for me, but I never noticed it. Until the update.

The remedy appeared to be to manually add the table entry using PHPmyadmin. The structure.php file in the admin folder has the parameters for it. In the phplist_user_user table, I added a column between blacklisted and bouncecount, named the new column optedin with type TINYINT, and at first set it to NULL yes. But I came back later and changed the NULL column to “as defined” and set it as 0.

The link in the email then worked to confirm, and I saw it on the User Profile page. Also, the database structure check was happy since I also added the many missing indexes indicated in the 3.5.2 structure.php arrays. Running the Rebuild DB Indexes had listed a pile of indexes that it SAID were being added. But they were not, in fact added. So I hope I got them added manually ok. I have yet to find out. My next mail queue runs tomorrow.

Why was my optedin column missing? I have no idea. Perhaps something happened when versions 1.6 and 1.7 forked and I tried 1.7. Just a guess.

By a fluke, on installing the update, I had a whim to add my gmail address as a newly subscribed user. I wanted to see the newsletter on my cell phone display. But the expected message never came. I checked why and found out everything above.

This is likely a rarely encountered problem, but just in case it happens to somebody else, I here record what I did. No promises that I was smart enough to do it right, though. Use at your own risk.

You didn’t say from which release you upgraded. The “optedin” column was added in release 2.11.3 so, unless you upgraded from a 2.10.x release, that column should already have been present.
Regardless, there will have been a prompt to upgrade the database. Did you do that on this upgrade and all previous ones?

When installing the 3.2.5 - yes, I certainly did click the button to upgrade the database. Clearly, there is some oversight in how that script is written or it should have seen and entered the missing column and indexes. I’ve installed new releases sporadically since 1.x. Then 2.x. The previous one may have been 3.0.0. I skipped some because from years ago I had written tweaks in the scripts that I did not want to lose. Although there may now be plugin(s) that I can adopt for that in the future.