Copied to clipboard

Flag this post as spam?

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


  • Andy Felton 140 posts 407 karma points c-trib
    Oct 18, 2017 @ 09:51
    Andy Felton
    0

    Issues with Umbraco 7.6 and 301 URL Tracker

    Hi,

    I'm just in the process of upgrading a site from Umbraco 5.5.7 and was having trouble going straight to 7.7.3 so went to 7.6.0 first.

    When I upgrade to 7.6.0 with URL Tracker 3.13.1 I'm getting the following issue:

    Error running NonQuery: 
    SQL Statement:
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'icUrlTracker') AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'icUrlTracker' AND COLUMN_NAME = 'ForceRedirect') BEGIN     ALTER TABLE [icUrlTracker] ADD ForceRedirect bit NOT NULL CONSTRAINT DF_icUrlTracker_ForceRedirect DEFAULT 0 END
    
    Exception:
    System.Data.SqlServerCe.SqlCeException (0x80004005): There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]
      at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
      at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
      at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
      at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
      at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteNonQueryTry(SqlCeConnection conn, SqlCeTransaction trx, String commandText, SqlCeParameter[] commandParameters)
      at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteNonQuery(SqlCeConnection conn, SqlCeTransaction trx, CommandType commandType, String commandText, SqlCeParameter[] commandParameters)
    

    I've seen similar notes on the forum going back to 2014 but I'm guessing that was with an older version of URL Tracker. Is there any solution to this issue at the moment?

    Many Thanks

    Andy

  • Daniel Chenery 119 posts 462 karma points
    Oct 18, 2017 @ 10:16
    Daniel Chenery
    0

    Hmm, it looks like it doesn't like the word "IF", if I'm reading correctly (no pun intended)

    Do you have the "ForceRedirect" column on your database?

    If not, you could try running this command manually against it.

    ALTER TABLE [icUrlTracker] ADD ForceRedirect bit NOT NULL CONSTRAINT DF_icUrlTracker_ForceRedirect DEFAULT 0

    I'm not too sure if will get you around this issue though, since in theory it should still run the update, but worth a shot.

    Obviously, do backup your database before executing this command.

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 18, 2017 @ 10:59
    Andy Felton
    0

    Hi Daniel,

    Thanks for the super quick response!

    Just checked the SQL CE DB and yes it has the ForceRedirect column in the database.

    Cheers Andy

  • Daniel Chenery 119 posts 462 karma points
    Oct 18, 2017 @ 13:29
    Daniel Chenery
    0

    Hmm. So it looks like the update has been successful.

    When exactly does the error come up?

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 18, 2017 @ 15:05
    Andy Felton
    0

    Hi,

    Just checked the live SQL CE database and that also has the ForceRedirect column so I think this is failing on the check to see if it needs upgrading.

    The version prior to the upgrade is 3.12.43 and after upgrade would be 3.13.1.

    The error is happening when the site first loads and prior to Umbraco going to it's upgrade DB screen.

    Thanks

    Andy

  • Daniel Chenery 119 posts 462 karma points
    Oct 18, 2017 @ 15:30
    Daniel Chenery
    0

    You were running 3.12.43 previously? There are no database updates between 3.12 and 3.13

    You said you're running SQL CE? Oddly enough, from what I can see the line throwing the error is an update for non-CE database's

    Is your connection string all correct?

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 18, 2017 @ 15:39
    Andy Felton
    0

    How odd!

    Downgraded earlier and brought the site backup so connection string was correct prior to upgrade.

    Guess there could be an issue in that version of Umbraco with SQL CE, oddly it does seem to think it's SQL CE on the exception:-(

  • Daniel Chenery 119 posts 462 karma points
    Oct 18, 2017 @ 15:54
    Daniel Chenery
    0

    What's the provider name on your connection string?

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 18, 2017 @ 15:58
    Andy Felton
    0

    System.Data.SqlServerCe.4.0

  • Daniel Chenery 119 posts 462 karma points
    Oct 18, 2017 @ 16:03
    Daniel Chenery
    0

    Is that once upgraded to 7.6?

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 18, 2017 @ 16:16
    Andy Felton
    0

    Yes it is.

  • Daniel Chenery 119 posts 462 karma points
    Oct 18, 2017 @ 16:22
    Daniel Chenery
    0

    I'm stumped as to how it's getting the wrong update script 🤔

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 19, 2017 @ 10:57
    Andy Felton
    0

    Hi Dan,

    I'll fork your code tomorrow and try and step through it.

    Thanks Andy

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 20, 2017 @ 09:23
    Andy Felton
    0

    Now this is even more confusing.

    If I build a debug version of the DLL all works nicely, build a release version and it fails on exactly the same thing as before.

    I've got to move to something else for the rest of the day, but will try and look again over the next few days.

  • Daniel Chenery 119 posts 462 karma points
    Oct 20, 2017 @ 09:31
    Daniel Chenery
    0

    Hi Andy,

    That certainly adds to the confusion. Do the URLs redirect when built against the debug version?

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 21, 2017 @ 12:03
    Andy Felton
    0

    Hi,

    Think I've worked this out now. Basically looks like a timing issue in the Umbraco source. If you look at Umbraco.Core.DatabaseContext then the DatabaseProvider function:

     public virtual DatabaseProviders DatabaseProvider
        {
            get
            {
                string dbtype = Database.Connection == null ? ProviderName : Database.Connection.GetType().Name;
    
                if (dbtype.StartsWith("MySql")) return DatabaseProviders.MySql;
                if (dbtype.StartsWith("SqlCe") || dbtype.Contains("SqlServerCe")) return DatabaseProviders.SqlServerCE;
                if (dbtype.StartsWith("Npgsql")) return DatabaseProviders.PostgreSQL;
                if (dbtype.StartsWith("Oracle") || dbtype.Contains("OracleClient")) return DatabaseProviders.Oracle;
                if (dbtype.StartsWith("SQLite")) return DatabaseProviders.SQLite;
                if (dbtype.Contains("Azure")) return DatabaseProviders.SqlAzure;
    
                return DatabaseProviders.SqlServer;
            }
        }
    

    In debug mode the Database.Connection is null and therefore uses ProviderName which is SQLCE. With the Release version of URL Tracker it looks like you call this after the DB Connection is set up, this then uses Database.Connection.GetType().Name which actually returns the name of the Class of Database.Connection which is ProfiledDBConnecton and therefore the code defaults to returning the Provider as SQL Server.

    As I said I suspect this has come to light due to a timing issue, but looks like a bug with the Umbraco Code.

    I'll raise an issue on Umbraco.

    Thanks for the help.

    Andy

  • Andy Felton 140 posts 407 karma points c-trib
    Oct 21, 2017 @ 12:12
    Andy Felton
    1
Please Sign in or register to post replies

Write your reply to:

Draft