How is it possible for DB_PROPERTY ( 'ConnCount' ) to return '0'?

[Breck_Carter]

Reproducible: dbsrv17 -k

...stay tuned for more details.

---

The previous title was "Why are most DB_PROPERTY values returned as zero?" but the question is more fundamental: What could possibly make 17.0.10.5963 return '0' from DB_PROPERTY ( 'ConnCount' ) without crashing the server or causing other symptoms?

---

Previous post...

Why are most DB_PROPERTY values returned as zero?

I have never seen anything like this: The databases in one instance of SQL Anywhere 17.0.10.5963 are returning zero for many DB_PROPERTY values.

For example, DB_PROPERTY ( 'ConnCount' ) cannot be zero, yet for this server the return value is '0'.

Here are some excerpts from SELECT CURRENT TIMESTAMP, @@VERSION, * FROM sa_db_properties() from the suspect server, and from a test server on a different computer:

Suspect server...

2020-02-12 08:53:22.426,'17.0.10.5963',0,392,'ApproximateCPUTime','Approximate CPU time used','1724030.1124608'
...
2020-02-12 08:53:22.426,'17.0.10.5963',0,2,'BytesReceived','Bytes received by server','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,3,'BytesReceivedUncomp','Bytes received after decompression','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,4,'BytesSent','Bytes sent to client','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,5,'BytesSentUncomp','Bytes sent before compression','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,10,'CacheHits','Cache Hits','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,16,'CacheRead','Cache reads','0'
...
2020-02-12 08:53:22.426,'17.0.10.5963',0,43,'ConnCount','Number of active connections','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,393,'ConnectedTime','Total time connections have been connected','14877281.5125692'
...
2020-02-12 08:53:22.426,'17.0.10.5963',0,54,'DiskRead','Disk reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,55,'DiskReadHint','Disk read hints','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,56,'DiskReadHintPages','Disk read hint pages','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,52,'DiskReadIndInt','Disk index interior reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,53,'DiskReadIndLeaf','Disk index leaf reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,63,'DiskReadTable','Disk table reads','0'
2020-02-12 08:53:22.426,'17.0.10.5963',0,66,'DiskReadWorkTable','Disk work table reads','0'

Working server...

2020-02-12 08:30:39.808,'17.0.10.5963',0,392,'ApproximateCPUTime','Approximate CPU time used','.2028544'
...
2020-02-12 08:30:39.808,'17.0.10.5963',0,2,'BytesReceived','Bytes received by server','24501'
2020-02-12 08:30:39.808,'17.0.10.5963',0,3,'BytesReceivedUncomp','Bytes received after decompression','24501'
2020-02-12 08:30:39.808,'17.0.10.5963',0,4,'BytesSent','Bytes sent to client','11262'
2020-02-12 08:30:39.808,'17.0.10.5963',0,5,'BytesSentUncomp','Bytes sent before compression','11262'
2020-02-12 08:30:39.808,'17.0.10.5963',0,10,'CacheHits','Cache Hits','8914'
2020-02-12 08:30:39.808,'17.0.10.5963',0,16,'CacheRead','Cache reads','8914'
...
2020-02-12 08:30:39.808,'17.0.10.5963',0,43,'ConnCount','Number of active connections','3'
2020-02-12 08:30:39.808,'17.0.10.5963',0,393,'ConnectedTime','Total time connections have been connected','89.2171611900035'
...
2020-02-12 08:30:39.808,'17.0.10.5963',0,54,'DiskRead','Disk reads','191'
2020-02-12 08:30:39.808,'17.0.10.5963',0,55,'DiskReadHint','Disk read hints','146'
2020-02-12 08:30:39.808,'17.0.10.5963',0,56,'DiskReadHintPages','Disk read hint pages','222'
2020-02-12 08:30:39.808,'17.0.10.5963',0,52,'DiskReadIndInt','Disk index interior reads','10'
2020-02-12 08:30:39.808,'17.0.10.5963',0,53,'DiskReadIndLeaf','Disk index leaf reads','59'
2020-02-12 08:30:39.808,'17.0.10.5963',0,63,'DiskReadTable','Disk table reads','123'
2020-02-12 08:30:39.808,'17.0.10.5963',0,66,'DiskReadWorkTable','Disk work table reads','0'

[johnsmirnios]

What is in 'rroad_database_properties()'? Is it really just a select DB_PROPERTY( 'ConnCount' )? or is is calling one of the sa_.*_property() functions? If it is the latter, might it be returning properties for multiple databases (eg, including the utility_db)? Or maybe you are connected to the utility_db?

[Breck_Carter]

Sorry, wrong copy-and-paste, fixed it 🙂

[johnsmirnios]

Okay... sa_db_properties returns properties of all databases by default. The first column is the database number. Does the suspect server list properties for any other databases? Equivalently, how many rows does sa_db_list() return (since sa_db_properties uses sa_db_list() to get all db IDs)?

[Breck_Carter]

I don't have immediate access to the suspect server.

The suspect server has only one database.

Note that DB_PROPERTY ( 'ConnCount' ) returns '0'.

SQL Central also displays zero in the Server - Database tab - # Conn column.

I don't know how SQL Anywhere can return so many invalid property values without otherwise crashing.

This behavior started sometime between September 2018 (17.0.9.4803) and February 2020 (17.0.10.5963).

My feeling is the software should be reinstalled from scratch.

[Breck_Carter]

This not a bug, it is a feature!

The server -k option was introduced in SQL Anywhere 10, and one of the side effects is to turn off DB_PROPERTY ( 'ConnCount' ) and other stuff (although I haven't exhaustively tested it on V10 through 16, just 17.

Anyway, the client's gonna remove dbsrv17 -k

...and I'm gonna add code to Foxhound to send an Alert if the target database is running with -k.

[VolkerBarth]

and one of the side effects is to turn off DB_PROPERTY ( 'ConnCount' )

Seems to be an undocumented side effect, right?

(And another "Stealth mode" option:))

[Breck_Carter]

Here's where it gets really embarrassing: This behavior is documented in the Foxhound Help; go to this topic and click on "(What is INT: StmtPerfMngrConn?)" to see this...

You can eliminate the INT: StmtPerfMngrConn connection by using the dbsrv17 -k option, but that's a bad idea if you use Foxhound because it disables many important performance properties

[VolkerBarth]

Hm, I would not think of a mere "ConnCount" as an important performance property...

[Breck_Carter]

ConnCount isn't the only one disabled.

[VolkerBarth]

Yes, I'm aware, I was just trying to understand the docs and still are thinking that I would not consider "ConnCount" a performance property at all...:) But apparently, it is.