It's been some years that I had some incomplete entities, back in the days of some Elgg 1.x. I guess it was due to some server glitches back then. The DbValidator plugin helped to sort out the issue.

The version to be fount in the github repo you've linked should still work on Elgg 2.x (probably NOT 3.x or newer anymore). The plugin has two options "validate" or "repair". You can try a validate run first. If it works without (php) errors the repair option probably works, too. In any case, backup your database before doing a repair run!!

Thanks iionly!

I tried the dbvalidator plugin on Elgg 2.x site and it works like a charm.

or is there a better/systematic approach to update the DB charset?

Update to Elgg 3.3 ;)

A few changes were made to the MySQL schema

• http://learn.elgg.org/en/stable/appendix/upgrade-notes/2.x-to-3.0.html#schema-changes
• http://learn.elgg.org/en/stable/appendix/upgrade-notes/2.x-to-3.0.html#new-mysql-schema-features-are-not-applied

@Nikolai, thanks for the discussion link. 

@Jerome, "Update to Elgg 3.3 ;)" will definitely solve this problem :) I have 2 servers running Elgg 3.3.15 and there are no charset issues. However, I still have three servers running Elgg 2.3.x which are planned to upgrade to Elgg 3.3.x. Thanks for the links to the upgrade notes.

Cheers!

Just wanted to share with Elgg community if someone wanted to get utf8mb4 working on Elgg 2.3.x. It is impossible to achieve this unless you modify core code (engine/classes/Elgg/Database.php) which is not recommended by the Core developer team. So, the only alternative is to upgrade to Elgg 3.x as Jerome suggested above.