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

[imthebest]

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

 

[Chris D]

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?

 

[imthebest]

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?

 

[Chris D]

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.

 

[imthebest]

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).

What date is being displayed?

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

And where is it being displayed?

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



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

What is the last_update_date timestamp for the affected albums in the database?

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).

 

[Chris D]

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).

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.

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).

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

 

[imthebest]

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!

 

[Chris D]

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

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.

 

[imthebest]

Here you go:

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.

 

[Chris D]

This was actually an issue in the album thumbnail builder.

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

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
)

 

[imthebest]

I ran the query and got the following output:

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?

 

[Chris D]

Yes, likely to be empty albums.

I would recommend running this query:

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.

 

[imthebest]

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?

 

[Chris D]

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

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

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

 

[imthebest]

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

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:

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.

 

[imthebest]

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?

Just wondering if this was expected. Thanks!

 

[Chris D]

Yes