0

I am on Centos 6 and mysql version is 5.6. - I have several relatively large tables and when I try to run a command it takes up all the connections to the database so the site gets down and I'll get this error:

SQLSTATE[HY000] [1040] Too many connections ...

Lets say I want to run this command: CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

Is there anyway that I can limit the number of connection that this command takes to mysql?

2 Answers 2

0

Hm, unless mysql works like any other database I know...

one command takes exactly ONE connection.

The error is not your command, the error is having and keeping too many connections open (application error), likely by not properly closing them and forgetting about them.

But one command runs on one connection. Period.

You basically ask how to avoid a traffic jam in front of your house by limiting the pizza you ordered to only come with one car - which it always does (because you only ordered one pizza anyway).

2
  • Thanks. But how come this happens only when I run commands on large table like the one I mentioned or for example: mysqlcheck -o -uusename -p mydatabase>mytable through ssh or phpmyadmin. And only when the table is very large for example 2mil records above. Nov 16, 2017 at 12:23
  • Because CREATE TABLE may stall the db server so that nothing else gets processed and your website/apache keep opening new connections until you hit the limit. Now, create table - unlikely, but mysqlcheck can be ressource intensive on a low powered server.
    – TomTom
    Nov 16, 2017 at 13:22
0
  • Using MyISAM leads to table-level locks -- Use InnoDB instead.
  • There is no version 6 of MySQL.
  • What is the client? If it is a web server; cut back on the number of clients it can simultaneously handle.
  • You have some slow queries; identify them and fix time.
  • Etc.

And, as TomTom says, that CREATE takes only one connection, so it is not directly to blame. But indirectly it may be tying up tbl_old in a way that causes other connections to "wait" for access.

4
  • Thanks. I edited the version, it is community 5.6. I run the command through SSH and in the meantime try to reach the site through browsers where I get the error. For example phpmyadmin gives me: phpMyAdmin - Error #1040 - Too many connections Nov 16, 2017 at 12:57
  • What is max_connections? Max_used_connections? Threads_connected?
    – Rick James
    Nov 16, 2017 at 14:16
  • useing "show global status;" I got: Max_used_connection: 152, and Thread_connected: 5. and max_connections is 151. This is for the time that website is running and has no problems. Nov 24, 2017 at 13:55
  • At some point you did use all 151 connections (plus 1 extra for root). There are probably no clues available of what happened then.
    – Rick James
    Nov 24, 2017 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.