# Filebeat mysql module slowlog - error.message: Provided Grok expressions do not match field value

**URL:** <https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945>\
**Category:** Beats\
**Tags:** filebeat\
**Created:** [June 14, 2018, 2:51pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945 "2018-06-14T14:51:31Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 14, 2018, 2:51pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/1 "2018-06-14T14:51:31Z")

</div>

Hi, all!

I want use Filebeat MySQL Module for collecting slowlogs into Elasticsearch to view in Kibana Dashboard.

I would like to resolve this issue only without using Logstash, only filebeat module.

Anyone can help me to create grok pattern for this case?

**My versions:**

**Elasticsearch version:** 6.3.0  
**MariaDB Versions:** 5.5.56-MariaDB and 10.1.21-MariaDB  
**Filebeat version:** 6.3.0  
**Kibana version:** 6.3.0

**This is my log pattern:**

```
# Time: 180613 11:04:36
# User@Host: root[root] @ localhost []
# Thread_id: 5 Schema: QC_hit: No
# Query_time: 2.000652 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1528898676;
select sleep(2);

```

**Error Message:**

`error.message Provided Grok expressions do not match field value: [# User@Host: root[root] @ localhost []\n# Thread_id: 313237 Schema: QC_hit: No\n# Query_time: 35.000261 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0\n# Rows_affected: 0\nSET timestamp=1528825570;\nSELECT SLEEP(35);]`

**Default "pipeline.json" file**

cat /usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json

```
{
  "description": "Pipeline for parsing MySQL slow logs.",
  "processors": [{
    "grok": {
      "field": "message",
      "patterns":[
        "^# User@Host: %{USER:mysql.slowlog.user}(\\[[^\\]]+\\])? @ (%{HOSTNAME:mysql.slowlog.host})? \\[(%{IP:mysql.slowlog.ip})?\\](\\s*Id:\\s* %{NUMBER:mysql.slowlog.id})?\n# Query_time: %{NUMBER:mysql.slowlog.query_time.sec}\\s* Lock_time: %{NUMBER:mysql.slowlog.lock_time.sec}\\s* Rows_sent: %{NUMBER:mysql.slowlog.rows_sent}\\s* Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}\n(SET timestamp=%{NUMBER:mysql.slowlog.timestamp};\n)?%{GREEDYMULTILINE:mysql.slowlog.query}"
        ],
      "pattern_definitions" : {
        "GREEDYMULTILINE" : "(.|\n)*"
      },
      "ignore_missing": true
    }
  }, {
    "remove":{
      "field": "message"
    }
  }, {
    "date": {
      "field": "mysql.slowlog.timestamp",
      "target_field": "@timestamp",
      "formats": ["UNIX"],
      "ignore_failure": true
    }
  }, {
    "gsub": {
      "field": "mysql.slowlog.query",
       "pattern": "\n# Time: [0-9]+ [0-9][0-9]:[0-9][0-9]:[0-9][0-9](\\.[0-9]+)?$",
       "replacement": "",
       "ignore_failure": true
    }
  }],
  "on_failure" : [{
    "set" : {
      "field" : "error.message",
      "value" : "{{ _ingest.on_failure_message }}"
    }
  }]
}

```

**Default "slowlog.yml" file**

cat /usr/share/filebeat/module/mysql/slowlog/config/slowlog.yml

```
type: log
paths:
{{ range $i, $path := .paths }}
 - {{$path}}
{{ end }}
exclude_files: ['.gz$']
multiline:
  pattern: '^# User@Host: '
  negate: true
  match: after
exclude_lines: ['^[\/\w\.]+, Version: .* started with:.*'] # Exclude the header

```

Thanks!

---

<div class="post-metadata">

**Author:** ![jsoriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsoriano/32/27920_2.png) [@jsoriano](https://discuss.elastic.co/u/jsoriano)\
**Post date:** [June 14, 2018, 3:16pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/2 "2018-06-14T15:16:13Z")

</div>

Hi @Rodrigo_Floriano,

You can use filebeat just with Elasticsearch and without Logstash, filebeat module pipelines work with Elasticsearch ingest nodes.  
Regarding your problem, we have received about incompatibilities with recent MySQL/MariaDB versions like this one: [https://github.com/elastic/beats/issues/6665](https://github.com/elastic/beats/issues/6665), your problem is probably related to these issues.

---

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 14, 2018, 4:00pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/3 "2018-06-14T16:00:24Z")

</div>

Thanks for you reply @jsoriano!

I read this issue before open my issue, but the log pattern is different, i don't use percona server.

I identified 3 different patterns for mysql slowlogs, I belive that grok pattern needed adjust for this log types, but i tried many combinations unsucess 😕 .

**Output default for "5.5.56-MariaDB and 10.1.21-MariaDB" (My case)**

```
# Time: 180613 11:04:36
# User@Host: root[root] @ localhost []
# Thread_id: 5 Schema: QC_hit: No
# Query_time: 2.000652 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1528898676;
select sleep(2);

```

**Other output that i found for other versions**

```
# Time: 171011 11:55:48
# User@Host: root[root] @ [10.254.254.91] Id: 54014516
# Query_time: 43.426563 Lock_time: 0.000093 Rows_sent: 63264 Rows_examined: 63264
SET timestamp=1507694148;
select sleep(2);

```

**Default percona server log:**

```
# Time: 2018-03-26T08:03:59.598547Z
# User@Host: root[root] @ localhost [] Id: 37045034
# Schema: Last_errno: 0 Killed: 0
# Query_time: 10.000204 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0
# Bytes_sent: 57 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
# Log_slow_rate_type: session Log_slow_rate_limit: 100
SET timestamp=1522051439;
select sleep(10);
```

---

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 15, 2018, 7:37pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/4 "2018-06-15T19:37:37Z")

</div>

**Anyone? Any idea how to parse the log?**

I tested many patterns but unsucess...keeping testing

**My last FAIL pattern test:**

```auto
"^# User@Host: %{USER:mysql.slowlog.user}\\[%{USER:mysql.slowlog.current_user}\\] @ %{HOSTNAME:mysql.slowlog.host}? \\[(%{IP:mysql.slowlog.ip})?/n# Thread_id: %{NUMBER:mysql.slowlog.thread_id} /s*Schema: [A-z0-9]? /s*QC_hit: %{WORD:mysql.slowlog.qc_hit}/n# Query_time: %{NUMBER:mysql.slowlog.query_time} /s*Lock_time: %{NUMBER:mysql.slowlog.lock_time} /s*Rows_sent: %{NUMBER:mysql.slowlog.rows_sent} /s*Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}/nSET timestamp=%{NUMBER:timestamp};%{GREEDYMULTILINE:mysql.slowlog.query}"

```

---

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 16, 2018, 8:23pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/5 "2018-06-16T20:23:15Z")

