SQL Error when editing email template

[LukeDouglas]

So I made a change to the description of an email template but when I went to save the change, I got the following error.  Anyone have a clue what went wrong?

 

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'blindcopyto' in 'field list' in /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php:462
Stack trace:
#0 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(462): PDO->prepare('update `tblemai...')
#1 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(706): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\MySqlConnection), 'update `tblemai...', Array)
#2 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('update `tblemai...', Array, Object(Closure))
#3 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(467): Illuminate\Database\Connection->run('update `tblemai...', Array, Object(Closure))
#4 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(410): Illuminate\Database\Connection->affectingStatement('update `tblemai...', Array)
#5 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Query/Builder.php(2071): Illuminate\Database\Connection->update('update `tblemai...', Array)
#6 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Eloquent/Builder.php(531): Illuminate\Database\Query\Builder->update(Array)
#7 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Eloquent/Model.php(1551): Illuminate\Database\Eloquent\Builder->update(Array)
#8 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Eloquent/Model.php(1470): Illuminate\Database\Eloquent\Model->performUpdate(Object(Illuminate\Database\Eloquent\Builder), Array)
#9 /home/XXXXXXXX/public_html/billing/administrator/configemailtemplates.php(0): Illuminate\Database\Eloquent\Model->save()
#10 {main}
Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'blindcopyto' in 'field list' (SQL: update `tblemailtemplates` set `updated_at` = 2019-01-19 00:19:43, `blindcopyto` = where `id` = 29) in /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php:713
Stack trace:
#0 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(669): Illuminate\Database\Connection->runQueryCallback('update `tblemai...', Array, Object(Closure))
#1 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(467): Illuminate\Database\Connection->run('update `tblemai...', Array, Object(Closure))
#2 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php(410): Illuminate\Database\Connection->affectingStatement('update `tblemai...', Array)
#3 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Query/Builder.php(2071): Illuminate\Database\Connection->update('update `tblemai...', Array)
#4 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Eloquent/Builder.php(531): Illuminate\Database\Query\Builder->update(Array)
#5 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Eloquent/Model.php(1551): Illuminate\Database\Eloquent\Builder->update(Array)
#6 /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Eloquent/Model.php(1470): Illuminate\Database\Eloquent\Model->performUpdate(Object(Illuminate\Database\Eloquent\Builder), Array)
#7 /home/XXXXXXXX/public_html/billing/administrator/configemailtemplates.php(0): Illuminate\Database\Eloquent\Model->save()
#8 {main}

 

[brian!]

10 hours ago, LukeDouglas said:

So I made a change to the description of an email template but when I went to save the change, I got the following error.  Anyone have a clue what went wrong?

what exactly did you do... and more importantly, can you undo whatever you did by returning it to how it was ??

the error makes some sense because there is no 'blindcopyto' field in the tblemailtemplates database table - it's called 'blind_copy_to' and i've seen WHMCS throw SQL errors before (in v7) with table fields that have underscores in them.

I can only think of three practical solutions to this...

1. 'undo' whatever you did in the first place.
2. restore the table as was from a previous backup.
3. open a ticket with support.

[LukeDouglas]

Well, it's pretty darn impossible to undo what has been done.  As far as restoring the table from a previous backup, I'm not sure exactly what that would accomplish as the table structure hasn't changed. I'll open a ticket with support.

 

[WHMCS ChrisD]

@lukedouglas as mentioned by Brian it seems that the blindcopyto field is missing from your tblemailtemplates table you can re-add this using PHPMyAdmin if you have a support ticket open our team can assist

[LukeDouglas]

ChrisD,

I downloaded the 7.6.1 version, extracted to a folder and looked at the 'install.sql'.  Here is the tblemailtemplates table setup.  There is NO "blindcopyto" field.  I checked the entire install.sql file and there is no 'blindcopyto' field at all in any table.  🙂

I added the field, edited a template and it saved the changes.

 

-- 
-- Table structure for table `tblemailtemplates`
-- 

