problem about mysql-error-1142-Collection of common programming errors

• 
  jhericks
  mysql stored-procedures mysql-error-1142
  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

Originally posted 2013-11-06 03:17:59.