Articles missing after exporting from a Joomla 3 database and reimporting them in Joomla 4

Recently during a lenghtly upgrading process of an old Joomla 3 site to Joomla 4 I have run into an unusual issue. During the process of rebuilding/rebranding the site a large number of articles where added to the original site, and obviously they are missing from the new site. I imported articles directly in the database into the #__content table like I often did in Joomla 3 - a smooth process generally. However even the database looked ok, the new articles in Joomla 4 they were not visible.

My first idea was that the culprit is the missing assets, so I used my preferred tool to fix the assets table. Unfortunately that did not find the issue.

After lot of digging I found a clue. By searching in the database to see what's different regarding the old, visible articles and the newly imported ones I discovered, that the problem might be a strange one: the old articles have references in the #_workflow_associations table, and the new articles does not have these references.

Weird, at least, because I did not used at all the Workflows feature.  But I decided, that worth a try, and inserted a new line in the given table, something like this:

INSERT INTO `MYPREFIX_workflow_associations` (`item_id`, `stage_id`, `extension`) VALUES ('MISSINGID', '1', 'com_content.article');

where MYPREFIX is the database prefix used in my Joomla install, and MISSINGID is one of article ID's apparently missing form backend.

And problem has been solved: the article is now showing up in backend and in frontend correctly.

BTW, frontend: was really misleading, that all of this time some of modules on the site where able to correclty show the articles missing from backend.

So, if you experience a similar situation, try this query (adapt it to your needs - replace MYPREFIX with your actual database prefix):

INSERT INTO MYPREFIX_workflow_associations (item_id, stage_id, extension) 
SELECT c.id as item_id, '1', 'com_content.article' FROM MYPREFIX_content AS c 
WHERE NOT EXISTS (SELECT wa.item_id FROM MYPREFIX_workflow_associations AS wa WHERE wa.item_id = c.id);