</div>

I was able to parse in Grok Debugger, but I still have an error in my pipeline. 😕

**Grok Debugger:**

[https://grokdebug.herokuapp.com/](https://grokdebug.herokuapp.com/)

**LOG**

`# User@Host: root[root] @ localhost []\n# Thread_id: 5 Schema: QC_hit: No\n# Query_time: 2.000652 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0\nSET timestamp=1529175026;\nselect sleep(2);`

**Pattern**

`^# User@Host:%{SPACE}%{USER:user}\[%{USER:current_user}\]%{SPACE}@%{SPACE}%{HOSTNAME:host}?%{SPACE}\[(%{IP:ip})?..+\#%{SPACE}Thread_id:%{SPACE}%{NUMBER:thread_id}%{SPACE}%{SPACE}Schema:%{SPACE}%{WORD:schema}?%{SPACE}%{SPACE}QC_hit:%{SPACE}%{WORD:qc_hit}..+\# Query_time: %{NUMBER:query_time}%{SPACE}%{SPACE}Lock_time: %{NUMBER:lock_time}%{SPACE}%{SPACE}Rows_sent: %{NUMBER:rows_sent}%{SPACE}%{SPACE}Rows_examined: %{NUMBER:rows_examined}..+SET timestamp=%{NUMBER:timestamp};..%{GREEDYDATA:query}`

**Result:**

```
{
  "user": [
    [
      "root"
    ]
  ],
  "current_user": [
    [
      "root"
    ]
  ],
  "host": [
    [
      "localhost"
    ]
  ],
  "ip": [
    [
      null
    ]
  ],
  "thread_id": [
    [
      "5"
    ]
  ],
  "schema": [
    [
      null
    ]
  ],
  "qc_hit": [
    [
      "No"
    ]
  ],
  "query_time": [
    [
      "2.000652"
    ]
  ],
  "lock_time": [
    [
      "0.000000"
    ]
  ],
  "rows_sent": [
    [
      "1"
    ]
  ],
  "rows_examined": [
    [
      "0"
    ]
  ],
  "timestamp": [
    [
      "1529175026"
    ]
  ],
  "query": [
    [
      "select sleep(2);"
    ]
  ]
}
```

---

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 18, 2018, 5:50pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/6 "2018-06-18T17:50:28Z")

</div>

**I finally solved it! 🕶 👏👏🙌**

**I wrote an article with the solution in my Blog (In Portuguese).**

