save() use wrong DB_HOST but the correct DB_USER

Published 1 week ago by cruex

Hello,

I have a weird bug and I can't find a solution to it, namely:

I host my website with the Forge Service, my database is on an external server, now to my problem.

I'm trying with a "post" function to change a value in the database to verify an email, the values it can read out problem without error, but as soon as I get a value like

$verify->email_verified = 1;
$verify->save();

as an example, he takes the IP address of the web server and tries to connect to it, only now the problem my database is on an external server. This also works and you can read or enter values (with create etc.), only with this save() and update(); value edits it wrong.

ERROR:

Illuminate/Database/QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1142 UPDATE command denied to user 'database_user'@'WRONGSERVERIP' for table 'table_name' (SQL: update `table_name` set `email_verified` = 1, `updated_at` = 2018-10-11 21:28:06 where `id` = 2)'

I hope someone can help me.

Best Answer (As Selected By cruex)
Cronix

That doesn't mean it's trying to connect to a different db at all!

You do know that you can grant permissions for users tied to specific IP's, right? That's all database_user'@'WRONGSERVERIP is meaning. It does NOT mean WRONGSERVERIP is the server it's trying to connect TO. That's the IP that the user is allowed to connect FROM.

Please please please check the permissions for the db user. What will it hurt? It takes like 20 seconds to do lol.

For instance, to create a user that is only allowed to access the db FROM server 1.2.3.4 and grant all privileges for all tables to db "db1", it would be

CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'username'@'1.2.3.4';

I'm 95% sure that's what your issue is. The user doesn't have the correct permissions and/or ip. I don't think this has anything to do with any other server except your remote db server.

in the error log: "WRONGSERVERIP" was the ipv4 address from the production server

That actually sounds correct due to what I mentioned above. The user should have permissions to access from your production server. It's not trying to connect TO the production server.

https://dev.mysql.com/doc/refman/5.7/en/grant.html

Cronix
Cronix
1 week ago (794,690 XP)

Do you only have 1 db connection defined, or multiple?

cruex
cruex
1 week ago (3,320 XP)

Yes only 1 Database and the driver is MySQL and the .env file with the Database Settings are correct

Cronix
Cronix
1 week ago (794,690 XP)

Do you mind showing your db settings in .env as well as /config/database.php? (it's ok to xxx out user/pass/ip/etc).

Have you tried running these?

php artisan config:clear
php artisan cache:clear
cruex
cruex
1 week ago (3,320 XP)

.env

DB_CONNECTION="mysql"
DB_HOST="XX.XX.XX.XX"
DB_PORT="3306"
DB_DATABASE="db"
DB_USERNAME="usr"
DB_PASSWORD="XXXXXXXXXXXXXXX"

config/database.php

'connections' => [
    'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
        ],
],

Reading and creating works in the database, only save() and update() makes the mistake so far.

and yes i run config:clear & cache:clear

Cronix
Cronix
1 week ago (794,690 XP)

Hmm. The only thing I'd suggest there is to remove the quotes from the values in the .env. They should only be necessary if there is a space in a value, but it shouldn't matter really. Just trying stuff here...

If you go into tinker and type config('database'), does the resulting array look ok for the mysql section and stuff?

Sorry, I don't really have any good ideas on this one. Are you sure you aren't specifying a connection when interacting with the DB query builder or in an Eloquent model?

cruex
cruex
1 week ago (3,320 XP)

Quotes from the values in the .env change have no effect...

Array from the thinker looks okay, IP is set and all another vars.

I have not defined a connection on a DB query builder or in an Eloquent model...

I'm a little confused. ^^'

Cronix
Cronix
1 week ago (794,690 XP)

Does the db user have full permissions, specifically update and create? You can check with running a SHOW GRANTS FOR username; query. The only other thing I can think to do is contact Forge Support.

cruex
cruex
1 week ago (3,320 XP)

The database has enough rights, otherwise I couldn't have anything created. And the query doesn't help me, because he uses the wrong IP from the Database server. But thanks for your help and ideas.

