InnoDB Ticket IDs incrementing by 10?

Hello,

Since upgrading to RT 3.6.3, I’ve noticed that all of our tickets are
incrementing their ticket numbers/IDs by n+10 -

From show table status:

| Tickets | InnoDB |10 | Compact | 14823 | 248 | 3686400 | 0 | 1146880
| 0 | 24632 | 2007-04-30 09:35:08 | NULL | NULL | latin1_swedish_ci | NULL
|

And the table itself (sorry for the mangling…):

mysql> describe Tickets;
| Field | Type | Null | Key | Default |
Extra |
| id | int(11) | NO | PRI | NULL |
auto_increment |
| EffectiveId | int(11) | NO | MUL | 0
| |
| Queue | int(11) | NO | MUL | 0
| |
| Type | varchar(16) | YES | | NULL
| |
| IssueStatement | int(11) | NO | | 0
| |
| Resolution | int(11) | NO | | 0
| |
| Owner | int(11) | NO | MUL | 0
| |
| Subject | varchar(200) | YES | | [no subject]
| |
| InitialPriority | int(11) | NO | | 0
| |
| FinalPriority | int(11) | NO | | 0
| |
| Priority | int(11) | NO | | 0
| |
| TimeEstimated | int(11) | NO | | 0
| |
| TimeWorked | int(11) | NO | | 0
| |
| Status | varchar(10) | YES | | NULL
| |
| TimeLeft | int(11) | NO | | 0
| |
| Told | datetime | YES | | NULL
| |
| Starts | datetime | YES | | NULL
| |
| Started | datetime | YES | | NULL
| |
| Due | datetime | YES | | NULL
| |
| Resolved | datetime | YES | | NULL
| |
| LastUpdatedBy | int(11) | NO | | 0
| |
| LastUpdated | datetime | YES | | NULL
| |
| Creator | int(11) | NO | | 0
| |
| Created | datetime | YES | | NULL
| |
| Disabled | smallint(6) | NO | | 0
| |

Have I missed something simple? The version of RT I upgraded from also used
InnoDB tables, so it doesn’t seem to me to be an issue with auto_increment
strangeness with InnoDB tables (which I’ve read a little about). Any clues?

Thanks!
Mike

One way or another, everyone stops bleeding.

I hate to reply to my own question, but does anyone else see this issue
with new tickets being generated with IDs of n+10 rather than n+1? It has
to be something simple/stupid that I’m missing, but I’ve been looking at it
for days and am clearly overlooking something at this point…I offer a 6
pack of your choice of beers in exchange for a quick fix :slight_smile:

cheers,
mike> On 4/30/07, mike mike@bseder.org wrote:

Hello,

Since upgrading to RT 3.6.3, I’ve noticed that all of our tickets are
incrementing their ticket numbers/IDs by n+10 -

From show table status:

| Tickets | InnoDB |10 | Compact | 14823 | 248 | 3686400 | 0 |
1146880 | 0 | 24632 | 2007-04-30 09:35:08 | NULL | NULL | latin1_swedish_ci
| NULL |

And the table itself (sorry for the mangling…):

mysql> describe Tickets;

±----------------±-------------±-----±----±-------------±---------------+
| Field | Type | Null | Key | Default |
Extra |
±----------------±-------------±-----±----±-------------±---------------+

