Column not found error

[iHelpersLLC]

I got this error today (below, and edited for brevity) when trying to add a new customer.  It created/added the customer but i'm guessing it was just this one record that didnt get added.. I'm hoping this table just needs to be added into the database or something easy like that?

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'marketingemailsoptin' in 'field list' in /home/vendor/illuminate/database/Connection.php:462
WHMCS\User\Client->marketingEmailOptOut('71.x.x.210', false)
Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'marketingemailsoptin' in 'field list' (SQL: update `tblclients` set `updated_at` = 2018-09-04 16:25:26, `marketingemailsoptin` = 0 where `id` = 104) in /home/

 

 

[brian!]

i'm wondering if you need to run the Marketing Email Opt-In Conversion process.. ?

[iHelpersLLC]

Thanks..

I ran the conversion process but i'm still seeing the same error when adding a new client.

[brian!]

25 minutes ago, iHelpersLLC said:

I ran the conversion process but i'm still seeing the same error when adding a new client.

have you examined the tblclients database table to check that it doesn't exist? if it's missing from there, then that may mean that the v7.6 update didn't complete properly as it didn't convert the table to the new columns.

if that's occuring, then Support might be able to give you a SQL query to fix the table structure.

[iHelpersLLC]

Just checked and it's not there, this is very similar to an issue I had upgrading 7.5.2->7.6 which also had missing entries:

 

So now i'm sure that adding that column will fix it - but I need the syntax.. If @string isnt around I'll contact support.

[brian!]

if it helps...

it's basically just a boolean field (0/1)... if this is the only column missing, you should be able to insert it manually using phpmyadmin without the need for SQL.

that said, if you're still using v7.6, I might be tempted to get the full v7.6.1 download, install that and see if that updates the database structure for you.

[iHelpersLLC]

I guess i dont know how to add a table in PHPMyadmin (although i've done it many times in the past) .. if anyone could post the SQL for this, that would be much helpful.

 

BTW - the board/community appears to be borked today so hopefully this posts..

[brian!]

20 hours ago, iHelpersLLC said:

I guess i dont know how to add a table in PHPMyadmin (although i've done it many times in the past) ..

it's not a new table, just a new column in the tblclients table... you could use phpmyadmin to add it - perhaps the video tutorial below might walk you through it.

 

[iHelpersLLC]

Duh.. thank you.

Can anyone else confirm the name the column?  In my error it's shown as "marketingemailsopin" but in @brian! 's post its shown as marketing_emails_opt_in  ..

Would i just create the name that the error indicates it's looking for?

 

Edited September 29, 2018 by iHelpersLLC

[iHelpersLLC]

Ok, after some coffee i added the column, but now i get this error, which tells me something went wrong/was missed at some point during one of the upgrades:

Base table or view not found: 1146 Table 'whmcs.tblmarketing_consent' doesn't exist

[brian!]

15 minutes ago, iHelpersLLC said:

Base table or view not found: 1146 Table 'whmcs.tblmarketing_consent' doesn't exist

then you'll probably need to create the table 'tblmarketing_consent' manually.

[iHelpersLLC]

Thanks.. I just opened a support ticket, but if you can give me that SQL I'll add it and make the support-guy's lives easier!

 

[brian!]

phpmyadmin says it should be this...

CREATE TABLE `tblmarketing_consent` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `opt_in` tinyint(1) NOT NULL DEFAULT '0',
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `ip_address` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci	

13 minutes ago, iHelpersLLC said:

make the support-guy's lives easier!

i'm not sure that I agree with that policy!

[iHelpersLLC]

Woot!