actiontech / dble Goto Github PK

View Code? Open in Web Editor NEW

1.1K 76.0 318.0 25.99 MB

A High Scalability Middle-ware for MySQL Sharding

Home Page: https://opensource.actionsky.com

License: GNU General Public License v2.0

Shell 2.17% Java 97.67% Batchfile 0.01% Makefile 0.01% Dockerfile 0.01% Python 0.15%

mysql distributed-database sharding dble mysql-sharding

dble's People

Contributors

Stargazers

Watchers

Forkers

ianholyoung dikang123 ditdb liuhuanting zhanglei neeky hulaoan oratop tom2jack evanmatlon zondahuman qyt2018 daihu newajax-1 dingwpmz lmy2011 xxj123go tangjian021 mandarenmanman kevin-penn androidzhaoxiaogang cym2016 mangolms zerolugithub tommyhxh runfriends syzxlyx yab adonis2014 mayou33 zhitongjob lukehuang skymysky linecode b-xiang fnet123 klboke igit-cn stevenmoks baidule neil-zhou zjpjohn zrbsprite weberhuang wjggwm chengang358 mysql-wkp znavy yanqingluo qu58w7 ll490703503 guyaozhou china-jiajia liuwen-leo abirdman 3792274 windzzp newbelee renguiqiang gupeter deanzhudd fackscoiety stvliu wenchongyang lmy4710 tonyliuwei gejove chyuch-github sohudo louishust dudu0614 2892931976 aaafei123 dingncn gwshawsh syniguo lcgg110 wanggrplus123 gspandy andyli028 jacksn2014 isgasho charlesliang1 merrymoon andkylele callmedba zag2017 harry775 forchid renzhongyu bobzhanggmail zhangzhiwu33 zhuangchaoxi gaodevops liuflylove666 wjl1619 ssxlulu sivensu zhaodongdong369 witefree

dble's Issues

Cannot craete temporary table

Raised by: @sevenFH
As titled.

XA is unavailable

Raised by: @yanhuqing666
Steps:

Enable XA, XA commands are sent to data-node, but dble doesn't receive ack of XA commands.

Auto-increment column generates same values with global sequence

Raised by: @zhunina
Steps:

Create a table:
create table sbtest1(id int,name varchar(8),p_id int primary key auto_increment);
Make 4 shardings, whose sharding column is id
Make a global sequence

GLOBAL.HISIDS=
GLOBAL.MINID=10001
GLOBAL.MAXID=20000
GLOBAL.CURID=10000

Query insert into sbtest1(id,name,p_id) values(1,'a',next value for MYCATSEQ_GLOBAL);
returns (1,‘a’,10001)
Query insert into sbtest1(id,name,p_id) values(2,'a',next value for MYCATSEQ_GLOBAL),(3,'a',next value for MYCATSEQ_GLOBAL),(4,'a',next value for MYCATSEQ_GLOBAL);
returns (2,'a',10002),(3,'a',10002),(4,'a',10002), which is not correct

Expectation:

Step 5 should give increment values of global sequence.

Global table with consistency check, `update where in` throws exception

Raised by: @yanhuqing666

Steps:
Global table with consistency check, query update with where in (char_column)

Relates:
MyCATApache/Mycat-Server#1319

Analysis:
Parser doesn't override toString() of where

query select with large result set in stress test, dble restart or abort client connection

Raised by: helingyun
Steps:

Sysbench makes stress test
- SQL:

case1 : select a.k from test1 a inner join test2 b where a.k = b.k limit 4000000
case2 : select a.k from test1 a inner join test2 b where a.k = b.k limit 100

* Command:

./sysbench --test=../db/select.lua --mysql-table-engine=innodb --mysql-user=action --mysql-password=action --mysql-port=8066 --mysql-host=10.186.17.107 --mysql-db=hly_test  --max-requests=0  --max-time=120 --oltp-tables-count=2 --report-interval=1 --oltp-nontrx-mode=select --oltp-table-size=50000000 --num-threads=512  run

