In mysql, count() will return syntax err

mysql> select count() from tb1; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') from tb1' at line 1

In radon the return info is:

mysql>  select count() from tb3;
ERROR 2013 (HY000): Lost connection to MySQL server during query

The error occur at radon/src/planner/expr.go:141 when get ex.Exprs[0] not exists, then report runtime error: index out of range.

1. execute this sql on backend mysql-server with no error
   but execute on radon-db-server ,it does not work!
   my english is not so good, did i make myself clear?

mysql> insert into pk_card_enter(cid,id,ltd_code,park_code,mac_code,card_id,type,car_no,acc_time,enter_type,status,action_time,video_no,create_date,door_name,upseason,reader_addr,feeseason,in_car_type,operator,park_name,card_type,in_time,leave_time,is_handset,is_img,phone_in_password,phone_no,file_path,is_hardware,is_csmain,is_subparking,sub_parking,card_car_no,eq_card_no,is_effective,card_sn_id,is_carno_matching,liquidating_code,liquidating_mark,standar_acc_time,up_time) values(111147361,'7bc0087d-0bb5-4cce-81b8-bb9187f9ab19','0001','00010001','000100010001','12816503',2,'粤AS885P','2016-12-01 08:21:30',1,1,'2016-12-01 08:21:30','00010001D0001','2016-12-01 08:21:13','车场入口','2','1','','','刘风连','三寓宾馆停车场','月租卡','2016-12-01 08:21:30',NULL,0,1,NULL,'','20161201/08/2016120108213012816503.jpg',0,1,0,NULL,'粤AS885P',NULL,NULL,'',1,NULL,0,'2016-12-01 08:21:11','2016-12-19 18:36:19');

ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 108 near 'status'
mysql>

如题

Summary

Create two tables tb1 and tb2, such as:

