Open-AudIT https://www.open-audit.org/phpBB3/ |
|
[HELP] Upgrade to database 1.12.8 Fails with Error https://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
|
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
|
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[/code] |
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/ |