SQL Remote Publisher Address Loses Backslash in extracted remote database

[former_SQLA_member1694871]

We've run into an issue with the SQL Remote address and publisher address changing in the extracted remote database. Specifically the path. FYI. We are using the dbxtract utility via the command line.

Here is an example.

First off, we are using file based replication. The FILE publisher address for the consolidated database is

"\\\\Frontrunner\\frontrunner\\repfiles\\central".

One of our extracted remote databases replication address is

"\\\\Frontrunner\\frontrunner\\repfiles\\conderd"

"\\\\Frontrunner\\frontrunner is the server and share name that we have used with replication for years.

The problem is that in the extracted remote database under SQL Anywhere 12, the initial backslash is being truncated from the address. So the SQL Remote and publisher addresses are ending up as

"\\Frontrunner\\frontrunner\\repfiles\\central"

and

"\\Frontrunner\\frontrunner\\repfiles\\conderd"

So when replication is run for conderd, the following log is generated...

I. 2011-12-05 18:35:14. SQL Remote Message Agent Version 12.0.1.3484
I. 2011-12-05 18:35:14. 
I. 2011-12-05 18:35:14. Copyright © 2001-2011, iAnywhere Solutions, Inc.
I. 2011-12-05 18:35:14. Portions copyright © 1988-2011, Sybase, Inc. All rights reserved.
I. 2011-12-05 18:35:14. Use of this software is governed by the Sybase License Agreement.
I. 2011-12-05 18:35:14. Refer to http://www.sybase.com/softwarelicenses.
I. 2011-12-05 18:35:14. 
I. 2011-12-05 18:35:14. 1: -l
I. 2011-12-05 18:35:14. 2: 100000
I. 2011-12-05 18:35:14. 3: -c
I. 2011-12-05 18:35:14. 4: ********************************************
I. 2011-12-05 18:35:14. 5: -k
I. 2011-12-05 18:35:14. 6: -v
I. 2011-12-05 18:35:14. 7: -t
I. 2011-12-05 18:35:14. 8: -ot
I. 2011-12-05 18:35:14. 9: C:\\Documents and Settings\\Don Conder\\My Documents\\Frontrunner\\Database\\dbremote.dat
I. 2011-12-05 18:35:19. Scanning logs starting at offset 0011893432
I. 2011-12-05 18:35:19. Processing transaction logs from directory "C:\\Documents and Settings\\Don Conder\\My Documents\\Frontrunner\\Database\\"
I. 2011-12-05 18:35:19. Processing transactions from active transaction log
I. 2011-12-05 18:35:21. Sending message to "frunner_pub" (0-0000000000-0012017028-0)
I. 2011-12-05 18:35:21. sopen "\\Frontrunner\\frontrunner\\repfiles\\central\\conderd.0" failure 3: No such file or directory
I. 2011-12-05 18:35:21. sopen "\\Frontrunner\\frontrunner\\repfiles\\central\\conderd.1" failure 3: No such file or directory
I. 2011-12-05 18:35:21. sopen "\\Frontrunner\\frontrunner\\repfiles\\central\\conderd.2" failure 3: No such file or directory
I. 2011-12-05 18:35:21. sopen "\\Frontrunner\\frontrunner\\repfiles\\central\\conderd.3" failure 3: No such file or directory
I. 2011-12-05 18:35:21. sopen "\\Frontrunner\\frontrunner\\repfiles\\central\\conderd.4" failure 3: No such file or directory
E. 2011-12-05 18:35:21. Error sending message
I. 2011-12-05 18:35:21. Resend requests are being queued
I. 2011-12-05 18:35:22. Execution completed

However, if I open the extracted database first, and add the "" back to the front of the address, replication runs perfectly.

I'm going to go out on a limb here and say that this is a bug in the extraction utility.

We never ran into this issue extracting a database from our consolidated database when it was running under ASA6.

Has anybody else ran into this type of an issue after an extraction? Just curious. We are running SQL Anywhere 12.0.1.3484.

TIA

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

[Former Member]

We've had this happen in ASA9/SA10 through the years. We just got used to fixing them, unfortunately, instead of reporting it.

[former_SQLA_member1694871]

Thanks for the heads up on this Calvin!! Glad to see somebody else ran into this besides myself. Hopefully since two of us have reported it now, they can figure out a fix for it.

Jeff Gibson

[regdomaratzki · Accepted solution]