CREATE TABLE `tb1` (
 `a` int, 
 `b` varchar(255),  
 PRIMARY KEY  (`a`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `tb2` (
 `c` int, 
 `d` char,  
 PRIMARY KEY  (`c`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8
 PARTITION BY HASH(`c`);

Without partition by hash, tb1 will be a global table (or broadcast table). The global table has full data at ervery backend, it can support the join with a partition table. Such as:

SELECT tb1.a,tb1.b FROM tb1 JOIN tb2 ON tb1.a=tb2.c WHERE tb2.d='NanJing'

The sql can be distributed to the backends. The datas from the backends gather on the radon.

The global tables are generally used for tables with fewer changes and smaller capacity, requiring frequent association with other tables.

mysql> SHOW FULL TABLES;
ERROR 1105 (HY000): unsupported.query:SHOW FULL TABLES

the SQL used by Navicat 12.1.12

Thank you for your open source this great product, but I can't find some docs about how to install radon.

As it said in title.

i try insert 100 record. use transaction.
sqlconnection close function, catch error.

error:
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 9 near 'rollback'

1. 参考文档，rest-api的确可以实现给后端MySQL添加本地用户，但radon只能在本机访问
   报错如下：
   2018/05/15 17:17:45.892306 auth.go:139: [ERROR] proxy: auth.user[root].failed(password.invalid):want[[89 223 240 70 44 7 2 202 154 74 149 248 53 42 238 70 192 194 109 206]]!=got[[77 34 129 174 3 224 34 45 152 251 202 58 202 169 175 254 225 178 11 148]]
   2018/05/15 17:17:45.892361 server.go:153: [WARNING] server.user[root].auth.check.failed

Hi,

I have just tried with radondb, it worked fine with the normal database creation but not for extra options. See below:

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql>  create database test1 character set utf8 collate utf8_general_ci;
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 32 near 'character'`

RadonDB's output log:

2018/05/16 10:09:09.701490 query.go:63:    	 [ERROR] 	query[create database test1 character set utf8 collate utf8_general_ci].parser.error: syntax error at position 32 near 'character'
2018/05/16 10:09:09.701532 server.go:204:    	 [ERROR] 	server.handle.query.from.session[2].error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 32 near 'character' (errno 1149) (sqlstate 42000).query[create database test1 character set utf8 collate utf8_general_ci]

This command works fine on normal mysql instance. What can be a problem?

Summary

For supporting myloader, we add dummy 'START TRANSACTION' statement in query.go:
https://github.com/radondb/radon/blob/master/src/proxy/query.go#L232

Although radon returns a WARNING, but user may confused here.

$ make build
fatal: No names found, cannot describe anything.
fatal: No names found, cannot describe anything.
--> Building...
go build -v -o bin/xenon --ldflags '-X "build.tag=" -X "build.time=2018/05/11 12:18:19" -X "build.git=658d7f1"' src/xenon/xenon.go

Fedora 28 x86_64 w/ go version go1.10.1 linux/amd64

Is there some design doc for implementation of Snapshot Isolation ?

Queue commit action?

// CommitLock used to acquire the commit.
func (mgr *TxnManager) CommitLock() {
	mgr.commitLock.Lock()
}

Firstly, consider supporting union between global tables.

Xenon cluster

mysql -uroot -h168.5.80.102 -P3306 -p123456
It's OK

Single-node radon

Add backend1 Xenon cluster

curl -i -H 'Content-Type: application/json' -X POST -d '{"name": "backend1", "address": "168.5.80.102:3306", "user":"root", "password": "123456", "max-connections":1024}' http://127.0.0.1:8080/v1/radon/backend

add user

curl -i -H 'Content-Type: application/json' -X POST -d '{"user": "radon", "password": "radon"}' http://127.0.0.1:8080/v1/user/add

add allowIP

curl -i -H 'Content-Type: application/json' -X PUT -d '{"allowip":["my client ip"]}' http://127.0.0.1:8080/v1/radon/config

Mysql Client

mysql -uradon -h168.5.80.92 -P3308 -pradon
It's OK.
when i use navicat connect to the db，The error has appeared

 2018/08/31 16:02:58.868561 router.go:163:       [ERROR]      
 router.can.not.find.db[information_schema]
 2018/08/31 16:02:58.868606 query.go:225:        [ERROR]
 proxy.select[SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA].from.session[8].error:Table 'information_schema.SCHEMATA' doesn't exist (errno 1146) (sqlstate 42S02)
 2018/08/31 16:02:58.868628 server.go:204:       [ERROR]        
 server.handle.query.from.session[8].error:Table 'information_schema.SCHEMATA' doesn't exist (errno 1146) (sqlstate 42S02).query[SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA]

And
mysql> use information_schema;
ERROR 1227 (42000): Access denied; lacking privileges for database information_schema

I am confused with it ：）

To improve code reliability, the code coverage of every file in radon should not be less than 95 percent！

thank you.

Summary

Radon has support the global table for users to do better(small glboal table join the large partition table) join(#101), we will deprecate the binlog relay in later milestone(v1.0.5).

We just deprecate the relay logic from Radon, the binlog still can relay from other drivers(show binlog events) if you want.

The codes are:
https://github.com/radondb/radon/blob/master/src/proxy/relay.go

make lint

#go get -u golang.org/x/tools/cmd/golint
golint src src/monitor src/proxy src/proxy/xacheck src/optimizer src/config src/planner src/backend src/backend/radon-meta src/cli src/cli/cmd src/cli/cmd/xacheck src/xcontext src/xbase src/xbase/sync2 src/xbase/stats src/audit src/radon src/binlog src/executor src/build src/syncer src/ctl src/ctl/v1 src/ctl/v1/xacheck src/fakedb src/vendor src/router
#go get -u -v github.com/golangci/golangci-lint/cmd/golangci-lint
golangci-lint run
ERRO Running error: context loading failed: failed to load program with go/packages: go [list -e -json -compiled -test=true -export=false -deps=true -- ./...]: exit status 1: go build golang.org/x/tools/go/ast/astutil: no Go files in
go build golang.org/x/tools/go/gcexportdata: no Go files in
go build golang.org/x/lint: no Go files in
go build golang.org/x/tools/go/buildutil: no Go files in
go build golang.org/x/tools/go/loader: no Go files in
make: *** [lint] Error 3

• https://github.com/go-critic/go-critic ( https://go-critic.github.io/overview )
  & https://github.com/golangci/golangci-lint
• https://github.com/mgechev/revive
• https://github.com/alecthomas/gometalinter

Summary

Add go-fuzz corpus for checking sqlparser and radon:
https://github.com/dvyukov/go-fuzz-corpus/tree/master/sqlparser

• https://goreleaser.com ( https://github.com/goreleaser/goreleaser )
• https://docs.travis-ci.com/user/customizing-the-build/#deploying-your-code

Summary

makefile have add make check to check the coce via gometalinter&megacheck, it's unhappy with:

 make check 
go get -v gopkg.in/alecthomas/gometalinter.v2
go get -v honnef.co/go/tools/cmd/megacheck
bin/gometalinter.v2 -j 4 --disable-all \
--enable=gofmt \
--enable=golint \
--enable=vet \
--enable=gosimple \
--enable=unconvert \
--deadline=10m xbase ctl/v1/ xcontext config router optimizer planner executor backend proxy audit syncer binlog monitor 2>&1 | tee /dev/stderr
bin/megacheck xbase ctl/v1/ xcontext config router optimizer planner executor backend proxy audit syncer binlog monitor  2>&1 | tee /dev/stderr
src/proxy/query.go:258:6: this value of qr is never used (SA4006)
src/proxy/query.go:258:6: this value of qr is never used (SA4006)
src/backend/xacheck.go:32:2: const xaRedoError is unused (U1000)
src/backend/xacheck.go:33:2: const xaRedoOk is unused (U1000)
src/backend/xacheck.go:32:2: const xaRedoError is unused (U1000)
src/backend/xacheck.go:33:2: const xaRedoOk is unused (U1000)

Hi,
sorry but .. where can I found the docs and source for RadonDb ?
Thank you

In addition to joins between global tables or between global tables and shard tables, distributed joins that support push-down can be implemented in the following two cases.

1. Tables participating in JOIN all have shard keys as conditions in filtering conditions,  
the data calculated by shard algorithm is distributed on the same partition。

2. Tables participating in JOIN all use shard keys as JOIN conditions, and have same 
shard partitions。

mysql> select @@version_comment limit 1;
ERROR 1046 (3D000): No database selected

log:
2018/05/30 17:52:57.504176 query.go:209: [ERROR] proxy.select[select @@version_comment limit 1].from.session[2].error:No database selected (errno 1046) (sqlstate 3D000)
2018/05/30 17:52:57.504238 server.go:204: [ERROR] server.handle.query.from.session[2].error:No database selected (errno 1046) (sqlstate 3D000).query[select @@version_comment limit 1]

is similar to cmd: select 1;

the xa is invalid: insert some values return ok, but some values is not successful when the twopc-enable is true

the client insert some values, run long time and return ok but some values is not successful,
when one backend is lost and some values on the backend node ,at the same time the twopc-enable is true

mysql> insert a(i,j) values(3,3),(7,7);
Query OK, 2 rows affected (2 min 11.95 sec)

the value (3,3) on the backend node1;
the value (7,7) on the backend node2;

When executing the cmd, the backend node2 is lost, the value (7,7) is not successful, but the client which waiting for a long time got the Query OK.

Considering that the value of peer-address in the production environment is generally not 127.0.0.1:8080, add a parameter --radon-host to cli.

the SQL used by Navicat

mysql> show table status;
ERROR 1105 (HY000): unsupported.query:show table status

For easier access to user lists, instead of using select statements to connect to the database, so add an API /v1/user/userz.

After execute command use db successfully, some error logs appear:

2018/06/06 16:14:55.889670 server.go:212:       [ERROR]    session.command:COM_FIELD_LIST.not.implemented
 2018/06/06 16:14:55.889799 server.go:212:       [ERROR]    session.command:COM_FIELD_LIST.not.implemented
 2018/06/06 16:14:55.889904 server.go:212:       [ERROR]    session.command:COM_FIELD_LIST.not.implemented
 2018/06/06 16:14:55.889996 server.go:212:       [ERROR]    session.command:COM_FIELD_LIST.not.implemented
 2018/06/06 16:14:55.890112 server.go:212:       [ERROR]    session.command:COM_FIELD_LIST.not.implemented
 2018/06/06 16:14:55.890181 server.go:212:       [ERROR]    session.command:COM_FIELD_LIST.not.implemented

Summary

surpport DQL like :

1. table alias : SELECT table_alias.column FROM table AS table_alias
2. fields alias : SELECT column AS column_alias FROM table
   and
3. currently radon doesn`t support status like:
   SELECT sessionid,userid,lastaccess FROM sessions WHERE status='1';

1.部署方式建议采用ansible来部署提高部署效率。
2.要有图形化的监控方式。
3.目前是否支持表空间的在线收缩和重平衡。
4.数据倾斜和热点数据的问题如何解决，有没比较好的处理方式。
5.文档需要完善。

Summary

Support distributed multi-statement transactions like:

begin;
sql1;
sql2;
commit;

We also based on MySQL XA transaction:

-- begin phase
xa start;  

-- statements 
sql1;
sql2;

 -- commit phase
xa end;
xa prepare;
xa commit;

user.go:44 line,why localhost?

query := fmt.Sprintf("GRANT SELECT ON *.* TO '%s'@'localhost' IDENTIFIED BY '%s'", p.User, p.Password)

error log

 2018/05/12 21:10:25.418202 user.go:42:    	 [WARNING] 	api.v1.create.user[from:127.0.0.1:8083].[{root test}]
 2018/05/12 21:10:25.419886 connection.go:198:    	 [ERROR] 	conn[10.0.2.44:3306].execute[GRANT SELECT ON *.* TO 'root'@'localhost' IDENTIFIED BY 'test'].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)
 2018/05/12 21:10:25.419924 txn.go:412:    	 [ERROR] 	txn.execute.on[10.0.2.44:3306].query[GRANT SELECT ON *.* TO 'root'@'localhost' IDENTIFIED BY 'test'].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)
 2018/05/12 21:10:25.420007 connection.go:198:    	 [ERROR] 	conn[10.0.2.43:3306].execute[GRANT SELECT ON *.* TO 'root'@'localhost' IDENTIFIED BY 'test'].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)
 2018/05/12 21:10:25.420033 txn.go:412:    	 [ERROR] 	txn.execute.on[10.0.2.43:3306].query[GRANT SELECT ON *.* TO 'root'@'localhost' IDENTIFIED BY 'test'].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)
 2018/05/12 21:10:25.420195 connection.go:198:    	 [ERROR] 	conn[10.0.2.42:3306].execute[GRANT SELECT ON *.* TO 'root'@'localhost' IDENTIFIED BY 'test'].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)
 2018/05/12 21:10:25.420215 txn.go:412:    	 [ERROR] 	txn.execute.on[10.0.2.42:3306].query[GRANT SELECT ON *.* TO 'root'@'localhost' IDENTIFIED BY 'test'].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)
 2018/05/12 21:10:25.420601 user.go:46:    	 [ERROR] 	api.v1.create.user[{User:root Password:test}].error:Access denied for user 'root'@'10.0.2.40' (using password: YES) (errno 1045) (sqlstate 28000)

