Modify

Opened 7 years ago

Closed 8 months ago

Last modified 8 months ago

#7975 closed defect (fixed)

Subscription.add and Subscription._update_priority incorrectly use CURRENT_TIMESTAMP on BIGINT column

Reported by: anonymous Owned by: Steffen Hoffmann
Priority: normal Component: AnnouncerPlugin
Severity: major Keywords: database API
Cc: Trac Release: 0.12

Description

The subscription table columns time and changetime are BIGINT as per most of the rest of Trac, which stores time data as microseconds since Unix epoch. However, AnnouncerPlugin uses CURRENT_TIMESTAMP when inserting the data, which returns an ISO-8601 string, and this is not appropriate for the given column type. With MySQL backend, MySQL silently mangles the data to “2010”; with PostgreSQL backend, an error is raised (cannot cast type timestamp with time zone to bigint). This means the plugin does not work at all on Postgres, and works incorrectly on MySQL.

Attachments (0)

Change History (10)

comment:1 Changed 7 years ago by Robert Corsaro

A tested patch would be great. I don't have a mysql trac instance to test on. I can test on postgres and sqlite though.

comment:2 Changed 5 years ago by Ryan J Ollos

#8065 closed as a duplicate, and it has a patch which we may want to review.

comment:3 in reply to:  1 Changed 5 years ago by Steffen Hoffmann

Keywords: database API added
Severity: normalmajor

Replying to doki_pen:

A tested patch would be great. I don't have a mysql trac instance to test on. I can test on postgres and sqlite though.

Me too, but since the patch in #8065 looks promising, I feel more confident for resolution than Robert did before.

comment:4 Changed 5 years ago by Steffen Hoffmann

Hint: We actually have to deal with the fix for #7823 here.

comment:5 Changed 5 years ago by Steffen Hoffmann

(In [12302]) TracAnnouncer: Part 7 of 7 - Finally: Go from present to future, refs #5774, #7975, #8065, #9742 and #10384.

Now we've bridged the gap and provide an upgrade path for each historic schema revision of this plugin, while data migration is incomplete yet, especially regarding subscription attributes stored in session_attribute (before v3). Due to component name changes the conversion will be rather complicated, and therefore corresponding research and development is postponed to a future date and will largely depend on explicite demand as well.

comment:6 Changed 5 years ago by Steffen Hoffmann

(In [12303]) TracAnnouncer: Convert type to match db table definitions, refs #7975, #8065 and #10384.

These changes are based on work by olistudent, Stephen Anderson and rea. I made sure, that we respect PEP8 as well, at least as far as Trac core does.

Thanks to all of you for testing, reports and suggestions towards a portable fix, and - ultimately - patience to get it finally resolved.

comment:7 Changed 5 years ago by Robert Corsaro

Owner: changed from Robert Corsaro to Steffen Hoffmann

comment:8 Changed 9 months ago by Ryan J Ollos

Owner: Steffen Hoffmann deleted

comment:9 Changed 8 months ago by Ryan J Ollos

Resolution: fixed
Status: newclosed

comment:10 Changed 8 months ago by Ryan J Ollos

Owner: set to Steffen Hoffmann

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Steffen Hoffmann.
The resolution will be deleted.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.