Azure Database for MySQL single server Log queries fail

Question

I'm trying to run a query against the logs for our Mysql db. I navigate to Monitoring | Logs . Then any attempt to run any of the default queries fails with some sort of syntax error.

For example:

// Execution time exceeding a threshold
// Identify queries that their run time exceeds 10 seconds.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s, ResourceId
| where query_time_d > 10 //You may change the time threshold

Fails with this error:

'project' operator: Failed to resolve scalar expression named 'event_class_s'
If the issue persists, please open a support ticket. Request id: 14fa978a-8332-4968-a914-2d9accf13cfe

Answer 1

Hi, @John Robinson The error means, there’s no data in the logs yet. If the logs were enabled just before executing this, it can take a few minutes for the generated logs to arrive in log analytics to be queried.
It could also mean that, even if log data exists, there are no data in the column ‘event_class_s’. Some of the logs may not have some columns in the data.
You could customize the KQL query by removing this column from the project or comment the project line to get all columns and then filter to what data you need.

Examples:
• Projecting specific columns from the data, removing ‘event_class_s’
// Execution time exceeding a threshold
// Identify queries that run time exceeds 10 seconds.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, start_time_t , query_time_d, sql_text_s, ResourceId
| where query_time_d > 10 //You may change the time threshold

• Projecting all columns

// Execution time exceeding a threshold
// Identify queries that run time exceeds 10 seconds.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlSlowLogs'
//| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s, ResourceId
| where query_time_d > 10 //You may change the time threshold

I hope this information helps, Please let us know if any further queries.

Regards
Geetha

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.