This is what I did.
mysql> CREATE USER 'matrix'@'%.something.com' IDENTIFIED BY 'weak';
ERROR 1396 (HY000): Operation CREATE USER failed for 'matrix'@'%.something.com'
mysql> CREATE USER 'foo'@'%.something.com' IDENTIFIED BY 'weak';
Query OK, 0 rows affected (0.00 sec)
And this is what I end up with:
mysql> select user,host from mysql.user;
+------------------+--------------------+
| user | host |
+------------------+--------------------+
| auth | %.something.com |
| foo | %.something.com |
| submit | %.something.com |
| testcase | %.something.com |
| version | %.something.com |
| root | 127.0.0.1 |
| root | this.something.com |
| debian-sys-maint | localhost |
| master | localhost |
| root | localhost |
+------------------+--------------------+
10 rows in set (0.00 sec)
It seems that the issue is that I removed users via a DELETE FROM mysql.user WHERE User IN (%s);
to get around the problem that I don't know if the accounts exist.
My (ugly) solution, replace the DELETE FROM mysql.user WHERE...
with:
DELIMITER //
CREATE PROCEDURE dropuserif(u VARCHAR(32), h VARCHAR(64))
BEGIN
DECLARE s INT;
SELECT COUNT(*) INTO s FROM mysql.user WHERE user = u AND host = h;
IF s > 0 THEN
BEGIN
SET @d = CONCAT("DROP USER '", u, "'@'", h, "';");
PREPARE stmt_name FROM @d;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
END;
END IF;
END//
DELIMITER ;