Case1 found:
1. Make stress for a while, stop sysbench, dble cpu usage lasts high, and then it's restarted for unknown reason.
2. Make stress for a while, client connections will be abort.
Case2 works find

sharding-join give wrong answer, the result was union of joins on each data-node

Raised by: @sevenFH
Effects:

Outer join (left join/right join/full join)
Cross join
Self join

`select @@session.tx_isolation; ` gives wrong answer

Steps:

Query: set session transaction isolation level READ UNCOMMITTED;
Query: select * from sbtest1;
According to general log of data-node, tx_isolation is set successfully
Query: select @@session.tx_isolation;
Returns: REPEATABLE-READ
Expects: READ UNCOMMITTED

Duplicate records found in `select count(distinct…) from …`

Raised by: @sevenFH
As titled.

Management commands should be all handled.

Raised by: @ditdb
Dble is handling only COM_QUERY of management commands.

`alter table change column` should be disabled on sharding column

Raised by: @sunsun314
if alter column is sharding column or join column, alter should be disabled with error message.

Column alias name on aggregation function doesn't support format 'name'

Raised by: @sevenFH
Steps:

Query: select id,count(id) 'ids' from test1;
Found NPE

Global table which includes not all data-nodes of a datasource, `select` may return wrong result or return table doesn't exist

Raised by: @yanhuqing666
Steps:
As titled.

Relates:
MyCATApache/Mycat-Server#1195 pull request involved this issue

Hash partition makes wrong routes

Raised by: @yanhuqing666
Steps:

Calculating routes of between A and B, A is of data-node a, B is of data-node b, and a index is no less than b index, the routes result is wrong.

unfinished transaction doesn't auto-rollback after client exit

Raised by: @sevenFH
Steps:

Execute sql on client:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update parent_table set name = 'hhh' where f_id='001';

Query OK, 1 row affected (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 0

mysql> exit

Transaction is running on data-node

mysql> select trx_id,trx_mysql_thread_id,trx_state from information_schema.INNODB_TRX;

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

| trx_id  | trx_mysql_thread_id | trx_state |

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

| 1324669 |                 314 | RUNNING   |

| 1324668 |                 316 | RUNNING   |

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

2 rows in set (0.00 sec)

Reconnect to dble, query:

mysql> update parent_table set name = 'hhh' where f_id='002';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

`lock table read`, then select on unlocked table, transaction will be rollback

Raised by: @FlyingMao
Steps:

lock table sbtest1 read;
select * from sbtest2;
select * from sbtest2;
unlock tables;
select * from sbtest2;
Returns: Error 1105（HY000）：MySQL Error Packet{Length=56,id=1}

Analysis:
Data-node general log shows there is a rollback after first select.

[internal]

This issue is internal, and will not be migrated.

EPollArrayWrapper.epollWait 100% CPU

Raised by: @yanhuqing666
Refer:

Sort with multi columns, whose first sort column is not sharding column, gives wrong result.

Raised by: @sevenFH
As titled.

SQL: select ... from ... order by colunm2，id

cleanup unused configurations in SystemConfig

Raised by: @sunsun314
As titled.

Data type set is not supported

Raised by: @zhunina
Steps:

Execute

create table set_columns(
   id int  primary key,
   c_set set('s','m','x') 
);

return error: ERROR 1064 (HY000): error SET

Log:

2016-12-09 11:26:40,556 [WARN ][$_NIOREACTOR-3-RW] ServerConnection [id=1, schema=TESTDB, host=10.186.21.74, user=test,txIsolation=3, autocommit=true, schema=TESTDB]create table set_columns(
INFO   | jvm 1    | 2016/12/09 11:26:40 |    id int  primary key,
INFO   | jvm 1    | 2016/12/09 11:26:40 |    c_set set('s','m','x') 
INFO   | jvm 1    | 2016/12/09 11:26:40 | ) err:com.alibaba.druid.sql.parser.ParserException: error SET com.alibaba.druid.sql.parser.ParserException: error SET
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.parser.SQLExprParser.name(SQLExprParser.java:974)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.parser.SQLExprParser.parseDataType(SQLExprParser.java:1624)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.parseColumn(MySqlExprParser.java:537)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlCreateTableParser.parseCrateTable(MySqlCreateTableParser.java:114)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseCreate(MySqlStatementParser.java:342)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:194)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:2065)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.interceptor.impl.GlobalTableUtil.handleDDLSQL(GlobalTableUtil.java:127)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.interceptor.impl.GlobalTableUtil.consistencyInterceptor(GlobalTableUtil.java:100)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.interceptor.impl.GlobalTableUtil.interceptSQL(GlobalTableUtil.java:83)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.interceptor.impl.DefaultSqlInterceptor.interceptSQL(DefaultSqlInterceptor.java:56)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:43)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.route.RouteService.route(RouteService.java:133)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:273)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.ServerConnection.execute(ServerConnection.java:222)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:136)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.FrontendConnection.query(FrontendConnection.java:317)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.FrontendConnection.query(FrontendConnection.java:337)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.FrontendConnection.rawHandle(FrontendConnection.java:478)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.FrontendConnection.handle(FrontendConnection.java:460)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.AbstractConnection.onReadData(AbstractConnection.java:321)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.NIOSocketWR.asynRead(NIOSocketWR.java:190)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.AbstractConnection.asynRead(AbstractConnection.java:273)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at io.mycat.net.NIOReactor$RW.run(NIOReactor.java:102)
INFO   | jvm 1    | 2016/12/09 11:26:40 |       at java.lang.Thread.run(Thread.java:722)
INFO   | jvm 1    | 2016/12/09 11:26:40 |  (io.mycat.server.ServerConnection:ServerConnection.java:281)

