[rt-users] Migrating from SQLite to MySQL

[Matthew Seaman]

David X. Glover wrote:
> On 26 Nov 2009, at 15:38, David X. Glover wrote:
> 
>> In the end, that's what we did.
> 
> One (hopefully minor) problem left over. When attempting to create a  
> new ticket in the migrated database, I'm getting the SQL error  
> "Duplicate entry '0' for key 1" when trying to create the new record  
> in the Tickets table.
> 
> Actual errors from the log follows. Any ideas?
> 
> Nov 27 10:25:03 servername RT: DBD::mysql::st execute failed:  
> Duplicate entry '0' for key 1 at /usr/share/perl5/DBIx/SearchBuilder/ 
> Handle.pm line 505. (/usr/share/perl5/DBIx/SearchBuilder/Handle.pm:505)
> 
> Nov 27 10:25:03 servername RT: RT::Handle=HASH(0x2486af0) couldn't  
> execute the query 'INSERT INTO Tickets (Subject, Status, Queue,  
> Creator, Owner, LastUpdatedBy, Started, Type, Starts, Resolved,  
> Created, Priority, Due, LastUpdated) VALUES  
> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' at /usr/share/perl5/DBIx/ 
> SearchBuilder/Handle.pm line 518  
> ^IDBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH 
> (0x2486af0)', 'INSERT INTO Tickets (Subject, Status, Queue, Creator,  
> Owner, ...', 'test', 'new', 3, 22, 22, 22, '1970-01-01 00:00:00', ...)  
> called at /usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 353  
> ^IDBIx::SearchBuilder::Handle::Insert('RT::Handle=HASH(0x2486af0)',  
> 'Tickets', 'Subject', 'test', 'Status', 'new', 'Queue', 3,  
> 'Creator', ...) called at /usr/share/perl5/DBIx/SearchBuilder/Handle/ 
> mysql.pm line 36 ^IDBIx::SearchBuilder::Handle::mysql::Insert 
> ('RT::Handle=HASH(0x2486af0)', 'Tickets', 'Subject', 'test', 'Status',  
> 'new', 'Queue', 3, 'Creator', ...) called at /usr/share/perl5/DBIx/ 
> SearchBuilder/Record.pm line 129
> 
> Nov 27 10:25:03 servername RT: Couldn't create a ticket: Internal  
> Error: Couldn't execute the query 'INSERT INTO Tickets (Subject,  
> Status, Queue, Creator, Owner, LastUpdatedBy, Started, Type, Starts,  
> Resolved, Created, Priority, Due, LastUpdated) VALUES  
> (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'Duplicate entry '0' for key  
> 1 (/usr/share/request-tracker3.6/lib/RT/Ticket_Overlay.pm:602)
> 

Is the autoincrement field for that table too low?  Compare the output
of the following SQL queries:

SHOW CREATE TABLE Tickets \G

SELECT id FROM Tickets ORDER BY id DESC LIMIT 1 ;

If the AUTOINCREMENT=nnn setting at the end of the output from the first
command is not at least one more than the result of the second command, then
do this:

ALTER TABLE Tickets AUTOINCREMENT=xxx

where xxx is some number larger than the largest id number currently in the
Tickets table.  This will be the next Ticket Id number created from RT.  As the id 
field is the only unique key in that table (it is in fact the primary key)
it must be somehow the cause of the error message you're seeing -- but it should
be set automatically by MySQL whenever a new row is inserted into the table.

	Cheers,

	Matthew

-- 
Dr Matthew Seaman                     The Bunker, Ash Radar Station
PGP: 0x60AE908C on servers            Marshborough Rd
Tel: +44 1304 814890                  Sandwich
Fax: +44 1304 814899                  Kent, CT13 0PL, UK

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 259 bytes
Desc: OpenPGP digital signature
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091127/f8974e1b/attachment.sig>