Open-AudIT
http://www.open-audit.org/phpBB3/

[HELP] Upgrade to database 1.12.8 Fails with Error
http://www.open-audit.org/phpBB3/viewtopic.php?f=20&t=6551
Page 1 of 2

Author:  bignick0 [ Wed Aug 24, 2016 5:49 am ]
Post subject:  [HELP] Upgrade to database 1.12.8 Fails with Error

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 150 times

Author:  Mark [ Wed Aug 24, 2016 8:37 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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.

Author:  Mark [ Wed Aug 24, 2016 8:15 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

1.12.8.1 released.

Author:  bignick0 [ Wed Aug 24, 2016 11:02 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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

Author:  Vargas77 [ Fri Sep 02, 2016 5:43 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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

Author:  jpa [ Sat Sep 03, 2016 2:20 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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.

Author:  Mark [ Mon Sep 05, 2016 10:13 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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.

Author:  Vargas77 [ Tue Sep 13, 2016 3:52 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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 3325 times ]

Author:  Mark [ Wed Sep 14, 2016 9:07 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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

Author:  Mark [ Thu Sep 15, 2016 10:21 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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.

Author:  Mark [ Thu Sep 15, 2016 11:10 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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).

Author:  Mark [ Thu Sep 15, 2016 11:58 am ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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.

Author:  jpa [ Thu Sep 15, 2016 1:06 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

Some sort of database corruption?

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

Author:  Mark [ Thu Sep 15, 2016 2:12 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

Nice thought JPA but the customer did exactly that.
They dumped their DB and restored it on a testing system to test the upgrade.

Author:  Mark [ Thu Sep 15, 2016 10:42 pm ]
Post subject:  Re: [HELP] Upgrade to database 1.12.8 Fails with Error

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.

Page 1 of 2 All times are UTC + 10 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/