search

Cacti / cacti

Cacti ™
http://www.cacti.net
GNU General Public License v2.0
1.64k stars 405 forks source link

This bug is related to bug #5812, after fixing #5812, the error "CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES" started to get reported. #5829

Closed MSS970 closed 1 month ago

MSS970 commented 1 month ago

OS: Windows 2019 CACTI version [1.3.0 Dev]

This bug is related to bug #5812, after fixing #5812, the error "CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES" started to get reported.

2024-09-18 04:31:45 - SQL Backtrace: ( D:\cacti\apache\htdocs\cacti\poller.php[960]:api_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[97]:api_plugin_run_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[243]:weathermap_poller_bottom(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\setup.php[1169]:weathermap_run_maps(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\poller-common.php[430]:WeatherMap->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\datasources\WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), D:\cacti\apache\htdocs\cacti\lib\rrd.php[1063]:boost_fetch_cache_check(), D:\cacti\apache\htdocs\cacti\lib\boost.php[306]:boost_process_poller_output(), D:\cacti\apache\htdocs\cacti\lib\boost.php[775]:db_fetch_assoc_prepared(), D:\cacti\apache\htdocs\cacti\lib\database.php[909]:db_execute_prepared() ) 2024-09-18 04:31:45 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES

MSS970 commented 1 month ago

Good morning @TheWitness , Kindly can you extend your support to fix.

TheWitness commented 1 month ago

@MSS970, I've just committed a fix. Please test ASAP as we are hoping to release by this weekend.

MSS970 commented 1 month ago

@TheWitness , Thanks, I will test it tomorrow morning 4:00 GMT and feedback.

MSS970 commented 1 month ago

@TheWitness , I've applied the fix, I will wait for tomorrow morning 04:00 GMT to provide the feedback.

Much appreciated.

TheWitness commented 1 month ago

@bmfmancini is also trying to reproduce.

MSS970 commented 1 month ago

@TheWitness , The fix did not work, 2 more errors are getting displayed repeatedly:

CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES CMDPHP ERROR: A DB Exec Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCK TABLE data_local AS dl READ LOCAL' at line 1

MSS970 commented 1 month ago

2024-09-18 22:41:50 - SQL Backtrace: (D:\cacti\apache\htdocs\cacti\poller.php[960]:api_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[97]:api_plugin_run_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[243]:weathermap_poller_bottom(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\setup.php[1169]:weathermap_run_maps(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\poller-common.php[430]:WeatherMap->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\datasources\WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), D:\cacti\apache\htdocs\cacti\lib\rrd.php[1063]:boost_fetch_cache_check(), D:\cacti\apache\htdocs\cacti\lib\boost.php[306]:boost_process_poller_output(), D:\cacti\apache\htdocs\cacti\lib\boost.php[782]:db_fetch_assoc_prepared(), D:\cacti\apache\htdocs\cacti\lib\database.php[909]:db_execute_prepared())

2024-09-18 22:41:50 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES

AND

2024-09-18 22:41:50 - SQL Backtrace: (D:\cacti\apache\htdocs\cacti\poller.php[960]:api_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[97]:api_plugin_run_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[243]:weathermap_poller_bottom(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\setup.php[1169]:weathermap_run_maps(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\poller-common.php[430]:WeatherMap->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\datasources\WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), D:\cacti\apache\htdocs\cacti\lib\rrd.php[1063]:boost_fetch_cache_check(), D:\cacti\apache\htdocs\cacti\lib\boost.php[306]:boost_process_poller_output(), D:\cacti\apache\htdocs\cacti\lib\boost.php[763]:db_execute(), D:\cacti\apache\htdocs\cacti\lib\database.php[496]:db_execute_prepared()) 2024-09-18 22:41:50 - CMDPHP ERROR: A DB Exec Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCK TABLE data_local AS dl READ LOCAL' at line 1

MSS970 commented 1 month ago

MySQL version 8.0.32

MSS970 commented 1 month ago

I will try: if ($locks) { db_execute("LOCK TABLE poller_output_boost AS po READ LOCAL"); db_execute("LOCK TABLE data_local AS dl READ LOCAL"); }

OR

if ($locks) {
    db_execute("LOCK TABLES poller_output_boost AS po READ LOCAL, data_local AS dl READ LOCAL");
}

MySQL Reference: https://dev.mysql.com/doc/refman/8.4/en/lock-tables.html LOCK {TABLE | TABLES} tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...

lock_type: { READ [LOCAL] | WRITE }

UNLOCK {TABLE | TABLES}

TheWitness commented 1 month ago

Good update. I suspect that I will have to be a bit more creative here. I've been giving it some more thought.. Keep me posted with your testing.

It's likely going to be better returning to the separate queries combined with a temp table. More to follow.

TheWitness commented 1 month ago

The minimum requirement is going to be rrdtool version 1.5 or above though for sure.

jdcoats commented 1 month ago

same after upgrading to current 1.2.28 branch today

2024/09/18 18:50:34 - CMDPHP ERROR: A DB Exec Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCK TABLE data_local AS dl READ LOCAL' at line 1
2024/09/18 18:50:34 - CMDPHP SQL Backtrace: (/poller.php[941]:api_plugin_hook(), /lib/plugins.php[95]:api_plugin_run_plugin_hook(), /lib/plugins.php[237]:weathermap_poller_bottom(), /plugins/weathermap/setup.php[1169]:weathermap_run_maps(), /plugins/weathermap/lib/poller-common.php[430]:WeatherMap->ReadData(), /plugins/weathermap/lib/WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), /plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), /lib/rrd.php[995]:boost_fetch_cache_check(), /lib/boost.php[299]:boost_process_poller_output(), /lib/boost.php[763]:db_fetch_assoc_prepared(), /lib/database.php[788]:db_execute_prepared())
2024/09/18 18:50:34 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES
2024/09/18 18:50:34 - CMDPHP SQL Backtrace: (/poller.php[941]:api_plugin_hook(), /lib/plugins.php[95]:api_plugin_run_plugin_hook(), /lib/plugins.php[237]:weathermap_poller_bottom(), /plugins/weathermap/setup.php[1169]:weathermap_run_maps(), /plugins/weathermap/lib/poller-common.php[430]:WeatherMap->ReadData(), /plugins/weathermap/lib/WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), /plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), /lib/rrd.php[995]:boost_fetch_cache_check(), /lib/boost.php[299]:boost_process_poller_output(), /lib/boost.php[744]:db_execute(), /lib/database.php[420]:db_execute_prepared())
2024/09/18 18:50:34 - CMDPHP ERROR: A DB Exec Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCK TABLE data_local AS dl READ LOCAL' at line 1
2024/09/18 18:50:34 - CMDPHP SQL Backtrace: (/poller.php[941]:api_plugin_hook(), /lib/plugins.php[95]:api_plugin_run_plugin_hook(), /lib/plugins.php[237]:weathermap_poller_bottom(), /plugins/weathermap/setup.php[1169]:weathermap_run_maps(), /plugins/weathermap/lib/poller-common.php[430]:WeatherMap->ReadData(), /plugins/weathermap/lib/WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), /plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), /lib/rrd.php[995]:boost_fetch_cache_check(), /lib/boost.php[299]:boost_process_poller_output(), /lib/boost.php[763]:db_fetch_assoc_prepared(), /lib/database.php[788]:db_execute_prepared())
2024/09/18 18:50:34 - CMDPHP ERROR: A DB Row Failed!, Error: Table 'po' was not locked with LOCK TABLES
2024/09/18 18:50:34 - CMDPHP SQL Backtrace: (/poller.php[941]:api_plugin_hook(), /lib/plugins.php[95]:api_plugin_run_plugin_hook(), /lib/plugins.php[237]:weathermap_poller_bottom(), /plugins/weathermap/setup.php[1169]:weathermap_run_maps(), /plugins/weathermap/lib/poller-common.php[430]:WeatherMap->ReadData(), /plugins/weathermap/lib/WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), /plugins/weathermap/lib/datasources/WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), /lib/rrd.php[995]:boost_fetch_cache_check(), /lib/boost.php[299]:boost_process_poller_output(), /lib/boost.php[744]:db_execute(), /lib/database.php[420]:db_execute_prepared())
TheWitness commented 1 month ago

