// Execute a SQL
func (c *Canal) Execute(cmd string, args ...interface{}) (rr *mysql.Result, err error) {
c.connLock.Lock()
defer c.connLock.Unlock()
retryNum := 3
for i := 0; i < retryNum; i++ {
if c.conn == nil {
c.conn, err = client.Connect(c.cfg.Addr, c.cfg.User, c.cfg.Password, "")
if err != nil {
return nil, err
}
}
rr, err = c.conn.Execute(cmd, args...)
if err != nil && err != mysql.ErrBadConn {
return
} else if err == mysql.ErrBadConn {
c.conn.Close()
c.conn = nil
continue
} else {
return
}
}
return
}
// DSN user:password@addr[?db]
func (d driver) Open(dsn string) (sqldriver.Conn, error) {
seps := strings.Split(dsn, "@")
if len(seps) != 2 {
return nil, fmt.Errorf("invalid dsn, must user:password@addr[?db]")
}
var user string
var password string
var addr string
var db string
if ss := strings.Split(seps[0], ":"); len(ss) == 2 {
user, password = ss[0], ss[1]
} else if len(ss) == 1 {
user = ss[0]
} else {
return nil, fmt.Errorf("invalid dsn, must user:password@addr[?db]")
}
if ss := strings.Split(seps[1], "?"); len(ss) == 2 {
addr, db = ss[0], ss[1]
} else if len(ss) == 1 {
addr = ss[0]
} else {
return nil, fmt.Errorf("invalid dsn, must user:password@addr[?db]")
}
c, err := client.Connect(addr, user, password, db)
if err != nil {
return nil, err
}
return &conn{c}, nil
}
func (t *testSyncerSuite) setupTest(c *C, flavor string) {
var port uint16 = 3306
switch flavor {
case mysql.MariaDBFlavor:
port = 3316
}
t.flavor = flavor
var err error
if t.c != nil {
t.c.Close()
}
t.c, err = client.Connect(fmt.Sprintf("%s:%d", *testHost, port), "root", "", "")
if err != nil {
c.Skip(err.Error())
}
// _, err = t.c.Execute("CREATE DATABASE IF NOT EXISTS test")
// c.Assert(err, IsNil)
_, err = t.c.Execute("USE test")
c.Assert(err, IsNil)
if t.b != nil {
t.b.Close()
}
t.b = NewBinlogSyncer(100, flavor)
err = t.b.RegisterSlave(*testHost, port, "root", "")
c.Assert(err, IsNil)
}
func hello(c web.C, w http.ResponseWriter, r *http.Request) {
conn, _ := client.Connect("127.0.0.1:3306", "root", "", "tel_project_feb_2016")
//conn.Ping()
res, _ := conn.Execute(`insert into tel_res (type) values (` + string(c.URLParams["type"]) + `)`)
flag := 0
if res.InsertId > 0 {
flag = 1
}
//if err != nil{
// fmt.Println(err)
//}
fmt.Fprint(w, flag)
}
func (b *BinlogSyncer) registerSlave() error {
if b.c != nil {
b.c.Close()
}
log.Infof("register slave for master server %s:%d", b.cfg.Host, b.cfg.Port)
var err error
b.c, err = client.Connect(fmt.Sprintf("%s:%d", b.cfg.Host, b.cfg.Port), b.cfg.User, b.cfg.Password, "")
if err != nil {
return errors.Trace(err)
}
//for mysql 5.6+, binlog has a crc32 checksum
//before mysql 5.6, this will not work, don't matter.:-)
if r, err := b.c.Execute("SHOW GLOBAL VARIABLES LIKE 'BINLOG_CHECKSUM'"); err != nil {
return errors.Trace(err)
} else {
s, _ := r.GetString(0, 1)
if s != "" {
// maybe CRC32 or NONE
// mysqlbinlog.cc use NONE, see its below comments:
// Make a notice to the server that this client
// is checksum-aware. It does not need the first fake Rotate
// necessary checksummed.
// That preference is specified below.
if _, err = b.c.Execute(`SET @master_binlog_checksum='NONE'`); err != nil {
return errors.Trace(err)
}
// if _, err = b.c.Execute(`SET @master_binlog_checksum=@@global.binlog_checksum`); err != nil {
// return errors.Trace(err)
// }
}
}
if err = b.writeRegisterSlaveCommand(); err != nil {
return errors.Trace(err)
}
if _, err = b.c.ReadOKPacket(); err != nil {
return errors.Trace(err)
}
return nil
}
func (s *schemaTestSuite) SetUpSuite(c *C) {
var err error
s.conn, err = client.Connect(fmt.Sprintf("%s:%d", *host, *port), "root", "", "")
c.Assert(err, IsNil)
s.d, err = NewDumper(*execution, fmt.Sprintf("%s:%d", *host, *port), "root", "")
c.Assert(err, IsNil)
c.Assert(s.d, NotNil)
s.d.SetErrOut(os.Stderr)
_, err = s.conn.Execute("CREATE DATABASE IF NOT EXISTS test1")
c.Assert(err, IsNil)
_, err = s.conn.Execute("CREATE DATABASE IF NOT EXISTS test2")
c.Assert(err, IsNil)
str := `CREATE TABLE IF NOT EXISTS test%d.t%d (
id int AUTO_INCREMENT,
name varchar(256),
PRIMARY KEY(id)
) ENGINE=INNODB`
_, err = s.conn.Execute(fmt.Sprintf(str, 1, 1))
c.Assert(err, IsNil)
_, err = s.conn.Execute(fmt.Sprintf(str, 2, 1))
c.Assert(err, IsNil)
_, err = s.conn.Execute(fmt.Sprintf(str, 1, 2))
c.Assert(err, IsNil)
_, err = s.conn.Execute(fmt.Sprintf(str, 2, 2))
c.Assert(err, IsNil)
str = `INSERT INTO test%d.t%d (name) VALUES ("a"), ("b"), ("\\"), ("''")`
_, err = s.conn.Execute(fmt.Sprintf(str, 1, 1))
c.Assert(err, IsNil)
_, err = s.conn.Execute(fmt.Sprintf(str, 2, 1))
c.Assert(err, IsNil)
_, err = s.conn.Execute(fmt.Sprintf(str, 1, 2))
c.Assert(err, IsNil)
_, err = s.conn.Execute(fmt.Sprintf(str, 2, 2))
c.Assert(err, IsNil)
}
func (t *testSyncerSuite) TestSync(c *C) {
var err error
t.c, err = client.Connect(fmt.Sprintf("%s:%d", *testHost, *testPort), *testUser, *testPassword, "test")
c.Assert(err, IsNil)
err = t.b.RegisterSlave(*testHost, uint16(*testPort), *testUser, *testPassword)
c.Assert(err, IsNil)
//get current master binlog file and position
r, err := t.c.Execute("SHOW MASTER STATUS")
c.Assert(err, IsNil)
binFile, _ := r.GetString(0, 0)
s, err := t.b.StartSync(binFile, uint32(4))
c.Assert(err, IsNil)
t.testSync(c, s)
}
func (t *testSyncerSuite) TestSyncGTID(c *C) {
var err error
t.c, err = client.Connect(fmt.Sprintf("%s:%d", *testGTIDHost, *testGTIDPort), *testGTIDUser, *testGITDPassword, "test")
c.Assert(err, IsNil)
err = t.b.RegisterSlave(*testGTIDHost, uint16(*testGTIDPort), *testGTIDUser, *testGITDPassword)
c.Assert(err, IsNil)
masterUuid, err := t.b.GetMasterUUID()
c.Assert(err, IsNil)
set := new(GTIDSet)
set.Sets = []*UUIDSet{NewUUIDSet(masterUuid, Interval{1, 2})}
s, err := t.b.StartSyncGTID(set)
c.Assert(err, IsNil)
t.testSync(c, s)
}
func (t *testSyncerSuite) setupTest(c *C, flavor string) {
var port uint16 = 3306
switch flavor {
case mysql.MariaDBFlavor:
port = 3316
}
t.flavor = flavor
var err error
if t.c != nil {
t.c.Close()
}
t.c, err = client.Connect(fmt.Sprintf("%s:%d", *testHost, port), "root", "", "")
if err != nil {
c.Skip(err.Error())
}
// _, err = t.c.Execute("CREATE DATABASE IF NOT EXISTS test")
// c.Assert(err, IsNil)
_, err = t.c.Execute("USE test")
c.Assert(err, IsNil)
if t.b != nil {
t.b.Close()
}
cfg := BinlogSyncerConfig{
ServerID: 100,
Flavor: flavor,
Host: *testHost,
Port: port,
User: "******",
Password: "",
}
t.b = NewBinlogSyncer(&cfg)
}
func (s *Server) Execute(cmd string, args ...interface{}) (r *Result, err error) {
retryNum := 3
for i := 0; i < retryNum; i++ {
if s.conn == nil {
s.conn, err = client.Connect(s.Addr, s.User.Name, s.User.Password, "")
if err != nil {
return nil, err
}
}
r, err = s.conn.Execute(cmd, args...)
if err != nil && ErrorEqual(err, ErrBadConn) {
return
} else if ErrorEqual(err, ErrBadConn) {
s.conn = nil
continue
} else {
return
}
}
return
}
func (s *schemaTestSuite) SetUpSuite(c *C) {
var err error
s.conn, err = client.Connect(fmt.Sprintf("%s:%d", *host, 3306), "root", "", "test")
c.Assert(err, IsNil)
}
func (s *riverTestSuite) SetUpSuite(c *C) {
var err error
s.c, err = client.Connect(*my_addr, "root", "", "test")
c.Assert(err, IsNil)
s.testExecute(c, "SET SESSION binlog_format = 'ROW'")
schema := `
CREATE TABLE IF NOT EXISTS %s (
id INT,
title VARCHAR(256),
content VARCHAR(256),
mylist VARCHAR(256),
tenum ENUM("e1", "e2", "e3"),
tset SET("a", "b", "c"),
PRIMARY KEY(id)) ENGINE=INNODB;
`
s.testExecute(c, "DROP TABLE IF EXISTS test_river")
s.testExecute(c, fmt.Sprintf(schema, "test_river"))
for i := 0; i < 10; i++ {
table := fmt.Sprintf("test_river_%04d", i)
s.testExecute(c, fmt.Sprintf("DROP TABLE IF EXISTS %s", table))
s.testExecute(c, fmt.Sprintf(schema, table))
}
cfg := new(Config)
cfg.MyAddr = *my_addr
cfg.MyUser = "******"
cfg.MyPassword = ""
cfg.ESAddr = *es_addr
cfg.ServerID = 1001
cfg.Flavor = "mysql"
cfg.DataDir = "/tmp/test_river"
cfg.DumpExec = "mysqldump"
cfg.StatAddr = "127.0.0.1:12800"
os.RemoveAll(cfg.DataDir)
cfg.Sources = []SourceConfig{SourceConfig{Schema: "test", Tables: []string{"test_river", "test_river_[0-9]{4}"}}}
cfg.Rules = []*Rule{
&Rule{Schema: "test",
Table: "test_river",
Index: "river",
Type: "river",
FieldMapping: map[string]string{"title": "es_title", "mylist": "es_mylist,list"},
},
&Rule{Schema: "test",
Table: "test_river_[0-9]{4}",
Index: "river",
Type: "river",
FieldMapping: map[string]string{"title": "es_title", "mylist": "es_mylist,list"},
},
}
s.r, err = NewRiver(cfg)
c.Assert(err, IsNil)
err = s.r.es.DeleteIndex("river")
c.Assert(err, IsNil)
}