mysql too many connection error.

Question

I have a .txt file with a list of 60,000 English words. I wanted to insert those to my database, so I simply just did as show here.

$file = new SplFileObject('list.txt');

foreach ($file as $line => $word) {
    $p = new PDO('mysql:host=localhost; dbname=test_dictionary', 'root', 'test');
    $p->query("INSERT INTO words (english) VALUES('$word') ");
}

Now, after I run this script, I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1040] Too many connections' in /var/www/skillz/test/curl/index.php:17 Stack trace: #0 /var/www/test/index.php(17): PDO->__construct('mysql:host=loca...', 'root', 'test') #1 {main} thrown in /var/www/test/index.php on line 4

That line 4 is where the new PDO('mysql:') is located. So, I tried to search this error, and found this answer that seemed a solution. And I edited mysql accordingly, as

$ vi /etc/my.cnf
  max_connections=250

But I still get the same error, I have MySql 5.5.38 running PHP-FPM, NGINX in CentOS 6.5

solved! Sorry. It worked when I moved PDO from the loop. such a silly mistake — robue-a7119895, Aug 10 '14 at 13:15

score 2 · Accepted Answer · answered Aug 10 '14 at 13:14

Don't open a new Connection for every word. You only need one connection open for the lifetime of your inserts. I'm not sure about the true lifetime of a PDO object, I know they're cleaned up when they're not used, but garbage collection might not do that for a couple of minutes, and for 60,000 words, you're going to hit your limit of connections to the database faster than it can clean them up.

$file = new SplFileObject('list.txt');
$p = new PDO('mysql:host=localhost; dbname=test_dictionary', 'root', 'test');

foreach ($file as $line => $word) {

    $p->query("INSERT INTO words (english) VALUES('$word') ");
}

score 2 · Answer 2 · answered Aug 10 '14 at 13:15

You should declare the SQL connection outside the foreach. Because it will make 60.000 connections.

$file = new SplFileObject('list.txt');
$p = new PDO('mysql:host=localhost; dbname=test_dictionary', 'root', 'test');

foreach ($file as $line => $word) {

    $p->query("INSERT INTO words (english) VALUES('$word') ");
}

You only need to declare the SQL connection once and you can use it anytime as you want. If you put it in foreach it will make a SQL connection every word, that's why you got that message.

Ankit Singhania · Answer 3 · 2014-08-10T14:13:46.827

2

Solution 1 Use batch insert statement :

INSERT INTO words (col1, col2) VALUES ('val1', 'val2'), ('val3', 'val4'), ...('val3n', 'val4n');

This fails if you also want to check if some rows failed or not. So, below is another solution.

Solution 2

Create a persistent database connection. This will use the same connection in all iterations of the loop.

$file = new SplFileObject('list.txt');
$p = new PDO('mysql:host=localhost; dbname=test_dictionary', 'root', 'test', array(
    PDO::ATTR_PERSISTENT => true)); //Persistent Database Connection
foreach ($file as $line => $word) {
    $p->query("INSERT INTO words (english) VALUES('$word') ");
}
$p = null; //Destroy Connection

edited Aug 10 '14 at 14:13

answered Aug 10 '14 at 13:24

Ankit Singhania

1,010
9
17

For 60,000 entries I wouldn't want to try and take a look at that SQL if it failed to insert one. – tbddeveloper Aug 10 '14 at 13:26
Hey @Hammerstein, I've made an edit to the answer. You can use `sleep` command to force the connection to be re-used. – Ankit Singhania Aug 10 '14 at 13:40
Not very effective, but +1 for the effort. Because, `sleep(1)` would delay each iteration by 1 second, it would almost take 60,000 seconds to complete the query – robue-a7119895 Aug 10 '14 at 13:50
Hey @Hammerstein, I've made another edit. Solution 2 is perfect for you. Let me know if it doesn't work. – Ankit Singhania Aug 10 '14 at 14:14
I wasn't the one looking to insert 60,000 words. But, yes we all agreed that opening a single connection for the duration of the 60,000 was a good idea. Although, take a look at http://stackoverflow.com/questions/3332074/what-are-the-disadvantages-of-using-persistent-connection-in-pdo it's an interesting answer to using persistent connection in PDO. – tbddeveloper Aug 11 '14 at 16:07

mysql too many connection error.

3 Answers3