MantisBT

View Issue Details Jump to Notes ] Wiki ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0007229mantisbtmigrationpublic2006-06-25 09:132013-04-02 06:12
Reportercas 
Assigned Togiallu 
PrioritynormalSeverityfeatureReproducibilityN/A
StatusassignedResolutionopen 
PlatformOSOS Version
Product Version 
Target VersionFixed in Version 
Summary0007229: Merging multiple instances of Mantis
DescriptionSuppose you have multiple Mantis installations and you would like to combine them.
Then you are in trouble since there is no automated procedure.

This is why I developed a few scripts in order to automate this process as much as possible.

How does this work ?
0. Ensure installations are on the same version
1. Make a backup of your current database
2. Make a copy of the Mantis databse tables you would like to merge
3. Copy these tables into the original mantis database with a different prefix
    So if they are called mantis_bug_table, copy them as mexico_bug_table
4. Copy the scripts in the root of your Mantis installation
5. Add the text elements contained in " import_instance_txt.php" into custom_strings_inc.php
6. Logon to Mantis with Admin rights
7. Run program import_instance_page.php
8. Follow instructions
9. Verify results
10. Remove the scripts

This also has support for the FAQ and Timetracking addon.
Additional InformationPlease verify the max execution time in php.ini and the possible cgi time out value (under windows)

Tables that are being converted:

mantis_bug_file_table
mantis_bug_history_table
mantis_bug_monitor_table
mantis_bug_relationship_table
mantis_bug_table
mantis_bug_text_table
mantis_bugnote_table
mantis_bugnote_text_table

mantis_custom_field_project_table
mantis_custom_field_string_table
mantis_custom_field_table

mantis_project_category_table
mantis_project_file_table
mantis_project_hierarchy_table
mantis_project_table
mantis_project_user_list_table
mantis_project_version_table

mantis_user_table

mantis_filters_table
mantis_sponsorship_table
mantis_news_table

mantis_module_timetracking
mantis_faq_table

Tagspatch
Attached Fileszip file icon import_instance.zip [^] (17,419 bytes) 2006-06-25 09:13
zip file icon import_instance2.zip [^] (17,994 bytes) 2006-07-03 15:51
zip file icon Import_instances.zip [^] (17,994 bytes) 2006-10-28 08:38
png file icon error.png [^] (8,688 bytes) 2008-11-04 12:41


zip file icon import_instance-2010-04-30.zip [^] (17,505 bytes) 2010-04-30 02:08
gz file icon patch-2010-04-30.gz [^] (1,177 bytes) 2010-04-30 02:08
zip file icon import_instance20100526.zip [^] (15,385 bytes) 2010-05-26 11:48
patch file icon import_instance_20100526.patch [^] (5,561 bytes) 2010-06-02 08:33 [Show Content]
gz file icon import_instance_20130214.patch.gz [^] (1,485 bytes) 2013-02-14 11:40
tgz file icon MantisImportInstance.tgz [^] (7,013 bytes) 2013-04-02 06:07

- Relationships
has duplicate 0006933closedryandesign Merging Two Mantis Installations 

-  Notes
User avatar (0013029)
cas (reporter)
2006-06-25 12:00

This version only syncs the user table, next one will have the same function for custom_fields.
In addition I am splitting multiple table conversions in parts of max 5000 records to avoid time outs.
Update expected shortly although testing is appreciated so it can be improved.
User avatar (0013062)
cas (reporter)
2006-07-03 15:52

Version 2 have been added, hope this can be used by others also.
User avatar (0013164)
vboctor (administrator)
2006-07-29 02:01

Haven't looked at the code, but do you handle refernces to issues and issue notes from withing the summary, description, additional information, steps to reproduce and notes?
User avatar (0013170)
cas (reporter)
2006-07-30 07:02

At current they are not being "translated" since there was little use for it in our environment.
This however can be added to the code if there is enough need for it. This could be made an option since it can prove to be very time consuming on huge databases.
User avatar (0013646)
vboctor (administrator)
2006-10-26 03:53

cas, it would be great if you can re-add the attachment since it was lost during the server crash.
User avatar (0013654)
cas (reporter)
2006-10-28 08:40

Latest version is available here again. I have emailed the scripts to various people, if they have comments add-ons, improvements, it should be reported here.
User avatar (0018421)
grangeway (developer)
2008-07-13 10:14

Hello,

I've added the tag patch for someone to look at this attachment.

Giallu has been looking at some import/export routines, so I dont know if this would be helpful for him.
User avatar (0018554)
grangeway (developer)
2008-07-15 14:10

Assigning to giallu to review
User avatar (0019771)
dapozzom (reporter)
2008-11-04 12:40