column alias, on aggregation function, support only `avg+(number)`

Raised by: @sevenFH
Steps:

Query: select avg(data) as avg1,avg(data) as avg_data from test1;
Found: avg1 value is correct, while avg_data value miss its dot

transaction (update+select) failed, it relates to autocommit

Raised by: @FlyingMao
Steps:

Prepare

create table aly_test (orderID int(11),R_REGIONKEY int(11) primary key,R_NAME varchar(50),R_COMMENT varchar(50));

insert into aly_test (orderID,R_REGIONKEY,R_NAME,R_COMMENT) values (1,1, 'Eastern','test001'),(3,3, 'Northern','test003'),(2,2, 'Western','test002'),(4,4, 'Southern','test004');

alter table aly_test engine=INNODB;

Execute transaction

begin;
update aly_test set R_COMMENT='25-989-741-4444' where orderID=1;
select * from aly_test;
commit;

Transaction was commited, but the data wasn't updated.

Log:

From general log:

there is no set autocommit=0 before update
there is set autocommit=0 before select
Subsequence queries on the same connection will be under autocommit=0

query min/max on date, will returns diff results on same query

Raised by: @sevenFH
Steps:

Create a table date_patch, whose id is date type.
Query select max(id) from date_patch twice, returns
1. 2017-11-28
2. 2017-11-10
Query select min(id) from date_patch twice, returns
1. 2016-12-21
2. 2016-12-13

not support `desc table` `show create table`

Raised by: @sevenFH
As titled.

select on sharding table, having clause is missing

Raised by: @sevenFH
Steps:

Query: select * from aly_test group by id having count(*) >= 1;
Check data-node general log, found: select * from aly_test group by id;. having clause is missing.

Set isolation level in configuration will fail

Raised by: @FlyingMao
Steps:

Change isolation level to 4(serializable) in configuration
Restart dble
Query select
Found:
1. no set isolation in data-node's general log
2. two transaction read/write same table will not be blocked, which is under lower isolation level.

join/union/subquery give wrong answer, the result was union of data return by each data-node

Raised by: @sevenFH
As titled.

[internal]

This issue is internal, and will not be migrated.

