task

PROBLEM TO SOLUTION

FIX-PRB# 7140

max user connections exceeded

  1. TITLE =
    1. 'PRB-PG-*.*' 
  2. DESC =
    1. WHEN='click {random page}' RESULT='error msg', msg='max user connections exceeded (limit=10)',
    2. ] ERROR - PAGE search/tasks, search = id=7128, PG view/tasks ERROR 
    3. ] flush mysql  
    4. ] g?='mysql tracking connections' 
  3. [WHERE]
    1. ] ON PG = 'random'
  4. [WHEN]
    1. ] ON PG loads
  5. [WHAT.HAPPENS.WHEN]
    1. x] ERROR MSG = 'max user connections exceeded (limit=10)'
    2. x] ERROR MSG DETAILS ='see file' for extended details
  6. [CAUSE.SUSPECT]
    1. x] = might be something to do with having too many 'user_connections' ??? /s
    2. x] APP is leaving db connections open,  
  7. [TRY =]
    1. ]
    2. [TEST=]
      1. ] ...
    3. [RESULT=]
      1. ] ...
  8. [RESOLUTION=]
    1. x] increased connections limit from:10 to:20
    2. i] added explicit db close, db connection close, db dispose to *.cs files
    3. i] added using statement to /qry files 
    4. x*] /new = REM call to getPath() method which opens ... for each 
    5. x] User.cs = REM tracking db property 
    6. x] update web.config for app .org to use seperate/different db user connection VS same as .com 
  9. [STATUS]
    1. ] RESOLVED, ] Pending,i] Working
  10. [OTHER]
    1. x] openDate:2017-03-16
    2. x] openBy:don.sagrott
    3. i] startDate:2017-03-16
    4. ] completeDate:
    5. ] owner: don.sagrott
    6. ] time:*&^%##

