Setting initial ticket number (auto_increment)


#1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Checking the archives of this list, I see advice on setting the initial
ticket number for a new RT database. This is for mysql 4.1.13. The advice
is to use the following command:

ALTER TABLES Tickets auto_increment=nnnn

to cause ticket numbers to begin with nnnn + 1.

However, the mysql manual says, “The effect of this option is canceled by
a server restart”. I verified that this is the case by restarting my
mysql server after setting auto_increment for the Tickets table to 60000.
The value did get set to 60000, as shown by a SHOW TABLE STATUS (BTW, is
there a better way to display just the value of auto_increment?).
However, after restarting the mysql server, the value of auto_increment
went back to 1.

So, what do I do if I want to retain the value of 60000 even if my server
happens to restart (say because of a reboot)? Once I start creating
tickets in my new RT database (starting at 60001, I hope), I wouldn’t want
new ticket numbers suddenly to be assigned lower values just because the
server happened to restart at some point in the future.

Could someone clarify all this?

Thanks.

Mike

Mike Friedman System and Network Security
mikef@ack.Berkeley.EDU 2484 Shattuck Avenue
1-510-642-1410 University of California at Berkeley
http://ack.Berkeley.EDU/~mikef http://security.berkeley.edu

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQA/AwUBQymdO60bf1iNr4mCEQIAHgCggse1KcIIHotS8CbBlkXzZerLXWcAoKbj
KeOkFTSidYkIKg8Kn+6FvneQ
=83Jv
-----END PGP SIGNATURE-----


#2

AFAIK, AUTO INCREMENT is useful only in MyISAM tables not InnoDB. My system
automatically updates the AUTO INCREMENT setting for the next ticket number,
anyway (currently at 9609). If you initially set it for 60000, create a
ticket, check to make sure AUTO INCREMENT has advanced to the next number,
then restart your server, what happens?

Thanks
Tim Mahoney
Win Svr 2003 / RT 3.0.12 / Apache 1.33 / Perl 5.8.6 / MySQL4.1.18-----Original Message-----
From: Mike Friedman [mailto:mikef@ack.Berkeley.EDU]
Sent: Thursday, September 15, 2005 11:12 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Setting initial ticket number (auto_increment)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Checking the archives of this list, I see advice on setting the initial
ticket number for a new RT database. This is for mysql 4.1.13. The advice
is to use the following command:

ALTER TABLES Tickets auto_increment=nnnn

to cause ticket numbers to begin with nnnn + 1.

However, the mysql manual says, “The effect of this option is canceled by a
server restart”. I verified that this is the case by restarting my mysql
server after setting auto_increment for the Tickets table to 60000.
The value did get set to 60000, as shown by a SHOW TABLE STATUS (BTW, is
there a better way to display just the value of auto_increment?).
However, after restarting the mysql server, the value of auto_increment went
back to 1.

So, what do I do if I want to retain the value of 60000 even if my server
happens to restart (say because of a reboot)? Once I start creating tickets
in my new RT database (starting at 60001, I hope), I wouldn’t want new
ticket numbers suddenly to be assigned lower values just because the server
happened to restart at some point in the future.

Could someone clarify all this?

Thanks.

Mike

Mike Friedman System and Network Security
mikef@ack.Berkeley.EDU 2484 Shattuck Avenue
1-510-642-1410 University of California at Berkeley
http://ack.Berkeley.EDU/~mikef http://security.berkeley.edu

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQA/AwUBQymdO60bf1iNr4mCEQIAHgCggse1KcIIHotS8CbBlkXzZerLXWcAoKbj
KeOkFTSidYkIKg8Kn+6FvneQ
=83Jv
-----END PGP SIGNATURE-----
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com


#3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1On Thu, 15 Sep 2005 at 11:46 (-0500), Mahoney, Tim wrote:

AFAIK, AUTO INCREMENT is useful only in MyISAM tables not InnoDB. My
system automatically updates the AUTO INCREMENT setting for the next
ticket number, anyway (currently at 9609). If you initially set it for
60000, create a ticket, check to make sure AUTO INCREMENT has advanced
to the next number, then restart your server, what happens?

Tim,

Your point is well-taken and had occurred to me as well, but I was
reluctant to experiment.

So, I did as you suggest and, sure enough, the first ticket had ID 60000
and, even after restarting the mysql server, subsequent tickets had IDs of
60001, 60002, etc. (I also restarted Apache and the incrementing
continued properly from there).

Anyway, I guess it’s working as I want, so the initial advice on how to
set auto_increment was correct. I don’t quite know what you meant by
’AUTO INCREMENT is useful only in MyISAM tables not InnoDB’. It sure
proved useful to me for my purpose.

Thanks.

Mike

Mike Friedman System and Network Security
mikef@ack.Berkeley.EDU 2484 Shattuck Avenue
1-510-642-1410 University of California at Berkeley
http://ack.Berkeley.EDU/~mikef http://security.berkeley.edu

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQA/AwUBQym5Vq0bf1iNr4mCEQKQ4QCggxhleUNWM+4aYl0zjF2IHAY3M3QAn3w+
4eTnht6Luf1V0ltbKgGS93J6
=+Dfe
-----END PGP SIGNATURE-----


#4

Anyway, I guess it’s working as I want, so the initial advice on how to
set auto_increment was correct. I don’t quite know what you meant by
’AUTO INCREMENT is useful only in MyISAM tables not InnoDB’. It sure
proved useful to me for my purpose.

It should work with either, but it isn’t actually remembered until
you create a record. You could have manually inserted an item
in that table and then deleted it for the same effect.

Les Mikesell
les@futuresource.com


#5

Can anyone help me accomplish this in a Postgres database?

Hi, i’m trying to change the starting ticket number to a higher number.
I have a RT 4.4.3 brand new installation on a Centos 7 using the Centos installation guide from the wiki.

I’m issuing these commands:

sudo -u postgres psql - to enter the posgres shell and enter the password to authenticate

then I issue

postgres=# SELECT setval(‘tickets_id_seq’, 20800);

and get this error

ERROR: relation “tickets_id_seq” does not exist
LINE 1: SELECT setval(‘tickets_id_seq’, 20800);
^
postgres=#

Would you guys be able to help interpret this?


#6

alter sequence tickets_id_seq restart <NUM> should work, with <NUM> being the new starting ticket number