[DB Auto Maintainer] SELECT command denied to user 'OSRUNTIME'@'xxx' for table 'tables'

[DB Auto Maintainer] SELECT command denied to user 'OSRUNTIME'@'xxx' for table 'tables'

  
Forge Component
(4)
Published on 6 Apr by Hanno
4 votes
Published on 6 Apr by Hanno

We're getting an error like this with MySQL:

Message:Timer DatabaseSizeEmail error (inside action 'DatabaseSizeEmail'). Timer duration = 0 secs:Error in advanced query SQL1 in DatabaseSizeEmail in DBAutoMaintainer (SELECT    0 AS Id,   t.NAME AS TableName,   (SUM(a.total_pages) * 8) AS TotalSpaceKB,    (SUM(a.used_pages) * 8)  AS UsedSpaceKB,    (SUM(a.data_pages) * 8)  AS DataSpaceKB,   @InGuid AS [Guid]  FROM    sys.tables t  INNER JOIN     sys.indexes i ON t.OBJECT_ID = i.object_id  INNER JOIN    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  INNER JOIN    sys.allocation_units a ON p.partition_id = a.container_id  WHERE    t.NAME NOT LIKE 'dt%' AND   i.OBJECT_ID > 255 AND     i.index_id <= 1  GROUP BY    t.NAME, i.object_id, i.index_id, i.name   HAVING SUM(a.total_pages) >= @InMinTableSizeKB  ): SELECT command denied to user 'OSRUNTIME'@'xxxx' for table 'tables' [retry 2 of 3 scheduled]
Environment Information
Stack:OutSystems.HubEdition.RuntimePlatform.DataBaseException: Error in advanced query SQL1 in DatabaseSizeEmail in DBAutoMaintainer (SELECT    0 AS Id,   t.NAME AS TableName,   (SUM(a.total_pages) * 8) AS TotalSpaceKB,    (SUM(a.used_pages) * 8)  AS UsedSpaceKB,    (SUM(a.data_pages) * 8)  AS DataSpaceKB,   @InGuid AS [Guid]  FROM    sys.tables t  INNER JOIN     sys.indexes i ON t.OBJECT_ID = i.object_id  INNER JOIN    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  INNER JOIN    sys.allocation_units a ON p.partition_id = a.container_id  WHERE    t.NAME NOT LIKE 'dt%' AND   i.OBJECT_ID > 255 AND     i.index_id <= 1  GROUP BY    t.NAME, i.object_id, i.index_id, i.name   HAVING SUM(a.total_pages) >= @InMinTableSizeKB  ): SELECT command denied to user 'OSRUNTIME'@'10.69.4.244' for table 'tables'
   at ssDBAutoMaintainer.Actions.FuncActionDatabaseSizeEmail.QuerySQL1(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, String qpstInGuid, Int32 qpinInMinTableSizeKB)
   at ssDBAutoMaintainer.Actions.ActionDatabaseSizeEmail(HeContext heContext, String inParamInEmailAddresses)
   at ssDBAutoMaintainer.TimerDatabaseSizeEmail.Execute(HeContext heContext, Int32 timeout)

Any ideas how to fix that?



Br,

Toni


Hi Toni

The timer needs to run under a user account with elevated permissions.

Are you running this in a personal environment?

If not, it is preferable to use a service account for this purpose.

Regards
Hanno

I haven't set up a service account for the timer, but even if I would do that wouldn't the database query still be ran under OSRUNTIME database user? That user does not have access to table called "tables" which actually does not exist in my Outsystems schema?

I have run this component without issue in a personal and enterprise environments (cloud and self-hosted).

Can you perhaps elaborate on your environment setup? Perhaps there is something out of the ordinary? My guess is the DBAs limited the access of the OSRUNTIME user in your environment.

The component uses an advanced SQL query to determine the database size. The tables need not be part of your OutSystems schema.

We are using MySQL. Did you test on that?

Also, I previously meant that the table sys.tables does not exist.

Br,

Toni

Thank you, Toni

I have not tested on MySQL and it then makes sense that it does not work.

I will need to update the version listing to make it MSSQL specific.

There is obviously also a need to cater for a MySQL version specifically.

If you are technically-minded, you could replace the SQL1 query in the DatabaseSizeEmail server action with the following:


SELECT table_schema as `Database`, table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE (data_length + index_length) > @InMinTableSizeKB
ORDER BY (data_length + index_length) DESC;


Hanno

Thanks Hanno!

I'm a bit puzzled since the output structure of the query above does not match the current? For example, the InGuid is not there. Why is this? Am I looking at the correct query?

Current query below:

SELECT 
 0 AS Id,
 t.NAME AS TableName,
 (SUM(a.total_pages) * 8) AS TotalSpaceKB, 
 (SUM(a.used_pages) * 8)  AS UsedSpaceKB, 
 (SUM(a.data_pages) * 8)  AS DataSpaceKB,
 @InGuid AS [Guid]
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
HAVING SUM(a.total_pages) >= @InMinTableSizeKB

Apologies for that, Toni.

I don't have an active MySQL installation to verify my query at the moment, but the following should suffice:

SELECT 
0 as Id,
table_name AS `TableName`, 
round(((data_length + index_length) / 1024 ), 2) AS `TotalSpaceKB`,
0 AS `UsedSpaceKB`,
round((data_length / 1024),2) AS `DataSpaceKB`,
@InGuid AS `Guid`
FROM 
information_schema.TABLES 
WHERE 
(data_length + index_length) > @InMinTableSizeKB
ORDER BY 
(data_length + index_length) DESC;