Changes for bigint tickets

RT Developers
1

Hi –

A local requirement for migration to RT is to use our existing ticket
format, which is essentially YYYYMMDDsss where sss is a serial. It
will be easy to update the sequence/auto_increment in the database to
ensure that each day’s tickets are numbered correctly, but a number of
that magnitude is a long, not an int.

I’ve attached my new schema. When I go to create a new ticket now, I
get an empty error – through the web UI, it’s an “RT error” table
with no text in, and rt-mailgate returns

Create failed: / /

I also noticed that EffectiveId in the Tickets table remains NULL
rather than matching the id of the ticket as it does for tickets
created prior to the change.

I’m pretty much stumped as to where to go from here, and would
appreciate suggestions on figuring out exactly where things are going
pear-shaped.

Thanks,

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------

schema.mysql.bigint-tickets (8.16 KB)

2

I’ve attached my new schema. When I go to create a new ticket now, I
get an empty error – through the web UI, it’s an “RT error” table
with no text in, and rt-mailgate returns

Create failed: / /

I also noticed that EffectiveId in the Tickets table remains NULL
rather than matching the id of the ticket as it does for tickets
created prior to the change.

An additional observation: Deleting the malformed tickets and setting
the sequence to start back at an int-sized number got everything
working fine, but pushing the sequence back up beyond an int breaks.
The sequence appears to keep working, though, because Ticket.id is set
correctly.

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------

3

An additional observation: Deleting the malformed tickets and setting
the sequence to start back at an int-sized number got everything
working fine, but pushing the sequence back up beyond an int breaks.
The sequence appears to keep working, though, because Ticket.id is set
correctly.

Still more detail – It succeeds at 4294967296 (UINT_MAX + 1), but
fails at 4294967297 (UINT_MAX + 2). But when it fails at UINT_MAX + 2,
it doesn’t fail like I described before – it gives me a real error
message, namely,

   Ticket 1 created in queue 'support'. That user is already that
   sort of watcher for this ticket

This is important. :slight_smile: UINT_MAX works. UINT_MAX + 1 doesn’t appear to
fail, but it’ll be dealing with “Ticket 0” in that wraparound.
UINT_MAX + 2 fails wrapped around to 1.

So there’s some sort of overflow of UINT_MAX here. But we know that
MySQL handles it fine, because it sets Tickets.id correctly. Off
to more debugging…

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------

4

So there’s some sort of overflow of UINT_MAX here. But we know that
MySQL handles it fine, because it sets Tickets.id correctly. Off
to more debugging…

“It’s worse than that, Jim.”

The overflow appears to be happening somewhere inside
DBIx::SearchBuilder::Handle. DBIx::Searchbuilder::Record calls

  $self->_Handle->Insert($self->Table, %attribs);

and that expression evaluates to the rolled-over ticket number. I’m
confused at that point, though –

$self->_Handle is an instance of DBIx::SearchBuilder::Handle, and
DBIx::SearchBuilder::Handle::Insert returns a statement handle, not a
value:

  my $sth =  $self->SimpleQuery($QueryString, @bind);
  return($sth);

And $sth is a DBD::st instance right before it’s returned. Where
does the magic happen to make

  $self->_Handle->Insert($self->Table, %attribs);

return a scalar?

I’m not sure what I’m missing there, so I’m going to break for a bit.

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------

5

An additional observation: Deleting the malformed tickets and setting
the sequence to start back at an int-sized number got everything

Personally I’d be declaring your bigints to be unsigned as well, but thats
merely to allow lots of room in the number sequence, rather than half of
them possibly negative and not needed. This has little, if any, bearing
on your problem though.

working fine, but pushing the sequence back up beyond an int breaks.
The sequence appears to keep working, though, because Ticket.id is set
correctly.

Still more detail – It succeeds at 4294967296 (UINT_MAX + 1), but
fails at 4294967297 (UINT_MAX + 2). But when it fails at UINT_MAX + 2,

This is beginning to sound like a perl problem, specifically how perl
groks numbers. A peruse of the perlnumber manpage puts the limititations
on ‘a format supported by the C compiler which was used to build perl’.
You will probably find the above number declared in stdint.h in your
system (on a handy linux 2.2.x box, its UINT32_MAX )

