.db-zabbix/log/log.go:299 Error()] [E] Error while executing script: sql: Scan error on column index 0: unsupported Scan, storing driver.Value type <nil> into type *string

[menardorama]

Hi

I don't really know why but at the first load I get this error.

[menardorama]

More information, the issue seems to occur on history_uint for now.

I've tried the sql query on a psql and it's working fine.

Do you have any ideas ?

[zensqlmonitor]

Hi, it is strange because the table history_uint has not any nullable column. Could you please double check the table definition ?

[menardorama]

Hi That's correct the history_uint table have not null on all fields.

By the way, my setup is using history and trend partionning using partition inheritance but it shouldn't have any effect (it worked for trend).

Any chance to put more debug option ?

[menardorama]

Hi,

If you can find in this output why the history_uint is failing.... result.gz

[menardorama]

Hi,

Do you have any idea why it's failing ?

I tried to find a reason, but the data structure seems good to me....

By googling the error message I got, I can find some related issues but referencing the mysql or sqllite driver.

By the way I encounter this issue with history_uint and trend_uint not the others.

But executing the history_uint manually give me some results

[menardorama]

Ok I made some progress on my issue.

The issue is not related to the table structure at all.

In fact from what I see if I put a limit to 10000 records data is correctly transfered in influxdb.

Digging around the data itself made me focus on the item name formatting in the query.

First issue I see is when using $3 parameter in the item name, it's not being replaced example :

TCP count SRC 636 $3 --> net.tcp.count[636,,ESTABLISHED]

Another issue but I don't know if it can have an impact is all the "various" characters such as :

• /
• "
• (
• )
• special characters such as accents : é à ....

I don't know yet where to find the $3 value in the db but I'm afraid the query will be bigger and bigger and cpu consuming on the db.

What do you think ?

[zensqlmonitor]

Ok for the table definition.

As shown in the log, the program stops here: if err := rows.Scan(&result); err != nil { return err } The issue comes from the rows.Scan operation. You can add a counter inside the loop to identify the row that seems to be NULL...

I will check how to replace the $3 value.

[menardorama]

Hi,

Ok I've found the issue, in the output I get some blank lines which make the fetch failing.

I think by security it would be best to skip those lines.

I have fixed the issue by modifying the Scan if statement like the following (quick and dirty I know...)

for rows.Next() {
  var result string
  if err := rows.Scan(&result); err != nil {
      counter += 1
      // blank line skipping
    } else {
      resultTmp = append(resultTmp, result)
    }
}

and for the sql query with missing $3 arguments I've done the following :

WHEN (position('$3' in ite.name) > 0)
       THEN replace(ite.name, '$3', split_part(substring(ite.key_ FROM '\[(.+)\]'), ',', 3))
   WHEN (position('$1' in ite.name) > 0) AND (position('$3' in ite.name) > 0)
     THEN replace(replace(ite.name, '$1', split_part(substring(ite.key_ FROM '\[(.+)\]'), ',', 1)), '$3', split_part(substring(ite.key_ FROM '\[(.+)\]'), ',', 3))

If you are ok I can push a pull request for that (even if I don't like the if statement

[menardorama]

And the real issue is that your query is based on the fact that all items belong to an application.

Which is not mandatory in zabbix.

The real fix would be to have a more tolerant sql query

[zensqlmonitor]

I will bring the modification this week.

[zensqlmonitor]

solved