Persistent Database Upload Error

  • Resolved PSS

    (@airtasker6)


    2 years, 11 months ago

    Hi,

    We are trying to upload our SQL database from a locahost development site to our web host online via cPanel’s phpmyadmin and we keep running into errors related to the tables for the automator plugin. First it was requiring super user privileges and when we finally fixed that another error has popped up.

    CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW wp_uap_action_logs_view  AS SELECT a.automator_action_id AS automator_action_id, a.date_time AS action_date, a.completed AS action_completed, a.error_message AS error_message, a.automator_recipe_id AS automator_recipe_id, a.ID AS action_log_id, a.automator_recipe_log_id AS recipe_log_id, r.date_time AS recipe_date_time, r.completed AS recipe_completed, r.run_number AS recipe_run_number, pa.post_title AS action_title, am.meta_value AS action_sentence, p.post_title AS recipe_title, u.ID AS user_id, u.user_email AS user_email, u.display_name AS display_name FROM (((((wp_uap_action_log a left join wp_uap_recipe_log r on(a.automator_recipe_log_id = r.ID)) left join wp_posts p on(p.ID = a.automator_recipe_id)) join wp_posts pa on(pa.ID = a.automator_action_id)) left join `wp_ua[...]

#1054 - Unknown column 'a.ID`ID' in 'group statement'

    What is the reason for this and how do we fix this?

Viewing 8 replies - 1 through 8 (of 8 total)
  • Thread Starter PSS

    (@airtasker6)

    2 years, 11 months ago

    To add to my comments above, the tables that were imported into the online database did not include any tables from ‘Views’ even though all of the tables from the ‘Tables’ and ‘Views’ were exported from our locahost development site.

    Re: submitting tickets to Automator, I will use the email address from now.

    • This reply was modified 2 years, 11 months ago by PSS.
    Thread Starter PSS

    (@airtasker6)

    2 years, 11 months ago

    Here’s the full error message:

    CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW wp_uap_action_logs_view  AS SELECT a.automator_action_id AS automator_action_id, a.date_time AS action_date, a.completed AS action_completed, a.error_message AS error_message, a.automator_recipe_id AS automator_recipe_id, a.ID AS action_log_id, a.automator_recipe_log_id AS recipe_log_id, r.date_time AS recipe_date_time, r.completed AS recipe_completed, r.run_number AS recipe_run_number, pa.post_title AS action_title, am.meta_value AS action_sentence, p.post_title AS recipe_title, u.ID AS user_id, u.user_email AS user_email, u.display_name AS display_name FROM (((((wp_uap_action_log a left join wp_uap_recipe_log r on(a.automator_recipe_log_id = r.ID)) left join wp_posts p on(p.ID = a.automator_recipe_id)) join wp_posts pa on(pa.ID = a.automator_action_id)) left join wp_uap_action_log_meta am on(a.automator_action_id = am.automator_action_id and am.automator_action_log_id = a.ID and am.user_id = a.user_id and am.meta_key = 'sentence_human_readable_html')) left join wp_users u on(a.user_id = u.ID)) GROUP BY a.IDID;

    I have tried creating a view using Query but the error is still the same:
    “#1054 - Unknown column 'a.ID`ID' in 'group statement'“

    Plugin Contributor Uncanny Automator

    (@uncannyautomator)

    2 years, 11 months ago

    Hi @airtasker6 ,

    Thanks for the additional details. One of our developers had a look and had a few notes for you:

    • We would like you to visit Automator > Status > Tools, and from there drop and recreate the views. Even if you had been able to migrate views, they would no longer be valid.
    • It is possible that your user doesn’t have permissions to create a view (in relation to the original post), but that might be better confirmed by the host.
    • Our developer is concerned by your last reference, as it should be “GROUP BY a.ID;”; the second “ID” should not exist. Is there any chance something was modified?
    • If none of the above helps, our team wondered if you could submit a system report (Automator > Status) to our Help Desk or via email rather than here, since there is information about the site in it.

    Thanks

    Thread Starter PSS

    (@airtasker6)

    2 years, 11 months ago

    Hi @uncannyautomator

    1. Done that and tried re-upload with same result and error.
    2. We were originally using our website’s cPanel account to upload the database but have since been using phpmyadmin from WHM as root (which got rid of the first error requiring Super User privileges).
    3. Nothing has been modified. All we’ve done is export the database from locahost then import to the online database as above.
    4. I will send through the System Report via email.
    Thread Starter PSS

    (@airtasker6)

    2 years, 11 months ago

    Hi @uncannyautomator

    As I have sent the System Report, I am attaching our convo from emails here as we prefer to keep all info in one place.

    ____________________

    Hi Ryan,

    There is no migrated site because we can’t or don’t want to use the remaining tables without the complete database. We don’t want to be messing around with the database on a live site. We want this to function properly from the get-go and this is the only issue we are facing which is prolonging the launch of our website.

    So yes, the Automator System Report is from the localhost development site.

    > It sounds too like this was an entire database migration and Automator views specifically are the only things that failed?
    As you can see from the error, yes this is the case. Automator is the only plugin causing an issue with the database upload.

    ——- Original Message ——-
    Uncanny Automator Support wrote:

    The System Report you sent here seems like maybe it was the original site, or do you have cPanel and WHM running locally too? This instance doesn’t show obvious issues, so I wanted to confirm that this is the system report for the migrated site.

    Then for the .org ticket comments, I wanted to confirm that your #1 means you did that post migration (since you mentioned “re-upload” I wasn’t sure). It sounds too like this was an entire database migration and Automator views specifically are the only things that failed?

    Thanks

    – Ryan @ Uncanny Owl

    Plugin Contributor Uncanny Automator

    (@uncannyautomator)

    2 years, 11 months ago

    Hi @airtasker6 ,

    I did want to update here that we did reply via the Help Desk with a request for additional information. As the reply may include information about your site, we haven’t posted an update here, but please let us know if you didn’t receive the reply.

    Thanks

    Thread Starter PSS

    (@airtasker6)

    2 years, 11 months ago

    Hi Ryan,

    I have received your message and will get back to you once I have had a chance to try out the suggested method.

    Plugin Contributor Uncanny Automator

    (@uncannyautomator)

    2 years, 11 months ago

    We will go ahead and mark this as closed. @airtasker6 followed up on our Help Desk with the following update:

    Circling back to your email below with the suggestions, I am glad to say that the suggestions have worked out. I managed to upload the database successfully and re-install the Automator plugins on the server site as recommended. 🥳

    P.S. The support provided by Uncanny Automator has been awesome.

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘Persistent Database Upload Error’ is closed to new replies.