| id | int(11) | NO | PRI | NULL |
auto_increment |
| EffectiveId | int(11) | NO | MUL | 0
| |
| Queue | int(11) | NO | MUL | 0
| |
| Type | varchar(16) | YES | | NULL
| |
| IssueStatement | int(11) | NO | | 0
| |
| Resolution | int(11) | NO | | 0
| |
| Owner | int(11) | NO | MUL | 0
| |
| Subject | varchar(200) | YES | | [no subject]
| |
| InitialPriority | int(11) | NO | | 0
| |
| FinalPriority | int(11) | NO | | 0
| |
| Priority | int(11) | NO | | 0
| |
| TimeEstimated | int(11) | NO | | 0
| |
| TimeWorked | int(11) | NO | | 0
| |
| Status | varchar(10) | YES | | NULL
| |
| TimeLeft | int(11) | NO | | 0
| |
| Told | datetime | YES | | NULL
| |
| Starts | datetime | YES | | NULL
| |
| Started | datetime | YES | | NULL
| |
| Due | datetime | YES | | NULL
| |
| Resolved | datetime | YES | | NULL
| |
| LastUpdatedBy | int(11) | NO | | 0
| |
| LastUpdated | datetime | YES | | NULL
| |
| Creator | int(11) | NO | | 0
| |
| Created | datetime | YES | | NULL
| |
| Disabled | smallint(6) | NO | | 0
| |

±----------------±-------------±-----±----±-------------±---------------+

Have I missed something simple? The version of RT I upgraded from also
used InnoDB tables, so it doesn’t seem to me to be an issue with
auto_increment strangeness with InnoDB tables (which I’ve read a little
about). Any clues?

Thanks!
Mike


One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

mike wrote:

I hate to reply to my own question, but does anyone else see this issue
with new tickets being generated with IDs of n+10 rather than n+1? It
has to be something simple/stupid that I’m missing, but I’ve been
looking at it for days and am clearly overlooking something at this
point…I offer a 6 pack of your choice of beers in exchange for a quick
fix :slight_smile:
Don’t know if the folling qualifies but I have had the same issue but
then I’m using Oracle. With Oracle it is simple to remedy, namelijk
adjust the caching of the sequence numbers from 20, in my case and the
default of Oracle sequences, to nocache. This has a performance impact
but not noticeable in our environment.
May this can also be set for MySQL on table level or server level.

Joop

mike wrote:

Hello,

Since upgrading to RT 3.6.3, I’ve noticed that all of our tickets are
incrementing their ticket numbers/IDs by n+10 -

Are you using multiple-master replication?

http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html

yes indeed - the master is now set to

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
| Variable_name | Value |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |

and the slave is

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
| Variable_name | Value |
| auto_increment_increment | 1 |
| auto_increment_offset | 2 |
2 rows in set (0.00 sec)

prior to making the change suggested, the master was configured as:

Current database: rt3

| Variable_name | Value |
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
2 rows in set (0.00 sec)

do I need to reload mysql, or is this a hot-fix? needless to say, i am not
a DBA, let alone terribly cluey with MySQL (but learning more every day) ;).

Thank you very much for the assistance, it’s very much appreciated and I owe
Mark Roedel a six-pack for the initial assist :)On 5/1/07, Graham Dunn gdunn01@harris.com wrote:

mike wrote:

Hello,

Since upgrading to RT 3.6.3, I’ve noticed that all of our tickets are
incrementing their ticket numbers/IDs by n+10 -

Are you using multiple-master replication?

http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

hmm…does this require a restart of mysql? It now looks correct, but is
still incrementing n+10?

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
| Variable_name | Value |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |

I made this change as as the root/dba account, so I’m confused…On 5/1/07, mike mike@bseder.org wrote:

yes indeed - the master is now set to

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

and the slave is

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 2 |
±-------------------------±------+
2 rows in set (0.00 sec)

prior to making the change suggested, the master was configured as:

Current database: rt3

±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
±-------------------------±------+
2 rows in set (0.00 sec)

do I need to reload mysql, or is this a hot-fix? needless to say, i am
not a DBA, let alone terribly cluey with MySQL (but learning more every
day) ;).

Thank you very much for the assistance, it’s very much appreciated and I
owe Mark Roedel a six-pack for the initial assist :slight_smile:

On 5/1/07, Graham Dunn gdunn01@harris.com wrote:

mike wrote:

Hello,

