Open jonathanstegall opened 6 years ago
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;
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.
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:
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.
I think for now it's best to just force InnoDB on that table. This will get released in the next plugin update.
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.
I think we also need to investigate this utf8mb4 stuff. https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/.
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;
@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?
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.
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.
@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;
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.
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 5cc560ea39579b59cf9fc5f8d542521f13a961d0.
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.
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.
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;
@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.
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: