There is no pro_sites_transaction table in our database

As you know, we have been experiencing many Pro-Site problems. Looking through our debug log we see the following error:

[20-Jan-2016 21:49:22 UTC] WordPress database error Table 'fg9aw0px_ueoprd.uecom_pro_sites_transactions' doesn't exist for query INSERT INTO uecom_pro_sites_transactions(transaction_id, transaction_date, items, total, sub_total, tax_amount, tax_percentage, country, currency, meta)
VALUES ('13807745760098341', '2016-01-20', 'a:1:{i:0;O:8:\"stdClass\":5:{s:2:\"id\";s:13:\"7KYN34XTQT4TG\";s:9:\"custom_id\";s:13:\"7KYN34XTQT4TG\";s:6:\"amount\";s:5:\"29.99\";s:8:\"quantity\";s:1:\"1\";s:11:\"description\";s:70:\"larrytheman.usingessentialoils.com Pro Business: $29.99 USD each month\";}}', '29.99', '29.99', '0.00', '0', '', 'USD', '' ) made by do_action('wp_ajax_nopriv_psts_pypl_ipn'), call_user_func_array, ProSites_Gateway_PayPalExpressPro->ipn_handler, ProSites_Gateway_PayPalExpressPro::record_transaction, ProSites_Helper_Transaction::record, ProSites_Helper_Transaction::record_to_database

Checking into the database we found that this database really does not contain a _pro_sites_transactions table (and predictably, nor does our staging network created from recent database import) - yet we note that a separate, very recently set up WPMS running current Pro Sites does have a _pro_sites_transactions table.
seems that perhaps this table was not created properly... have not been actively adding sites to this network since prior to Pro Sites 3.5 upograde and now are experiencing many unexpected behaviors, many seem similar to confirmed 'bugs' in other threads.

Please see below for list of our other (possibly related) outstanding Pro Sites threads:
https://premium.wpmudev.org/forums/topic/database-errors-with-new-site-sign-ups
https://premium.wpmudev.org/forums/topic/pro-site-not-activated-after-paypal-transaction
https://premium.wpmudev.org/forums/topic/prosites-did-not-recognize-payment-and-site-terminated
https://premium.wpmudev.org/forums/topic/pro-sites-expiring-accounts-after-payment-received
https://premium.wpmudev.org/forums/topic/pro-site-erroneously-sent-out-receipts
https://premium.wpmudev.org/forums/topic/pro-sites-generating-multiple-extensions

Urgent - please include second-level support staff ASAP

We need to ensure that current accounts are preserved and reactivated, and be able to onboard new customers.

