The Mysql Testing Framework tests sql federation engine queries

zihaoAK47 commented 1 year ago

Question

For English only, other languages will not accept.

Before asking a question, make sure you have:

Googled your question.
Searched open and closed GitHub issues.
Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Hi Community, I plan to use the mysql testing framework to test federated queries to find sql that doesn't execute, but I want to make sure that the current testing methods are working.

Here is my test environment: Mysql version：5.7.43 ShardingSphere-proxy version：master zookeeper version：3.8.3 Os：Arch Linux

Here are my test steps:

1.Plan to create 10 databases

create database test_db_0;
create database test_db_1;
create database test_db_2;
create database test_db_3;
create database test_db_4;
create database test_db_5;
create database test_db_6;
create database test_db_7;
create database test_db_8;
create database test_db_9;

2.sharding-proxy global.yaml conf

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: poc_sharding
      server-lists: 127.0.0.1:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500

sqlFederation:
  sqlFederationEnabled: true
  allQueryUseSQLFederation: true
  executionPlanCache:
    initialCapacity: 2000
    maximumSize: 65535

3.connect to sharding-proxy

use mysql;
// Register mysql Data Source
REGISTER STORAGE UNIT mysql (
    URL="jdbc:mysql://127.0.0.1:3304/mysql?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

4.Create a test logic library

create database test;

5.Registered data source

use test;

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_1 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_2 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_3 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_3?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_4 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_4?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_5 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_5?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_6 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_6?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_7 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_7?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_8 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_8?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

REGISTER STORAGE UNIT ds_9 (
    URL="jdbc:mysql://127.0.0.1:3304/test_db_9?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    USER="root",
    PASSWORD="root",
    PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);

strongduanmu commented 1 year ago

@zihaoAK47 A great idea, Mysql Testing Framework can effectively improve the support of SQL Federation. Let's get started on this exciting work.

strongduanmu commented 1 year ago

Can you show the exception which caused by create table statement?

zihaoAK47 commented 1 year ago

Can you show the exception which caused by create table statement?

For example, let's test sql:

DROP TABLE IF EXISTS t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (
  cont_nr int(11) NOT NULL auto_increment,
  ver_nr int(11) NOT NULL default '0',
  aufnr int(11) NOT NULL default '0',
  username varchar(50) NOT NULL default '',
  hdl_nr int(11) NOT NULL default '0',
  eintrag date NOT NULL default '0000-00-00',
  st_klasse varchar(40) NOT NULL default '',
  st_wert varchar(40) NOT NULL default '',
  st_zusatz varchar(40) NOT NULL default '',
  st_bemerkung varchar(255) NOT NULL default '',
  kunden_art varchar(40) NOT NULL default '',
  mcbs_knr int(11) default NULL,
  mcbs_aufnr int(11) NOT NULL default '0',
  schufa_status char(1) default '?',
  bemerkung text,
  wirknetz text,
  wf_igz int(11) NOT NULL default '0',
  tarifcode varchar(80) default NULL,
  recycle char(1) default NULL,
  sim varchar(30) default NULL,
  mcbs_tpl varchar(30) default NULL,
  emp_nr int(11) NOT NULL default '0',
  laufzeit int(11) default NULL,
  hdl_name varchar(30) default NULL,
  prov_hdl_nr int(11) NOT NULL default '0',
  auto_wirknetz varchar(50) default NULL,
  auto_billing varchar(50) default NULL,
  touch timestamp NOT NULL,
  kategorie varchar(50) default NULL,
  kundentyp varchar(20) NOT NULL default '',
  sammel_rech_msisdn varchar(30) NOT NULL default '',
  p_nr varchar(9) NOT NULL default '',
  suffix char(3) NOT NULL default '',
  PRIMARY KEY (cont_nr),
  KEY idx_aufnr(aufnr),
  KEY idx_hdl_nr(hdl_nr),
  KEY idx_st_klasse(st_klasse),
  KEY ver_nr(ver_nr),
  KEY eintrag_idx(eintrag),
  KEY emp_nr_idx(emp_nr),
  KEY wf_igz(wf_igz),
  KEY touch(touch),
  KEY hdl_tag(eintrag,hdl_nr),
  KEY prov_hdl_nr(prov_hdl_nr),
  KEY mcbs_aufnr(mcbs_aufnr),
  KEY kundentyp(kundentyp),
  KEY p_nr(p_nr,suffix)
);

INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
INSERT INTO t1 VALUES (3359359,468,3359359,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1507831,2143894,'+','','P',1909162,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
INSERT INTO t1 VALUES (3359360,0,0,'Mustermann Musterfrau',29674907,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1900169997,2414578,'+',NULL,'N',1909148,'',NULL,NULL,'RV99066_2',20,NULL,'POS',29674907,NULL,NULL,20010202105916,'Mobilfunk','','','97317481','007');
INSERT INTO t1 VALUES (3359361,406,3359361,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag storniert','','(7001-84):Storno, Kd. möchte nicht mehr','privat',NULL,0,'+','','P',1909150,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
INSERT INTO t1 VALUES (3359362,406,3359362,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1509984,2145874,'+','','P',1909154,'MobilComSuper92000D1(Akquise)',NULL,NULL,'MS9ND1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');

error:

failed: 1067: Invalid default value for 'eintrag'

The result from queries just before the failure was:
DROP TABLE IF EXISTS t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
safe_process[34606]: Child process: 34607, exit: 1

github-actions[bot] commented 11 months ago

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

kanha-gupta commented 11 months ago

@zihaoAK47 Thanks for this initiative, are there any major errors encountered ?

zihaoAK47 commented 11 months ago

@zihaoAK47 Thanks for this initiative, are there any major errors encountered ?

Hi, some SQL in the MySQL testing framework needs to be set with corresponding SQL models before execution to execute correctly. When setting the SQL model through proxy, there may be settings that fail and cannot be distributed to all target databases, resulting in the inability to run the testing framework smoothly

kanha-gupta commented 11 months ago

@zihaoAK47 are logs generated for these errors ?

zihaoAK47 commented 11 months ago

@zihaoAK47 are logs generated for these errors ?

These errors only indicate incorrect SQL syntax, but these SQL statements can be executed correctly after setting SQL models.

github-actions[bot] commented 10 months ago

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

github-actions[bot] commented 4 months ago

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

apache / shardingsphere

The Mysql Testing Framework tests sql federation engine queries #28895

Question