perfSONAR User Q&A and Other Discussion

[Zhi-Wei Lu <>]

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