Hello,

I launch a set of HA servers and in the process also start a database. I then need to Start a group of other databases. (This group could be quite large and in fact is not always a fixed set so that's why these dbs are started after the servers start up.) I have discovered that when I use the following Connection String (from a Java app server on the same subnet as the HA servers) I can connect to and work with the database that is started with the servers but I cannot do so with the databases that are started later. I have reversed the roles of the two databases so that the one that "fails" is started with the launch of the servers and the other database is started afterwards. The failure "stays" with the database that is launched later (that is, I have no reason to think there is anything "wrong" with the actual databases). Here are the specifics:

I launch the servers and start a database at the same time with:

dbsrv17 -n Server01 -x "tcpip(PORT=<port1>;DOBROAD=no)" -su <utilitydbpassword> -xf "/home/wizard/Databases/Server1/Server01.state" -o "/home/wizard/Databases/Logs/Server1/Server1 Console Log.txt" -os 256k -oe "/home/wizard/Databases/Server1/Server1 Errors Log.txt" "/home/wizard/Databases/Server1/Database1/Database1.db" -xp on

dbsrv17 -n Server02 -x "tcpip(PORT=<port2>;DOBROAD=no)" -su <utilitydbpassword> -xf "/home/wizard/Databases/Server2/Server02.state" -o "/home/wizard/Databases/Logs/Server2/Server2 Console Log.txt" -os 256k -oe "/home/wizard/Databases/Logs/Server2/Server2 Errors Log.txt" "/home/wizard/Databases/Server2/Database1/Database1.db" -xp on

dbsrv17 -n Server03 -su <utilitydbpassword> -x "tcpip(PORT=<port3>;DOBROAD=no)" -xf "/home/wizard/Databases/Server3/Server03.state" -xa "AUTH=c05593b1d355a54597b13236b448eb08c72c1260;DBN=*" -o "/home/wizard/Databases/Logs/Server3/Server3 Console Log.txt" -os 256k -oe "/home/wizard/Databases/Logs/Server3/Server3 Errors Log.txt"

I start the other database with:

dbisql -nogui -c "UID=dba;PWD=<utilitydbpassword>;DBN=utility_db;SERVER=Server01" "START DATABASE '/home/wizard/Databases/ES01/Database2/Database2.db' MIRROR ON AUTOSTOP OFF;"
dbisql -nogui -c "UID=dba;PWD=<utilitydbpassword>;DBN=utility_db;SERVER=Server02" "START DATABASE '/home/wizard/Databases/ES02/Database2/Database2.db' MIRROR ON AUTOSTOP OFF;"

The HA Connection string for a database connection is:

jdbc:sybase:Tds:<ipaddress>:<port1>?ALTERNATE_SERVER_NAME=Database1   This works
jdbc:sybase:Tds:<ipaddress>:<port1>?ALTERNATE_SERVER_NAME=Database2

The connection to Database1 works in that I can access tables in that database.

The connection to Database2 fails in that upon trying to do a SELECT I get this error: "SQL Anywhere Error -141: Table '<tablename>' not found

As I said, if I flip the roles of Database1 and Database2 the I continue to get the above error on the database that is started with the START DATABASE command.

Can some one please tell me what I am doing wrong?

Thank you.

asked 07 Feb '18, 14:32

AlK's gravatar image

AlK
735313554
accept rate: 37%

edited 08 Feb '18, 04:04

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819


You will likely need to use the jConnect RemotePWD to specify the database name to use in the connection as shown in the code snipet that follow. Please take care to use the comma as otherwise the values will not be accepted in jConnect and handled properly in communicating with a SQL Anywhere server. I would still recommend using the SQL Anywhere JDBC driver if possible as much of this is simple to achieve in that driver. Please also ensure that when starting a database on a server that you identify that it is to be mirrored (MIRROR ON in the SQL statement).

Properties props = new Properties();
props.put( "User", "dba" );
props.put( "Password", "sql" );
props.put( "RemotePWD", ",dbn=mirror_demo,con=jConn Mirroring Example" );

_connection = DriverManager.getConnection( _connStr, props );
permanent link

answered 12 Feb '18, 12:27

Chris%20Keating's gravatar image

Chris Keating
7.7k49127
accept rate: 32%

Hi Chris,

Thank you, thank you, thank you! That was the missing ingredient. I can now access the Primary db (and that in turn means I have write access).

I intent on taking your advise and replacing jConnect with SAJDBC. I tried that last week but for an unknown reason I could not get Wildfly to "see" it. By getting the code working with jConnect I can turn it over for testing (that has nothing to do with the HA) while I then return to the SAJDBC/Wildfly task. By any chance, do you have experience with Wildfly?

Thank you again for the solution!

(12 Feb '18, 14:39) AlK

I have no experience with Wildfly. The SQL Anywhere JDBC driver is a Type 2 driver which means there native components that must be available as well as the JAR file. The simplest method is install and source a SQL Anywhere install such that it is visible to the Wildfly environment. Here is the specifics of what is required:

http://dcx.sap.com/index.html#sqla170/en/html/815de54e6ce21014b42afce8c8d9eef9.html

(12 Feb '18, 15:06) Chris Keating
Replies hidden

need to use the jConnect RemotePWD to specify the database name to use

Wow, that sounds like a rather weird exotic ingredient, to say the least...

Nevertheless I see that it is even officially documented for SQL Anywhere / jConnect here.:)

(12 Feb '18, 15:38) Volker Barth

Hi Chris,

Thanks again for your help. Hopefully the migration from jConnect to SAJDBC will go smoothly this time!

(12 Feb '18, 23:13) AlK

The ALTERNATE_SERVER_NAME is based on the name specified in the

CREATE MIRROR SERVER <ALTERNATE_SERVER_NAME>
AS PRIMARY
...

It is not the server name of the database server that you want to act as the primary.

You can use the command:

dbping -c "UID=DBA;PWD=sql;SERVER=<ALTERNATE_SERVER_NAME>" -ps ServerName -pd MirrorRole,MirrorState,PartnerState,ArbiterState

to verify that the database mirroring system is running properly.

permanent link

answered 07 Feb '18, 15:46

Chris%20Keating's gravatar image

Chris Keating
7.7k49127
accept rate: 32%

Hi Chris,

I apologize. In an attempt to simplify the example I went "too far". If I understand the issue you are pointing to, here is what I'm using:

For Database1:

CREATE MIRROR SERVER PRIMDatabase1 AS PRIMARY connection_string='SERVER=PrimDatabase1;HOST=<ipaddress>:<port1>,<ipaddress>:<port2>';
CREATE MIRROR SERVER MIRRDatabase1 AS MIRROR connection_string='SERVER=MirrDatabase1;HOST=<ipaddress>:<port1>,<ipaddress>:<port2>';

Database2 had similar statements applied against it.

Here is the Connection String:

jdbc:sybase:Tds:<IPAddress>:<Port1>?ALTERNATE_SERVER_NAME=PRIMDatabase1

The problem is that when I use the above to access a database that is started at the same time that I launch the servers I have no problem accessing tables in that database. However, when I use the same Connection String (with, of course, the correct database name) to access a database started with the START DATABASE command I get the -141 error when I try to SELECT from any table. If I reverse the roles of the two databases I again fail to be able to access tables in the database started with START DATABASE even though that database was fine when started when the servers launched.

So, I believe I am using the correct "names" in the correct places in the CREATE MIRROR and Connection string but something appears to be wrong when I start a database with the START DATABASE command.

(thank you for the dbping!)

Can you see what's wrong?

(07 Feb '18, 16:34) AlK
Replies hidden

Can you confirm that the connection to a later-started mirrored database does also fail when you do use a non-jConnect connection, say by using DBISQL with the usual SA connection parameters?

(In my limited understanding, your setup seems correct.)

(08 Feb '18, 04:07) Volker Barth

Hi Volker,

Actually, the later-started db does NOT fail with iSQL. That is, I can connect and SELECT from the same table that fails when I use the jConnect Connection string. This is very strange.

(08 Feb '18, 06:50) AlK

Not necessarily very strange - it might just mean that HA with jConnect does not work as well as with other connection facilities - but that's just guesswork on my side. I leave that to jConnect/SQL Anywhere experts like Chris...:(

(08 Feb '18, 07:17) Volker Barth
1

SQLCODE -141 is NOT a connection failure. In fact, if you get SQLCODE -141 then you know the connection WORKED because SQL Anywhere is able to check the system tables for the table name.

The problem is in your code, or your user id setup on your database: "You misspelled the name of a table, or you did not qualify a table name with the owner name."

It is also possible you used the wrong user id to make the connection, or you connected to the wrong database altogether (one that does not allow userid X to "see" table Y.

(08 Feb '18, 07:20) Breck Carter
1

What is the connection string "with...the correct database name". I believe that the DBN has to be supplied in the CREATE MIRROR ... AS PARTNER connection string. I doubt that the jConnect URL ServiceName will be used when connecting in a mirrored environment.

You should run

   select DB_NAME()

on the connection where you are getting the SQL Code -141 and let us know if the database name is for another database running on the server.

(08 Feb '18, 10:30) Chris Keating
Replies hidden

Hi Breck,

I was a bit sloppy when I said that the connection failed but I cannot see how there is anything wrong with my code, table name or credentials. All of those items are static in my code and what I am trying to describe is that if I start a db with the server I can access its tables. When I start the same db after the servers have come up with START I get the -141 error. This leads me to think that there is something wrong in how I am starting or connecting.

Thanks.

(09 Feb '18, 20:07) AlK
1

Hi Chris,

I have spent the last few days trying to track down what is wrong with my jConnect based efforts. Thanks for the suggestion to use DB_NAME() - it confirmed that I am not connecting to the correct database and that's why it can't find a table in my SELECT statement.

Here are what I think are the important points: - The HA servers are running under Ubuntu 16.04 and are on the same subnet as the Wildfly Java app server. - All of the HA servers are running on the same Host. I realize that this negates a lot of the benefit of running an HA system but at the moment it is much easier fot testing and debugging and there's no indication that this is causing a problem. - For DB1 I used CREATE MIRROR SERVER PrimDB1 AS PRIMARY. - For DB2 I used CREATE MIRROR SERVER PrimDB2 AS PRIMARY. - I start all three HA servers without any databases. - I use two iSQL statements, each with a START DATABASE command to start DB1 and DB2. - In my app I try to connect to DB1 with the following: jdbc:sybase:Tds:<ipaddress>:<port1>?ALTERNATE_SERVER_NAME=PrimDB1 (I an literally using "ALTERNATE_SERVER_NAME" per your comment in my earlier post "What is the JDBC connection string for a High Availability server group?") - I then try to connect to DB2 with the following: jdbc:sybase:Tds:<ipaddress>:<port1>?ALTERNATE_SERVER_NAME=PrimDB2

Here is what I observe. - If I start DB1 FIRST, DB_NAME() shows that my 1st connection (using jdbc:sybase:Tds:<ipaddress>:<port1>?ALTERNATE_SERVER_NAME=PrimDB1) does connect to DB1. However, with second connection (using jdbc:sybase:Tds:<ipaddress>:<port1>?ALTERNATE_SERVER_NAME=PrimDB2), DB_NAME() shows that I am AGAIN CONNECTED to DB1! - If I reverse the order so that DB2 is STARTED 1st, then both of my connection attempts result in DB_NAME() ALWAYS showing DB2!

So, it seems as that whatever database is the 1st to be started in the HA system, the requested ALTERNATE_SERVER_NAME is ignored and I always connect to that 1st db! In one of your responses to my prior post you mentioned that under certain conditions "UDP discovery mechanism isn't reliable" and I am at the point where I'm wondering if jConnect is just "broken" for my case. Do you know what those conditions are? Or, can you see something wrong that I keep missing?

Thank you.

(10 Feb '18, 19:27) AlK

Yes, there may be something wrong with how you are connecting... perhaps you are connecting to a different database than the one you want. SELECT DB_PROPERTY ( 'File' ) in you code will tell you what physical database file you are actually connected to.

(11 Feb '18, 08:21) Breck Carter

Hi Breck. Thanks for the suggestion of DB_PROPERTY('File'). In my reply to Chris below I used DB_NAME() and that does confirm I'm not connecting to the db I specify. Can you see anything in the details there to suggest if I am doing something wrong or is this one of the cases Chris mentioned where jConnect just doesn't work?

(11 Feb '18, 09:51) AlK
1

and there's no indication that this is causing a problem.

Not sure if this is true. As you have stated in the other FAQ, with the SAJDBC driver your connection to the different HA databases does work, and with jConnect it fails. In my limited understanding, your connection string is correct, so it might be possible that jConnect cannot cope with different HA servers running on the same host. jConnect was originally developed for ASE, a system where one connects to a server usually running several databases, and then choosing which database to use. That's fundamentally different to SA with its connection to a specified database running on a server. AFAIK it is common that one host does host only one ASE server.

This is just guesswork on my part, but I could imagine based on jConnect's history and architecture, it just does not expect a SA HA system running on the same host.

I would still recommend the SAJDBC driver but YMMV...

With the SAJDBC driver, you could easily add the LOG connection parameter to debug the connecting phase. I don't know whether jConnect has an according property. If so, I would give that a try.

(11 Feb '18, 13:21) Volker Barth

Hi Volker,

Thanks for your perspective on the "all on one Host" issue and the history of jConnect. Perhaps that is the problem! I do plan to migrate to SAJDBC but my efforts to do do last week were blocked by some, yet to be diagnosed, problem getting the Wildfly app server to recognize the new driver. I decided to put that on hold for the moment and try and get something accomplished by getting jConnect to work. I hope Chris will respond and if there is nothing wrong with what I am doing I guess this will be a dead end and I'll have to switch back to figuring out the problem I'm having running SAJDBC under Wildfly. By any chance do you have any experience with that?

(11 Feb '18, 14:34) AlK

No, I do not use Wildfly, and honestly, I am not primarily a Java developer and have not used jConnect lately and have never used Java clients in a HA setup, so not much personal experience to share...

I just remember that switching from jConnect to SAJDBC within a Tomcat application several years ago was quite easy and straightforward.

(11 Feb '18, 14:53) Volker Barth
showing 2 of 13 show all flat view

See Chris Keating's answer above:

Properties props = new Properties(); props.put( "User", "dba" ); props.put( "Password", "sql" ); props.put( "RemotePWD", ",dbn=mirror_demo,con=jConn Mirroring Example" );

_connection = DriverManager.getConnection( _connStr, props );

permanent link

answered 12 Feb '18, 23:15

AlK's gravatar image

AlK
735313554
accept rate: 37%

Well, if Chris second answer does solve your question, I would recommend to "accept" it by pressing the checkmark in front of the answer...

(13 Feb '18, 07:42) Volker Barth
Replies hidden
1

Thanks Volker. I wanted to give him credit but am still fumbling around learning how this interface works!

(13 Feb '18, 13:56) AlK

Well, accepting answers as you obviously have done is one way to give credits, upvoting questions, answers and comments is another one... See the FAQ menu in the title bar for more information:)

(13 Feb '18, 14:12) Volker Barth

Thanks Volker.

(13 Feb '18, 14:54) AlK
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:

×86
×61
×44
×39

question asked: 07 Feb '18, 14:32

question was seen: 1,939 times

last updated: 13 Feb '18, 14:54