func (s *FingerprintTestSuite) TestFingerprintUnion(t *C) {
t.Skip("Fingerprint UNION doesn't work yet")
var q string
// union fingerprints together
q = "select 1 union select 2 union select 4"
t.Check(
log.Fingerprint(q),
Equals,
"select ? /*repeat union*/",
)
// union all fingerprints together
q = "select 1 union all select 2 union all select 4"
t.Check(
log.Fingerprint(q),
Equals,
"select ? /*repeat union all*/",
)
// union all fingerprints together
q = `select * from (select 1 union all select 2 union all select 4) as x
join (select 2 union select 2 union select 3) as y`
t.Check(
log.Fingerprint(q),
Equals,
`select * from (select ? /*repeat union all*/) as x
join (select ? /*repeat union*/) as y`,
)
}
func (s *FingerprintTestSuite) TestFingerprintValueList(t *C) {
var q string
// VALUES lists
q = "insert into foo(a, b, c) values(2, 4, 5)"
t.Check(
log.Fingerprint(q),
Equals,
"insert into foo(a, b, c) values(?+)",
)
// VALUES lists with multiple ()
q = "insert into foo(a, b, c) values(2, 4, 5) , (2,4,5)"
t.Check(
log.Fingerprint(q),
Equals,
"insert into foo(a, b, c) values(?+)",
)
// VALUES lists with VALUE()
q = "insert into foo(a, b, c) value(2, 4, 5)"
t.Check(
log.Fingerprint(q),
Equals,
"insert into foo(a, b, c) value(?+)",
)
}
func (s *FingerprintTestSuite) TestFingerprintOrderBy(t *C) {
var q string
// Remove ASC from ORDER BY
// Issue 1030: Fingerprint can remove ORDER BY ASC
q = "select c from t where i=1 order by c asc"
t.Check(
log.Fingerprint(q),
Equals,
"select c from t where i=? order by c",
)
// Remove only ASC from ORDER BY
/*
q = "select * from t where i=1 order by a, b ASC, d DESC, e asc"
t.Check(
log.Fingerprint(q),
Equals,
"select * from t where i=? order by a, b, d desc, e",
)
*/
// Remove ASC from spacey ORDER BY
/*
q = `select * from t where i=1 order by
a, b ASC, d DESC,
e asc`
t.Check(
log.Fingerprint(q),
Equals,
"select * from t where i=? order by a, b, d desc, e",
)
*/
}
func (s *FingerprintTestSuite) TestFingerprintOneLineComments(t *C) {
var q string
/*
// Removes one-line comments in fingerprints
q = "select \n--bar\n foo"
t.Check(
log.Fingerprint(q),
Equals,
"select foo",
)
// Removes one-line comments in fingerprint without mushing things together
q = "select foo--bar\nfoo"
t.Check(
log.Fingerprint(q),
Equals,
"select foo foo",
)
// Removes one-line EOL comments in fingerprints
q = "select foo -- bar\n"
t.Check(
log.Fingerprint(q),
Equals,
"select foo ",
)
*/
// Removes one-line # hash comments
q = "### Channels ###\n\u0009\u0009\u0009\u0009\u0009SELECT sourcetable, IF(f.lastcontent = 0, f.lastupdate, f.lastcontent) AS lastactivity,\n\u0009\u0009\u0009\u0009\u0009f.totalcount AS activity, type.class AS type,\n\u0009\u0009\u0009\u0009\u0009(f.nodeoptions \u0026 512) AS noUnsubscribe\n\u0009\u0009\u0009\u0009\u0009FROM node AS f\n\u0009\u0009\u0009\u0009\u0009INNER JOIN contenttype AS type ON type.contenttypeid = f.contenttypeid \n\n\u0009\u0009\u0009\u0009\u0009INNER JOIN subscribed AS sd ON sd.did = f.nodeid AND sd.userid = 15965\n UNION ALL \n\n\u0009\u0009\u0009\u0009\u0009### Users ###\n\u0009\u0009\u0009\u0009\u0009SELECT f.name AS title, f.userid AS keyval, 'user' AS sourcetable, IFNULL(f.lastpost, f.joindate) AS lastactivity,\n\u0009\u0009\u0009\u0009\u0009f.posts as activity, 'Member' AS type,\n\u0009\u0009\u0009\u0009\u00090 AS noUnsubscribe\n\u0009\u0009\u0009\u0009\u0009FROM user AS f\n\u0009\u0009\u0009\u0009\u0009INNER JOIN userlist AS ul ON ul.relationid = f.userid AND ul.userid = 15965\n\u0009\u0009\u0009\u0009\u0009WHERE ul.type = 'f' AND ul.aq = 'yes'\n ORDER BY title ASC LIMIT 100"
t.Check(
log.Fingerprint(q),
Equals,
"select sourcetable, if(f.lastcontent = ?, f.lastupdate, f.lastcontent) as lastactivity, f.totalcount as activity, type.class as type, (f.nodeoptions & ?) as nounsubscribe from node as f inner join contenttype as type on type.contenttypeid = f.contenttypeid inner join subscribed as sd on sd.did = f.nodeid and sd.userid = ? union all select f.name as title, f.userid as keyval, ? as sourcetable, ifnull(f.lastpost, f.joindate) as lastactivity, f.posts as activity, ? as type, ? as nounsubscribe from user as f inner join userlist as ul on ul.relationid = f.userid and ul.userid = ? where ul.type = ? and ul.aq = ? order by title limit ?",
)
}
func (w *SlowLogWorker) Run(job *Job) (*Result, error) {
w.status.Update(w.name, "Starting job "+job.Id)
result := &Result{}
// Open the slow log file.
file, err := os.Open(job.SlowLogFile)
if err != nil {
return nil, err
}
defer file.Close()
// Create a slow log parser and run it. It sends events log events via its channel.
// Be sure to stop it when done, else we'll leak goroutines. stopChan must be buffered
// so we don't block on send if parser crashes.
stopChan := make(chan bool, 1)
defer func() { stopChan <- true }()
opts := parser.Options{
StartOffset: uint64(job.StartOffset),
FilterAdminCommand: map[string]bool{
"Binlog Dump": true,
"Binlog Dump GTID": true,
},
}
p := parser.NewSlowLogParser(file, stopChan, opts)
go func() {
defer func() {
if r := recover(); r != nil {
errMsg := fmt.Sprintf("Error parsing %s: %s", job, r)
w.logger.Error(errMsg)
result.Error = errMsg
}
}()
p.Run()
}()
// The global class has info and stats for all events.
// Each query has its own class, defined by the checksum of its fingerprint.
global := mysqlLog.NewGlobalClass()
queries := make(map[string]*mysqlLog.QueryClass)
jobSize := job.EndOffset - job.StartOffset
var runtime time.Duration
var progress string
t0 := time.Now()
EVENT_LOOP:
for event := range p.EventChan {
runtime = time.Now().Sub(t0)
progress = fmt.Sprintf("%.1f%% %d/%d %d %.1fs",
float64(event.Offset)/float64(job.EndOffset)*100, event.Offset, job.EndOffset, jobSize, runtime.Seconds())
w.status.Update(w.name, fmt.Sprintf("Parsing %s: %s", job.SlowLogFile, progress))
// Check runtime, stop if exceeded.
if runtime >= job.RunTime {
errMsg := fmt.Sprintf("Timeout parsing %s: %s", progress)
w.logger.Warn(errMsg)
result.Error = errMsg
break EVENT_LOOP
}
if int64(event.Offset) >= job.EndOffset {
result.StopOffset = int64(event.Offset)
break EVENT_LOOP
}
// Add the event to the global class.
err := global.AddEvent(event)
switch err.(type) {
case mysqlLog.MixedRateLimitsError:
result.Error = err.Error()
break EVENT_LOOP
}
// Get the query class to which the event belongs.
fingerprint := mysqlLog.Fingerprint(event.Query)
classId := mysqlLog.Checksum(fingerprint)
class, haveClass := queries[classId]
if !haveClass {
class = mysqlLog.NewQueryClass(classId, fingerprint, job.ExampleQueries)
queries[classId] = class
}
// Add the event to its query class.
class.AddEvent(event)
}
w.status.Update(w.name, "Finalizing job "+job.Id)
if result.StopOffset == 0 {
result.StopOffset, _ = file.Seek(0, os.SEEK_CUR)
}
// Done parsing the slow log. Finalize the global and query classes (calculate
// averages, etc.).
for _, class := range queries {
class.Finalize()
}
global.Finalize(uint64(len(queries)))
// Sort the results, keep the top and combine the rest into a single class: Low-Ranking Queries (LRQ).
w.status.Update(w.name, "Combining LRQ job "+job.Id)
nQueries := len(queries)
classes := make([]*mysqlLog.QueryClass, nQueries)
for _, class := range queries {
// Decr before use; can't classes[--nQueries] in Go.
nQueries--
classes[nQueries] = class
}
result.Global = global
result.Classes = classes
if !job.ZeroRunTime {
result.RunTime = time.Now().Sub(t0).Seconds()
}
w.status.Update(w.name, "Done job "+job.Id)
w.logger.Info(fmt.Sprintf("Parsed %s: %s", job, progress))
return result, nil
}
func (s *FingerprintTestSuite) TestFingerprintBasic(t *C) {
var q string
// A most basic case
q = "SELECT c FROM t WHERE id=1"
t.Check(
log.Fingerprint(q),
Equals,
"select c from t where id=?",
)
// The values looks like one line -- comments, but they're not.
q = `UPDATE groups_search SET charter = ' -------3\'\' XXXXXXXXX.\n \n -----------------------------------------------------', show_in_list = 'Y' WHERE group_id='aaaaaaaa'`
t.Check(
log.Fingerprint(q),
Equals,
"update groups_search set charter = ?, show_in_list = ? where group_id=?",
)
// PT treats this as "mysqldump", but we don't do any special fingerprints.
q = "SELECT /*!40001 SQL_NO_CACHE */ * FROM `film`"
t.Check(
log.Fingerprint(q),
Equals,
"select /*!? sql_no_cache */ * from `film`",
)
// Fingerprints stored procedure calls specially
q = "CALL foo(1, 2, 3)"
t.Check(
log.Fingerprint(q),
Equals,
"call foo",
)
// Fingerprints admin commands as themselves
q = "administrator command: Init DB"
t.Check(
log.Fingerprint(q),
Equals,
"administrator command: Init DB",
)
// Removes identifier from USE
q = "use `foo`"
t.Check(
log.Fingerprint(q),
Equals,
"use ?",
)
// Handles bug from perlmonks thread 728718
q = "select null, 5.001, 5001. from foo"
t.Check(
log.Fingerprint(q),
Equals,
"select ?, ?, ? from foo",
)
// Handles quoted strings
q = "select 'hello', '\nhello\n', \"hello\", '\\'' from foo"
t.Check(
log.Fingerprint(q),
Equals,
"select ?, ?, ?, ? from foo",
)
// Handles trailing newline
q = "select 'hello'\n"
t.Check(
log.Fingerprint(q),
Equals,
"select ?",
)
// Does not handle all quoted strings
// This is a known deficiency, fixes seem to be expensive though.
q = "select '\\\\' from foo"
t.Check(
log.Fingerprint(q),
Equals,
"select '\\ from foo",
)
// Collapses whitespace
q = "select foo"
t.Check(
log.Fingerprint(q),
Equals,
"select foo",
)
// Lowercases, replaces integer
q = "SELECT * from foo where a = 5"
t.Check(
log.Fingerprint(q),
Equals,
"select * from foo where a = ?",
)
// Floats
q = "select 0e0, +6e-30, -6.00 from foo where a = 5.5 or b=0.5 or c=.5"
t.Check(
log.Fingerprint(q),
Equals,
"select ?, ?, ? from foo where a = ? or b=? or c=?",
)
// Hex/bit
q = "select 0x0, x'123', 0b1010, b'10101' from foo"
t.Check(
log.Fingerprint(q),
Equals,
"select ?, ?, ?, ? from foo",
)
// Collapses whitespace
q = " select * from\nfoo where a = 5"
t.Check(
log.Fingerprint(q),
Equals,
"select * from foo where a = ?",
)
// IN lists
q = "select * from foo where a in (5) and b in (5, 8,9 ,9 , 10)"
t.Check(
log.Fingerprint(q),
Equals,
"select * from foo where a in(?+) and b in(?+)",
)
// Numeric table names. By default, PT will return foo_?, etc. because
// match_embedded_numbers is false by default for speed.
q = "select foo_1 from foo_2_3"
t.Check(
log.Fingerprint(q),
Equals,
"select foo_1 from foo_2_3",
)
// Numeric table name prefixes
// 123f00 => ?oo because f "looks like it could be a number".
q = "select 123foo from 123foo"
t.Check(
log.Fingerprint(q),
Equals,
"select ?oo from ?oo",
)
// Numeric table name prefixes with underscores
q = "select 123_foo from 123_foo"
t.Check(
log.Fingerprint(q),
Equals,
"select ?_foo from ?_foo",
)
// A string that needs no changes
q = "insert into abtemp.coxed select foo.bar from foo"
t.Check(
log.Fingerprint(q),
Equals,
"insert into abtemp.coxed select foo.bar from foo",
)
// limit alone
q = "select * from foo limit 5"
t.Check(
log.Fingerprint(q),
Equals,
"select * from foo limit ?",
)
// limit with comma-offset
q = "select * from foo limit 5, 10"
t.Check(
log.Fingerprint(q),
Equals,
"select * from foo limit ?",
)
// limit with offset
q = "select * from foo limit 5 offset 10"
t.Check(
log.Fingerprint(q),
Equals,
"select * from foo limit ?",
)
// Fingerprint LOAD DATA INFILE
q = "LOAD DATA INFILE '/tmp/foo.txt' INTO db.tbl"
t.Check(
log.Fingerprint(q),
Equals,
"load data infile ? into db.tbl",
)
// Fingerprint db.tbl<number>name (preserve number)
q = "SELECT * FROM prices.rt_5min where id=1"
t.Check(
log.Fingerprint(q),
Equals,
"select * from prices.rt_5min where id=?",
)
// Fingerprint /* -- comment */ SELECT (bug 1174956)
q = "/* -- S++ SU ABORTABLE -- spd_user: rspadim */SELECT SQL_SMALL_RESULT SQL_CACHE DISTINCT centro_atividade FROM est_dia WHERE unidade_id=1001 AND item_id=67 AND item_id_red=573"
t.Check(
log.Fingerprint(q),
Equals,
"select sql_small_result sql_cache distinct centro_atividade from est_dia where unidade_id=? and item_id=? and item_id_red=?",
)
}