Okay, give it one more shot. This time I think it's going to be fine.

TheWitness commented 1 month ago

@MSS970. Please note. Update lib/boost.php. Keep testing reviewing for errors.

MSS970 commented 1 month ago

Good morning @TheWitness , Thanks for the support, it seems to be working fine so far. I will keep on monitoring and report if there is any incident.

Much appreciated

TheWitness commented 1 month ago

Okay, I'm going to close, but like you said keep tracking and update us as conditions on the ground change.

MSS970 commented 1 month ago

Dear @TheWitness , it seems more complicated that expected. one more error:

2024-09-19 10:46:52 - SQL Backtrace: (D:\cacti\apache\htdocs\cacti\poller.php[960]:api_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[97]:api_plugin_run_plugin_hook(), D:\cacti\apache\htdocs\cacti\lib\plugins.php[243]:weathermap_poller_bottom(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\setup.php[1169]:weathermap_run_maps(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\poller-common.php[430]:WeatherMap->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\WeatherMap.class.php[1058]:WeatherMapDataSource_rrd->ReadData(), D:\cacti\apache\htdocs\cacti\plugins\weathermap\lib\datasources\WeatherMapDataSource_rrd.php[647]:rrdtool_function_fetch(), D:\cacti\apache\htdocs\cacti\lib\rrd.php[1063]:boost_fetch_cache_check(), D:\cacti\apache\htdocs\cacti\lib\boost.php[306]:boost_process_poller_output(), D:\cacti\apache\htdocs\cacti\lib\boost.php[793]:db_execute(), D:\cacti\apache\htdocs\cacti\lib\database.php[496]:db_execute_prepared()) 2024-09-19 10:46:52 - CMDPHP ERROR: A DB Exec Failed!, Error: Unknown table 'cacti.poller_output_boost_temp_1726728412'

TheWitness commented 1 month ago

Okay I'm thinking maybe not enough entropy let me think about this.

TheWitness commented 1 month ago

It's like weather map is beating the snot out of a few local data IDs.

TheWitness commented 1 month ago

Okay, I made an update to lib/boost php. Update and try again.

TheWitness commented 1 month ago

Something's wrong with your line numbers dude 793 is not the correct line for any DB execute statement. Make sure you're pulling from the 1.2.x branch as it does not seem to be the updated file.

MSS970 commented 1 month ago

Okay, I made an update to lib/boost php. Update and try again.

Applied the fix, will monitor and keep you posted. Thanks @TheWitness