[MDEV-16743] Error creating sharded partitioned table Created: 2018-07-12  Updated: 2018-10-18  Resolved: 2018-07-30

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3.8
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Dermot Brereton Assignee: Jacob Mathew (Inactive)
Resolution: Cannot Reproduce Votes: 1
Labels: None
Environment:

Ubuntu 16.04.4 LTS


 Description   

Step 1). Create users for spider to connect to backend servers.

ssh root@maria-shard1
mysql -p
CREATE USER 'spider_user'@'10.4.21.7' IDENTIFIED BY 'spider';
GRANT ALL ON test.* TO 'spider_user'@'10.4.21.7';
FLUSH PRIVILEGES;

ssh root@maria-shard2
mysql -p
CREATE USER 'spider_user'@'10.4.21.7' IDENTIFIED BY 'spider';
GRANT ALL ON test.* TO 'spider_user'@'10.4.21.7';
FLUSH PRIVILEGES;

Step 2.) Verfify connections from the spider node (maria-dba).

mysql -u spider_user -p -h 10.4.21.76 test
mysql -u spider_user -p -h 10.4.21.77 test

Step 3) Define server entries on the spider node (maria-dba).

CREATE SERVER shard1 FOREIGN DATA WRAPPER MYSQL OPTIONS
(host '10.4.21.76', database 'test', user 'spider_user', password 'spider', port 3306);

CREATE SERVER shard2 FOREIGN DATA WRAPPER MYSQL OPTIONS
(host '10.4.21.77', database 'test', user 'spider_user', password 'spider', port 3306);

4.) Create Partitioned table on spider node (maria-dba);

CREATE TABLE opportunities (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT '0',
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11) NOT NULL DEFAULT '',
primary key (id),
key k (k)
) engine=spider COMMENT='wrapper "mysql", table "opportunities"'
PARTITION BY KEY (id)
(
PARTITION pt1 COMMENT = 'srv "shard1"',
PARTITION pt2 COMMENT = 'srv "shard2"'
) ;

*ERROR 1062 (23000): Duplicate entry 'test-opportunities#P#pt1-0' for key 'PRIMARY'*

 Comments   
Comment by Dermot Brereton [ 2018-07-18 ]

For clarification there is no data contained in the tables.

Comment by Dermot Brereton [ 2018-07-18 ]

Is it a bug ?

Comment by Jacob Mathew (Inactive) [ 2018-07-19 ]

I am unable to reproduce the problem.

Comment by Dermot Brereton [ 2018-07-24 ]

MariaDB version 10.3.8 was installed on the spider node.
MariaDB version 10.2.16 was installed on the sharded nodes.

Could you please confirm if your environment is similar?

Comment by Jacob Mathew (Inactive) [ 2018-07-24 ]

My original attempts to reproduce the problem used MariaDB 10.3 on all nodes, as it was not explained in the bug description that a different version was used on the data nodes. I have retried using version 10.3.8 on the Spider node and version 10.2.16 on the data nodes. Once again, I am unable to reproduce the problem.

Comment by Ivan Tyagov [ 2018-07-25 ]

Hi,
I can confirm I got same behavior with 10.3.8 / Ubuntu 16.04 LTS with all nodes having same release.
First time I was able (at least it reported so) to create the table, see below:

===
node1 [localhost]

{msandbox} (test) > create table opportunities (
-> id int,
-> accountName varchar(20),
-> name varchar(128),
-> owner varchar(7),
-> amount decimal(10,2),
-> closeDate date,
-> stageName varchar(11),
-> primary key (id),
-> key (accountName)
-> ) engine=spider COMMENT='wrapper "mysql", table "opportunities"'
-> PARTITION BY HASH (id)
-> (
-> PARTITION pt1 COMMENT = 'srv "backend1"',
-> PARTITION pt2 COMMENT = 'srv "backend2"'
-> ) ;
Query OK, 0 rows affected (0.004 sec)

Then trying to insert a row:

node1 [localhost] {msandbox}

(test) > insert into opportunities values(1, "test", "test", "test", 10, "01/01/2018", "test");
ERROR 1932 (42S02): Table 'test.opportunities' doesn't exist in engine

node1 [localhost]

{msandbox} (test) > show table status \G
*************************** 1. row ***************************
Name: opportunities
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: Table 'test.opportunities' doesn't exist in engine
Max_index_length: NULL
Temporary: NULL
1 row in set, 1 warning (0.001 sec)

I tried to remove table:

node1 [localhost] {msandbox}

