re-setting of privileges each puppet run after adding privileges for another database to puppet controlled user

[o4ier]

Describe the Bug

Creating a new, additional database on mysqlserver (by hand) and granting puppet controlled user privileges on it causes setting of privileges on each puppet run, because the set privileges are not evaluated correctly

Expected Behavior

granting a puppet controlled user privileges on another databases should not cause endless re-setting of privileges for that user

Steps to Reproduce

Steps to reproduce the behavior:

1. create a database (eg. database1) with this module
2. create a user (eg. user1) with this module
3. grant user1 privileges (eg. 'ALL') on database1 with this module
4. run puppet multiple times -> no more changes after initial set up
5. create a new database (eg. database2) by hand on the system
6. grant user1 privileges (eg. 'ALL') on database2
7. run puppet (multiple times) -> each puppet run changes the privileges for user1@database1

Environment

• Version 1.13.2.0
• Platform RHEL 8 and 9

Additional Context

I still have another issue ( #1502 ) /pr ( #1503 ) open/existing, which somehow got lost on my desk.
Can I reopen the pr and use it to fix both issues?
Or should I create a new pr for this issue, after the first one is accepted after i fixed it?

[o4ier]

Fix for this is available in https://github.com/o4ier/puppetlabs-mysql/tree/o4ier-patch-2

[fetzerms]

@o4ier did you find out why this behaves differently on "externally" created databases? We encountered the same issue in some RHEL7->RHEL8 migrated database, while it works on others.

Update: Running mysql_upgrade fixed the issue for "old" databases.

[o4ier]

@fetzerms The problem was that when "show grants" for a specific user produces output with more than one row, only the last row was evaluated by the module.
So puppet always thinks "user1 has no grants on database1" and sets them each time.

In our use case the first row is the database managed by puppet, as we don't support automatic installations with multiple users/databases. But we cannot prevent that somebody else creates new databases and grants.

I don't know how the "mysql way" of doing such things is in theory (like "a new user for each database"), but in practice it happens that one user has grants on multiple databases.

Maybe the ordering of grants output in your setup was not strictly "puppet controlled first", so puppet evaluated the correct row by chance on some systems, as it was the last one.

[daylicron]

Hi @o4ier ,
thanks for your work so far, we're hitting the same issue. Do you might have some time to open a PR for this? We would really appretiate this. 👍

[o4ier]

@daylicron as the changes made in my fix are dependent on a "broken" PR, I am currently not able to integrate my changes into the current main-Codebase, which looks very different than the state it was in when I wrote my fix.

But feel free to fork, add my changes, and open a PR