Framework: Error occurs while running sql command in queue.

[YangEndly]

• Laravel Version:5.3
• PHP Version:7.1
• Database Driver & Version: mysql5.7.9
  -local development：wamp

My online production environment: php and mysql are two independent servers. It does not work.
The local development environment: php and mysql are the same server. It can work well.

Description:

While running sql command, mysql that is connected to PDO is 127.0.0.1. However, my mysql is another server so that it does not work. The error is as follows:
Stack trace:

0 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(119): PDO->__construct('mysql:host=127....', '', '', Array)

1 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(56): IlluminateDatabaseConnectorsConnector->createPdoConnection('mysql:host=127....', '', '', Array)

2 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php(24): IlluminateDatabaseConnectorsConnector->createConnection('mysql:host=127....', Array, Array)

3 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connectors/ConnectionFactory.php(126): IlluminateDatabaseConnectorsMySqlConnector->connect(Array)

4 [internal function]: IlluminateDatabaseConnectorsConnectionFactory->IlluminateDatabaseConnectors{closure}()

5 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(965): call_user_func(Object(Closure))

6 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(476): IlluminateDatabaseConnection->getPdo()

7 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(763): IlluminateDatabaseConnection->IlluminateDatabase{closure}(Object(IlluminateDatabaseMySqlConnection), 'insert into `in...', Array)

8 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(726): IlluminateDatabaseConnection->runQueryCallback('insert into `in...', Array, Object(Closure))

9 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(481): IlluminateDatabaseConnection->run('insert into `in...', Array, Object(Closure))

10 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(435): IlluminateDatabaseConnection->statement('insert into `in...', Array)

11 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Query/Processors/Processor.php(32): IlluminateDatabaseConnection->insert('insert into `in...', Array)

12 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2154): IlluminateDatabaseQueryProcessorsProcessor->processInsertGetId(Object(IlluminateDatabaseQueryBuilder), 'insert into `in...', Array, 'id')

13 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(1470): IlluminateDatabaseQueryBuilder->insertGetId(Array, 'id')

14 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1609): IlluminateDatabaseEloquentBuilder->__call('insertGetId', Array)

15 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1574): IlluminateDatabaseEloquentModel->insertAndSetId(Object(IlluminateDatabaseEloquentBuilder), Array)

16 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1471): IlluminateDatabaseEloquentModel->performInsert(Object(IlluminateDatabaseEloquentBuilder))

17 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(561): IlluminateDatabaseEloquentModel->save()

18 /data/www/yungou.ay/app/Jobs/SendNotifyPurchase.php(46): IlluminateDatabaseEloquentModel::create(Array)

19 [internal function]: AppJobsSendNotifyPurchase->handle(Object(AppHttpControllersJPushController))

20 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Container/Container.php(508): call_user_func_array(Array, Array)

21 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(94): IlluminateContainerContainer->call(Array)

22 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): IlluminateBusDispatcher->IlluminateBus{closure}(Object(AppJobsSendNotifyPurchase))

23 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): IlluminatePipelinePipeline->IlluminatePipeline{closure}(Object(AppJobsSendNotifyPurchase))

24 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(98): IlluminatePipelinePipeline->then(Object(Closure))

25 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(47): IlluminateBusDispatcher->dispatchNow(Object(AppJobsSendNotifyPurchase), false)

26 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(73): IlluminateQueueCallQueuedHandler->call(Object(IlluminateQueueJobsRedisJob), Array)

27 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(203): IlluminateQueueJobsJob->fire()

28 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(152): IlluminateQueueWorker->process('redis', Object(IlluminateQueueJobsRedisJob), Object(IlluminateQueueWorkerOptions))

29 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(100): IlluminateQueueWorker->runNextJob('redis', 'default', Object(IlluminateQueueWorkerOptions))

30 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(83): IlluminateQueueConsoleWorkCommand->runWorker('redis', 'default')

31 [internal function]: IlluminateQueueConsoleWorkCommand->fire()

32 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Container/Container.php(508): call_user_func_array(Array, Array)

33 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Console/Command.php(169): IlluminateContainerContainer->call(Array)

34 /data/www/yungou.ay/vendor/symfony/console/Command/Command.php(261): IlluminateConsoleCommand->execute(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

35 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Console/Command.php(155): SymfonyComponentConsoleCommandCommand->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

36 /data/www/yungou.ay/vendor/symfony/console/Application.php(817): IlluminateConsoleCommand->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

37 /data/www/yungou.ay/vendor/symfony/console/Application.php(185): SymfonyComponentConsoleApplication->doRunCommand(Object(IlluminateQueueConsoleWorkCommand), Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

38 /data/www/yungou.ay/vendor/symfony/console/Application.php(116): SymfonyComponentConsoleApplication->doRun(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

39 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(121): SymfonyComponentConsoleApplication->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

40 /data/www/yungou.ay/artisan(35): IlluminateFoundationConsoleKernel->handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

41 {main}

Next IlluminateDatabaseQueryException: SQLSTATE[HY000] [1045] Access denied for user 'treasure'@'127.0.0.1' (using password: YES) (SQL: insert into information (info_cate_id, title, desc, consumer_id, type, count, updated_at, created_at) values (3, Announcing soon!, Check who is lucky! Click to view: SAMSUNG MB-MP16DA/EU 16Gb Evo Micro SD Card, 10073, 2, 1, 2017-03-17 06:07:31, 2017-03-17 06:07:31)) in /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php:770
Stack trace:

0 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(726): IlluminateDatabaseConnection->runQueryCallback('insert into `in...', Array, Object(Closure))

1 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(481): IlluminateDatabaseConnection->run('insert into `in...', Array, Object(Closure))

2 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Connection.php(435): IlluminateDatabaseConnection->statement('insert into `in...', Array)

3 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Query/Processors/Processor.php(32): IlluminateDatabaseConnection->insert('insert into `in...', Array)

4 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2154): IlluminateDatabaseQueryProcessorsProcessor->processInsertGetId(Object(IlluminateDatabaseQueryBuilder), 'insert into `in...', Array, 'id')

5 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(1470): IlluminateDatabaseQueryBuilder->insertGetId(Array, 'id')

6 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1609): IlluminateDatabaseEloquentBuilder->__call('insertGetId', Array)

7 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1574): IlluminateDatabaseEloquentModel->insertAndSetId(Object(IlluminateDatabaseEloquentBuilder), Array)

