/devices webpage no results found

kbwilliams · 7 February 2020 01:09

[2020-02-06 16:58:24] production.ERROR: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00 or end_recurring_dt = )) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(‘%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null or recurring_day = ))) and ((alert_schedulable_type = device and alert_schedulable_id = 5) or (alert_schedulable_type = device_group and 0 = 1) or (alert_schedulable_type = location and alert_schedulable_id = 3))) as exists) {“userId”:1,“exception”:"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00 or end_recurring_dt = )) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(‘%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null or recurring_day = ))) and ((alert_schedulable_type = device and alert_schedulable_id = 5) or (alert_schedulable_type = device_group and 0 = 1) or (alert_schedulable_type = location and alert_schedulable_id = 3))) as exists) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)

====================================

Component	Version
LibreNMS	1.60-8-g6285caba5
DB Schema	2020_02_05_224042_device_inserted_null (158)
PHP	7.2.11
MySQL	8.0.17
RRDTool	1.7.0
SNMP	NET-SNMP 5.8
====================================

[OK] Composer Version: 1.9.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct

Health pages show everthing it polling and mini graphs show, but anytime i click a device i flag the error above in logs and get an whoops error screen.

Trying to display all devices just displays no results found.

TheGreatDoc · 7 February 2020 06:23

Do you have any scheduled maintenance?

kbwilliams · 7 February 2020 16:18

I saw this in the forums, and no I do not.

TheGreatDoc · 8 February 2020 06:16

Try checking directly in the database instead using the gui.

It looks like you have one with 0000-00-00 date and that is invalid so its not going to show up in the gui

kbwilliams · 8 February 2020 18:26

How can I check that, what table?

mysql> select * from alert_schedulables;
Empty set (0.00 sec)
mysql> select * from alert_schedule;
Empty set (0.00 sec)

I believe originally that this was ‘’ and the 00’s came as a result of trying
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
in mysql.cnf, after that it changed to the all 0’s.

kbwilliams · 9 February 2020 20:56

Current errors…

[2020-02-09 12:55:11] production.ERROR: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00 or end_recurring_dt = )) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null or recurring_day = ))) and ((alert_schedulable_type = device and alert_schedulable_id = 7) or (alert_schedulable_type = device_group and alert_schedulable_id in (1)))) as exists) {“exception”:"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00 or end_recurring_dt = )) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null or recurring_day = ))) and ((alert_schedulable_type = device and alert_schedulable_id = 7) or (alert_schedulable_type = device_group and alert_schedulable_id in (1)))) as exists) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)
[stacktrace]
#0 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback(‘select exists(s…’, Array, Object(Closure))
#1 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run(‘select exists(s…’, Array, Object(Closure))
#2 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2426): Illuminate\Database\Connection->select(‘select exists(s…’, Array, true)
#3 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(1353): Illuminate\Database\Query\Builder->exists()
#4 /opt/librenms/app/Models/Device.php(219): Illuminate\Database\Eloquent\Builder->__call(‘exists’, Array)
#5 /opt/librenms/LibreNMS/Alert/AlertUtil.php(199): App\Models\Device->isUnderMaintenance()
#6 /opt/librenms/LibreNMS/Alert/AlertRules.php(46): LibreNMS\Alert\AlertUtil::isMaintenance(7)
#7 /opt/librenms/poller.php(180): LibreNMS\Alert\AlertRules->runRules(7)

[2020-02-09 12:55:34] production.ERROR: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00 or end_recurring_dt = )) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null or recurring_day = ))) and ((alert_schedulable_type = device and alert_schedulable_id = 2) or (alert_schedulable_type = device_group and alert_schedulable_id in (1)))) as exists) {“exception”:"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00 or end_recurring_dt = )) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null or recurring_day = ))) and ((alert_schedulable_type = device and alert_schedulable_id = 2) or (alert_schedulable_type = device_group and alert_schedulable_id in (1)))) as exists) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)
[stacktrace]
#0 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback(‘select exists(s…’, Array, Object(Closure))
#1 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run(‘select exists(s…’, Array, Object(Closure))
#2 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2426): Illuminate\Database\Connection->select(‘select exists(s…’, Array, true)

kbwilliams · 9 February 2020 20:57

Tried creating new group and grouping devices and new user account with no change.

kbwilliams · 11 February 2020 03:15

Anyone have any thoughts on this?

ForkZ · 11 February 2020 03:54

just as my problems…
try to delete the lines from app/Models/AlertSchedule.php

kbwilliams · 11 February 2020 04:04

Yeah I’d seen that post and have tried that as well. It changes the error but devices still don’t list.

[2020-02-10 19:57:13] production.ERROR: syntax error, unexpected ‘}’ {“userId”:1,“exception”:"[object] (Symfony\Component\Debug\Exception\FatalThrowableError(code: 0): syntax error, unexpected ‘}’ at /opt/librenms/app/Models/AlertSchedule.php:58)
[stacktrace]

I don’t see a hanging } so don’t get this error. Line 58 is the first }); under your first delete.

