ERROR: value too long for type character varying(255)

[Simon Gao]

I am seeing following error:

{noformat}

2013-04-19 00:02:13 PDT:192.168.1.11(34122):jiradbuser@jiradb:[21524]: ERROR: value too long for type character varying(255)
2013-04-19 00:02:13 PDT:192.168.1.11(34122):jiradbuser@jiradb:[21524]: STATEMENT: UPDATE public."AO_78C957_NOTIFICATION_HIST" SET "DIRECTION" = $1,"SUBJECT" = $2,"DATE" = $3,"AUDIT" = $4,"TRANSPORT_TYPE" = $5 WHERE "ID" = $6
2013-04-19 07:21:24 PDT:192.168.1.11(50800):jiradbuser@jiradb:[1306]: ERROR: insert or update on table "AO_78C957_AUDITHINT" violates foreign key constraint "fk_ao_78c957_audithint_audit_event_id"
2013-04-19 07:21:24 PDT:192.168.1.11(50800):jiradbuser@jiradb:[1306]: DETAIL: Key (AUDIT_EVENT_ID)=(149173) is not present in table "AO_78C957_AUDITEVENTS".
2013-04-19 07:21:24 PDT:192.168.1.11(50800):jiradbuser@jiradb:[1306]: STATEMENT: UPDATE public."AO_78C957_AUDITHINT" SET "VALID_VALUES" = $1,"HINT_TEXT" = $2,"AUDIT_EVENT_ID" = $3,"EXCEPTION_STACK" = $4,"PROVIDED_VALUE" = $5,"CAUSED_REJECTION" = $6,"CREATED" = $7 WHERE "ID" = $8
2013-04-19 09:15:22 PDT:192.168.1.11(59810):jiradbuser@jiradb:[18938]: ERROR: value too long for type character varying(255)
2013-04-19 09:15:22 PDT:192.168.1.11(59810):jiradbuser@jiradb:[18938]: STATEMENT: UPDATE public."AO_78C957_NOTIFICATION_HIST" SET "DIRECTION" = $1,"SUBJECT" = $2,"DATE" = $3,"AUDIT" = $4,"TRANSPORT_TYPE" = $5 WHERE "ID" = $6
{noformat}

Please let me know what the fix is. We are running JIRA 5.2.9 with JEMH 1.3.17.

Thanks,

Simon

[Sorin Sbarnea (Citrix)]

Really that the bug https://jira.atlassian.com/browse/JRA-37377which was marked as resolved... wrongly. Add you comments to it and raise support tickets pointing to the same bug, that's the only way to get this fixed by Atlassian, both comments on the bug + support tickets.

[Simon Gao]

Thanks. I've changed the SUBJECT data type from character 255 to text.

[Simon Gao]

Should I also change data type to text for CREATED_BY and UPDATED_BY in "public.AO_78C957_AUDITHINT"?

[Andy Brook [Plugin People]]

No, I really doubt anyone will have a registered username that big.

[Simon Gao]

Here it is:

Table "public.AO_78C957_NOTIFICATION_HIST"
        Column        |            Type             |                                 Modifiers                                  | Storage  | Description 
----------------------+-----------------------------+----------------------------------------------------------------------------+----------+-------------
 ASSIGNEE             | character varying(255)      |                                                                            | extended | 
 AUDIT                | integer                     |                                                                            | plain    | 
 COMMENT              | bigint                      |                                                                            | plain    | 
 DATE                 | timestamp without time zone |                                                                            | plain    | 
 DIRECTION            | character varying(255)      |                                                                            | extended | 
 EVENT                | bigint                      |                                                                            | plain    | 
 EVENT_INITIATOR      | character varying(255)      |                                                                            | extended | 
 FROM                 | character varying(255)      |                                                                            | extended | 
 ID                   | integer                     | not null default nextval('"AO_78C957_NOTIFICATION_HIST_ID_seq"'::regclass) | plain    | 
 ISSUE_KEY            | character varying(255)      |                                                                            | extended | 
 JIRA_ADDRESSEE_USERS | text                        |                                                                            | extended | 
 NON_JIRA_ADDRESSEES  | text                        |                                                                            | extended | 
 PROJECT              | character varying(255)      |                                                                            | extended | 
 SUBJECT              | character varying(255)      |                                                                            | extended | 
 TRANSPORT_TYPE       | character varying(255)      |                                                                            | extended | 
