mysql_grant is not idempotent

[gildas]

when used as a standalone type or from mysql::db, mysql_grant will always grant the permissions even if these permissions are already granted.
As a consequence, the node is marked as "changed" in Dashboard or any other reporting tool.

mysql_grant should check the permissions first, and grant the new ones only if they are not already granted.

[tekenny]

I'm experiencing the same issue except not for all my grants. All the grants exist in the database however there seems to be a pattern to which grants are idempotent or not.

The following are fine (idempotent): user@fqdn,/., user@10.10.10.%/db.table, user@localhost/.

The following patterns are not idempotent: user@10.10.10.%/., user@%/., user@10.10.10.%/db.*

I hope this helps in some way to determine the root cause.

[apenney]

Thanks for the update! I'll try testing those out and see if I can figure out what's going on here. It should be testing those exist already. Can you do me one favor, get me a puppet resource mysql_grant from the machine with the grants on?

[tekenny]

Here is a snippet of my manifest. I escaped * here for readability. Below the manifest is the puppet resource mysql_grant output.

$dbSubnet = '10.10.10.%'

Mysql_grant {
    ensure     => present,
    options    => ['GRANT'],
    privileges => ['ALL'],
    table      => '*.*',
}

mysql_grant { "user1@${dbSubnet}/\*.\*":
    user       => "user1@${dbSubnet}",
}
mysql_grant { "user2@${dbSubnet}/foo.bar":
    privileges => ['SELECT', 'INSERT', 'UPDATE'],
    user       => "user2@${dbSubnet}",
    table      => 'foo.bar',
}
mysql_grant { "user3@${dbSubnet}/foo.\*":
    privileges => ['SELECT', 'INSERT', 'UPDATE'],
    user       => "user3@${dbSubnet}",
    table      => 'foo.\*',
}
mysql_grant { 'web@%/\*.\*':
    user       => 'web@%',
}
mysql_grant { "web@${dbSubnet}/\*.\*":
    user       => "web@${dbSubnet}",
}
mysql_grant { "web@${fqdn}/\*.\*":
    user       => "web@${fqdn}",
}
mysql_grant { 'web@localhost/\*.\*':
    user       => 'web@localhost',
}

Here is the puppet resource mysql_grant output after the above manifest was applied.

mysql_grant { 'backup@localhost/.':
ensure => 'present',
privileges => ['SELECT', 'RELOAD', 'LOCK TABLES', 'SHOW VIEW'],
table => '.',
user => 'backup@localhost',
}
mysql_grant { 'root@127.0.0.1/.':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => '.',
user => 'root@127.0.0.1',
}
mysql_grant { 'root@host.subnet.company/.':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => '.',
user => 'root@host.subnet.company',
}
mysql_grant { 'root@localhost/.':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => '.',
user => 'root@localhost',
}
mysql_grant { 'web@host.subnet.company/.':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => '.',
user => 'web@host.subnet.company',
}
mysql_grant { 'web@localhost/.':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => '.',
user => 'web@localhost',
}

[apenney]

Could you take a look at #319 and see if it behaves better? Basically we tried to be "smart" about what we match but there's a lot of variation in what mysql accepts as a user and a host and trying to be smart was just causing us to miss things. I've cut this down to "just match what's here no matter what" and it seems to fix the test cases I put together and behave as I would expect.

This is all kind of messy as we use a regex to extract this stuff so I'm constantly tweaking it to make sure we get every variation, sorry for the churn on this.

[apenney]

I added your above manifest (with a tweak to create the table) as a more complex test case so no matter what I do I won't break your specific setup again. :)

[tekenny]

That fixed the issue and thanks for adding the complex test case!

[tekenny]

This seems to be happening again for me in 2.0.1 for a grants such as:

user_name@10.10.10.%/db.table
user_name@10.10.10.%/db.*

but it works fine for:

user_name@10.10.10.%/.

[baurmatt]

Can confirm this for the current trunk version (11/04/2013 21:45 UTC) of this respository. My manifest looks like this:

  mysql_database { 'wordpress':
    ensure  =>  present,
    charset =>  'utf8',
    collate =>  'utf8_general_ci',
  }

  mysql_user { 'wordpress@10.42.1.%':
    ensure                   => 'present',
    password_hash            => mysql_password('XXXXXXXXXXXXXXXXXX'),
    max_connections_per_hour => '0',
    max_queries_per_hour     => '0',
    max_updates_per_hour     => '0',
    max_user_connections     => '0',
  }

  mysql_grant { 'wordpress@10.42.1.%/wordpress':
    table       =>  'wordpress.*',
    options     =>  ['GRANT'],
    privileges  =>  ['ALL'],
    user        =>  'wordpress@10.42.1.%',
  }

[tekenny]

My bad, my problem was due to not specifying a specific value for table. Well actually I had a default table for the Mysql_grant resource of . which obviously caused the problem. Sorry for the false report and thanks!

[baurmatt]

I don't think this is a false report, since my manifest specifies an value for 'table' and puppet repeats to set the rights on each run.

[tekenny]

baurmatt sorry I did not mean your report was false. I meant my report of the problem reoccurring was false since your report helped me to see that I was missing the appropriate table parameter for my grant.

I noticed that in the title you only have wordpress not wordpress.*
I wonder if the inconsistency between the title and the table parameter could be the cause as it was in my case?

[baurmatt]

Oh i'm sorry, i though you meant both of our reports and yes the missing '.' did it. I've just copied it from here: https://github.com/puppetlabs/puppetlabs-mysql/blob/master/tests/mysql_grant.pp
So i think the test is also missing the '.' in the title, am i right?

[tekenny]

https://github.com/puppetlabs/puppetlabs-mysql states

mysql_grant can be used to create grant permissions to access databases within MySQL. To use it you must create the title of the resource as shown below, following the pattern of username@hostname/database.table:

mysql_grant { 'root@localhost/.':
ensure => 'present',
options => ['GRANT'],
privileges => ['ALL'],
table => '.',
user => 'root@localhost',
}

Based on the documentation it seems that the test is not correct. Unless the intent was to support partial qualification despite what is stated.

[tekenny]

I submitted pull request #355 to correct the test assuming the documentation is correct.