Data lost with TMyQuery

junk_bart_andie · Post by **junk_bart_andie** » Sat 08 Oct 2011 02:24

Dear All,

Do anyone can help me, pleeasee...
I made application with delphi, mysql, n MyDAC ( TMyQuery ).

the problem is when after the completion of data entry and exit from the program, the data already entered was suddenly lost. This incident occurred one than 15 times when entering data and a random occurrence.

In the table data, I use auto increment for the id of each data.
Suppose that the last data id is 253. After the input 5 data, should last id is 258. However, when data is lost and the input data back, his id immediately jumped to 259. id data 254 to 258 missing.

Has anyone ever experienced this?

It's a random occurrence and i never update the application program.

Pleassee.. pleasee..

AndreyZ · Post by **AndreyZ** » Tue 11 Oct 2011 08:29

Data loss can happen if your application doesn't commit transactions. Please check that all started transactions in your application are committed later. Note that even if transaction was not committed, the counter of an autoincrement field was increased. For example, if you created a new table in the following way:

Code: Select all

CREATE TABLE TEST(id INT(11) NOT NULL AUTO_INCREMENT, txt VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id))
ENGINE = INNODB
AUTO_INCREMENT = 1;

, and executed the following code:

Code: Select all

MyConnection.Open;
MyConnection.StartTransaction;
for i := 1 to 5 do
  MyConnection.ExecSQL('insert into test(txt) values(''test'')', []);
MyConnection.Rollback;
MyConnection.StartTransaction;
for i := 1 to 5 do
  MyConnection.ExecSQL('insert into test(txt) values(''test'')', []);
MyConnection.Commit;

, you will have five fields in the TEST table with id values from 6 to 10. Such MySQL behaviour is correct.

junk_bart_andie · Post by **junk_bart_andie** » Wed 19 Oct 2011 09:35

Thank AndreyZ for ur reply..

In my application, i use TMyConnection for mysql connection. I read on TMyConnection behavior that they have autocommit on their default.

It means that they should have in-commit itself to any transaction ( insert, update, delete ) that occurs. Am i correct ??
Or even they have autocommit on their default, should we still put commit transaction on each transaction ( insert, update, delete ) ?

In my current application, i didn't use start n commit transaction. I only use TMyConnection and probability of data stored and lost was 95%: 5%. I want 100% of the data stored

Sorry for my bad english...
Any suggestion will be helpful

Thank you..[/quote]

Dimon · Post by **Dimon** » Thu 20 Oct 2011 15:11

junk_bart_andie wrote:In my application, i use TMyConnection for mysql connection. I read on TMyConnection behavior that they have autocommit on their default.

It means that they should have in-commit itself to any transaction ( insert, update, delete ) that occurs. Am i correct ??
Or even they have autocommit on their default, should we still put commit transaction on each transaction ( insert, update, delete ) ?

TMyConnection and TMyQuery components don't have the AutoCommit property. If you change data (e.g., insert, update, delete), it is always saved in a database automaticly on calling the Post method, unless you use transactions.
But if you execute the StartTransaction method, then to save data to a database, you should execute the Commit method.

Dimon · Post by **Dimon** » Thu 20 Oct 2011 15:12

junk_bart_andie wrote:In my current application, i didn't use start n commit transaction. I only use TMyConnection and probability of data stored and lost was 95%: 5%. I want 100% of the data stored

I can not reproduce the problem. Try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

junk_bart_andie · Post by **junk_bart_andie** » Wed 30 Nov 2011 07:33

Still no clue how can this happend...

Here's another case with same problem.
Still with my application, there were run on two PC. When first PC insert or update data, data on grid are changed.
But, when I looked at second PC, there are no data updated or inserted.
Then i refresh data on second PC, and still no data updated or inserted.
I refresh many times on second PC, and still no data updated or inserted.

After that, I refresh data on first PC, then suddenly the data were back just like no data were updated or inserted.
It's weird...

Then I re inserted and re updated data on first PC. On second PC, I refresh data on second PC and data were updated just like data on first PC.

Does the cause of this problem maybe came from the network or the application or on mysql or on MyDAC component ??

For information, i made 4 application using same MyDAC and same method for inserting and updating data. There are no problem with the other.

I find difficulty for composing a small sample..
So sorry...

AndreyZ · Post by **AndreyZ** » Thu 01 Dec 2011 11:10

It really looks like you are using transactions and do not commit them. Please make sure you commit transactions in your application. We cannot help you without a sample that demonstrates the problem you encountered. If you are able to create such sample and send it to us, we will gladly investigate it. We are looking forward to receiving such sample from you.

junk_bart_andie · Post by **junk_bart_andie** » Fri 13 Jan 2012 06:49

Still no clue how can this happend...
Here i try to compose a small sample to demonstrate how data lost happend..

Data lost sometime happend. It's a random occurrence.

I have another application use a same code. And never happend about losing data.

I email it to dmitryg*devart*com.
Please let me know if there're something wrong with my code that can produce data lost...

AndreyZ · Post by **AndreyZ** » Fri 13 Jan 2012 10:44

You are using filtering in your application, and sometimes your application cannot show inserted or updated data because of this. The time on different computers can be different. When you update data on the first computer, you set datetime fields using the time of this computer. If the time on the second computer is different, your query may not return these records. Please check if the problem persists if you don't use filtering.

junk_bart_andie · Post by **junk_bart_andie** » Sat 14 Jan 2012 05:49

Thank you for a short reply....

Yes, in my application i use filter. But, i use filters base on date. Not with the time. And i'm complately sure that between other computer they have same date.

What about the lockmode or unidirectional option on TMyQuery ??
I use it as default.

Or maybe on TMyQuery ?? Are they OK when it used for insert/update/delete and for showing data in the same time ?

On my other applications, I do not find such lost data. In fact I used the same method..

AndreyZ · Post by **AndreyZ** » Tue 17 Jan 2012 11:17

We couldn't reproduce the problem using your sample. Without a sample that demonstrates the problem, we cannot give you the correct solution. There are several hints that may help to determine the reason of data lost:
- if you are using CachedUpdates=True, check that you perform ApplyUpdates;
- if you are using transactions, check that you commit all of them;
- if you are using the FetchAll=False mode, note that committing transactions in the main session will not save changes. For more information, please read the FetchAll property description in the MyDAC documentation;
- if you set the UniDirectional=True mode, the FetchAll property is set to False. It can cause problems described above. For more information, please read the UniDirectional property description in the MyDAC documentation;
- LockMode cannot cause data lost.