I have noticed a couple of errors in the server recently that I have never noticed before.  They take the format of Access denied to table of backup file 'query (62603)' where the logged on user thread id is 6260.  The numbers obviously change depending on the thread.

Anyone ever seen this before as I have never noticed it before if it has happened.  I do get some access denied errors though, but normally for names memory tables, not a table like this.

I have recently turned on index compression for one of my tables that has a large index but other than that I have not made any changes.

Anyone ever seen this sort of thing before?

Regards
David

On 3/22/2021 10:56 AM, David wrote:
> I have noticed a couple of errors in the server recently that I have never noticed before.  They take the format of Access denied to table of backup file 'query (62603)' where the logged on user thread id is 6260.  The numbers obviously change depending on the thread.
>
> Anyone ever seen this before as I have never noticed it before if it has happened.  I do get some access denied errors though, but normally for names memory tables, not a table like this.
>
> I have recently turned on index compression for one of my tables that has a large index but other than that I have not made any changes.
>
> Anyone ever seen this sort of thing before?

11013 is access denied and numeric files you are seeing are runtime
temporary tables for results (i.e. canned queries).

Most common reasons for this are
- permissions/access to the specified temp folder - in your case you're
using DBSRVR so make sure the "temp files directory" setting is properly
configured (from perspective of DBSRVR process and account it runs
service under)

- Anti-Virus is messing with the temp files

Raul

Hi Raul.

The bit that has me baffled is where it says Access denied to file 'query (62603)'  as if that is the temporary file name.  I have seen it mention temporary files before but it normally just references the random number, no mention of query (random number).

I did a bit of digging around and realised that this is a normal dbisam error, however I understood that temporary files were a random number, the issue I am having always has the ThreadID of the logged in user, then a random number appended, is this the way dbisam creates temporary files or am I experiencing something else?

On 3/22/2021 2:46 PM, David wrote:
> The bit that has me baffled is where it says Access denied to file 'query (62603)'  as if that is the temporary file name.  I have seen it mention temporary files before but it normally just references the random number, no mention of query (random number).

I did a quick search in DBISAM source and "'query' +'
('+FResultCursor.TableName+')' " is an error DBISAM would generate.

So in your case numeric table name indicates write or access problems to
temp folder when working with canned results

Raul

I can see some of the files still in the temporary folder even after the client has logged off.  Interestingly  not all of them are still there only a couple of them.  So with the files being there it would rule out a permissions problem, leading to the last option being an A/v?

Is it possible that this error is only raised when DBISan tries to delete the files and the A/V has access to the file at the time leading to this error?

Dam Virus checked.  We use Sentinel One and normally it is quite good as it should only be dealing with in memory processes, but I think sometimes it can check files as well.


Raul wrote:


So in your case numeric table name indicates write or access problems to
temp folder when working with canned results

Raul

David


As Raul said, permissions and AV. Most of the AV systems I've used will check a file when created, and if its a large file, can hold onto it longer than DBISAM can wait, hence the access denied error.

I think DBISAM has a facility for setting where temporary files (eg canned query output) go. I'd suggest setting that and excluding it from the AV's checks. Haven't used DBISAM for ages but I think it could be PrivateDir.

Roy Lambert

Hi Roy.

I have asked the people who manage Sentinel One to make sure the temp folder has been excluded and they say they have done so but unfortunately it does not seemed to have worked as I am getting more and more of these errors.

I did wonder if this could have something to do with me turning on Index Compression but I have seen the error appear from a user who would not use the table I turned this on for, so I think that rules this out.

I really don't know what is happening, never had this before and the DB server hasn't changed the only thing on the tables that have changed is index compression.  I have made minor changes to my application, but again, I have seen this error happening from a web app that hasn't changed.

Confused and baffled.

Regards
David.


Roy Lambert wrote:

David


As Raul said, permissions and AV. Most of the AV systems I've used will check a file when created, and if its a large file, can hold onto it longer than DBISAM can wait, hence the access denied error.

I think DBISAM has a facility for setting where temporary files (eg canned query output) go. I'd suggest setting that and excluding it from the AV's checks. Haven't used DBISAM for ages but I think it could be PrivateDir.

Roy Lambert

David


As I said I haven't used DBISAM for ages but my old manual has this

<<Temporary Files
If a SELECT SQL statement is generating a canned result set, a temporary table will be created in the directory
specified by the TDBISAMSession PrivateDir property for local sessions. If the query is being executed within a
remote session, the location of the temporary table for the canned result set will be determined by the database
server's configuration setting for the location of temporary tables, which can be modified remotely via the
TDBISAMSession ModifyRemoteConfig method or locally on the server via the TDBISAMEngine
ModifyServerConfig method. The TDBISAMQuery SessionName property determines what session is being used
for the execution of the SQL statement. Please see the DBISAM Architecture topic for more information.>>

Check that out and make sure the directory is excluded.Personally I'd move it away from the Windows temporary directory.

Roy Lambert

Hi Roy.

Yes I have the Temporary Files folder set on the server to a different location than Windows temp.  

It turns out this was the A/V causing the issue after all, although I asked for the temporary files folder and DB folder to be excluded from the A/V scans this did not bear fruit and I was still getting this error.  However later on I was allowed to disable the A/V to verify it was the cause, and low and behold, no more errors since that was turned off.

I found out that our security people had put in an a new version of the A/V 30 minutes before I started to notice errors yesterday.  I just need to work out how to get the A/V to work again without impacting DBISam.  It just wont be today.



Thanks Raul and Roy.
Regards
David

Roy Lambert wrote:

David


As I said I haven't used DBISAM for ages but my old manual has this

<<Temporary Files
If a SELECT SQL statement is generating a canned result set, a temporary table will be created in the directory
specified by the TDBISAMSession PrivateDir property for local sessions. If the query is being executed within a
remote session, the location of the temporary table for the canned result set will be determined by the database
server's configuration setting for the location of temporary tables, which can be modified remotely via the
TDBISAMSession ModifyRemoteConfig method or locally on the server via the TDBISAMEngine
ModifyServerConfig method. The TDBISAMQuery SessionName property determines what session is being used
for the execution of the SQL statement. Please see the DBISAM Architecture topic for more information.>>

Check that out and make sure the directory is excluded.Personally I'd move it away from the Windows temporary directory.

Roy Lambert