Fatal error encountered during command execution in C#

This forum is for programmers who have questions about the source code.
Post Reply
ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Fatal error encountered during command execution in C#

Post by ajhalls » Thu Apr 12, 2018 11:02 am

I am having trouble with running this query. On my personal computer I had upgraded MySQL to 5.7 to do some better logging and this query works just fine. When I put it into production on 5.5 it works, but only sometimes.

Using HeidiSQL it works but takes 50 seconds to run (only takes 2.6 seconds on my PC using the same data). Using my C# app, I get an error "Fatal error encountered during command execution". Any idea what is different about those two situations that it would work in one but not the other? Does the execution time have any bearing on it?

Code: Select all

SELECT p.patnum            AS reference_id, 
       p.guarantor         AS guarantor_reference_id, 
       p.bal_0_30          AS balance_0_30, 
       p.bal_31_60         AS balance_31_60, 
       p.bal_61_90         AS balance_61_90, 
       p.balover90         AS balance_over_90, 
       p.baltotal          AS account_balance, 
       pending_writeoff, 
       claims_estimated_amount, 
       patient_portion_due AS amount_due_from_patient, 
       Sum(procfee)        AS lifetime_value 
FROM   patient AS p 
       LEFT JOIN (SELECT patient.patnum, 
                         patient.guarantor, 
                         patient.bal_0_30, 
                         patient.bal_31_60, 
                         patient.bal_61_90, 
                         patient.balover90, 
                         guarAging.baltotal, 
                         guarAging.inswoest 
                                             AS 
                                                       pending_writeoff, 
                         guarAging.inspayest 
                                             AS 
                                                       claims_estimated_amount, 
                         guarAging.baltotal - guarAging.inspayest - 
                         guarAging.inswoest 
                                             AS 
                         patient_portion_due 
                  FROM   (SELECT tSums.patnum, 
                                 Round(CASE 
                                         WHEN tSums.totalcredits >= 
                                              tSums.chargesover90 
                                       THEN 0 
                                         ELSE tSums.chargesover90 - 
                                              tSums.totalcredits 
                                       end, 2)              Balover90, 
                                 Round(CASE 
                                         WHEN tSums.totalcredits <= 
                                              tSums.chargesover90 
                                       THEN 
                                         tSums.charges_61_90 
                                         WHEN tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              <= 
                                              tSums.totalcredits 
                                       THEN 0 
                                         ELSE tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              - 
                                              tSums.totalcredits 
                                       end, 2)              Bal_61_90, 
                                 Round(CASE 
                                         WHEN tSums.totalcredits < 
                                              tSums.chargesover90 + 
                                              tSums.charges_61_90 THEN 
                                         tSums.charges_31_60 
                                         WHEN tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              + tSums.charges_31_60 <= 
                                              tSums.totalcredits THEN 0 
                                         ELSE tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              + tSums.charges_31_60 - 
                                              tSums.totalcredits 
                                       end, 2)              Bal_31_60, 
                                 Round(CASE 
                                         WHEN tSums.totalcredits < 
                                              tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              + tSums.charges_31_60 THEN 
                                         tSums.charges_0_30 
                                         WHEN tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              + tSums.charges_31_60 + 
                                              tSums.charges_0_30 <= 
                                              tSums.totalcredits 
                                       THEN 0 
                                         ELSE tSums.chargesover90 + 
                                              tSums.charges_61_90 
                                              + tSums.charges_31_60 + 
                                              tSums.charges_0_30 - 
                                              tSums.totalcredits 
                                       end, 2)              Bal_0_30, 
                                 Round(tSums.baltotal, 2)   BalTotal, 
                                 Round(tSums.inswoest, 2)   InsWoEst, 
                                 Round(tSums.inspayest, 2)  InsPayEst, 
                                 Round(tSums.payplandue, 2) PayPlanDue 
                          FROM   (SELECT p.guarantor              PatNum, 
                                         Sum(CASE 
                                               WHEN trans.tranamount > 0 
                                                    AND trans.trandate < 
                                                        '2018-01-11' 
                                             THEN 
                                               trans.tranamount 
                                               ELSE 0 
                                             end)                 ChargesOver90, 
                                         Sum(CASE 
                                               WHEN trans.tranamount > 0 
                                                    AND trans.trandate < 
                                                        '2018-01-12' 
                                                    AND trans.trandate >= 
                                                        '2018-02-10' 
                                             THEN 
                                               trans.tranamount 
                                               ELSE 0 
                                             end)                 Charges_61_90, 
                                         Sum(CASE 
                                               WHEN trans.tranamount > 0 
                                                    AND trans.trandate < 
                                                        '2018-02-11' 
                                                    AND trans.trandate >= 
                                                        '2018-03-12' 
                                             THEN 
                                               trans.tranamount 
                                               ELSE 0 
                                             end)                 Charges_31_60, 
                                         Sum(CASE 
                                               WHEN trans.tranamount > 0 
                                                    AND trans.trandate <= 
                                                        '2018-03-13' 
                                                    AND trans.trandate >= 
                                                        '2018-04-12' 
                                             THEN 
                                               trans.tranamount 
                                               ELSE 0 
                                             end)                 Charges_0_30, 
                                         -Sum(CASE 
                                                WHEN trans.tranamount < 0 
                                                     AND trans.trandate <= 
                                                         '2018-04-12' 
                                              THEN 
                                                trans.tranamount 
                                                ELSE 0 
                                              end)                TotalCredits, 
                                         Sum(CASE 
                                               WHEN trans.tranamount != 0 THEN 
                                               trans.tranamount 
                                               ELSE 0 
                                             end)                 BalTotal, 
                                         Sum(trans.inswoest)      InsWoEst, 
                                         Sum(trans.inspayest)     InsPayEst, 
                                         Sum(trans.payplanamount) PayPlanDue 
                                  FROM   (SELECT 'Proc' 
                                                 TranType, 
                                                 pl.patnum, 
                                                 pl.procdate 
                                                 TranDate, 
                                                 pl.procfee * ( 
                                                 pl.unitqty + pl.baseunits ) 
                                                 TranAmount, 
                                                 0 
                                                 PayPlanAmount 
                                                 , 
                                                 0 
                                                 InsWoEst, 
                                                 0 
                                                 InsPayEst 
                                          FROM   procedurelog pl 
                                          WHERE  pl.procstatus = 2 
                                                 AND pl.procfee != 0 
                                          UNION ALL 
                                          SELECT 'Claimproc' TranType, 
                                                 cp.patnum, 
                                                 cp.datecp   TranDate, 
                                                 ( CASE 
                                                     WHEN cp. status != 0 THEN 
                                                     ( CASE 
                                                     WHEN cp.payplannum = 0 THEN 
                                                     - 
                                                     cp.inspayamt 
                                                     ELSE 0 
                                                       end ) 
                                                     - 
                                                     cp.writeoff 
                                                     ELSE 0 
                                                   end )     TranAmount, 
                                                 ( CASE 
                                                     WHEN cp.payplannum != 0 
                                                          AND cp. status IN ( 1, 
                                                              4, 5 ) 
                                                   THEN - 
                                                     cp.inspayamt 
                                                     ELSE 0 
                                                   end )     PayPlanAmount, 
                                                 ( CASE 
                                                     WHEN cp. status = 0 THEN 
                                                     cp.writeoff 
                                                     ELSE 0 
                                                   end )     InsWoEst, 
                                                 ( CASE 
                                                     WHEN cp. status = 0 THEN 
                                                     cp.inspayest 
                                                     ELSE 0 
                                                   end )     InsPayEst 
                                          FROM   claimproc cp 
                                          WHERE  cp. status IN ( 0, 1, 4, 5, 7 ) 
                                          HAVING tranamount != 0 
                                                  OR payplanamount != 0 
                                                  OR inswoest != 0 
                                                  OR inspayest != 0 
                                          UNION ALL 
                                          SELECT 'Adj'     TranType, 
                                                 a.patnum, 
                                                 a.adjdate TranDate, 
                                                 a.adjamt  TranAmount, 
                                                 0         PayPlanAmount, 
                                                 0         InsWoEst, 
                                                 0         InsPayEst 
                                          FROM   adjustment a 
                                          WHERE  a.adjamt != 0 
                                          UNION ALL 
                                          SELECT 'PatPay'   TranType, 
                                                 ps.patnum, 
                                                 ps.datepay TranDate, 
                                                 ( CASE 
                                                     WHEN ps.payplannum = 0 THEN 
                                                     - 
                                                     ps.splitamt 
                                                     ELSE 0 
                                                   end )    TranAmount, 
                                                 ( CASE 
                                                     WHEN ps.payplannum != 0 
                                                   THEN - 
                                                     ps.splitamt 
                                                     ELSE 0 
                                                   end )    PayPlanAmount, 
                                                 0          InsWoEst, 
                                                 0          InsPayEst 
                                          FROM   paysplit ps 
                                          WHERE  ps.splitamt != 0 
                                          UNION ALL 
                                          SELECT 'PPCharge' 
                                                 TranType, 
                                                 ppc.guarantor 
                                                 PatNum, 
                                                 ppc.chargedate 
                                                 TranDate, 
                                                 0 
                                                 TranAmount, 
                                                 Coalesce (ppc.principal + 
                                                 ppc.interest, 0) 
                                                 PayPlanAmount 
                                                 , 
                                                 0 
                                                 InsWoEst, 
                                                 0 
                                                 InsPayEst 
                                          FROM   payplancharge ppc 
                                          WHERE  ppc.chargedate <= '2018-04-22' 
                                                 AND ppc.chargetype = 0 
                                                 AND Coalesce (ppc.principal + 
                                                               ppc.interest, 0) 
                                                     != 0 
                                          UNION ALL 
                                          SELECT 'PPComplete'     TranType, 
                                                 pp.patnum, 
                                                 pp.payplandate   TranDate, 
                                                 -pp.completedamt TranAmount, 
                                                 0                PayPlanAmount, 
                                                 0                InsWoEst, 
                                                 0                InsPayEst 
                                          FROM   payplan pp 
                                          WHERE  pp.completedamt != 0) trans 
                                         RIGHT JOIN patient p 
                                                 ON p.patnum = trans.patnum 
                                  GROUP  BY p.guarantor 
                                  ORDER  BY NULL) tSums) guarAging 
                         INNER JOIN patient 
                                 ON patient.patnum = guarAging.patnum 
                         LEFT JOIN (SELECT patnum, 
                                           claimstatus, 
                                           claimfee, 
                                           inspayest, 
                                           writeoff 
                                    FROM   claim) AS c 
                                ON c.patnum = patient.patnum 
                  GROUP  BY patient.patnum) AS results 
              ON results.patnum = p.patnum 
       LEFT JOIN procedurelog AS plog 
              ON plog.patnum = p.patnum 
                 AND plog.procstatus = 2 
