0

I working with one of email sender services API and facing a problem. Here is my process:

1) I'm fetching all subscribers emails (more than 1000 records),

2) Then I need to get XML info from API for the EACH email,

3) And then I need to save this info to the DB.

I use Yii2 console command but after several minutes the script terminates with 'Error while sending QUERY packet.'

How can I resolve this issue?

Yii console command (action):

class StartAction extends Action{

    public function run(){

        $uniqEmails = $activitiesComp->getUniqueEmails(); // emails array

        $feedHelper = new FeedHelper([
            'apiKey' => $value,
            'apiHost' => $key,
            'date' => $this->date,
            'columns' => 'Extended',
        ]);

        $XMLSubscriberArr = [];

        foreach ($uniqEmails as $email) {
            $XMLSubscriberArr[] = $feedHelper->getSubscriberInfo($email);
        }

        foreach ($XMLSubscriberArr as $XMLSubscriberInfo) {
            $parsedSubscriber = new SimpleXMLElement($XMLSubscriberInfo);

            $subscriberEmail = (string) $parsedSubscriber->Data->Email;

            if (!$subscribersModel = $subscribersComp->getSubscriberByEmail($subscriberEmail)) {
                $subscribersModel = $subscribersComp->getModel();
            }
            $subscribersModel->ip = (string)$parsedSubscriber->Data->Ip;
            $subscribersModel->email = $subscriberEmail;
            $subscribersModel->first_name = (string) $parsedSubscriber->Data->Firstname;
            $subscribersModel->second_name = (string) $parsedSubscriber->Data->Lastname;

            $properties = $parsedSubscriber->Data->Properties;

            foreach ($properties->Property as $property) {
                if ($property->Name == "custom_id") {
                    $subscribersModel->sem_id = (int) $property->StringValue;
                }
            }

            $subscribersModel->save();
        }

    echo 'Success';

    return ExitCode::OK;
    }
}

Feed Helper class:

class FeedHelper 
{
    public $apiKey;
    public $apiHost;
    public $date;
    public $columns;

    public function __construct($params)
    {
        $this->apiKey = $params['apiKey'];
        $this->apiHost = $params['apiHost'];
        $this->date = $params['date'];
        $this->columns = $params['columns'];
    }

    public function getSubscriberInfo($email)
    {
        $params = [
            'apiKey' => $this->apiKey,
            'email' => $email,
            'option' => 'full',
        ];

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $this->apiHost . 'Api/Subscribers/?' . http_build_query($params));
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        $result = curl_exec($curl);
        curl_close($curl);

        return $result;
    }
}
Muhammad Omer Aslam
  • 22,976
  • 9
  • 42
  • 68
Dmitry
  • 83
  • 4
  • 11
  • it's related to the number of database requests a website can make when it's running on a shared web hosting. You should know that every web hosting has its limitation to those. – Muhammad Omer Aslam Sep 13 '19 at 12:07
  • Where can I view and change this settings for localhost? – Dmitry Sep 13 '19 at 12:32
  • https://stackoverflow.com/questions/30753674/error-while-sending-query-packet – Muhammad Omer Aslam Sep 13 '19 at 12:58
  • I set 'max_allowed_packet' = 512M in config, but it doesn't help. Script handles fine about 200 emails , but the larger quantity ends it with the error above. What else can i do to fix the problem? – Dmitry Sep 13 '19 at 14:50
  • did you edit these values in `my.cnf` and did you restart mysql after updating, instead stop and then start mysql – Muhammad Omer Aslam Sep 13 '19 at 17:22
  • yes, I restart web server made sure that mysql works with new 'max_allowed_packet' value. – Dmitry Sep 13 '19 at 18:01
  • `max_allowed_packet` doesn't affect the number of queries. It affects max size of query. The limits for number of queries are set per user and should be stored in `mysql.user` table. – Michal Hynčica Sep 14 '19 at 09:10
  • @MichalHynčica, thanks for the reply. But I don't see any restrictions in `mysql.user` table. There is a `0` value in `max_questions, max_updates, max_connections, max_user_connections, max_statement_time`. Maybe there is another way to resolve my issue? – Dmitry Sep 16 '19 at 07:37
  • Are there any suggestions on what method I could use to resolve this problem, please? – Dmitry Sep 18 '19 at 13:15

0 Answers0