Upgrade from 3.2.12->3.3.0.rc1

[palme117]

Hello! I was following this guide http://otrs.github.io/doc/manual/admin/ ... ading.html to upgrade my Ubuntu 12.04 install from 3.2.12 to 3.3.0.rc1. I get the same problems seen here in Davx's second post: viewtopic.php?t=16241. The only difference is that it's not the PostMaster, it's the Scheduler.
crythias suggested the schema was likely corrupted somewhere, but I can't figure out how I'm doing it. The 3.2.12 install is stable and produces no errors. The tables in my database are mostly MyISAM and I have tried converting the few InnoDB tables to MyISAM as well as converting the 80 MyISAM tables to InnoDB as suggested in step 9. Both methods produce the same result. Any suggestions?

Things I have tried further:
http://www.openredes.com/2014/02/15/otr ... ng-debian/
Jumping to 3.3.6 or higher and used scheduler_watchdog
Upgrading to 3.3.0.rc1 with the failing scheduler, then upgrading to 4.0.6 (same errors, just with 4.0.6)

[crythias]

What *exactly* is the error, and you should have gone directly from 3.2.x -> 3.3.latest, whatever it is.

[jojo]

never go to RC versions...

please always use the latest version of the target version

[palme117]

Ok, retried going from 3.2.12 -> 3.3.12(latest 3.3.x). The first error I get is after CheckDB is happy with my database and I run "cat otrs/scripts/DBUpdate-to-3.3.mysql.sql | mysql -p -f -u root otrs":

Code: Select all

INFO: Foreign key constraint FK_standard_response_valid_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_valid_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_queue_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_queue_id_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_queue_standard_response_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_response_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_attachment_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_standard_attachment_id_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_create_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_change_by_id does not exist, skipping.
INFO: Foreign key constraint FK_standard_response_attachment_change_by_id does not exist, skipping.
ERROR 1091 (42000) at line 109: Can't DROP 'standard_response_name'; check that column/key exists

Throwing in the DBUpdate-to-3.3.pl script output just in case; ran with "su -c "otrs/scripts/DBUpdate-to-3.3.pl" -s /bin/bash otrs"

Code: Select all

Migration started...

Step 1 of 13: Refresh configuration cache... 
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
[Wed Mar 18 09:57:19 2015] DBUpdate-to-3.3.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Step 2 of 13: Check framework version... done.

Step 3 of 13: Generate MessageID md5sums... done.

Step 4 of 13: Migrate old settings... done.

Step 5 of 13: Migrate OTRSExternalTicketNumberRecognition... done.

Step 6 of 13: Checking Standard Template table columns... 
Check if 'template_type' columns exists.
'template_type' column not found, create it.
ALTER TABLE standard_template ADD template_type VARCHAR (100) NULL
UPDATE standard_template SET template_type = 'Answer' WHERE template_type IS NULL
ALTER TABLE standard_template CHANGE template_type template_type VARCHAR (100) DEFAULT 'Answer' NOT NULL
done.

Step 7 of 13: Updating Queue Standard Template relations table... 
Cleaning queue_standard_template table
Creating new Foreign Keys for queue_standard_template table

--- Note: ---
If you have already run this script before then the Foreign Keys are already set and you might see errors regarding 'duplicate key' or 'constrain already exists', that's fine, no need to worry!
---

ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_standard_template_id_id FOREIGN KEY (standard_template_id) REFERENCES standard_template (id)
ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_queue_id_id FOREIGN KEY (queue_id) REFERENCES queue (id)
ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_create_by_id FOREIGN KEY (create_by) REFERENCES users (id)
ALTER TABLE queue_standard_template ADD CONSTRAINT FK_queue_standard_template_change_by_id FOREIGN KEY (change_by) REFERENCES users (id)
done.

Step 8 of 13: Migrate OTRSGenericStandardTemplates... done.

Step 9 of 13: Checking if ACL tables already exist... Check if ACL table exists.
ACL tables not found, create it.
CREATE TABLE acl (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR (200) NOT NULL,
    comments VARCHAR (250) NULL,
    description VARCHAR (250) NULL,
    valid_id SMALLINT NOT NULL,
    stop_after_match SMALLINT NULL,
    config_match LONGBLOB NULL,
    config_change LONGBLOB NULL,
    create_time DATETIME NOT NULL,
    create_by INTEGER NOT NULL,
    change_time DATETIME NOT NULL,
    change_by INTEGER NOT NULL,
    PRIMARY KEY(id),
    UNIQUE INDEX acl_name (name)
)
CREATE TABLE acl_sync (
    acl_id VARCHAR (200) NOT NULL,
    sync_state VARCHAR (30) NOT NULL,
    create_time DATETIME NOT NULL,
    change_time DATETIME NOT NULL
)
ALTER TABLE acl ADD CONSTRAINT FK_acl_create_by_id FOREIGN KEY (create_by) REFERENCES users (id)
ALTER TABLE acl ADD CONSTRAINT FK_acl_change_by_id FOREIGN KEY (change_by) REFERENCES users (id)
ALTER TABLE acl ADD CONSTRAINT FK_acl_valid_id_id FOREIGN KEY (valid_id) REFERENCES valid (id)
done.