(test) > drop table opportunities;
Query OK, 0 rows affected, 1 warning (0.001 sec)

node1 [localhost]

{msandbox} (test) > show warnings;
---------------------------------------------------------------
| Level | Code | Message |
---------------------------------------------------------------
| Warning | 1932 | Table 'test.opportunities' doesn't exist in engine |
---------------------------------------------------------------
1 row in set (0.000 sec)

node1 [localhost] {msandbox}

(test) > show tables;
Empty set (0.000 sec)

node1 [localhost]

{msandbox} (test) > create table opportunities (
-> id int,
-> accountName varchar(20),
-> name varchar(128),
-> owner varchar(7),
-> amount decimal(10,2),
-> closeDate date,
-> stageName varchar(11),
-> primary key (id),
-> key (accountName)
-> ) engine=spider COMMENT='wrapper "mysql", table "opportunities"'
-> PARTITION BY HASH (id)
-> (
-> PARTITION pt1 COMMENT = 'srv "backend1"',
-> PARTITION pt2 COMMENT = 'srv "backend2"'
-> ) ;
ERROR 1062 (23000): Duplicate entry 'test-opportunities#P#pt1-0' for key 'PRIMARY'


node1 [localhost] {msandbox}

(test) > show engines;
-------------------------------------------------------------------------------------------------------------------------------------+

Engine Support Comment Transactions XA Savepoints

-------------------------------------------------------------------------------------------------------------------------------------+

SPIDER YES Spider storage engine YES YES NO
CSV YES Stores tables as CSV files NO NO NO
MRG_MyISAM YES Collection of identical MyISAM tables NO NO NO
MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO
Aria YES Crash-safe tables with MyISAM heritage NO NO NO
MyISAM YES Non-transactional engine with good performance and small data footprint NO NO NO
PERFORMANCE_SCHEMA YES Performance Schema NO NO NO
InnoDB DEFAULT Supports transactions, row-level locking, foreign keys and encryption for tables YES YES YES
SEQUENCE YES Generated tables filled with sequential values YES NO YES

-------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.000 sec)

Maybe this helps you debug?
Ivan

Comment by Ivan Tyagov [ 2018-07-25 ]

I investigated more. It turned out to be an issue with connectivity between "spider" node and backedn1 and backedn2 nodes.
Still
ERROR 1062 (23000): Duplicate entry 'test-opportunities#P#pt1-0' for key 'PRIMARY'
remains after restoring connectivity which is not nice (to overcome I started with fresh instance).

So it should rather display a more meaningful message and / or warn for connectivity issues (no idea if possible) during "spider" table creation.

Comment by Jacob Mathew (Inactive) [ 2018-07-25 ]

Something I failed to notice earlier is that in the steps to reproduce the problem you did not issue CREATE TABLE statements on the data nodes (shards). Spider does not push DDL statements to the data nodes. You must issue DDL statements separately on every node in the Spider cluster. This is explained in the following knowledge base page:

https://mariadb.com/kb/en/library/spider-storage-engine-overview/

Comment by Ivan Tyagov [ 2018-07-26 ]

Yes, on my side I consider this issue a misconfiguration except fact that it is not very intuitive message to user.
Still I certainly acknowledge that user must know what he / she is doing. Sorry for noise.

Comment by Dermot Brereton [ 2018-07-26 ]

The CREATE TABLE statements were originally executed on the data noes (shards). The DML was not included in the above steps to reduce noise.

#On both shard nodes.

DROP TABLE IF EXISTS opportunities;
CREATE TABLE opportunities (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT '0',
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11) NOT NULL DEFAULT '',
primary key (id),
key k (k)
)
PARTITION BY KEY (id)
(
PARTITION pt1 COMMENT = 'srv "shard1"',
PARTITION pt2 COMMENT = 'srv "shard2"'
) ;

#On spider node.

DROP TABLE IF EXISTS opportunities;
CREATE TABLE opportunities (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT '0',
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11) NOT NULL DEFAULT '',
primary key (id),
key k (k)
) engine=spider COMMENT='wrapper "mysql", table "opportunities"'
PARTITION BY KEY (id)
(
PARTITION pt1 COMMENT = 'srv "shard1"',
PARTITION pt2 COMMENT = 'srv "shard2"'
) ;

*ERROR 1062 (23000): Duplicate entry 'test-opportunities#P#pt1-0' for key 'PRIMARY'*

Comment by Dermot Brereton [ 2018-07-26 ]

Also note the spider engine is NOT INSTALLED on the shard nodes.

