Announcement

Collapse
No announcement yet.

Vbulletin MySql Question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Vbulletin MySql Question

    As admin of a vbulletin5 forum, I've been asked to "dot" a users older posts as they wish to keep their wild youth out of the eyes of potential employers.They basically want to get rid of all their content prior to this year.

    There's no function to do this on vbulletin admin panel that I can see (pruning doesn't seem to be able to do quite what I want to do) however, I cannot see a reason why the below wouldn't work.

    Vbulletin 5's DB schema is a bit more complex to me than vb3 that I was used to, and I just want to see if my code below is ok. My forum is well over a million posts, and this particular user has over 30k posts, so it has the potential to cause big problems if done wrong. Any help appreciated.

    UPDATE
    `text`
    SET
    `rawtext` = "..."
    WHERE
    `text`.`nodeid` IN(
    SELECT
    t.nodeid
    FROM
    `node` AS n
    JOIN `text` AS t
    ON
    (n.nodeid = t.nodeid)
    WHERE
    n.userid = <Userid> AND FROM_UNIXTIME(n.created) < DATE_SUB(NOW(), INTERVAL 6 MONTH))

    or

    UPDATE `text`
    as T
    inner join
    (
    SELECT
    t.nodeid as TODOT,
    "..." as TEXT
    FROM
    `node` AS n
    JOIN `text` AS t
    ON
    (n.nodeid = t.nodeid)
    WHERE
    n.userid = <UserId>
    and FROM_UNIXTIME(n.created)<DATE_SUB(now(), INTERVAL 6 MONTH) U
    on T.`nodeid`=U.TODOT
    SET t.`rawtext`=u.TEXT


  • #2
    The queries as written have operational errors. However, without errors you should be able to craft a query.

    I'd suggest creating a query to create a list and verifying it against your live data before running the update. You should also make a backup of your database before running a query to update 30,000 posts.

    I've modified the first query so it runs on MySQL:

    Code:
    UPDATE `text` SET `rawtext` = "...", pagetext = "...", previewtext = "..."
      WHERE `text`.`nodeid` IN
      (
          SELECT n.nodeid FROM `node` AS n
            WHERE n.userid = %userid% AND FROM_UNIXTIME(n.created) > DATE_SUB(NOW(), INTERVAL 6 MONTH)
      )
    You cannot join in `text` in the sub-query and doing so doesn't actually provide any benefit. Though your test query could be something like:

    Code:
    SELECT n.nodeid, n.authorname, t.rawtext FROM `node` AS n
      JOIN `text` AS t ON (n.nodeid = t.nodeid)
      WHERE n.userid = %userid% AND FROM_UNIXTIME(n.created) > DATE_SUB(NOW(), INTERVAL 6 MONTH))
    Replace %userid% with the actual userid.
    Wayne Luke
    vBulletin Technical Support Lead
    Last edited by Wayne Luke; Wed 16 Jun '21, 9:58am.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment


    • #3
      You'll probably have to truncate your cacheevent and cache tables for changes to take effect as well.
      Translations provided by Google.

      Wayne Luke
      The Rabid Badger - a vBulletin Cloud demonstration site.
      vBulletin 5 API

      Comment


      • #4
        That's great, thanks for the detailed response!

        Comment

        Related Topics

        Collapse

        Working...
        X