Command out of sync

you local-record-cursor does not match the server-record-cursor

look at a resync-method

(sorry i have no dbexpress components,
therefore i don't know if such method there exists)

Hello
 
  Could you please give us more info, which Delphi version you are using?, and which components

  I'm not familiar with MySql, but I heard there was some bugs in Borland driver for DBX with Delphi 6

also the error you got is from MySql it self, not from Delphi, look at

  http://www.mysql.com/doc/en/Commands_out_of_sync.html

Best regards
Mohammed Nasman

kretzschmar, I think we should use Refresh instead of Resync method. Yes, I have already try all of my knowledge including resync/refresh the data even before it's state becoming insert/edit. The method is not help.

Hi Mohammed Nasman, I used Delphi 7. But Delphi 6 should have DBExpress components. Here the components I used:

SQLConnection <- SQLDataSet <- DataSetProvider <- ClientDataSet <- DataSource

In Delphi 6 you could use SQLClientDataSet that behave as SQLDataSet, DataSetProvider and ClientDataSet. But this component was removed on Delphi 7.

I have already search clues and solutions on the net including your suggestion link. The link is the first find on my search.

SQLClientDataSet was so buggy so Borland dropped it, and replace it with TSimpleDataSet in Delphi 7, and this will work like DataSetProvider and ClientDataset

Could please be more specific when the problem show to you?

Mohammed Nasman,

When the app still simple, all code run well. I do edit, insert and delete some records smoothly. But when it become more complex, the modification of records can not be updated. Note that the complexity of app because I add more forms, not bother the first form that run well. But because of this, the simple first form will become error too, even I didn't change the code.

The problem is when I try to apply the update. Here is some piece of code:

TForm1.DataSetAfterPost (Dataset : TDataset);
begin
  if cdsDummy.ChangeCount > 0 then cdsDummy.ApplyUpdates (-1);
end;

I have tried to separate ApplyUpdates method on buttonClick event, but didn't help.

Regards, Dewo

I have the same problem with my MySQL server. To decide problem you have to close all connections to the server (fro instance simply restart them) and then use REPAIR procedure from admin console. The problem is that MySQL index files was damaged I think. Why does this happen? I don't no, but in my case it was caused by using the direct connection components for another version then MySQL server running. When got components for the same version of MySQL the problem was decided.

stepashka,

FYI, I use mysql version 3.23.49. Right now I trying version 4.0.3-beta. I have update all mysql's lib with the update file: mysql-lib-3.23.36-borland.zip.

Regarding your suggestion about the version that should same version, should I use mysql-lib with the same version with mysql server?

I think index is not the problem. I used phpMyAdmin to administer mysql server. I have saved all tables and data and save it to a sql script file, then I drop my database, create again, and execute the sql script file. But the problem still occur. This radical step I take because until now I could not find the problem & the solution. Yes, I already try shutdown and restart the mysql server. even I've upgrade the 4.0.3-beta version.

Are you sure your suggestion could solve our problem?

Regards, Dewo

for addition, event OnReconcileError can not trap this error. How come?

As I wrote I didn't know the reason of this problem. But if you use MySQL 3.23.49 for example, you ought to use  the mysql-lib of the same version. And it is important to launch REPAIR procedure for all tables of the database which is "out of sync". I assumed that the reason is connected with the indexes because after REPAIRing all became ok. There was no such the problem still I'm using the correct version of mysql-lib and (IMPORTANT!!!) components which was written for the same lib version.
My problem was in that I had used components for MySQL 3.23.28, then I installed 3.23.49 and corresponding lib, but my old components worked incorrect with this library.
Besides of "out of sync" I had some other problems. After installation of new components all problems will be decided, so may be the it helps you too. Try and tell us your results. Good luck.

Hi stepashka, thank you for your suggestions,

1. Right now I still searching to find the correct match mysql database and mysql-lib. I found a stable version mysql-max-3.23.52-win.zip, but I could not find the corresponding mysql-lib version. Any suggestion for the best match version? Maybe you could give me a link to mysql-lib-3.23.49.

2. Is it useful to REPAIR while I have drop the database and load the SQL script that would create all tables and indexes including the data? This radical move I took because I also thought that might something wrong happened in database, but it could not help me until now.

Q will open for any suggestion, primary to find what's going wrong with dbexpress-mysql and how the solution.

Hi stepashka,

I have tried to use the same version both mysql server and mysql-lib. I downgraded both to 3.23.36 (I search on the net and only find this pair). The problem still happened.

Regards, Dewo

Sorry for my delay with the answer, I have no enough time to my regret:(

Ofcourse you nedd not to do repairing if you dropped and then created database, coz I deem MeSQL has to recreate indexes during this. Frankly speaking I'm at a loss to  propose you something else... I have the only idea... What kind of components do you use? Is it direct connection components, or through the ADO or somethin else? I used direct connection components and my problem was decided when I downloaded components that had been written for the same version of mysql-lib as I used.
I have no this components at hand, but if you want I may send them to you so you to try them in your project.

perhaps you would like to try Zeos?
http://zeoslib.sourceforge.net/

Hi DragonSlayer,
I though Zeos is still in beta. I would test it, but maybe not use it on my app :)

FYI, many of developers stuck with this problem. Here one of them:
http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&safe=off&th=a19e221432633b8

stepashka,
I use delphi/kylix standard dbexpress (I though will kept my app with standard components). Here is the components I used :
SQLConnection <- SQLDataSet <- DataSetProvider <- ClientDataSet <- DataSource

Could you name the component you have please?

Thanks, Dewo

Frankly speaking I used ZLib from Zeos. May be you really should try it? And I think if your problem is still actual you may contact with the MySql developers. They have to know the reaseon of this problem at least I think.

Hello all, sorry for late, I did a lot for my app,

I think I have solved the problem, and I have hypothesis about what's wrong with DBExpress-MySQL.

This all about the limitation of SQLConnection component (or from the datasets?). It could not manage a lot of Datasets attach to it. I didn't test how much it could handle datasets, but when I reduce datasets, it work fine. As I mentioned first, my apps work fine at first, but when it going so complex, the error appears, even in my previous units that first work fine.

So, after a long search, I did trick with my app with reduced the concurrent connection of the Dataset to SQLConnection. I move the datasets which very spesific with form to the form (rather than collect them in datamodule). Then I create and destroy the all forms on need on runtime. Now my app work fine and run without error anymore. My assumption is datasets (in forms) will connect when it created and disconnect when it destroy regarding with their form.

But I don't know how many datasets SQLConnection still could handle. And this assumption still could be debated. So my points here would be nice to dedicated to whom that could prove my opinion or broke it. Of course with the good reason and prove.

It's very important because we should know the limitation of our app and why it could be happened and how to cope it with good solution (even if we should change with other 3rd party component). This Q will be opened for several days.

stepashka & DragonSlayer, I could not download the zeoslib, how come? Maybe zeos had moved the link?
Anyway, Let's benchmark SQLCOnnection (or dataset).

Thanks a lot to all who contribute comments & answers. Q will be opened and waiting for your next comments & suggestions.

Regards, Dewo

Try here
http://prdownloads.sourceforge.net/zeoslib/zeosdbo-5.3.0-prebeta6.zip?download

or here
http://sourceforge.net/projects/zeoslib/

DragonSlayer,
Thanks a lot, I can download it. Now I'm going to try it.
Any comment for my assumption?

Regards, Dewo.

well, if I wanted to put everything in a DataModule, then what I would do is to set the Dataset's Active property to True only when needed and then back to False when not needed.

On the other hand, I have created 3-tier applications where only the application server does all the connection to the database. I've done stress test onto the appserver up to 2500 simultaneous database connections (using Zeos) without any problems. I didn't test beyond that because my appservers are load-balanced and they usually handle less than 300 simultaneous connections (for now).

DragonSlayer,

In case putting everything in a datamodule, if you use dbx, so SQLConnection & DataSet (-DataProvider-ClientDataset) still maintain change-log even if ClientDataset's active property set to false. It cause potential "Command out of sync" error as my case. I don't know the number of connection of SQLConnection-Dataset could failed.

so you think zeoslib is powerfull enough to handle heavy-load connection compare to dbx?

I actually haven't tried dbx before, so I can't comment on that :)

