Blog Template - Insertion Error at site creation

I have successfully created hundreds of sites using the New Blog Template. Our business has been on stall for a few months and we are getting ready to re-open. I tried creating a site today and got the following error:

"Insertion Error: Table 'fg9aw0px_ueoprd.uecom_607_affiliatereferrers' doesn't exist - The template was not applied. (New Website Templates - With CREATE TABLE query for Additional Tables)"

The blog template settings includes the affiliatereferrers table.

A site does get created, however, the major problem I noticed is that our Gravity Forms are not copied over (which they previously have been created via the blog template.

I have provided access.

  • Predrag Dubajic

    Hi @antKat,

    Sorry for the delay, I did create new site for testing yesterday however I wanted to do some further tests on my installation before responding.
    Unfortunately I was unable to replicate the issue you are having, with Pro Sites, Affiliates and Gravity forms activated I don't see the error and my forms are transferred normally.

    I have flagged this thread for our second level support dev guys to have another look at this and see if they have any ideas.

    Best regards,
    Predrag

  • antKat

    I have the same problem on my test site ueo-test.com
    On the test site I have deactivated ALL plugins except New Blog Templates, Pro Sites, Gravity Forms, WPMU dashboard and MU plugins and I am still getting the insertion error "Insertion Error: Table 'fxk1tbz8_ueotest1.uecom_617_affiliatereferrers' doesn't exist - The template was not applied. (New Blog Templates - With CREATE TABLE query for Additional Tables)" and the site that gets created does not have the Gravity Forms from template

  • Predrag Dubajic

    Hi antKat,

    Is your test site running on the same server?

    Could you provide us with FTP and cPanel access to test site so we can investigate this further?

    You can send us your details using our contact form https://premium.wpmudev.org/contact/ and the template below:

    IMPORTANT: Make sure you select "I have a different question" for your topic so it doesn't go back to forums - this and the subject line ensure that it gets assigned to me.

    Subject: "Attn: Predrag Dubajic"
    - WordPress admin username
    - WordPress admin password
    - login url
    - FTP credentials (host/username/password)
    - cPanel credentials (host/username/password)
    - link back to this thread for reference
    - any other relevant urls

    Best regards,
    Predrag

  • wp.network

    @@Raelene MoreyPredrag Dubajic I am working as a consultant with @antKat on these projects...

    We have recently migrated to new production server - we have maintained the legacy server in part so that WPMUDEV will have an environment to look at that is not quite so sensitive as a production system... in addition to our current production and staging networks at current production server, we are also seeing this unexpected behavior at legacy network/server.

    I have sent in creds to Predrag with IP and other details on this...

    * I edited my local host file to use the legacy VPS IP address
    * created new site via network admin dashboard with site ID 605 (max-newsite-test)
    * got an error message:

    Warning: array_diff(): Argument #1 is not an array in /home/usingess/public_html/wp-content/plugins/pro-sites/pro-sites-files/modules/premium-plugins-manager.php on line 322
    Insertion Error: Table 'usingess_migr8a.uecom_605_affiliatereferrers' doesn't exist - The template was not applied. (New Website Templates - With CREATE TABLE query for Additional Tables)

    * Similar to what we are now seeing at new server, there are many 'missing' tables beyond the one named in the error message - including the gravity forms tables

    for instance (and similar to what happened for max-newsite-test at legacy system), the wpmutest subsite at current production system created by WPMUDEV (site ID 608) has the following tables:

    uecom_608_affiliatedata
    uecom_608_affiliaterecords
    uecom_608_commentmeta
    uecom_608_comments
    uecom_608_links
    uecom_608_options
    uecom_608_postmeta
    uecom_608_posts
    uecom_608_terms
    uecom_608_term_relationships
    uecom_608_term_taxonomy

    while the last know good example of a new site creation (tinkessentialoils, Site ID 602) shows the following tables:

    uecom_602_affiliatedata
    uecom_602_affiliaterecords
    uecom_602_affiliatereferrers
    uecom_602_agm_maps
    uecom_602_app_appointments
    uecom_602_app_cache
    uecom_602_app_exceptions
    uecom_602_app_services
    uecom_602_app_transactions
    uecom_602_app_workers
    uecom_602_app_working_hours
    uecom_602_commentmeta
    uecom_602_comments
    uecom_602_et_social_stats
    uecom_602_gf_addon_feed
    uecom_602_links
    uecom_602_mn_form_data
    uecom_602_options
    uecom_602_postmeta
    uecom_602_posts
    uecom_602_rg_form
    uecom_602_rg_form_meta
    uecom_602_rg_form_view
    uecom_602_rg_incomplete_submissions
    uecom_602_rg_lead
    uecom_602_rg_lead_detail
    uecom_602_rg_lead_detail_long
    uecom_602_rg_lead_meta
    uecom_602_rg_lead_notes
    uecom_602_taxonomymeta
    uecom_602_terms
    uecom_602_term_relationships
    uecom_602_term_taxonomy

    I don't think that there is any issue w/ corruption of Gravity Forms metadata - I think that the issue is likely something to do w/ New Blog Templates + Pro Sites (plugin module) such that many tables are not being created properly, including the Gravity Forms tables.

    Cheers, Max

    edit: ...also, we are VERY concerned with the possibility that Pro Sites is involved in this issue... we have another outstanding thread on a critical, recurring issue and have not heard back from WPMUDEV since submitting creds a while ago... if possible, can you please check in w/ @Hoang Ngo and @Rheinard about https://premium.wpmudev.org/forums/topic/pro-sites-generating-multiple-extensions#post-982267

  • Adam Czajczyk

    Hello antKat and Max,

    I hope you're well today!

    Thank you for this additional information, I hope this will be of help for our devs.

    As for both threads of yours, I've asked @Hoang Ngo directly to take a look as soon as possible and I'm sure he'll get into it once he gets back online. I apologize for keeping you waiting but the issue seems quite complex and we need developer's help here.

    Please keep an eye on this thread and either me or Predrag we will immediately update you with new information (hopefully a fix) as soon as we get a response from developers.

    Best regards,
    Adam

    • wp.network

      thanks @Adam Czajczyk

      ...as a db note, the legacy server is using MySQL 5.6 and some pretty standard configs while the current production server is using cPanel packaged MariaDB with a few less-usual settings [in both cases we're using only InnoDB and utf8mb4/utf8mb4_unicode_ci]... happy to provide my.cnf (or other server config) files via contact form if that would contribute toward expeditiousness - pls advise if so =)

      Cheers, Max

      • Adam Czajczyk

        Hello Max,

        Thanks for this information. As for MariaDB vs MySQL - these should be fully compatible but I bet you already know this :slight_smile: I'm not sure if this is relevant then but of course it's an important clue as there's always a chance that there may be some db-specific issue to be addressed.

        That said, I once again apologize for this taking that much time. I think that's just that our developers are extremely busy at the moment but just to make sure I ping them again to update me with a status of the issue as soon as possible.

        Best regards,
        Adam

    • wp.network

      Hi @Predrag Dubajic thanks for the reply... I do not believe that any of the custom .htaccess stuff is at play here (though, if there is anything in particular that caught your eye pls advise =)...

      Happily, I have other news to report:

      here is an excerpt from error_log re recent failed subsite creation from template:

      [04-Jan-2016 22:24:21 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 2 for query SELECT * FROM uecom_101_posts
                          WHERE ID IN (  ) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array, call_user_func_array, ProSites->checkout_page_load, do_action('psts_checkout_page_load'), call_user_func_array, ProSites_Gateway_PayPalExpressPro->process_checkout_form, ProSites_Helper_Registration::activate_blog, wpmu_activate_signup, wpmu_create_blog, do_action('wpmu_new_blog'), call_user_func_array, blog_templates->set_blog_defaults, NBT_Template_copier->execute, call_user_func, NBT_Template_copier->copy_menus, NBT_Template_copier->copy_menu
      [04-Jan-2016 22:24:21 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 2 for query SELECT * FROM uecom_101_postmeta
                          WHERE post_id IN (  ) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array, call_user_func_array, ProSites->checkout_page_load, do_action('psts_checkout_page_load'), call_user_func_array, ProSites_Gateway_PayPalExpressPro->process_checkout_form, ProSites_Helper_Registration::activate_blog, wpmu_activate_signup, wpmu_create_blog, do_action('wpmu_new_blog'), call_user_func_array, blog_templates->set_blog_defaults, NBT_Template_copier->execute, call_user_func, NBT_Template_copier->copy_menus, NBT_Template_copier->copy_menu
      [04-Jan-2016 22:24:22 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query CREATE TABLE IF NOT EXISTS uecom_616_affiliatereferrers (
        <code>user_id</code> bigint(20) DEFAULT NULL,
        <code>period</code> varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        <code>url</code> varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        <code>referred</code> bigint(20) DEFAULT '0',
        UNIQUE KEY <code>user_period_url</code> (<code>user_id</code>,<code>period</code>,<code>url</code>),
        KEY <code>user_id</code> (<code>user_id</code>),
        KEY <code>period</code> (<code>period</code>)
      ) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, do_action_ref_array, call_user_func_array, ProSites->checkout_page_load, do_action('psts_checkout_page_load'), call_user_func_array, ProSites_Gateway_PayPalExpressPro->process_checkout_form, ProSites_Helper_Registration::activate_blog, wpmu_activate_signup, wpmu_create_blog, do_action('wpmu_new_blog'), call_user_func_array, blog_templates->set_blog_defaults, NBT_Template_copier->execute, NBT_Template_copier->copy_additional_tables

      so, since we are set up to allow use of ROW_FORMAT=dynamic which allows 3072 bytes rather than 767 bytes

      [mysqld]
      ...
      innodb_large_prefix = on
      innodb_file_format = Barracuda
      innodb_file_per_table = 1

      after trying various things that did not work, the issue is seemingly most easily *solved* by avoiding the current default ROW_FORMAT (compact) which is applied if none is specified with CREATE TABLE (default row_format is changing to dynamic in MySQL 5.7.7 as innodb_large_prefix and Baracuda become default, WP needs re utf8mb4 support are mentioned specifically... see http://www.tocker.ca/2015/01/23/proposal-to-change-additional-defaults-in-mysql-5-7.html and https://mariadb.com/kb/en/mariadb/will-mariadb-101x-pick-up-mysql-577-system-variable-changes-for-innodb/ - also, see https://bugs.mysql.com/bug.php?id=75576 )

      ...so, I found what seems to be a workaround that we would like to see about integrating into NBT - heres the change:

      \blogtemplatesfiles\copier.php
      line 504
      $wpdb->query( "CREATE TABLE IF NOT EXISTS {$new_table} {$table_body}" );
      adds explicit table option, changes to

      $wpdb->query( "CREATE TABLE IF NOT EXISTS {$new_table} {$table_body} ROW_FORMAT=dynamic" );

      making this change seems to allow things to work as expected... am still testing and have a few legacy tables using compact which will alter to use dynamic as well... will appreciate your input re this *fix* to copier.php ...?

      Would love to not have to edit NBT after every update, not sure of backcompat considerations from dev's perspective for others' databases... would love to know your thoughts on this as well =)

      Support Access is extended, the seemingly successful new subsite is ID 618

      Cheers, Max

      (@Adam Czajczyk)

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.