Re: [rt-users] Migrating from SQLite to MySQL
On 27 Nov 2009, at 10:32, David X. Glover wrote:
> 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.
Apologies for solving my own problem again, but the issue lay with the
conversion script which I posted yesterday. We've solved it now, so
here, for anyone interested, is the fixed version:
#!/usr/bin/env python
import re
import fileinput
import sys
def this_line_is_useless(line):
useless_es = [
'BEGIN TRANSACTION',
'COMMIT',
'sqlite_sequence',
'CREATE UNIQUE INDEX',
]
for useless in useless_es:
if re.search(useless, line):
return True
def has_primary_key(line):
return bool(re.search(r'PRIMARY KEY', line))
searching_for_end = False
for line in fileinput.input():
if this_line_is_useless(line): continue
# this line was necessary because ''); was getting
# converted (inappropriately) to \');
if re.match(r".*, ''\);", line):
line = re.sub(r"''\);", r'``);', line)
if re.match(r'^CREATE TABLE.*', line):
searching_for_end = True
m = re.search('CREATE TABLE ([a-zA-Z_]+) ', line)
if m:
(name,) = m.groups()
sys.stderr.write('creating table %s\n'%name)
line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT
EXISTS %(name)s(\n"
line = line % dict(name=name)
else:
m = re.search('INSERT INTO "([a-zA-Z_]*)"(.*)', line)
if m:
line = 'INSERT INTO %s%s\n' % m.groups()
line = line.replace('"', r'\"')
line = line.replace('"', "'")
line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
line = line.replace('THIS_IS_FALSE', '0')
# Add auto_increment if it's not there since sqlite
auto_increments ALL
# primary keys
if searching_for_end:
if re.search(r"INTEGER(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*
\s*,", line, re.IGNORECASE ):
line = re.sub("(?i)PRIMARY KEY", "PRIMARY KEY
AUTO_INCREMENT", line)
# replace " and ' with ` because mysql doesn't like quotes in
CREATE commands
if re.search(r'varchar.+DEFAULT', line):
sys.stderr.write('Not changing ` for DEFAULT string: %s' %
line)
else:
line = line.replace('"', '`').replace("'", '`')
# And now we convert it back (see above)
if re.match(r".*, ``\);", line):
line = re.sub(r'``\);', r"'');", line)
if searching_for_end and re.match(r'.*\);', line):
searching_for_end = False
if re.match(r"CREATE INDEX", line):
line = re.sub('"', '`', line)
print line,
--
David X. Glover - Macintosh IT Support
Physics @ University of Oxford
http://www-astro.physics.ox.ac.uk/~Glover/
Jabber/GTalk: david...@jabber.ox.ac.uk
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: sa...@bestpractical.com
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from SQLite to MySQL
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 serversMarshborough Rd
Tel: +44 1304 814890 Sandwich
Fax: +44 1304 814899 Kent, CT13 0PL, UK
signature.asc
Description: OpenPGP digital signature
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: sa...@bestpractical.com
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from SQLite to MySQL
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)
--
David X. Glover - Macintosh IT Support
Physics @ University of Oxford
http://www-astro.physics.ox.ac.uk/~Glover/
Jabber/GTalk: david...@jabber.ox.ac.uk
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: sa...@bestpractical.com
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from SQLite to MySQL
On 26 Nov 2009, at 11:22, Dominic Hargreaves wrote:
> or just write a little filter to tweak things yourself (eg you could
> start by removing the "BEGIN TRANSACTION" and corresponding "COMMIT.."
> lines from the dump), then see what else breaks.
In the end, that's what we did. So, for anyone else who wants to do this:
1. Dump the SQLite database with the ".dump" command.
2. Run the script through the Python script will I'll paste below.
3. Run "rt-setup-database --action init" on the new server. (To create things
like the Sessions table, which don't exist when you're using SQLite.)
4. Run the processed script through mysql.
5. Done.
Note that our script may not work with databases other than RT databases,
because we took a couple of shortcuts.
Script follows:
#!/usr/bin/env python
import re
import fileinput
import sys
def this_line_is_useless(line):
useless_es = [
'BEGIN TRANSACTION',
'COMMIT',
'sqlite_sequence',
'CREATE UNIQUE INDEX',
]
for useless in useless_es:
if re.search(useless, line):
return True
def has_primary_key(line):
return bool(re.search(r'PRIMARY KEY', line))
searching_for_end = False
for line in fileinput.input():
if this_line_is_useless(line): continue
# this line was necessary because ''); was getting
# converted (inappropriately) to \');
if re.match(r".*, ''\);", line):
line = re.sub(r"''\);", r'``);', line)
if re.match(r'^CREATE TABLE.*', line):
searching_for_end = True
m = re.search('CREATE TABLE ([a-zA-Z_]+) ', line)
if m:
(name,) = m.groups()
sys.stderr.write('creating table %s\n'%name)
line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS
%(name)s(\n"
line = line % dict(name=name)
else:
m = re.search('INSERT INTO "([a-zA-Z_]*)"(.*)', line)
if m:
line = 'INSERT INTO %s%s\n' % m.groups()
line = line.replace('"', r'\"')
line = line.replace('"', "'")
line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
line = line.replace('THIS_IS_FALSE', '0')
# Add auto_increment if it's not there since sqlite auto_increments ALL
# primary keys
if searching_for_end:
if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
# replace " and ' with ` because mysql doesn't like quotes in CREATE
commands
if re.search(r'varchar.+DEFAULT', line):
sys.stderr.write('Not changing ` for DEFAULT string: %s' % line)
else:
line = line.replace('"', '`').replace("'", '`')
# And now we convert it back (see above)
if re.match(r".*, ``\);", line):
line = re.sub(r'``\);', r"'');", line)
if searching_for_end and re.match(r'.*\);', line):
searching_for_end = False
if re.match(r"CREATE INDEX", line):
line = re.sub('"', '`', line)
print line,
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: sa...@bestpractical.com
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Migrating from SQLite to MySQL
On Thu, Nov 26, 2009 at 10:04:06AM +, David X. Glover wrote: > We've been running a test RT server using SQLite, and we'd now like to > migrate the data in it to MySQL, in order to move the system to a production > environment. > > I dumped the contents of the SQLite database into a file using ".dump", but > I'm having problems importing it into MySQL. Specifically, I get the > following error: > > # mysql -p rt < rt.sql > Enter password: > ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the > manual that corresponds to your MySQL server version for the right syntax to > use near 'TRANSACTION' at line 1 > > Lines 1 and 2 of the file look like this: > BEGIN TRANSACTION; > CREATE TABLE Attachments ( > > Do SQLite and MySQL speak "different" versions of SQL? Is there a tool to > convert a dumped database to be MySQL compatible? Offhand, I can't remember the exact differences, but either you could look at something like http://sqlfairy.sourceforge.net/ or just write a little filter to tweak things yourself (eg you could start by removing the "BEGIN TRANSACTION" and corresponding "COMMIT.." lines from the dump), then see what else breaks. The thing that you'll need to pay most attention to is probably getting the index definitions right. It would probably be a good idea to compare the final result with a fresh database as generated by rt-setup-database. [I guess the MySQL-based RT installation that I was responsible for in Astrophysics is no more, hmm? :)] Cheers, Dominic. -- Dominic Hargreaves, Systems Development and Support Team Computing Services, University of Oxford signature.asc Description: Digital signature ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Migrating from SQLite to MySQL
Hello, We've been running a test RT server using SQLite, and we'd now like to migrate the data in it to MySQL, in order to move the system to a production environment. I dumped the contents of the SQLite database into a file using ".dump", but I'm having problems importing it into MySQL. Specifically, I get the following error: # mysql -p rt < rt.sql Enter password: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1 Lines 1 and 2 of the file look like this: BEGIN TRANSACTION; CREATE TABLE Attachments ( Do SQLite and MySQL speak "different" versions of SQL? Is there a tool to convert a dumped database to be MySQL compatible? Thanks. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
