Ygthkb

How long has this character been impersonating a Starfleet Officer?

Rigorous justification for non-relativistic QM perturbation theory assumptions?

Why are mages sometimes played bot instead of traditional ADCs?

Writing dialogues for characters whose first language is not English

Players preemptively rolling, even though their rolls are useless or are checking the wrong skills

What is a good way to explain how a character can produce flames from their body?

Insecure private-key encryption

How do I avoid the "chosen hero" feeling?

Buying a "Used" Router

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Why is it that Bernie Sanders is always called a "socialist"?

Is it possible to detect 100% of SQLi with a simple regex?

Why do neural networks need so many examples to perform?

Is Screenshot Time-tracking Common?

Can me and my friend spend the summer in Canada (6 weeks) at 16 years old without an adult?

Where does documentation like business and software requirement spec docs fit in an agile project?

How can I prevent an oracle who can see into the past from knowing everything that has happened?

Is there a non trivial covering of the Klein bottle by the Klein bottle

"I showed the monkey himself in the mirror". Why is this sentence grammatical?

Create linguistic diagram (in TikZ?)

Does it take energy to move something in a circle?

Why do single electrical receptacles exist?

What is the draw frequency for 3 consecutive games (same players; amateur level)?

Equivalent of "illegal" for violating civil law

Detect data spikes (loops) in MySQL database and ignore these

Mysql fulltext boolean ignore phraseMySQL detect duplicate human namesMysql replication and ignore tablesMySQL CPU & Memory SpikesDetect IP subnet overlap from mySQL databaseMySQL: How to detect export?Replication and replicate-ignore-dbInnoDB Read and Write speed constantly spikesMySQL: replicate some tables with replicate-ignore-db and binlog-ignore-dbHow to detect JSON data type support in MariaDB/MySQL

1

i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical rows like the following:

...

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01

...

Is there a way to detect unusal count grouped by the ip/url in an interval? I think i need to select all counts for each minute an detect a spike and ignore that... Does anyone had the same difficulties?

mysql

share|improve this question

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have the different fields of the access log available as different columns?
  
  – Colin 't Hart
  Aug 4 '14 at 11:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This kind of query is very easy with window functions -- specifically lag and lead, but I don't think MySQL has these.
  
  – Colin 't Hart
  Aug 4 '14 at 11:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Colin'tHart Yes of course, everything is in it's own column, hence the "-". The tables contains way more columns but these are not relevant for my problem...
  
  – Constantin
  Aug 4 '14 at 11:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Constantin So how would you define a "spike", more than N hits in the same minute by the same user, on the same URL, with a particular code, all of them?
  
  – jynus
  Aug 4 '14 at 17:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jynus Basically there are some redirect loops due to the fact that some users block cookies... so a typical request i have is something like: normal html site with a link to a dynamic application which required a login (the login may be a default user auto login), so a click on the application link redirects to the login, because the user isn't logged in yet, the login detects if the user is a default user and may login the user as a default user and redirect back to the dynamic application, but if cookies are blocked. this redirect loops forever if the user does not know whats going on
  
  – Constantin
  Aug 5 '14 at 7:34
  

  
  
  
  

 | 
show 1 more comment

1

i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical rows like the following:

...

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01

...

Is there a way to detect unusal count grouped by the ip/url in an interval? I think i need to select all counts for each minute an detect a spike and ignore that... Does anyone had the same difficulties?

mysql

share|improve this question

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have the different fields of the access log available as different columns?
  
  – Colin 't Hart
  Aug 4 '14 at 11:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This kind of query is very easy with window functions -- specifically lag and lead, but I don't think MySQL has these.
  
  – Colin 't Hart
  Aug 4 '14 at 11:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Colin'tHart Yes of course, everything is in it's own column, hence the "-". The tables contains way more columns but these are not relevant for my problem...
  
  – Constantin
  Aug 4 '14 at 11:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Constantin So how would you define a "spike", more than N hits in the same minute by the same user, on the same URL, with a particular code, all of them?
  
  – jynus
  Aug 4 '14 at 17:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @jynus Basically there are some redirect loops due to the fact that some users block cookies... so a typical request i have is something like: normal html site with a link to a dynamic application which required a login (the login may be a default user auto login), so a click on the application link redirects to the login, because the user isn't logged in yet, the login detects if the user is a default user and may login the user as a default user and redirect back to the dynamic application, but if cookies are blocked. this redirect loops forever if the user does not know whats going on
  
  – Constantin
  Aug 5 '14 at 7:34
  

  
  
  
  

 | 
show 1 more comment

i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical rows like the following:

...

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01

...

Is there a way to detect unusal count grouped by the ip/url in an interval? I think i need to select all counts for each minute an detect a spike and ignore that... Does anyone had the same difficulties?

mysql

share|improve this question

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

...

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00

/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00

/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01

...

share|improve this question

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

share|improve this question

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

asked Aug 4 '14 at 10:07

ConstantinConstantin

61

1 Answer
1

active

oldest

votes

0

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT count(*) as `# redirects`, ip, min(url) as url, 

              time_event - INTERVAL SECOND(time_event) SECOND AS minute 

       FROM log WHERE url like '/login?redirect=%' 

       GROUP BY ip, minute 

       HAVING `# redirects` > @max_redirects;

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

