After update on ubuntu 18 to mysql 8 i found many DB errors and the devices and detail pages are empty. Executung the sugested SQl commands don’t help.
Downgrade to mysql 5.7 dont work because of apt errors.
any suggestions are welcome
thanks
(can not include the whole validate.php output it is to long)
librenms.log:
[2020-02-18 15:55:42] 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 = 4) or (alert_schedulable_type = device_group and 0 = 1) or (alert_schedulable_type = location and alert_schedulable_id = 20))) 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 = 4) or (alert_schedulable_type = device_group and 0 = 1) or (alert_schedulable_type = location and alert_schedulable_id = 20))) 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]
./validate.php
| Component | Version |
|---|---|
| LibreNMS | 1.60-47-gc8bdd10e6 |
| DB Schema | 2020_02_10_223323_create_alert_location_map_table (159) |
| PHP | 7.3.14-6+ubuntu18.04.1+deb.sury.org+1 |
| MySQL | 8.0.19 |
| RRDTool | 1.7.0 |
| SNMP | NET-SNMP 5.7.3 |
====================================
[OK] Composer Version: 1.9.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (access_points/accesspoint_id)
[FAIL] Database: incorrect column (access_points/device_id)
[FAIL] Database: incorrect column (access_points/radio_number)
[FAIL] Database: incorrect column (access_points/channel)
[FAIL] Database: incorrect column (access_points/txpow)
[FAIL] Database: incorrect column (access_points/radioutil)
[FAIL] Database: incorrect column (access_points/numasoclients)
[FAIL] Database: incorrect column (access_points/nummonclients)
[FAIL] Database: incorrect column (access_points/numactbssid)
[FAIL] Database: incorrect column (access_points/nummonbssid)
[FAIL] Database: incorrect column (access_points/interference)
[FAIL] Database: incorrect column (alerts/id)
[FAIL] Database: incorrect column (alerts/device_id)
[FAIL] Database: incorrect column (alerts/rule_id)
[FAIL] Database: incorrect column (alerts/state)
[FAIL] Database: incorrect column (alerts/alerted)
[FAIL] Database: incorrect column (alerts/open)
[FAIL] Database: incorrect column (alert_device_map/id)
[FAIL] Database: incorrect column (alert_device_map/rule_id)
[FAIL] Database: incorrect column (alert_device_map/device_id)
[FAIL] Database: incorrect column (alert_group_map/id)
[FAIL] Database: incorrect column (alert_group_map/rule_id)
[FAIL] Database: incorrect column (alert_group_map/group_id)
[FAIL] Database: incorrect column (alert_location_map/id)
[FAIL] Database: incorrect column (alert_location_map/rule_id)
[FAIL] Database: incorrect column (alert_location_map/location_id)
[FAIL] Database: incorrect column (alert_log/id)
[FAIL] Database: incorrect column (alert_log/rule_id)
[FAIL] Database: incorrect column (alert_log/device_id)
[FAIL] Database: incorrect column (alert_log/state)
[FAIL] Database: incorrect column (alert_rules/id)
[FAIL] Database: incorrect column (alert_schedulables/item_id)
[FAIL] Database: incorrect column (alert_schedulables/schedule_id)
[FAIL] Database: incorrect column (alert_schedulables/alert_schedulable_id)
[FAIL] Database: incorrect column (alert_schedule/schedule_id)
[FAIL] Database: incorrect column (alert_schedule/recurring)
[FAIL] Database: incorrect column (alert_templates/id)
[FAIL] Database: incorrect column (alert_template_map/id)
[FAIL] Database: incorrect column (alert_template_map/alert_templates_id)
[FAIL] Database: incorrect column (alert_template_map/alert_rule_id)
[FAIL] Database: incorrect column (alert_transports/transport_id)
[FAIL] Database: incorrect column (alert_transport_groups/transport_group_id)
and 661 more...