错误的分布式连接计划:结果表分片键不匹配 [英] Bad distributed join plan: result table shard keys do not match

问题描述

我们对memsql/mysql还是很陌生，我们正在尝试进行memsql安装.

We are very new to memsql/mysql and we are trying to play around with a memsql installation.

它已安装在CentOS7虚拟机上，并且正在运行5.1.0版的MemSQL.

It is installed on a CentOS7 virtual machine and we are running version 5.1.0 of MemSQL.

我们从尝试中的一个查询中收到错误: 错误1889(HY000):错误的分布式联接计划:结果表分片键不匹配.请通过[e-mail]与MemSQL支持人员联系.

We are receiving the error from one of the queries we are attempting: ERROR 1889 (HY000): Bad distributed join plan: result table shard keys do not match. Please contact MemSQL support at [e-mail].

关于我们的一个查询

我们有两个表:

CREATE TABLE `MyObjects` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(128) NOT NULL, 
    `Description` VARCHAR(256) NULL,
    `Boolean` BIT NOT NULL,
    `Int8` TINYINT NOT NULL,
    `Int16` SMALLINT NOT NULL,
    `Int32` MEDIUMINT NOT NULL,
    `Int64` INT NOT NULL,
    `Float` DOUBLE NOT NULL,
    `DateCreated` TIMESTAMP  NOT NULL,

    SHARD KEY (`Id`),
    PRIMARY KEY (`Id`)
);

CREATE TABLE `MyObjectDetails` (
    `MyObjectId` INT,
    `Int32` MEDIUMINT NOT NULL,

    SHARD KEY (`MyObjectId`),
    INDEX (`MyObjectId`)
 );

这是我们正在执行并得到错误的SQL.

And here is the SQL we are executing and getting the error.

memsql> SELECT mo.`Id`,mo.`Name`,mo.`Description`,mo.`Boolean`,mo.`Int8`,mo.`Int16`,
mo.`Int32`,mo.`Int64`,mo.`Float`,mo.`DateCreated`,mods.`MyObjectId`,
mods.`Int32` FROM 
    ( SELECT
        mo.`Id`,mo.`Name`,mo.`Description`,mo.`Boolean`,mo.`Int8`,
        mo.`Int16`,mo.`Int32`,mo.`Int64`,mo.`Float`,mo.`DateCreated` 
      FROM `MyObjects` mo LIMIT 10 ) AS mo 
LEFT JOIN `MyObjectDetails` mods ON mo.`Id` = mods.`MyObjectId` ORDER BY `Name` DESC;
ERROR 1889 (HY000): Bad distributed join plan: result table shard keys do not match. Please contact MemSQL support at [e-mail].

有人知道我们为什么会收到此错误，如果有可能进行更改，我们可以帮助缓解此问题?

Does anyone know why we are receiving this error, and if there is a possible change we can make to help alleviate this issue?

我们知道的一件事是，它与内部选择有关，就像我将其拉出并进行连接一样有效，但是从连接中总共只能得到10行.我们正在尝试从主表中获取前10名，并从右侧包括所有详细信息.

The one thing we do know is it has something to do with the inner select as if I pull it out and do the join it works, however we only get 10 total rows from the join. What we are attempting is getting the top 10 from the main table and include all of the details from the right.

我们还尝试将MyObjectDetails表更改为具有空的SHARD KEY，但这会导致相同的错误.

We also tried changing the MyObjectDetails table to have an empty SHARD KEY, but that resulted in the same error.

SHARD KEY()

我们还向明细表中添加了一个自动递增的ID列，并将分片放在该列上，但仍然收到相同的错误.

We also added an auto-incrementing Id column to the details table and put the shard on that column, and yet still received the same error.

在此先感谢您的帮助.

我通过电子邮件与MemSQL联系(顺便说一下，这对他们的客户服务有很大帮助-响应时间非常快，不到几个小时)

I contacted MemSQL through email (huge props to their customer service by the way -- very fast response time, less than a couple hours)

但是根据Mike所说，我将表更改为REFERENCE表，并删除了create table语句的SHARD KEY部分.完成此操作后，便可以运行查询.我不确定这会带来什么后果，但100％可以解决我的问题.谢谢

But from what Mike stated I changed the table to be a REFERENCE table and removed the SHARD KEY part of the create table statement. Once I did this, I was able to run the queries. I am not 100% sure on what ramifications this will have but it fixed my issue at hand. Thanks

CREATE REFERENCE TABLE `MyObjects` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(128) NOT NULL, 
    `Description` VARCHAR(256) NULL,
    `Boolean` BIT NOT NULL,
    `Int8` TINYINT NOT NULL,
    `Int16` SMALLINT NOT NULL,
    `Int32` MEDIUMINT NOT NULL,
    `Int64` INT NOT NULL,
    `Float` DOUBLE NOT NULL,
    `DateCreated` TIMESTAMP  NOT NULL,

    PRIMARY KEY (`Id`)
);

推荐答案

感谢Mike Gallegos对此进行了研究，并在此处添加了他的回答的摘要:

Thanks to Mike Gallegos for looking into this, adding a summary of his answer here:

这里的错误消息是错误的，但错误的原因是MemSQL当前不支持分布式左侧联接，其中左侧(在这种情况下为Limit子查询)具有LIMIT运算符.如果无法在连接后重写查询以执行限制，则可以将MyObjects表更改为参考表以解决此问题.

The error message here is bad, but the reason for the error is that MemSQL does not currently support a distributed left join where the left side (the Limit subquery in this case) has a LIMIT operator. If you cannot rewrite the query to do the limit after the join, then you could change the MyObjects table to a reference table to work around the issue.

这篇关于错误的分布式连接计划:结果表分片键不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！