[dev.icinga.com #13951] MySQL Warnings due to OR operator instead of CONCAT function

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/13951

Created by leeclemens on 2017-01-11 20:09:42 +00:00

Assignee: (none)
Status: Closed (closed on 2017-01-11 20:23:42 +00:00)
Target Version: (none)
Last Update: 2017-01-11 21:09:49 +00:00 (in Redmine)

---

I ran into this issue when using ProxySQL between Icinga2 and my database servers (MariaDB 10.1).

When I ran the query directly, it caused a lot of warnings (below). By using the CONCAT function instead of the OR operator (||), these warnings do no appear and the expected text is displayed on the Activity Log screen.

I'm looking into other areas affected by this, but the only place in Director seems to be the Activity Log screen (IDO is also affected).

| Warning | 1292 | Truncated incorrect INTEGER value: '['               |
| Warning | 1292 | Truncated incorrect INTEGER value: '] '              |
| Warning | 1292 | Truncated incorrect INTEGER value: ' '               |
| Warning | 1292 | Truncated incorrect INTEGER value: ' "'              |
| Warning | 1292 | Truncated incorrect INTEGER value: '"'               |
| Warning | 1292 | Truncated incorrect DOUBLE value: '['                 |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'myuser'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: '] '                |

Attachments

• 0001-Use-SQL-CONCAT-function.patch leeclemens - 2017-01-11 20:21:04 +00:00

[icinga-migration]

Updated by leeclemens on 2017-01-11 20:21:08 +00:00

• File added 0001-Use-SQL-CONCAT-function.patch

#45

Note: I did NOT test PostgreSQL, but CONCAT is a PostgreSQL function with the same syntax.

[icinga-migration]

Updated by tgelf on 2017-01-11 20:23:42 +00:00

• Tracker changed from Bug to Support
• Status changed from New to Closed

Guess you're running into this ProxySQL bug:

sysown/proxysql#509

Icinga Web 2 uses a pretty strict sql_mode for it's connections to MySQL (and it's variants). The string it uses is:

SET SESSION SQL_MODE='STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ANSI_QUOTES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION';

Your proxy needs to pass this to your database, you'd face a lot of other problems too in case it doesn't. There is nothing we could do to help you here, however it seems that ProxySQL fixed this bug a month ago:

sysown/proxysql@902e9d6

Alternatively you could try to somehow configure this as a default, not sure whether that would work out for your environment.

Cheers,
Thomas

[icinga-migration]

Updated by leeclemens on 2017-01-11 20:29:55 +00:00

In case it's useful, the resultant query which causes warnings:

SELECT
 '[' || l.author || '] ' || l.action_name || ' ' || REPLACE(l.object_type, 'icinga_', '') || ' "' || l.object_name || '"' AS log_message,
 l.author, l.action_name AS action, l.object_name, l.object_type, l.id, l.change_time, UNIX_TIMESTAMP(l.change_time) AS ts_change_time
 FROM director_activity_log AS l
 ORDER BY change_time DESC, id DESC LIMIT 25

[icinga-migration]

Updated by leeclemens on 2017-01-11 20:36:00 +00:00

Thank you for that bug reference, that does seem to be related and could cause other issues.

I'm curious why the decision to rely on PIPES_AS_CONCAT being set as opposed to using the CONCAT function explicitly?

[icinga-migration]

Updated by tgelf on 2017-01-11 20:44:57 +00:00

leeclemens wrote:

I'm curious why the decision to rely on PIPES_AS_CONCAT being set as opposed to using the CONCAT function explicitly?

Pipes are ANSI SQL, CONCAT isn't. There is nothing wrong with CONCAT in MySQL. Just, PostgreSQL has no CONCAT, Oracle has it, but it behaves differently. PIPES_AS_CONCAT allows us to do string concatenation the same way for multiple databases.

This is just a matter of personal preference, nothing more. And as soon as it goes to GROUP_CONCAT or similar we have to ship different queries anyways.

[icinga-migration]

Updated by leeclemens on 2017-01-11 20:58:50 +00:00

PostgreSQL 9.1 docs show it having CONCAT. But I'm inclined to agree with the ANSI rationale regardless, always appreciate the response/explanation!

[icinga-migration]

Updated by tgelf on 2017-01-11 21:09:49 +00:00

leeclemens wrote:

PostgreSQL 9.1 docs show it having CONCAT.

Honestly, I didn't know that, thanks for the hint!

But I'm inclined to agree with the ANSI rationale regardless, always appreciate the response/explanation!

You're welcome!