| # redirects | ip              | url                  | minute              |

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

|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |

|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |

|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |

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

3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.

share|improve this answer

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, i think that gets me working... but it is, as you said, pretty slow, even with all nesessary indexes
  
  – Constantin
  Aug 6 '14 at 9:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Constantin It can be easily improved, but you would need a couple of changes in structure, that I do not know if you can do or if the original table supports it.
  
  – jynus
  Aug 6 '14 at 9:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The table only has the following columns: id,e_url,e_full_url,e_time,e_method,e_status,e_length,e_referer,e_ip,e_country,e_city,ua_name,ua_version,ua_full,os_name,os_version,os_family,e_ignore,e_internal. The database is kind of old but could or should be replaced if necessary. Old data could be imported. (e_url is varchar(254) to be indexed, e_full_url contains the full url with query string)
  
  – Constantin
  Aug 6 '14 at 13:04
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Maybe another info: each month is stored in an own table. each tables has 5-6m rows, trending up. bots and some files(js,css,jpg,png,ico) already filtered out, because we do not need these information in the database.
  
  – Constantin
  Aug 6 '14 at 13:25
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f73097%2fdetect-data-spikes-loops-in-mysql-database-and-ignore-these%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT count(*) as `# redirects`, ip, min(url) as url, 

              time_event - INTERVAL SECOND(time_event) SECOND AS minute 

       FROM log WHERE url like '/login?redirect=%' 

       GROUP BY ip, minute 

       HAVING `# redirects` > @max_redirects;

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

| # redirects | ip              | url                  | minute              |

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

|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |

|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |

|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |

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

3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.

share|improve this answer

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, i think that gets me working... but it is, as you said, pretty slow, even with all nesessary indexes
  
  – Constantin
  Aug 6 '14 at 9:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Constantin It can be easily improved, but you would need a couple of changes in structure, that I do not know if you can do or if the original table supports it.
  
  – jynus
  Aug 6 '14 at 9:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The table only has the following columns: id,e_url,e_full_url,e_time,e_method,e_status,e_length,e_referer,e_ip,e_country,e_city,ua_name,ua_version,ua_full,os_name,os_version,os_family,e_ignore,e_internal. The database is kind of old but could or should be replaced if necessary. Old data could be imported. (e_url is varchar(254) to be indexed, e_full_url contains the full url with query string)
  
  – Constantin
  Aug 6 '14 at 13:04
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Maybe another info: each month is stored in an own table. each tables has 5-6m rows, trending up. bots and some files(js,css,jpg,png,ico) already filtered out, because we do not need these information in the database.
  
  – Constantin
  Aug 6 '14 at 13:25
  

  
  
  
  

add a comment | 

0

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT count(*) as `# redirects`, ip, min(url) as url, 

              time_event - INTERVAL SECOND(time_event) SECOND AS minute 

       FROM log WHERE url like '/login?redirect=%' 

       GROUP BY ip, minute 

       HAVING `# redirects` > @max_redirects;

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

| # redirects | ip              | url                  | minute              |

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

|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |

|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |

|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |

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

3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.

share|improve this answer

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, i think that gets me working... but it is, as you said, pretty slow, even with all nesessary indexes
  
  – Constantin
  Aug 6 '14 at 9:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Constantin It can be easily improved, but you would need a couple of changes in structure, that I do not know if you can do or if the original table supports it.
  
  – jynus
  Aug 6 '14 at 9:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The table only has the following columns: id,e_url,e_full_url,e_time,e_method,e_status,e_length,e_referer,e_ip,e_country,e_city,ua_name,ua_version,ua_full,os_name,os_version,os_family,e_ignore,e_internal. The database is kind of old but could or should be replaced if necessary. Old data could be imported. (e_url is varchar(254) to be indexed, e_full_url contains the full url with query string)
  
  – Constantin
  Aug 6 '14 at 13:04
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Maybe another info: each month is stored in an own table. each tables has 5-6m rows, trending up. bots and some files(js,css,jpg,png,ico) already filtered out, because we do not need these information in the database.
  
  – Constantin
  Aug 6 '14 at 13:25
  

  
  
  
  

add a comment | 

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT count(*) as `# redirects`, ip, min(url) as url, 

              time_event - INTERVAL SECOND(time_event) SECOND AS minute 

       FROM log WHERE url like '/login?redirect=%' 

       GROUP BY ip, minute 

       HAVING `# redirects` > @max_redirects;

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

| # redirects | ip              | url                  | minute              |

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

|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |

|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |

|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |

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

3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.

share|improve this answer

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832

mysql> SET @max_redirects := 10;

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT count(*) as `# redirects`, ip, min(url) as url, 

              time_event - INTERVAL SECOND(time_event) SECOND AS minute 

       FROM log WHERE url like '/login?redirect=%' 

       GROUP BY ip, minute 

       HAVING `# redirects` > @max_redirects;

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

| # redirects | ip              | url                  | minute              |

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

|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |

|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |

|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |

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

3 rows in set (0.01 sec)

share|improve this answer

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832

answered Aug 5 '14 at 8:11

jynusjynus

11.1k11832