Upgrading OTRS 3.1.x (SQL errors)

[Romaing]

[First cron issue solved, please check 2nd post for SQL one]

Hi,

I'm trying to upgrade a current 3.1.2 install to 3.1.3 on a ubuntu 10.04.

And i fail at the first step, stopping the cron service.

First i need to change the command to service cron stop but it fails as there is no cron service started (as i'm logged in as root). For example stopping apache service gives me no problem, which is normal.

Since i followed this tutorial for installation: http://wiki.otterhub.org/index.php?titl ... %2810.4%29

and i use the official doc for the upgrade: http://doc.otrs.org/3.1/en/html/upgrading.html

i can see my cron jobs by executing crontab -l -u otrs, but how do i manage to stop the cron service when being logged as root ?

Best regards,
Romain

[edit]

found the answer, i needed to add this line to the crontab file:
PATH = /sbin:$PATH

Code: Select all

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
PATH = /sbin:$PATH

# m h dom mon dow user  command
17 *    * * *   root    cd / && run-parts --report /etc/cron.hourly
25 6    * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )
47 6    * * 7   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly )
52 6    1 * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )
#

now i get:

Code: Select all

root@otrs:~# service cron stop
cron stop/waiting

Would be nice to add that to the install doc.

[Romaing]

up,

So later, i'm having troubles with the sql commands:

