It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Simon Gao Apr 19, 2013

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

0 votes
Andy Brook (DEV) Apr 19, 2013

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 Apr 19, 2013

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 (DEV) Apr 19, 2013

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 Apr 19, 2013

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

Simon

Andy Brook (DEV) Apr 19, 2013

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 (DEV) Apr 19, 2013

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.

0 votes
Simon Gao Apr 21, 2013

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 Apr 21, 2013

CREATED_BY and UPDATED_BY are both character type of 255 length.

Andy Brook (DEV) Apr 21, 2013

And for AO_78C957_.NOTIFICATION_HIST?

0 votes
Simon Gao Apr 21, 2013

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 (DEV) Apr 21, 2013

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

0 votes
Simon Gao Apr 21, 2013

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

Andy Brook (DEV) Apr 21, 2013

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

0 votes
Simon Gao Apr 21, 2013

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

0 votes

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in Off-topic

Disney Live Action Reboots: Are you for or against?

Happy Friday Everyone! Today marks the international release of Disney's live action version of the animated classic Aladdin. I know that this movie was met with some controversy of over cast...

137 views 19 8
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you