I'm using 1.1.1 and trying to merg two instances. The migration process is working till step 7 when I receive the error attached. The source instance for bug_text_table has ids from 2 to 1334 the destination one from 1787 to 2281.
Do you have any ideas where the problem can be.
Thanks and regards,
Marco
User avatar (0019814)
cas (reporter)
2008-11-08 12:49

Will look into it when back home. Suppose you cannot share the databases so I can test with your data?
If you can, send me a pm and I will give you my email address.
Otherwise it will proof very difficult since I have done this process multiple times be it not with 1.1.1
User avatar (0020659)
cas (reporter)
2009-01-20 06:05

Issue was solved. It appeared that both instances were not on the saem version.
User avatar (0020661)
giallu (developer)
2009-01-20 07:11

in the latest release we made (1.2.0a3) there is a new plugin for exporting data in xml format and importing it back.

Please have a look at it, I think it makes up a good base and it should be easy to expand the coverage of the import/export routine. Feel free to ping me if you would like to help.
User avatar (0020665)
cas (reporter)
2009-01-20 13:27

Had already seen that, not yet time to explore.
Will check it out.
User avatar (0023506)
joshribakoff (reporter)
2009-10-31 00:38

This would be useful
User avatar (0025353)
johmart (reporter)
2010-04-30 02:12
edited on: 2010-04-30 02:12

Thanks a lot for this wonderful work. It saved me a lot of time!

The attached patch and new version fix the following things:
- error in table name on executing import_instance3.php
- error when importing bugnote texts (using wrong id)

Also introduces new feature:
- possibility to set minimum bug number to use for imported problem reports (rather than auto-detected one)

User avatar (0025609)
rini17 (reporter)
2010-05-25 12:00

I was under impression that 2010-04-30 version is compatible with 1.2.x, but seems it isn't? I got error in import_instance1.php under mantis 1.2.1:

Database query failed. Error received from database was 0001146: Table 'test.kavo_project_category_table' doesn't exist for the query: ALTER TABLE kavo_project_category_table DROP PRIMARY KEY .

Problem is caused by mantis_project_category_table was renamed to mantis_category_table .



Second question, why do you drop primary keys of source tables? It makes debugging harder (I have to re-import them every time).
User avatar (0025611)
johmart (reporter)
2010-05-26 01:00

The 2010-04-30 version worked for me with mantis 1.2.1.
User avatar (0025613)
cas (reporter)
2010-05-26 11:49
edited on: 2010-05-26 13:25

Have reviewed the code and made some changes. There still was an issue with the category table which should be fine now.
New code uploaded for version 1.2.X

Support for FAQ & Timetracking is restricted to the plugins available.

User avatar (0025659)
rini17 (reporter)
2010-06-02 06:05

Now I am getting this: "0001075: Incorrect table definition; there can be only one auto column and it must be defined as a key : ALTER TABLE kavo_category_table DROP PRIMARY KEY".

I am using mysql 5.0.51a-24+lenny2+spu1 and can't upgrade. Found helpful comment at http://dev.mysql.com/doc/refman/5.0/en/alter-table.html [^] , will try to implement it:

> Attempting to "ALTER TABLE ... DROP PRIMARY KEY" on a table when an
> AUTO_INCREMENT column exists in the key generates an error:

> ERROR 1075 (42000): Incorrect table definition; there can be only one auto
> column and it must be defined as a key.
> To make this work without erroring, drop and re-add the new primary key in a
> single statement, e.g.:

> ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY(col1,col2);
User avatar (0025660)
rini17 (reporter)
2010-06-02 08:42
edited on: 2010-06-02 08:42

I have attached patch against version 20100526 for issues I encountered under mysql-5.0.51a-24+lenny2+spu1 and mantis 1.2.1:

1. Above error "0001075: Incorrect table definition;....".
--> fixed by resetting id column to non-autoincrement before dropping primary key
2. still broken category table support
--> fixed typo and updated columns
3. broken custom_field table support
--> removed all references to mantis_custom_field_table.advanced column

I have yet to thoroughly check the import results, so there could be more problems ahead. Despite everything I am still very thankful to you, cas, for your work!

User avatar (0031744)
derril (reporter)
2012-05-03 02:48

I found that the script written by cas didn't work for, so I've written another script to do the migration as I needed and I thought it might be useful: https://gist.github.com/2573767 [^]
I've written it very procedurally so that it's easy to understand. Feel free to add or make changes. It won't work for everyone as it's meant to deal with 2 mantis installs that have the same users and also keep the bug IDs.
User avatar (0031752)
Dentxinho (reporter)
2012-05-03 10:53