Comment by Dermot Brereton [ 2018-07-27 ]

Column "k" is an INT datatype. Please insert a numeric value or leave blank and the default value (0) will be inserted.

INSERT INTO opportunities (name,owner,amount,closedate,stagename) VALUES ('another','Test',10.2,"2018-07-12",'Test');

Comment by Jacob Mathew (Inactive) [ 2018-07-27 ]

Precisely following your steps to reproduce, including your latest revision, there are no errors:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 30
 
Server version: 10.3.8-MariaDB-debug-log Source distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> CREATE TABLE opportunities (
 
    -> id int(10) unsigned NOT NULL AUTO_INCREMENT,
 
    -> k int(10) unsigned NOT NULL DEFAULT '0',
 
    -> name varchar(128),
 
    -> owner varchar(7),
 
    -> amount decimal(10,2),
 
    -> closeDate date,
 
    -> stageName varchar(11) NOT NULL DEFAULT '',
 
    -> primary key (id),
 
    -> key k (k)
 
    -> )
 
    -> ENGINE=SPIDER COMMENT='wrapper "mysql", table "opportunities"'
 
    -> PARTITION BY KEY (id)
 
    -> (
 
    -> PARTITION pt1 COMMENT = 'srv "shard1"',
 
    -> PARTITION pt2 COMMENT = 'srv "shard2"'
 
    -> ) ;
 
Query OK, 0 rows affected (0.013 sec)
 
 
 
MariaDB [test]> INSERT INTO opportunities (name,owner,amount,closedate,stagename) VALUES ('another','Test',10.2,"2018-07-12",'Test');
 
Query OK, 1 row affected (0.024 sec)
 
 
 
MariaDB [test]> DROP TABLE opportunities;
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
MariaDB [test]> CREATE TABLE opportunities (
 
    -> id int(10) unsigned NOT NULL AUTO_INCREMENT,
 
    -> k int(10) unsigned NOT NULL DEFAULT '0',
 
    -> name varchar(128),
 
    -> owner varchar(7),
 
    -> amount decimal(10,2),
 
    -> closeDate date,
 
    -> stageName varchar(11) NOT NULL DEFAULT '',
 
    -> primary key (id),
 
    -> key k (k)
 
    -> )
 
    -> ENGINE=SPIDER COMMENT='wrapper "mysql", table "opportunities"'
 
    -> PARTITION BY KEY (id)
 
    -> (
 
    -> PARTITION pt1 COMMENT = 'srv "shard1"',
 
    -> PARTITION pt2 COMMENT = 'srv "shard2"'
 
    -> ) ;
 
Query OK, 0 rows affected (0.008 sec)
 
 
 
MariaDB [test]> INSERT INTO opportunities (name,owner,amount,closedate,stagename) VALUES ('another','Test',10.2,"2018-07-12",'Test');
 
Query OK, 1 row affected (0.019 sec)
 
 
 
MariaDB [test]> Ctrl-C -- exit!
 
Aborted

Here is my Ubuntu version information:

Fri Jul 27 14:50 jacob@JM-Ubuntu-Server-VM ~/MariaDB
 
169> lsb_release -a
 
No LSB modules are available.
 
Distributor ID: Ubuntu
 
Description:    Ubuntu 16.04.5 LTS
 
Release:        16.04
 
Codename:       xenial
 
 
 
Fri Jul 27 14:55 jacob@JM-Ubuntu-Server-VM ~/MariaDB
 
170> uname -a
 
Linux JM-Ubuntu-Server-VM 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

I cannot reproduce the reported errors. Unless you have any further information for reproducing the errors, I will need to close the bug as unreproducible.

Comment by Jacob Mathew (Inactive) [ 2018-07-30 ]

Cannot reproduce the reported errors.

Comment by Dermot Brereton [ 2018-10-18 ]

Error was related to the DDL used to create the table on the sharded nodes;

Shard#1: Create table with partition 1.

Shard#2: Create table with partition 2.

DROP TABLE IF EXISTS opportunities;
CREATE TABLE opportunities (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT '0',
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11) NOT NULL DEFAULT '',
primary key (id),
key k (k)
) engine=spider COMMENT='wrapper "mysql", table "opportunities"'
PARTITION BY KEY (id)
(
PARTITION pt1 COMMENT = 'srv "shard1"',
PARTITION pt2 COMMENT = 'srv "shard2"'
) ;

Query OK, 0 rows affected (0.03 sec)

Generated at Thu Feb 08 08:31:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.