cannot create view

Raised by: @sevenFH
Steps:

SQL: create view view1 as select * from fixed_patch_uniform;
got error: ERROR 1064 (HY000): op table not in schema----VIEW

E-R, cannot insert data to sub and parent in a transaction, when sub-parentKey is parent-non-sharding-key

Raised by: @sevenFH
Steps:

parent sharding-key is p_id, sub sharding-key is f_id, sub join-key is f_id
Execute transaction:

begin
insert into parent_table (p_id, name, f_id) values ('008','aaa','008'); //add data to parent
insert into child_table (d_id, name, c_id) values (4,'aaa','008'); //add data to sub

there is error in wrapper.log

Load data with empty value '' makes wrong data

Raised by: @zhunina
Steps:

Execute

create table binary_columns(
   id int  primary key,
   c_binary binary(255) 
);

Load Data: load data local infile '/opt/loaddata/binary_columns' into table binary_columns fields terminated by ',' lines terminated by '\r\n' (id,c_binary);
(data is attached: binary_columns.zip )
Result:
1. 16 rows inserted, while 40 rows are expected
2. the data of id=19: c_binary is '000000000...000', while '' was expected

global table consistency check may fail when global table is not configured on all data-nodes

Raised by: @yanhuqing666
As titled.

make stress when dble is starting, server resource is exhaust, and client hangs

Raised by: @FlyingMao
Steps:

Prepare a vm with 4-core
Start dble
Sysbench 100-threads test with following lua:

function event(thread_id)
local table_name
local a=sb_rand(1, oltp_table_size-1)
local b=a+1
table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
rs = db_query("SELECT max(pad) from " .. table_name .. " where id between " .. a .. " and " ..b)
end

Found:
1. dble use up to 400% cpu
2. sysbench hang

support insert without column definitions

Raised by: @yanhuqing666
Route could be calculated by meta data of data-nodes

crash when query aggregation with where

Raised by: @FlyingMao
Steps:

sysbench query: select sum(id) from sbtest1 where id between a and a+3; (a is random number between 1-50000000)
run sysbench:

./sysbench --test=/usr/local/sysbench-1.0/db/select32.lua --mysql-user=test --mysql-password=test --mysql-port=8066 --mysql-host=10.186.23.70 --oltp_auto_inc=off --max-time=300 --max-requests=0 --report-interval=10 --num-threads=1 run

after a while, dble crashed: Error in Java ：double free or cprruption(out):0x00007fcc9c121360

support `allowMultiQueries=true`

Raised by: @yanhuqing666
As titled.

implicit transaction consistency may fail

Raised by: @yanhuqing666
data consistency may fail between data-nodes in implicit transaction

E-R, cannot insert multi records to sub-table

Raised by: @sevenFH
Steps:

Insert data to E-R sub-table: insert into district (d_w_id, number, name) values (001,2,'aaa'),(002,3,'bbb');
Got error: ERROR 1064 (HY000): ChildTable multi insert not provided

select doesn't support 'keyword' format

Raised by: @sevenFH
As titled.

Global table with consistency check, `insert ...on duplicate...` doesn't update `_mycat_op_time`

Raised by: @yanhuqing666
Steps:

Global table with consistency check, query insert ...on duplicate..., when there is duplication,
_mycat_op_time isn't updated.

mysql.connecter driver failed, because `SET @@session.autocommit = ON` is not supported

Raised by: @sevenFH
As titled.

alter table failed for global table

Raised by: @sevenFH

Steps:

Make a global table called global_table
Following queries will fail with error: ERROR 1064 (HY000): op table not in schema----GLOBAL_TABLE DROP|ADD|CHANGE
1. alter table global_table drop name; //drop column
2. alter table global_table add name1 char(10); //add column
3. alter table global_table change name name char(15); //alter column type
4. alter table global_table change name name1 varchar(10); //update column name
5. alter table global_table drop index global_table_inx; //drop index
Above queries succeed on sharding table