How to keep the same bug IDs, if you can have the same ID on both instances?
User avatar (0031755)
derril (reporter)
2012-05-03 12:53

I was lucky in my case that the mantis i wanted to merge into hadn't been used a lot and in the other one the project's bug IDs started after the IDs in the i wanted to merge into.
Maybe useful in only a small number of cases, but thought I make it available.
User avatar (0032580)
jgeorge (reporter)
2012-08-16 09:17

Hi,
I have an issue related with Mantis and SQL server.
Yesterday i downloaded "mantisbt-1.2.11" and installed.
We have a running mantis pointed to a Database 'bugtracker', i don't know version of this. We forced to install because, 'Reset password' was not working in old version.

Now, i want to connect to existing database with New "mantisbt-1.2.11".
any chance?
Pls advice
thanks
User avatar (0035140)
RobertMartin (reporter)
2013-02-14 10:22

I've encounter an issue in step 1 of this tool, when a category name has a simple quote in it. I've solved this issue with this modification :

--- /root/res/pkg/tmp/import_instance1.php 2013-02-08 17:03:54.000000000 +0100
+++ import_instance1.php 2013-02-14 16:09:37.000000000 +0100
@@ -108,7 +108,8 @@
 echo "Preparing/Handling custom_field conversion" ;
 $result1 = mysql_query("SELECT id,name, type, possible_values,default_value,valid_regexp, access_level_r, access_level_rw, length_min, length_max,require_report,require_update,display_report,display_update,require_resolved,display_resolved,display_closed,require_closed from $g_cn_custom_field_table");
 while (list( $id,$name, $type, $possible_values,$default_value,$valid_regexp, $access_level_r, $access_level_rw, $length_min, $length_max,$require_report,$require_update,$display_report,$display_update,$require_resolved,$display_resolved,$display_closed,$require_closed ) = mysql_fetch_row($result1)) {
- $result2 = db_query("SELECT id from $t_mantis_custom_field_table where name='$name'");
+ $name = str_replace('\'', '\\\'', $name);
+ $result2 = db_query("SELECT id from $t_mantis_custom_field_table where name='$esc_name'");
        $num_rows = db_num_rows($result2);
        if ($num_rows>0) {
                ## user already exists, retrieve future id
User avatar (0035142)
RobertMartin (reporter)
2013-02-14 10:59

I've also had this problem when trying to list bugs after the merge:

APPLICATION ERROR 0001502

Category not found.

I've solved it by applying this modification:

--- import_instance5.php 2013-02-14 16:41:35.000000000 +0100
+++ import_instance5.new.php 2013-02-14 16:35:57.000000000 +0100
@@ -28,7 +28,7 @@
 /////////////////////////////////////
 echo "
";
 echo "Preparing bug_table" ;
-$result = db_query("update $g_cn_bug_table set id=id+$bug_start, bug_text_id=bug_text_id+$bug_start, project_id=project_id+$project_start");
+$result = db_query("update $g_cn_bug_table set id=id+$bug_start, bug_text_id=bug_text_id+$bug_start, project_id=project_id+$project_start, category_id=category_id+$category_start");
 $result = db_query("update $g_cn_bug_table set duplicate_id=duplicate_id+$project_start where duplicate_id!=0");
 ## next update this file with the correct userid
 $result1 = mysql_query("SELECT id,reporter_id,handler_id from $g_cn_bug_table");
@@ -72,4 +72,4 @@
 </div>

 <?php
-html_page_bottom1( __FILE__ );
\ Pas de fin de ligne à la fin du fichier.
+html_page_bottom1( __FILE__ );
User avatar (0035144)
RobertMartin (reporter)
2013-02-14 11:42

I've attached a patch gathering the corrections mentionned in activity #35140 and #35142.
User avatar (0035916)
joshribakoff (reporter)
2013-03-19 13:54

Instead of trying to merge at the table level wouldn't it be better to add the ability to export all data to a CSV? This would be good because it would allow people to switch over from other ticket systems by importing data from a 3rd party ticket system. It would also solve the problem of merging.

The way you're going about it now by merging at the table level will make it difficult to maintain the software, every-time you change the database you'll need to update this merge feature. Its also not a standard feature. Import/export to CSV on the other hand is a very standard feature, accomplishes the same goals, and more.
User avatar (0035917)
cas (reporter)
2013-03-19 14:49

CSV im/ex-ports are indeed quite common but the databases of the various systems are not. It is easy enough to export all tables to csv but it will not help very much. When merging, certain logic needs to be applied, that is what it is all about.
So both serve their own purpose.
User avatar (0035926)
joshribakoff (reporter)
2013-03-20 08:21

Not so sure about that. When you import, you're merging new data into the system.
User avatar (0035927)
cas (reporter)
2013-03-20 10:12

That is correct but when importing you are simply adding issues to a (new/other) system. Merging is taking all available data (like history etcetera) from 2 installations of the same product into account.
For me, simple import was just not enough hence the development of the merging scripts/plugin.
User avatar (0036010)
RobertMartin (reporter)
2013-04-02 06:11

I've turned the original script to a plugin (MantisImportInstance.tgz).

My two mantis instances are now only one thanks to it.

Afterward, I realized that it shouldn't be a plugin but more a script available in the admin directory.

- Issue History
Date Modified Username Field Change
2006-06-25 09:13 cas New Issue
2006-06-25 09:13 cas File Added: import_instance.zip
2006-06-25 12:00 cas Note Added: 0013029
2006-06-26 13:14 chillax Relationship added related to 0006933
2006-07-03 15:51 cas File Added: import_instance2.zip
2006-07-03 15:52 cas Note Added: 0013062
2006-07-26 08:09 ryandesign Relationship replaced has duplicate 0006933
2006-07-29 02:01 vboctor Note Added: 0013164
2006-07-30 07:02 cas Note Added: 0013170
2006-10-26 03:53 vboctor Note Added: 0013646
2006-10-28 08:38 cas File Added: Import_instances.zip
2006-10-28 08:40 cas Note Added: 0013654
2008-07-13 10:12 grangeway Tag Attached: patch
2008-07-13 10:14 grangeway Note Added: 0018421
2008-07-15 14:10 grangeway Status new => assigned
2008-07-15 14:10 grangeway Assigned To => giallu
2008-07-15 14:10 grangeway Note Added: 0018554
2008-11-04 12:40 dapozzom Note Added: 0019771
2008-11-04 12:41 dapozzom File Added: error.png
2008-11-08 12:49 cas Note Added: 0019814
2008-11-09 09:03 dapozzom Note Added: 0019820
2008-11-09 11:13 dapozzom Note Deleted: 0019820
2008-11-09 11:14 dapozzom Note Added: 0019821
2008-11-19 10:53 dapozzom Note Deleted: 0019821
2009-01-20 06:05 cas Note Added: 0020659
2009-01-20 07:11 giallu Note Added: 0020661
2009-01-20 13:27 cas Note Added: 0020665
2009-10-31 00:38 joshribakoff Note Added: 0023506
2010-04-30 02:08 johmart File Added: import_instance-2010-04-30.zip
2010-04-30 02:08 johmart File Added: patch-2010-04-30.gz
2010-04-30 02:12 johmart Note Added: 0025353
2010-04-30 02:12 johmart Note Edited: 0025353 View Revisions
2010-05-25 12:00 rini17 Note Added: 0025609
2010-05-26 01:00 johmart Note Added: 0025611
2010-05-26 11:48 cas File Added: import_instance20100526.zip
2010-05-26 11:49 cas Note Added: 0025613
2010-05-26 13:24 cas Note Edited: 0025613 View Revisions
2010-05-26 13:25 cas Note Edited: 0025613 View Revisions
2010-06-02 06:05 rini17 Note Added: 0025659
2010-06-02 08:33 rini17 File Added: import_instance_20100526.patch
2010-06-02 08:42 rini17 Note Added: 0025660
2010-06-02 08:42 rini17 Note Edited: 0025660 View Revisions
2012-05-03 02:48 derril Note Added: 0031744
2012-05-03 10:53 Dentxinho Note Added: 0031752
2012-05-03 12:53 derril Note Added: 0031755
2012-08-16 09:17 jgeorge Note Added: 0032580
2013-02-14 10:22 RobertMartin Note Added: 0035140
2013-02-14 10:59 RobertMartin Note Added: 0035142
2013-02-14 11:40 RobertMartin File Added: import_instance_20130214.patch.gz
2013-02-14 11:42 RobertMartin Note Added: 0035144
2013-03-19 13:54 joshribakoff Note Added: 0035916
2013-03-19 14:49 cas Note Added: 0035917
2013-03-20 08:21 joshribakoff Note Added: 0035926
2013-03-20 10:12 cas Note Added: 0035927
2013-04-02 06:07 RobertMartin File Added: MantisImportInstance.tgz
2013-04-02 06:11 RobertMartin Note Added: 0036010


MantisBT 1.2.16dev master-1.2.x-8c2bd07 [^]
Copyright © 2000 - 2013 MantisBT Team
Time: 0.1539 seconds.
memory usage: 3,070 KB
Powered by Mantis Bugtracker