fluent-bit-script.lua:42: attempt to perform arithmetic on global 'id1' (a nil value)

[mahmoud-anwer]

Hello,
I am trying to configure mysql Slow Statements using SkyWalking Docs
I have two docker compose files
The first one: docker-compose.single-node.yaml, it is for starting OAP server and its UI in addition to elasticsearch.

version: '2.1'
services:
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch-oss:7.10.0
    ports:
      - "9200:9200"
    healthcheck:
      test: [ "CMD-SHELL", "curl --silent --fail localhost:9200/_cluster/health" ]
      interval: 30s
      timeout: 10s
      retries: 3
    environment:
      - discovery.type=single-node
      - bootstrap.memory_lock=true
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
    ulimits:
      memlock:
        soft: -1
        hard: -1
  oap:
    image: ghcr.io/apache/skywalking/oap:4b88d85291b7cc5b442e2c020d34f4f58632544f
    volumes:
      - ./config/mysql-slowsql.yaml:/skywalking/config/lal/mysql-slowsql.yaml
    environment:
      SW_HEALTH_CHECKER: default # @feature: health-check;
      SW_OTEL_RECEIVER: default # @feature: vm; enable the OC receiver that receives the VM metrics
      SW_OTEL_RECEIVER_ENABLED_OTEL_RULES: mysql/* # @feature: vm,mysql,postgresql; enable the OC rules that analyse the metrics
      SW_STORAGE: elasticsearch
      SW_STORAGE_ES_CLUSTER_NODES: elasticsearch:9200
      SW_TELEMETRY: prometheus # @feature: so11y; expose the metrics of self o11y through prometheus
      SW_PROMETHEUS_FETCHER: default # @feature: so11y; fetch the metrics of self o11y through prometheus
      JAVA_OPTS: "-Xms2048m -Xmx2048m"
      SW_METER_ANALYZER_ACTIVE_FILES: datasource,threadpool,satellite,spring-sleuth
    healthcheck:
      test: [ "CMD-SHELL", "/skywalking/bin/swctl ch" ]
      interval: 30s
      timeout: 10s
      retries: 3
    depends_on:
      elasticsearch:
        condition: service_healthy
  ui:
    image: ghcr.io/apache/skywalking/ui:4b88d85291b7cc5b442e2c020d34f4f58632544f
    ports:
      - "80:8080"
    depends_on:
      oap:
        condition: service_healthy
    environment:
      SW_OAP_ADDRESS: http://oap:12800

and the second one: docker-compose.mysql.yaml, it is for mysql, mysql-exporter, otel-collector and fluentbit.

version: '2.1'

services:
  mysql:
    image: mariadb:10.7.3
    container_name: db
    ports:
      - 3306
    volumes:
     - ./resource:/docker
     - ./config/my.cnf:/etc/mysql/my.cnf
     - ./log/db/slow.log:/var/lib/mysql/slow.log
    environment:
      - MYSQL_ROOT_PASSWORD=password

  mysql-service:
    image: prom/mysqld-exporter:v0.14.0
    container_name: mysqlq-exporter
    ports:
      - 9104
    environment:
      - DATA_SOURCE_NAME=root:password@(mysql:3306)/
    depends_on:
      - mysql


  otel-collector:
    image: otel/opentelemetry-collector:0.50.0
    command: [ "--config=/etc/otel-collector-config.yaml" ]
    volumes:
      - ./config/otel-collector-config-mysql.yaml:/etc/otel-collector-config.yaml
    expose:
      - 55678
    depends_on:
      oap:
        condition: service_healthy


  fluentbit:
    image: fluent/fluent-bit:1.9
    container_name: fluentbit
    volumes:
      - ./log/db/slow.log:/tmp/skywalking-logs/slow.log
      - ./config/fluentbit/fluent-bit.conf:/fluent-bit/etc/fluent-bit.conf
      - ./config/fluentbit/fluent-bit-parser.conf:/fluent-bit/etc/fluent-bit-parser.conf
      - ./config/fluentbit/fluent-bit-script.lua:/fluent-bit/etc/fluent-bit-script.lua
    depends_on:
      - mysql
      - oap

The configuration files of fluentbit are from SkyWalking repository

But I got the following error in fluentbit logs when I am trying to execute querey SELECT SLEEP(12);
It is related to the fluentbit script but I did not change anything in the file.

[2022/11/21 13:44:44] [error] [filter:lua:lua.3] error code 2: /fluent-bit/etc/fluent-bit-script.lua:42: attempt to perform arithmetic on global 'id1' (a nil value)
[2022/11/21 13:44:44] [error] [filter:lua:lua.3] error code 2: /fluent-bit/etc/fluent-bit-script.lua:42: attempt to perform arithmetic on global 'id1' (a nil value)
[2022/11/21 13:44:44] [error] [filter:lua:lua.3] error code 2: /fluent-bit/etc/fluent-bit-script.lua:42: attempt to perform arithmetic on global 'id1' (a nil value)
[2022/11/21 13:44:44] [error] [filter:lua:lua.3] error code 2: /fluent-bit/etc/fluent-bit-script.lua:42: attempt to perform arithmetic on global 'id1' (a nil value)
[2022/11/21 13:44:45] [error] [filter:lua:lua.3] error code 2: /fluent-bit/etc/fluent-bit-script.lua:42: attempt to perform arithmetic on global 'id1' (a nil value)
[{"date":1669038284.417097,"log":"# Time: 221121 13:44:44\n# User@Host: root[root] @ localhost []\n"},{"date":1669038284.417097,"log":"# Thread_id: 1998  Schema:   QC_hit: No\n"},{"date":1669038284.417097,"log":"# Query_time: 12.000192  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0\n"},{"date":1669038284.417097,"log":"# Rows_affected: 0  Bytes_sent: 66\n"},{"date":1669038284.417097,"log":"SET timestamp=1669038284;\nselect sleep(12);\n"}]
[2022/11/21 13:44:45] [ info] [output:http:http.1] oap:12800, HTTP status=200

So did I miss something or is there a bug with the fluentbit script?

Thanks.

[yswdqz]

Hi, I’m glad that I could be of help.
The reason for this problem is that the slow log of mariadb is different from the slow log of mysql.
All the thing fluent-bit-script.lua do is extract data from log and put them in to a JSON string.
So if the format is different, this script will not work. You can just change mariadb to mysql, this problem will resolve.
If it is necessary to use mariadb. You can write a corresponding script yourself. The format of mariadb is following:

# Time: 200929 15:42:35
# User@Host: root[root] @  [192.168.1.1]
# Thread_id: 2332  Schema: parkcloud_performancetest  QC_hit: No
# Query_time: 10.93443  Lock_time: 0.000091  Rows_sent: 5  Rows_examined: 9022
# Rows_affected: 0
SET timestamp=xxxxxxxxxx;

You can refer to LAL extractor doc to extract data and send them to oap, this way you will get the same effect.

[mahmoud-anwer]

Databases available on OAP are MySQL and Postgres, I am using MySQL to receive my data because there is no Mariadb database so is that acceptable or should I do something else?

Yes, I will make a pull request but I want to make sure that everything is correct first.

[yswdqz]

Databases available on OAP are MySQL and Postgres, I am using MySQL to receive my data because there is no Mariadb database so is that acceptable or should I do something else?

Yes, you can.
You can send a PR first, and we will check it together.

[wu-sheng]

Databases available on OAP are MySQL and Postgres, I am using MySQL to receive my data because there is no Mariadb database so is that acceptable or should I do something else?

Yes, I will make a pull request but I want to make sure that everything is correct first.

Do you change anything on MAL or dashboard to support MariaDB? Or just MariaDB side setup.

[mahmoud-anwer]

No, it is just an E2E case but I would like to do more so if there is a guide to do that, it will be great.

[wu-sheng]

I had left comments, let's continue on #10115

[wu-sheng]

Thanks @yswdqz for your reply. As a test before trying to write my own script for mariadb, I tried to use mysql and now it is fine there are no errors but I got nothing in SkyWalking UI.

This is the log from fluentbit when I tried to execute SELECT SLEEP(3); query.

[{"date":1669114671.728612,"body":{"json":{"json":"{\"time\":1669114672000,\"layer\":\"MYSQL\",\"query_time\":3000,\"id\":\"5a862f56-e3e2-e22a-2786-1cf1a8a61ac3\",\"statement\":\"SET timestamp=1669114671;select sleep(3);\",\"service\":\"mysql::root[root]\"}"}},"service":"mysql::root[root]","layer":"MYSQL","tags":{"data":[{"value":"SLOW_SQL","key":"LOG_KIND"}]}}]
[2022/11/22 10:57:52] [ info] [output:http:http.1] oap:12800, HTTP status=200

and this is the log from the database

# Time: 2022-11-22T10:57:51.728490Z
# User@Host: root[root] @ localhost []  Id:  7720
# Query_time: 3.000241  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1669114671;
select sleep(3);

I noticed this part of the log "service":"mysql::root[root]",

do I have to change it to match with my service name mysql::db ?

I also checked the rule mysql-slowsql at OAP server and I think it is fine.

rules:
  - name: mysql-slowsql
    layer: MYSQL
    dsl: |
      filter {
        json{
        }
        extractor{
          layer parsed.layer as String
          service parsed.service as String
          timestamp parsed.time as String
          if (tag("LOG_KIND") != "SLOW_SQL") {
             slowSql {
               id parsed.id as String
               statement parsed.statement as String
               latency parsed.query_time as Long
             }
          }
        }
      }

Which database are you using? It is better and easier we could check the service_traffic table to see whether there is a service in the database.

[mahmoud-anwer]

Thanks @wu-sheng for your reply.

Which database are you using?

If you mean which database I use before executing SELECT SLEEP(3); query, actually I tried to use mysql and test db which created using the database script but it is the same case.

we could check the service_traffic table

Where can I find this table ?
these are my databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

[wu-sheng]

I mean the storage of SkyWalking OAP server.

[mahmoud-anwer]

In my case the storage is elasticsearch but actually I do not work with it before so I decided to use the h2 database for simplification purposes.

I changed SW_STORAGE to equal h2 and now I think that OAP server received data from fluentbit but there is an issue while processing that data .

This is the logs of OAP

2022-11-22 17:20:44,254 org.apache.skywalking.oap.meter.analyzer.dsl.Expression 89 [grpcServerPool-1-thread-4] ERROR [] - [9.3.0-SNAPSHOT-4b88d85] failed to run "(mysql_global_status_slow_queries.rate('PT1M')).tag({tags -> tags.host_name = 'mysql::' + tags.host_name}).service(['host_name'] , Layer.MYSQL)"

and here is the configuration of h2 from application.yml

  h2:
    properties:
      jdbcUrl: ${SW_STORAGE_H2_URL:jdbc:h2:mem:skywalking-oap-db;DB_CLOSE_DELAY=-1}
      dataSource.user: ${SW_STORAGE_H2_USER:sa}
    metadataQueryMaxSize: ${SW_STORAGE_H2_QUERY_MAX_SIZE:5000}
    maxSizeOfBatchSql: ${SW_STORAGE_MAX_SIZE_OF_BATCH_SQL:100}
    asyncBatchPersistentPoolSize: ${SW_STORAGE_ASYNC_BATCH_PERSISTENT_POOL_SIZE:1}