perfSONAR User Q&A and Other Discussion

[Jason Zurawski <>]

Hey Zhi-Wei/Kevin;

I will add this to our open bug so that we can take your steps into account 
as the upgrade scripts are modified.  

Thanks;

-jason

On Apr 14, 2014, at 1:34 PM, Zhi-Wei Lu 
<>
 wrote:

> Hi Jason,
> 
> We have made our cacti working again.  Here are what I have done to correct 
> the database incompatibilities between the old and new versions of cacti:
> 
> 1.  Add these tables  to the cacti database according to the cacti.sql file
> plugin_config, plugin_db_changes, plugin_hooks, and plugin_realms
> 
> 2. Modify the two table host and poller_item
> 
> ALTER TABLE host ADD device_threads tinyint(2) unsigned NOT NULL DEFAULT 
> '1' after max_oids;
> ALTER TABLE poller_item ADD present tinyint NOT NULL DEFAULT '1' after 
> action;
> 
> After I made these changes, our cacti poller and graph started to work 
> again.
> 
> I need to hop onto other work, I am sure that other tables of cacti might 
> need to be modified as well, I will report back if I see one.
> 
> Thank you for your help.
> 
> Zhi-Wei Lu
> IET-CR-Network Operations Center
> University of California, Davis
> (530) 752-0155
> 
> -----Original Message-----
> From: 
> 
>  
> [mailto:]
>  On Behalf Of Zhi-Wei Lu
> Sent: Saturday, April 12, 2014 2:47 PM
> To: 
> ;
>  Kevin Kawaguchi
> Subject: Re: [perfsonar-user] cacti failed on 
> perl-perfSONAR_PS-Toolkit-3.3.2-13.psPS
> 
> Hi Jason,
> 
> Thank you very much for your help.  I have added these tables below, 
> indeed, the cacti log file is much quieter now.  While we move on to 
> change the host ip, I noticed that the TABLE "host" has an extra entry 
> in the table definition, and I am sure, other tables might have 
> modifications as well.  I will track them down and report back to the 
> list. /opt/perfsonar_ps/toolkit/web/root/admin/cacti/cacti.sql
> 
> It appears to me that the following file
> /opt/perfsonar_ps/toolkit/web/root/admin/cacti/cacti.sql
> is the definition of the latest from cacto
> 
> While the following file pertains to the older version of cacti, perlSonar 
> distro might need to reconcile those two files!
> /opt/perfsonar_ps/toolkit/scripts/cacti_toolkit_init.sql
> 
> Again, thank you very much for your help and I think that I am on the 
> right track now!
> 
> On 04/11/2014 11:39 AM, Jason Zurawski wrote:
>> Hey Zhi-Wei/Kevin;
>> 
>> Being that cacti is an external product I am not sure we can offer any 
>> more positive suggestions on how to proceed.  At this stage you may have 
>> more luck asking on the cacti mailing list.
>> 
>> There are two SQL files on the host that would be valuable to look at if 
>> you do decide to do a manual add of tables:
>> 
>>> /opt/perfsonar_ps/toolkit/scripts/cacti_toolkit_init.sql
>>> /opt/perfsonar_ps/toolkit/web/root/admin/cacti/cacti.sql
>> 
>> The first is what our scripts create (and we will file a bug to modify 
>> this), and the second is what ships with the 0.8.8b version of cacti.  
>> This second one probably has the definitions to create the missing tables, 
>> these were added to my instance when I ran the script:
>> 
>>> plugin_config
>>> plugin_db_changes
>>> plugin_hooks
>>> plugin_realms
>> 
>> I suppose the other alternative is to downgrade to a version of cacti that 
>> doesn't have the plugin architecture (0.8.7 most likely).
>> 
>> Thanks, and sorry we can't help you fully resolve this;
>> 
>> -jason
>> 
>> On Apr 11, 2014, at 2:26 PM, Zhi-Wei Lu 
>> <>
>>  wrote:
>> 
>>> Hi Jason,
>>> 
>>> The upgrade script didn't work, it gave many warning messages, and it 
>>> says that your cacti is already up to date.
>>> 
>>> PHP Warning:  date(): It is not safe to rely on the system's timezone 
>>> settings. You are *required* to use the date.timezone settin\
>>> g or the date_default_timezone_set() function. In case you used any of 
>>> those methods and you are still getting this warning, you m\
>>> ost likely misspelled the timezone identifier. We selected 
>>> 'America/Los_Angeles' for 'PDT/-7.0/DST' instead in /opt/perfsonar_ps/t\
>>> oolkit/web/root/admin/cacti/lib/functions.php on line 486
>>> 
>>> Your Cacti is already up to date.
>>> 
>>> We might have to manually adding those missing tables in, but the 
>>> existing tables could be altered as well.
>>> 
>>> 
>>> Zhi-Wei Lu
>>> IET-CR-Network Operations Center
>>> University of California, Davis
>>> (530) 752-0155
>>> 
>>> -----Original Message-----
>>> From: Jason Zurawski 
>>> [mailto:]
>>> Sent: Friday, April 11, 2014 6:40 AM
>>> To: Zhi-Wei Lu; Kevin Kawaguchi
>>> Cc: 
>>> 
>>> Subject: Re: [perfsonar-user] cacti failed on 
>>> perl-perfSONAR_PS-Toolkit-3.3.2-13.psPS
>>> 
>>> Hey Zhi-Wei/Kevin;
>>> 
>>> From reading online documentation, version 0.8.8 of CACTI now includes 
>>> the Plugin Architecture directly as you note.  It appears there is a 
>>> database upgrade script that can be run to add the new tables.  I tried 
>>> this on a local instance, but I didn't have any historical data:
>>> 
>>>> php 
>>>> /opt/perfsonar_ps/toolkit/web/root/admin/cacti/cli/upgrade_database.php
>>> 
>>> I would recommend before trying this to make a mysql backup (note, below 
>>> command may need a -u or -p option supplied, depending on what the 
>>> permissions of the database are):
>>> 
>>>> mysqldump -l --add-drop-table cacti > mysql.cacti
>>> Let us know if this works, and we can file a bug to improve our database 
>>> upgrade scripts.
>>> 
>>> Thanks;
>>> 
>>> -jason
>>> 
>>> On Apr 11, 2014, at 3:04 AM, Zhi-Wei Lu 
>>> <>
>>>  wrote:
>>> 
>>>> Hi Jason,
>>>> 
>>>> I went back to the source rpm and perfSONAR_PS-Toolkit-3.3.1.1.tar.gz 
>>>> introduces extra plugin_hooks tables (and other tables) while 
>>>> perfSONAR_PS-Toolkit-3.3.1.tar.gz didn't have those tables!
>>>> 
>>>> 
>>>> On 04/10/2014 11:24 PM, Zhi-Wei Lu wrote:
>>>>> Hi Jason,
>>>>> 
>>>>> The mysql database for cacti is actually clean, I can query the 
>>>>> database either as user "root" or "cacti", but the cacti database built 
>>>>> from the previous version of perfSonar is incompatible with the current 
>>>>> version.
>>>>> table plugin_hooks along with many other tables required in the current 
>>>>> version do not exist in the previous version.
>>>>> 
>>>>> On 04/10/2014 04:52 PM, Jason Zurawski wrote:
>>>>>> Hey Zhi-Wei/Kevin;
>>>>>> 
>>>>>> This is just a guess - but maybe mysql needs to have a repair done?  
>>>>>> Some forums I read on the cacti web site seem to indicate that is a 
>>>>>> good first thing to try:
>>>>>> 
>>>>>>> mysqlcheck -u aaa -p --auto-repair --optimize --all-databases
>>>>>> Let us know if that has any positive impact;
>>>>>> 
>>>>>> Thanks;
>>>>>> 
>>>>>> -jason
>>>>>> 
>>>>>> On Apr 10, 2014, at 7:25 PM, Zhi-Wei Lu 
>>>>>> <>
>>>>>>  wrote:
>>>>>> 
>>>>>>> Hi all,
>>>>>>> We recently update our system to the latest perfSonar version using 
>>>>>>> "yum update" last week and rebooted system.   We noticed that today 
>>>>>>> that our cacti environment failed since our last reboot.
>>>>>>> There are tons of error message such as the following in the log file:
>>>>>>> 04/10/2014 03:02:37 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, 
>>>>>>> Error:'1146', SQL:"SELECT 1 AS id, ph.name, ph.file, ph.function  
>>>>>>> FROM plugin_hooks AS ph  LEFT JOIN plugin_config AS pc  ON 
>>>>>>> pc.directory=ph.name  WHERE ph.status = 1 AND hook = 'config_arrays'  
>>>>>>> AND ph.name IN ('settings', 'boost', 'dsstats')  UNION  SELECT pc.id, 
>>>>>>> ph.name, ph.file, ph.function  FROM plugin_hooks AS ph  LEFT JOIN 
>>>>>>> plugin_config AS pc  ON pc.directory=ph.name  WHERE ph.status = 1 AND 
>>>>>>> hook = 'config_arrays'  AND ph.name NOT IN ('settings', 'boost', 
>>>>>>> 'dsstats')  ORDER BY id ASC"
>>>>>>> Upon investigating the issue, we noticed that
>>>>>>> /opt/perfsonar_ps/toolkit/web/root/admin/cacti/cacti.sql
>>>>>>> has all the definitions of the missing SQL tables, but our current 
>>>>>>> /var/lib/mysql/cacti does not have the proper table.
>>>>>>> Apparently, this current version if cacti is different from the 
>>>>>>> previous version from the perfSonar packages, what is the proper way 
>>>>>>> to upgrade the mysql cacti database to be consistent with the current 
>>>>>>> version. We would like to keep our previous data if it is possible.
>>>>>>> Thank you very much for your help.
>>>>>>> Zhi-Wei Lu
>>>>>>> IET-CR-Network Operations Center
>>>>>>> University of California, Davis
>>>>>>> (530) 752-0155
> 
> -- 
> Zhi-Wei Lu
> IET-CR-Network Operations Center
> University of California, Davis
> (530) 752-0155