/devices webpage no results found

[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.

Do you have any scheduled maintenance?

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

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

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.

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)

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

Anyone have any thoughts on this?

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

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’)
});
});
});
}

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

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]


don’t delete }); just add

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]

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!

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)

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

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.

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