Version 6.6 breaks daisy chain replication

[mimai]

We recently updated from 6.4 to 6.6. Since then, we have experienced two breaks in our daisy chain servers. It appears to be related to aging activity and specifically creating or deleting temporary tables. The MySQL error log from the down stream server (the server not running the aging activities) are as below:

Error 'Table 'tempaging' already exists' on query. Default database: 'opendental'. Query: 'CREATE TEMPORARY TABLE tempaging (PatNum INT DEFAULT 0,Guarantor INT DEFAULT 0,Charges_0_30 DOUBLE DEFAULT 0,Charges_31_60 DOUBLE DEFAULT 0,Charges_61_90 DOUBLE DEFAULT 0,ChargesOver90 DOUBLE DEFAULT 0,TotalCredits DOUBLE DEFAULT 0,InsEst DOUBLE DEFAULT 0,PayPlanDue DOUBLE DEFAULT 0,BalTotal DOUBLE DEFAULT 0)'

Error 'Table 'tempodagingtrans' already exists' on query. Default database: 'opendental'. Query: 'CREATE TEMPORARY TABLE tempodagingtrans (PatNum INT DEFAULT 0,TranDate DATE DEFAULT '0001-01-01',TranAmount DOUBLE DEFAULT 0)'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT ppc.PatNum PatNum,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges FROM payplancharge ppc WHERE ppc.PatNum<>0 AND ppc.PatNum IN (38219,38323,52577,4911763,3337217) GROUP BY ppc.PatNum) c,(SELECT ps.PatNum PatNum,IFNULL(SUM(ps.SplitAmt),0) PayPlanPayments FROM paysplit ps WHERE ps.PayPlanNum<>0 AND ps.PatNum IN (38219,38323,52577,4911763,3337217) GROUP BY ps.PatNum UNION SELECT DISTINCT ppc2.PatNum,0 FROM payplancharge ppc2 LEFT JOIN paysplit ps2 ON ps2.PatNum=ppc2.patnum WHERE ISNULL(ps2.PatNum) ) p SET a.PayPlanDue=IFNULL(c.PayPlanCharges-p.PayPlanPayments,0) WHERE c.PatNum=a.PatNum AND p.PatNum=a.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM tempodagingtrans t WHERE t.TranAmount<>0 GROUP BY t.PatNum) totals SET a.BalTotal=totals.BalTotal WHERE a.PatNum=totals.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE patient p,(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60,SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90,SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue,SUM(BalTotal) BalTotal FROM tempaging a GROUP BY a.Guarantor) f SET p.BalOver90=CASE WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 ELSE f.ChargesOver90-f.TotalCredits END,p.Bal_61_90=CASE WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END,p.Bal_31_60=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END,p.Bal_0_30=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Ch

Error 'Table 'opendental.tempodagingtrans' doesn't exist' on query. Default database: 'opendental'. Query: 'INSERT INTO tempodagingtrans (PatNum,TranDate,TranAmount) SELECT pl.PatNum PatNum,pl.ProcDate TranDate,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount FROM procedurelog pl WHERE pl.ProcStatus=2 AND pl.PatNum IN (38219,38323,52577,4911763,3337217)'

Error 'Table 'opendental.tempodagingtrans' doesn't exist' on query. Default database: 'opendental'. Query: 'INSERT INTO tempodagingtrans (PatNum,TranDate,TranAmount) SELECT ps.PatNum PatNum,ps.ProcDate TranDate,-ps.SplitAmt TranAmount FROM paysplit ps WHERE ps.PayPlanNum=0 AND ps.PatNum IN (38219,38323,52577,4911763,3337217)'

Error 'Table 'opendental.tempodagingtrans' doesn't exist' on query. Default database: 'opendental'. Query: 'INSERT INTO tempodagingtrans (PatNum,TranDate,TranAmount) SELECT a.PatNum PatNum,a.AdjDate TranDate,a.AdjAmt TranAmount FROM adjustment a WHERE a.AdjAmt<>0 AND a.PatNum IN (38219,38323,52577,4911763,3337217)'

Error 'Table 'opendental.tempodagingtrans' doesn't exist' on query. Default database: 'opendental'. Query: 'INSERT INTO tempodagingtrans (PatNum,TranDate,TranAmount) SELECT cp.PatNum PatNum,cp.DateCp TranDate,-cp.InsPayAmt-cp.Writeoff TranAmount FROM claimproc cp WHERE cp.status IN (1,4,5,7) AND cp.PatNum IN (38219,38323,52577,4911763,3337217)'

