How to fix an "Unable to find the Primary Key for tablename" error displayed after adding a query monitor?
After adding a Database Query Monitor, users must select and update a Primary Key for each table. The primary key can be a single column or a combination of multiple columns, but it must uniquely identify each row in the result set.
Keep in mind:
- Primary keys must be unique for every row.
- Columns created using aggregate functions such as AVG, SUM, MIN, MAX, or COUNT cannot be used as primary keys, as these values are computed and not row-specific.
- Count-based columns (results of COUNT operations) are also ineligible since they are not unique per row.
This ensures accurate row identification and consistent monitoring results.
How to fix automatically removed thresholds or actions?
If you notice that the thresholds or actions you configured are getting removed automatically—especially for new rows in a Database Query Monitor or Script Monitor—it’s likely because those new rows didn’t inherit the existing thresholds.
To fix this:
- Use the Configure Alarm Template option available at the top of each table. This ensures that any threshold or action you configure is applied as a template.
- Go to Settings → Performance Polling → Optimize Data Collection → choose Script/Database Query monitor as the monitor type, and select Enable Configure Alarm Template for Database Query Monitor and Script Monitor then save.
- Once enabled, thresholds and actions set through the alarm template will automatically apply to any newly discovered rows during polling, preventing manual reconfiguration and ensuring consistent alerting.
How to add stored procedure in query monitor?
- To monitor stored procedures (like queries that begin with EXEC, SHOW, etc.) in database query monitor enable performance polling option.
- To Enable Stored Procedures, go to Settings → Performance Polling → Optimize Data Collection → Script / Database Query Monitor and select Enable Stored Procedure Monitoring for Database Query Monitor.
When using aggregate functions like COUNT, MIN, MAX, etc., in your Database Query Monitor, it's important to ensure each row has a unique identifier. Since aggregate functions return summarized data (e.g., totals), they lack a natural primary key, which is required for Applications Manager to track and display results effectively.
Recommended Approach: Use a Dummy Column as Primary Key
Let’s say you want to monitor the number of pending tasks in your application. These tasks are stored in a table named app_pending_tasks.
A typical query might look like this:
SELECT COUNT(*) FROM app_pending_tasks
However, this lacks a unique identifier and will not work correctly in Applications Manager. Instead, modify the query to include a static/dummy column:
- For MySQL, Oracle, and Sybase:
SELECT 'Number of Pending Tasks', COUNT(*) AS totalcount FROM app_pending_tasks
- For Kingbase, MS SQL, PostgreSQL, and Db2:
SELECT 'Number of Pending Tasks' AS NumberOfPendingTasks, COUNT(*) AS totalcount FROM app_pending_tasks
This method ensures that the result row has a unique and recognizable identifier for Applications Manager to track.
Common Errors to Avoid
- Multiline Queries:
Each query should be written in a single line. Writing across multiple lines causes each line to be treated as a separate query. - Semicolons:
Avoid ending queries with a semicolon (;), especially when working with Oracle databases. - Quoted Values in MS SQL:
When including quoted values (e.g., 'APM.API.0101') in your query, you must provide an alias (For example: SELECT TOP 1 CreateTime, UserId, 'APM.API.0101' AS ColumnName FROM TempTable).