Query executed in liferay-6.2 database

[Jenny Katsada，修改在7 年前。]

Hello everyone!

Using third party software (jTDS) a query was found to be executed at least 70000 times per day:

select
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = convert(sysname,o.name),
TABLE_TYPE = convert(varchar(32),
rtrim(substring('SYSTEM TABLE TABLE VIEW ',
(ascii(o.type)-83)*12+1,
12)) -- 'S'=0,'U'=2,'V'=3

),
REMARKS = convert(varchar(254),null) -- Remarks are NULL.

from
sys.all_objects o
where
o.type in ('S','U','V') and
has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),
'object',
'select') = 1 and
charindex(substring(o.type,1,1),@type1) <> 0 and -- Only desired types.

(@table_name is NULL or o.name like @table_name) and
(@table_owner is NULL or schema_name(o.schema_id) like @table_owner)
order by 4,
1,
2,
3

Does anyone know what does this query do? I'm certain it's not something we did, is it some kind of liferay automated procedure?

[David H Nebinger，修改在7 年前。]

Jenny Katsada:

Does anyone know what does this query do? I'm certain it's not something we did, is it some kind of liferay automated procedure?

I disagree with that. I did a quick search for "has_perms_by_name" and "schema_name" and found zero hits, so I don't think this is any liferay automated query.

Also Liferay doesn't support schemas in any of the queries generated by service builder, so this pushes the chances of it being a liferay generated query down further.

Do you have some sort of healthcheck or something that you've created to verify table availability?

[Jenny Katsada，修改在7 年前。]

Using the program ExpressProfiler I traced that particular query as a stored procedure with the name sys.sp_tables. I still don't know why it is called so many times, but I still think it is somehow automatically called. By hibernate maybe, if not liferay?

[David H Nebinger，修改在7 年前。]

Liferay doesn't go after the db metadata all that often, if at all. And if this was a hibernate issue, there would be all kinds of reported issues online related to it.

I still lean towards a self-inflicted issue, but I'm not sure how you're going to trace the call stack for a sproc...