Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Ji Pattison-Smith 3 posts 74 karma points
    Jan 31, 2019 @ 13:39
    Ji Pattison-Smith
    0

    Invalid column name 'controlId' on upgrade

    I'm trying to upgrade a project from v7.12.3 to v7.13.1, but I keep getting this error:

    Invalid column name 'controlId'
    

    The strange thing is, I upgraded successfully in my local environment, but on deployment to the live environment it caused this error, and I had to roll the code back.

    Having followed the stack trace into the source code, it looks like what it's trying to do is a major version upgrade to v7, but clearly it shouldn't be, so it must be getting the wrong version number from somewhere. This site was built in Umbraco 7, and I've double checked the umbracoMigration table and the only upgrades are from older versions of 7.

    I've had issues upgrading in the past, but they've generally been solved by some combination of clearing browser cookies/cache and clearing out temp folders on the Umbraco install.

    I've also tried setting up a brand new site on the server with a copy of the live database and the upgraded code (with the old version number in web.config) and still get the same error.

    I've scoured Stack Overflow and these forums, but haven't come across a solution that works for me. I'd really appreciate any assistance anyone can provide.

  • Ji Pattison-Smith 3 posts 74 karma points
    Feb 02, 2019 @ 14:34
    Ji Pattison-Smith
    1

    I've still not sussed this, but I have made some progress. I realised then when I get the error, it doesn't ask me to log in to Umbraco (even with cookies cleared, private window), which is strange. The install always asks for a login in my experience, and did when completing the successful local upgrade.

    I found that if I connect to the remote database from my local code, I get the same error.

    If I dump the remote database and restore it locally, I'm not even able to log in. But some of the table schemas are not dbo. The following tables have a schema matching the database user: cmsMedia, umbracoAudit, umbracoConsent, umbracoUser2UserGroup, umbracoUserGroup, umbracoUserGroup2App, umbracoUserGroup2Node, umbracoUserLogin, umbracoUserStartNode.

    If I alter these tables to have the dbo schema, suddenly the upgrade works! So I have a potential path for getting an upgraded site working remotely now, but I'm still baffled at why this is happening, and how I can stop it from happening in future, so if anyone has any ideas I'd love to hear them :)

  • Ji Pattison-Smith 3 posts 74 karma points
    Feb 27, 2019 @ 13:33
    Ji Pattison-Smith
    0

    Just in case anyone else has this issue, my solution was to switch the site into maintenance mode, bring the live database down to dev, alter the offending tables to use dbo schema, run the upgrade, and then replace the live database with my upgraded one. Quite a faff, but it's done the job. Now to see if it keeps happening that way...

  • Zac 232 posts 511 karma points
    May 22, 2019 @ 04:28
    Zac
    0

    We ran into the same issue in a shared hosting environment. We were able to resolve this by downloading the db, upgrading Umbraco locally, then uploading the db and files.

  • Matthew Kirschner 323 posts 605 karma points
    May 31, 2019 @ 15:09
    Matthew Kirschner
    0

    I can confirm that this worked for me. No need to put the site into maintenance mode.

  • Chris Thwaites 7 posts 90 karma points
    Aug 01, 2019 @ 07:48
    Chris Thwaites
    0

    It's not just the tables that have to be in the dbo schema.

    You also need access to some of the other admin features of the SQL Server. Possibly the some sys. views in the master DB?

    I'm not sure what exactly, but I know I couldn't upgrade on a hosted SQL server where I do not have admin access, even though all the tables were in the accessible dbo schema. I would get the "Invalid column name 'controlid'" error, even on a clean, empty umbraco install upgrade from 12.4 to 13.0.

    Copying the DB down to a SQL server where I had admin access to the master db, changing the umbraco connection string in web.config, upgrading, and then copying the upgraded DB back up to the hosted server worked for me.

  • Simon Dingley 1405 posts 3259 karma points c-trib
    Aug 07, 2019 @ 07:48
    Simon Dingley
    0

    I've just hit this same issue but your solution is not an option for me as the site is load balanced and I can't afford the downtime to upgrade the database offline.

    What's odd is that the upgrade worked elsewhere.

  • Simon Dingley 1405 posts 3259 karma points c-trib
    Aug 07, 2019 @ 08:22
    Simon Dingley
    0

    This is a pretty nasty issue which I need to investigate as something must have changed that I missed. In the past I used to sometimes have the opposite problem whereby new tables would be created with the database user schema as the owner and upgrades would fail but this is the other way around. The upgrade fails because the tables are owned by the dbo schema and not the database user.

    In case it helps anyone else here is a script to allow you to iterate over all of your tables to change the owner:

    DECLARE @old sysname, @new sysname, @sql varchar(1000)
    
    SELECT
      @old = 'dbo'
      , @new = 'XXXX_DBUSR2'
      , @sql = '
      IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
      WHERE
          QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
          AND TABLE_SCHEMA = ''' + @old + '''
      )
      EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
    
    EXECUTE sp_MSforeachtable @sql
    
  • Craig100 980 posts 2113 karma points c-trib
    Sep 24, 2019 @ 21:05
    Craig100
    0

    I just had this issue and found it was due to having put the wrong version number in web.config. I was doing a set of rapid DB updates in a sequence to the live version of the site database having done the main file work on an old version.

    The error was "Invalid column name 'controlId'"

    Easy done!

Please Sign in or register to post replies

Write your reply to:

Draft