Mysql 1041 queries limit in php

Question

I've got a PHP script (mysqli function) which generates insert into... queries as an array of strings and send them to mysql database one by one. The problem is that there are about 40000 queries and my database always stop inserting new ones after 1041 and soon later close the connection.

Is it a php limitation or problem with mysql? Or maybe something else? How can I avoid this problem and send all queries to database?

Here is the code:

        $data // my array of strings - mysql queries

        ini_set ('error_reporting', E_ALL);
        ini_set ('display_errors', '1');
        error_reporting (E_ALL|E_STRICT);

        $connection = mysqli_init();
        mysqli_options ($connection, MYSQLI_CLIENT_SSL_VERIFY_SERVER_CERT, true);

        mysqli_ssl_set($connection,'/usr/local/certs/client-key.pem',
       '/usr/local/certs/client-cert.pem', '/usr/local/certs/server-ca.pem', NULL, NULL);

        $link = mysqli_real_connect ($connection, $db['host'], $db['user'], $db['password'], 
       $db['dbName'], 3306, NULL,MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);


        if (!$link)
        {   
            die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
        } 
        else 
        {   

           foreach ($data as $index => $queryToSend) {

                if($connection->query($queryToSend))
                {
                    $this->output->writeln('succesfull query number: '.$index);
                }   
           }



            $connection->close();
        }

Is it possible that your script terminates with the default 30 second time limit? — Jirka Hrazdil, Dec 29 '17 at 07:14
You could increase the php.ini settings, time limit or you can divide the job into some pieces. — halojoy, Dec 29 '17 at 07:17
Jiri: I don't know is that the problem because in my terminal mysql process seems to be still working but without adding any new queries. I mean I can only terminate it with ctr/cmd + c button — ampher911, Dec 29 '17 at 07:20
@ampher911 What is the error message you get from mysql after the `mysqli_query()` call failed? Also, you should use prepared statements when you run SQL queries with variable inputs. — Progman, Dec 29 '17 at 13:16

score 1 · Accepted Answer · answered Dec 29 '17 at 07:36

1

I think you should try batch insertion, this will help you to overcome the problem that you are facing.

Here is a reference How to do a batch insert in MySQL

answered Dec 29 '17 at 07:36

kkarayat

392
1
5
14

score 1 · Answer 2 · answered Dec 29 '17 at 09:11

1

Change your default execution time in php file

ini_set('max_execution_time', 120); //120 seconds

I hope this will help.

answered Dec 29 '17 at 09:11

Sachin

397
4
13

Mysql 1041 queries limit in php

2 Answers2