1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fixed [vBulletin 3.x importer] Wrong album last update time

Discussion in 'Media Gallery Resolved Bugs' started by imthebest, Apr 14, 2015.

  1. imthebest

    imthebest Formerly Super120

    I noticed this the day after I migrated but somehow forgot to report.

    vBulletin pictures inside user albums are being imported with the proper last update time. However the last update time for vBulletin albums is being set by XFMG as the day when the import was done.

    The last update time for vBulletin albums should be set as the day when the last picture inside that album was added.

    If I'm right and it is indeed a bug, after you fix it please provide a query to run in order to make the XFMG to properly set the last update time for my albums as the day when the last picture inside that album was added instead of the day when the import was done.

    Thank you,
    Super120
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    I can't currently see an issue here.

    The last_update_date comes from a value we get from the "albumupdate" table in vB3.

    What date is being displayed? And where is it being displayed? What is the last_update_date timestamp for the affected albums in the database?
     
  3. imthebest

    imthebest Formerly Super120

    Well I did the migration with one of the latest (if not the latest) version of XMG before it got renamed to XFMG. Maybe the problem was identified and solved in the next releases?

    Could you please provide the SQL query to get the value of last_update_date for a specific album?
     
  4. Chris D

    Chris D XenForo Developer Staff Member

    I have already checked that possibility. The pre release code where the importer was first introduced hasn't actually changed.

    No I can't provide that at the moment.

    Though if you answer my other questions I can have a look later for you.
     
  5. imthebest

    imthebest Formerly Super120

    Just being curious, is there an internal policy for you the devs to not provide SQL queries? I have noticed that Mike also doesn't gives SQL queries... whenever there is a request for one it usually comes from Brogan but never from Mike (and now, neither from you).

    "XX Oct 2014", which is the day when I performed the migration. XX is a number of two digits.

    At community/media/albums and when viewing the albums tab at members/someone.123. In fact, in every place where there is the following:

    Untitled.png

    Also when viewing the album itself at the "Last Update" line inside the "Information" block.

    Can't answer that if I'm not provided with the SQL query. If you can't do it publicly but you need that information then please start a conversation with me since I can't (I don't have permissions to do that).
     
    Last edited: Apr 16, 2015
  6. Chris D

    Chris D XenForo Developer Staff Member

    We'll all provide queries where it is appropriate but many times it isn't appropriate.

    Regardless, that's not the issue here, I just said I can't at the moment but I should be able to later when I'm not on my phone.

    I've already told you the field name. It's in the xengallery_albums table. The create date might be worth looking at too.
     
  7. imthebest

    imthebest Formerly Super120

    Back in my vBulletin days I was so so able to write SQL queries but now I forgot how to do it and that's why I'm asking for them. Anyway I have no problems to wait until you get back to your PC. Thanks!
     
  8. Chris D

    Chris D XenForo Developer Staff Member

    I'm certain you know how to look at a table and give me a few pieces of data.

    Code:
    SELECT * FROM xengallery_albums WHERE album_id = 99999
    where 99999 is an affected album ID.

    Let me know the create date and last update dates of a few affected albums.
     
  9. imthebest

    imthebest Formerly Super120

    Here you go:

    Code:
    SELECT * FROM xengallery_album WHERE album_id = 9;
    album_create_date=1206319625 (shows in the UI as 23 Mar 2008)
    last_update_date=1413671668 (shows in the UI as XX Oct 2014)
    
    SELECT * FROM xengallery_album WHERE album_id = 114;
    album_create_date=1214324365 (shows in the UI as 24 Jun 2008)
    last_update_date=1413671668 (shows in the UI as XX Oct 2014)
    
    SELECT * FROM xengallery_album WHERE album_id = 7168;
    album_create_date=1257473510 (shows in the UI as 5 Nov 2009)
    last_update_date=1413671758 (shows in the UI as XX Oct 2014)
    Where "XX" is the day of the migration.

    Edit: I think it's in fact a bug because those last update timestamps all point to XX Oct 2014.
     
    Last edited: Apr 16, 2015
  10. Chris D

    Chris D XenForo Developer Staff Member

    This was actually an issue in the album thumbnail builder.

    This query, should fix the problem. Please back up first:

    Code:
    UPDATE xengallery_album
    SET last_update_date = (
        SELECT media_date
        FROM xengallery_media
        WHERE xengallery_media.album_id = xengallery_album.album_id
        ORDER BY media_date DESC
        LIMIT 1
    )
     
  11. imthebest

    imthebest Formerly Super120

    I ran the query and got the following output:

    Code:
    Query OK, 11025 rows affected, 1971 warnings (0.71 sec)
    Rows matched: 11025  Changed: 11025  Warnings: 1971
    I don't know what those 1,971 warnings stand for and unfortunately nothing got logged on my mysqld.log file however I noticed that albums without media got the following new last update date: Dec 31, 1969. Maybe those 1,971 warnings are those empty albums?
     
  12. Chris D

    Chris D XenForo Developer Staff Member

    Yes, likely to be empty albums.

    I would recommend running this query:

    Code:
    UPDATE xengallery_albums
    SET last_update_date = album_create_date
    WHERE last_update_date = 0
    I did think of that the other day but forgot to include it.
     
  13. imthebest

    imthebest Formerly Super120

    So to be safe first I run the original query and then the second query, right?

    Isn't possible to do it with a single query?
     
  14. Chris D

    Chris D XenForo Developer Staff Member

    It sounds like you've already run the first query.

    If you haven't then you could do it with:

    Code:
    UPDATE xengallery_album
    SET last_update_date = (
        SELECT media_date
        FROM xengallery_media
        WHERE xengallery_media.album_id = xengallery_album.album_id
        ORDER BY media_date DESC
        LIMIT 1
    )
    WHERE album_media_count > 0
     
  15. imthebest

    imthebest Formerly Super120

    Okay so I ran the query you provided in your last message with the following results:

    Code:
    Query OK, 8968 rows affected (2 min 58.22 sec)
    Rows matched: 8968  Changed: 8968  Warnings: 0
    
    Off topic question: 3 minutes for running that query is expected due to its complexity and being uncommon thus not having the proper indexes, right?

    Now back on topic, for anyone following this issue, after you run the query provided by Chris you need to run this second query:

    Code:
    UPDATE xengallery_album SET last_update_date = album_create_date WHERE album_media_count = 0;
    That will set the last update time for empty albums to be the same as the creation date.
     
    Last edited: Apr 19, 2015
  16. imthebest

    imthebest Formerly Super120

    Just wondering if this was expected. Thanks!
     
  17. Chris D

    Chris D XenForo Developer Staff Member

Share This Page