Hi Michiel,
Many thanks for you in-depth reply! It certainly answered my question : )
The reason for the long 'Symptom' is that we are using OTRS FAQ as a
knowledgebase, into which I am copying and pasting a lot of text.
I am going to try edit the FAQ layout so you can't see the Symptom,
Problem and Solution fields as it doesn't really suit the format we would
like.
Your suggestion of changing the field type to mediumtext worked perfectly
thank you very much for that!
Kind Regards,
Emily Flynn
Michiel Beijen
FAQ::Item::Field1. The fields are limited at 65535 characters, to be precise.
mysql> select length(f_field1) from faq_item;
+------------------+
| length(f_field1) |
+------------------+
| 31 |
| 107 |
| 168 |
| 65535 |
| 65535 |
+------------------+
5 rows in set (0.00 sec)
Then I found this post about the storage capacities of 'text' type fields
in mysql: http://simonwillison.net/2002/Aug/1/mysqlTextLimits/
That made me wonder if this is present in more places in OTRS:
mysql> select table_name, column_name from information_schema.columns
where data_type = 'text';
+-------------------+---------------+
| table_name | column_name |
+-------------------+---------------+
| article | a_from |
| article | a_reply_to |
| article | a_to |
| article | a_cc |
| article | a_subject |
| article | a_message_id |
| article_search | a_from |
| article_search | a_to |
| article_search | a_cc |
| article_search | a_subject |
| article_search | a_message_id |
| auto_response | text0 |
| auto_response | text1 |
| auto_response | text2 |
| faq_item | f_keywords |
| faq_item | f_field1 |
| faq_item | f_field2 |
| faq_item | f_field3 |
| faq_item | f_field4 |
| faq_item | f_field5 |
| faq_item | f_field6 |
| notifications | text |
| salutation | text |
| sessions | session_value |
| signature | text |
| standard_response | text |
+-------------------+---------------+
26 rows in set (0.12 sec)
But as you can see only in places where it makes sense (at least to me)
the 'text' type is used... except in the faq_item table. Personally, I
find symptoms of > 65535 bytes a bit long but I think that should be
possible. Also I think it the Problem and Solution fields should be able
to handle real long amounts of text and AT LEAST the system should
complain instead of silently truncating your texts.
As a quick hack, you could change the field type in the database to
'mediumtext' type which can keep a maximum of 2^32 characters which is
about 4.294.967.296.
mysql> alter table `faq_item` change `f_field1` `f_field1` MEDIUMTEXT;
Query OK, 5 rows affected (0.14 sec)
Records: 5 Duplicates: 0 Warnings: 0
You'd probably want to do the same to the fields f_field2 ("Problem") and
f_field3 ("Solution").
The field lenghts are defined in the OPML file which is here:
ftp://ftp.otrs.org/pub/otrs/packages/FAQ-1.5.4.opm - there you see the
fields are defined as storing 20000 characters, MySQL then creates them
with the 'text' datatype.
I would like to ask you if you can file this bug in Bugzilla (
http://bugs.otrs.org) so it may be resolved in the next version of the FAQ
module.
Kind regards,
--
Michiel Beijen
Software Consultant
+31 6 - 457 42 418
Bee Free IT + http://beefreeit.nl
On Wed, May 27, 2009 at 17:57, Emily Flynn