CCX calendars not working. Error 500: could not execute query

[floatingpurr]

Welcome to Episode 2: Validation strikes back : )

Hi there, I've just found another problem related to calendar management via API (see here episode 1). I was creating shifts for a FLEXIBLEHOURS calendar (with id=1) exploiting API (HTTP/PUT), when suddenly all got stuck.

Symptoms:

• In the web interface, I cannot see any calendar anymore. I just get a generic error message 'Error while handling the request'
• All HTTP GET calls to .../adminapi/calendar return 500 Internal Server Error (org.hibernate.exception.GenericJDBCException: could not execute query)
• I cannot get nor interact with the calendar I was modifying. Indeed, HTTP GET .../adminapi/calendar/1 returns the same error of above
• I can still get and interact with other calendars  (e.g, HTTP GET .../adminapi/calendar/2 works)
• If I try to get the row related to the shift I was creating, I get an error:

  admin:run uccx sql db_cra select * from dayshiftmap WHERE recordid = 796
  Internal CLI Error: java.sql.SQLException: Encoding or code set not supported.

My explanation:

It looks like I entered a shift name that for some reasons (e.g., encoding, illegal chars, etc...) broke all the queries related with it (i.e., the calendar list, the calendar such a shift belongs to). Again, the problem here is that the CCX is supposed to perform a data validation before writing to the DBMS (the default Informix, in my case). But it didn't happen, breaking the most of the calendar management.

Quick fix:

If you are experiencing this problem (I hope not!), you can try to fix the broken record up. For example, you can write an update query (stick with this thread for updates about it)

Long Term Fix (hopefully by Cisco!):

Cisco CCX, please, do not allow the write of nasty data, if you really don't like it! Not only from Web interface, but also from API calls (see here episode 1 for similar stuff).

[floatingpurr]

UPDATE: It seems like the quick fix isn't feasible. Not via the CLI, at least. Cisco DOC says: 

run uccx sql database_name sql_query
Executes an SQL "select" statement from the CLI. Read-only operations are permitted. Insert, Update, Delete and any DML statements are disallowed.

I look forward to receiving some hints!

[david.macias]

Any chance you've opened a TAC case on this, including your first episode? It really does seem that server side validation is not happening or broken.

david

[floatingpurr]

I haven't yet opened a TAC about that so far but I'll do it as soon as possibile. This is definitely the way to request a permanent patch and avoid similar problems in the future.

In the meanwhile, I need to fix my specific problem (i.e., the nasty db entry). Theoretically speaking, I just need a way to run an update query against db_cra.dayshiftmap. Any idea about how to do that? Reinstalling the CCX from scratch is not an option.

[dekwan · Accepted solution]

Hi,

I reached out to the CCX team about your issue and stated that they do indeed do data validation, but it seems like the specific sequence of characters that you used must have been missed. I will open a defect for it. They recommended that you open a TAC case to fix the broken record on your system. If the TAC engineer gives you any pushback, please have them contact me (Denise Kwan).

Thanx,

Denise

[dekwan · Accepted solution]

Hi,

Just FYI, I opened CSCvx24333 for the team to improve the server-side data validation. Since I just opened the bug, it will not be visible to the public yet and it will take some time to go through the process and system.

Thanx,

Denise

[floatingpurr]

Hi Denise,

   thank you again for your kind help. Regarding the server-side validation of calendar data, please, have also a look at Episode 1 here tl;dr: Fixedhours calendar can be created with non-fixed hours, and more... The point here is that server-side validation follows different rules respect to the Web interface. Maybe some pieces of data validation are client-side only.

Cheers!

- A.

[dekwan]

Hi @floatingpurr, 

The CCX team is currently working on fixing the bug. In your post, you stated:

It looks like I entered a shift name that for some reasons (e.g., encoding, illegal chars, etc...) broke all the queries related with it (i.e., the calendar list, the calendar such a shift belongs to). Again, the problem here is that the CCX is supposed to perform a data validation before writing to the DBMS (the default Informix, in my case). But it didn't happen, breaking the most of the calendar management.

They were not able to reproduce this issue, so would you be able to share the shift name that you used to cause this issue? They have already worked on adding validation so that fixed calendars cannot accept non fixed hours.

Thanx,

Denise

[floatingpurr]

Hi @dekwan,

     I'm glad to hear that the CCX Team is on it.

Regarding your question, I do not remember the exact string that broke the CCX db (via API). However, this is what we found out in the db w/ the TAC Engineer upon examining the table dayshiftmap as root:

This means that probably the string was "Turno lungo del mercoled?". Maybe the problem was the question mark (just guessing).

Let me know if you need more context.

Bye!

- A.

[dekwan]

Hi,

Thank you. I will pass this information on to them.

Thanx,

Denise