Field 'last_time' doesn't have a default value

[choefing]

Thanks for the effort to make this plugin work again until whatever final fix.

Plain Cacti 1.2.8 on Debian with MariaDB
Weathermap cloned from git here

I got messages in Cacti pointing to a not existing default value for "last_time".

12/27/2019 15:05:03 - CMDPHP ERRORS DETECTED - DISABLING PLUGIN 'weathermap'

12/27/2019 15:05:03 - CMDPHP PHP ERROR Backtrace: (CactiShutdownHandler())
12/27/2019 15:05:03 - ERROR PHP ERROR in Plugin 'weathermap': Uncaught PDOException: SQLSTATE[HY000]: General error: 1364 Field 'last_value' doesn't have a default value in /opt/cacti-1.2.8/plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php:143 Stack trace: #0 /opt/cacti-1.2.8/plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php(143): PDOStatement->execute(Array) #1 /opt/cacti-1.2.8/plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php(596): WeatherMapDataSource_rrd->wmrrd_read_from_poller_output('/opt/cacti-1.2....', 'AVERAGE', 'now-800', 'now', Array, Array, Object(WeatherMap), 0, Object(WeatherMapLink)) #2 /opt/cacti-1.2.8/plugins/weathermap/lib/Weathermap.class.php(955): WeatherMapDataSource_rrd->ReadData('/opt/cacti-1.2....', Object(WeatherMap), Object(WeatherMapLink)) #3 /opt/cacti-1.2.8/plugins/weathermap/lib/poller-common.php(195): WeatherMap->ReadData() #4 /opt/cacti-1.2.8/plugins/weathermap/setup.php(778): weathermap_run_maps('/opt/cacti-1.2....') #5 /opt/cacti-1.2.8/lib/plugins.php(177): weathermap_poller_bottom(Array) #6 /op in file: /opt/cacti-1.2.8/plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php on line: 143

I tried to fix that in setup.php where the line 370ff
if ( !in_array( 'weathermap_data', $tables ) ) { $sql[] = "CREATE TABLE IF NOT EXISTS weathermap_data (id int(11) NOT NULL auto_increment, rrdfile varchar(255) NOT NULL,data_source_name varchar(19) NOT NULL, last_time int(11) DEFAULT -1 NOT NULL,last_value varchar(255) DEFAULT '' NOT NULL, last_calc varchar(255) DEFAULT '' NOT NULL, sequence int(11) NOT NULL, local_data_id int(11) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY rrdfile (rrdfile), KEY local_data_id (local_data_id), KEY data_source_name (data_source_name) ) ENGINE=MyISAM";

But for me it seems an "uninstall plugin" does not drop the tables / revert the changes done by installing and a re-install also does not update them properly if existent.
Manually dropping the tables weathermap_* and re-adding them seem to not work properly too as there might be actions executed against other tables.

Not a PHP/SQL pro but hope that helps to figure out the issue.

[howardjones]

The only tables Weathermap touches are the weathermap_* ones, and a few settings in settings.

There are a few extra steps where it checks for previous versions of the tables, and adds new fields if necessary.

For historical versions, I've always avoided deleting the tables, because it's so easy to "do a quick install/uninstall" to fix a problem and lose all your config. It's not very clear what the impact will be. If you had a lot of access control config, that would be really annoying.

(Can't speak for any changes Thomas has made for 1.x, of course!)

For this, it looks like changing line 143 of lib/datasources/WeatherMapDataSource_rrd.php from

$statement_insert = $pdo->prepare("INSERT INTO weathermap_data (rrdfile, data_source_name, sequence, local_data_id) VALUES (?,?, 0,?)");

to

$statement_insert = $pdo->prepare("INSERT INTO weathermap_data (rrdfile, data_source_name, sequence, local_data_id, last_value) VALUES (?,?, 0,?,'')");

might also resolve it. This is probably a bug in the standard 0.98a too, with certain mysql/mariadb settings.

[choefing]

1st test

• disabled and deinstalled plugin (webif)
• dropped weathermap* tables (sql)
• deleted all weathermap related entries in settings table (sql)
• applied Howies change only
• installed, activated, configured plugin
• same error msg appeared

2nd test

• disabled and deinstalled plugin (webif)
• dropped weathermap* tables (sql)
• deleted all weathermap related entries in settings table (sql)
• applied Howies change + mine
• installed, activated, configured plugin
• works

As this is a testing system only I can try anything youd like me to do.

[nicolatron]

Had the same problem, for me also uninstalling/installing the plugin did not drop/create tables en the DDBB.
Applying Howie's and choefings changes, fixed it for me.
In my case what i did was change Howie's line.
And in MariaDB:
MariaDB [cacti]> drop table weathermap_data;
MariaDB [cacti]> CREATE TABLE IF NOT EXISTS weathermap_data (
-> id int(11) NOT NULL auto_increment,
-> rrdfile varchar(255) NOT NULL,
-> data_source_name varchar(19) NOT NULL,
-> last_time int(11) DEFAULT -1 NOT NULL,
-> last_value varchar(255) DEFAULT '' NOT NULL,
-> last_calc varchar(255) DEFAULT '' NOT NULL,
-> sequence int(11) NOT NULL,
-> local_data_id int(11) NOT NULL DEFAULT 0,
-> PRIMARY KEY (id), KEY rrdfile (rrdfile),
-> KEY local_data_id (local_data_id),
-> KEY data_source_name (data_source_name)
-> ) ENGINE=MyISAM;

[thurban]

The default values during table creation has been added along with the insert line change