Devart Forums

I use code like the following to insert a record into a MySQL InnoDB table:

MyQuery->Close();
MyQuery->SQL->Clear();
MyQuery->SQL->Add("SELECT * FROM mytablename LIMIT 1");
MyQuery->Open();
MyQuery->Insert();
..
MyQuery->FieldByName("VehRef")->AsInteger = VehicleUniqNr;
MyQuery->FieldByName("Status")->AsInteger = Status;
MyQuery->FieldByName("Event")->AsInteger = Event;
..
MyQuery->Post();
MyQuery->Close();

I write the same information to a log file but sometimes the MySQL table and the log file doesn't contain the same values - and the values in the log file seems right.
It seems like a field in the MySQL table is updated with the previous information stored into the table.

Any idea what could be wrong ?
Could it be a problem in MySQL / communication to MySQL?
Could it be the setup of the TMyQuery that should be changed?

Any bugs solved in MyDAC concerning this issue so that it would be an idea to upgrade to latest MyDAC ?

I use MyDAC v. 4.40.0.24 and MySQL v. 5.0.24A and Borland C++ Builder 5

I can not reproduce the problem.
Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script to create and fill table.

I will try make a small test application and do a test here - and let you know if I can make it happen for the test application.

I just hoped that somebody had seen a problem like that can and knew that it had been fixed in a later version of MyDAC / MySQL

I have come across a similar problem where an update being made from my program is not being passed through to the server, more worryingly the program reports no errors. I have traced the query using the monitoring component and get the log file below, however when I check the server the record has not changed (*nothing* received in logfile!).

Refreshing the query dataset after running the update shows the change has taken place but re-running the program shows the change was not made! There is no caching I know of and it is being posted with

UpdateQuery.post;
log(updatequery.FinalSQL);
if updatequery.rowsaffected1 then raise exception.create('update did not work, retry may work');
Modalresult:=mrok;

which does not report an error, the only thing I do see is an increase in the ROLLBACK command types on the server after each failure but I cannot see where I have any transactions enabled

This problem is worrying as I will soon be going live on my new application and I need to know what is happening!. Is there a fix or a way of confirming the update has been applied and generating an error. Any thoughts or anyone else solved this ?

Using V5.55.0.39 for delphi 2007 and DBMonitor version 2.14 and MySQL V5.051b


Log file

UPDATE SOP
SET
SOPID = ?, ProcedureCategory = ?, UOMRef = ?, ProcessStageRef = ?, JobTypeRef = ?, SOPOrder = ?, WMSOrder = ?, InstructionGroup = ?, ProcedureTitle = ?, ProcedureDetails = ?, WMSAltText = ?, Revision = ?, RevisionHistory = ?, ResultDisplayFormat = ?, DefaultChargeRate = ?, ChargeUnits = ?, ContractNSC = ?, IsDefaultWMI = ?, ShowOnWMS = ?, ClientMustSpecify = ?, ProductRef = ?, ControlValue1 = ?, ControlValue2 = ?, AssetClassGroup = ?, ProductAddnRate = ?, AddnRateUnits = ?, AddnCalcBase = ?, ReportingGroup = ?, VarietyType = ?, TypManHours = ?, LastModified = ?, LastModifiedBy = ?
WHERE
SOPID = ?

:SOPID(Integer,IN)=58
:ProcedureCategory(Word,IN)=3
:UOMRef(Word,IN)=
:ProcessStageRef(Integer,IN)=2
:JobTypeRef(Integer,IN)=1
:SOPOrder(Float,IN)=7
:WMSOrder(Float,IN)=
:InstructionGroup(String[9],IN)='WH-PMS-CR'
:ProcedureTitle(String[53],IN)='[White Wine] - Free Run PMS addition to Crusher test'
:ProcedureDetails(Memo,IN)=
:WMSAltText(Memo,IN)=
:Revision(Word,IN)=18
:RevisionHistory(Memo,IN)=
:ResultDisplayFormat(SmallInt,IN)=6
:DefaultChargeRate(Float,IN)=0
:ChargeUnits(Integer,IN)=1
:ContractNSC(Boolean,IN)=False
:IsDefaultWMI(Boolean,IN)=True
:ShowOnWMS(Boolean,IN)=True
:ClientMustSpecify(Boolean,IN)=False
:ProductRef(Integer,IN)=410
:ControlValue1(Float,IN)=2
:ControlValue2(Float,IN)=
:AssetClassGroup(Word,IN)=0
:ProductAddnRate(Float,IN)=51
:AddnRateUnits(LargeInt,IN)=2
:AddnCalcBase(SmallInt,IN)=2
:ReportingGroup(String[0],IN)=
:VarietyType(FixedChar[5],IN)='White'
:TypManHours(Word,IN)=
:LastModified(DateTime,IN)=1/10/2008 1:33:13 p.m.
:LastModifiedBy(String[3],IN)='Boz'
:Old_SOPID(Integer,IN)=58

OK Something really weird seems to be going on (or I'm working too late!)

If I change the SQLmonitor flags to show the ACTUAL SQL update statement and copy and paste this into heidisql and run it, it updates correctly AND appears in the mysql bin log so the SQL generated by the DAC is OK

Either the prepared query is not being sent to the mysql server or it is being rolled back immediately

It still does not explain why the main program shows the update has been made no matter how many refreshes I make.

The fault is hard and reproducable at the moment but I know that once I start changing everything in desperation I will eventually start working.

Im happy to accept it is caused by my tinkering with some setting I know nothing about but I would also really like to be able to know what is causing this and how to trace it in future.

Please try to compose a sample to demonstrate the problem and send it to dmitryg*devart*com.

I'm not really sure I can replicate it in a test sample but I have found out more about the problem

I set up the mysql query analyser and confirmed the update is getting through to the mysql server it is issuing the comand

SELECT * FROM SOP WHERE SOPID=427 FOR UPDATE

(MyQuery using pessimistic locking)

When I POST the record the UPDATE command is issued and received by the mySQL server HOWEVER IT IS NOT COMMITTED

Subsequent refreshes and commands issued to the server are now failing but my application sees the changes and thinks everything is OK

When I exit the application it issues a ROLLBACK !!!

this is the problem.

If I Change the locking to none this does not occur.

Several other of my modules use locking and do not experience this problem so it must be a combination of settings and possibly some 'bad' programming practice I tend to do such as issuing subqueries in the BeforePost event to do checks.

Regardless of however bad my programming possibly is, when I issue the post method and I am not using transactions I expect it to commit the query and issue an exception if it cannot.

The form causing the problem is a master/detail one with 5 lookup queries, probably my most complex form in the application, I will try and discover more information and the logs and post them too you, if there is something I can do to confirm the COMIT has taken place I would be grateful in the meantime.

You are right. When you don't use transactions, then on the Post method changes must be committed.
Please make sure that you have no started transactions.
Also try to download the latest MyDAC build (5.70.0.44) and check if this problem still exists.

Thanks for the quick response, Once my current project is complete and the pressure is off I will upgrade to the latest MyDAC and do some tests for you.

As a workaround I added the following to manually commit the data on closing the form
This works

the ROLLBACK still occurs when the application closes but it now has no affect.