Null is retreated as 0, when aggregating, which is not correct.

Raised by: @yanhuqing666
Steps:

2 data-nodes, a is empty while b is not:

mysql> select * from char_columns;
+----+--------+
| id | c_char |
+----+--------+
|  1 | 512    |
+----+--------+
1 row in set (0.01 sec)

Aggregation is not correct if a returns NULL

mysql> select min(id) from char_columns;
+------+
| MIN0 |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

insert failed, when dble is case-nonsensitive and MySQL is case-sensitive

Raised by: @FlyingMao
Steps:

MySQL set lower_case_table_names=0
Dble use default configuration
Create table:

CREATE TABLE sbtest1 (
id int(10) unsigned NOT NULL,
k int(10) unsigned NOT NULL DEFAULT '0',
c char(120) NOT NULL DEFAULT '',
pad int(11) NOT NULL,
PRIMARY KEY (id),
KEY k_1 (k)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query: insert into sbtest1 values (1,1,1,1),(2,2,2,2);
Returns: Error 1064（HY000）：can't find table [SBTEST1] define in schema:sbtest

user schema remains case-sensitive, even dble is configured as case-nonsensitive

Raised by: @sunsun314
schemas works find with dble case-sensitive settings, except user schema which is specified when login.

[internal]

This issue is internal, and will not be migrated.

Aggregation function only support `count()` `sum()` `max()` `min()` `avg()`

Raised by: @sevenFH

`start transaction;` and hang

Raised by: @FlyingMao
As titled.

cannot insert data without column definitions, to dble-created table

Raised by: @sevenFH
Log:

2016-12-16 14:50:14.061  WARN [$_NIOREACTOR-1-RW] (io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:299)) - ServerConnection [id=1, schema=TESTDB, host=10.186.23.12, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into enum_patch_integer values (1,1,'computer') err:java.sql.SQLNonTransientException: bad insert sql (sharding column:ID not provided,INSERT INTO enum_patch_integer
VALUES (1, 1, 'computer')
java.sql.SQLNonTransientException: bad insert sql (sharding column:ID not provided,INSERT INTO enum_patch_integer
VALUES (1, 1, 'computer')
        at io.mycat.route.parser.druid.impl.DruidInsertParser.parserSingleInsert(DruidInsertParser.java:162) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.route.parser.druid.impl.DruidInsertParser.statementParse(DruidInsertParser.java:70) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.route.parser.druid.impl.DefaultDruidParser.parser(DefaultDruidParser.java:64) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.route.impl.DruidMycatRouteStrategy.routeNormalSqlWithAST(DruidMycatRouteStrategy.java:79) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:81) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.route.RouteService.route(RouteService.java:133) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:291) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.server.ServerConnection.execute(ServerConnection.java:240) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:144) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.FrontendConnection.query(FrontendConnection.java:324) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.FrontendConnection.query(FrontendConnection.java:344) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:71) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.FrontendConnection.rawHandle(FrontendConnection.java:500) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.FrontendConnection.handle(FrontendConnection.java:482) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.AbstractConnection.onReadData(AbstractConnection.java:322) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.NIOSocketWR.asynRead(NIOSocketWR.java:190) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.AbstractConnection.asynRead(AbstractConnection.java:274) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at io.mycat.net.NIOReactor$RW.run(NIOReactor.java:102) ~[Action_Mycat-0.2.1-DEV.jar:?]
        at java.lang.Thread.run(Thread.java:745) [?:1.7.0_79]

Performance is bad, for short connections with concurrency, and log level is INFO

Raised by: helingyun
Steps:

Prepare environment: 2 data-nodes, 2 tables on each data-node, 50000000 rows in each table
Sysbench makes 500 threads, each thread queries 1000 sqls: select * from xxx where id=?
Found performance is bad, comparing with log level WARN

actiontech / dble Goto Github PK

dble's People

Contributors

Stargazers

Watchers

Forkers

dble's Issues

Recommend Projects

Recommend Topics

Recommend Org