Open-AudIT

What's on your network?
It is currently Sun Jan 21, 2018 9:02 am

All times are UTC + 10 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Wed Aug 24, 2016 5:49 am 
Offline
Newbie

Joined: Sat Jun 04, 2016 3:26 am
Posts: 9
Here is my setup:

You are running version 1.12.6 of Open-AudIT.
Your Host is: openaudit, and it's OS is Linux.
Your database platform is mysql (version 5.7.12).
Your web server is Apache/2.4.18 (Fedora) PHP/5.6.22 .
Your PHP version is 5.6.22 and it's running in timezone America/New_York.

I am running Fedora 23 and I ran into some problems initially installing Open-Audit regarding mysql. The installer never seems to believe that I have mysql installed, though it clearly is, as can be seen above. I was able to get Open-Audit 1.12.6 running with no issue and I have been using it for a few months. I am preparing to expand our usage of it, as well as grow into the full Enterprise version, so I was performing an upgrade today.

As I ran the installer, unfortunately it also failed to detect mysql. I proceeded through the installation and it seemed to complete correctly. I was able to connect to the interface and received instruction to upgrade the database. This is where the failure occurred (see below):


Quote:
A Database Error Occurred

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (`openaudit`.`#sql-8c0_b00`, CONSTRAINT `audit_log_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`id`) ON DELETE CASCADE)

ALTER TABLE audit_log ADD CONSTRAINT audit_log_system_id FOREIGN KEY (system_id) REFERENCES system (id) ON DELETE CASCADE

Filename: controllers/admin.php

Line Number: 5312


After this happened, I was no longer able to connect to the Open-Audit interface at all. I am now receiving the following error:

Quote:
A Database Error Occurred

Error Number: 1054

Unknown column 'system_id' in 'field list'

/* M_oa_config::load_config */ SELECT count(system_id) as device_count FROM system WHERE man_status = 'production'

Filename: models/m_oa_config.php

Line Number: 166


Can you offer any suggestions? Thanks,

-Nick


Attachments:
File comment: Log of installer for Open-Audit 1.12.8
Open-Audit-InstallerLog.txt [11.51 KiB]
Downloaded 152 times
Top
 Profile  
Reply with quote  
PostPosted: Wed Aug 24, 2016 8:37 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
Thanks for reporting this. This is the second report I have had of it. I have not been able to replicate it though :-(
I'm about to release 1.12.8.1 which should fix whatever is causing this and worst case - it will log every upgrade SQL statement before it runs.
Give me a few hours, check if 1.12.8.1 is available and try upgrading to it.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Wed Aug 24, 2016 8:15 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
1.12.8.1 released.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Wed Aug 24, 2016 11:02 pm 
Offline
Newbie

Joined: Sat Jun 04, 2016 3:26 am
Posts: 9
Mark - thanks for the quick response.

I ran the new 1.12.8.1 installer and the web instance was restored and asked me again to perform the database upgrade. Sadly this was the result this time:

Quote:
A Database Error Occurred

Error Number: 1091

Can't DROP 'id'; check that column/key exists

ALTER TABLE system DROP KEY id

Filename: controllers/admin.php

Line Number: 5352


Each time I go to the main page I am presented with the request to perform the database upgrade, and the failure is the same. So, this is the state I am currently stuck in. I attempted to restore the openaudit database to the pre-upgraded version and I am receiving an error and unable to roll back:

Quote:
[root@openaudit root]# mysql -u openaudit -popenauditpassword openaudit < /root/temp/root/openaudit_backup.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1215 (HY000) at line 56: Cannot add foreign key constraint


Any recommendations for moving forward?

-Nick


Top
 Profile  
Reply with quote  
PostPosted: Fri Sep 02, 2016 5:43 pm 
Offline
Newbie

Joined: Wed Aug 31, 2016 11:10 pm
Posts: 5
Hello,
i got same error when tried upgrade from 1.12.6 to 1.12.8.2 (OS ubuntu):
A Database Error Occurred
Error Number: 1091
Can't DROP 'id'; check that column/key exists
ALTER TABLE system DROP KEY id
Filename: controllers/admin.php
Line Number: 5352


Top
 Profile  
Reply with quote  
PostPosted: Sat Sep 03, 2016 2:20 am 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1226
I can't replicate the DROP KEY id error any way I've tried upgrading OA.

I would restore from backup and make sure the system table has the "id", "id2", "id3" indexes. We could more forward depending on the status of those indexes in your 1.12.6 DB.


Top
 Profile  
Reply with quote  
PostPosted: Mon Sep 05, 2016 10:13 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
I'm guessing they have run the database upgrade (inside OA) twice. Don't do that or you will get an error.
In future I'll try to make the DB upgrade more error proof.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Tue Sep 13, 2016 3:52 pm 
Offline
Newbie

Joined: Wed Aug 31, 2016 11:10 pm
Posts: 5
Hello,
do you have instruction how upgrade database (not OAE). My action:
1. run OAE-Linux-x86_64-release_1.12.8.2.run
2. in script accept database backup and so on
3. restart OAE service
4. Login
5. After login OAE will (must do) upgrade DB
6. Got error
7. Stuck
In OAE wiki can't find that instruction or recommendation.
System table has id's Indexes


Attachments:
OAESystemTableIndexes.JPG
OAESystemTableIndexes.JPG [ 17.37 KiB | Viewed 3411 times ]
Top
 Profile  
Reply with quote  
PostPosted: Wed Sep 14, 2016 9:07 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
If anyone has these errors, can you please post here the result of the below command (attach it as a file). It will dump the database structure only (not the actual data). We can then try to replicate the issue.
Code:
mysqldump -u openaudit -popenauditpassword --no-data openaudit > openaudit_no_data.sql

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Sep 15, 2016 10:21 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
FYI - We have a customer having this issue. Just to keep you all in the loop, I tried the following.

Took his schema only dump and added config and user data to it.
Restored it.
Set my install of OA to use the 1.12.8.2 files.
Logged in and got the upgrade prompt.
Upgraded the DB.
No issues.

Took his schema only dump and added config and user data to it.
Restored it.
Set my install of OA to use the 1.12.6 files (he is on 1.12.6).
Logged in.
Discovered a couple of devices.
Set my install of OA to use the 1.12.8.2 files.
Logged in and got the upgrade prompt.
Upgraded the DB.
No issues.

As stated before - I cannot replicate this issue but I know it's happening (obviously I believe everyone that's reported it).

I'm starting to think it might be related to the version of MySQL or even PHP. I haven't dropped this case so stay tuned for further updates.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Sep 15, 2016 11:10 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
Just tried upgrading a Centos 6 machine from 1.12.2 (with device data) to 1.12.8.2 and it worked without issue.
Next step is to try a genuine RedHat 6 machine (our customer is running on RH6).

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Sep 15, 2016 11:58 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
Dammit - RedHat 6 running 1.12.6 (including data) to 1.12.8.2 upgrade worked as expected.
I'm just about out of idea's. Will give it some more thought while doing other tasks today.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Sep 15, 2016 1:06 pm 
Offline
Moderator

Joined: Fri Jul 20, 2007 8:27 am
Posts: 1226
Some sort of database corruption?

Maybe a dump and restore if the affected database is small enough.


Top
 Profile  
Reply with quote  
PostPosted: Thu Sep 15, 2016 2:12 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
Nice thought JPA but the customer did exactly that.
They dumped their DB and restored it on a testing system to test the upgrade.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Sep 15, 2016 10:42 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1933
Location: Brisbane, Australia
PROBLEM FOUND!!!

On a call with the customer tonight to resolve this (it's 11pm here - see what support gets you!). It's a data issue. When we try to create an index the table in question has a value in table.system_id that doesn't exist in system.id - hence MySQL refuses to create the index. I'm working on a fix for the customer ASAP and they will get a standalone script to fix the issue. The fix will be incorporated into the next release of Open-AudIT.

Mark.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group