Perhaps a better solution would be to not mess with the representation of
the ticket ids in the SQL database (ie, keep as integer). As I see it,
you merely have a cosmetic problem, with what your customers/staff
recognise as a ticket ‘id’. Assume that you also store a column (tied to
the Ticket->id) known as ‘PublicId’:

rt-mailgate to recognise [$rtname #PublicId] in your subject line.

RT::Ticket to have a function to retrieve the PublicId.  Load()
also needs to be able to retrieve from this column.

RT::Action::SendEmail to use Ticket->PublicId instead of
Ticket->id

Various Templates which do the same.

WebUI to display and load PublicId instead of id.

The easiest place to put the ‘PublicId’ is the ‘Tickets’ table, as an
extra column. A graceful place to put it (and requires no changes to an
RT SQL installation) is in the ‘Links’ table. Jesse will probably hit me
for that as I suggested putting Scrip dependencies in there as well :wink:

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B                      Operations
6

Still more detail – It succeeds at 4294967296 (UINT_MAX + 1), but
fails at 4294967297 (UINT_MAX + 2). But when it fails at UINT_MAX + 2,

This is beginning to sound like a perl problem, specifically how perl
groks numbers.

$ perl -e ‘$u = 4294967294; foreach (1…5) { print $u++, ", "}; print “\n”’
4294967294, 4294967295, 4294967296, 4294967297, 4294967298,

I’m not sure. It’s a Perl problem inasmuch as the program that’s
exhibiting buggy behavior is written in Perl, but I suspect (but don’t
yet know) that I’ll find the culprit in DBD::mysql, which is XS.

A peruse of the perlnumber manpage puts the limititations
on ‘a format supported by the C compiler which was used to build perl’.
You will probably find the above number declared in stdint.h in your
system (on a handy linux 2.2.x box, its UINT32_MAX )

Well, as I said above, it’s UINT_MAX. It’s obvious that it’s wrapping
around, but it’s nonobvious why it’s wrapping around. I haven’t had
a chance to dig in much deeper yet, but I suspect Perl is using an IV
where it should be using an NV. Of course, that should never
happen. :slight_smile:

Perhaps a better solution would be to not mess with the representation of
the ticket ids in the SQL database (ie, keep as integer).

I could also declare by fiat that ticket numbers have a new format. :slight_smile:

But if I do that, there’s still a bug in either DBIx::SearchBuilder,
DBD:mysql, or Perl. Since I’ve nearly isolated the bug, it’d be silly
not to try and fix it. Dirty hacks are meant to work around something
because it’s too hard to fix.

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------

7

“RL” == Rich Lafferty rich+rt@lafferty.ca writes:

RL> $ perl -e ‘$u = 4294967294; foreach (1…5) { print $u++, ", "}; print “\n”’
RL> 4294967294, 4294967295, 4294967296, 4294967297, 4294967298,

Sure, you’re using floating point numbers.

RL> I’m not sure. It’s a Perl problem inasmuch as the program that’s
RL> exhibiting buggy behavior is written in Perl, but I suspect (but don’t
RL> yet know) that I’ll find the culprit in DBD::mysql, which is XS.

In XS, you’ve got C integers, depending on how the XS module was
written. I’d focus there.

8

I’m not sure. It’s a Perl problem inasmuch as the program that’s
exhibiting buggy behavior is written in Perl, but I suspect (but don’t
yet know) that I’ll find the culprit in DBD::mysql, which is XS.

And I did. :slight_smile: The MySQL API function mysql_insert_id() returns a
my_ulonglong; the DBD::mysql I was using implictly casts that into a
ulong. The ironic part is that at sometime in the past, the author
changed from a uint to a ulong after a bug report. In an ideal world,
that bug would only have to be fixed once. :slight_smile:

Luckily, there’s a new branch of DBD::mysql – the one in
Msql-Mysql-modules is apparently deprecated (although still suggested
on www.mysql.com, sigh). The new one has routines to explicitly
convert MySQL’s data types to SVs, and it Works Fine[tm].

-Rich

Rich Lafferty --------------±----------------------------------------------
Ottawa, Ontario, Canada | Save the Pacific Northwest Tree Octopus!
http://www.lafferty.ca/ | Save The Pacific Northwest Tree Octopus
rich@lafferty.ca -----------±----------------------------------------------