please provide more detail wifi, example install wiki.

thank you.

case: create a table and the partition key is a, but primary key is b

mysql> create table test(a int, b char, primary key(b)) partition by hash(a);
Query OK, 0 rows affected (0.42 sec)

2018/05/11 20:30:32.971037 admin.go:56: [PANIC] listen tcp :8080: bind: address already in use
panic: [PANIC] listen tcp :8080: bind: address already in use

hard code in radon/src/ctl/admin.go

admin.server = &http.Server{Addr: ":8080", Handler: handlers}

$ cat bin/radon.default.json
{
"proxy": {
"endpoint": ":3308",
"meta-dir": "bin/radon-meta",
"peer-address": "127.0.0.1:9080"
},
"binlog": {
"binlog-dir": "bin/radon-binlog"
},
"audit": {
"audit-dir": "bin/radon-audit"
},
"log": {
"level": "WARNING"
}
}

proxy: auth.error:Table 'mysql.user' doesn't exist ？？？？~~

mysql> select DATE_FORMAT(create_date,'%Y%m%d') days,sum(type) as parkfee from pk_card_enter where create_date between '2017-04-22 00:00:00' and '2017-05-23 00:00:00' group by days;
ERROR 1105 (HY000): unsupported: function:DATE_FORMAT
mysql>

