291776 - A script to copy a Bugzilla DB between two cross-DB installations

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, enhancement)

Description

[Max Kanat-Alexander]

I don't expect real users to ever want to switch database systems, so this
script wouldn't be intended for normal users.

However, I want a script that would allow me to copy over some of our testing
databases on landfill into PostgreSQL installations, so that we have a populated
PostgreSQL database to do some testing on.

So, I'm going to write a script that will copy a Bugzilla database between MySQL
and PostgreSQL on landfill, or find one in the PostgreSQL contrib/ directory
that works.

Comment 1

[Max Kanat-Alexander]

Attachment: Initial Script

Here, this script seems to do the job. You have to have an existing PgSQL
Bugzilla DB with the same name as the MySQL DB that you want to copy. The
script currently has to be hacked to take a username/pass for the connections
to each DB.

It seems to be working now, on landfill. It's very slow, particularly when it
gets to the email_setting table.

Comment 2

[Teemu Mannermaa (:wicked)]

Actually, now that (or soon) Bugzilla supports other database systems than
MySQL, there could be users that want to migrate from MySQL to PgSQL. And some
might even want to go other way. In the future, as more and more databases are
supported, migrations paths between each database should be provided if at all
possible.

Comment 3

[Max Kanat-Alexander]

(In reply to comment #2)
> In the future, as more and more databases are
> supported, migrations paths between each database should be provided if at all
> possible.

  Yeah. Actually, the script as written is very database-agnostic, so it would
be easy to change it from "MySQL to PgSQL" to "Source DB to Target DB."

Comment 4

[Joel Peshkin]

Why not merge this with dbclone to make a more universal database cloner??  This
would then be extended for versions of PG (just the way dbclone handles versions
of mysql) and potentially to others as well.

Comment 5

[Max Kanat-Alexander]

(In reply to comment #4)
> Why not merge this with dbclone to make a more universal database cloner??  This
> would then be extended for versions of PG (just the way dbclone handles versions
> of mysql) and potentially to others as well.

  Yeah, I thought about the same thing. Unfortunately, while dbclone takes a few
minutes to clone a DB, this script takes usually somewhere around 1/2 an hour or
more, at this point. It's much faster to use the native tools when they're
available, and only use this script to copy things back and forth between
different platforms.

Comment 6

[Henrik Wist]

I am eagerly waiting for PostgreSQL support in bugzilla, so that I can throw
away the MySQL instance on my server. This script is therefore rather important.

Comment 7

[Max Kanat-Alexander]

Attachment: DB-agnostic version

OK, this version works. I'd like to throw it in contrib/, actually, since it
doesn't require being on landfill, really.

Comment 8

[Max Kanat-Alexander]

Attachment: v3, New and Improved

OK, now it's pretty much fully generic. I haven't tested a Pg-to-MySQL copy
yet, but it should work.

Now, even if the Bugzilla schema changes in the future, this script should
continue to work.

The main critical bug that was fixed in this version (besides the fact that I
generally cleaned it up) was that we have to do "setval" on the sequences for
serial fields after inserting something into them manually, or they will be
very unhappy (they will try to insert values that already exist).

Comment 9

[Max Kanat-Alexander]

Attachment: v4, Totally Works All The Time

OK! Now with the patch that's on the blocker, I've verified that this script
works from both MySQL-to-Pg and Pg-to-MySQL.

Provided I haven't made any silly mistakes, I'd like to get it into contrib/.

Comment 10

[:glob ✱]

Comment on attachment 188892 [details]
v4, Totally Works All The Time

i'm going to r+ this, assuming you've tested it.

comments..

># We *should* use the table list from the target DB, because if somebody
># has customized their source DB, we still want the script to work,
># and it may otherwise fail in that situation (that is, the tables
># may not exist in the target DB).
># However, right now the bz_table_list_real function doesn't work
># properly on Pg.

so this script assumes that it's only for mysql --> pg ?

># We turn off autocommit on the target DB, because we're doing so
># much copying.
>$target_db->{AutoCommit} = 0;

could this be written as $target_db->bz_start_transaction ?
(and bz_commit_transaction at the end).

>                # If it's longer than precision + decimal point
>                if ( length($row->[$colnum]) > ($precision + 1) ) {
>                    # Truncate it to the highest allowed value.

i think the script should print a warning that a value has been truncated

>                $row->[$colnum] = '1901-01-01 00:00:00'
>                    if $date && $date eq '0000-00-00 00:00:00';

is this an epoch?

>    # PostgreSQL doesn't like it when you insert values into
>    # a serial field; it doesn't increment the counter 
>    # automatically.

maybe database specific fixups could be moved into the database module at a
later date.

Comment 11

[Max Kanat-Alexander]

(In reply to comment #10)
> [snip]
> so this script assumes that it's only for mysql --> pg ?

  Oh, oops! That's a legacy comment. :-) I fixed table_list_real in bug 300311.
So I should change the behavior to be the correct one and remove the comment.

> could this be written as $target_db->bz_start_transaction ?
> (and bz_commit_transaction at the end).

  Not really. bz_start_transaction implies other things than changing AutoCommit
to 0. For now, this does exactly what we want.

> i think the script should print a warning that a value has been truncated

  Agreed.

> >                $row->[$colnum] = '1901-01-01 00:00:00'
> >                    if $date && $date eq '0000-00-00 00:00:00';
> 
> is this an epoch?

  Sort of. It basically means "beginning of time" effectively enough for
Bugzilla's purposes.

> maybe database specific fixups could be moved into the database module at a
> later date.

  Well, it's contrib code, so I'd probably just prefer to leave it here, for
now. But maybe the script itself could be modularized.

Comment 12

[Max Kanat-Alexander]

Attachment: v5, nits addressed

OK, I addressed the nits I said I'd address, so I'm carrying forward r+.

Comment 13

[Max Kanat-Alexander]

Requesting approval for contrib.

Comment 14

[Max Kanat-Alexander]

Attachment: v6, Which Will Be Committed

I noticed a few slight nit bugs last time I ran the script, so I fixed them,
ran it again, and everything is good. :-) So here's the version that will
actually get committed. I hope nobody kills me for r+'ing my own patch twice,
here. :-)

Comment 15

[:glob ✱]

> I hope nobody kills me for r+'ing my own patch twice, here. :-)

still looks good to me.

Comment 16

[Max Kanat-Alexander]

I modified the comments at the top slightly, before I checked it in, so that
they were more accurate.

Note that most Bugzilla installs will require bug 300311 to be applied before
they can actually *use* this script.

RCS file: /cvsroot/mozilla/webtools/bugzilla/contrib/bzdbcopy.pl,v
done
Checking in contrib/bzdbcopy.pl;
/cvsroot/mozilla/webtools/bugzilla/contrib/bzdbcopy.pl,v  <--  bzdbcopy.pl
initial revision: 1.1
done

Comment 17

[Max Kanat-Alexander]

Wow, somehow I forgot to check this in on the 2.20 branch!! Well, it's there
now, I guess it will make it in for 2.20.1.

Checking in contrib/bzdbcopy.pl;
/cvsroot/mozilla/webtools/bugzilla/contrib/bzdbcopy.pl,v  <--  bzdbcopy.pl
new revision: 1.1.2.2; previous revision: 1.1.2.1
done