mysql连接错误太多了。

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

Answer 1

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') ");
}