Cronix
Cronix
1 week ago (794,690 XP)

The database has enough rights, otherwise I couldn't have anything created.

Yes, you don't have a problem creating. You said you have a problem updating, which is a different permission.

I'm trying with a "post" function to change a value in the database to verify an email, the values it can read out problem without error, but as soon as I get a value like

$verify->email_verified = 1;
$verify->save();

That's an update, not a create. The error message contains UPDATE command denied to user. So apparently the user exists on whatever server it's connecting to, but not allowed to update. Does this user exist in both databases? (remote and whatever you are running locally?)

I'd just use sequel pro/navicat/phpmyadmin or something else to connect remote db server directly and bypass the server laravel is installed on entirely to run the query.

cruex
cruex
1 week ago (3,320 XP)

There is no database service installed on the Production server.

The Model:

class Subscriber extends Model
{
    protected $table = 'newsletter_subscribers';

    protected $fillable = [
        'user_id', 'lang', 'email', 'email_verified', 'email_token'
    ];
}

This is the post function in the controller:

    public function verifyPost($token)
    {
        $verify = Subscriber::where('email_token', $token)->first();
        $generateToken = generateToken();

        $verify->email_verified = 1;
        $verify->email_token = $generateToken;
        $verify->save();

        return redirect( route('welcome'));
    }

any error or syntax error? (generateToken() is a helper function)

Cronix
Cronix
1 week ago (794,690 XP)

I don't see anything wrong in that (except that you should use firstOrFail() instead of first(), or at least check whether you got a result first before trying to save it)

Did you check whether the db user has update permissions? The error message shows the user doesn't. If you don't have a db server on this production server, then what's this "other server" you say it connects to? Whatever it's connecting to obviously has the db user or the error message would be different and talk about denying access to the user. It doesn't. It says the user isn't allowed to UPDATE, so you really need to check the user permissions.

cruex
cruex
1 week ago (3,320 XP)

Yes, I am aware of that, but what is the point if I check the rights on the production server if he should not access it at all? The system is supposed to access the externally hosted database. And right now, he's trying to access the local database. But it's just this one function that confuses me.

Cronix
Cronix
1 week ago (794,690 XP)

Check it on the external server it's supposed to be accessing. I didn't suggest to check the server that has no db service on it.

BTW what makes you think it's trying to connect to a different server other than the remote db server?

cruex
cruex
1 week ago (3,320 XP)

Illuminate/Database/QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1142 UPDATE command denied to user 'database_user'@'WRONGSERVERIP' for table 'table_name' (SQL: update table_name set email_verified = 1, updated_at = 2018-10-11 21:28:06 where id = 2)'

UPDATE command denied to user 'database_user'@'WRONGSERVERIP'

in the error log: "WRONGSERVERIP" was the ipv4 address from the production server

and the external db server working perfect with no error

Cronix
Cronix
1 week ago (794,690 XP)

That doesn't mean it's trying to connect to a different db at all!

You do know that you can grant permissions for users tied to specific IP's, right? That's all database_user'@'WRONGSERVERIP is meaning. It does NOT mean WRONGSERVERIP is the server it's trying to connect TO. That's the IP that the user is allowed to connect FROM.

Please please please check the permissions for the db user. What will it hurt? It takes like 20 seconds to do lol.

For instance, to create a user that is only allowed to access the db FROM server 1.2.3.4 and grant all privileges for all tables to db "db1", it would be

CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'username'@'1.2.3.4';

I'm 95% sure that's what your issue is. The user doesn't have the correct permissions and/or ip. I don't think this has anything to do with any other server except your remote db server.

in the error log: "WRONGSERVERIP" was the ipv4 address from the production server

That actually sounds correct due to what I mentioned above. The user should have permissions to access from your production server. It's not trying to connect TO the production server.

https://dev.mysql.com/doc/refman/5.7/en/grant.html

Please sign in or create an account to participate in this conversation.