Error 'Table 'opendental.tempodagingtrans' doesn't exist' on query. Default database: 'opendental'. Query: 'INSERT INTO tempodagingtrans (PatNum,TranDate,TranAmount) SELECT pp.PatNum PatNum,pp.PayPlanDate TranDate,-pp.CompletedAmt TranAmount FROM payplan pp WHERE pp.CompletedAmt<>0 AND pp.PatNum IN (38219,38323,52577,4911763,3337217)'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE patient p,(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60,SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90,SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue,SUM(BalTotal) BalTotal FROM tempaging a GROUP BY a.Guarantor) f SET p.BalOver90=CASE WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 ELSE f.ChargesOver90-f.TotalCredits END,p.Bal_61_90=CASE WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END,p.Bal_31_60=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END,p.Bal_0_30=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Ch

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT cp.PatNum,SUM(cp.InsPayEst+cp.Writeoff) InsEst FROM claimproc cp WHERE cp.PatNum<>0 AND cp.Status=0 AND cp.PatNum IN (40931,44879,44880,44956,53568,53637,58596,58597) GROUP BY cp.PatNum) t SET a.InsEst=t.InsEst WHERE a.PatNum=t.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT ppc.PatNum PatNum,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges FROM payplancharge ppc WHERE ppc.PatNum<>0 AND ppc.PatNum IN (40931,44879,44880,44956,53568,53637,58596,58597) GROUP BY ppc.PatNum) c,(SELECT ps.PatNum PatNum,IFNULL(SUM(ps.SplitAmt),0) PayPlanPayments FROM paysplit ps WHERE ps.PayPlanNum<>0 AND ps.PatNum IN (40931,44879,44880,44956,53568,53637,58596,58597) GROUP BY ps.PatNum UNION SELECT DISTINCT ppc2.PatNum,0 FROM payplancharge ppc2 LEFT JOIN paysplit ps2 ON ps2.PatNum=ppc2.patnum WHERE ISNULL(ps2.PatNum) ) p SET a.PayPlanDue=IFNULL(c.PayPlanCharges-p.PayPlanPayments,0) WHERE c.PatNum=a.PatNum AND p.PatNum=a.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM tempodagingtrans t WHERE t.TranAmount<>0 GROUP BY t.PatNum) totals SET a.BalTotal=totals.BalTotal WHERE a.PatNum=totals.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE patient p,(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60,SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90,SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue,SUM(BalTotal) BalTotal FROM tempaging a GROUP BY a.Guarantor) f SET p.BalOver90=CASE WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 ELSE f.ChargesOver90-f.TotalCredits END,p.Bal_61_90=CASE WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END,p.Bal_31_60=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END,p.Bal_0_30=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Ch

Error 'Table 'tempaging' already exists' on query. Default database: 'opendental'. Query: 'CREATE TEMPORARY TABLE tempaging (PatNum INT DEFAULT 0,Guarantor INT DEFAULT 0,Charges_0_30 DOUBLE DEFAULT 0,Charges_31_60 DOUBLE DEFAULT 0,Charges_61_90 DOUBLE DEFAULT 0,ChargesOver90 DOUBLE DEFAULT 0,TotalCredits DOUBLE DEFAULT 0,InsEst DOUBLE DEFAULT 0,PayPlanDue DOUBLE DEFAULT 0,BalTotal DOUBLE DEFAULT 0)'

Error 'Table 'tempodagingtrans' already exists' on query. Default database: 'opendental'. Query: 'CREATE TEMPORARY TABLE tempodagingtrans (PatNum INT DEFAULT 0,TranDate DATE DEFAULT '0001-01-01',TranAmount DOUBLE DEFAULT 0)'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT ppc.PatNum PatNum,IFNULL(SUM(ppc.Principal+ppc.Interest),0) PayPlanCharges FROM payplancharge ppc WHERE ppc.PatNum<>0 AND ppc.PatNum IN (33056,33134,33960,55173,12924687) GROUP BY ppc.PatNum) c,(SELECT ps.PatNum PatNum,IFNULL(SUM(ps.SplitAmt),0) PayPlanPayments FROM paysplit ps WHERE ps.PayPlanNum<>0 AND ps.PatNum IN (33056,33134,33960,55173,12924687) GROUP BY ps.PatNum UNION SELECT DISTINCT ppc2.PatNum,0 FROM payplancharge ppc2 LEFT JOIN paysplit ps2 ON ps2.PatNum=ppc2.patnum WHERE ISNULL(ps2.PatNum) ) p SET a.PayPlanDue=IFNULL(c.PayPlanCharges-p.PayPlanPayments,0) WHERE c.PatNum=a.PatNum AND p.PatNum=a.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE tempaging a,(SELECT t.PatNum,SUM(t.TranAmount) BalTotal FROM tempodagingtrans t WHERE t.TranAmount<>0 GROUP BY t.PatNum) totals SET a.BalTotal=totals.BalTotal WHERE a.PatNum=totals.PatNum'

Error 'Table 'opendental.tempaging' doesn't exist' on query. Default database: 'opendental'. Query: 'UPDATE patient p,(SELECT a.Guarantor,SUM(a.Charges_0_30) Charges_0_30,SUM(a.Charges_31_60) Charges_31_60,SUM(a.Charges_61_90) Charges_61_90,SUM(a.ChargesOver90) ChargesOver90,SUM(TotalCredits) TotalCredits,SUM(InsEst) InsEst,SUM(PayPlanDue) PayPlanDue,SUM(BalTotal) BalTotal FROM tempaging a GROUP BY a.Guarantor) f SET p.BalOver90=CASE WHEN f.TotalCredits>=f.ChargesOver90 THEN 0 ELSE f.ChargesOver90-f.TotalCredits END,p.Bal_61_90=CASE WHEN f.TotalCredits<=f.ChargesOver90 THEN f.Charges_61_90 WHEN f.ChargesOver90+f.Charges_61_90<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90-f.TotalCredits END,p.Bal_31_60=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90 THEN f.Charges_31_60 WHEN f.ChargesOver90+f.Charges_61_90+f.Charges_31_60<=f.TotalCredits THEN 0 ELSE f.ChargesOver90+f.Charges_61_90+f.Charges_31_60-f.TotalCredits END,p.Bal_0_30=CASE WHEN f.TotalCredits<f.ChargesOver90+f.Charges_61_90+f.Ch

Please help

[jordansparks]

Looks like we fixed it 6 days ago in version 6.7.17. I will make it a very high priority to review the fix, compare it with your errors, and backport it to 6.6. Hopefully within the hour.

Good news. Version 6.8 we have even better replication support.

[jordansparks]

Done

[mimai]

Thank you Dr. Sparks.