Access Denied for creating views from a backup

  • Resolved Spidergirl1979

    (@spidergirl1979)


    7 years ago

    I had some custom views (basically joining terms and taxonomy tables etc…) and I noticed it does have a drop view in the backup, the creation of the view doesn’t get executed. It comes back with “Access denied; you need (at least one of) the SUPER privilege(s) for this operation”

    If I go into the backup and tweak the SQL statement a bit (make it a simple create view as… instead of the create algorithm…) I can run it in PHPMyadmin without any access denied issues.

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author David Anderson

    (@davidanderson)

    7 years ago

    Hi,

    Please can you copy-and-paste examples of the failing lines, and the lines that you changed them to, so that we can understand the issue better?

    Many thanks,
    David

    • This reply was modified 7 years ago by David Anderson. Reason: subscribe to replies
    Thread Starter Spidergirl1979

    (@spidergirl1979)

    7 years ago

    Sure thing. The generated SQL was this:

    CREATE ALGORITHM=UNDEFINED DEFINER=cpses_jlzSPmfEaQ@localhostSQL SECURITY DEFINER VIEWwp_terms_vAS selecttr.object_idASobject_id,tr.term_orderASterm_order,tr.term_taxonomy_idASterm_taxonomy_id,tt.term_idASterm_id,tt.taxonomyAStaxonomy,tt.parentASparent,tt.descriptionASdescription,tt.countAScount,t.nameASname,t.slugASslug,t.term_groupASterm_groupfrom ((wp_term_relationshipstrjoinwp_term_taxonomytton((tr.term_taxonomy_id=tt.term_taxonomy_id))) joinwp_termston((t.term_id=tt.term_id))) ;

    and what I changed it to was this:

    CREATE VIEWwp_active_posts_vAS selectwp_posts.IDASIDfrom (wp_postsleft joinwp_postsp2on((wp_posts.post_parent=p2.ID))) where (((wp_posts.post_status<> ‘trash’) and (wp_posts.post_status<> ‘auto-draft’)) or ((wp_posts.post_status= ‘inherit’) and (p2.post_status<> ‘trash’) and (p2.post_status<> ‘auto-draft’))) group bywp_posts.ID;

    Plugin Author David Anderson

    (@davidanderson)

    7 years ago

    Thank you!

    Those two don’t look like they’re the same thing. Are those two different views?

    Thread Starter Spidergirl1979

    (@spidergirl1979)

    7 years ago

    Oh man, I’m sorry! I had two custom views and got them mixed up. Let’s try this again. 🙂

    The generated SQL was this:

    CREATE ALGORITHM=UNDEFINED DEFINER=cpses_jlzSPmfEaQ@localhost SQL SECURITY DEFINER VIEW wp_terms_v AS select tr.object_id AS object_id,tr.term_order AS term_order,tr.term_taxonomy_id AS term_taxonomy_id,tt.term_id AS term_id,tt.taxonomy AS taxonomy,tt.parent AS parent,tt.description AS description,tt.count AS count,t.name AS name,t.slug AS slug,t.term_group AS term_group from ((wp_term_relationships tr join wp_term_taxonomy tt on((tr.term_taxonomy_id = tt.term_taxonomy_id))) join wp_terms t on((t.term_id = tt.term_id))) ;

    and what I changed it to was this:

    CREATE VIEW wp_terms_v AS select tr.object_id AS object_id,tr.term_order AS term_order,tr.term_taxonomy_id AS term_taxonomy_id,tt.term_id AS term_id,tt.taxonomy AS taxonomy,tt.parent AS parent,tt.description AS description,tt.count AS count,t.name AS name,t.slug AS slug,t.term_group AS term_group from ((wp_term_relationships tr join wp_term_taxonomy tt on((tr.term_taxonomy_id = tt.term_taxonomy_id))) join wp_terms t on((t.term_id = tt.term_id))) ;

    Plugin Author David Anderson

    (@davidanderson)

    7 years ago

    Thank you. We’ve added an item for investigating this to our internal task-tracking system.

    David

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Access Denied for creating views from a backup’ is closed to new replies.