(public).sospep.events ~ view task ~ FIX-PRB-7140

<h1 style="text-align: center;">PROBLEM TO SOLUTION</h1>
<h1 style="text-align: center;">FIX-PRB# 7140</h1>
<h1 style="text-align: center;">max user connections exceeded</h1>
<ol>
<li>TITLE =</li>
<ol>
<li>'PRB-PG-*.*'&nbsp;</li>
</ol>
<li>DESC =</li>
<ol>
<li>WHEN='click {random page}' RESULT='error msg', msg='max user connections exceeded (limit=10)',</li>
<li>] ERROR - PAGE search/tasks, search = id=7128, PG view/tasks ERROR&nbsp;</li>
<li>] flush mysql &nbsp;</li>
<li>] g?='mysql tracking connections'&nbsp;</li>
</ol>
<li>[WHERE]</li>
<ol>
<li>] ON PG = 'random'</li>
</ol>
<li>[WHEN]</li>
<ol>
<li>] ON PG loads</li>
</ol>
<li>[WHAT.HAPPENS.WHEN]</li>
<ol>
<li>x] ERROR MSG&nbsp;= 'max user connections exceeded (limit=10)'</li>
<li>x] ERROR MSG DETAILS ='see file' for extended details</li>
</ol>
<li>[CAUSE.SUSPECT]</li>
<ol>
<li>x] = might be something to do with having too many 'user_connections' ??? /s</li>
<li>x] APP is leaving db connections open, &nbsp;</li>
</ol>
<li>[TRY =]</li>
<ol>
<li>]</li>
<li>[TEST=]</li>
<ol>
<li>] ...</li>
</ol>
<li>[RESULT=]</li>
<ol>
<li>] ...</li>
</ol></ol>
<li>[RESOLUTION=]</li>
<ol>
<li>x] increased connections limit from:10 to:20</li>
<li>i] added explicit db close, db connection close, db dispose to *.cs files</li>
<li>i] added using statement to /qry files&nbsp;</li>
<li>x*] /new = REM call to getPath() method which opens ... for each&nbsp;</li>
<li>x] User.cs = REM tracking db property&nbsp;</li>
<li>x] update web.config for app .org to use seperate/different db user connection VS same as .com&nbsp;</li>
</ol>
<li>[STATUS]</li>
<ol>
<li>] RESOLVED, ] Pending,i] Working</li>
</ol>
<li>[OTHER]</li>
<ol>
<li>x] openDate:2017-03-16</li>
<li>x] openBy:don.sagrott</li>
<li>i] startDate:2017-03-16</li>
<li>] completeDate:</li>
<li>] owner: don.sagrott</li>
<li>] time:*&amp;^%##</li>
</ol></ol>
<h2>[previously]</h2>
<ol>
<li>[2017-03-15][00:00] NEW task IN ?</li>
<ol>
<li>x] # 7140 - CREATE-article# # -&nbsp;<a href="/view/task?id=7140" target="_blank">FIX-PRB-7140</a>&nbsp;- max-user-connections-exceeded</li>
</ol>
<li>[00:00] NEW article IN&nbsp;</li>
<ol>
<li>x] # # -</li>
</ol>
<li>[00:00] NEW task IN REFERENCE</li>
<ol>
<li>x] # 7125 - MIGRATION-steps-all &nbsp;( CC )&nbsp;</li>
</ol>
<li>[00:00] NEW task IN TRY</li>
<ol>
<li>x] SET GLOBAL max_user_connections=100;&nbsp;</li>
<li>x] RESULT=FAIL - need 'super' permissions</li>
</ol>
<li>[00:00] NEW task IN RESEARCH</li>
<ol>
<li>] research g?='asp.net close connection'</li>
<li>] + BM stack&nbsp;</li>
<li>] + BM codeproject&nbsp;</li>
<li>] show global variables like '%connections%';&nbsp;</li>
<li>] HOST - arvixe = max_user_connections = 0, max_connnections = 450&nbsp;</li>
<li>] HOST - smarter - max_user_connections = 10, max = 1000,&nbsp;</li>
<li>] show status like '%onn%';&nbsp;</li>
</ol>
<li>[00:00] NEW task</li>
<ol>
<li>x] # 7141 -&nbsp;<a href="/view/task?id=7141" target="_blank">TRANSCRIPT-SUPPORT-chat</a>&nbsp;(or lack there-of) x] AGENT:Bob SUGGESTS: x]&nbsp;document-flush connections, x] submit a ticket&nbsp;</li>
<li>*] flushing connections will (usually/sometimes) clear issue for period of time</li>
</ol>
<li>[2017-03-dd][00:00] NEW task</li>
<ol>
<li>x] # # -&nbsp;SUPPORT-email&nbsp;</li>
<li>x] # # - max_user_connections increased to 20</li>
<li>x] # # - UPD - support ticket - response, connections limit has been increased from:10 to: 20&nbsp;</li>
</ol>
<li>[00:00] NEW task IN TRY</li>
<ol>
<li>i] ADD explicit {db}.connection.close, {db}.close statements to class code,&nbsp;</li>
<li>] EX - db.Connection.Close()</li>
<li>] EX - db.Close()</li>
<li>] LIST = x] Calendar, x] Plan, x] User, x] Task, x] TaskGroup&nbsp;</li>
<li>] ERROR stack trace - 'Plan.cs' - line 137 ish</li>
<li>] BM - article - on properly closing connections&nbsp;</li>
<li>&nbsp;** MU = webmatrix database helper should close connections</li>
<li>i] review add/task - classes in use before</li>
<li>] _App -&nbsp;</li>
</ol>
<li>[00:00] NEW task IN TEST</li>
<ol>
<li>x] # # - TEST &nbsp;= &nbsp;</li>
<li>x] PASS - ADD task,&nbsp;</li>
<li>x] PASS - view pages(l1 - libraries, warehouses, ...),&nbsp;</li>
<li>x] PASS - EDIT task</li>
<li>] FAIL = view/new - scrolling back (x) pages, will eventually get you a maxed connection,&nbsp;</li>
<li>] AKA its better, BUT its NOT FIXED</li>
</ol>
<li>[2017-04-05]&gt;[00:00] NEW task IN&nbsp;</li>
<ol>
<li>] # # - Library.cs - adding explicit close connection, close db calls to each method where db is invoked</li>
</ol>
<li>[2017-04-10][00:00] NEW task IN UPDATES</li>
<ol>
<li>x] # # - code updates to subjects library classes&nbsp;</li>
<li>x] # 1725 Library, x] # 1726 Book, x] # 1727 Chapter and x] # 1728 Article classes,&nbsp;</li>
<li>x] adding explicit connection close, db close statement</li>
</ol>
<li>[00:00] NEW task IN TEST</li>
<ol>
<li>] # # - RESULT=FAIL doesn't appear to be making much of/any difference</li>
</ol>
<li>[00:00] NEW task IN TRY&nbsp;</li>
<ol>
<li>x] use mysql workbench to ts sleeping connections</li>
<li>x] # # - setup connection to remote db, db = .org , usr=default (not dataed)</li>
<li>*] server status - shows connections, i dont think thats max user&nbsp;</li>
<li>*] show processlist</li>
<li>*] show status like '%onn%'</li>
<li>] TEST =&nbsp;</li>
<li>] RESULT =&nbsp;</li>
</ol>
<li>[00:00] NEW task IN TRY</li>
<ol>
<li>] # # - &nbsp;ADD explicit "db.Dispose(); statement to Article.cs</li>
<li>x] TEST = view/new -&nbsp;</li>
<li>x] RESULT = FAIL still crapping out in about the same area, MAR-28</li>
</ol>
<li>[00:00] NEW task IN TRY</li>
<ol>
<li>x] # # - clear browser cache - (are error pages cached, is this why its breaking at the same point)</li>
<li>x] TEST =&nbsp;</li>
<li>x] RESULT = FAIL</li>
</ol>
<li>&gt; [00:00] NEW task IN TRY</li>
<ol>
<li>&nbsp;x] maybe other classes in use are, other classes = ] User, &nbsp;] _app&nbsp;</li>
<li>x] ADD db close.conn, db close, db dispose statements to User.cs</li>
<li>*] BUT user isnt the PRB BC this is different DB</li>
<li>x] _App isnt the PRB BC it doesnt make any DB calls</li>
<li>x] TEST = same&nbsp;</li>
<li>x] RESULT=FAIL, aka no change</li>
</ol>
<li>[00:00] NEW task IN TRY</li>
<ol>
<li>x] REM call to this method(getPath()) on PAGE "new" returned items</li>
<li>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</li>
</ol>
<li>[00:00] NEW task IN TEST</li>
<ol>
<li>x] TEST = same, RESULT = pass&nbsp;</li>
</ol>
<li>[00:00] NEW task IN TRY</li>
<ol>
<li>x] adding explicit db.Dispose();&nbsp;</li>
<li>x] # # - code updates to subjects library classes ] # 1725 Library, ] # 1726 Book, ] # 1727 Chapter and ] # 1728 Article classes,&nbsp;</li>
</ol>
<li>[00:00] NEW task IN TEST</li>
<ol>
<li>x] # # - TEST = view/new from today, navigate back - RESULT = FAIL still crapping out in the exact same area, MAR-28</li>
</ol>
<li>[2017-04-10]&gt;[00:00] NEW task IN /5545-migration</li>
<ol>
<li>i] use mysql wkbench to TS&nbsp;</li>
<li>i] explicitly db.closeConnection()&nbsp;</li>
<li>i] explicitly db.close(),&nbsp;</li>
<li>i] exlicitly dispose() db&nbsp;</li>
</ol>
<li>[2017-04-13][00:00] NEW task</li>
<ol>
<li>] # # - open support ticket# 2 - again experiencing issues, 150+ sessions,&nbsp;</li>
<li>] add/task&nbsp;</li>
</ol>
<li>[2017-04-dd][00:00] NEW task&nbsp;</li>
<ol>
<li>x] # 1972 User.cs -&nbsp;</li>
<li>x] REM tracking db property - EVERY user was getting a connection to our 'tracking/loggin' db, BUT it isnt being used&nbsp;</li>
<li>x] REM logVisit() method,&nbsp;</li>
<li>*] PREV added ] close db, ] close conn, ] dispose db to class methods</li>
<li>x] TEST = walkback thru 'new today',&nbsp;</li>
<li>x] RESULT=PASS,&nbsp;</li>
</ol>
<li>[2017-04-23]&gt;[00:00] NEW task IN&nbsp;</li>
<ol>
<li>i] USING add/article - PRB errors, @80 users&nbsp;</li>
</ol>
<li>[2017-04-24][00:00] NEW task IN&nbsp;</li>
<ol>
<li>x] qry/{* files} - TRY 'using db' statement vs db open to auto close, database conn</li>
<li>x] FILES = /qry/articles(3), x] /qry/tasks(3), x] /qry/items(3), x] /qry/places(3), x] /qry/people(3), x] systems(3)</li>
<li>x] 3 files each, based file specified in _App.cs, where &nbsp;</li>
<li>x] TEST - x] articles, x] tasks, x] items, x] places, x] people, x] systems</li>
<li>*] on PG add/article, add/..., SELECT 3 dropdowns, sospephelpers function, _App object props ( = filenames to /qry files, which execute queries, return result set)</li>
</ol>
<li>[2017-04-25][00:00] UPDATE task</li>
<ol>
<li>x] 300 online, NOT getting hardly any errors,&nbsp;</li>
<li>x] TEST = adding task, x] adding article, x] edits&nbsp;</li>
<li>x] ERROR - add book - using option default chapters - auto adds chapter, ( x] ADDED next=)</li>
</ol>
<li>[2017-04-28][00:00] NEW task IN&nbsp;</li>
<ol>
<li>x] again errors, w &lt; 100 users online</li>
<li>x] assign different db user to conn string for .org VS .com,&nbsp;</li>
<li>x] local .org/web.config settings aligned with remote, &nbsp;</li>
<li>x] fix conn string for db-{user=e***}&nbsp;</li>
<li>+] migration-notes.txt</li>
<li>x] TEST = /view/..., &nbsp;/add/task, /add/article,&nbsp;</li>
<li>x] RESULT = pass</li>
</ol>
<li>[2017-05-20] NEW task IN</li>
<ol>
<li>[x] FIX-PRB-7325 - Ticket ID 939275, max_user_connections REQUEST increase to 20 on all current dbs, 2017-05-20</li>
<li>[x] Ticket ID 939275&nbsp;</li>
<li>[x] Can you please set the max_user_connections for the following databases to 20.&nbsp;</li>
<li>[x] ADD this.task to 7140</li>
<li>[x] SRC = FIX-PRB-7325, reuse as new</li>
</ol>
<li>[2017-12-21][17:15] NEW task IN ?</li>
<ol>
<li>] # # - CREATE-article# # - task = F I max_use_connections</li>
<li>] 2017-12-21</li>
<li>x] previously used "using db' VS open db in ?classes?, this 'seemed to' 'resolve' the issue with max_user connections&nbsp;</li>
<li>x] dont seem to be able to find any documentation or notes on it</li>
<li>x] wait I do HAVE some ' notes' in id= 7140, item # 25 on 2017-04-24&nbsp;</li>
<li>x] BUT i still dont seem to be able to find 'code' example/sample that i implmented</li>
</ol></ol>
<div> </div>
<h2>[currently]</h2>
<div><ol>
<li>[00:00] NEW task&nbsp;</li>
<ol>
<li>]</li>
</ol>
<li>[00:00] NEW article IN&nbsp;</li>
<ol>
<li>] # # -</li>
</ol>
<li>[00:00] NEW task IN TRY ** SOLUTION **</li>
<ol>
<li>] # # - ADD wrap "db call in using statement", connections will then be automatically closed and disposed of</li>
</ol></ol></div>
<h2>[next]</h2>
<ol>
<li>[00:00] NEW task IN&nbsp;</li>
<ol>
<li>] # # - COPY revised ] E task, TaskGroup, Plan, Calendar class files to s .org</li>
</ol>
<li>[00:00] NEW task IN&nbsp;</li>
<ol>
<li>] # # - COPY revised ] S Article, Book, Chapter, Library class files to s .org</li>
</ol>
<li>[00:00] NEW task IN REWRITE</li>
<ol>
<li>] # # - O Item, ...</li>
<li>] # # - Y Step, ...</li>
<li>] # # - P Person, ...</li>
<li>] # # - L Place, ...&nbsp;</li>
</ol>
<li>[2017-04-23][00:00] NEW task IN</li>
<ol>
<li>] review all /qry files, ] explicitly close db connections</li>
</ol>
<li>[2017-04-23]&gt;[00:00] NEW task IN&nbsp;</li>
<ol>
<li>] # # - FIX-PRB-Nnnn - &nbsp;</li>
<li>] ERROR - add book - using option default chapters -&nbsp; auto adds chapter, article records, before ERROR, ADDs book, chapter contents, chapter index, article contents, THEN ERROR before article index</li>
<li>] Book.CS - method&nbsp;</li>
</ol></ol>
<h2>[reference]</h2>
<ol>
<li>] https://forums.asp.net/t/1847019.aspx?Closing+a+database+in+WebMatrix&nbsp;</li>
<li>] https://major.io/2007/05/20/mysql-connections-in-sleep-state/</li>
</ol>
&nbsp;
&nbsp;
<h2 style="font-family: Verdana; font-weight: normal; font-size: 14pt; color: maroon;">User 'db******ed' has exceeded the 'max_user_connections' resource (current value: 20)</h2>
Description:&nbsp;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.&nbsp; Exception Details:&nbsp;MySql.Data.MySqlClient.MySqlException: User 'db_a19eea_dataed' has exceeded the 'max_user_connections' resource (current value: 20) Source Error:&nbsp; 
<table width="100%" bgcolor="#ffffcc">
<tbody>
<tr>
<td>
<pre style="font-family: Consolas, 'Lucida Console', monospace; font-size: 11pt; margin: 0px; padding: 0.5em; line-height: 14pt;">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;</pre>
</td>
</tr>
</tbody>
</table>
&nbsp;
<div>&nbsp;</div>
<div>&nbsp;</div>

~ view task ~ TaskGroup] PROJECT-5545 FROM [ Plan] plan-projects IN [ calendars] prj-024-sospep-business