Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16743

Error creating sharded partitioned table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Cannot Reproduce
    • 10.3.8
    • N/A
    • None
    • 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'*

      Attachments

        Activity

          People

            jacob-mathew Jacob Mathew (Inactive)
            Dermot.Brereton Dermot Brereton
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.