SQL Exception When Adding Comments

[Arda Koç]

Hi everyone,

I'm trying to add threaded comments when pressing the reply button. I want to do that with Active Objects. When I press the button, it normally needs to write to my entity table named AO_C21EEC_THREADED_COMMENTS automatically created in the database. But I got this error:

[common.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.3.24
- minor version:3
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.0 JDBC4 (build 801)

org.postgresql.util.PSQLException: ERROR: relation "public.AO_C83514_THREADED_COMMENTS_ID_seq" does not exist
Position: 16

My addComment function is below there:

@POST
@AnonymousAllowed
@Produces({MediaType.APPLICATION_JSON})
@Path("/addcomment")
public Response addComment(final CommentModel comment)
{

  final Comment commentObj = commentManager.getCommentById(comment.getParentCommentId());

  if(comment == null || comment.getIssueId() == null || comment.getParentCommentId() == null || comment.getCommentBody() == null) {
    return Response.notModified("Required parameters are missing.").build();
  }
  if(commentObj == null) {
    return Response.notModified("Wrong comment id.").build();
  }

  final ApplicationUser loggedInUser = ComponentAccessor.getJiraAuthenticationContext().getUser();

  final MutableIssue issueObject = issueManager.getIssueObject(comment.getIssueId());

  if(!permissionManager.hasPermission(ProjectPermissions.ADD_COMMENTS, issueObject, loggedInUser)) {
  return Response.status(Response.Status.FORBIDDEN).entity("No Permission").build();
  }
  
  final Comment newComment = commentManager.create(issueObject, loggedInUser, StringEscapeUtils.unescapeHtml4(comment.getCommentBody().replaceAll("\\n","\n")), true);

  final ThreadedComments commentInfo = ao.create(ThreadedComments.class);

  commentInfo.setAuthor(loggedInUser.toString());
  commentInfo.setCommentBody(comment.getCommentBody());
  commentInfo.setCommentId(newComment.getId());
  commentInfo.setParentCommentId(comment.getParentCommentId());
  commentInfo.setIssueId(comment.getIssueId());
  commentInfo.save();

  comment.setCommentId(newComment.getId());

  return Response.ok(comment).build();
}

I've printed the logs up to the line starts with 'final Comment newComment...' and it gives the error after this line, I guess...

I don't know if there is any other information needed but I would be glad if you can help.

[Radek Dostál]

Hi,

I've printed the logs up to the line starts with 'final Comment newComment...' and it gives the error after this line, I guess...

Well that would be good to verify :)

I would say that the most helpful in pinning this down would be to run a debug mode on your test instance -- see more here: https://developer.atlassian.com/server/framework/atlassian-sdk/creating-a-remote-debug-target/

When then you start the instance via atlas-debug and attach to that process, you can go line by line to find the exact line number causing that exception. Then you will know precisely what to look for. If this is not reproducible on the debug sdk instance, then we can assume it's due to differences between H2 and Postgres databases.

Typically these Postgres errors are that the table names in the sql queries are not wrapped in double quotes, could you check in your AO manager class how you are querying the database, are all of plugin-related tables wrapped in double quotes? e.g. 

ao.find(ThreadedComments.class, "\"MY_COLUMN\" = ?", key);

I don't know if this is correct -- but just noticed that both of the tables mentioned have different name-space in them:

AO_C21EEC_THREADED_COMMENTS
AO_C83514_THREADED_COMMENTS_ID_seq

Is this right? I would have thought they should have the same name space in them but I'm not sure how the ID_seq works in AO.

Anyhow just a few guesses, but most of all I would say running a debug session will tell you/us the exact problematic line and then it'll be easy to fix it.

Thanks,

Radek

[Arda Koç]

I don't know if this is correct -- but just noticed that both of the tables mentioned have different name-space in them:

AO_C21EEC_THREADED_COMMENTS
AO_C83514_THREADED_COMMENTS_ID_seq

Is this right? I would have thought they should have the same name space in them but I'm not sure how the ID_seq works in AO.

Yes, that's why it gives the error. It automatically creates the first table with Active Objects, but when I want to add comment to that table it wants to add datas to the second table.

I try to debug but I use Eclipse and can't do that. But I print the logs as I say. Today, I was able to print this line too:

final Comment newComment = commentManager.create(issueObject, loggedInUser, StringEscapeUtils.unescapeHtml4(comment.getCommentBody().replaceAll("\\n","\n")), true);

and it gave that output:

newComment = com.atlassian.jira.issue.comments.CommentImpl@17b4c8f4 

If there is no problem, I guess the problem is in the creation phase of Active Objects, the next line:

final ThreadedComments commentInfo = ao.create(ThreadedComments.class);

 Because it keeps giving the same error and I can't print the logs of that lines:

commentInfo.setAuthor(loggedInUser.toString());
commentInfo.setCommentBody(comment.getCommentBody());
commentInfo.setCommentId(newComment.getId());
commentInfo.setParentCommentId(comment.getParentCommentId());
commentInfo.setIssueId(comment.getIssueId());

Also, i tried to change the name of automatically created table to the name of the new table. It gives another exception:

Uncaught exception thrown by REST service: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.3.24
- minor version:3
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.0 JDBC4 (build 801)

org.postgresql.util.PSQLException: ERROR: "AO_C83514_THREADED_COMMENTS_ID_seq" is not a sequence

Do you know what is sequence?

Thank you for your answer,
Arda

[Italo Qualisoni [e-Core]]

Take a look in this document to understand the bold part of your Active Objects:

AO_C21EEC_THREADED_COMMENTS
AO_C83514_THREADED_COMMENTS_ID_seq

https://developer.atlassian.com/server/framework/atlassian-sdk/table-names/

https://developer.atlassian.com/server/framework/atlassian-sdk/active-objects-plugin-module/

Sequence is used to auto increment values in database. So if you say that your ID column is required and auto incremented, you can then insert new rows without providing the ID

Also you should use Developer Community for this question, there are more chances of someone that have had this issue reply you with better instructions.

https://community.developer.atlassian.com/ 

[Arda Koç]

I reviewed the document and specified the namespace of ao. It creates another table (AO_E6091F_THREADED_COMMENTS). But the error was same. It wants to create a new table every time.

Also, I create the same topic on Developer Community, thank you for your suggestion and answer.