Since upgrading to RT 3.6.3, I’ve noticed that all of our tickets are
incrementing their ticket numbers/IDs by n+10 -

Are you using multiple-master replication?

http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

hate to be repetitive, but i also don’t like bouncing production databases
(our mysql handles a lot more than our RT system)…do I need to
reload/restart mysql for the auto_increment_increment change to take hold?
our tickets are still incrementing n+10 rather than n+1, and the slave’s
config is set for auto_increment_offset=2 rather than 1 like the master is.

thanks!On 5/1/07, mike mike@bseder.org wrote:

hmm…does this require a restart of mysql? It now looks correct, but
is still incrementing n+10?

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

I made this change as as the root/dba account, so I’m confused…

On 5/1/07, mike mike@bseder.org wrote:

yes indeed - the master is now set to

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
±-------------------------±------+

and the slave is

mysql> SHOW VARIABLES LIKE ‘auto_inc%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 1 |
| auto_increment_offset | 2 |
±-------------------------±------+
2 rows in set (0.00 sec)

prior to making the change suggested, the master was configured as:

Current database: rt3

±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
±-------------------------±------+
2 rows in set (0.00 sec)

do I need to reload mysql, or is this a hot-fix? needless to say, i am
not a DBA, let alone terribly cluey with MySQL (but learning more every
day) ;).

Thank you very much for the assistance, it’s very much appreciated and I
owe Mark Roedel a six-pack for the initial assist :slight_smile:

On 5/1/07, Graham Dunn gdunn01@harris.com wrote:

mike wrote:

Hello,

Since upgrading to RT 3.6.3, I’ve noticed that all of our tickets
are
incrementing their ticket numbers/IDs by n+10 -

Are you using multiple-master replication?

http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.

I don’t see it here
http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html so
I suspect you may need a restart. Double-check that though.

mike wrote:

hate to be repetitive, but i also don’t like bouncing production
databases (our mysql handles a lot more than our RT system)…do I
need to reload/restart mysql for the auto_increment_increment change
to take hold? our tickets are still incrementing n+10 rather than
n+1, and the slave’s config is set for auto_increment_offset=2 rather
than 1 like the master is.

thanks!

hmm....does this require a restart of mysql?  It now *looks*
correct, but is still incrementing n+10?
 
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
 
I made this change as as the root/dba account, so I'm confused...
    yes indeed - the master is now set to
     
    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 1  |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+
     
    and the slave is
     
    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 1     |
    | auto_increment_offset    | 2     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
     
    prior to making the change suggested, the master was
    configured as:
     

    Current database: rt3

    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)

    do I need to reload mysql, or is this a hot-fix?  needless to
    say, i am not a DBA, let alone terribly cluey with MySQL (but
    learning more every day) ;).

    Thank you very much for the assistance, it's very much
    appreciated and I owe Mark Roedel a six-pack for the initial
    assist :) 
        mike wrote:
        > Hello,
        >
        > Since upgrading to RT 3.6.3, I've noticed that all of our
        tickets are
        > incrementing their ticket numbers/IDs by n+10 -
        >
        Are you using multiple-master replication?

        http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html
        <http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html>

        _______________________________________________
        http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

        Community help: http://wiki.bestpractical.com
        <http://wiki.bestpractical.com/>
        Commercial support: sales@bestpractical.com
        <mailto:sales@bestpractical.com>


        Discover RT's hidden secrets with RT Essentials from
        O'Reilly Media.
        Buy a copy at http://rtbook.bestpractical.com
        <http://rtbook.bestpractical.com/>




    -- 

    --
    One way or another, everyone stops bleeding.


    -- 
    --
    One way or another, everyone stops bleeding.




-- 
--
One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

mike wrote:

hate to be repetitive, but i also don’t like bouncing production
databases (our mysql handles a lot more than our RT system)…do I need
to reload/restart mysql for the auto_increment_increment change to take
hold? our tickets are still incrementing n+10 rather than n+1, and the
slave’s config is set for auto_increment_offset=2 rather than 1 like the
master is.

