Update action hook is called BEFORE updating when used in combination with recursive relationships and MySQL

[christianrr]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

We have the following setup:

• Recursive O2M-relationship with tree view interface configured
• Now we're using tree view interface to change some child
• If we then query the item in update action hook (= AFTER hook), ItemsService returns the values BEFORE changing (it's the same if we query the database directly with knex)

Bug does happen with MySQL adapter, does not happen with SqLite.
Bug is also happening with O2M interface, so it might be related with the recursive relationship - maybe update order?

Everything is working as expected for "normal" O2M relationships...

To Reproduce

• Create Table pages
• Create Field title
• Create Field subpages-> Tree view (relationship to pages, fk page_id, display template title)
• Add hook as below
• Add items (min. 1 level deep), e.g. Root -> Child 1
• Save items
• Go to Root element und use tree view interface to change Child 1's title to Child1 - changed
• Save items
• Hook still returns Child1 instead of changed title (see below)

    action('pages.items.update', async ({ keys }, { schema, accountability }) => {
        const itemsService = new ItemsService('pages', {
            schema: schema,
            accountability: accountability
        });

        for(let i=0; i<keys.length; i++) {
            let key = keys[i];
            
            const item = await itemsService.readOne(key, {
                fields: ["id", "title"],
            });
            
            console.log('changed item:', item);
        }
    });

Hook update (title should be "Child1 - changed")

changed item: { id: 2, title: 'Child 1' }
changed item: { id: 1, title: 'Root' }

Errors Shown

No response

What version of Directus are you using?

9.2.2

What version of Node.js are you using?

12.2

What database are you using?

MySQL 5.7

What browser are you using?

Chrome

What operating system are you using?

macOS

How are you deploying Directus?

locally