[https://churrops.io/2018/06/18/elastic-modulo-mysql-do-filebeat-para-capturar-slowlogs-slow-queries/](https://churrops.io/2018/06/18/elastic-modulo-mysql-do-filebeat-para-capturar-slowlogs-slow-queries/)

Thanks!

---

<div class="post-metadata">

**Author:** ![jsoriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsoriano/32/27920_2.png) [@jsoriano](https://discuss.elastic.co/u/jsoriano)\
**Post date:** [June 20, 2018, 2:36pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/7 "2018-06-20T14:36:44Z")

</div>

@Rodrigo_Floriano great! your post looks nice 🙂

Would you like to contribute your grok expressions to the current mysql module so we can support these recent MariaDB versions?

---

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 25, 2018, 1:37pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/8 "2018-06-25T13:37:11Z")

</div>

Sure!!! ...

I would be very happy with that!

---

<div class="post-metadata">

**Author:** ![azzra](https://avatars.discourse-cdn.com/v4/letter/a/b38774/32.png) [@azzra](https://discuss.elastic.co/u/azzra)\
**Post date:** [June 26, 2018, 9:43am UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/9 "2018-06-26T09:43:33Z")

</div>

Hi,

I ran into the same issue on a Docker setup with Filebeat 6.3.0, Elasticsearch 6.3.0 and MySQL 5.7.22.  
I got slow logs like this:

```sql
# Time: 2018-06-26T09:26:23.578890Z
# User@Host: toto[toto] @ [172.23.0.9] Id: 5
# Query_time: 0.530174 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use foo_test;
SET timestamp=1530005183;
CREATE TABLE bar (baz VARCHAR(255) NOT NULL, PRIMARY KEY(baz)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

```

I solved like this (inspired from @Rodrigo_Floriano)

`/usr/share/filebeat/module/mysql/slowlog/config/slowlog.yml`

```auto
type: log
paths:
{{ range $i, $path := .paths }}
 - {{$path}}
{{ end }}
exclude_files: ['.gz$']
multiline:
  pattern: '^# Time:'
  negate: true
  match: after
exclude_lines: ['^[\/\w\.]+, Version: .* started with:.*'] # Exclude the header

```

`/usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json`

```json
{
  "description": "Pipeline for parsing MySQL slow logs.",
  "processors": [{
    "grok": {
      "field": "message",
      "patterns":[
        "^# Time: %{TIMESTAMP_ISO8601:mysql.slowlog.time}\n# User@Host: %{USER:mysql.slowlog.user}\\[%{USER:mysql.slowlog.current_user}\\] @ %{HOSTNAME:mysql.slowlog.host}? \\[%{IP:mysql.slowlog.ip}?\\]%{SPACE}Id:%{SPACE}%{NUMBER:mysql.slowlog.id}\n# Query_time: %{NUMBER:mysql.slowlog.query_time.sec}%{SPACE}Lock_time: %{NUMBER:mysql.slowlog.lock_time.sec}%{SPACE}Rows_sent: %{NUMBER:mysql.slowlog.rows_sent}%{SPACE}Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}\n((use|USE) .*;\n)?SET timestamp=%{NUMBER:mysql.slowlog.timestamp};\n%{GREEDYDATA:mysql.slowlog.query}"
      ],
      "pattern_definitions" : {
        "GREEDYMULTILINE" : "(.|\n)*"
      },
      "ignore_missing": false
    }
  }, {
    "remove":{
      "field": "message"
    }
  }, {
    "date": {
      "field": "mysql.slowlog.time",
      "target_field": "@timestamp",
      "formats": ["ISO8601"],
      "ignore_failure": true
    }
  }],
  "on_failure" : [{
    "set" : {
      "field" : "error.message",
      "value" : "{{ _ingest.on_failure_message }}"
    }
  }]
}

```

main differences are:

- an optionnal `use ....;` line in grok pattern
- ISO8601 timestamp
- `.` instead of `_` in grok pattern named captures

Don't forget to add these files in your `docker-compose.yml`:

```auto
filebeat:
        image: docker.elastic.co/beats/filebeat:6.3.0
        ...
        volumes:
            - ...
            - ./your/path/to/mysql/slowlog/ingest/pipeline.json:/usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json:ro
            - ./your/path/to/mysql/slowlog/config/slowlog.yml:/usr/share/filebeat/module/mysql/slowlog/config/slowlog.yml:ro

```

---

<div class="post-metadata">

**Author:** ![Rodrigo\_Floriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rodrigo_floriano/32/32044_2.png) [@Rodrigo\_Floriano](https://discuss.elastic.co/u/Rodrigo_Floriano)\
**Post date:** [June 26, 2018, 3:25pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/10 "2018-06-26T15:25:09Z")

</div>

@jsoriano, see my pull request: [https://github.com/elastic/beats/pull/7422](https://github.com/elastic/beats/pull/7422)

---

<div class="post-metadata">

**Author:** ![jsoriano](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsoriano/32/27920_2.png) [@jsoriano](https://discuss.elastic.co/u/jsoriano)\
**Post date:** [June 26, 2018, 3:53pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/11 "2018-06-26T15:53:25Z")

</div>

Thanks!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 24, 2018, 3:53pm UTC](https://discuss.elastic.co/t/filebeat-mysql-module-slowlog-error-message-provided-grok-expressions-do-not-match-field-value/135945/12 "2018-07-24T15:53:27Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.