Unable to use stored procedure as datasource (mySQL) #89
Comments
|
Could you please paste your (sanitized as needed) query-exporter config? |
|
Closing this as it's incomplete. Please reopen providing the required info. |
|
This issue is real, for example this is my case: databases:
prod_tracking_db:
dsn: env:CONNECTION_STRING_PROD_TRACKING_DB
metrics:
link_clicks:
type: gauge
queries:
password_mail_tracking_query:
interval: 5m
databases: [prod_tracking_db]
metrics: [mail_opens, link_clicks]
sql: >
CALL get_clicks_and_opens_for_mail_types("'id_14'", "1 day"); |
|
Could you please provide an example of a store procedure that would return the expected data? Doesn't have to be the real one, a simpler reproducer would be enough |
|
Table for today's date: CREATE TABLE `link_open_2022_02_10` (
`id` char(36) NOT NULL,
`link` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`mail_id` char(36) NOT NULL,
`mail_type` varchar(255) DEFAULT NULL,
`user_id` int NOT NULL,
`user_agent` varchar(255) NOT NULL,
`utm_source` varchar(255) DEFAULT NULL,
`utm_content` varchar(255) DEFAULT NULL,
`utm_campaign` varchar(255) DEFAULT NULL,
`utm_medium` varchar(255) DEFAULT NULL,
`opened_at` datetime NOT NULL,
`data` json DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `INDEX_MAIL_LINK_ID` (`mail_id`),
KEY `INDEX_MAIL_TYPE` (`mail_type`),
KEY `INDEX_USER_ID` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `mail_open_2022_02_10` (
`id` char(36) NOT NULL,
`mail_id` char(36) NOT NULL,
`mail_type` varchar(255) DEFAULT NULL,
`user_id` int NOT NULL,
`user_agent` varchar(255) NOT NULL,
`opened_at` datetime NOT NULL,
`data` json DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `INDEX_MAIL_ID` (`mail_id`),
KEY `INDEX_MAIL_TYPE` (`mail_type`),
KEY `INDEX_USER_ID` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE DEFINER=`dbuser`@`%` PROCEDURE `get_clicks_and_opens_for_mail_types`(
IN type_id_list VARCHAR(255),
IN timeframe VARCHAR(50)
)
BEGIN
SET @date := DATE_FORMAT(now(), "%Y_%m_%d");
SET @link_open_table := CONCAT('link_open_', @date);
SET @mail_open_table := CONCAT('mail_open_', @date);
SET @query := CONCAT('SELECT (SELECT COUNT(distinct user_id) FROM ', @link_open_table, ' WHERE opened_at > now() - INTERVAL ', timeframe ,' AND mail_type IN (', type_id_list, ')) as link_opens, (SELECT COUNT(distinct user_id) FROM ', @mail_open_table, ' WHERE opened_at > now() - interval ', timeframe , ' AND mail_type IN (', type_id_list, ')) as mail_opens;');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END |
|
What version of mysql are you using? I'm trying to create a similar stored procedure but I get the following error: Also, to return data from the procedure, don't you need to define an |
|
DB Version: I am calling it that way: Getting the results outputted directly without assigning it to any custom variable - it's like a regular select returning rows |
|
@albertodonato any progress on this? is there any chance to have a fix? |
|
I am having a similar issue with Percona 5.7.26. Trying to use query_exporter to pass table or columns as parameters doesn't work, so created a procedure to do it for me. Procedure definition: ERROR: |
|
Any update on the above? I am also getting the same error when trying to use a stored procedure as a data source. Surely this should be possible, as you might want to have complex logic when querying data, which is not achievable with standard query functionality. |
|
I have tried drastically simplifying just to see if I can find when a stored proc will work. Some google searches suggest that maybe it's about closing cursors and reading the resultset. |
|
Found this on the googles. |
Describe the bug
I have a very complex query that needs to be executed to gather data from multiple tables using many different IFs in a query with variables.
If I put
CALL procedure_nameas a query, and this stored procedure returns the data I need to have a metric I'm getting an error:Seems like SQLAlchemy doesn't want to recognize SP resultset as valid data.
Installation details
The text was updated successfully, but these errors were encountered: