Running migrations "down" randomly fails

[Daemach]

While testing cfmigrations, I've been running migrations down then back up. I assume that "down" means starting with the last migration run, run the down function then remove the record from the cfmigrations table and keep doing this until none are left....

Down occasionally does not fire for some reason, verified by adding cfdump("Got this far");abort; in my migration file, inside the "down" method.

function onAppInit(event,rc,prc){
		getInstance( "MigrationService@cfmigrations" ).runAllMigrations( "down" );
		getInstance( "MigrationService@cfmigrations" ).runAllMigrations( "up" );
}

[Daemach]

Additionally, schema.drop( "table" ) does not work consistently. Changing this to schema.drop( "table", {}, true ) did work.

[elpete]

I need some more information on this before I can work on it. A reproducible example would be best, but it seems like that is part of the problem according to what you wrote above. If you can at least give the conditions and code this is happening with, even if it isn't reproducible 100% of the time, that would be something. I'd like:

1. CFML Engine and Version
2. Database Type and Version
3. JDBC Driver and Version
4. All migration files trying to be ran.
5. ColdBox Version
6. cfmigrations version

Additionally, schema.drop( "table" ) does not work consistently. Changing this to schema.drop( "table", {}, true ) did work.

This is especially bizarre to me since schema.drop( "table", {}, true ) is just putting in the default values.

[Daemach]

• Lucee 5.3.5+9
• MSSQL 2019
• net.sourceforge.jtds.jdbc.Driver (maybe I should switch this to the microsoft driver...)
• Coldbox 5.6.2+1148
• cfmigrations (2.0.3)

I'm with you on the bizarre. I ran it repeatedly and it kept skipping the drop, causing errors. When I added true it started working...

And this needs a dropIfExists for columns too, (any drop really), to avoid having to put them in try/catch blocks which may miss other errors.

2020_04_15_092429_addGalleryTable:
component {

function up( SchemaBuilder schema, QueryBuilder query ) {
    try{
        schema.create( "Galleries", function( Blueprint table ) {
            table.increments( "id" );
            table.integer( "userID" );
            table.string( "subdomain" );
            table.string( "galleryName" );
            table.string( "about" );
            table.timestamp( "createdDate" ).default("getDate()");
            
        } );
    } catch (any e) {}

    try {
        schema.alter( "Images", function( table ) {
            table.addColumn( table.integer( "galleryID" ).nullable("true") );
        } );
    } catch (any e) {}

    try {
        schema.alter( "Users", function( table ) {
            table.addColumn( table.timestamp( "createdDate" ).default("getDate()") );
            table.addColumn( table.timestamp( "updatedDate" ).default("getDate()") );
            table.addColumn( table.timestamp( "lastLogin" ).default("getDate()") );
        } );
    } catch (any e) {}

}

function down( SchemaBuilder schema, QueryBuilder query ) {

    // schema.alter( "Galleries", function( table ) {
    //     table.dropConstraint( table.primaryKey() );
    // } );

   schema.dropIfExists( "Galleries",{}, true );
//    dump( pita.toSQL() )
//    abort;
    schema.alter( "Images", function( table ) {
        table.dropColumn( "galleryID" );
    } );
    schema.alter( "Users", function( table ) {
        table.dropConstraint( "df_Users_createdDate" );
        table.dropColumn( "createdDate" );
        table.dropConstraint( "df_Users_updatedDate" );
        table.dropColumn( "updatedDate" );
        table.dropConstraint( "df_Users_lastLogin" );
        table.dropColumn( "lastLogin" );
    } );

    // dump("wtf");abort;
}

}

2020_04_16_084055_updateGalleryData

component {

function up( schema, query ) {
    userList = query.newQuery().from( "users" )
            .orderBy("userID")
            .orderby("email")
            .get();

    lastEmail = "";
    currentUserID = 0;
    deleteList = [];

    for ( row in userList ){

        // If a user has multiple accounts currently, we're going to use the first one and delete the rest
        // We are sorted by email address so compare the last email with the current one to see if it's a duplicate
        if (lastEmail != row.email){

            // Fresh - init vars
            lastEmail = row.email;
            currentUserID = row.userID;

        } else {
            // Duplicate user. Add this ID to a list to delete when the updates are done
            deleteList.append(row.userID);
        }

        // Insert a new gallery record, returning the new galleryID for use in the next query
        // Each new gallery will be created with the currentUserID, AKA the first user account we came across
        newGalleryID = query.newQuery().from( "Galleries" )
                            .returning( "id" )
                            .insert({
                                "subdomain" = row.username,
                                "userID" = currentUserID,
                                "galleryName" = row.username,
                                "about" = ""
                            });

                            // dump(newGalleryID.query.ID);abort;

        // Update the images table, using the galleryID we just created.  
        // This gallery (new subdomain) belongs to a separate userID currently, though.
        // So we use the existing userID to find the right images to attach to the new gallery record.
        query.newQuery().from( "Images" )
                        .where("userID", row.userID)
                        .update({
                            "galleryID" = newGalleryID.query.ID
                        });


    } 
    // Delete the extra user records.
    // if (deleteList.len()){
    //     query.newQuery().from( "Users" )
    //                 .whereIn( "userID", deleteList )
    //                 .delete()
    // }

    // Clean up old columns
    // schema.alter( "Users", function( table ) {
    //     table.dropColumn( "username" );
    // } );
    
    // Must delete constraints before columns.
    // schema.alter( "Images", function( table ) {
    //     table.dropConstraint( "DF_images_userID" );
    // } );

    // schema.alter( "Images", function( table ) {
    //     table.dropColumn( "userID" );
    // } );

        // Users => Galleries => Images 
}

function down( schema, query ) {
    
}

}

2020_04_16_091359_changeIds

component {

function up( schema, query ) {
    schema.alter( "users", function( table ) {
        table.renameColumn( "userID", table.string( "id" ) );
    } );
    schema.alter( "images", function( table ) {
        table.renameColumn( "imageID", table.string( "id" ) );
    } );
    schema.alter( "images", function( table ) {
        table.renameColumn( "userID", table.string( "oldUser" ) );
    } );
}

function down( schema, query ) {
    try{
        schema.alter( "users", function( table ) {
            table.renameColumn( "id", table.string( "userID" ) );
        } );
        schema.alter( "images", function( table ) {
            table.renameColumn( "id", table.string( "imageID" ) );
        } );
        schema.alter( "images", function( table ) {
            table.renameColumn( "oldUser", table.string( "userID" ) );
        } );
    } catch (any e){}

}

}

[adael]

In my case, was because multiple migrations having same date in the name:

IE:
2021_04_28_170640_create_contact
2021_04_28_170640_create_post
2021_04_28_170640_create_contact_post

When running migrate down it tried to drop posts before contact_posts (which were not allowed by constraints)

Changing the date part in the migration filename, solved this error for me.

Your library is awesome BTW