SetStartingId

RT uses autogenerated sequences for ids of its records in database. All databases do similar things with autogenerated columns, they store current value of the id. When RT inserts data into table database server generates new value(current + 1) and use it as id of the new record. Some times people want start counting ticket ids from 100001, so next ~900000 tickets would have ids with the same length. Most databases support altering of the current value. Let's see how to alter id in different database servers:

MySQL

• You couldn't set id to the value lesser than current max id in the table
• You can set next id greater than current max id with next command

mysql> ALTER TABLE auto_inc_test AUTO_INCREMENT=1000000;

Look at next example that demonstrates how it works:

mysql> CREATE TABLE auto_inc_test( id INTEGER AUTO_INCREMENT PRIMARY KEY ) TYPE=InnoDB;
 mysql> INSERT INTO auto_inc_test VALUES( 3 );
 mysql> SELECT * FROM auto_inc_test;
 +----+
 | id |
 +----+
 |  3 |
 +----+
 mysql> ALTER TABLE auto_inc_test AUTO_INCREMENT=1; # value lesser than max id
 mysql> INSERT INTO auto_inc_test VALUES( NULL );   # doesn't work as expected
 mysql> SELECT * FROM auto_inc_test;
 +----+
 | id |
 +----+
 |  3 |
 |  4 |
 +----+
 mysql> ALTER TABLE auto_inc_test AUTO_INCREMENT=10;
 mysql> INSERT INTO auto_inc_test VALUES( NULL );
 mysql> SELECT * FROM auto_inc_test;
 +----+
 | id |
 +----+
 |  3 |
 |  4 |
 | 10 |
 +----+
 
 

NOTE: after altering you must insert at least one row into the table before server restart, MySQL loose altered value on restarts.

The above has been seen to not work, for reasons unknown to this AnonymousGnome - the alter table runs but has no effect. In this case, you should be able to INSERT INTO auto_inc_test VALUES(10) instead, and the auto_increment picks up from there.

NOTE: If the INSERT INTO commands give "ERROR 1136 (21S01): Column count doesn't match value count at row 1" try using "INSERT INTO Tickets(id) VALUES(10);"

Also you should delete the newly created row with "DELETE FROM Tickets WHERE id = 10;"

For example, to set the next ticket id to 123456, you'd run the following statement:

ALTER TABLE Tickets AUTO_INCREMENT=123456;

To test that the next id is properly set, you insert a dummy record which you promptly delete via,

INSERT INTO Tickets(Disabled) VALUES(1);

Now retrieve the last issued ticket id,

SELECT LAST_INSERT_VALUE()

This should display 123456

Now lets delete the dummy row

DELETE FROM Tickets WHERE id = '123456'

Note

After inserting a dummy row the auto_increment is automatically increased by one (1), so be sure to reset via the alter table command if you absolutely must start at the desired ticket id.

Recap

Man that was confusing at first. Not sure about all that stuff up top, but the bottom section worked well for me on a fresh RT4 install on MySQL. In a nutshell:

ALTER TABLE Tickets AUTO_INCREMENT=5000;
INSERT INTO Tickets(Disabled) VALUES(1);
DELETE FROM Tickets WHERE id = '5000'

Create new ticket to test. Drink beer to confirm your greatness.

-S

PostgreSQL

Postgres has a special object called a sequence that RT uses to generate a new, unique id (primary key) for the rows of each of its tables. The name of the sequence for each table is '<table name>_id_seq' and it acts on the '<table name>.<table name>_id' field). You can change current value of the sequence (and, therefore, the ticket number of the next ticket created) by issuing the following command from the psql command prompt:

rtdb# SELECT setval('tickets_id_seq', 1000000);

NOTE that you'll get contraint violation if set value lesser then max id in the table.

Oracle

You can do this manually by dropping/recreating the sequence

DROP SEQUENCE foo_seq;
CREATE SEQUENCE foo_seq START WITH 1000000 INCREMENT BY 1;

However, if you're trying to do this for all the tables in RT, you may find the contributed fix-sequences.pl scrip to be more useful. http://download.bestpractical.com/pub/rt/contrib/3.0/fix-sequences.pl