CREATE TABLE IF NOT EXISTS `tblemailtemplates` (
  `id` int(1) unsigned zerofill NOT NULL auto_increment,
  `type` text COLLATE utf8_unicode_ci NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `subject` text COLLATE utf8_unicode_ci NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `fromname` text COLLATE utf8_unicode_ci NOT NULL,
  `fromemail` text COLLATE utf8_unicode_ci NOT NULL,
  `disabled` text COLLATE utf8_unicode_ci NOT NULL,
  `custom` text COLLATE utf8_unicode_ci NOT NULL,
  `language` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

[brian!]

14 hours ago, LukeDouglas said:

Well, it's pretty darn impossible to undo what has been done. 

depends what you did in the first place - only you know what you did!

14 hours ago, LukeDouglas said:

As far as restoring the table from a previous backup, I'm not sure exactly what that would accomplish as the table structure hasn't changed.

see above.

6 hours ago, LukeDouglas said:

I downloaded the 7.6.1 version, extracted to a folder and looked at the 'install.sql'.  Here is the tblemailtemplates table setup.  There is NO "blindcopyto" field.  I checked the entire install.sql file and there is no 'blindcopyto' field at all in any table.

you should have taken a look in /resources/sql/install/tblemailtemplates.schema.sql...

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `tblemailtemplates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tblemailtemplates` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `type` text COLLATE utf8_unicode_ci NOT NULL,
  `name` text COLLATE utf8_unicode_ci NOT NULL,
  `subject` text COLLATE utf8_unicode_ci NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  `attachments` text COLLATE utf8_unicode_ci NOT NULL,
  `fromname` text COLLATE utf8_unicode_ci NOT NULL,
  `fromemail` text COLLATE utf8_unicode_ci NOT NULL,
  `disabled` tinyint(1) NOT NULL,
  `custom` tinyint(1) NOT NULL,
  `language` text COLLATE utf8_unicode_ci NOT NULL,
  `copyto` text COLLATE utf8_unicode_ci NOT NULL,
  `blind_copy_to` text COLLATE utf8_unicode_ci NOT NULL,
  `plaintext` tinyint(1) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `type` (`type`(32)),
  KEY `name` (`name`(64))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

sadly, I think I assumed that you had already checked whether the database field actually existed (If I got an error saying a table doesn't exist, it would have been my first thought to double-check that!) - if you had told me neither existed, I could have pointed you to the above. 🙄

[LukeDouglas]

My confusion is the field in the "tblemailtemplates.schema.sql." is shown as 'blind_copy_to' yet the previous recommendation was to add the field 'blindcopyto'.  Once I added the field 'blindcopyto', I was able to edit the template normally without any errors.  This was in reference to 'Brian!' response concerning the error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'blindcopyto' in 'field list' in /home/XXXXXXXX/public_html/billing/vendor/illuminate/database/Connection.php:462

Then a WHMCS support person sent me an update to a ticket with the comment "I found that the "blind_copy_to" field was missing from your tblemailtemplates database table. I have created this for you now."  When I checked the table, the 'blindcopyto' field name was now replaced by 'blind_copy_to'.  So I edited a template and it saved normally without any errors.

Now I have done application development for about 30+ years of my life and I'm pretty darn sure that 'blindcopyto' and 'blind_copy_to' would be viewed as two completely different fields in code.

So how in the heck does both work without one of them throwing an error?

 

Edited January 21, 2019 by LukeDouglas

[brian!]

Hi Luke,

18 hours ago, LukeDouglas said:

My confusion is the field in the "tblemailtemplates.schema.sql." is shown as 'blind_copy_to' yet the previous recommendation was to add the field 'blindcopyto'.  Once I added the field 'blindcopyto', I was able to edit the template normally without any errors.

at it's core, and I can't prove this as I can't see the code, but I think this is an upgrade issue because if this was a fresh installation, I wouldn't expect this to occur (as the installer should create all the tables with the correct fields)... yet i've seen this occur a number of times with upgrades - the BCT feature wasn't added until v7.3 I think, so upgrades from that point on should have adjusted the database tables to add these new fields.. but if for whatever reason, the tables weren't adjusted correctly, you may not notice until you try to get the email templates to do something new... hence why I asked what you did that caused this - not 'cause' in the sense that it's your fault, the chances are that these table errors were always there since an incomplete / corrupted upgrade process.

18 hours ago, LukeDouglas said:

Then a WHMCS support person sent me an update to a ticket with the comment "I found that the "blind_copy_to" field was missing from your tblemailtemplates database table. I have created this for you now."  When I checked the table, the 'blindcopyto' field name was now replaced by 'blind_copy_to'.  So I edited a template and it saved normally without any errors.

yeah, I thought Chris' advice was wrong (I put it down to a typo on his part) as this issue has been raised a number of times...

18 hours ago, LukeDouglas said:

So how in the heck does both work without one of them throwing an error?

as I said, i've seen that type of error occur elsewhere (can't find the threads though) with other SQL queries where the field should contain underscores, but WHMCS is seemingly trying to find an equivalent field without the underscores... could be a bug in the core code; it could be how the errors are being reported by WHMCS - that's really one for the WHMCS developers to figure out themselves as only they know their own code... the rest of us can only try to infer what's going on from the error messages generated. 🙄

btw - if you ever get a spare few moments, i'd suggest that you double-check your other database table structures with those schemas just to ensure that there aren't any more missing fields elsewhere.