[previously]

  1. [2017-03-15][00:00] NEW task IN ?
    1. x] # 7140 - CREATE-article# # - FIX-PRB-7140 - max-user-connections-exceeded
  2. [00:00] NEW article IN 
    1. x] # # -
  3. [00:00] NEW task IN REFERENCE
    1. x] # 7125 - MIGRATION-steps-all  ( CC ) 
  4. [00:00] NEW task IN TRY
    1. x] SET GLOBAL max_user_connections=100; 
    2. x] RESULT=FAIL - need 'super' permissions
  5. [00:00] NEW task IN RESEARCH
    1. ] research g?='asp.net close connection'
    2. ] + BM stack 
    3. ] + BM codeproject 
    4. ] show global variables like '%connections%'; 
    5. ] HOST - arvixe = max_user_connections = 0, max_connnections = 450 
    6. ] HOST - smarter - max_user_connections = 10, max = 1000, 
    7. ] show status like '%onn%'; 
  6. [00:00] NEW task
    1. x] # 7141 - TRANSCRIPT-SUPPORT-chat (or lack there-of) x] AGENT:Bob SUGGESTS: x] document-flush connections, x] submit a ticket 
    2. *] flushing connections will (usually/sometimes) clear issue for period of time
  7. [2017-03-dd][00:00] NEW task
    1. x] # # - SUPPORT-email 
    2. x] # # - max_user_connections increased to 20
    3. x] # # - UPD - support ticket - response, connections limit has been increased from:10 to: 20 
  8. [00:00] NEW task IN TRY
    1. i] ADD explicit {db}.connection.close, {db}.close statements to class code, 
    2. ] EX - db.Connection.Close()
    3. ] EX - db.Close()
    4. ] LIST = x] Calendar, x] Plan, x] User, x] Task, x] TaskGroup 
    5. ] ERROR stack trace - 'Plan.cs' - line 137 ish
    6. ] BM - article - on properly closing connections 
    7.  ** MU = webmatrix database helper should close connections
    8. i] review add/task - classes in use before
    9. ] _App - 
  9. [00:00] NEW task IN TEST
    1. x] # # - TEST  =  
    2. x] PASS - ADD task, 
    3. x] PASS - view pages(l1 - libraries, warehouses, ...), 
    4. x] PASS - EDIT task
    5. ] FAIL = view/new - scrolling back (x) pages, will eventually get you a maxed connection, 
    6. ] AKA its better, BUT its NOT FIXED
  10. [2017-04-05]>[00:00] NEW task IN 
    1. ] # # - Library.cs - adding explicit close connection, close db calls to each method where db is invoked
  11. [2017-04-10][00:00] NEW task IN UPDATES
    1. x] # # - code updates to subjects library classes 
    2. x] # 1725 Library, x] # 1726 Book, x] # 1727 Chapter and x] # 1728 Article classes, 
    3. x] adding explicit connection close, db close statement
  12. [00:00] NEW task IN TEST
    1. ] # # - RESULT=FAIL doesn't appear to be making much of/any difference
  13. [00:00] NEW task IN TRY 
    1. x] use mysql workbench to ts sleeping connections
    2. x] # # - setup connection to remote db, db = .org , usr=default (not dataed)
    3. *] server status - shows connections, i dont think thats max user 
    4. *] show processlist
    5. *] show status like '%onn%'
    6. ] TEST = 
    7. ] RESULT = 
  14. [00:00] NEW task IN TRY
    1. ] # # -  ADD explicit "db.Dispose(); statement to Article.cs
    2. x] TEST = view/new - 
    3. x] RESULT = FAIL still crapping out in about the same area, MAR-28
  15. [00:00] NEW task IN TRY
    1. x] # # - clear browser cache - (are error pages cached, is this why its breaking at the same point)
    2. x] TEST = 
    3. x] RESULT = FAIL
  16. > [00:00] NEW task IN TRY
    1.  x] maybe other classes in use are, other classes = ] User,  ] _app 
    2. x] ADD db close.conn, db close, db dispose statements to User.cs
    3. *] BUT user isnt the PRB BC this is different DB
    4. x] _App isnt the PRB BC it doesnt make any DB calls
    5. x] TEST = same 
    6. x] RESULT=FAIL, aka no change
  17. [00:00] NEW task IN TRY
    1. x] REM call to this method(getPath()) on PAGE "new" returned items
    2. x] I have a method call getPath() that returns the full path name for an article, but it opens new Library, Book, Chapter for each record in returned articles set
  18. [00:00] NEW task IN TEST
    1. x] TEST = same, RESULT = pass 
  19. [00:00] NEW task IN TRY
    1. x] adding explicit db.Dispose(); 
    2. x] # # - code updates to subjects library classes ] # 1725 Library, ] # 1726 Book, ] # 1727 Chapter and ] # 1728 Article classes, 
  20. [00:00] NEW task IN TEST
    1. x] # # - TEST = view/new from today, navigate back - RESULT = FAIL still crapping out in the exact same area, MAR-28
  21. [2017-04-10]>[00:00] NEW task IN /5545-migration
    1. i] use mysql wkbench to TS 
    2. i] explicitly db.closeConnection() 
    3. i] explicitly db.close(), 
    4. i] exlicitly dispose() db 
  22. [2017-04-13][00:00] NEW task
    1. ] # # - open support ticket# 2 - again experiencing issues, 150+ sessions, 
    2. ] add/task 
  23. [2017-04-dd][00:00] NEW task 
    1. x] # 1972 User.cs - 
    2. x] REM tracking db property - EVERY user was getting a connection to our 'tracking/loggin' db, BUT it isnt being used 
    3. x] REM logVisit() method, 
    4. *] PREV added ] close db, ] close conn, ] dispose db to class methods
    5. x] TEST = walkback thru 'new today', 
    6. x] RESULT=PASS, 
  24. [2017-04-23]>[00:00] NEW task IN 
    1. i] USING add/article - PRB errors, @80 users 
  25. [2017-04-24][00:00] NEW task IN 
    1. x] qry/{* files} - TRY 'using db' statement vs db open to auto close, database conn
    2. x] FILES = /qry/articles(3), x] /qry/tasks(3), x] /qry/items(3), x] /qry/places(3), x] /qry/people(3), x] systems(3)
    3. x] 3 files each, based file specified in _App.cs, where  
    4. x] TEST - x] articles, x] tasks, x] items, x] places, x] people, x] systems
    5. *] on PG add/article, add/..., SELECT 3 dropdowns, sospephelpers function, _App object props ( = filenames to /qry files, which execute queries, return result set)
  26. [2017-04-25][00:00] UPDATE task
    1. x] 300 online, NOT getting hardly any errors, 
    2. x] TEST = adding task, x] adding article, x] edits 
    3. x] ERROR - add book - using option default chapters - auto adds chapter, ( x] ADDED next=)
  27. [2017-04-28][00:00] NEW task IN 
    1. x] again errors, w < 100 users online
    2. x] assign different db user to conn string for .org VS .com, 
    3. x] local .org/web.config settings aligned with remote,  
    4. x] fix conn string for db-{user=e***} 
    5. +] migration-notes.txt
    6. x] TEST = /view/...,  /add/task, /add/article, 
    7. x] RESULT = pass
  28. [2017-05-20] NEW task IN
    1. [x] FIX-PRB-7325 - Ticket ID 939275, max_user_connections REQUEST increase to 20 on all current dbs, 2017-05-20
    2. [x] Ticket ID 939275 
    3. [x] Can you please set the max_user_connections for the following databases to 20. 
    4. [x] ADD this.task to 7140
    5. [x] SRC = FIX-PRB-7325, reuse as new
  29. [2017-12-21][17:15] NEW task IN ?
    1. ] # # - CREATE-article# # - task = F I max_use_connections
    2. ] 2017-12-21
    3. x] previously used "using db' VS open db in ?classes?, this 'seemed to' 'resolve' the issue with max_user connections 
    4. x] dont seem to be able to find any documentation or notes on it
    5. x] wait I do HAVE some ' notes' in id= 7140, item # 25 on 2017-04-24 
    6. x] BUT i still dont seem to be able to find 'code' example/sample that i implmented

