Allow migration of users from one conference to another

[sjmurdoch]

Once use case for HotCRP is the hybrid conference/journal model (e.g. VLDB, EVT/WOTE, PETS). Here there's one mainly static program committee, with each issue being a "conference" in HotCRP's multi-conference model.

It would help here if the PC from one issue could be moved to the next. I did this through the following SQL, but it's a bit of a hack. This also doesn't migrate conference settings because I wasn't sure what items in the Settings table was safe to copy over. I presume both conferences need the same passwordHmacKey to make this work.

First get a list of the contactId's for PC members, Chairs, and Sysadmins:

SELECT GROUP_CONCAT(contactId) FROM (SELECT contactId from Chair UNION DISTINCT SELECT contactId from ChairAssistant UNION DISTINCT SELECT contactId FROM PCMember) AS T GROUP BY NULL;

which returns something like:

1,2,4,5,6,7

Then dump the tables which you want to copy all of:

mysqldump OLD_DATABASE Chair ChairAssistant TopicArea PCMember > DUMPFILE

Then dump the information needed just for the selected contactId's:

mysqldump -w "contactId IN (1,2,4,5,6,7,...)" OLD_DATABASE ContactInfo TopicInterest >> DUMPFILE

Then restore the tables in a freshly created conference:

mysql NEW_DATABASE < DUMPFILE

[kohler]

So there's already support for this; try lib/backupdb.sh --pc.

[sjmurdoch]

Ah, I didn't spot that. Though it doesn't work for me:

[smurdoch@pets2015 lib]$ ./backupdb.sh -n pets2015-01 --pc > ~/tmp
+ mysqldump -u'pets201501live' -p<REDACTED> --max_allowed_packet=1000M 'pets201501live'
mysqldump: unknown variable 'defaults-extra-file=/tmp/hotcrptmp.PRLXzi'
mysqldump: Couldn't find table: "ChairTag"

[kohler]

That should be fixed in latest git. It also doesn't address the Settings question—you are right that many Settings aren't safe to move from one conference to another.

Eddie

On Thu, Jan 29, 2015 at 9:56 AM, Steven Murdoch notifications@github.com wrote:

Ah, I didn't spot that. Though it doesn't work for me:

[smurdoch@pets2015 lib]$ ./backupdb.sh -n pets2015-01 --pc > ~/tmp

• mysqldump -u'pets201501live' -p --max_allowed_packet=1000M 'pets201501live' mysqldump: unknown variable 'defaults-extra-file=/tmp/hotcrptmp.PRLXzi' mysqldump: Couldn't find table: "ChairTag"

— Reply to this email directly or view it on GitHub https://github.com/kohler/hotcrp/issues/37#issuecomment-72037754.

[sjmurdoch]

I tried manually moving over the settings that I understood:

mysqldump --no-create-info --skip-add-drop-table -w 'name in ("tag_chair", "outcome_map", "review_form", "msg.clickthrough_submit", "opt.clickthrough_submit", "msg.revprefdescription", "msg.clickthrough_review", "opt.clickthrough_review", "msg.conflictdef", "options")' pets201501live Settings > ~/pets2015-01-settings.sql
mysql pets201502live -e 'DELETE FROM Settings WHERE name IN ("tag_chair", "outcome_map", "review_form", "msg.clickthrough_submit", "opt.clickthrough_submit", "msg.revprefdescription", "msg.clickthrough_review", "opt.clickthrough_review", "msg.conflictdef", "options");'
mysql pets201502live < ~/pets2015-01-settings.sql

This worked, but I hit a pretty big gotcha – the first person to subsequently register becomes a sys-admin! Fortunately this was my test user :smile: I guess this is because HotCRP is still in new conference mode after such a copy.

[kohler]

You need

DELETE FROM Settings WHERE name='setupPhase'

in the new conference.

[nc2y]

Following up on this (four and a half years later ;-), I also get one of the errors Steve referred to (using the latest git at the time of writing):

$ sudo lib/backupdb.sh -n eppquals-plan20 --pc > /tmp/pc.sql
+ mysqldump -u'eppquals-plan20' -p<REDACTED> --max_allowed_packet=1000M 'eppquals-plan20'
mysqldump: [ERROR] unknown variable 'defaults-extra-file=/tmp/hotcrptmp.7ct4fO'```

[nc2y]

This can be fixed with the following patch (see corresponding pull request):

--- lib/backupdb.sh.orig    2019-11-26 13:55:14.648211593 -0500
+++ lib/backupdb.sh 2019-11-26 14:26:05.770519499 -0500
@@ -99,7 +99,7 @@

 database_dump () {
     if $pc; then
-        eval "$MYSQLDUMP $FLAGS $mydumpargs $dbname --where='(roles & 7) != 0' ContactInfo"
+        eval "$MYSQLDUMP $mydumpargs $FLAGS $dbname --where='(roles & 7) != 0' ContactInfo"
         pcs=`echo 'select group_concat(contactId) from ContactInfo where (roles & 7) != 0' | eval "$MYSQL $myargs $FLAGS -N $dbname"`
         eval "$MYSQLDUMP $mydumpargs $FLAGS --where='contactId in ($pcs)' $dbname TopicInterest"
         eval "$MYSQLDUMP $mydumpargs $FLAGS $dbname Settings TopicArea"```