I'm adding a new column to an existing table say
ABC with the following query,
ALTER TABLE ABC add column
STATUSint(10) NOT NULL
This is done in master, slave and secondary slaves. Meanwhile, while altering the existing table (which does not contain any data) - other databases (other schemas and tables) were queried - both in master and slave databases.
After altering column in the table
ABC has been completed I could see slowness in many app servers and few got killed stating "You have reached your max limit to DB Connections" and you could make no more DB connections and the app server got killed.
I also run a
jsp in one of my servers - the thread gets hanged (stopped after making connection to one of secondary slave) in the mean time. I'm a no DB expert. I'm a novice MySQL learner and do not have any prior experience I do not know how to debug further.
I suspect whether my MySQL servers (either master or slave) does not accept any incoming connections while altering a table and once the table alteration gets completed it accepts the queued up requests and process them one by one.
Since the connections got queued up the app servers - which are waiting for previous query response - couldn't make any more requests. Can someone shed some insights to debug
on db connection getting exhausted and
the thread which invoked a
jspnever ends (stops where it makes connection to mysql) - this happened during the timestamp when