Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Users are sometimes unable to create database tables but we don't tell them #121

Open
jonathanstegall opened this issue Nov 16, 2017 · 15 comments
Open

Users are sometimes unable to create database tables but we don't tell them #121

jonathanstegall opened this issue Nov 16, 2017 · 15 comments
Labels
enhancement New feature or request help wanted Other developer help would be great partially done This issue is partially solved but still needs work
Milestone
v3.0.0

Comments

@jonathanstegall
Copy link
Member

jonathanstegall commented Nov 16, 2017 •
edited

I see some questions in the support forum where users try to activate the plugin, but it doesn't create the correct database tables.

Currently the plugin fails silently when this happens, but it won't ever successfully connect to Salesforce.

I think we need to put something into the activate process to check for whether the tables were successfully created, and if they weren't show the user some messaging. Ideally we could find out why the tables weren't created - whether the server user didn't have permission or what - but we should show whatever we know at that point.

database tables

As of 3/7/2019, this is the table structure the plugin needs to create:

CREATE TABLE `wp_object_sync_sf_field_map` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `name` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `wordpress_object` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `salesforce_object` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `salesforce_record_types_allowed` longblob,
  `salesforce_record_type_default` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `fields` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pull_trigger_field` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'LastModifiedDate',
  `sync_triggers` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `push_async` tinyint(1) NOT NULL DEFAULT '0',
  `push_drafts` tinyint(1) NOT NULL DEFAULT '0',
  `weight` tinyint(1) NOT NULL DEFAULT '0',
  `version` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `pull_to_drafts` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_sf_type_wordpress_type` (`wordpress_object`,`salesforce_object`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `wp_object_sync_sf_object_map` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wordpress_id` varchar(32) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `salesforce_id` varbinary(32) NOT NULL DEFAULT '',
  `wordpress_object` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `object_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_sync` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_sync_action` varchar(128) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `last_sync_status` tinyint(1) NOT NULL DEFAULT '0',
  `last_sync_message` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `salesforce` (`salesforce_id`),
  UNIQUE KEY `salesforce_wordpress` (`wordpress_object`,`wordpress_id`),
  KEY `wordpress_object` (`wordpress_object`,`wordpress_id`),
  KEY `salesforce_object` (`salesforce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
@jonathanstegall jonathanstegall added the enhancement New feature or request label Nov 16, 2017
@jonathanstegall jonathanstegall changed the title Users are sometimes unable to create database tables Users are sometimes unable to create database tables but we don't tell them Nov 16, 2017
@jonathanstegall
Copy link
Member Author

At least part of the solution here is to tell users what the SQL query is to create the tables, so they can send it to their host.

table one

CREATE TABLE wp_object_sync_sf_field_map (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  label varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  name varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  wordpress_object varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  salesforce_object varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  salesforce_record_types_allowed longblob,
  salesforce_record_type_default varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  fields text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  pull_trigger_field varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'LastModifiedDate',
  sync_triggers text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  push_async tinyint(1) NOT NULL DEFAULT '0',
  push_drafts tinyint(1) NOT NULL DEFAULT '0',
  weight tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  UNIQUE KEY name (name),
  KEY name_sf_type_wordpress_type (wordpress_object,salesforce_object)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

table two

CREATE TABLE wp_object_sync_sf_object_map (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  wordpress_id varchar(32) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  salesforce_id varchar(32) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  wordpress_object varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  created datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  object_updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  last_sync datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  last_sync_action varchar(128) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  last_sync_status tinyint(1) NOT NULL DEFAULT '0',
  last_sync_message varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY salesforce (salesforce_id),
  UNIQUE KEY salesforce_wordpress (wordpress_object,wordpress_id),
  KEY wordpress_object (wordpress_object,wordpress_id),
  KEY salesforce_object (salesforce_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

@jonathanstegall jonathanstegall added this to the v2 milestone Nov 21, 2017
@jonathanstegall
Copy link
Member Author

jonathanstegall commented Dec 4, 2017 •
edited

At least some of this is because they are using MyISAM instead of InnoDB link here. The table creation will fail for MyISAM users because the key KEY name_sf_type_wordpress_type (wordpress_object,salesforce_object) in the object_sync_sf_field_map table is too long.

To fix this we should try to create the table as it is (because Salesforce can use a lot of characters and this should absolutely be our default), and if it fails because the key is too long we should try again with a limit on the key of 1000 bytes.

Right now the key length is up to 383 (128 wp object name; 255 sf object name) characters, and they're all varchar. So presumably it wants 1149 bytes and MyISAM won't do it. We would need to cut 50 characters for those users.

However cutting only 50 doesn't work. I got it to work with this: KEY name_sf_type_wordpress_type (wordpress_object(100),salesforce_object(150)). I'm not sure that is viable, from a Salesforce data perspective, but maybe?

Alternatively we could just force InnoDB on these tables, or not do anything. I need to think more about this.

jonathanstegall added a commit that referenced this issue Dec 6, 2017
@jonathanstegall
related to #121, this will force InnoDB on the field_map table becaus…
f1753d1 
…e the index is too long for MyISAM.
@jonathanstegall
Copy link
Member Author

The easiest way to keep allowing users to run MyISAM without messing up Salesforce data configurations for everyone else would be something like this, in activate.php:

dbDelta( $field_map_sql );

if ( 'Specified key was too long; max key length is 1000 bytes' === $this->wpdb->last_error ) {
  // this user is using MyISAM; give them a shorter key length
  $field_map_sql = "CREATE TABLE $field_map_table (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    label varchar(64) NOT NULL DEFAULT '',
    name varchar(64) NOT NULL DEFAULT '',
    wordpress_object varchar(128) NOT NULL DEFAULT '',
    salesforce_object varchar(255) NOT NULL DEFAULT '',
    salesforce_record_types_allowed longblob,
    salesforce_record_type_default varchar(255) NOT NULL DEFAULT '',
    fields text NOT NULL,
    pull_trigger_field varchar(128) NOT NULL DEFAULT 'LastModifiedDate',
    sync_triggers text NOT NULL,
    push_async tinyint(1) NOT NULL DEFAULT '0',
    push_drafts tinyint(1) NOT NULL DEFAULT '0',
    weight tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    UNIQUE KEY name (name),
    KEY name_sf_type_wordpress_type (wordpress_object(100),salesforce_object(150))
  ) ENGINE=MyISAM $charset_collate";
  // try again to create the table
  dbDelta( $field_map_sql );
}

However this has a couple of issues:

  1. It causes the following error:

The plugin generated 1122 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.

  1. It is theoretically possible that the max key length might be something other than 1000 bytes and still fail.

I think for now it's best to just force InnoDB on that table. This will get released in the next plugin update.

@jonathanstegall
Copy link
Member Author

I'm going to close this for now. It is theoretically possible that users might have more reasons that tables aren't created, but we'll cross that when we get there. I think the majority just have a default of MyISAM.

@jonathanstegall
Copy link
Member Author

I think we also need to investigate this utf8mb4 stuff. https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/.

@mistermarco
Copy link
Contributor

Thanks for this note, it helped me get up and running.

I encountered this bug as well. For me, it manifested as a generic error when trying to save field maps. I finally traced it to a missing table (wp_object_sync_sf_field_map). I used the SQL code above to create the table manually but that didn't quite work. It turns out that since it was posted there is a new column in the table (version).

When I created that, things worked fine.

So, here is the SQL code to create the wp_object_sync_sf_field_map table that worked for me in case it can help others.

CREATE TABLE `wp_object_sync_sf_field_map` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `name` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `wordpress_object` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `salesforce_object` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `salesforce_record_types_allowed` longblob,
  `salesforce_record_type_default` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `fields` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pull_trigger_field` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'LastModifiedDate',
  `sync_triggers` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `push_async` tinyint(1) NOT NULL DEFAULT '0',
  `push_drafts` tinyint(1) NOT NULL DEFAULT '0',
  `weight` tinyint(1) NOT NULL DEFAULT '0',
  `version` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_sf_type_wordpress_type` (`wordpress_object`,`salesforce_object`(191))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

@jonathanstegall
Copy link
Member Author

@mistermarco thanks for the reminder there. I'm curious if PHP or MySQL ever returned error messages, especially during the activation of the plugin? Did it tell you why it was unable to create the tables? And was it only the field map table that it failed to create, or both?

@mistermarco
Copy link
Contributor

PHP didn't show any errors, and I don't have access to the MySQL error log (this is on Pantheon, and I actually found out it's MariaDB - not sure if that makes a difference). It only failed to create the wp_object_sync_sf_field_map table. I'm trying to set up an identical environment locally to see if I can reproduce this issue reliably.

jonathanstegall added a commit that referenced this issue Jun 28, 2018
@jonathanstegall
document how to create the offending table from #121
ab6d78f
@jonathanstegall jonathanstegall added the partially done This issue is partially solved but still needs work label Nov 27, 2018
@jonathanstegall jonathanstegall added the help wanted Other developer help would be great label Jan 22, 2019
@andygagnon
Copy link

Hi,
On Pantheon, after install, could not create any field maps. Table was missing. Used this SQL,
CREATE TABLE wp_object_sync_sf_field_map (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
label varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
name varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
wordpress_object varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
salesforce_object varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
salesforce_record_types_allowed longblob,
salesforce_record_type_default varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
fields text COLLATE utf8mb4_unicode_520_ci NOT NULL,
pull_trigger_field varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'LastModifiedDate',
sync_triggers text COLLATE utf8mb4_unicode_520_ci NOT NULL,
push_async tinyint(1) NOT NULL DEFAULT '0',
push_drafts tinyint(1) NOT NULL DEFAULT '0',
weight tinyint(1) NOT NULL DEFAULT '0',
version varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY name (name),
KEY name_sf_type_wordpress_type (wordpress_object,salesforce_object(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Still doesn't work. Missing columns error.
Attached is a huge php error log (a few days) all related to this.
Lots of information in there.
Please advise a solution for Pantheon environment.
Thanks for your time.

php-error.log

@jonathanstegall
Copy link
Member Author

jonathanstegall commented Mar 7, 2019 •
edited

@andygagnon I don't have the space to look through days of error logs for you.

This issue is not regularly updated when the plugin changes, so here are the current table structures. You may be able to just use them without worrying about your logs. Although I've seen this plugin work well in Pantheon, so you may have other issues.

CREATE TABLE `wp_object_sync_sf_field_map` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `name` varchar(64) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `wordpress_object` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `salesforce_object` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `salesforce_record_types_allowed` longblob,
  `salesforce_record_type_default` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `fields` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pull_trigger_field` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'LastModifiedDate',
  `sync_triggers` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `push_async` tinyint(1) NOT NULL DEFAULT '0',
  `push_drafts` tinyint(1) NOT NULL DEFAULT '0',
  `weight` tinyint(1) NOT NULL DEFAULT '0',
  `version` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `pull_to_drafts` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_sf_type_wordpress_type` (`wordpress_object`,`salesforce_object`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE `wp_object_sync_sf_object_map` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wordpress_id` varchar(32) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `salesforce_id` varbinary(32) NOT NULL DEFAULT '',
  `wordpress_object` varchar(128) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `object_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_sync` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_sync_action` varchar(128) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `last_sync_status` tinyint(1) NOT NULL DEFAULT '0',
  `last_sync_message` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `salesforce` (`salesforce_id`),
  UNIQUE KEY `salesforce_wordpress` (`wordpress_object`,`wordpress_id`),
  KEY `wordpress_object` (`wordpress_object`,`wordpress_id`),
  KEY `salesforce_object` (`salesforce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

@andygagnon
Copy link

Thank you. Deleted and re-created mysql tables. Works now.

Missing column was 'pull_to_drafts'.

Here are the highlights from the error log that may help solve this problem long term. My apologies for the large data dump.

At plugin install, this is the critical error on Pantheon.

[04-Mar-2019 14:55:39 UTC] WordPress database error Index column size too large. The maximum column size is 767 bytes. for query CREATE TABLE wp_object_sync_sf_field_map (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
label varchar(64) NOT NULL DEFAULT '',
name varchar(64) NOT NULL DEFAULT '',
wordpress_object varchar(128) NOT NULL DEFAULT '',
salesforce_object varchar(255) NOT NULL DEFAULT '',
salesforce_record_types_allowed longblob,
salesforce_record_type_default varchar(255) NOT NULL DEFAULT '',
fields longtext NOT NULL,
pull_trigger_field varchar(128) NOT NULL DEFAULT 'LastModifiedDate',
sync_triggers text NOT NULL,
push_async tinyint(1) NOT NULL DEFAULT '0',
push_drafts tinyint(1) NOT NULL DEFAULT '0',
pull_to_drafts tinyint(1) NOT NULL DEFAULT '0',
weight tinyint(1) NOT NULL DEFAULT '0',
version varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id),
UNIQUE KEY name (name),
KEY name_sf_type_wordpress_type (wordpress_object,salesforce_object)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci made by require_once('wp-admin/admin.php'), do_action('admin_init'), WP_Hook->do_action, WP_Hook->apply_filters, Object_Sync_Sf_Activate->wordpress_salesforce_update_db_check, Object_Sync_Sf_Activate->wordpress_salesforce_tables, dbDelta

After that, can't write to a non-existent table,

[04-Mar-2019 15:00:08 UTC] WordPress database error Table 'pantheon.wp_object_sync_sf_field_map' doesn't exist for query SELECT id, label, wordpress_object, salesforce_object, salesforce_record_types_allowed, salesforce_record_type_default, fields, pull_trigger_field, sync_triggers, push_async, push_drafts, pull_to_drafts, weight, version FROM wp_object_sync_sf_field_map made by require_once('wp-admin/admin.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('plugins_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, Object_Sync_Sf_Salesforce_Push->add_actions, Object_Sync_Sf_Mapping->get_fieldmaps

Created table from SQL I referred to previously, but it was missing pull_to_drafts column.

[06-Mar-2019 21:17:51 UTC] WordPress database error Unknown column 'pull_to_drafts' in 'field list' for query SELECT id, label, wordpress_object, salesforce_object, salesforce_record_types_allowed, salesforce_record_type_default, fields, pull_trigger_field, sync_triggers, push_async, push_drafts, pull_to_drafts, weight, version FROM wp_object_sync_sf_field_map made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('plugins_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, Object_Sync_Sf_Salesforce_Push->add_actions, Object_Sync_Sf_Mapping->get_fieldmaps

Deleted and created 2 new tables, per your SQL. Creating fieldmaps works now.

Thanks for your time.

@jonathanstegall
Copy link
Member Author

jonathanstegall commented Apr 11, 2019 •
edited

I've added a new section in the documentation that contains the SQL for creating tables when they're missing. It is located here.

My hope is that this can be maintained if and when the SQL changes, and that users will be able to refer to that document any time they have this issue. I've made this instruction in code as well in 5cc560e.

I'm also hopeful that it'll happen less and less, but I'm still unclear exactly what conditions - aside from out of date versions of MySQL - cause it to happen.

@jonathanstegall
Copy link
Member Author

One further thing that came up in this forum post is that one possible cause is that hosts might not have innodb_large_prefix enabled on the MySQL server.

I don't have any space to build out that kind of compatibility into this plugin, and I don't foresee that ever changing, but it would be nice to check for that feature's presence during the activation process. Then we could help those users avoid getting too far into this for no reason.

@jonathanstegall jonathanstegall mentioned this issue Aug 30, 2019
Open
@jonathanstegall jonathanstegall modified the milestones: v2.0.0, v3.0.0 Jan 10, 2020
@jrfoell
Copy link

jrfoell commented Apr 8, 2020

Just want to chime in that I encountered this problem when trying to create wp_object_sync_sf_object_map on a server running MySQL 5.5:

CREATE TABLE `wp_object_sync_sf_object_map` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `wordpress_id` varchar(32) NOT NULL,
    `salesforce_id` varbinary(32) NOT NULL DEFAULT '',
    `wordpress_object` varchar(128) NOT NULL DEFAULT '',
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `object_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_sync` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_sync_action` varchar(128) DEFAULT NULL,
    `last_sync_status` tinyint(1) NOT NULL DEFAULT '0',
    `last_sync_message` varchar(255) DEFAULT NULL,
    PRIMARY KEY  (`id`),
    KEY wordpress_object (`wordpress_object`,`wordpress_id`),
    KEY salesforce_object (`salesforce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ERROR 1067 (42000): Invalid default value for 'created'

One suggestion was to change the created column from a datetime to a timestamp: https://dba.stackexchange.com/a/132954

But MySQL will only allow one column to have one DEFAULT CURRENT_TIMESTAMP so I had to use a statement with zeroes as defaults:

CREATE TABLE `wp_object_sync_sf_object_map` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `wordpress_id` varchar(32) NOT NULL,
    `salesforce_id` varbinary(32) NOT NULL DEFAULT '',
    `wordpress_object` varchar(128) NOT NULL DEFAULT '',
    `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `object_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `last_sync` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `last_sync_action` varchar(128) DEFAULT NULL,
    `last_sync_status` tinyint(1) NOT NULL DEFAULT '0',
    `last_sync_message` varchar(255) DEFAULT NULL,
    PRIMARY KEY  (`id`),
    KEY wordpress_object (`wordpress_object`,`wordpress_id`),
    KEY salesforce_object (`salesforce_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

@jonathanstegall
Copy link
Member Author

@jrfoell one thing I observed in trying to understand #291 is that the innodb_large_prefix issue exists for users running MySQL versions between 5.5 and 5.7.7. Before and after that it's not applicable. I wonder if that's the case for your report as well.

If it is, I would certainly review a pull request that checked MySQL versions or capability in order to make it work for those users, but it's very unlikely that I would have the capacity to do that work myself.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request help wanted Other developer help would be great partially done This issue is partially solved but still needs work
Projects
None yet
Milestone
v3.0.0
Development

No branches or pull requests
4 participants
@mistermarco @jonathanstegall @andygagnon @jrfoell