Just so you know, my 'upgrade plan' is: (feel free to bash me if you think some of the stuff i'm doing is stupid, just at least give some explanation )

Code: Select all

# Arrêt des services
service cron stop
service apache2 stop
service postfix stop

# Backup des fichiers de config
cp -R /opt/otrs /opt/otrs.backup

# Backup BDD SQL
mysqldump -h localhost -u otrs -p otrs | bzip2 -c > otrs.sql.bz2
#demande mot de passe SQL
mv otrs.sql.bz2 /opt/otrs.backup/

# Installation nouvelle version
tar -xzf otrs-3.1.3.tar.gz
cp -R otrs-3.1.3/* /opt/otrs

# Restauration des fichiers de configuration
cp /opt/otrs.backup/Kernel/Config.pm /opt/otrs/Kernel/Config.pm
cp /opt/otrs.backup/Kernel/Config/Files/ZZZAuto.pm /opt/otrs/Kernel/Config/Files/ZZZAuto.pm
cp /opt/otrs.backup/Kernel/Config/GenericAgent.pm /opt/otrs/Kernel/Config/GenericAgent.pm
cp /opt/otrs.backup/var/httpd/htdocs/skins/Customer/default/img/xxx.png /opt/otrs/var/httpd/htdocs/skins/Customer/default/img/xxx.png
cp /opt/otrs.backup/var/cron/* /opt/otrs/var/cron/

# Nécessaire ?
cd otrs
bin/otrs.SetPermissions.pl --otrs-user=otrs --otrs-group=otrs --web-user=www-data --web-group=www-data /opt/otrs

# Appliquer modifications à la BDD (pour mySQL) - partie 1
cd scripts
./DBUpdate-to-3.1.mysql.sql | mysql -u otrs -p otrs -f

# lancer le script de migration de BDD (EN TANT QU'UTILISATEUR 'otrs', et PAS root):
su - otrs
cd scripts
./DBUpdate-to-3.1.pl

exit

# Appliquer modifications à la BDD - partie 2
./DBUpdate-to-3.1-post.mysql.sql | mysql -u otrs -p otrs -f

# Dernière étape BDD SQL
cd ..
bin/otrs.RebuildConfig.pl
bin/otrs.DeleteCache.pl

# Redémarrage des services
service cron start
service postfix start
service apache2 start

So typing this:

Code: Select all

$ ./DBUpdate-to-3.1.pl

gives the following error log:

(btw, i don't know if it's normal, but the 2 commands right before [./DBUpdate-to-3.1.mysql.sql | mysql -u otrs -p otrs -f] and after [./DBUpdate-to-3.1-post.mysql.sql | mysql -u otrs -p otrs -f
] don't give any log, the password is asked, then several seconds just pass and that's it, the shell is ready for another command. Shouldn't they at least display a OK sign for a successfull job done ?)

If i finish the upgrade process, OTRS now boots fine on 3.1.3 (3.1.2 before), and it seems to run fine.

Migration started...

Step 1 of 24: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 5.
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 5.
done.

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

Step 3 of 24: Creating DynamicField tables (if necessary)... done.

DBD::mysql::db do failed: Unknown column 'freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'freekey1' in 'field list', SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 95

DBD::mysql::db do failed: Unknown column 'a_freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'a_freekey1' in 'field list', SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 95

Step 4 of 24: Create new dynamic fields for free fields (text, key, date)... Free fields were deleted, migration is already done!
done.

Step 5 of 24: Migrate ticket free fields to dynamic fields...
DBD::mysql::db do failed: Unknown column 'freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'freekey1' in 'field list', SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 102

DBD::mysql::db do failed: Unknown column 'a_freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'a_freekey1' in 'field list', SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 102

Free fields were deleted, migration is already done!
done.

Step 6 of 24: Migrate article free fields to dynamic fields...
DBD::mysql::db do failed: Unknown column 'freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'freekey1' in 'field list', SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 109

DBD::mysql::db do failed: Unknown column 'a_freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'a_freekey1' in 'field list', SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 109

Free fields were deleted, migration is already done!
done.

Step 7 of 24: Verify if ticket data was successfully migrated...
DBD::mysql::db do failed: Unknown column 'freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'freekey1' in 'field list', SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 117

DBD::mysql::db do failed: Unknown column 'a_freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'a_freekey1' in 'field list', SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 117

Free fields were deleted, migration is already done!
done.

DBD::mysql::db do failed: Unknown column 'freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'freekey1' in 'field list', SQL: 'SELECT freekey1, freetext1, freetime1 FROM ticket'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 417
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 125

DBD::mysql::db do failed: Unknown column 'a_freekey1' in 'field list' at /opt/otrs/Kernel/System/DB.pm line 478.
ERROR: OTRS-DBUpdate-to-3.1-10 Perl: 5.10.1 OS: linux Time: Tue Apr 24 09:25:44 2012

Message: Unknown column 'a_freekey1' in 'field list', SQL: 'SELECT a_freekey1, a_freetext1 FROM article'

Traceback (13376):
Module: main::_IsFreefieldsMigrationAlreadyDone (v1.85) Line: 422
Module: ./DBUpdate-to-3.1.pl (v1.85) Line: 125

Step 8 of 24: Verify if article data was successfully migrated... Free fields were deleted, migration is already done!
done.

Step 9 of 24: Migrate free fields configuration... done.

Step 10 of 24: Update history type from 'TicketFreeTextUpdate' to 'TicketDynamicFieldUpdate'... done.

Step 11 of 24: Migrate free fields window configuration... done.

Step 12 of 24: Migrate free fields stats configuration... done.

Step 13 of 24: Migrate free fields generic agent jobs configuration... done.

Step 14 of 24: Migrate free fields post master configuration... done.

Step 15 of 24: Migrate free fields standard responses configuration... done.

Step 16 of 24: Migrate free fields auto responses configuration... done.

Step 17 of 24: Migrate free fields salutations configuration... done.

Step 18 of 24: Migrate free fields signatures configuration... done.

Step 19 of 24: Migrate free fields search profiles configuration... done.

Step 20 of 24: Migrate free fields notifications configuration... done.

Step 21 of 24: Migrate free fields notification event configuration... done.

Step 22 of 24: Checking for duplicate entries on ticket_flag table...
done.

Step 23 of 24: Clean up the cache... done.

Step 24 of 24: Refresh configuration cache another time...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 5.
Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 5.
done.

Migration completed!
$

Oh also when i reboot the otrs server, the mysql error log seems fine, so do i have anything to worry about ?

Code: Select all

120424 14:51:51 [Note] Plugin 'FEDERATED' is disabled.
120424 14:51:51  InnoDB: Initializing buffer pool, size = 8.0M
120424 14:51:51  InnoDB: Completed initialization of buffer pool
120424 14:51:51  InnoDB: Started; log sequence number 0 44233
120424 14:51:51 [Note] Event Scheduler: Loaded 0 events
120424 14:51:51 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.61-0ubuntu0.10.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

[EDIT]

Oh, could it have something to do with this: http://www.mail-archive.com/otrs@otrs.org/msg36372.html ?

i opened the otrs.sql file from the mysql dump before the upgrade, and i see ENGINE=MyISAM everywhere