XF 2.2 Message count for user profiles does not update

[Miri]

Hi,
I am having problems with post counts for users after a migration from an unsupported cms. Migration done manually.

If a user posts, the count works, but for those migrated, the system fails to update published posts. No mistakes or things to report.

I also followed the admin side tool but it still fails to update the post count for users.
I found this query (I think it is for version 1 of xenforo) written by @Jake Bunce

Unmaintained - Rebuild user post counts (query)

This will recalculate and update the post counts for all users at once. This is a large query that may take a while to finish. UPDATE xf_user AS user SET message_count = ( SELECT COUNT(*) FROM xf_post AS post LEFT JOIN xf_thread AS thread...

xenforo.com

UPDATE xf_user AS user
SET message_count = (
    SELECT COUNT(*)
    FROM xf_post AS post
    LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
    WHERE post.user_id = user.user_id
    AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
    GROUP BY post.user_id
);

when I run this query, on a test copy of my database, I get 64 alerts

Warning: #1048 Column 'message_count' cannot be null

Not sure if it works on xenforo 2.
Who would be kind enough to help me with such a query, or some other option, to properly update the message count for user profiles?
thanks a lot

Miri

 

[Miri]

I answer myself, I ran this query on my database anyway, and it worked.

obviously I ignored the warning of the first post reported.

now all counters have the correct messages for all users.