GROUP  BY p.patnum 
During C# execution, it fails at the last line of this:

Code: Select all

           try
            {
                using (var mySqlConnection = new MySqlConnection(connBuilder.ConnectionString))
                {
                    mySqlConnection.Open();

                    using (var cmd = new MySqlCommand(strQuery, mySqlConnection))
                    {
                        using (var reader = cmd.ExecuteReader())   <= FAILS HERE
                        {
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Re: Solved

Post by ajhalls » Tue Apr 17, 2018 8:40 am

I found a number of problems and solutions. Since I didn't hear anything from here, I posted on StackOverflow (https://stackoverflow.com/questions/498 ... in-c-sharp) and got some help. The issue was that it was taking about 60 seconds to run, and so it would time out. I had to change the timeout by setting `cmd.CommandTimeout = 0;` to allow it to finish.

The interesting part is that I had originally gotten parts of my query from:
"OpenDental\opendental17.4\OpenDentBusiness\Db Multi Table\Ledgers.cs"

I was able to simplify the query quite a bit (down to only 187 lines) by simply using the balances from the patient table so that it runs in about 3 seconds on the production Mysql 5.5 machine, but the next part is the most interesting to me. Researching the differences in 5.5 Vs 5.7, I came across this link: https://stackoverflow.com/questions/489 ... -mysql-5-7
In particular what grabbed my attention was this:

MySQL 5.5 - EXISTS - 53 seconds
MySQL 5.5 - IN - 48 seconds

MySQL 5.7 - EXISTS - 46 seconds
MySQL 5.7 - IN - 4 seconds

Seems like it would be simpler to upgrade the distributed version of Mysql which could probably be done with a single developer, rather than to go back and rewrite all the queries to be more optimized for 5.5 which would take much more manpower. Since 5.7 has been out since 2015-10-21, what are we waiting for? I saw on this thread from a year and a half ago that you were working on 5.6 - are we going to step into 5.6 first, or just skip it since 5.7 has been out 2.5 years?

For what it is worth, here is the updated query, I know it can be more optimized still, but it was what I came up as a first draft - which runs about 20X faster.

Code: Select all

SELECT p.patnum            AS reference_id, 
       p.guarantor         AS guarantor_reference_id, 
       p.bal_0_30          AS balance_0_30, 
       p.bal_31_60         AS balance_31_60, 
       p.bal_61_90         AS balance_61_90, 
       p.balover90         AS balance_over_90, 
       p.baltotal          AS account_balance, 
       pending_writeoff, 
       claims_estimated_amount, 
       patient_portion_due AS amount_due_from_patient, 
       Sum(procfee)        AS lifetime_value 
FROM   patient AS p 
       LEFT JOIN(SELECT patient.patnum, 
                        patient.guarantor, 
                        patient.bal_0_30, 
                        patient.bal_31_60, 
                        patient.bal_61_90, 
                        patient.balover90, 
                        guarAging.baltotal, 
                        guarAging.inswoest 
                                            AS 
                                                     pending_writeoff, 
                        guarAging.inspayest 
                                            AS 
                                                     claims_estimated_amount, 
                        guarAging.baltotal - guarAging.inspayest - 
                        guarAging.inswoest 
                                            AS 
                        patient_portion_due 
                 FROM   (
                         SELECT tSums.patnum, 
                                
                                Round(tSums.baltotal, 2)   BalTotal, 
                                Round(tSums.inswoest, 2)   InsWoEst, 
                                Round(tSums.inspayest, 2)  InsPayEst, 
                                Round(tSums.payplandue, 2) PayPlanDue 
                         FROM   (SELECT p.guarantor              PatNum, 
                                        
                                        Sum(CASE 
                                              WHEN trans.tranamount != 0 THEN 
                                              trans.tranamount 
                                              ELSE 0 
                                            END)                 BalTotal, 
                                        Sum(trans.inswoest)      InsWoEst, 
                                        Sum(trans.inspayest)     InsPayEst, 
                                        Sum(trans.payplanamount) PayPlanDue 
                                 FROM   (SELECT 'Proc' 
                                                TranType, 
                                                pl.patnum, 
                                                pl.procdate 
                                                TranDate, 
                                                pl.procfee * ( 
                                                pl.unitqty + pl.baseunits ) 
                                                TranAmount, 
                                                0 
                                                PayPlanAmount 
                                                , 
                                                0 
                                                InsWoEst, 
                                                0 
                                                InsPayEst 
                                         FROM   procedurelog pl 
                                         WHERE  pl.procstatus = 2 
                                                AND pl.procfee != 0 
                                         UNION ALL 
                                         SELECT 'Claimproc' TranType, 
                                                cp.patnum, 
                                                cp.datecp   TranDate, 
                                                ( CASE 
                                                    WHEN cp. status != 0 THEN 
                                                    ( CASE 
                                                    WHEN cp.payplannum = 0 THEN 
                                                    - 
                                                    cp.inspayamt 
                                                    ELSE 0 
                                                      END ) - 
                                                    cp.writeoff 
                                                    ELSE 0 
                                                  END )     TranAmount, 
                                                ( CASE 
                                                    WHEN cp.payplannum != 0 
                                                         AND cp. status IN ( 1, 4 
                                                             , 5 ) 
                                                  THEN - 
                                                    cp.inspayamt 
                                                    ELSE 0 
                                                  END )     PayPlanAmount, 
                                                ( CASE 
                                                    WHEN cp. status = 0 THEN 
                                                    cp.writeoff 
                                                    ELSE 0 
                                                  END )     InsWoEst, 
                                                ( CASE 
                                                    WHEN cp. status = 0 THEN 
                                                    cp.inspayest 
                                                    ELSE 0 
                                                  END )     InsPayEst 
                                         FROM   claimproc cp 
                                         WHERE  cp. status IN ( 0, 1, 4, 5, 7 ) 
                                         HAVING tranamount != 0 
                                                 OR payplanamount != 0 
                                                 OR inswoest != 0 
                                                 OR inspayest != 0 
                                         UNION ALL 
                                         SELECT 'Adj'     TranType, 
                                                a.patnum, 
                                                a.adjdate TranDate, 
                                                a.adjamt  TranAmount, 
                                                0         PayPlanAmount, 
                                                0         InsWoEst, 
                                                0         InsPayEst 
                                         FROM   adjustment a 
                                         WHERE  a.adjamt != 0 
                                         UNION ALL 
                                         SELECT 'PatPay'   TranType, 
                                                ps.patnum, 
                                                ps.datepay TranDate, 
                                                ( CASE 
                                                    WHEN ps.payplannum = 0 THEN 
                                                    - 
                                                    ps.splitamt 
                                                    ELSE 0 
                                                  END )    TranAmount, 
                                                ( CASE 
                                                    WHEN ps.payplannum != 0 THEN 
                                                    - 
                                                    ps.splitamt 
                                                    ELSE 0 
                                                  END )    PayPlanAmount, 
                                                0          InsWoEst, 
                                                0          InsPayEst 
                                         FROM   paysplit ps 
                                         WHERE  ps.splitamt != 0 
                                         UNION ALL 
                                         SELECT 'PPCharge' 
                                                TranType, 
                                                ppc.guarantor 
                                                PatNum, 
                                                ppc.chargedate 
                                                TranDate, 
                                                0 
                                                TranAmount, 
                                                COALESCE (ppc.principal + 
                                                          ppc.interest, 
                                                0) 
                                                PayPlanAmount 
                                                , 
                                                0 
                                                InsWoEst, 
                                                0 
                                                InsPayEst 
                                         FROM   payplancharge ppc 
                                         WHERE  ppc.chargedate <= '2018-04-26' 
                                                AND ppc.chargetype = 0 
                                                AND COALESCE (ppc.principal + 
                                                              ppc.interest, 0) 
                                                    != 0 
                                         UNION ALL 
                                         SELECT 'PPComplete'     TranType, 
                                                pp.patnum, 
                                                pp.payplandate   TranDate, 
                                                -pp.completedamt TranAmount, 
                                                0                PayPlanAmount, 
                                                0                InsWoEst, 
                                                0                InsPayEst 
                                         FROM   payplan pp 
                                         WHERE  pp.completedamt != 0) trans 
                                        RIGHT JOIN patient p 
                                                ON p.patnum = trans.patnum                                           
                                 GROUP  BY p.guarantor 
                                 ORDER  BY NULL) tSums) guarAging 
                        INNER JOIN patient 
                                ON patient.patnum = guarAging.patnum AND Date_format(patient.datetstamp, '%Y-%m-%d %T') >= 
       Date_format('2018-03-16 17:12:19', '%Y-%m-%d %T')  
                        LEFT JOIN (SELECT patnum, 
                                          claimstatus, 
                                          claimfee, 
                                          inspayest, 
                                          writeoff 
                                   FROM   claim) AS c 
                               ON c.patnum = patient.patnum 
                 GROUP  BY patient.patnum) AS results 
              ON results.patnum = p.patnum 
       LEFT JOIN procedurelog AS plog 
              ON plog.patnum = p.patnum 
                 AND plog.procstatus = 2 
GROUP  BY p.patnum 
Last edited by ajhalls on Tue Apr 17, 2018 9:07 am, edited 1 time in total.
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Fatal error encountered during command execution in C#

Post by cmcgehee » Tue Apr 17, 2018 9:06 am

It is on our roadmap to upgrade our offices to MySQL 5.6 or 5.7 although it could require a significant amount of work because we will have to upgrade the databases of our existing customers to work with the newer version of MySQL.
Chris McGehee
Open Dental Software
http://www.opendental.com

User avatar
drtech
Posts: 1647
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: Fatal error encountered during command execution in C#

Post by drtech » Tue Apr 24, 2018 10:34 am

MySQL 8.0 is out now, Advertised up to 2x faster than 5.7. I think it is time for OD to upgrade.
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Fatal error encountered during command execution in C#

Post by cmcgehee » Tue Apr 24, 2018 3:25 pm

You are welcome to upgrade your own office to MySQL 8.0 although we will not support that version immediately. If you do, I would be interested in hearing your experience, especially if you hit any snags.
Chris McGehee
Open Dental Software
http://www.opendental.com

Post Reply