How to maintain identical jotform DB structure between different environments?

  • Profile Image
    Jacek Gruca
    Asked on February 17, 2012 at 10:00 AM

    Hi,

     

    We have jotforms installed on two separate environments and we've written a migration script, which migrates forms by id. Relevant data from the forms, form_properties, question_properties is migrated. Additionally, when a user doesn't exist in the target environment, he/she is migrated as well.

     

    During one such migration we got an error because the target users table didn't contain an LDAP column, while the source did. We also got the following error after manually migrating the user:

    Error on query: SELECT * FROM `bounced_emails` WHERE `email` = 'mstanford@eircom.ie'
    Error returned:Table 'jotform.bounced_emails' doesn't exist

    because the bounced_emails table didn't exist on target environment, while it existed on the source environment.

     

    There are a number of files, which can alter the DB structure of jotforms. They are located in the forms\opt\db\ruckusing\db\migrate directory. It is not clear to me, how these are triggered. I upload a log of changes which happened on the source environment but not on the target environment, which I believe is the reason for the errors we're getting.

     

    Can you please advise when these are triggerred and how we can ensure identical DB structure on both source and target?

     

    Many thanks,

     

    Jack

     

    PS. I was unable to upload the log, so I paste it here:

    Dec 19 17:01:18 [info] SELECT version FROM `schema_info`
    Dec 19 17:01:18 [info] CREATE TABLE `block_email_banners` (
                    `username` VARCHAR( 31 ) NOT NULL, PRIMARY KEY ( `username` )
                   ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci COMMENT = 'These users will not see banners on their emails'
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 51
    Dec 19 17:01:18 [info] ALTER TABLE `users` ADD INDEX ( `email` )
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 52
    Dec 19 17:01:18 [info] ALTER TABLE `users` ADD `LDAP` INT NOT NULL COMMENT 'If this user account migrated from LDAP or not'
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 53
    Dec 19 17:01:18 [info] ALTER TABLE  `spam_prob` CHANGE  `form_id`  `form_id` BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL
    Dec 19 17:01:18 [info] ALTER TABLE `spam_prob` ADD CONSTRAINT `spam_prob_form_fk` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 54
    Dec 19 17:01:18 [info] ALTER TABLE  `users` CHANGE  `account_type`  `account_type` ENUM(  'FREE',  'OLDPREMIUM',  'PREMIUM',  'PROFESSIONAL',  'ADMIN',  'GUEST',  'SUPPORT' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT  'FREE'
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 55
    Dec 19 17:01:18 [info] CREATE TABLE `bounced_emails` (
                            `email` VARCHAR( 250 ) NOT NULL ,
                             PRIMARY KEY ( `email` )
                            ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci
                            COMMENT = 'Contains addresses that we should not send emails to.'
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 56
    Dec 19 17:01:18 [info] ALTER TABLE  `forms` CHANGE  `new`  `new` INT( 11 ) NULL DEFAULT NULL
    Dec 19 17:01:18 [info] UPDATE `forms` SET `new`=-1 WHERE `new`=127
    Dec 19 17:01:18 [info] UPDATE `schema_info` SET version = 57