Hi there, I see lot of similarities with vitess from youtube, how is it different ?

using report error

mysql> select * from (select * from tb1) as t1 join (select * from tb2) as t2 using(a);
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 77 near 'using'

subquery report info wrong

mysql> select * from (select * from tb1) as tb1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

having unsupport expr

mysql> select    min(Year), max(Year), Carrier, count(*) as cnt,    sum(ArrDelayMinutes>30) as flights_delayed,    round(sum(ArrDelayMinutes>30)/count(*),2) as rate FROM ontime WHERE    DayOfWeek not in (6,7) and OriginState not in ('AK', 'HI', 'PR', 'VI')    and DestState not in ('AK', 'HI', 'PR', 'VI')    and FlightDate < '2010-01-01' GROUP by Carrier HAVING cnt > 100000 and max(Year) > 1990 ORDER by rate DESC LIMIT 1000;
ERROR 1105 (HY000): unsupported: expr[max(`year`)].in.having.clause

unsupport function

round

mysql> select    min(Year), max(Year), Carrier, count(*) as cnt,    sum(ArrDelayMinutes>30) as flights_delayed,    round(sum(ArrDelayMinutes>30)/count(*),2) as rate FROM ontime WHERE    DayOfWeek not in (6,7) and OriginState not in ('AK', 'HI', 'PR', 'VI')    and DestState not in ('AK', 'HI', 'PR', 'VI')    and FlightDate < '2010-01-01' GROUP by Carrier HAVING cnt > 100000  ORDER by rate DESC LIMIT 1000;     
ERROR 1105 (HY000): unsupported: function:round

