Facing a bit of a crisis... last night the NTP daemon failed on our live OTRS server, and the OS reverted back to the system clock which is, rather unhelpfully, one hour ahead of my timezone.
This has resulted in many tickets being created with wrong "create times" in the future, which has had a knock on impact on escalation times, ticket age, etc.
Is there a way to fix this, beyond manually going into the database and changing these times?
Fix incorrect ticket dates after NTP problems?
Moderator: crythias
Fix incorrect ticket dates after NTP problems?
OTRS 3.2.11 on Centos 6.4 with MySQL 5.0. Agents and internal customers authenticate via Active Directory.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Fix incorrect ticket dates after NTP problems?
since the time stamps are attached to the ticket table, you'd have to adjust them in the table, and I doubt that Generic Agent would allow you to do that.
DISCLAIMER: BACKUP FIRST. Don't blindly copy/paste stuff and mess up your database. Some changes are unrecoverable. Try to SELECT before UPDATE to make sure this will do what you want.
The following code may be useful, though I have not tried it; adjust time stamps appropriately, and back up.
http://ldev.mysql.com/doc/refman/5.1/en ... mestampadd
If escalations have been set in ticket, you'll have to adjust them as well. Don't forget ticket_history, where you might have to do the same thing.
DISCLAIMER: BACKUP FIRST. Don't blindly copy/paste stuff and mess up your database. Some changes are unrecoverable. Try to SELECT before UPDATE to make sure this will do what you want.
The following code may be useful, though I have not tried it; adjust time stamps appropriately, and back up.
http://ldev.mysql.com/doc/refman/5.1/en ... mestampadd
Code: Select all
UPDATE ticket SET create_time=TIMESTAMPADD(HOUR,-1,create_time), change_time=NOW() WHERE create_time >= '2011-02-01 12:00:00'
AND create_time < '2011-02-02 9:00:00' + INTERVAL 1 DAY;
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Re: Fix incorrect ticket dates after NTP problems?
I was crossing my fingers and hoping OTRS might have already provided a safe script to fix my problem... all the while suspecting that the answer would be something along the lines of what you've printed above.
Unfortunately, changing stuff in the database on a production server, with only 2 days to run in my contract, would be reckless in the extreme so, thanks, but I don't think I'll be attempting it on this occasion!
Unfortunately, changing stuff in the database on a production server, with only 2 days to run in my contract, would be reckless in the extreme so, thanks, but I don't think I'll be attempting it on this occasion!
OTRS 3.2.11 on Centos 6.4 with MySQL 5.0. Agents and internal customers authenticate via Active Directory.