Database Error after updating to latest version

[jynxy]

Hi,

Updated a clients account from 7.3 to latest version when clicking on an invoice via admin we are getting the following error, i cant seem to track down why. does anyone have any ideas ?

Thanks

 

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoice_id' in 'where clause' in /home/username/public_html/vendor/illuminate/database/Connection.php:331
Stack trace:
#0 /home/username/public_html/vendor/illuminate/database/Connection.php(331): PDO->prepare('select count(*)...')
#1 /home/username/public_html/vendor/illuminate/database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('select count(*)...', Array)
#2 /home/username/public_html/vendor/illuminate/database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('select count(*)...', Array, Object(Closure))
#3 /home/username/public_html/vendor/illuminate/database/Connection.php(339): Illuminate\Database\Connection->run('select count(*)...', Array, Object(Closure))
#4 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2202): Illuminate\Database\Connection->select('select count(*)...', Array, true)
#5 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2190): Illuminate\Database\Query\Builder->runSelect()
#6 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2685): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#7 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2191): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#8 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2612): Illuminate\Database\Query\Builder->get(Array)
#9 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2540): Illuminate\Database\Query\Builder->aggregate('count', Array)
#10 /home/username/public_html/vendor/illuminate/database/Eloquent/Builder.php(1403): Illuminate\Database\Query\Builder->count()
#11 /home/username/public_html/adminhvr5/invoices.php(0): Illuminate\Database\Eloquent\Builder->__call('count', Array)
#12 {main}
Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoice_id' in 'where clause' (SQL: select count(*) as aggregate from `tblaffiliateshistory` where `invoice_id` = 102483) in /home/username/public_html/vendor/illuminate/database/Connection.php:671
Stack trace:
#0 /home/username/public_html/vendor/illuminate/database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('select count(*)...', Array, Object(Closure))
#1 /home/username/public_html/vendor/illuminate/database/Connection.php(339): Illuminate\Database\Connection->run('select count(*)...', Array, Object(Closure))
#2 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2202): Illuminate\Database\Connection->select('select count(*)...', Array, true)
#3 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2190): Illuminate\Database\Query\Builder->runSelect()
#4 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2685): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2191): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#6 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2612): Illuminate\Database\Query\Builder->get(Array)
#7 /home/username/public_html/vendor/illuminate/database/Query/Builder.php(2540): Illuminate\Database\Query\Builder->aggregate('count', Array)
#8 /home/username/public_html/vendor/illuminate/database/Eloquent/Builder.php(1403): Illuminate\Database\Query\Builder->count()
#9 /home/username/public_html/adminhvr5/invoices.php(0): Illuminate\Database\Eloquent\Builder->__call('count', Array)
#10 {main}

 

