export from mysql and import into postgresql

[Michael Cronenworth]

Hello,

Google searching for my subject line has not returned results worth
while. I am trying to move a Bugzilla installation from one local
machine to another local machine.

Current machine:
Fedora 12 x86_32
Bugzilla 3.4.8
MySQL 5.1.47
LDAP auth

New machine:
Fedora 13 x86_64
Bugzilla 3.4.8 (clean bugzilla installation)
PostgreSQL 8.4.4
LDAP auth

There are about 1000 bugs in the current database with about 50
attachments (patches, PDFs, and some PNGs), and two groups (for two
classifications). I need to maintain the bugs, their history,
attachments, classifications, and group data.

I have tried to dump the SQL data from MySQL and use a translator Perl
script, but the script could not parse the binary attachment data and
failed quite fantastically.

Then I tried to export the MySQL data using the --hex-blob option.
PostgreSQL doesn't understand this on import.

I haven't found a clear "export" or "dump" function of Bugzilla. I have
seen people mention "xml.cgi" but I have not found any documentation on
how to use it.

Anyone have an idea?

Thanks,
Michael

[Michael Cronenworth]

Michael Cronenworth wrote:
> Anyone have an idea?

The closest I have been able to get is to import all the data except for
the attach_data table.

mysqldump --complete-insert --compatible=postgresql -u bugs -p bugs >
bugs.sql
psql -h localhost -U bugs -W bugs < bugs.sql

I just need to find something to properly escape the binary data so that
PostgreSQL will import it. Any ideas on how to do this or otherwise
import my attachment data would be most welcome.

[Michael Cronenworth]

Michael Cronenworth wrote:
> The closest I have been able to get is to import all the data except for
> the attach_data table.

I used a program called "dbconvert" (Windows only, closed source) to get
the binary attach_data table escaped properly.

[fida]

Hi

I did this recently.

First both databases must have exactly the same bugzilla schema. so
you have to run the checksetup.pl from the new bugzilla instance on
the source one. If you added some custom field on the source database,
you must add them on the destination too. (the same name, the same
type,etc.. )
The steps i did are :
1- dump the mysql database
mysqldump -B <the_name_of_the_database_to_convert> -p --add-drop-
database --add-drop-table --single-transaction > /tmp/bug-mysql.sql

2- restore this dump on the new machine.
Mysql -p < bug-mysql.sql

3- check if all is ok on the restored database (you can use myisamchk)
and run this command from the bugzilla source code directory ( (insure
that you put the right database name/user/password on the source
localconfig file)

contrib/recode.pl --guess --show-failure --charset=cp1252

4- If you change the bugzilla version, execute the new checksetup.pl
on this database. you must pay attention on the custom field you added
manually on the old database. You can for example edit the Bugzilla/
Field.pm perl Library to make checksetup.pl add those fields
automatically.

5- create the new postgresql bugzilla database on the new machine

Add the bugzilla database user on postgresql (bugs)
create the database (example createdb bugs --owner bugs )

6- run the same checksetup.pl on this database . (On localconfig,
change the database db_driver/db_name/db_user/password to point on the
postgresql DB parameters )

to have the same bug id as the source database, we have to move the
database under an empty bugzilla postgresql database.
7-
configure the file contrib/bzdbcopy.pl to put on it the database
source and the database destination parameters

Here is an example with database source = bugs5 , database destination
= bugpg

use constant SOURCE_DB_TYPE => 'Mysql';
use constant SOURCE_DB_NAME => 'bugs5';
use constant SOURCE_DB_USER => 'bugs';
use constant SOURCE_DB_PASSWORD => 'pass';
use constant SOURCE_DB_HOST => 'localhost';

Settings for the 'Target' DB that you are copying to.

use constant TARGET_DB_TYPE => 'Pg';
use constant TARGET_DB_NAME => 'bugpg';
use constant TARGET_DB_USER => 'bugzilla';
use constant TARGET_DB_PASSWORD => 'pass';

8- Execute Perl contrib/bzdbcopy.pl

9- execute ./checksetup.pl

[Max Kanat-Alexander]

On 10/11/2010 02:38 PM, Michael Cronenworth wrote:
> Google searching for my subject line has not returned results worth
> while. I am trying to move a Bugzilla installation from one local
> machine to another local machine.

There's a script included with Bugzilla called contrib/bzdbcopy.pl that
exists for just the purpose of copying from one type of DB to another.

-Max
--
http://www.everythingsolved.com/
Competent, Friendly Bugzilla and Perl Services. Everything Else, too.