[New Blog Templates] New Blog Templates error after WooCommerce

Earlier today I had a conversation with WPMUDev live tech support about this issue. I am not sure I will have the time to handle this through live chat, so I wanted to continue this discussion here. I don't know how to add images to the modal window, so I'll continue the thread below this.

  • blue

    After updating to WooCommerce 3.4.0, no new accounts could be created while using New Blog Templates and Pro sites. Either through the sign up page, manual activation with Activiation key via Pro Sites > Manage Sites, or just trying to add a new site via Network Admin > SItes > Add New, we always get the same error.

    Here is the full error from the Log files:

    WordPress database error Can't write; duplicate key in table 'wp_44_wc_download_log' for query CREATE TABLE IF NOT EXISTS wp_44_wc_download_log (
      <code>download_log_id</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      <code>timestamp</code> datetime NOT NULL,
      <code>permission_id</code> bigint(20) unsigned NOT NULL,
      <code>user_id</code> bigint(20) unsigned DEFAULT NULL,
      <code>user_ip_address</code> varchar(100) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
      PRIMARY KEY (<code>download_log_id</code>),
      KEY <code>permission_id</code> (<code>permission_id</code>),
      KEY <code>timestamp</code> (<code>timestamp</code>),
      CONSTRAINT <code>wp_2_wc_download_log_ibfk_1</code> FOREIGN KEY (<code>permission_id</code>) REFERENCES <code>wp_2_woocommerce_downloadable_product_permissions</code> (<code>permission_id</code>) ON DELETE CASCADE
    ) made by wpmu_create_blog, do_action('wpmu_new_blog'), WP_Hook->do_action, WP_Hook->apply_filters, blog_templates->set_blog_defaults, NBT_Template_copier->execute, NBT_Template_copier->copy_additional_tables

    I was working on this today in live chat with Patrick but I had to leave before we could make any serious headway. That chat should be available to reference. Patrick were you able to discover anything after our session had ended?

    Thanks!

    Blue

  • James Morris

    Hello blue

    I hope you are well today.

    I'll ping Patrick directly to get a better picture of what he's already done. In the meantime, would you please grant us access to your site and serve so that we can perform some advanced debugging?

    NOTE: Please make a full backup of your site before sending the following information.

    Please visit the Contact page and complete the form with the following information:

    https://premium.wpmudev.org/contact/#i-have-a-different-question

    Option: I have a different Question

    Subject: "Attn: James Morris"

    In the Message box, please provide the following:

    - link back to this thread for reference
    - any other relevant urls

    - Admin login:
    Admin username
    Admin password
    Login url

    - Hosting Control Panel Login
    Admin username
    Admin password
    Login url

    ~OR~

    - FTP credentials
    host
    username
    password
    (and port if required)

    Best regards,

    James Morris

  • blue

    James Farmer Patrick ,

    I've created a clone of the production server for us to dig into a little deeper into the issue without fear of breaking anything. The options will have changed for login. James send those to you, or Patrick?

    Since migrating everything to a development server, I have a slightly different error, although it still seems to be related:

    At the time of this post, there are 11 updates needed for plugins. I have not updated yet, but will and report back if that itself clears up the issue ( I see WooCommerce did some updates over the past few days)

    To test though, I tried to setup an account on the new server. I"m getting a slightly different error in the database which has to do with an ajax call. Here is the full error triggered when someone tries to sign up:

    [30-May-2018 12:35:43 UTC] WordPress database error Can't write; duplicate key in table 'wp_47_wc_download_log' for query CREATE TABLE IF NOT EXISTS wp_47_wc_download_log (
      <code>download_log_id</code> bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      <code>timestamp</code> datetime NOT NULL,
      <code>permission_id</code> bigint(20) unsigned NOT NULL,
      <code>user_id</code> bigint(20) unsigned DEFAULT NULL,
      <code>user_ip_address</code> varchar(100) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
      PRIMARY KEY (<code>download_log_id</code>),
      KEY <code>permission_id</code> (<code>permission_id</code>),
      KEY <code>timestamp</code> (<code>timestamp</code>),
      CONSTRAINT <code>wp_2_wc_download_log_ibfk_1</code> FOREIGN KEY (<code>permission_id</code>) REFERENCES <code>wp_2_woocommerce_downloadable_product_permissions</code> (<code>permission_id</code>) ON DELETE CASCADE
    ) made by do_action('wp_ajax_nopriv_check_prosite_blog'), WP_Hook->do_action, WP_Hook->apply_filters, ProSites_Model_Registration::ajax_check_prosite_blog, ProSites_Helper_Registration::activate_blog, wpmu_activate_signup, wpmu_create_blog, do_action('wpmu_new_blog'), WP_Hook->do_action, WP_Hook->apply_filters, blog_templates->set_blog_defaults, NBT_Template_copier->execute, NBT_Template_copier->copy_additional_tables
    [30-May-2018 12:35:43 UTC] WordPress database error Table 'toursoft.wp_47_wc_download_log' doesn't exist for query INSERT INTO wp_47_wc_download_log SELECT * FROM wp_2_wc_download_log made by do_action('wp_ajax_nopriv_check_prosite_blog'), WP_Hook->do_action, WP_Hook->apply_filters, ProSites_Model_Registration::ajax_check_prosite_blog, ProSites_Helper_Registration::activate_blog, wpmu_activate_signup, wpmu_create_blog, do_action('wpmu_new_blog'), WP_Hook->do_action, WP_Hook->apply_filters, blog_templates->set_blog_defaults, NBT_Template_copier->execute, NBT_Template_copier->copy_additional_tables

    At this point, we've blasted a huge group of people to sign up for our production site. This issue has our entire company on hold, so we're viewing it as an emergency since we can't even sign people up manually.

    I know Patrick you said you can't reproduce it. At this point I'm willing to be creative to try to work around it. Unfortunately we can't uninstall and reinstall woocommerce. Active orders are being taken by the clients who are already signed up, unless you guys can suggest a way to work on a live server?

    In the meantime, the dev server is up. However aggressive, let me know how to rip into it to diagnose.

  • blue

    Also, just a heads up, after some digging it seems that this issue has happened several times in the past few years. It may be helpful to reference those tickets and possibly ping Panos and Luís who were active on them. Panos mentioned that he could actually reproduce the error in his testing environment.

    Here are references to the past times this has happened:
    1) https://premium.wpmudev.org/forums/topic/blog-templates-create-site-raise-error

    2) https://premium.wpmudev.org/forums/topic/new-blog-templates-plugin-creates-error-when-affiliate-wp-is-enabled-in-template

    3) https://wordpress.org/support/topic/duplicate-key-in-table-wp_number_slim_events/

    As you can see, this same error is has been happening across 3 different plugins. So, wouldn't this imply that it's not necessarily related to a specific plugin? Indeed in my testing just now, I have deactivated all plugins and still get the error. If Panos was able to provide a hot fix for it, wouldn't this also suggest that NBT needs to be updated in a way that would allow for a larger variety of tables? What I mean is, given that this has happened across 3 different plugins, there has to be a common issue these share that the NBT isn't taking into account when trying to create tables for new templates.

  • blue

    In his response on the thread https://premium.wpmudev.org/forums/topic/new-blog-templates-plugin-creates-error-when-affiliate-wp-is-enabled-in-template?replies=17#post-1322960, Panos suggested a file that hotfixes this issue. I followed his instructions and copied out one file for the other. And, it works. So, when you go to test, you won't get the error.

    While this hotfix let's us get around our immediate issue and continue sign ups (hopefully, we're still testing it) it would great if WPMU could review the code in that file.

    Why is it working? What did he do that overcame the issue? What is the issue? Why was that code never pulled into production if it can solve this problem?

    Patrick can't reproduce the error even without the code, so I still want to keep this ticket open, figure out and solve the fundamental issue. If you need to test the error again before I can get back to it, you are welcome to login and switch the file back to the original. There is a copy in the folder named copier.phpBU

  • James Morris

    Hello blue

    I am terribly sorry for the delay in response here. Thank you so much for keeping us updated and letting us know this is working now, but also providing the detailed steps you have taken to address this immediate need.

    Since Panos provided the original hotfix, I'm going to ping him direct and ask for his valuable feedback on this thread. Please keep in mind that, since Panos is SLS, he's got quite a load on his plate, but he or another SLS tech will reply as soon as possible to assist with this issue.

    Best regards,

    James Morris

  • Panos

    Hey there blue ,

    That file doesn't through the error cause it removes the constraint. In case you want to keep the constraint you can keep the original file and add the following snippet in a mu-plugin:

    add_filter( 'nbt_copy_additional_tables', function( $source_tables ){
    
        $wc_table = 'wc_download_log';
        $values_to_push = array();
        foreach ( $source_tables as $key => $table ) {
             if ( substr_compare( $table, $wc_table, -strlen( $wc_table ) ) === 0 ) {
                unset( $source_tables[$key] );
                $values_to_push[] = $table;
             }
        }
    
        if( ! empty( $values_to_push ) ) {
            $source_tables = array_merge( $source_tables, $values_to_push );
        }
    
        return $source_tables;
    }, 20 );
    
    add_filter( 'nbt_create_additional_table_query' , function( $query, $new_table, $table ){
    
        $wc_table = 'wc_download_log';
        if ( substr_compare( $new_table, $wc_table, -strlen( $wc_table ) ) === 0 ) {
            $source_blog_id = str_replace( $wc_table, '', $table );
            $new_blog_id = str_replace( $wc_table, '', $new_table );
    
            $query = str_replace( $source_blog_id, $new_blog_id, $query );
        }
    
        return $query;
    }, 20, 3 );

    Hope this helps :slight_smile:

  • blue

    Thank you for that Panos

    Can you please explain a bit more what's happening for those of us who aren't SQL savvy? The solution from this thread works fine, but would we not want to just continue to use the solution from your other thread a couple years ago? What is a constraint and why is it needed?

    Many people over the past couple years have had the same problem with different database tables from different plugins. Your earlier solution fixed all of them.

    What happens if, next month for example, we install a new plugin and this happens again? (Since that seems to be specific to woocommerce above).

    Should this not be considered an issue that needs a different direction in the plugin that would handle all cases regardless of plugin like your earlier solution did? Why would this not be considered a bug if the same thing has happened many times over the years?

    Also, do you have any idea why I'm having this issue but Patrick couldn't replicate it when he tried?

    Thanks!

  • Panos

    Hi blue ,

    Sorry for not explaining this better.

    The above snippet does 2 things. First it pushes the specified table wc_download_log to the end of the tables to be copied list. This is required, because in the CONSTRAINT it requires that other table (woocommerce_downloadable_product_permissions) exists already in db.

    Second thing it does, is to replace the blog_id in the required table in the CONSTRAINT.

    This should not affect future release as far as I understand. There is no standard way for such cases, as there might be other plugins/themes that will have the same issue or similar issue. Best way is to specify each using the existing filters as in the snippet provided here.

    I'm afraid I can't be sure why Patrick didn't replicate this as there are many factors that could lead to different error management.

    Hope I managed to explain better this time :slight_smile:

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.