8 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1471): IlluminateDatabaseEloquentModel->performInsert(Object(IlluminateDatabaseEloquentBuilder))

9 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(561): IlluminateDatabaseEloquentModel->save()

10 /data/www/yungou.ay/app/Jobs/SendNotifyPurchase.php(46): IlluminateDatabaseEloquentModel::create(Array)

11 [internal function]: AppJobsSendNotifyPurchase->handle(Object(AppHttpControllersJPushController))

12 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Container/Container.php(508): call_user_func_array(Array, Array)

13 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(94): IlluminateContainerContainer->call(Array)

14 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): IlluminateBusDispatcher->IlluminateBus{closure}(Object(AppJobsSendNotifyPurchase))

15 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): IlluminatePipelinePipeline->IlluminatePipeline{closure}(Object(AppJobsSendNotifyPurchase))

16 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(98): IlluminatePipelinePipeline->then(Object(Closure))

17 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(47): IlluminateBusDispatcher->dispatchNow(Object(AppJobsSendNotifyPurchase), false)

18 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(73): IlluminateQueueCallQueuedHandler->call(Object(IlluminateQueueJobsRedisJob), Array)

19 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(203): IlluminateQueueJobsJob->fire()

20 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(152): IlluminateQueueWorker->process('redis', Object(IlluminateQueueJobsRedisJob), Object(IlluminateQueueWorkerOptions))

21 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(100): IlluminateQueueWorker->runNextJob('redis', 'default', Object(IlluminateQueueWorkerOptions))

22 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(83): IlluminateQueueConsoleWorkCommand->runWorker('redis', 'default')

23 [internal function]: IlluminateQueueConsoleWorkCommand->fire()

24 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Container/Container.php(508): call_user_func_array(Array, Array)

25 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Console/Command.php(169): IlluminateContainerContainer->call(Array)

26 /data/www/yungou.ay/vendor/symfony/console/Command/Command.php(261): IlluminateConsoleCommand->execute(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

27 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Console/Command.php(155): SymfonyComponentConsoleCommandCommand->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

28 /data/www/yungou.ay/vendor/symfony/console/Application.php(817): IlluminateConsoleCommand->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

29 /data/www/yungou.ay/vendor/symfony/console/Application.php(185): SymfonyComponentConsoleApplication->doRunCommand(Object(IlluminateQueueConsoleWorkCommand), Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

30 /data/www/yungou.ay/vendor/symfony/console/Application.php(116): SymfonyComponentConsoleApplication->doRun(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

31 /data/www/yungou.ay/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(121): SymfonyComponentConsoleApplication->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

32 /data/www/yungou.ay/artisan(35): IlluminateFoundationConsoleKernel->handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

33 {main}

Steps To Reproduce:

[mrliptontea]

@YangEndly that's an error with your configuration, you said it yourself, MySQL is on a separate server.

Update DB_HOST to your MySQL's external IP address.

[YangEndly]

@mrliptontea Thank you for your attention. I am certain that normal outside mysql address is set in my DB_HOST but error still occurs. My doubt: is it a bug that default connection of sql command in queue is 127.0.0.1?

[edenreich]

@YangEndly if I understood right you are trying to set DB_HOST other than 127.0.0.1 but it sets you this as default ?

did you cleared your cache in the bootstrap folder ?

the configuration are probably cached..try:
php artisan clear-compiled
composer dump-autoload

[bradleyy1012]

if you do php artisan clear-compiled dont forget php artisan config:clear after :P

[YangEndly]

@edenreich I already tried to clear cache in bootstrap folder, and also tried
php artisan clear-compiled
composer dump-autoload
but still the same, do you have any other suggestions?

Thanks again...

[YangEndly]

this is class
class SendNotifyPurchase implements ShouldQueue
{
use InteractsWithQueue, Queueable, SerializesModels;

private $product;
private $alias;

/**
 * Create a new job instance.
 *
 * @return void
 */
public function __construct($alias,$product)
{
    $this->product=$product;
    $this->alias=$alias;
}

/**
 * Execute the job.
 *
 * @return void
 */
public function handle(JPushController $JPushController)
{
    $alert = "Check who is lucky! Click to view: $this->product";
    $title = 'Announcing soon!';
     Information::create([
        'info_cate_id'=>3,
        'title'=>$title,
        'desc'=>$alert,
        'consumer_id'=>implode(' ',$this->alias),
        'type'=>2,
        'count'=>1,
    ]);
    $JPushController->send_notify_one($alert,$this->alias,$title,'0');
}

}

[mrliptontea]

The error message says Access denied for user 'treasure'@'127.0.0.1' - Is your db user treasure?
If yes, can you confirm that in the file where you set DB_USERNAME=treasure you also set DB_HOST to something else than 127.0.0.1?

Relevant command is php artisan config:clear.

Also, if run your job in the queue, run php artisan queue:restart so workers can pick up the updated config file.

[YangEndly]

@mrliptontea
Thank you so much for helping me solving the problem.