[currently]

  1. [00:00] NEW task 
    1. ]
  2. [00:00] NEW article IN 
    1. ] # # -
  3. [00:00] NEW task IN TRY ** SOLUTION **
    1. ] # # - ADD wrap "db call in using statement", connections will then be automatically closed and disposed of

[next]

  1. [00:00] NEW task IN 
    1. ] # # - COPY revised ] E task, TaskGroup, Plan, Calendar class files to s .org
  2. [00:00] NEW task IN 
    1. ] # # - COPY revised ] S Article, Book, Chapter, Library class files to s .org
  3. [00:00] NEW task IN REWRITE
    1. ] # # - O Item, ...
    2. ] # # - Y Step, ...
    3. ] # # - P Person, ...
    4. ] # # - L Place, ... 
  4. [2017-04-23][00:00] NEW task IN
    1. ] review all /qry files, ] explicitly close db connections
  5. [2017-04-23]>[00:00] NEW task IN 
    1. ] # # - FIX-PRB-Nnnn -  
    2. ] ERROR - add book - using option default chapters -  auto adds chapter, article records, before ERROR, ADDs book, chapter contents, chapter index, article contents, THEN ERROR before article index
    3. ] Book.CS - method 

[reference]

  1. ] https://forums.asp.net/t/1847019.aspx?Closing+a+database+in+WebMatrix 
  2. ] https://major.io/2007/05/20/mysql-connections-in-sleep-state/

 

 

User 'db******ed' has exceeded the 'max_user_connections' resource (current value: 20)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: MySql.Data.MySqlClient.MySqlException: User 'db_a19eea_dataed' has exceeded the 'max_user_connections' resource (current value: 20)

Source Error: 

Line 57:     
Line 58:     public Library(int id_library){
Line 59: var x = sospep.QuerySingle(qryGetLibrary,id_library); Line 60:         this.id_library = x.id_library;
Line 61:         this.name_library = x.name_library;

 

 
 
Details Photos Edit more

Details

ID: 7140

NAME: FIX-PRB-7140

DESCRIPTION: ] # # - FIX-PRB-7140 - user ### has exceeded max user connections (10),

START DATE TIME: 2017-03-16 07:11:17

EST DURATION: 01:00:00

END DATE TIME: 2017-03-16 08:11:17

STATUS: To-Do

PRIORITY: -5

OWNER ID: 1

Content Photos Edit more

photos

photos for this task

actions

Agenda Email task SMS task Priorities