[Bigol'tastynuggets]

Restore from back up and try again, welcome to whmcs! 

8.3 hasn't been around long, although it's released for general availability it's considered a beta when the plan is to release patches when users have found bugs. Sadly the community aren't as keen to help any more and the more competent people involved in whmcs have gone therefore leaving us in a cycle of terrible updates that let us give them more money, which they claim we want yet have a striking inability to do anything meaningful. 

Sadly, billing and invoicing seem to be an afterthought. Aswell as it working  🙂

[Evolve Web Hosting]

2 hours ago, Bigol'tastynuggets said:

Restore from back up and try again, welcome to whmcs! 

8.3 hasn't been around long, although it's released for general availability it's considered a beta when the plan is to release patches when users have found bugs. Sadly the community aren't as keen to help any more and the more competent people involved in whmcs have gone therefore leaving us in a cycle of terrible updates that let us give them more money, which they claim we want yet have a striking inability to do anything meaningful. 

Sadly, billing and invoicing seem to be an afterthought. Aswell as it working  🙂

I guess we've been lucky. 8.3 has been good overall. We don't use MarketConnect or a handful of the other features but the ones that we do use are working properly.

[Evolve Web Hosting]

5 hours ago, jynxy said:

Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoice_id' in 'where clause' (SQL: select count(*) as aggregate from `tblaffiliateshistory` where `invoice_id` = 102483) in /home/username/public_html/vendor/illuminate/database/Connection.php:671

Take a look at the structure for your tblaffiliateshistory

It should look like the structure shown in the image I've attached. I think you may be missing the invoice_id column

[Bigol'tastynuggets]

33 minutes ago, evolve hosting said:

I guess we've been lucky. 8.3 has been good overall. We don't use MarketConnect or a handful of the other features but the ones that we do use are working properly.

Do you use it in production already? 

[Evolve Web Hosting]

2 minutes ago, Bigol'tastynuggets said:

Do you use it in production already? 

Yes. For 3 or 4 weeks now.

[Bigol'tastynuggets]

That's good to know! Enjoy! 

[maren]

Hey!

Same issue here, except that the invoice_id column DOES exist. Also, manually executing the query against the database returns 0 (not an error), which is to be expected since we have the affiliate system disabled.

Here's a sample error from our system, although it looks like the same issue to me (says no invoice_id column in tblaffiliateshistory)
 

Quote

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoice_id' in 'where clause' in /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php:331
Stack trace:
#0 /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php(331): PDO->prepare('select count(*)...')
#1 /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('select count(*)...', Array)
#2 /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('select count(*)...', Array, Object(Closure))
#3 /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php(339): Illuminate\Database\Connection->run('select count(*)...', Array, Object(Closure))
#4 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2202): Illuminate\Database\Connection->select('select count(*)...', Array, true)
#5 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2190): Illuminate\Database\Query\Builder->runSelect()
#6 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2685): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#7 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2191): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#8 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2612): Illuminate\Database\Query\Builder->get(Array)
#9 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2540): Illuminate\Database\Query\Builder->aggregate('count', Array)
#10 /home/username/domains/primary/public_html/vendor/illuminate/database/Eloquent/Builder.php(1403): Illuminate\Database\Query\Builder->count()
#11 /home/username/domains/primary/public_html/admin/invoices.php(0): Illuminate\Database\Eloquent\Builder->__call('count', Array)
#12 {main}
Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoice_id' in 'where clause' (SQL: select count(*) as aggregate from `tblaffiliateshistory` where `invoice_id` = 2195) in /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php:671
Stack trace:
#0 /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('select count(*)...', Array, Object(Closure))
#1 /home/username/domains/primary/public_html/vendor/illuminate/database/Connection.php(339): Illuminate\Database\Connection->run('select count(*)...', Array, Object(Closure))
#2 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2202): Illuminate\Database\Connection->select('select count(*)...', Array, true)
#3 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2190): Illuminate\Database\Query\Builder->runSelect()
#4 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2685): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2191): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#6 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2612): Illuminate\Database\Query\Builder->get(Array)
#7 /home/username/domains/primary/public_html/vendor/illuminate/database/Query/Builder.php(2540): Illuminate\Database\Query\Builder->aggregate('count', Array)
#8 /home/username/domains/primary/public_html/vendor/illuminate/database/Eloquent/Builder.php(1403): Illuminate\Database\Query\Builder->count()
#9 /home/username/domains/primary/public_html/admin/invoices.php(0): Illuminate\Database\Eloquent\Builder->__call('count', Array)
#10 {main}

Cheers!

[WHMCS John]

Hi all,

This error indicates that one or more of the database schema changes was not applied during the update for some reason. Possibly a server timeout killed the update process prematurely.

The query to create the field (plus the others) is located in the /resources/sql/upgrade830alpha1.sql file.

Creating the missing field will resolve the error:

ALTER TABLE `tblaffiliateshistory` ADD `invoice_id` int(10) unsigned NOT NULL DEFAULT 0 AFTER `affaccid`;

As always, before making changes to the database, please backup.