multiple users and indices

[Antonino Perricone]

Hello everybody,

I am pretty new with Harbour, Clipper and RDD(6 months). I am working on a program developed in the last 20 years, This program is used by multiple users with remote desktop, so all users use the same directory. Our problem is that sometimes the indices become invalid, so we need to rebuild them deleting the cdx file and calling INDEX ON ***. The program is made with an xHarbour of 2009.

Trying to replicate our problem I made this program:

#include <dbinfo.ch>
REQUEST DBFCDX


#ifdef __XHARBOUR__
   #xtranslate hb_threadStart( <x,...> ) => StartThread( <x> )
   #xtranslate hb_threadJoin( <x,...> ) => JoinThread( <x> )
#endif


#define DBNAME "test"
#define NADD 1000
#define NTHREAD 10


proc Add100()
   local i
   RddSetDefault( "DBFCDX" )
   SET DBFLOCKSCHEME TO DB_DBFLOCK_CLIP
   ? "use"
   USE DBNAME SHARED
   ? "begin"
   for i:=1 to NADD
      dbAppend()
      FIELD->RAND = hb_Random(100)
      FIELD->TESTO = hb_randStr(10)
   next
   ? "end"
return


proc main()
   local i, oldR, threads
   
   FERASE( DBNAME+".dbf" )
   FERASE( DBNAME+".cdx" )


   RddSetDefault( "DBFCDX" )
   
   dbCreate( DBNAME, { { "ITEM", "N+", 10, 2 },{ "RAND", "N", 10, 2 },{ "TESTO", "C", 10, 0 } })
   USE DBNAME SHARED
   ? "database done"
   
   INDEX ON FIELD->RAND TAG "ITEST"  
   DBGOTOP()
   ? "INDEX done"
   
   threads := {}
   for i:=1 to NTHREAD
      aadd(threads, hb_threadStart( @Add100() ))
   next
   for i:=1 to len(threads)
      hb_threadJoin( threads[i] )
   next
   ? "filled done"
   
   OrdSetFocus( "ITEST" )
   DBGOTOP()
   oldR := FIELD->RAND
   i := 0
   dbSkip()
   do while .not. eof()
      if FIEL->RAND < oldR
         i++
      endif
      oldR := FIELD->RAND
      dbSkip()
   enddo
   ? "There are " + str(i) + "error/s"
RETURN

My problem is I do not have the xHarbour used for build the main program, I am porting it to harbour 2015, in the meantime they asked me a solution for the indices problem, so I created this test, that never fails with harbour of 2015.

Now I think there are 2 possibilities:

1) the bug was solved in the last 6 years, so using the ported version the problem does not appears anymore, and we don't need to rebuild the indices.

2) My test program does not show the problem, the bug is still present and I must found another solution, that I don't have idea, the problem is that for rebuild the indices all users must disconnect, and it is a big problem.

In the second possibility we want change database system, but what we can use? SQLRDD is from xharbour, and we are abounding it, RDDSQL needs to rewrite all, ADORDD is still not usable ( I tried with my configuration and I was no able to build it), ADS is slow and expensive...

Please, can anyone help me? 

Antonino Perricone

[Klas Engwall]

Hi Antonino,

> I am pretty new with Harbour, Clipper and RDD(6 months). I am working on
> a program developed in the last 20 years, This program is used by
> multiple users with remote desktop, so all users use the same directory.
> Our problem is that sometimes the indices become invalid, so we need to
> rebuild them deleting the cdx file and calling INDEX ON ***. The program
> is made with an xHarbour of 2009.

Index corruption is usually caused by the Windows environment, not by
the application or the toolchain. So that is what you should to look
into first. The main things you have to configure are the registry
settings called EnableOpLocks, CachedOpenLimit and SMB2. There is a
function called win_osNetRegOk() in the hbwin contrib that can do it for
you (check out contrib\hbwin\win_os.prg for details), or download .reg
files for both server and workstation from
http://www.witzendcs.co.uk/nt_networking.html, apply them and reboot
(that is what I prefer to do). There are also numerous old discussions
about oplocks etc (search for that keyword) in this nesgroup that you
can study. And there are many web sites that explain the whys and the
hows (search the web for Oplocks).

I have applied those settings in every Windows environment where I have
been involved since Windows NT was first released in the 90's, and I
have *never* had any index corruption in Clipper, xHarbour or Harbour.

Regards,
Klas

[elch]

Welcome Antonino,

you should not change locking scheme choosen by Harbour for you, as long you have no concrete needs to do so.
Further the names have changed -- look into: doc/locks.txt for the new ones.
So better remove that line: SET DBFLOCKSCHEME.
IF you really need to change the locking scheme, then do that only ONCE at start of the main() procedure.
Threads' should IMO inherit that setting.

!! Important: shared access of DBF need same scheme used by all users. !!

---
Question:
do you need the SQL server for data access of 3rd party programs ?

regards
Rolf