Indexes:
    "AO_78C957_NOTIFICATION_HIST_pkey" PRIMARY KEY, btree ("ID")

[Andy Brook [Plugin People]]

Yes, the SUBJECT column should be expanded to TEXT type, that I think will be the cause of what you see?

[Simon Gao]

Here is what I have:

Table "public.AO_78C957_AUDITHINT"
      Column      |            Type             |                             Modifiers                              | Storage  | Description 
------------------+-----------------------------+--------------------------------------------------------------------+----------+-------------
 AUDIT_EVENT_ID   | integer                     |                                                                    | plain    | 
 CAUSED_REJECTION | boolean                     |                                                                    | plain    | 
 CREATED          | timestamp without time zone |                                                                    | plain    | 
 CREATED_BY       | character varying(255)      |                                                                    | extended | 
 EXCEPTION_STACK  | text                        |                                                                    | extended | 
 HINT_TEXT        | text                        |                                                                    | extended | 
 ID               | integer                     | not null default nextval('"AO_78C957_AUDITHINT_ID_seq"'::regclass) | plain    | 
 PROVIDED_VALUE   | text                        |                                                                    | extended | 
 UPDATED          | timestamp without time zone |                                                                    | plain    | 
 UPDATED_BY       | character varying(255)      |                                                                    | extended | 
 VALID_VALUES     | text                        |                                                                    | extended | 
Indexes:
    "AO_78C957_AUDITHINT_pkey" PRIMARY KEY, btree ("ID")
    "index_ao_78c957_aud1071085059" btree ("AUDIT_EVENT_ID")
Foreign-key constraints:
    "fk_ao_78c957_audithint_audit_event_id" FOREIGN KEY ("AUDIT_EVENT_ID") REFERENCES "AO_78C957_AUDITEVENTS"("ID")
Has OIDs: no

[Simon Gao]

CREATED_BY and UPDATED_BY are both character type of 255 length.


[Andy Brook [Plugin People]]

And for AO_78C957_.NOTIFICATION_HIST?

[Andy Brook [Plugin People]]

Hi Simon,

This is a known issue that afffects older releases. Lossless schema type updates are not directly supported by AO, the char[255] fields need changing to TEXT type, as applicable to your database. For resolution details, please see:

- https://thepluginpeople.atlassian.net/wiki/display/JEMH/Common+Problems#CommonProblems-Datatruncation:Datatoolongforcolumn'TO_ADDRESS'atrow1

[Simon Gao]

Hi Andy,

During upgrade to 5.2.9, I deleted all JEMH tables and re-create them. Why did I still see some field not set to text?

The link seems to be out of date:

{quote}

alter table "AO_78C957_CONFIG" alter column "TO_ADDRESS" type text;
alter table "AO_78C957_CONFIG" alter column "CC_ADDRESS" type text;
alter table "AO_78C957_CONFIG" alter column "EMAIL_SUBJECT" type text;
{quote}

I did not find above columes in AO_78C957_CONFIG table.  

The error I am seeing is AO_78C957_NOTIFICATION_HIST or AO_78C957_AUDITHINT. Can you tell which table and column 
they are?

Thanks,

Simon

[Andy Brook [Plugin People]]

Yep, an incorrect table was referred, now fixed. There are three columns to change as you listed, target table is AO_78C957_AUDITEVENTS.

[Simon Gao]

The three columns are already "text" type. So it should be some other column.

Simon

[Andy Brook [Plugin People]]

for some reason I can't seem to edit posts in this new version of answers, most annoying. Tables should be:

- AO_78C957_.NOTIFICATION_HIST:SUBJECT

- AO_78C957_.NOTIFICATION_HIST:NON_JIRA_ADDRESSEES

- AO_78C957_.NOTIFICATION_HIST:JIRA_ADDRESSEES

[Andy Brook [Plugin People]]

For reference, there should be no char(255) columns in AO_78C957_.AUDITHINT, they should all be text type. Please confirm this is the case? Also, the following columns should also be expanded to text, I've udpated the wiki;

AO_78C957_.NOTIFICATION_HIST:SUBJECT

AO_78C957_.NON_JIRA_ADDRESSEES

AO_78C957_.JIRA_ADDRESSEES

If this has been done I'd need some sql logging to see what was was being inserted to see which field needs to be updated; I have no open support cases regarding this so am pretty sure the above will resolve the problem you see.