Mailing List Archive: Request Tracker: Users

Migrating from SQLite to MySQL

Index | Next | Previous | View Threaded

d.glover1 at physics

Nov 26, 2009, 2:04 AM

Post #1 of 6 (1017 views)
Permalink

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: sales [at] bestpractical

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

dominic.hargreaves at oucs

Nov 26, 2009, 3:22 AM

Post #2 of 6 (973 views)
Permalink

Re: Migrating from SQLite to MySQL [In reply to]

On Thu, Nov 26, 2009 at 10:04:06AM +0000, 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

Attachments:

signature.asc (0.19 KB)

d.glover1 at physics

Nov 26, 2009, 7:38 AM

Post #3 of 6 (968 views)
Permalink

Re: Migrating from SQLite to MySQL [In reply to]

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: sales [at] bestpractical

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

d.glover1 at physics

Nov 27, 2009, 2:32 AM

Post #4 of 6 (951 views)
Permalink

Re: Migrating from SQLite to MySQL [In reply to]

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: davidcwg [at] jabber

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

Community help: http://wiki.bestpractical.com
Commercial support: sales [at] bestpractical

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

matthew.seaman at thebunker

Nov 27, 2009, 2:53 AM

Post #5 of 6 (949 views)
Permalink

Re: Migrating from SQLite to MySQL [In reply to]

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

Attachments:

signature.asc (0.25 KB)

d.glover1 at physics

Nov 27, 2009, 3:42 AM

Post #6 of 6 (952 views)
Permalink

Re: Migrating from SQLite to MySQL [In reply to]

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: davidcwg [at] jabber

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

Community help: http://wiki.bestpractical.com
Commercial support: sales [at] bestpractical

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

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads