2

We are having a very strange problem on our server.

Any user (including root), can't access mysql on local system, and it swaps between flushes.

We are using a simple php script to test it...

$username = "test";
$password = "pass123";

$conn = new mysqli("localhost", $username, $password);
if ($conn->connect_error) {echo "[localhost] Connection failed: " . $conn->connect_error;} else echo "[localhost] Connected successfully";
echo "<hr>";

$conn = new mysqli("127.0.0.1", $username, $password);
if ($conn->connect_error) {echo "[127.0.0.1] Connection failed: " . $conn->connect_error;} else echo "[127.0.0.1] Connected successfully";
echo "<hr>";

$conn = new mysqli("192.168.1.2", $username, $password);
if ($conn->connect_error) {echo "[192.168.1.2] Connection failed: " . $conn->connect_error;} else echo "[192.168.1.2] Connected successfully";

So let me step you through the sequence.

  1. MySQL user configured, user@% with pass123, with full server privs on all db.
  2. PHP Script is run. First two tests fail with access denied. Last one succeeds.
  3. Mysql user reconfigured: user@127.0.0.1
  4. Script run: All three denied.
  5. MySQL Flush Privs
  6. First two succeed. Last fails. (makes sense)
  7. MySQL User configured: user@% (original of step 1)
  8. Script run: All three succeed. (Hooray? No)
  9. MySQL Flush Privs (and/or server reboot)
  10. Back to step 2.

We can't get Coldfusion or PHP to maintain connections. It works at first, the first connection, when user@127.0.0.1 (configured so in mysql), but subsequently fails in coldfusion, and php can't reliably connect depending on service restarts or flushes. PHP really outlines the problem, as seen above. But the JDBC connector for coldfusion just seems to randomly either work or not.

Any ideas what is happening here? user@% should always work, should it not?

  • MySQL v5.5, PHP v 7, Coldfusion v11 (jdbc .38 latest). Windows Server 2012.
  • my.cnf is NOT configured with bind address (all hosts allowed).
    • max-connections = 500
  • ipv6 disabled (OxFFFFFF)
  • firewall is off.
  • Same database, same users, and same configuration, works fine on windows server 2008, which we are attempting to migrate from. These are all fresh installs of each software. We were able to simply create user@%, and connect on all 3 tests, at all times.

Additional notes:

  • "localhost" when typed in browser (local IIS), sometimes works, sometimes doesnt. 127.0.0.1 works fine always.
  • no hosts entry, as win7+ uses internal dns. We added localhost entry, it made no effect.
  • IIS keeps freezing on us. The sites begin to "spin" in the browser, and never load. We have to run an iisreset. We can't determine why this happens - it seems random. Seems related to the localhost issue, yes?
Barry
  • 362
  • 3
  • 14

2 Answers2

1

If I remember correctly, at one point in time I found that I needed to have 2 users. Check for the existence of a second user@localhost user before proceeding.

This was because % did not actually match for local connections over the UNIX socket, and instead the user@localhost record will be used

This question might be helpful: Mysql enabling remote and local access

Remember that any changes to the users table requires a FLUSH PRIVILEGES before changes take effect.

Community
  • 1
  • 1
edhurtig
  • 2,331
  • 1
  • 25
  • 26
  • 100% correct, we were trying to avoid duplicate users, and just have the one user in mysql. Letting our firewall & vpn handle the security of login access points. – Barry Apr 21 '16 at 02:21
  • 2
    Yeah unfortunately I don't think you are going to be able to get around that here if you are performing local connections. Also note that (I think) there is a difference between localhost and 127.0.0.1 (one is the UNIX socket and one is the loop back interface respectively) – edhurtig Apr 21 '16 at 02:24
  • This is a common misconception. `'user'@'%'` matches everything *unless a more specific host value for that user exists*... or you hit the empty username user because you didn't properly secure the initial installation. – Michael - sqlbot Apr 21 '16 at 23:41
1

Found the answer, sorry guys, should have researched just a moment more. https://serverfault.com/questions/122472/allowing-wildcard-access-on-mysql-db-getting-error-access-denied-for-use

Sometimes we find the right keywords by talking about it, that rubber duck effect.

Basically, well, apparently, mysql by default includes a blank user record, with a host of localhost, which supersedes any user@%. Resulting in this behaviour. Delete the user, and problem solved. now my firewall can be turned back on... wow.

The IIS problems I mentioned are a topic for another day I suppose...

Community
  • 1
  • 1
Barry
  • 362
  • 3
  • 14
  • 1
    The explanation isn't quite right. When a user attempts to connect to the MySQL server, MySQL looks at rows from the mysql.user table in a specific order. You are right that MySQL orders **''@'localhost'** before any **'user'@'%'**, and that will be checked first. In MySQL, the host value of 'localhost' is *not* synonymous with the loopback IP 127.0.0.1. It's not an IP connection at all. It's a connection on the local server, through a socket which is *not* an IP socket. So this entry in the user table only affects clients on the local server, and not connecting through IP. – spencer7593 Apr 21 '16 at 02:48
  • 1
    Any DML (INSERT/UPDATE/DELETE) changes made the msyql.user, mysql.db, et al. tables are *not* effective until a FLUSH PRIVILEGES causes MySQL server to read the tables and repopulate the memory structures. If privilege changes are made through GRANT and REVOKE statements, the changes do take effect without requiring the execution of a FLUSH PRIVILEGES statement. – spencer7593 Apr 21 '16 at 02:58
  • Great explanation, that makes a ton more sense now. This stuff should be required reading, it's simply essential. Not sure if you are able to improve/edit my answer, would prefer to give credit where credit is due. – Barry Apr 21 '16 at 05:44
  • A user in MySQL is identified by user and host. There can be multiple rows for the same user, with different host. Each can have different password (or authentication plugin) and different privileges. MySQL looks at rows in order, looking for the most specific match first. If a match is found, then that's the user you are connected as. If it doesn't match a more specific user, it will match the wildcard host... user@%. It sounds like you've removed the rows from mysql.user... ''@localhost. user@localhost, user@127.0.0.1, user@192.168.1.2, the more specific ones that would match before user@% – spencer7593 Apr 21 '16 at 06:06