0

I have a fresh installation of mysql-community-server 5.7 installed on centos 7. I can successfully connect to the mysql server remotely, also locally using mysql command. But when trying to connect with laravel (latest version of 5.8.*), I get the error Host 'SERVER_IP_ADDRESS' is not allowed to connect to this MySQL server.

(There are great posts about this like in here and here, but it's not the case and the problem doesn't seem to go away.)

So this made me think maybe this isn't a mysql problem.

Here is mysql conf from /etc/my.cnf:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I'm running PHP-FPM and nginx as a reverse proxy, everything works fine, until laravel tries to connect to the db.

I also tried changing the DB_HOST from 127.0.0.1 to localhost , server_ip_address, domain_name,... even in the .env file. changed the DB_USERNAME and DB_PASSWORD to sth random to see if I get a different error, stuck with the same one.

MySQL user table:

+---------------+-----------+
| user          | host      |
+---------------+-----------+
| deka          | %         |
| root          | %         |
| deka          | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

(Deleted every user record with hostname '%' or '_' as other posts instructed and recreated the users with full permission on the database. even when there was no user with wild cards, it didn't work.)

root user grants:

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

deka user grants:

+----------------------------------------------------------------------+
| Grants for deka@%                                                    |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'deka'@'%'                                     |
| GRANT ALL PRIVILEGES ON `dekatech`.* TO 'deka'@'%' WITH GRANT OPTION |
+----------------------------------------------------------------------+

I also:

  1. Made sure laravel is not caching any config.
  2. Flushed privileges on every change.

None of the users seem to work.

Also mysql log files look pretty standard, except the two last lines.

2019-10-02T20:54:53.246940Z 0 [Warning] CA certificate ca.pem is self signed.
2019-10-02T20:54:53.249171Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-10-02T20:54:53.249220Z 0 [Note] IPv6 is available.
2019-10-02T20:54:53.249235Z 0 [Note]   - '::' resolves to '::';
2019-10-02T20:54:53.249263Z 0 [Note] Server socket created on IP: '::'.
2019-10-02T20:54:53.289907Z 0 [Note] Event Scheduler: Loaded 0 events
2019-10-02T20:54:53.290223Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.27-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2019-10-02T20:56:09.047393Z 2 [Warning] IP address 'SERVER_IP_ADDRESS' could not be resolved: Name or service not known
2019-10-02T20:56:19.052216Z 2 [Note] Got timeout reading communication packets
Amir Hossein Abdollahi
  • 1,253
  • 2
  • 9
  • 14
  • Have you checked your MySQL server log for any further error messages? – Koala Yeung Oct 02 '19 at 03:35
  • Is the laravel app on the same host as the database? Can you connect to the db using a vanilla php database connection? – alex.pilon Oct 02 '19 at 21:18
  • Yes they are on the same server, I can successfully connect to the DB remotely and locally with a php script (https://www.hostinger.com/tutorials/how-to-connect-php-to-mysql), definitely a laravel problem. – Amir Hossein Abdollahi Oct 02 '19 at 21:51

1 Answers1

0

I had duplicate 'DB_HOST' in my .env file, which was causing the problem.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_HOST=mysql => Faulty line (which was previously used on a docker dev environment (laradock))
DB_PORT=3306
DB_DATABASE=XXXX
DB_USERNAME=XXXX
DB_PASSWORD=XXX

It would be nice to have a warning from laravel, all this trouble, because of one over written variable.

Amir Hossein Abdollahi
  • 1,253
  • 2
  • 9
  • 14
  • 1
    Well, it gave you an error. The first debugging step with database issues in Laravel is typically to fire up `php artisan tinker` and output the value of `config('database')` to make sure everything equals what you *think* they equal. – ceejayoz Oct 03 '19 at 14:32