MySQL General Query log filled with alf_transaction queries

[dkeidel]

I turned on MySQL general logging to watch the logs when I poke around Alfresco.  I am finding that the log is filled with queries such as the following:

  17 Query select

            txn.id              as id,

            txn.version         as version,

            txn.change_txn_id   as change_txn_id,

            txn.commit_time_ms  as commit_time_ms

        from

            alf_transaction txn

        where

            txn.commit_time_ms =

            (

                select max(commit_time_ms) from alf_transaction

                 where commit_time_ms <= 1536104592302

            )

  18 Query select

            txn.id              as id,

            txn.version         as version,

            txn.change_txn_id   as change_txn_id,

            txn.commit_time_ms  as commit_time_ms

        from

            alf_transaction txn

        where

            txn.commit_time_ms =

            (

                select max(commit_time_ms) from alf_transaction

                 where commit_time_ms <= 1536104592302

            )

  18 Query commit

  17 Query commit

  18 Query select @@session.tx_read_only

  18 Query set session transaction read write

  17 Query select @@session.tx_read_only

  18 Query select @@session.tx_read_only

  18 Query rollback

  17 Query set session transaction read write

  18 Query SET autocommit=1

  17 Query select @@session.tx_read_only

  17 Query rollback

  17 Query SET autocommit=1

  17 Query SET autocommit=0

  17 Query set session transaction read only

  17 Query select

            txn.id              as id,

            txn.commit_time_ms  as commit_time_ms,

            count(case when node.type_qname_id != 427 then 1 end) as updates,

            count(case when node.type_qname_id = 427 then 1 end) as deletes

        from

            alf_transaction txn

        join alf_node node on (txn.id = node.transaction_id)

         WHERE txn.commit_time_ms >= 1535095946411

               and txn.commit_time_ms < 1535124746411

        group by txn.commit_time_ms, txn.id

        order by txn.commit_time_ms ASC, txn.id ASC

  18 Query SET autocommit=0

  17 Query select

            max(commit_time_ms)

        from

            alf_transaction

  18 Query set session transaction read only

  17 Query select

            txn.id              as id,

            txn.version         as version,

            txn.change_txn_id   as change_txn_id,

            txn.commit_time_ms  as commit_time_ms

        from

            alf_transaction txn

        where

            txn.commit_time_ms =

            (

                select max(commit_time_ms) from alf_transaction

                 where commit_time_ms <= 1536104592305

            )

  18 Query select

            txn.id              as id,

            txn.commit_time_ms  as commit_time_ms,

            count(case when node.type_qname_id != 427 then 1 end) as updates,

            count(case when node.type_qname_id = 427 then 1 end) as deletes

        from

            alf_transaction txn

        join alf_node node on (txn.id = node.transaction_id)

         WHERE txn.commit_time_ms >= 1535070746411

               and txn.commit_time_ms < 1535074346411

        group by txn.commit_time_ms, txn.id

        order by txn.commit_time_ms ASC, txn.id ASC

Does anyone know what Alfresco is attempting to do or is doing by executing these queries?

[afaust]

This is part of the index tracking of SOLR / Alfresco Search Services. It checks on a regular interval for updates in the database to reindex, and does so by checking for alf_transaction entries, which are used to group updates to nodes (documents / folders).

[dkeidel]

Thank you Axel Faust.  I am trying to look for queries in MySQL that are specific to the actions I am performing such as adding a document and these queries are fillin gup the general query log.  I need to figure out a way to suppress these regular interval queries so that I can see only the ones of interest.  Is there a way in Alfresco or MySQL to do that?

[afaust]

Not sure about MySQL - I rarely ever use that anymore. The only way in Alfresco to suppress these queries would be to disable index tracking in SOLR (or not use SOLR at all).