Reply
Emerging Member
Posts: 50
Registered: ‎02-14-2014
Kudos: 18
Solutions: 2
Accepted Solution

Server error when accessing certain clients

When we go into certain clients on our system, we get the attached page up and there is no other way that we can see of accessing that client. It looks like this has been the case for a while as if we restore a previous backup it also happens. Is there a way we can repair the database to resolve this issue?

UCRM Error.jpg


Accepted Solutions
Ubiquiti Employee
Posts: 1,259
Registered: ‎03-21-2016
Kudos: 198
Solutions: 145

Re: Server error when accessing certain clients

Hello @RobWiFiScotland, so the problem is you have an invoice in your database with due date in year 275809, which the database can handle, but UCRM app cannot (the maximum year is 9999). When the invoice is loaded, the conversion crashes and you get the error.

 

We'll look into how this could be possible, but you seem to be the only one with this problem. Did you by any chance edit something in the database manually?

 

The fix for you is fairly simple, you can use the following command to detect the problematic invoice(s):

sudo docker exec -t ucrm_web_app_1 bash -c 'export PGPASSWORD="${POSTGRES_PASSWORD}" && psql -d "${POSTGRES_DB}" -h "${POSTGRES_HOST}" -p "${POSTGRES_PORT}" -U "${POSTGRES_USER}" -t -c "select invoice_id, invoice_number from invoice where due_date > '"'"'9999-12-31 00:00:00'"'"';"'

This is simply to let you know which invoices will be updated, you will see the invoice ID and invoice number in the output.

 

Then, you can run the following command to set the year of the problematic due date to 2018:

sudo docker exec -t ucrm_web_app_1 bash -c 'export PGPASSWORD="${POSTGRES_PASSWORD}" && psql -d "${POSTGRES_DB}" -h "${POSTGRES_HOST}" -p "${POSTGRES_PORT}" -U "${POSTGRES_USER}" -t -c "update invoice set due_date = due_date + (2018 - EXTRACT(YEAR FROM due_date) || '"'"' years'"'"')::interval where due_date > '"'"'9999-12-31 00:00:00'"'"';"'

View solution in original post


All Replies
Ubiquiti Employee
Posts: 1,259
Registered: ‎03-21-2016
Kudos: 198
Solutions: 145

Re: Server error when accessing certain clients

Hello @RobWiFiScotland, please post what version of UCRM you're running and also please post the error message as plain text, we cannot handle it if it's an image.

After that we can let you know how to fix it if it's possible on your own or we'll include fix in next UCRM release.

Emerging Member
Posts: 50
Registered: ‎02-14-2014
Kudos: 18
Solutions: 2

Re: Server error when accessing certain clients

Cheers, we're currently on 2.15.0 Beta 2, but it looks like it was happening when we were on 2.14 as well.

 

Plain text as follows:

 