Please advise soonest with actionable plan/options (creds already provided, support access active)

  • wp.network

    @Luís et al.
    so... was thinking to just use code from pro-sites.php around 523

    $table4 = "CREATE TABLE {$wpdb->base_prefix}pro_sites_transactions (
    		  id bigint(20) unsigned NOT NULL auto_increment,
    		  transaction_id varchar(255) NOT NULL,
    		  transaction_date DATE NOT NULL,
    		  items longtext NOT NULL,
    		  total decimal(13,4) NOT NULL DEFAULT 0,
    		  sub_total decimal(13,4) NOT NULL DEFAULT 0,
    		  tax_amount decimal(13,4) NOT NULL DEFAULT 0,
    		  tax_percentage decimal(4,2) NOT NULL DEFAULT 0,
    		  country varchar(3) NULL,
    		  currency varchar(3) NULL,
    		  meta longtext NULL,
    		  PRIMARY KEY  (id),
    		  KEY  (id, transaction_id)
    		);";

    The above is the stock code from Pro Sites that is used to create the table upon installation.

    So, I altered slightly to:

    CREATE TABLE uecom_pro_sites_transactions (
    		  id bigint(20) unsigned NOT NULL auto_increment,
    		  transaction_id varchar(255) NOT NULL,
    		  transaction_date DATE NOT NULL,
    		  items longtext NOT NULL,
    		  total decimal(13,4) NOT NULL DEFAULT 0,
    		  sub_total decimal(13,4) NOT NULL DEFAULT 0,
    		  tax_amount decimal(13,4) NOT NULL DEFAULT 0,
    		  tax_percentage decimal(4,2) NOT NULL DEFAULT 0,
    		  country varchar(3) NULL,
    		  currency varchar(3) NULL,
    		  meta longtext NULL,
    		  PRIMARY KEY  (id),
    		  KEY  (id, transaction_id)
    );

    The above is the stock code, modified slightly (uses actual database prefix, drops some punctuation marks) to function as straight SQL for manual execution.

    and then altered further to deal with error detailed below, adding row_format table option after the create table definitions...

    CREATE TABLE uecom_pro_sites_transactions (
    		  id bigint(20) unsigned NOT NULL auto_increment,
    		  transaction_id varchar(255) NOT NULL,
    		  transaction_date DATE NOT NULL,
    		  items longtext NOT NULL,
    		  total decimal(13,4) NOT NULL DEFAULT 0,
    		  sub_total decimal(13,4) NOT NULL DEFAULT 0,
    		  tax_amount decimal(13,4) NOT NULL DEFAULT 0,
    		  tax_percentage decimal(4,2) NOT NULL DEFAULT 0,
    		  country varchar(3) NULL,
    		  currency varchar(3) NULL,
    		  meta longtext NULL,
    		  PRIMARY KEY  (id),
    		  KEY  (id, transaction_id)
    ) ROW_FORMAT=dynamic;

    The above is modified 'manual execution' model of Pro Sites table creation SQL for the _prosites_transactions table... now with create table option ROW_FORMAT=dynamic added after the create table definitions to effect increase of index column max to 3072 bytes... please see:
    https://premium.wpmudev.org/forums/topic/blog-template-insertion-error-at-site-creation#post-1009342
    for further details on this table option as relates to a separate open issue with NBT

    The original SQL I pulled out of pro-sites.php was failing due to the index column max of 767 bytes... thus I had to adapt further to use ROW_FORMAT=dynamic option at table creation.

    I have only tested this 'in the sandbox'... we have not run this SQL at the production database pending insight from WPMUDEV... please advise asap =)

    Cheers, Max

    • wp.network

      @Luís @Predrag Dubajic @Michael Bissett

      We have created the _pro_sites_transactions table at production database using the following SQL

      CREATE TABLE uecom_pro_sites_transactions (
      		  id bigint(20) unsigned NOT NULL auto_increment,
      		  transaction_id varchar(255) NOT NULL,
      		  transaction_date DATE NOT NULL,
      		  items longtext NOT NULL,
      		  total decimal(13,4) NOT NULL DEFAULT 0,
      		  sub_total decimal(13,4) NOT NULL DEFAULT 0,
      		  tax_amount decimal(13,4) NOT NULL DEFAULT 0,
      		  tax_percentage decimal(4,2) NOT NULL DEFAULT 0,
      		  country varchar(3) NULL,
      		  currency varchar(3) NULL,
      		  meta longtext NULL,
      		  PRIMARY KEY  (id),
      		  KEY  (id, transaction_id)
      ) ROW_FORMAT=dynamic;

      Cheers, Max

  • antKat

    1. I'm trying to understand how this table functions, its impact and correct for missing data if needed.
    I have approx 300 sites. We cancelled the subscriptions for about 260 of these sites back last fall. We have re-launched in January and everyone who had a previous site needs to create a new subscription. Is this table used for re-subscribing at all? Some of our sites are able to create new subscriptions without problems yet for others we are having problems. I would like to determine if this table is part of the problem. It seems if it were required, then no one would be able to re-subscribe.

    2. Please review the table structure that WP-Networks created above.
    3. We now require the Row_Format=dynamic for our character set.

    • Umesh Kumar

      Hi @antKat,

      I missed your reply in here.

      I'm trying to understand how this table functions, its impact and correct for missing data if needed.
      I have approx 300 sites. We cancelled the subscriptions for about 260 of these sites back last fall. We have re-launched in January and everyone who had a previous site needs to create a new subscription. Is this table used for re-subscribing at all? Some of our sites are able to create new subscriptions without problems yet for others we are having problems. I would like to determine if this table is part of the problem. It seems if it were required, then no one would be able to re-subscribe.

      The table is mostly used for debugging purpose, but it is required to complete the processing of payment, as otherwise it may lead to early exit of script, and leading to issues.

      Please review the table structure that WP-Networks created above

      Table structure is absolutely fine.

      We now require the Row_Format=dynamic for our character set.

      That shouldn't affect the plugin in any manner, but we don't include the Row_Format in any query and leave it on server configuration.

      So I guess we are good with this issue as well.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.