Mysql Error In Delivery/spc.php: Missing Azoneinfo['trafficker_Account_Id']

[mbreid]

Hello,

 

We manage a few different Revive AdServer instances (all v3.0.2), but on one of them I have been noticing some pretty frequent errors. NOTE, all were previous OpenX upgrades.

 

As far as I can tell, everything appears to be working as expected, but our logs are catching MySQL errors from delivery/spc.php 

 

 

The error is due to invalid SQL syntax, resulting from an empty trafficker_account_id:

$aZoneInfo['trafficker_account_id'] // trafficker_account_id does not exist!

Here is a snippet of the failing SQL:

        SELECT
            'default_banner_destination_url_trafficker' AS item,
            apa.value AS value
        FROM
            `ox_account_preference_assoc` AS apa
        WHERE
            apa.account_id =
            AND
            apa.preference_id = 2
        UNION
        SELECT
            'default_banner_destination_url_manager' AS item,
            apa.value AS value
        FROM
            `ox_account_preference_assoc` AS apa
        WHERE
            apa.account_id =
            AND
            apa.preference_id = 2
        UNION
        SELECT
        ...

Link to the full script on GitHub: https://github.com/revive-adserver/revive-adserver/blob/master/www/delivery/spc.php#L740

 

Does anyone have any insight into why this trafficker_account_id would not be populated? I would imagine it's mostly likely an issue with our configuration or banner / zone setup, and not related to the migration to Revive.

 

Any recommendations on what I could check to help correct these errors would be appreciated.

 

Regards,

- Mike

[mbreid]

UPDATE

 

It dawned on me after posting this detail that I could trace trafficker_account_id back to its source. A few lines up, I can see where it is coming from:

SELECT
    ...
    a.account_id AS trafficker_account_id,
    ...
FROM
    `ox_zones` AS z,
    `ox_affiliates` AS a,
    `ox_agency` AS m
WHERE
    ...

...it appears to be the ox_affiliates.account_id.

 

I will log some data and see if I can isolate which zone_id is behind this error, and will report back.

[mbreid]

SOLVED

 

It turns out this MySQL error is related to using JavaScript code referencing a Zone ID that no longer exists in ox_zones.

 

Question for the Revive devs: is there a more elegant way at possibly catching this error and bringing attention to it vs. the MySQL error as a result of an empty trafficker_account_id? Perhaps there is already something in place to identify this scenario that I am simply overlooking?

 

ISSUE #290 filed here: https://github.com/revive-adserver/revive-adserver/issues/290