SingleStore (MemSQL) works for Wordpress?

3rd-Party Integrations
1

Hi Guys,

I’ve installed SingleStore using docker (operator and node), all looks good I can login with a regular mysql-client from my Linux server (CentOS7) but when I try to install Wordpress using this DB it d only creates some tables and shows the following errors:

WordPress

WordPress database error: [The unique key named: ‘term_id_taxonomy’ must contain all columns specified in the primary key when no shard key is declared]
CREATE TABLE wp_term_taxonomy ( term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment, term_id bigint(20) unsigned NOT NULL default 0, taxonomy varchar(32) NOT NULL default ‘’, description longtext NOT NULL, parent bigint(20) unsigned NOT NULL default 0, count bigint(20) NOT NULL default 0, PRIMARY KEY (term_taxonomy_id), UNIQUE KEY term_id_taxonomy (term_id,taxonomy), KEY taxonomy (taxonomy) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

WordPress database error: [The unique key named: ‘option_name’ must contain all columns specified in the primary key when no shard key is declared]
CREATE TABLE wp_options ( option_id bigint(20) unsigned NOT NULL auto_increment, option_name varchar(191) NOT NULL default ‘’, option_value longtext NOT NULL, autoload varchar(20) NOT NULL default ‘yes’, PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name), KEY autoload (autoload) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

.
.
.

WordPress database error: [Table ‘wp_test.wp_options’ doesn’t exist]
INSERT INTO wp_options (option_name, option_value, autoload) VALUES (‘rewrite_rules’, ‘’, ‘yes’) ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)

Checking the Database it shows some tables:
MySQL [wp_test]> SHOW TABLES;
±----------------------+
| Tables_in_wp_test |
±----------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
±----------------------+
10 rows in set (0.00 sec)

So I’m wondering if I’m missing something or SingleStore is not 100% MySQL compatible.

Kind regards,

2

SingleStore has a requirement that any unique key in a distributed table must contain the shard key. If you define a primary key, we will shard on that, so the shard key is the same as the primary key.

If you want your tables to be distributed, this can create some incompatibilities with MySQL. You may need to change your keys a bit or add an explicit shard key.

If the table in consideration is not performance-critical or large, you may be able to save some work migrating a MySQL app by making it a reference table since it doesn’t have a shard key and thus doesn’t have these shard key requirements when defining unique keys. E.g. this runs in 7.3:

CREATE reference TABLE wp_term_taxonomy ( term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment, term_id bigint(20) unsigned NOT NULL default 0, taxonomy varchar(32) NOT NULL default ‘’, description longtext NOT NULL, parent bigint(20) unsigned NOT NULL default 0, count bigint(20) NOT NULL default 0, PRIMARY KEY (term_taxonomy_id), UNIQUE KEY term_id_taxonomy (term_id,taxonomy), KEY taxonomy (taxonomy) );

This topic has some useful information about your question.

1 Like
3

Hey Hanson,

Thanks for the response, now it makes sense, I’ll try your recommendation.

Kind regards,