MySQL Grant Pain

Ovid on 2008-04-17T14:26:18

mysql> grant all on no_such_database.* to 'no_such_user'@'%' identified by 'some_password';
Query OK, 0 rows affected (0.00 sec)

No warning. No problem. This is real fun to debug.

Also, when you drop a database, grants aren't dropped. We found this out when it turned out we had over 1.5 million grants left in the database. This had an impact on performance.

---

The MySQL guys keep saying...

Alias on 2008-04-17T14:47:05

... that apparently MySQL is a bit like Perl, the first thing you need to do is "use strict"...

Have you enabled strict mode?

Note: I have no freaking idea how to turn it on myself, but I think it's being positioned as a "gotcha" ala use strict.

Re:The MySQL guys keep saying...

Ovid on 2008-04-17T15:01:14

mysql> set @@session.sql_mode='traditional';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------- ---------------------------------------------------------------------+
| @@session.sql_mode                                                                                                            |
+---------------------------------------------------------------------------- ---------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV ISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+---------------------------------------------------------------------------- ---------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant all on no_such_database.* to 'no_such_user'@'%' identified by 'some_password';
Query OK, 0 rows affected (0.00 sec)

But it's quite possible I'm misunderstanding this (and it's annoying as hell that "strict mode" appears to be "per connection" (though you can apparently set it up for the server, too).