The values will revert to their defaults if you’ve restarted mysql. Do
you have those values set in the my.cnf?

"If one of these variables is set globally, its effects persist until
the global value is changed or overridden by setting them locally, or
until mysqld is restarted.

If set locally, the new value affects AUTO_INCREMENT columns for all
tables into which new rows are inserted by the current user for the
duration of the session, unless the values are changed during that session."

Are you setting them to different values on different hosts on purpose?

If so, it sounds like you’re seeing the “set locally” behaviour.

yep, found and changed those values on both master and slave dbs in
my.cnfafter reading more through the docs to make sure i don’t
miss/screw up
anything doing my DB restarts. i have a reload of both mysql
daemons scheduled for my next maintenance window, which should put this to
bed once and for all. there are no good reasons for any of the other tables
to be auto_incrementing n+10, so this should sort it out. i really need to
brush up on my mysql skills…i appreciate the clues, cheers guys and gals.

mikeOn 5/2/07, Graham Dunn gdunn01@harris.com wrote:

mike wrote:

hate to be repetitive, but i also don’t like bouncing production
databases (our mysql handles a lot more than our RT system)…do I need
to reload/restart mysql for the auto_increment_increment change to take
hold? our tickets are still incrementing n+10 rather than n+1, and the
slave’s config is set for auto_increment_offset=2 rather than 1 like the
master is.

The values will revert to their defaults if you’ve restarted mysql. Do
you have those values set in the my.cnf?

"If one of these variables is set globally, its effects persist until
the global value is changed or overridden by setting them locally, or
until mysqld is restarted.

If set locally, the new value affects AUTO_INCREMENT columns for all
tables into which new rows are inserted by the current user for the
duration of the session, unless the values are changed during that
session."

Are you setting them to different values on different hosts on purpose?

If so, it sounds like you’re seeing the “set locally” behaviour.

thanks!

On 5/1/07, mike <mike@bseder.org mailto:mike@bseder.org> wrote:

hmm....does this require a restart of mysql?  It now *looks*
correct, but is still incrementing n+10?

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

I made this change as as the root/dba account, so I'm confused...



On 5/1/07, *mike* <mike@bseder.org <mailto:mike@bseder.org>> wrote:

    yes indeed - the master is now set to

    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 1  |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+

    and the slave is

    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 1     |
    | auto_increment_offset    | 2     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)

    prior to making the change suggested, the master was configured

as:

    Current database: rt3

    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)

    do I need to reload mysql, or is this a hot-fix?  needless to
    say, i am not a DBA, let alone terribly cluey with MySQL (but
    learning more every day) ;).

    Thank you very much for the assistance, it's very much
    appreciated and I owe Mark Roedel a six-pack for the initial
    assist :)




    On 5/1/07, *Graham Dunn* <gdunn01@harris.com <mailto:gdunn01@harris.com>> wrote:

        mike wrote:
        > Hello,
        >
        > Since upgrading to RT 3.6.3, I've noticed that all of our
        tickets are
        > incrementing their ticket numbers/IDs by n+10 -
        >
        Are you using multiple-master replication?

http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html

        <

http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html>

        _______________________________________________

The rt-users Archives

        Community help: http://wiki.bestpractical.com
        <http://wiki.bestpractical.com/>
        Commercial support: sales@bestpractical.com
        <mailto:sales@bestpractical.com>


        Discover RT's hidden secrets with RT Essentials from
        O'Reilly Media.
        Buy a copy at http://rtbook.bestpractical.com
        <http://rtbook.bestpractical.com/>




    --

    --
    One way or another, everyone stops bleeding.


    --
    --
    One way or another, everyone stops bleeding.




--
--
One way or another, everyone stops bleeding.

One way or another, everyone stops bleeding.



The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

One way or another, everyone stops bleeding.