Step 10 of 13: Uninstall Merged Feature Add-Ons... done.

Step 11 of 13: Delete the files that are not longer needed... done.

Step 12 of 13: Clean up the cache... done.

Step 13 of 13: Refresh configuration cache another time... 
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
[Wed Mar 18 09:57:27 2015] DBUpdate-to-3.3.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 7.
[Wed Mar 18 09:57:27 2015] DBUpdate-to-3.3.pl: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
done.

Migration completed!

EDIT: I cp'd scheduler_watchdog.dist as scheduler_watchdog in the same directory and reset Cron.sh as user otrs
And here is the error I run into with the scheduler:

Code: Select all

******@******:/opt$ su -c "otrs/bin/otrs.Scheduler.pl -a start" -s /bin/bash otrs
DBD::mysql::st execute failed: Unknown column 'process_change' in 'field list' at /opt/otrs-3.3.12/Kernel/System/DB.pm line 644.
ERROR: OTRS-otrs.Scheduler-10 Perl: 5.14.2 OS: linux Time: Wed Mar 18 10:07:38 2015

 Message: Unknown column 'process_change' in 'field list', SQL: '
            SELECT process_name, process_id, process_host, process_create, process_change
            FROM process_id
            WHERE process_name = ? LIMIT 1'

 Traceback (4931): 
   Module: Kernel::System::PID::PIDGet (OTRS 3.3.12) Line: 187
   Module: main::_Start (unknown version) Line: 213
   Module: otrs/bin/otrs.Scheduler.pl (unknown version) Line: 161

******@******:/opt$ su -c "otrs/bin/otrs.Scheduler.pl -w 1" -s /bin/bash otrs
DBD::mysql::st execute failed: Unknown column 'process_change' in 'field list' at /opt/otrs-3.3.12/Kernel/System/DB.pm line 644.
ERROR: OTRS-otrs.Scheduler-10 Perl: 5.14.2 OS: linux Time: Wed Mar 18 10:07:51 2015

 Message: Unknown column 'process_change' in 'field list', SQL: '
            SELECT process_name, process_id, process_host, process_create, process_change
            FROM process_id
            WHERE process_name = ? LIMIT 1'

 Traceback (4961): 
   Module: Kernel::System::PID::PIDGet (OTRS 3.3.12) Line: 187
   Module: main::_Status (unknown version) Line: 604
   Module: main::_WatchDog (unknown version) Line: 786
   Module: otrs/bin/otrs.Scheduler.pl (unknown version) Line: 84

DBD::mysql::st execute failed: Unknown column 'process_change' in 'field list' at /opt/otrs-3.3.12/Kernel/System/DB.pm line 644.
ERROR: OTRS-otrs.Scheduler-10 Perl: 5.14.2 OS: linux Time: Wed Mar 18 10:07:51 2015

 Message: Unknown column 'process_change' in 'field list', SQL: '
            SELECT process_name, process_id, process_host, process_create, process_change
            FROM process_id
            WHERE process_name = ? LIMIT 1'

 Traceback (4961): 
   Module: Kernel::System::PID::PIDGet (OTRS 3.3.12) Line: 187
   Module: main::_Start (unknown version) Line: 213
   Module: main::_WatchDog (unknown version) Line: 788
   Module: otrs/bin/otrs.Scheduler.pl (unknown version) Line: 84

Thank you for looking!

[palme117]

Any ideas? If not, I'm going to try doing a fresh 4.0.6 install and manually moving certain tables over.
Thanks!

[crythias]

These messages feel like the upgrade to 3.2 wasn't valid.

[palme117]

I still have the 3.2 version live and a VM copy. Any way I could check? Nothing in the error logs on the live version.

[schulmann]

Any way I could check?

You could do a fresh install of version 3.2 on another machine and then a comparison of the database schemas.

[palme117]

Hey guys, just wanted to give a final update. I compared the 3.2.12 tables on the live database to a fresh 3.2.12 install and..I have no idea how our live version runs with no errors. Anyways, I used the fresh 3.2.12 database and wrote some insert statements to pull from the corrupt DB. Once everything was in place I upgraded to 3.3.12, then to 4.0.6. The new system is working fine and will soon become our live version.