It's a pity, but I have no time to benchmark SQLConnection now, but the problem is really interesting and I will watch closely on this discussion.

P.S. I thing you need not move your datasets from datamodule to forms. You simply may open and close some datasets when they are really needed, I think and it doesn't matter where do they situated.

DragonSlayer & stepashka, I just don't get it too why when I reduce concurrent connection to SQLConnection the problem was solved. I did runtime creation & destruction of forms (with Datasets). I didn't do anything on MySQL server.

As I mentioned, might be Dataset that attach to SQLConnection increase the overhead (still could debated). Even the dataset is inactive, but it still connect to SQLConnection. As we know, SQLConnection will maintain all dataset attach to it. And pair of DatasetProvice and ClientDataset still should maintain change-log file according with ClientDataset's jobs.

Since there are no any suggestions post here, I will delete this Q, or if I can not delete this Q, I would like to decrease the point and give to whom participate in this Q. But...

How to delete this Q?
If can not, how to decrease the point?

Hi all,

A request has been posted in CS to finalize this Q.
Please place your comments here so I can do it in a way all are satisfied.

dewo, when you want a pointsplit, please indicate the expert(s) and the number of points.

modulo

Community Support Moderator
Experts Exchange

I think I would like give bonus points for :
1. DragonSlayer : 10 points, grade : B
2. stepashka : 10 points, grade : B
who have contribute their answer & attention.

For all, there is a tip using DBExpress, set SQLConnection.Active to false before compile your project and open it explisitly by call SQLConnection.Open. This will always refresh connection made by SQLConnection when application begin to run. This will minimize the occurence of "Command out of sync" error.

Thanks, Dewo

Thanks...

Hi dewo,

I've anabled you to post a "Points for DragonSlayer" and a "Points for stepashka" Q in this topic area, but haven't seen them yet...

modulo

Community Support Moderator
Experts Exchange

Sorry on the delay time. I will post it.
Thanks,
Dewo