{
// TODO use Carbon?
return $query->where(function ($query) {
$query->where(function ($query) {
// Non recurring simply between start and end
$query->where(‘recurring’, 0)
->where(‘start’, ‘<=’, DB::raw(‘NOW()’))
->where(‘end’, ‘>=’, DB::raw(‘NOW()’));
})->orWhere(function ($query) {
$query->where(‘recurring’, 1)
// Check the time is after the start date and before the end date, or end date is not set
->where(function ($query) {
$query->where(‘start_recurring_dt’, ‘<=’, DB::raw(“date_format(NOW(), ‘%Y-%m-%d’)”))
->where(function ($query) {
$query->where(‘end_recurring_dt’, ‘>=’, DB::raw(“date_format(NOW(), ‘%Y-%m-%d’)”))
->orWhereNull(‘end_recurring_dt’)
->orWhere(‘end_recurring_dt’, ‘0000-00-00’)
});
})
// Check the time is between the start and end hour/minutes/seconds
->where(‘start_recurring_hr’, ‘<=’, DB::raw(“date_format(NOW(), ‘%H:%i:%s’)”))
->where(‘end_recurring_hr’, ‘>=’, DB::raw(“date_format(NOW(), ‘%H:%i:%s’)”))
// Check we are on the correct day of the week
->where(function ($query) {
/** @var Builder $query */
$query->where(‘recurring_day’, ‘like’, DB::raw(“CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’)”))
->orWhereNull(‘recurring_day’)
});
});
});
}

ForkZ · 11 February 2020 04:11

did u move the “;” to the end of each sql?
it seems that you missed the “;”

kbwilliams · 11 February 2020 04:12

Changing lines 58 and 68 from

}); to just ; get me to this new error.

[2020-02-10 20:10:40] production.ERROR: syntax error, unexpected ‘public’ (T_PUBLIC), expecting ‘,’ or ‘)’ {“userId”:1,“exception”:"[object] (Symfony\Component\Debug\Exception\FatalThrowableError(code: 0): syntax error, unexpected ‘public’ (T_PUBLIC), expecting ‘,’ or ‘)’ at /opt/librenms/app/Models/AlertSchedule.php:75)
[stacktrace]

ForkZ · 11 February 2020 04:17

don’t delete }); just add ；

kbwilliams · 11 February 2020 04:24

Ok, that gets me back to the date errors.

[2020-02-10 20:23:09] production.ERROR: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00)) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null))) and ((alert_schedulable_type = device and alert_schedulable_id = 5) or (alert_schedulable_type = device_group and alert_schedulable_id in (1)) or (alert_schedulable_type = location and alert_schedulable_id = 3))) as exists) {“userId”:1,“exception”:"[object] (Illuminate\Database\QueryException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ (SQL: select exists(select * from alert_schedule inner join alert_schedulables on alert_schedule.schedule_id = alert_schedulables.schedule_id where ((recurring = 0 and start <= NOW() and end >= NOW()) or (recurring = 1 and (start_recurring_dt <= date_format(NOW(), ‘%Y-%m-%d’) and (end_recurring_dt >= date_format(NOW(), ‘%Y-%m-%d’) or end_recurring_dt is null or end_recurring_dt = 0000-00-00)) and start_recurring_hr <= date_format(NOW(), ‘%H:%i:%s’) and end_recurring_hr >= date_format(NOW(), ‘%H:%i:%s’) and (recurring_day like CONCAT(’%’, date_format(NOW(), ‘%w’), ‘%’) or recurring_day is null))) and ((alert_schedulable_type = device and alert_schedulable_id = 5) or (alert_schedulable_type = device_group and alert_schedulable_id in (1)) or (alert_schedulable_type = location and alert_schedulable_id = 3))) as exists) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: ‘0000-00-00’ at /opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)
[stacktrace]

kbwilliams · 11 February 2020 04:27

OK, so deleted the following as well

->orWhere(‘end_recurring_dt’, ‘0000-00-00’)
and
->orWhereNull(‘recurring_day’)

and have devices again, woot!

Not sure what happened, this was all working fine up until about a week ago, but yay there’s devices!

ForkZ · 11 February 2020 04:30

another one I didn’t wrote to my post.
change vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php

just allow the lavavel to make the sql mode allow zero_in_ date and zero_date by delete the NO_ZERO_IN_DATE , NO_ZERO_DATE in line 180 and 183(maybe don’t change this line,lazy to recover)
…our poor mysql 8.0.* (>=16)

kbwilliams · 11 February 2020 04:53

Modified that file as well, thanks so much for the help!

Wim_Van_Ommen · 12 February 2020 23:58

Are you by any chance both using MySQL instead of Maria DB? I had the same issue, when I uninstalled MySQL, install Maria DB and restored my database (make a backup first will you ;-)), everything worked without this mod.

kbwilliams · 13 February 2020 02:50

I am using MySQL and apparently when my system upgraded to 8.0.16 and greater it caused these problems.