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

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

6 answers

This widget could not be displayed.

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

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

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

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

Simon

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

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.

This widget could not be displayed.

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

CREATED_BY and UPDATED_BY are both character type of 255 length.

And for AO_78C957_.NOTIFICATION_HIST?

This widget could not be displayed.

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")

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

This widget could not be displayed.

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

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

This widget could not be displayed.

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

This widget could not be displayed.

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.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted 4 hours ago in Europe

Speaker Required!

Hey, We're currently organising an AUG event on Tuesday the 23rd of October. It's being hosted by Tata Consultancy Services near High Street Kensington station. We currently require one more spea...

43 views 0 0
View post

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you