|
Adaptive Server Anywhere Server Version 9.0.2.3951 How do I change the field values last_download_time and last_upload_time syssync table? (via command line or instruction sql) create procedure SetLastDownload(inout @lastDownload timestamp) as begin select property('StartTime') into @lastDownload end CREATE PROCEDURE ModifyDownloadTimestamp (INOUT @last_download_time TIMESTAMP,IN @user_name VARCHAR(128) ) AS BEGIN select @last_download_time = dateadd(day, -1, @last_download_time ) END call ml_add_connection_script( 'OBA1', 'begin_connection', 'create variable @LastDownload timestamp;') call ml_add_connection_script( 'OBA1', 'begin_download', 'call SetLastDownload(@LastDownload)') Call ml_add_connection_script ( 'OBA1', 'begin_synchronization', 'CALL ModifyDownloadTimestamp(@LastDownload,? )' ); this is presented in dbmlsrv9: I. 09/27 17:37:49. Adaptive Server Anywhere MobiLink Version 9.0.2.3951 I. 09/27 17:37:49. I. 09/27 17:37:49. Copyright © 1989-2007 Sybase, Inc. Portions Copyright © 2002-2007, iAnywhere Solutions, Inc. I. 09/27 17:37:49. All rights reserved. All unpublished rights reserved. I. 09/27 17:37:49. I. 09/27 17:37:49. This software contains confidential and trade secret information of I. 09/27 17:37:49. iAnywhere Solutions, Inc. Use, duplication or disclosure of the software and documentation I. 09/27 17:37:49. by the U.S. Government is subject to restrictions set forth in a license I. 09/27 17:37:49. agreement between the Government and iAnywhere Solutions, Inc. or I. 09/27 17:37:49. other written agreement specifying the Government's rights to use the I. 09/27 17:37:49. software and any applicable FAR provisions, for example, FAR 52.227-19. I. 09/27 17:37:49. I. 09/27 17:37:49. iAnywhere Solutions, Inc., One Sybase Drive, Dublin, CA 94568, USA I. 09/27 17:37:49. I. 09/27 17:37:49. Networked Seat (per-seat) model. Access to the server is limited to 1 seat(s). This server is licensed to: I. 09/27 17:37:49. xxx I. 09/27 17:37:49. xxxxx I. 09/27 17:37:49. <Main>: MobiLink server started I. 09/27 17:37:49. <Main>: Option 1: -c I. 09/27 17:37:49. <Main>: Option 2: dsn=center I. 09/27 17:37:49. <Main>: Option 3: -x I. 09/27 17:37:49. <Main>: Option 4: tcpip(host=192.168.1.119;port=2638) I. 09/27 17:37:49. <Main>: Option 5: -v+ I. 09/27 17:37:49. <Main>: Option 6: -za I. 09/27 17:37:49. <Main>: Option 7: -zu+ I. 09/27 17:37:49. <Main>: Option 8: -tu I. 09/27 17:37:49. <Main>: Verbose logging: show row values I. 09/27 17:37:49. <Main>: Verbose logging: show script names when invoked I. 09/27 17:37:49. <Main>: Verbose logging: show script contents when invoked I. 09/27 17:37:49. <Main>: Verbose logging: show schema for each table I. 09/27 17:37:49. <Main>: Verbose logging: show an error when the first read of a synchronization fails I. 09/27 17:37:49. <Main>: Verbose logging: show translated SQL for prepared statements I. 09/27 17:37:49. <Main>: Verbose logging: show rowcount values I. 09/27 17:37:49. <Main>: Individual database connections will be closed after synchronization errors. I. 09/27 17:37:49. <Main>: BLOB cache size: 524288 I. 09/27 17:37:49. <Main>: Maximum number of BLOB bytes to compare: 4294967295 I. 09/27 17:37:49. <Main>: Maximum number of database connections: 6 I. 09/27 17:37:49. <Main>: Maximum number of deadlock retries: 10 I. 09/27 17:37:49. <Main>: Timeout for inactive database connections: 60 minutes I. 09/27 17:37:49. <Main>: Maximum delay between retries after deadlock: 30 seconds I. 09/27 17:37:49. <Main>: Rowset size: 10 I. 09/27 17:37:49. <Main>: Upload cache size: 524288 bytes I. 09/27 17:37:49. <Main>: Download cache memory size: 1048576 bytes I. 09/27 17:37:49. <Main>: Download cache directory size: 10485760 bytes I. 09/27 17:37:49. <Main>: Number of worker threads: 5 I. 09/27 17:37:49. <Main>: Maximum number of threads uploading concurrently: 5 W. 09/27 17:37:49. <Main>: Warning: Unknown users will be added automatically (when there is no authenticate_user script). I. 09/27 17:37:49. <Main>: Warning: Example scripts may be automatically generated and activated for unknown versions. I. 09/27 17:37:49. <Main>: Local file for remote synchronization logs: 'dbmlsrv.mle' I. 09/27 17:37:49. <Main>: Stream specifier 1: tcpip(host=192.168.1.119;port=2638) I. 09/27 17:37:50. <Main>: ODBC DBMS Name: Adaptive Server Anywhere I. 09/27 17:37:50. <Main>: ODBC DBMS Version: 09.00.0002 I. 09/27 17:37:50. <Main>: ODBC DBMS Driver Version: 09.00.0002 I. 09/27 17:37:50. <Main>: ODBC Version supported by the driver: 3.52 I. 09/27 17:37:50. <Main>: Collation sequence of the consolidated database is 'cp1252' I. 09/27 17:38:00. <Main>: Translated SQL: set temporary option chained = 'ON' I. 09/27 17:38:01. <Main>: ODBC isolation set to: Read Committed I. 09/27 17:38:01. <Main>: Connected I. 09/27 17:38:07. <Main>: Translated SQL: SELECT COUNT(*) FROM ml_scripts_modified I. 09/27 17:38:07. <thread 1.1>: Ready to handle requests I. 09/27 17:38:07. <thread 1.2>: Ready to handle requests I. 09/27 17:38:07. <thread 1.3>: Ready to handle requests I. 09/27 17:38:07. <thread 1.4>: Ready to handle requests I. 09/27 17:38:07. <thread 1.5>: Ready to handle requests I. 09/27 17:38:17. <thread 1.5>: Working on a request I. 09/27 17:38:17. <thread 1.5>: Translated SQL: SELECT last_modified FROM ml_scripts_modified I. 09/27 17:38:18. <thread 1.5>: ml_scripts_modified last modified at: 2013-09-27 17:36:19.000000 I. 09/27 17:38:18. <thread 1.5>: Translated SQL: SELECT version_id FROM ml_script_version WHERE name = ? I. 09/27 17:38:18. <thread 1.5>: ASA synchronization request from: simple (version: OBA1) I. 09/27 17:38:19. <1.5> [simple]: Translated SQL: set temporary option chained = 'ON' I. 09/27 17:38:19. <1.5> [simple]: ODBC isolation set to: Read Committed I. 09/27 17:38:19. <1.5> [simple]: Translated SQL: SELECT t1.event, t2.script_language, t2.script FROM ml_connection_script t1, ml_script t2 WHERE t1.version_id = ? AND t1.script_id = t2.script_id I. 09/27 17:38:20. <1.5> [simple]: begin_connection <connection> create variable @LastDownload timestamp; I. 09/27 17:38:20. <1.5> [simple]: Translated SQL: create variable @LastDownload timestamp; I. 09/27 17:38:21. <1.5> [simple]: COMMIT Transaction: begin_connection I. 09/27 17:38:21. <1.5> [simple]: Translated SQL: SELECT hashed_password FROM ml_user WHERE name = ? I. 09/27 17:38:22. <1.5> [simple]: Translated SQL: SELECT user_id FROM ml_user WHERE name = ? I. 09/27 17:38:23. <1.5> [simple]: Publication #1: OBA_OBA, subscription id: 2, last download time: 1900-01-01 00:00:00.000000 I. 09/27 17:38:23. <1.5> [simple]: Translated SQL: SELECT progress FROM ml_subscription WHERE user_id = ? AND subscription_id = ? I. 09/27 17:38:24. <1.5> [simple]: Progress offsets for the publications that are explicitly involved in the current synchronization I. 09/27 17:38:24. <1.5> [simple]: Subscription id 2: consolidated progress 361487029 and remote progress 361471492 I. 09/27 17:38:24. <1.5> [simple]: Synchronization failed this is presented in dbmlsrv9: I. 09/27 18:04:24. Synchronization Started I. 09/27 18:04:24. Adaptive Server Anywhere MobiLink Synchronization Version 9.0.2.3951 I. 09/27 18:04:24. I. 09/27 18:04:24. Copyright © 1989-2007 Sybase, Inc. Portions Copyright © 2002-2007, iAnywhere Solutions, Inc. I. 09/27 18:04:24. All rights reserved. All unpublished rights reserved. I. 09/27 18:04:24. I. 09/27 18:04:24. This software contains confidential and trade secret information of I. 09/27 18:04:24. iAnywhere Solutions, Inc. Use, duplication or disclosure of the software and documentation I. 09/27 18:04:24. by the U.S. Government is subject to restrictions set forth in a license I. 09/27 18:04:24. agreement between the Government and iAnywhere Solutions, Inc. or I. 09/27 18:04:24. other written agreement specifying the Government's rights to use the I. 09/27 18:04:24. software and any applicable FAR provisions, for example, FAR 52.227-19. I. 09/27 18:04:24. I. 09/27 18:04:24. iAnywhere Solutions, Inc., One Sybase Drive, Dublin, CA 94568, USA I. 09/27 18:04:24. I. 09/27 18:04:24. Connecting to remote database I. 09/27 18:04:24. Loading synchronization information I. 09/27 18:04:24. Begin synchronizing 'OBA_OBA' for MobiLink user 'simple' I. 09/27 18:04:24. Unknown last synchronization status, so get it from MobiLink server I. 09/27 18:04:24. Connecting to MobiLink server at 'host=192.168.1.119;port=2638' using 'dbmlsock9.dll' I. 09/27 18:04:24. Uploading header I. 09/27 18:04:29. The user authentication value is 1000. I. 09/27 18:04:29. Disconnecting from MobiLink server E. 09/27 18:04:29. No log operation at offset of 0361487029 in the current transaction log I. 09/27 18:04:29. Hovering at end of active log E. 09/27 18:04:29. Upload aborted at offset 0361487029 E. 09/27 18:04:29. Download aborted I. 09/27 18:04:29. Complete log scan required. I. 09/27 18:04:29. Synchronization completed I. 09/27 18:04:29. Disconnecting from remote database |
|
Your synchronization failed as soon as it started because the consolidated and remote database are "out of step" with each other. Since this is development, it is best if you start again from scratch, recreate both databases, and don't restore anything from a backup if you expect synchronization to continue working. Your two procedures SetLastDownload and ModifyDownloadTimestamp were never reached because synchronization failed long before they would have been called. Even if they had been called, they would have no effect on synchronization because all they do is read and write your user-defined global variable 'create variable @LastDownload timestamp;'. You probably got this idea from an example using the MobiLink 'modify_last_download_timestamp' connection event. You probably DO NOT WANT TO DO THIS before you understand how MobiLink works. 99% of MobiLink setups do not need to modify the last download timestamp, and you have not given any reason why you need to do that. Please stop looking for difficult solutions to what is not a difficult problem: synchronizing data between a consolidated and multiple remotes. Go back to the simple demo, and modify it to work for you. Do not make use of any fancy MobiLink features until you KNOW that you need them.
Ok, I'll start. Thank you!
(30 Sep '13, 09:11)
Walmir Taques
Replies hidden
I would still suggest to ask a new question if you want to know how to setup a system with a "smart failover" between remotes accessing a central database (aka online) and switching to an offline/sync'ed database in case of lost connections. That's a very different question compared to "how to reset a sync"...
(30 Sep '13, 15:29)
Volker Barth
In a MobiLink synchronization setup, if there is no communication the remote databases can continue to operate indefinitely... hours, days, weeks. There is no reason to "take a backup" just because there's no communication. Failure to synchronize is not an error, it is just a delay... when communication is restored you can run a synch.
(30 Sep '13, 15:42)
Breck Carter
Sure. However, I have had the impression that Walmir would like to setup a permantently connected system (i.e. stores directly accessing a central database) that would switch however to a disconnected mode (i.e. remote databases with syncing against the central database) when connection is lost. If that impression is correct, it would surely make sense to discuss if such a setup is possible/reasonable, and how to set it up - or what alternatives would exist.
(30 Sep '13, 15:45)
Volker Barth
1
The question contained a MobiLink log. It is not possible to set up a permanently connected system with MobiLink, and IMO it is not reasonable to let a discussion of MobiLink drift in that direction :) Perhaps the OP should post a question "Here are my requirements, what should I do?" without including assumptions about MobiLink etcetera.
(30 Sep '13, 16:46)
Breck Carter
Volker / Carter. I tried to sync and it worked without problems (sales made and sent to the center). My idea is to use a local database only when lost communication (I could do this in Delphi, but I thought that would work in these conditions Mobilink) I'll open a new topic following this line of reasoning: **how to setup the system with a "smart failover" . Permantently setup the connected system (ie stores directly Accessing a central database) However que would switch to a disconnected mode (ie remote databases with syncing against the central database) When connection is lost. setup is possible / reasonable, and how to set it up - or what alternatives would exist.**
(30 Sep '13, 17:02)
Walmir Taques
Replies hidden
If that is the new question, please ask it accordingly - i.e. ask a completely new question instead of posting this as a comment somewhat hidden in a very different question...
(01 Oct '13, 03:54)
Volker Barth
1
That is a bad idea: Using direct database I/O with the central database when everything is OK, then switch to a local database when communication is lost. The local database must be maintained while communications are working because it is TOO LATE to fill it after communications are lost (were your imagining some other possibility? that you could somehow fill the remote database AFTER comms were lost? :)... so you might as well hook your local application to the local database all the time. Having your application connect to the central database some of the time, and the local database the rest of the time, is a waste of development effort AND of testing effort, and you have to test extra scenarios: Does the application work when the switchover to local happens? Does it work when the opposite switchover happens? The last time I heard people talk about this kind of solution was back in the 1990s, before people trusted SQL Remote (before MobiLink) to work properly, when people didn't understand how difficult it was to create their own synchronization processes and the problems they could have. Summary: When you use MobiLink, your remote application should ONLY connect to the local database, and rely on MobiLink to send and receive data to/from the consolidated database. MobiLink is a synchronization product. It is not a High Availability product. MobiLink works with thousands of databases. High Availability works with exactly two databases (ok, it works with more than two, but the others are read-only :)
(01 Oct '13, 08:30)
Breck Carter
It is not a new question. I do not know if I'll open a new topic after the affirmative @ Breck Carter. I will look deeper into the matter and also study mirroring database. Thank you.
(01 Oct '13, 09:17)
Walmir Taques
Well, I will accept that we are discussing such a setup in some sub-comments... I agree with Breck that a "sometimes locally connected, sometimes centralized" application is difficult to design and to maintain. - MobiLink and SQL Remote are fine when you want your clients to be able to work locally even if a connection to a central database is not always available. Your applications simply work "always locally", and the sync component will make sure that your central site (and possibly other remote sites) get fresh data now and then (which may mean after a short time lag of some seconds/minutes if connecting is possible). Note, when using local databases at the remote sites, one usually will assume that a) data entry must be done locally because of performance and/or b) the remotes are more likely to have problems to connect compared to the central site. However, if you want to protect against connecting problems at the central site (assuming the clients will be able to connect but the central site may fail), and remote data entry is an option (say, by means of a web frontend accessing a central database), I'd think the way to go would be to setup a HA system, so clients could switch between two central database sites. A further option with SA would be to use some cloud-based solution like SA OnDemand Edition.
(01 Oct '13, 09:19)
Volker Barth
Ok. I will look deeper into the matter and also study mirroring database. Thank you.
(01 Oct '13, 09:21)
Walmir Taques
|
|
Hi Walmir, For what its worth: we have used MobiLink successfully for 10 years now for exactly your purposes. We have some very remote locations where connectivity is often slow and/or dropped altogether of periods of time. Having a full-time local database, syncing with Mobilink in the background, has insured: 1) the local site runs at full speed always without communication slowdowns, and 2) the data is updated in our central database in the most reliable way. I don't see where you need to be creating a solution, but I do believe spending some time implementing Mobilink will be a big help. I'd say SQL Remote has helped us in a comparable fashion for over a decade - starting with remote connections via dial-up/ISDN. Historical "high-speed" connections:)
(01 Oct '13, 10:58)
Volker Barth
|
They are changed automatically during the synchronization. Why do you want to override that process?
I'll try to explain why I want to do this. Can this work? I have only one (01) database - center (consolidated / main). The structure is as follows: Ten (10) different companies connect this database (each with its separate drive)
Assuming that there is a lack of communication between the stores want to take a backup of the database center and remote start in the store so they are not without making sales. When I make the connection reestablished synchronizing remote center, and shop around to connect at center and the bank was started (remote) is dropped (discarded since synchronization has worked). Center but does not update the table syssync only remote updates.
Spent several days and failed again in remote communication center and, beginning in remote backup center and I make sales when reestablished the connection I make synchronizing remote center.
I hope you understand.
I'd suggest to ask that – surely interesting - system architetcure question as a new one ...
You should not update those columns... MobiLink works just fine, and will synchronize one consolidated with 10 remote databases. If you have a problem, ask a question about the problem... show us the code, and show us the problem you are having... show us the EXACT code, and show us the EXACT error message, or provide a description of the symptom if there is no error message.
Do not worry about your English... we don't care about English, we care about SQL, so show us the code.
I changed the main question,
added scripts and samples dbmlsrv9 and dbmlsync9.
thank you