[Antonino Perricone]

Thank you both for the replies, as I said I am new with harbour and these things,

We want move to a SQL server scheme from DBF because:

* There is always this problem of indices,I will try these setting hoping it will be fixed.

* Being a multi-user environment looks logic using a server-client database instead of all users use same files, crossing fingers that it works, and it don't because  the indices become broken.

* Some time we need to align tables from different installations. it looks easier and safer do it with SQL.

* there is no key on dbf, it drives me crazy :)

Have someone some advice about SQL or other database to use in harbour.

Regards,

Antonino

[elch]

Hi Antonino,

We want move to a SQL server scheme from DBF because:

* There is always this problem of indices,I will try these setting hoping it will be fixed.

* Being a multi-user environment looks logic using a server-client database instead of all users use same files, crossing fingers that it works, and it don't because  the indices become broken.

 

that index problem is very personal at you ! -- right done, none never problems !!, no 'finger crossing' needed.

Conclusion: something somewhere is wrong in your source/ environment.

I may suppose the SQLRDD formerly just hide a problem, as e.g. with different locking schemes for client side, or something else ...

For "server" Harbour would offer HbNetIO, and there is also external LetoDB.

But surely, both are DBF based. They communicate over TCP sockets, no remote desktop needed, just 'internet' connection.

BTW, i won't miss the DBF logic to lock a record, which SQL (without chin-ups) doesn't know -- then additional work around is needed.

( i never tried SQLRDD from xHarbour, i believe it does so .. )

As yourself said, then you have to rework the whole application.

There are some different approaches ready in Harbour, but AFIK not exactly as SQLRDD does.

And real SQL "rowset" versus DBF "record" are quite different strategies of database working.

best regards
Rolf

[Jayadev U]

Hi,

 

To my mind  you can have two solutions.

 

1.       If you want to move the sql way, you can use MySql+OTC Mediator+Harbour, advantage is very little change in code.  More details available at www.otc.pl.  (100 user license is free for MySql from OTC).

2.       Use NETIO, examples and demos available in contrib\hbnetio folder.  More reference available at https://hmgthinking.wordpress.com/ see post of 28/07/2015.

 

Having said that, I have never experienced any index corruption in DBF environment in both MultiUser and SingleUser versions of various softwares.  I would suggest rechecking the code.

 

Warm regards,

 

Jayadev

 

 

--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Antonino Perricone]

Thank you all very much for the answers! 

Mediator looks very interesting

Anyway, The code is not mine, I inherited it. but looks correct, what are the measures that a programmer must take to be sure of a correct code? 

for example, the code I posted here, to add a row in the table, just call the dbappend and set the fields, where is the part to check? I must call flock and dbunlock because the database is shared? and rlock/dbrlock and runlock for change a record?

There is a way to check if the dbf and the cdx file are aligned? in this way, if they are not, I will recreate the index for this file (now the program recreate all indices every day).

Regards,

[Lorenzo Fiorini]

On Tue, Aug 11, 2015 at 10:09 AM, Antonino Perricone <iorp...@gmail.com> wrote:

> There is a way to check if the dbf and the cdx file are aligned? in this
> way, if they are not, I will recreate the index for this file (now the
> program recreate all indices every day).

1 - check that ALL the indexes are opened EVERY time the dbf is opened
2 - check the network settings ( optimistic locking disabled and so on )
3 - check the network hardware ( no faulty lan cards )
4 - check lan adapter settings ( green energy, autospeed )

if the lan is ok and you open all the indexes correctly everything
should work without reindex.

best regards,
Lorenzo

[Jayadev Urath]

Hi,

Apart from the advices already given, please use dbCommit() after any add/delete/modification.

I strongly suggest please invest some time studying the code and correcting it for pitfalls before migrating to SQL or other such platforms.  In my so many years of experience with Clipper/Harbour/xHarbour, I have never had any index corruption.

Details of DBCommit:

 Syntax

     DBCOMMIT() --> NIL

 Returns

     DBCOMMIT() always returns NIL.

 Description

     DBCOMMIT() causes all updates to the current work area to be written to
     disk.  All updated database and index buffers are written to DOS and a
     DOS COMMIT request is issued for the database (.dbf) file and any index
     files associated with the work area.

     DBCOMMIT() performs the same function as the standard COMMIT command
     except that it operates only on the current work area.  For more
     information, refer to the COMMIT command.

 Notes

     ¦  Network environment: DBCOMMIT() makes database updates visible
        to other processes.  To insure data integrity, issue DBCOMMIT()
        before an UNLOCK operation.  For more information, refer to the
        "Network Programming" chapter in the Programming and Utilities Guide.

     ¦  DBCOMMIT() uses DOS interrupt 21h function 68h to perform the
        solid-disk write.  It is up to the network operating system to
        properly implement this request.  Check with the network vendor to
        see if this is supported.

 Examples

     ¦  In this example, COMMIT is used to force a write to disk after
        a series of memory variables are assigned to field variables:

        USE Sales EXCLUSIVE NEW
        MEMVAR->Name := Sales->Name

        MEMVAR->Amount := Sales->Amount
        //
        @ 10, 10 GET MEMVAR->Name
        @ 11, 10 GET MEMVAR->Amount
        READ
        //
        IF UPDATED()
           APPEND BLANK
           REPLACE Sales->Name WITH MEMVAR->Name
           REPLACE Sales->Amount WITH MEMVAR->Amount
           Sales->( DBCOMMIT() )
        ENDIF

HTH,

Warm regards,

Jayadev

[Lorenzo Fiorini]

On Tue, Aug 11, 2015 at 10:47 AM, Jayadev Urath <ujay...@gmail.com> wrote:

> Apart from the advices already given, please use dbCommit() after any
> add/delete/modification.

Are you sure this is a good advise?

IMHO record lock/unlock does all that is needed for normal operations.

dbcommit() is never a solution for data integrity problems
( or something really strange is happening ).

best regards,
Lorenzo

[Jayadev]

Hi,

>>Are you sure this is a good advise?

>>IMHO record lock/unlock does all that is needed for normal operations.

>>dbcommit() is never a solution for data integrity problems
>>( or something really strange is happening ).

Please Read:

     DBCOMMIT() causes all updates to the current work area to be written to
     disk.  All updated database and index buffers are written to DOS and a
     DOS COMMIT request is issued for the database (.dbf) file and any index
     files associated with the work area.

IMHO, Lock/Unlock does not flush the work area.  DBCommit does, in multi-user Windows environment, we have to remember, Windows has its own mechanism as to when to flush the data to disk.  You may have read the Oplock posts written by our experts Mr. Klas and others.  With DBCommit you are forcing the "Flushing" instead of Windows and it makes the difference.

In SQL scenario, you have BEGIN TRANSACTION, COMMIT TRANSACTION on error, ROLLBACK TRANSACTION mechanism, DbCommit can be roughtly considered equal to COMMIT TRANSACTION.

HTH,


Warm regards,

Jayadev

[Lorenzo Fiorini]

On Tue, Aug 11, 2015 at 12:55 PM, Jayadev <ujay...@gmail.com> wrote:

> IMHO, Lock/Unlock does not flush the work area. DBCommit does, in
> multi-user Windows environment, we have to remember, Windows has its own
> mechanism as to when to flush the data to disk. You may have read the
> Oplock posts written by our experts Mr. Klas and others. With DBCommit you
> are forcing the "Flushing" instead of Windows and it makes the difference.

From a Przemyslaw's message that you can find in this list:
---
COMMIT is completely unimportant in case of synchronization so it can be
removed. Before UNLOCK modified buffers are written automatically to
data base and index files. COMMIT make only one additional job: it sends
to operating system / file server request to store internal OS buffers
to disk files.
...
There is only problem with some myths in Clipper world about COMMIT/
SKIP(0)/DBGOTO(RECNO()) operations and when/where/how they should be
used.
---

best regards,
Lorenzo

[JoséQuintas]

From clipper ng:

"In a network environment, any record movement command, including SKIP,
makes changes to the current work area visible to other applications if
the current file is shared and the changes were made during an RLOCK().
To force an update to become visible without changing the current record
position, use SKIP 0."

Since Clipper days, I never use COMMIT, only SKIP 0 after REPLACE.

Note: if becomes visible in a network environment, I think it is on
server, and terminal have nothing to do.

José M. C. Quintas

[elch]

Hi,

 

Since Clipper days, I never use COMMIT, only SKIP 0 after REPLACE.

do you remember the comment on that ?

https://groups.google.com/d/msg/harbour-users/igDTvpPLFEI/Txv0yeZGHrkJ

---
And i fear a bit, such confusing hints like to use DBSKIP(0) or DBCOMMIT(),
will not help Antonino to localize a maybe very simple topic.
DBCOMMIT() had an influence on NETWARE networks with unreliable D O S stations,
but i somehow fear Antonino have not ..;-)

regards
elch

 

[JoséQuintas]

You remember me it.

But comment is about workstation that makes the SKIP 0, not about another workstations in network

Without SKIP 0 (or anything like it), changes are not visible by another workstation.

Then, if application continues in same record after replace, another workstation will use old data.

Only now, I have a conclusion:
Depends on what the code do after the REPLACE, do not need SKIP 0 or UNLOCK.

In a routine like this, do not need SKIP 0 neither UNLOCK on each record.

DO WHILE .NOT. Eof()
   rLock()
  REPLACE ...
   SKIP
ENDDO
UNLOCK

Now I "see" in Clipper ng:
"Both FLOCK() and RLOCK() release the current lock before setting a new one"

Thanks to remember me the post, this makes me think about SKIP 0 and UNLOCK.
They can be needed or not, depends on routines.

José M. C. Quintas