unsupport distinct

mysql> SELECT distinct DestCityName, count(OriginCityName) AS u FROM ontime WHERE Year >= 2000 and Year <= 2010 GROUP BY DestCityName ORDER BY u DESC LIMIT 10;
ERROR 1105 (HY000): unsupported: distinct
mysql> SELECT DestCityName, count(distinct OriginCityName) AS u FROM ontime WHERE Year >= 2000 and Year <= 2010 GROUP BY DestCityName ORDER BY u DESC LIMIT 10;         
ERROR 1105 (HY000): unsupported: distinct.in.function:count

unsupport load data infile into table

mysql> load data infile "/data/mysql/e.sql" into table e fields terminated by ',';
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 5 near 'load'

count() report info wrong

mysql>  select count() from tb3;
ERROR 2013 (HY000): Lost connection to MySQL server during query

where clause with tablename report err when delete shard table

mysql> delete from tb3 where tb3.b='d';
ERROR 1054 (42S22): Unknown column 'tb3.b' in 'where clause'

unsupport join ... using

mysql> select * from tb1 join tb2 using(a,a);
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use, syntax error at position 33 near 'using'

In MySQL, when we execute:

xa start;
sql;
xa end; -- if error, we cant do rollback
xa prepare; -- if error, we cant do rollback

