I have a table called sales_observation_daily_summary which is a materialized view of sales_observation_daily_summary_view. I have defined a stored procedure called sync_daily_summary_view_with_table that will refresh the materialized view. Functionally, it works exactly as I expect. However, I have a strange bug when calling the stored procedure twice on the same connection (a likely scenario when using a connection pool). Originally this came up in my Java integration tests, but I am able to reproduce it on MySQL Workbench easily, so it shouldn't have anything to do with JDBC or Spring or anything in the middle like that.
call sync_daily_summary_view_with_table();
call sync_daily_summary_view_with_table();
On the first call, it does what it's supposed to do and returns normally. On the second call, I get:
Error Code: 1142
SELECT command denied to user 'test'@'localhost' for table 'one_pg_someone_sales_observation_daily_summary_view'
one_pg_someone_sales_observation_daily_summary_view is referenced in sales_observation_daily_summary_view, which is referenced in the stored procedure. The error message makes no sense as first of all, stored procedure did not object the first time it was run and second of all, that user has plenty of rights to select on that view.
I won't show all of the views involved as it is very complex, but the sales_observation_daily_summary_view is defined as a union of several other views thus:
CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`localhost`
SQL SECURITY DEFINER
VIEW `sales_observation_daily_summary_view` AS
/* Specific Stage and Observer */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM one_pg_someone_sales_observation_daily_summary_view
UNION ALL /* All Stages */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM all_stages_someone_sales_observation_daily_summary_view
UNION ALL /* All Activities */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM all_activities_someone_sales_observation_daily_summary_view
UNION ALL /* All Observers */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM one_pg_everyone_sales_observation_daily_summary_view
UNION ALL /* Everyone over All Stages */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM all_stages_everyone_sales_observation_daily_summary_view
UNION ALL /* Everyone over All Activities */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM all_activities_everyone_sales_observation_daily_summary_view
UNION ALL /* Benchmark */
SELECT zone,
session_date,
session_year,
session_month,
session_week,
phenomenon_group_id,
phenomenon_group_name,
stage_id,
stage_name,
observer_id,
series_name,
benchmark_id,
session_count,
session_value,
benchmark_value
FROM benchmark_sales_observation_daily_summary_view
The stored procedure is defined thus:
DELIMITER $$
CREATE DEFINER=`test`@`localhost` PROCEDURE `sync_daily_summary_view_with_table`()
BEGIN
/* Update any values that may have changed */
UPDATE sales_observation_daily_summary tb,
sales_observation_daily_summary_view vw
SET tb.session_count = vw.session_count,
tb.session_value = vw.session_count,
tb.benchmark_value = vw.benchmark_value,
tb.series_name = vw.series_name
WHERE vw.zone = tb.zone
AND vw.session_date = tb.session_date
AND Coalesce(vw.phenomenon_group_id, 0) =
Coalesce(tb.phenomenon_group_id, 0)
AND Coalesce(vw.stage_id, 0) = Coalesce(tb.stage_id, 0)
AND Coalesce(vw.observer_id, 0) = Coalesce(tb.observer_id, 0)
AND Coalesce(vw.benchmark_id, 0) = Coalesce(tb.benchmark_id, 0)
AND ( Coalesce(tb.session_count, -1) <> Coalesce(vw.session_count, -1)
OR Coalesce(tb.session_value, -1) <>
Coalesce(vw.session_value, -1)
OR Coalesce(tb.benchmark_value, -1) <>
Coalesce(vw.benchmark_value, -1)
OR tb.series_name <> vw.series_name );
END
I am using version 5.1.56-log on my local development box.
UPDATE 1
I have also reproduced the error on an Amazon RDS server version 5.1.57-log.
UPDATE 2
If I define the stored procedure to be SQL SECURITY INVOKER
and execute it as root, it works fine. This is not an acceptable workaround, but it might be some kind of clue. (e.g. It is not a table locking issue.
UPDATE 3 The tables involved are InnoDB tables. I'm not sure if it's a clue, but when I added a Start Transaction at the beginning and a Commit at the end, it took much longer to complete, but then got the same error on the second invocation.
UPDATE 4 I have simplified the stored procedure and still reproduced the problem. It used to have an insert statement followed by an update statement. It turns out that the update statement is sufficient to reproduce the error, so I removed the insert statement from the stored procedure above.
What is the value of autocommit? select @@autocommit;
If the value is 0 try to add a commit between the two calls as you could have an open transaction somehow call sync_daily_summary_view_with_table(); commit; call sync_daily_summary_view_with_table();
Is the materialized table part of any of the views?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With