|
We have an Installation of 2 SQL-Anywhere 17 Databases, synchronizing with each other via Mobilink. The Mobilink Server brings continously warnings: The consolidated and remote databases have different timestamp precisions. Consolidated database timestamps are precise to 0 digit(s) in the fractional second while the remote database timestamps are precise to 6 digit(s). Resolve the timestamp precision mismatch by setting the SQL Anywhere DEFAULT_TIMESTAMP_INCREMENT option on the remote database to 1000000 and TRUNCATE_TIMESTAMP_VALUES to 'On'. UltraLite remotes must set the TIMESTAMP_INCREMENT option to 1000000. The timestamp precision mismatch may affect upload conflict detection. Use the -zp option to cause the synchronization server to use the lowest timestamp precision for conflict detection purposes This means for me that the remote has a lower precision than the server. But the following statements gives the same result on cons. and rem. select * from sysoptions where "option" in ('TRUNCATE_TIMESTAMP_VALUES', 'DEFAULT_TIMESTAMP_INCREMENT');
---> default_timestamp_increment = 1
---> truncate_timestamp_values = Off
Also, the following statement gives the actual time on cons. and rem. with the same resolution:
select current timestamp; ---> 2022-11-29 11:02:55.394 Which means that both databases have the same timestamp resolution. I thought it may be related to the already existing timestamps in the table being synchronized, so that I cut all the fractions (milliseconds) from the column of the table in charge, it did not help! update MYTABLE set ColWithTimeStamp = dateadd(millisecond, -millisecond(ColWithTimeStamp), ColWithTimeStamp); I tried to start mlsrv17 with the option -zp, also no success! How could the solution look like? |
|
What value is returned if you connect to the consolidated database using DBISQL using the same connection string that the Mobilink Server uses and you execute the following SQL : SELECT last_modified FROM ml_scripts_modified I suspect no rows are returned, which is why ML is unable to determine the timestamp precision. If that's the case, populate the table again with a single row, using the same SQL you can find in the %SQLANY17%\MobiLink\setup\syncsa.sql file. insert into ml_scripts_modified ( last_modified ) values ( dateformat( CURRENT TIMESTAMP, 'yyyy/mm/dd hh:nn:ss' ) ); Reg Thank you very much! It has worked, I dont know who/why was the table empty!
(29 Nov '22, 16:05)
Baron
Replies hidden
3
I don't like the way we handle that situation without returning a warning that we were unable to determine the timestamp precision. I plan on making a change to make this situation more clear in the future.
(29 Nov '22, 16:11)
Reg Domaratzki
The table ml_scripts_modified should be refreshed every time a new mobilink script is updated/inserted. Calling the procedure ml_add_table_script didnt help as long as the table ml_scripts_modified was empty, once the table is filled, then calling the procedure refreshes the table. i.e. the trigger (if there is any) can only update the table but cant insert a new row. There is no any visible trigger on this table: select * from systriggers where tname = 'ml_scripts_modified'--> nothing
(30 Nov '22, 02:58)
Baron
1
There should be a "ml_script_trigger" (among others) according to the syncsa.sql script in the \MobiLink\Setup directory... The trigger is not a trigger on table ml_scripts_modified because that table isn't the one the trigger has to react upon - instead it is triggered by DML statements on table ml_script. (And there are similar triggers for ml_table_script and ml_connection_script).
(30 Nov '22, 04:02)
Volker Barth
Oje, sorry for the missevaluating. Yes you have right, the table ml_scripts_modified is the object and not the subject ;) The trigger does only updating the table. Could it be that the table ml_scripts_modified was from the begining (since applying syncsa.sql) empty?
(30 Nov '22, 05:28)
Baron
1
syncsa.sql adds a row to the table, and there is no code in MobiLink or any SQL Anywhere tool to delete the row. Something that wasn't an SAP process deleted the row, but you would need to look back in the transaction log (if you still have them) to see when and what deleted the row. I can tell you that it happened at the same time that you started getting the message that the consolidated database had 0 digits of precision in the fractional second. Reg
(30 Nov '22, 09:40)
Reg Domaratzki
Thanks Reg, but I dont have the tlogs anymore, we have the error since several weeks.
(30 Nov '22, 10:16)
Baron
|
of course, lowering the resolution on the remote solves the problem, but this is not what I look for:
Note, options can be different for different users and can also be changed temporarily, too, so you need to compare the settings that are in effect for the according (users and ) transactions, which might differ form the SYSOPTIONS contents.
I'm not sure whether possible differences for the timestamp_format/timestamp_with_time_zone_format options could have effects, too.
In my understanding, the opposite is true. You might check what timestamp values are reported in verbose mode.
In my case I find one entry for each option, and it is under user_name = 'PUBLIC'.
Do you mean I should query the SYSOPTIONS while running dbmlsync.exe? But under which circumstance could happen that dbmlsync.exe sets its own timestamp options? I dont see any such option for calling dbmlsync.exe!
I compared the options timestamp_format/timestamp_with_time_zone_format, are also identical and 'PUBLIC' in both DBs.
What do you mean exactly with '...check what timestamp values are reported in verbose mode.'?
I start both MLSRV17 and DBMLSYNC with -v option, and the timestamp on each line of the log files looks like this:
I. 2022-11-24 17:45:10.
You would usually check option settings via connection_property(...) to get the actual effective values, and you can do so for different users, too. AFAIK, SYSOPTION only shows the permament settings.
As to logging, I suggested to check the actual row contents via one of the -v detail levels, say -vr. (This should probaly only used for testing.)
I see it with -vr:
On of the rows is uploaded to the Cons. with a timestamp resolution of microseconds:
2022-02-15 06:00:01.010000
Despite the row in the remote is with a lower resolution:
2022-02-15 06:00:01.01
How can this happen? Could be that in the transaction log of the DB is the row with microseconds? But I dont have anymore the transaction log file containing this row!
How can you tell that? Both values are identical but the first format has more 0 digits - which to me seems primarily an effect of different timestamp_formats...
the table SYSOPTIONS is identical between Rem and Cons
Well, as stated, it does not tell what options a particular connection is using during a particular transaction.
But how can I tell which options has the particular connection of dbmlsync.exe?
Could it be related to the options with which both MLSRV17 and DBMLSYNC are started?
Well, both are clients to the according databases and should be observable via DBISQL while running on the cons resp. remote. So you could use connection_property calls for the according properties of interest and check their values.
I don't know whether their connections last long enough to do so in your case, apparently. If runtime is too short, you could also use event hooks on the MobiLink client resp. ML scripts on the ML server to issue MESSAGE TO LOG statements with the properties's results.
Thanks for the hint, here I can see it:
connection_property('timestamp_with_time_zone_format') for the connection of dbmlsync.exe is:
YYYY-MM-DD HH:NN:SS.SSSSSS+HH:NN
Whereas in SYSOPTION for public it is:
YYYY-MM-DD HH:NN:SS.SSS+HH:NN
But the question is again: How can I enforce DBMLSYNC.EXE to use the same options as PUBLIC? OR what caused the DBMLSYNC to use this option of timestamp_with_time_zone_format?
Please don't ignore Volker's questions, but I just wanted to state that the thing that surprised me the most about this message was that MobiLink determined that your v17 SQL Anywhere consolidated database had 0 digits of precision in the fractional second.
When the ML Server starts, it determines the precision in the fractional seconds by performing an update on the ml_scripts_modified table followed by a select of the value we just updated, then rolling back the operation. We execute a first attempt with a very high precision, and then repeat the process until the precision we get back in the select matches the precision we used in the update.
Would you be able to run a test where you start and then stop the ML Server with the following options, and then make the entire mlsrv17.txt and mlodbc17.txt files available for me to look at? I don't need you to perform a synchronization.
-vdb+ -ot mlsrv17.txt -tt mlodbc17.txt
Thanks, Reg
I guess that's the wrong goal. If you want to use timestamps with the default 1 microsecond precision/increment (as you have stated), you apparently need to specify an according format that does allow those 6 decimal places for fractions of a second instead of truncating those to 3 places...
I would assume that the ML server does not currently respect these 6 digits, at least that's what your initial post tells. - Or just use Mlsrv17 -zp as suggested in the warning...
As to why there is a difference between SYSOPTION and DBMlySync's temporary setting: See the docs on these options, different client APIs do set particular temporary option values. I would assume DbMlSync does, too.
@Reg Domaratzki How/where can I upload the files?
I tried using -zp, but it did not help!
As Info, we have several hunderds of such pair DBs installed in productive, all DBs are generated from the same DB, Mobilink starts with the same command in all those installations.
We have this problem only in one of those installations.
Also, when I copy this pair (defect) DBs to another machine, the same error comes with (so it is not related to system settings or similar).
I've sent a password to the email address you've used to register on the forum that should give you write access to
https://sap-my.sharepoint.com/:f:/p/reg_domaratzki/EvNTnybw_GlBpHxkpVAkJZEBQYuRNU4ltSDJkuieiTHcBg?e=vcdT3Q
did you send it to the email which I use to login, or the email shown in my profile? They are different.
I didnt get anything on the first, I dont have access on the latter!
I don't have access to the email address you use to login.
Are you OK if I post the password here? Everyone will have access to the files you upload if I post the password here.
Reg
Both files uploaded now! Thanks
I've responded to this thread with an answer, since I'm pretty confident I've sorted out the issue.