Remove Rollback phase when xaEnd/xaPrepare error.

make testsyncer failed in mac to the commit id 4b6377e:
in ubuntu just succeed

make testsyncer
go test -v -race syncer

=== RUN TestMetaVersionCheck
2018/08/29 17:27:20.665576 mock.go:83: [PANIC] syncer.mock.check.sha.error:oldsha1[[13 130 71 83 146 111 171 52 28 28 65 71 228 71 235 240 155 188 73 50]],sha1:[[117 72 151 60 187 148 53 147 163 224 218 32 226 176 66 65 218 85 219 132]]
--- FAIL: TestMetaVersionCheck (2.36s)
Error Trace: meta_test.go:126
Error: Should be true

panic: [PANIC] syncer.mock.check.sha.error:oldsha1[[13 130 71 83 146 111 171 52 28 28 65 71 228 71 235 240 155 188 73 50]],sha1:[[117 72 151 60 187 148 53 147 163 224 218 32 226 176 66 65 218 85 219 132]] [recovered]
panic: [PANIC] syncer.mock.check.sha.error:oldsha1[[13 130 71 83 146 111 171 52 28 28 65 71 228 71 235 240 155 188 73 50]],sha1:[[117 72 151 60 187 148 53 147 163 224 218 32 226 176 66 65 218 85 219 132]]

goroutine 25 [running]:
testing.tRunner.func1(0xc4201843c0)
/usr/local/Cellar/go/1.10.2/libexec/src/testing/testing.go:742 +0x567
panic(0x1597c80, 0xc4200a9220)
/usr/local/Cellar/go/1.10.2/libexec/src/runtime/panic.go:502 +0x24a
vendor/github.com/xelabs/go-mysqlstack/xlog.(*Log).Panic(0xc4200a92b0, 0x164a1dc, 0x33, 0xc420241ea8, 0x2, 0x2)
/Users/andy/go/src/radon/src/vendor/github.com/xelabs/go-mysqlstack/xlog/xlog.go:164 +0x202
syncer.mockSyncer.func2()
/Users/andy/go/src/radon/src/syncer/mock.go:83 +0x4e4
syncer.TestMetaVersionCheck(0xc4201843c0)
/Users/andy/go/src/radon/src/syncer/meta_test.go:127 +0x280
testing.tRunner(0xc4201843c0, 0x1652d38)
/usr/local/Cellar/go/1.10.2/libexec/src/testing/testing.go:777 +0x16e
created by testing.(*T).Run
/usr/local/Cellar/go/1.10.2/libexec/src/testing/testing.go:824 +0x565
FAIL syncer 2.402s
make: *** [testsyncer] Error

比如src/binlog/binlog/go文件中

import (
	"config"
	"os"
	"path"
	"path/filepath"
	"sync"
	"time"
	"xbase"
	"xbase/sync2"

	"github.com/xelabs/go-mysqlstack/xlog"
)

存在引用当前$GOPATH下面的package config等等
有存在github项目github.com/xelabs/go-mysqlstack/xlog

缺点：
标准库和自己的库没有区分开
别人无法引用radon项目存在的库

个人建议

import (
	"os"
	"path"
	"path/filepath"
	"sync"
	"time"

        "github.com/radondb/radon/config"
	"github.com/radondb/radon/xbase"
	"github.com/radondb/radon/xbase/sync2"

	"github.com/xelabs/go-mysqlstack/xlog"
)

In the doc Add a backend(mysql server) to radon step:
https://github.com/radondb/radon/blob/master/docs/how_to_build_and_run_radon.md#step4-add-a-backendmysql-server-to-radon

It's confused for people to know the backend user is for MySQL connection or Radon, we should make it clearly.