Imported-- all reviews listed as "new"

[nathan]

Hi Bob,

After an import, we marked all reviews as read for all users, unfortunately with 20k+ reviews and 200k+ users this has resulted in a table of over 300GB.

Do you have a solution that will enable us to drastically reduce this, such as only showing unread reviews for items newer than 2 weeks old or similar?

Thanks!

 

[Bob]

Showcase uses basically the same read marking system as threads and uses the OPTION setting 'Read Marking Data Lifetime (Days)' to prevent older content from being considered NEW/UNREAD.

Its probably too late now, but what you should have done is evaluate the ITEM Create Date and ignore items older than the 'Read marking Data Lifetime (Days)' so that only items within that period (or even your own custom period like 10 days) would come into play.

Also, if someone hasn't been active in x amount of days (like over 2 weeks, then skip that user as if they do log in, it won't matter because content will be older than the cutoff date.

Hope that makes sense.

 

[Bob]

btw, the xf_nflj_showcase_item_read table gets cleaned up on a daily basis. Anything older than the 'Read Marking Data Lifetime (Days)' is removed.

    /**
     * Clean up tasks that should be done daily. This task cannot be relied on
     * to run daily, consistently.
     */
    public static function runDailyCleanUp()
    {
        $db = XenForo_Application::getDb();

        // delete old showcase read marking data
        $readMarkingCutOff = XenForo_Application::$time - (XenForo_Application::get('options')->readMarkingDataLifetime * 86400);
        $db->delete('xf_nflj_showcase_item_read', 'item_read_date < ' . $readMarkingCutOff);
    }

 

[nathan]

That helps a lot! Thanks!

 

[Bob]

That helps a lot! Thanks!

Let me know how it works out!

 

[nathan]

It worked!

That said, we tried deleting an item today and got this error.

Server Error
Mysqli statement execute error : BIGINT UNSIGNED value is out of range in '(`stylefor_xf_final`.`xf_user`.`showcase_count` - 1)'

1. Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
2. Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
3. Zend_Db_Adapter_Abstract->query() in NFLJ/Showcase/DataWriter/Item.php at line 1122
4. NFLJ_Showcase_DataWriter_Item->_itemRemoved() in NFLJ/Showcase/DataWriter/Item.php at line 666
5. NFLJ_Showcase_DataWriter_Item->_postSave() in XenForo/DataWriter.php at line 1423
6. XenForo_DataWriter->save() in NFLJ/Showcase/ControllerPublic/Showcase.php at line 2631
7. NFLJ_Showcase_ControllerPublic_Showcase->actionDelete() in XenForo/FrontController.php at line 351
8. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
9. XenForo_FrontController->run() in /home/ourdomain/public_html/index.php at line 13

We're finding that it gives us this error anytime we try to delete any item.

 

[Bob]

Did you run all the Showcase REBUILDS after you imported? One of the Rebuilds is making sure that all the COUNTS in the xf_user table are correct.

 

[nathan]

I thought we did, but I'll try running it now. Thanks!

 

[nathan]

Fixed! Thanks!