ZGVmNTAyMDBmZDk3ODEwMDM5M2YxNzk3ZTdlZGVkMmQyYmNhMWQzNGQwYmQ0ZDE5Y2VkNjg5NTgxMzRiMWFlZGFlYzgyZWU1NzJlMGY1OWE1MzMwZmFhMzk1MjhiNDM4MzkxZWFlN2JlZmQ3YWRjNzMxMDhjNjg5N2YwN2JkM2NjYzBiYzMzMTk0Y2VjYWMwM2VmMDc5OTA0OTM1NTRlZjE4NTQwNjE5YTE5ZmJhYzRiZWU3NDBhYmU5MGM5ZDM4YjQwODBmMDMyZmMzNDQ4Y2VkZDRhYWYwZjMzYmIzMDFmMWZmMzk3MWFmNzdjNTA3OGQ4YWEzMWFlYWQyZDQ3OTM5OTRlYjdlZThjNDNmZWVhMmMxNzhiZThjMTAyYTJlMjkyMjgwNzEzZjZjYWRkODU0NWJiODI0ZmZmZmY5MGM3ZTRlYzRlYjUxZGNhN2FmMjZmYWZkODEwOTM3MjhkNjVlMTc5ODJkNTg4MmIyYTRhNTZkZjc2ZTA1MWZhMGMxMTY0MDNkYTgwMDVjMWE1MTRjMzZhZDBjYzU0MmIzNWEyNTMxZDY4ZTkzYzQzYWM1YTA2ZmI4MGYxZjEyMjQyY2FlMDlhYzFjZTc0NzRkNTNjYTZhZTQ3N2E2OWU3NDgwMDk0ZTg5NmJiZjJkNzJlMDJhMjRiZjBhYzM2M2QxODRhMmViNmY5ZTZlY2JiOWI5MDMyM2RiMDUyMmQ5MGQwNTlkZWMzMWEwNTE3NjI2NDI0NzU2OWIwMTNiYjQ0NzYxZDY4MTQwMTFjMmQzN2I0OWRlN2YzM2MyYmM4Y2RiYzQxMTUyYTBiOTY0MTdmNTUzNmYwYTZiMGMwZGRjM2YxMDY1ZWE0ZGJjMmE5ZGMwZDE3MTc4ODc0NDBmNjkyZTQ4NjAwZjJjNGE5NzI1MzY2M2MzZTdlMjBhZDFmNGE2Y2ZkMzUyYzAwZDkxOWQyMmU1NjQwM2I4ODIwZjNiODcwZTcwYjJlYTJlMjNiMjI4NjE3N2Q3NmUwMjhiZjdiNjc2NmYzMTNkOTFmNGI3YTYzNjdjM2VhMTg3NTMxOTY0ZjBhMmEyZWY2YTNiZjEyZDg4ZjM4ZWU4ZGQ4ZWNjMzRmMmIwZjI0OTJjODU3YWE3MjUzZDZlMzVlN2FjNWMwMDZkMzIwYmU0YmY0YzQwNTYwZGI5OGU0OWY0YjA1ZTQ3NTNiMDk2NmUwMTU1NGQ=

Ubiquiti Employee
Posts: 1,259
Registered: ‎03-21-2016
Kudos: 198
Solutions: 145

Re: Server error when accessing certain clients

Hello @RobWiFiScotland, so the problem is you have an invoice in your database with due date in year 275809, which the database can handle, but UCRM app cannot (the maximum year is 9999). When the invoice is loaded, the conversion crashes and you get the error.

 

We'll look into how this could be possible, but you seem to be the only one with this problem. Did you by any chance edit something in the database manually?

 

The fix for you is fairly simple, you can use the following command to detect the problematic invoice(s):

sudo docker exec -t ucrm_web_app_1 bash -c 'export PGPASSWORD="${POSTGRES_PASSWORD}" && psql -d "${POSTGRES_DB}" -h "${POSTGRES_HOST}" -p "${POSTGRES_PORT}" -U "${POSTGRES_USER}" -t -c "select invoice_id, invoice_number from invoice where due_date > '"'"'9999-12-31 00:00:00'"'"';"'

This is simply to let you know which invoices will be updated, you will see the invoice ID and invoice number in the output.

 

Then, you can run the following command to set the year of the problematic due date to 2018:

sudo docker exec -t ucrm_web_app_1 bash -c 'export PGPASSWORD="${POSTGRES_PASSWORD}" && psql -d "${POSTGRES_DB}" -h "${POSTGRES_HOST}" -p "${POSTGRES_PORT}" -U "${POSTGRES_USER}" -t -c "update invoice set due_date = due_date + (2018 - EXTRACT(YEAR FROM due_date) || '"'"' years'"'"')::interval where due_date > '"'"'9999-12-31 00:00:00'"'"';"'
Emerging Member
Posts: 50
Registered: ‎02-14-2014
Kudos: 18
Solutions: 2

Re: Server error when accessing certain clients

Yes, the year 275809 does seem a bit far out for a due date. Thanks for pinning it down, and having spoken to my collegue we know why it has occurred. For certain customers on Direct Debit collection he set the invoice maturity date to be absurdly high so that the customer wouldn't get an overdue email if we forgot to mark the invoice as paid manually. It appears that his definition of absurdly high is a LOT higher than most peoples!

 

I'll run those commands and hopefully it will all be back to normal.

Highlighted
Emerging Member
Posts: 50
Registered: ‎02-14-2014
Kudos: 18
Solutions: 2

Re: Server error when accessing certain clients

@UBNT-Ondra Thanks very much for the quick response and fix. Those commands worked perfectly.

Reply