This looks a lot like a bug to me, but I'm surprised you haven't run into this before. Have you just started using UNC names, or did you recently upgrade to v1201?

[former_SQLA_member1694871 · Accepted solution]

Hey Reg. Thanks for checking on this.

This system has been in operation since 1999. Had been using replication extensively in ASA 6.0.4 Build 3799 until about 4 weeks ago when we completed our migration to the latest version of SQL Anywhere 12. We have used UNC paths since the beginning.

Normal protocol at this company is to extract a new database for the Superintendent that is going out on a new construction project.

We've never had to alter the UNC path post extraction on the remote db's. It always came over correctly.

One small thing I forgot to add. When we migrated from ASA6 to SQLA12, the UNC path was truncated on the consolidated database. I had to modify the paths there initially.

I agree with the points that Volker is making. That I'm dealing with this being a type of control character issue. The problem is, if I add additional backslashes on the consolidated side to account for what happens on the remote databases, then that breaks the consolidated side, because you no longer have an UNC path then.

Should I post this in case express Reg???

Thanks for the help.

Jeff Gibson

[regdomaratzki · Accepted solution]

I wouldn't bother opening a case express case for this issue. I'm testing a change right now, but I'm not convinced I like it just yet. I suspect the behaviour changed when we merged all the SQL files that used to reside in %SQLANY??%\\scripts into the dbscript??.dll in v10.

[former_SQLA_member1694871 · Accepted solution]

Thanks for the heads up on this Reg!! Any information on getting this fix out in an EBF would be appreciated. Until then, I'll continue to modify the UNC path on the remote databases as we extract them.

Jeff Gibson

[regdomaratzki · Accepted solution]

Sorry for the delay Jeff. I checked in this change today. Here are the details :

SA Bug Fix (QTS 693255) - UNC paths for SQL Remote addresses could be mangled during unload/extract

Versions affected: 11.x+

Versions fixed: 11.0.1.2729, 12.0.1.3527

Modules affected: dbscript1?.dll

Customer Description: If the publisher's address for a database contained a UNC path, or if the address for a remote user contained a UNC path, dbunload and dbxtract would have failed to escape the string properly, resulting in an address in the new database with missing backslash characters. This has now been fixed.

We are hoping to post a v1201 EBF shortly, BUT it will likely be v1201.3519, so it will NOT include this fix.

[former_SQLA_member1694871 · Accepted solution]

Thanks for the heads up on this Reg! I'll make sure and keep an eye out for that EBF.

Jeff Gibson

[VolkerBarth]

No, I have not seen that behaviour - simply as we don't use a netshare for our FILE protocol, and instead use the SQLREMOTE environment variable to set the path of the FILE directory. So I'm just guessing:

But the issue may be due to the fact that with newer versions, the SQL Remote options are stored inside the database by default (inside system table SYSREMOTEOPTION). As such, you may have run into a classic character-masking problem, i.e. the backslashes might have to be doubled - as with other character data, such as:

SET REMOTE FILE OPTION PUBLIC.directory =
   '\\\\\\\\Frontrunner\\\\frontrunner\\\\repfiles\\\\central';

---

The SQL Remote external_remote_options option does decide whether SQL Remote options are stored inside (default) the database or outside.

[former_SQLA_member1694871]

That is new to me that the SQL remote options could be stored outside the database (trying to determine how that would benefit me).

I'm still leaning towards this being a bug. We've used shares like this for over eight years in replicating environments. I'm thinking they (the SQL Anywhere team) needs to take into account that there could be a double backslash at the front of the publishers or remote users address.

I agree with you though that it's probably looking at the initial backslash as some type of control character.

However, if that were the case, you would think it would be acting that way at every backslash. And it's only doing it on the first one.

Just throwing this out there.

Thanks for the response and the eduction on the new replication options.

Jeff

[VolkerBarth]

FWIW, Jeff, I don't claim that this is or is not a bug - but the behvaiour you are seeing w.r.t. to the doubled backslash being "singled" is due to the common literal string handling in SQL Anywhere, as documented here:

A backslash followed by any character other than n, x, X, or \\ is interpreted as two separate characters. For example, \\q inserts a backslash and the letter q.

So, I would conclude that in case other parts of your net share path would have started with "n" or "x", the backslash would have been treated unexpected for you as well.

---

Same old story: In order to show baskslashes here, in some (but not all?) cases they need to be doubled, too...:)