I want to access one database from another, but they both are on the same database server. So when I try do it by Proxy tables I can't create remote server: "[Sybase][ODBC Driver][SQL Anywhere]Unable to connect, server definition is circular". Please help.

asked 08 Mar '12, 09:07

nemo's gravatar image

nemo
51226
accept rate: 0%

edited 15 Mar '13, 20:40

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273


Here's an sample that works...

"%SQLANY12%\Bin32\dbinit.exe" ddd1.db
"%SQLANY12%\Bin32\dbinit.exe" ddd2.db

"%SQLANY12%\Bin32\dbspawn.exe" -f "%SQLANY12%\Bin32\dbeng12.exe" -n ddd ddd1.db ddd2.db

"%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd;DBN=ddd1;UID=dba;PWD=sql"
"%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd;DBN=ddd2;UID=dba;PWD=sql"

---------------------------------------------------------------------
-- On ddd2

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 2 );
INSERT t VALUES ( 2, 2 );
COMMIT;

---------------------------------------------------------------------
-- On ddd1

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd;DBN=ddd2';

CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere
   TO ddd2_server 
   REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

CREATE EXISTING TABLE proxy_t 
   AT 'ddd2_server...t';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

pkey,data
1,2
2,2
permanent link

answered 08 Mar '12, 09:15

Breck%20Carter's gravatar image

Breck Carter
26.3k430600866
accept rate: 21%

edited 08 Mar '12, 09:16

Thanks! That worked ;D

(08 Mar '12, 09:29) nemo

Trying to access the same database using proxy tables that resides on the same server can cause undetectable deadlocks and could cause your connection (or database server) to hang/wait forever and therefore doing this is not recommended.... and is why the server detects this case and disallows it.


[correction] If a proxy table refers to the same database from which the request was made then this is a circular connection and the database server will disallow the request. A deadlock can occur in this scenario because the originating connection could have a lock on a row or table which blocks the transaction on the proxy connection and hence the proxy request would never succeed. If multiple connections were to do the same or similar operations then eventually all of the server's worker threads would be consumed and the entire server would hang. Therefore circular proxy connections are disallowed.


Since you are getting a circular connection error your proxy table definition must be incorrect - it must be pointing back to the same database from which it came. Check your SQL to make sure that you are connecting to the correct (second) database.

permanent link

answered 08 Mar '12, 09:15

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

edited 08 Mar '12, 09:38

1

Huh???????

I think you're talking about web servers and web clients, a bogus limitation that nonetheless actually exists :)

(08 Mar '12, 09:21) Breck Carter
Replies hidden
Comment Text Removed

Thanks Breck... I have corrected my answer.

(08 Mar '12, 09:36) Mark Culp

What prevents the deadlock from being detected in this case? There are two connections involved, the originating connection, and the circular proxy connection... what makes this pair of deadlocking connections any different from some other pair of deadlocking connections? The whole argument for "no circular proxy connections" depends on such a deadlock not being detected... has anyone ever TESTED that to see if it is true?

(08 Mar '12, 09:47) Breck Carter
Replies hidden

...(and can :)

(08 Mar '12, 09:48) Breck Carter

Why would anyone want a circular proxy connection? Because some general-purpose code has been written to handle a process involving two databases, and sometimes it is needed for the situation where the "two databases" are actually one and the same. In particular, it is QUITE ANNOYING that Foxhound cannot monitor itself... I have to futz with another copy, running on different db and http ports... all because some Nanny has decided that I need to be protected from non-existent deadlocks.

(08 Mar '12, 09:52) Breck Carter
1

Breck: As you know there are two types of deadlocks which are detected: (1) thread deadlock which occurs when all of the server worker threads are blocked waiting for a lock or other resource which is held by another worker/connection and (2) transaction or cycle deadlock which happens when a cycle of locks are being held between connections - e.g. conn1 gets lockA, conn2 gets lockB, conn1 then attempts to get lockB but blocks because conn2 has the lock, and then conn2 attempts to get lockA but will block because it is held by conn1 and therefore fails with a deadlock error.

In the proxy table scenario the thread deadlock condition can be easily detected but the second type (transaction cycle) cannot - e.g. conn1 gets a lock on resourceA and then does a remote/proxy operation, conn2 attempts to get a lock on resourceA but blocks because conn1 is hold the resource... and we're deadlocked because conn1 will never relinquish the lock on resourceA because it is waiting for the remote/proxy operation to complete which it won't ever do because it is actually conn2 which is blocked. Note the "resource" does not have to be a table/row lock, it could be blocked on waiting for a checkpoint or commit operation to complete or other nefarious operation deep in the depths of the server.

(08 Mar '12, 15:10) Mark Culp
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×130
×125
×63
×6

question asked: 08 Mar '12, 09:07

question was seen: 2,894 times

last updated: 15 Mar '13, 20:40