Uploading files to otrs / creating attachements not possible [Solved]

[tehjanosch]

Hey folks,

some of our servers crashed in July because we had only a MySQL Backup from OTRS we installed a fresh OTRS (I think it was OTRS 4 Patch Level 9) and managed to get OTRS run, currently we are running OTRS 4 Patch Level 10. The previous version was OTRS 3, but I can't remember the patch-level anymore. After some time I discovered that I can't add attachements to our mails anymore.

The errors I get are the following:

Oct 23 15:18:14 otrs OTRS-otrs.Scheduler-63[7874]: [Error][main::_Start][Line:236]: Scheduler Daemon tried to start but found an already running service!
Oct 23 15:18:48 otrs OTRS-CGI-63[7876]: [Error][Kernel::System::Web::UploadCache::DB::FormIDGetAllFilesMeta][Line:235]: Unknown column 'content_size' in 'field list', SQL: '#012 SELECT filename, content_type, content_size, content_id, disposition#012 FROM web_upload_cache#012 WHERE form_id = ?#012 ORDER BY create_time_unix'
Oct 23 15:18:53 otrs OTRS-CGI-63[7876]: [Error][Kernel::System::Web::UploadCache::DB::FormIDAddFile][Line:108]: Unknown column 'content_size' in 'field list', SQL: '#012 INSERT INTO web_upload_cache (form_id, filename, content_type, content_size, content,#012 create_time_unix, content_id, disposition)#012 VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
Oct 23 15:18:53 otrs OTRS-CGI-63[7876]: [Error][Kernel::System::Web::UploadCache::DB::FormIDGetAllFilesMeta][Line:235]: Unknown column 'content_size' in 'field list', SQL: '#012 SELECT filename, content_type, content_size, content_id, disposition#012 FROM web_upload_cache#012 WHERE form_id = ?#012 ORDER BY create_time_unix'


I have already tried to re-run the SQL converting script. That showed the following errors to me:

ERROR 1060 (42S21) at line 9: Duplicate column name 'disposition'
ERROR 1060 (42S21) at line 13: Duplicate column name 'disposition'
INFO: Foreign key constraint FK_ticket_valid_id_id does not exist, skipping.
INFO: Foreign key constraint FK_ticket_valid_id_id does not exist, skipping.
ERROR 1091 (42000) at line 23: Can't DROP 'valid_id'; check that column/key exists
INFO: Foreign key constraint FK_ticket_history_valid_id_id does not exist, skipping.
INFO: Foreign key constraint FK_ticket_history_valid_id_id does not exist, skipping.
ERROR 1091 (42000) at line 33: Can't DROP 'valid_id'; check that column/key exists
ERROR 1050 (42S01) at line 38: Table 'personal_services' already exists
ERROR 1050 (42S01) at line 51: Table 'system_maintenance' already exists
ERROR 1005 (HY000) at line 66: Can't create table 'otrs.#sql-3cd_200a' (errno: 121)
ERROR 1005 (HY000) at line 67: Can't create table 'otrs.#sql-3cd_200a' (errno: 121)
ERROR 1005 (HY000) at line 68: Can't create table 'otrs.#sql-3cd_200a' (errno: 121)
ERROR 1005 (HY000) at line 69: Can't create table 'otrs.#sql-3cd_200a' (errno: 121)
ERROR 1005 (HY000) at line 70: Can't create table 'otrs.#sql-3cd_200a' (errno: 121)


The Migration script printed out the following:

Migration started...

Step 1 of 15: Refresh configuration cache...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.

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

Step 3 of 15: Migrate FontAwesome icons... done.

Step 4 of 15: Migrate ProcessManagement EntityIDs...
Process EntityIDs Migration Reference
PROCESS:
P1 Process-8226c530ef566846d275f1496a1a6f4c

ACTIVITY:
A2 Activity-bb45de321fde2a0f4c6244ee623ed888

ACTIVITY DIALOG:
AD1 ActivityDialog-71465166f0f21147480f94bdc062d951

TRANSITION:

TRANSITION ACTION:



There are process parts that are not yet deployed, automatic deployment is not possible, please review all processes and deploy then manually
done.

Step 5 of 15: Migrate ProcessManagement Dynamic Field Types... done.

Step 6 of 15: Migrate ACLs stored in the DB... done.

Step 7 of 15: Set service notifications... done.

Step 8 of 15: Migrate Settings... done.

Step 9 of 15: Uninstall Merged Feature Add-Ons... done.

Step 10 of 15: Migrate SysConfig settings from DT to Template::Toolkit... done.

Step 11 of 15: Migrate Dynamic Field links from DT to Template::Toolkit... done.

Step 12 of 15: Migrate Generic Agent Process Management Dynamic Fields ... done.

Step 13 of 15: Migrate Notification (Event) Process Management Dynamic Fields ... done.

Step 14 of 15: Clean up the cache... done.

Step 15 of 15: Refresh configuration cache another time...
If you see warnings about 'Subroutine Load redefined', that's fine, no need to worry!
done.

Migration completed!



I understand that there are issues - but as I'm not that experienced with Databases I don't know how to fix it. Do you guys have any hint for me?

[tehjanosch]

fledl was able to help me on IRC.

What I did to fix our upload was the following:

In Core::Web -> WebUploadCacheModule I switched from "DB" to "FS". File Upload now works like a charm.

[crythias]

Unknown column 'content_size' in 'field list', SQL: '#012 SELECT filename, content_type, content_size, content_id, disposition#012 FROM web_upload_cache#012 WHERE form_id = ?#012 ORDER BY create_time_unix'

Docs:
http://otrs.github.io/doc/manual/admin/ ... ading.html

Basically, there's a schema problem. changing to FS isn't the only fix, though.
https://github.com/OTRS/otrs/blob/rel-4 ... te-to-4.pl