views:

70

answers:

2

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 ;
+2  A: 

A web search for "error 1396" turns up this bug report.

The cause seems to be previously deleted user entries still somehow latent in the database, and blocking the creation of new accounts with the same name.

Try a FLUSH PRIVILEGES; before the first statement and run them again.

Pekka
I already have a FLUSH PRIVILEGES; in the script. :(
BCS
A: 

look here for a good start towards this issue

[snark] Is there a reason you didn't just search for 'mysql ERROR 1396'? [/snark]'

KevinDTimm
I just did run the search. Without knowing about what Pekka stated. I would have rejected the first half of the first page of results including the link he posted.
BCS