Copied to clipboard

Flag this post as spam?

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


  • Matt Parkinson 12 posts 65 karma points
    Jan 23, 2014 @ 18:32
    Matt Parkinson
    0

    Upgrading from v4.7.1.1 to v7.0.2 database schema validation

    Hi,

    I've been trying to upgrade from v4.7.1.1 to v7.0.2 for the last few days, but keep getting stuck at 90%, I've tried various approaches (including upgrading to v6.1.6 first, which was successful) and have looked at other related forum posts but can't find any way to successfully complete the upgrade process, the latest error I'm receiving is below and the trace log is below, please help!

    error

    2014-01-23 16:50:06,959 [23] INFO Umbraco.Core.DatabaseContext - [Thread 8] The database schema validation produced the following summary: The following tables were found in the database, but are not in the current schema: cmsMacroPropertyType,fullTextCache

    The following columns were found in the database, but are not in the current schema: cmsContentTypeAllowedContentType,sortOrder,cmsDataType,controlId,cmsMacroProperty,macroPropertyHidden,cmsMacroProperty,macroPropertyType,cmsMacroPropertyType,id,cmsMacroPropertyType,macroPropertyTypeAlias,cmsMacroPropertyType,macroPropertyTypeRenderAssembly,cmsMacroPropertyType,macroPropertyTypeRenderType,cmsMacroPropertyType,macroPropertyTypeBaseType,cmsTags,parentId,fullTextCache,Id,fullTextCache,nodeId,fullTextCache,fullHTML,umbracoUser,userDefaultPermissions,umbracoUser,defaultToLiveEditing

    The following constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema: FKcmsContentumbracoNode,FKcmsContentTypeumbracoNode,FKcmsContentVersioncmsContent,FKcmsContentXmlcmsContent,FKcmsDataTypeumbracoNode,FKcmsDataTypePreValuescmsDataType,FKcmsDocumentcmsContent,FKcmsDocumentcmsTemplate,FKcmsDocumentumbracoNode,FKcmsDocumentTypecmsContentType,FKcmsDocumentTypecmsTemplate,FKcmsDocumentTypeumbracoNode,FKcmsLanguageTextcmsDictionary,FKcmsMacroPropertycmsMacro,FKcmsMacroPropertycmsMacroPropertyTypeid,FKcmsMembercmsContent,FKcmsMemberumbracoNode,FKcmsMember2MemberGroupcmsMember,FKcmsMember2MemberGroupumbracoNode,FKcmsMemberTypecmsContentType,FKcmsMemberTypeumbracoNode,FKcmsPreviewXmlcmsContent,FKcmsPreviewXmlcmsContentVersion,FKcmsPropertyDatacmsPropertyType,FKcmsPropertyDataumbracoNode,FKcmsPropertyTypecmsContentType,FKcmsPropertyTypecmsDataType,FKcmsPropertyTypecmsTab,FKcmsPropertyTypeGroupcmsPropertyTypeGroup,FKcmsStylesheetumbracoNode,FKcmsStylesheetPropertyumbracoNode,FKcmsTabcmsContentType,FKcmsTagRelationshipumbracoNodeid,FKcmsTaskcmsTaskType,FKcmsTaskumbracoNode,FKumbracoDomainsumbracoNode,FKumbracoNodeumbracoNode,FKumbracoRelationumbracoRelationType,FKumbracoUser2appumbracoUser,FKumbracoUser2NodeNotifyumbracoNode,FKumbracoUser2NodeNotifyumbracoUser,FKumbracoUser2NodePermissionumbracoNode,FKumbracoUser2NodePermissionumbracoUser,FKuseruserType,PKcmsContentVersio060DEAE8,PKcmsContentPreviewXml,PKcmsTab,PKdomains,PKfullTextCache,PKlanguage,PKmacro,PKmacroProperty,PKmacroPropertyType,PKtemplates,PKuserType,IXcmsContentVersion,IXcmsDataType,IXcmsDictionary,IXcmsTaskType,IXcmsTemplate,IXumbracoLanguage,IX_umbracoUser

    The following unknown constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema: cmsTagscmsTagRelationship,umbracoNodecmsTagRelationship

  • Dennis Aaen 4500 posts 18255 karma points admin hq c-trib
    Jan 23, 2014 @ 19:07
    Dennis Aaen
    0

    Hi Matt,

    You have been able to upgrade to 6.1.6 successfully, but then it went wrong. I was thinking have you seen these upgrade guides for upgrading from a version 6 to 7.

    http://our.umbraco.org/documentation/installation/upgrading/v7-upgrade

    http://our.umbraco.org/documentation/installation/upgrading/version-specific

    I hope this can help you solve your issue upgrading to version 7.

    /Dennis

  • Matt Parkinson 12 posts 65 karma points
    Jan 23, 2014 @ 20:13
    Matt Parkinson
    0

    Hi Dennis,

    Thanks for replying, yep I've been using these two pages as a reference for my many attempts. I've created a process to copy the database structure and data before upgrading, do you know if there is any way to upgrade the database another way without using the normal upgrade screens?

    Thanks

  • Martin Griffiths 826 posts 1269 karma points c-trib
    Jan 24, 2014 @ 10:57
    Martin Griffiths
    100

    Hi Matt

    I've been working on a large web CMS project since the lofty days of Umbraco 4.0.4.2. In the last 3 years I've managed to steer our sites through the XML schema change which included a total nightmare scenario of XSLT changes, despite the tools made available (third party, nothing from the core team). Since then we've moved from 4.7.2 to 4.11.10, I patched Umbraco 4 source code all the way to ensure Courier 1.3 continued to run as we had a staging environment our marketing team used regularly. Once we started to look at Umbraco 6 and onwards it was decided to dump Courier altogether due to my various failed attempts at getting Courier 2 working reliably.

    Since Umbraco 7 was released I've taken a copy of our live site and attempted to upgrade. Now if like me you have a legacy of code and databases to deal with you WONT find the upgrade process smooth at all, despite various suggestions to the contrary!

    Most significantly and in your case the mention of database key changes below..

    http://our.umbraco.org/documentation/installation/upgrading/v7-upgrade

    I've raised the issue on the following bug item here...

    http://issues.umbraco.org/issue/U4-3616

    Unfortunately for some reason it has largely been treated as the end-users responsibility to sort out!

    So the way I managed to fix it was to download the Microsoft SQL Server data tools for Visual Studio. This will allow you to do a schema compare of your current database against a clean new install of Umbraco 7. The tool will build a complete list of the differences and allow you to selectively move them from source to target. Peasy....but very annoying I had to do it....But that's open source (and Umbraco) for you! It should be fixed in the installer, but the issue has so far been ignored.

    The single biggest bugbear for me over the last 3 years of upgrading our projects has been the lack of QA to ensure users aren't left with a multitude of hurdles to jump over to get the next version working correctly and/or easily.

    On the code front, I'm glad I held back on Razor macros because was with every release of Umbraco, something changed in the implementation! Then Umbraco 5 failed as project with the best features being back peddled into 4 and eventually becoming a mature 6! 

    I was approached by our marketing team in the last few months to incorporate a responsive web design into our site to ensure mobile compatibility and it was from here I made the swtich to full MVC, in fact I don't use any razor macros at all, just views and partials. But we still have a whole ton of legacy XSLT to convert! At least that still runs ok!

    All of our user controls and code which hit Umbraco APIs have been upgraded/replaced to work with Umbraco 6/7. It's been a huge effort that hopefully will serve us well into the next few versions of Umbraco.....we'll see!

    Good luck 

    Martin.

  • Matt Parkinson 12 posts 65 karma points
    Jan 24, 2014 @ 11:41
    Matt Parkinson
    0

    Hi Martin,

    Thanks for your reply, although some of your comments are negative it gave me a great incite into your project obstacles, which was very interesting to read, I've been working on this project for the last 6 months which I took over from anther developer, there isn't much legacy code (that I'm aware of) apart from some XSLT, which I'm not looking forward to fixing (when they break) and also don't use any packages, like you we've not got any Razor macro's and we're not using MVC currently, I'd like your to know your thoughts on converting to MVC, if you have the time to reply?

    I going to try Brian's suggestion next as I think this could be good suggestion and then I'll try using Microsoft SQL Server data tools for Visual Studio, if all else fails.

    I ended up taking a new 6.1.6 database, exporting its schema, then importing the data from my upgraded-to-6.1.6 database into that.

    Thanks,

    Matt

  • Martin Griffiths 826 posts 1269 karma points c-trib
    Jan 24, 2014 @ 12:07
    Martin Griffiths
    0

    Hi Matt

    Yea sorry it was quite negative, I plan to do a big post on DO's and DON'Ts based on my experiences over the last few years. Whilst i've had quite an uphill struggle with many aspects of Umbraco, it's still an awesome CMS.

    This is how I approached our MVC conversion...

    1. Switch on MVC, but retain webforms compatibility during the conversion effort. There are two switches in the umbracosettings.config.

    2. Convert Webforms based masterpages (templates) to MVC views and nested views with @RenderSection (basically the same as nested masterpages)

    3. Convert Usercontrols to MVC Partials

    4. Upgrade any custom dll code calling Umbraco APIs to latest codebase (currently 6)

    5. Look at any quick wins in XSLT code and covert to partials (you could use razor macros). The rest call with @Umbraco.RenderMacro.

    6. One by one, switch out your templates in your doctypes.

    Some overall, upgrade recommendations....

    1. Keep package extension usage to an absolute minimum, all but the most active packages will hold you up otherwise. We've used Courier and Contour from the start, but as I said before, Courier is too unreliable for production use sadly. i've looked with great interest at uSync, but i'm waiting on a version for Umbraco 7. I had to resort to quite a big one, uBlogsy because our marketing dept. wanted a blog! Thankfully this one is well supported, but i've got to do an MVC upgrade of that soon.

    2. Source control for your code! My code is regularly probed and tweaked by other team members.

    3. Use Visual Studio, and Intellisense your code. Makes life a lot easier. We have quite a big Umbraco VS project now that pulls in all of the necessary DLLs to get full code markup and intellisense. We run build events to test locally.

  • Matt Parkinson 12 posts 65 karma points
    Jan 24, 2014 @ 18:10
    Matt Parkinson
    2

    Hi Martin,

    It's working!

    I did what you said:

    So the way I managed to fix it was to download the Microsoft SQL Server data tools for Visual Studio. This will allow you to do a schema compare of your current database against a clean new install of Umbraco 7. The tool will build a complete list of the differences and allow you to selectively move them from source to target. Peasy....but very annoying I had to do it....But that's open source (and Umbraco) for you! It should be fixed in the installer, but the issue has so far been ignored.

    Thanks for the MVC information, I'm sure this will come in handy in the future.

    For anyone reading this with the same problem, my advise would be to use the below process to upgrade to version 6 first:

    1. Create a copy of your existing Umbraco database.
    2. Create a new website in IIS.
    3. Publish your current solution to the new site location.
    4. Overwrite with the new Umbraco files (Bin, Install, Umbraco and Umbraco_Client).
    5. Merge the Config files or copy from new Umbraco if they don't exist.
    6. Change the connection strings in your web.config to your new database.
    7. Delete the contents of App_Data (this might not be for everyone, it depends on your project configuration)
    8. Browse to your newly created IIS site.
    9. Follow the upgrade process.

    Then once version 6 has upgraded successfully, follow the below to upgrade to version 7:

    1. Install version 7 as a separate website to create a bare bones database.
    2. Using SQL Server Data Tools for Visual Studio, compare your database schema and upgrade the first database (with your existing data) so it matches the new v7 bare bones database.
    3. Change the web.config node umbracoConfigurationStatus to the new version 7 (7.0.2)
    4. Browser to your new version 7 site, this may now take about 10 minutes to load, you can check the progress by looking in the UmbracoTraceLog.txt located here: App_Data\Logs\

    Hope this helps anyone else who has problems upgrading to version 7.

    Matt

  • Craig100 1136 posts 2523 karma points c-trib
    Feb 04, 2014 @ 11:49
    Craig100
    0

    I've come across a similar problem on my first foray into V7. A client has a family history site in V4.7.0 Webforms and is desperate to have it in V7 and won't wait till V7.1 comes out, so it's to be V7.0.3. My idea is to create a blank install, then re-code everything to MVC and razor. Luckily there aren't many docTypes. However, what I don't want to do is to copy & paste the content of 172 long and detailed pages, all built in the RTE and have to re-attach all the images. Is there a sensible way to export just the content and media from the V4.7.0 site/DB and import into the V7.0.3 site/DB? I've seen uSync (not actually used it) and not sure if it's the right tool or if it is, if it's V7 valid yet.

    Any advice appreciated.

    Craig

  • Martin Griffiths 826 posts 1269 karma points c-trib
    Feb 04, 2014 @ 13:15
    Martin Griffiths
    0

    Hi Craig

    The two options Courier 2 & uSync content edition dont work in Umbraco 7. Courier 2 is a VERY flaky product (from a lot of experience) particularly when used with sites that have gone through a number of upgrades. I can promise you, you'll spend a lot of wasted time getting very annoyed with big red box errors.

    Your best option is to continue through the upgrade path up to 7 and turn on MVC. You can then continue to run webforms/user controls side-by-side with anything new you create in MVC.

    Any existing XSLT can be added to MVC views but usercontrols will need to be refactored to partials or razor macros.

    Kind regards

    Martin

  • Craig100 1136 posts 2523 karma points c-trib
    Feb 04, 2014 @ 13:27
    Craig100
    0

    Thanks Martin but I don't intend to go through an upgrade path, I'd rather pull my head off! That's why I'm going for a re-build in MVC but want to see if I can get just the content and possibly the media over without getting an RSI, which is the only other option.

    Cheers,

    Craig

  • Martin Griffiths 826 posts 1269 karma points c-trib
    Feb 04, 2014 @ 13:37
    Martin Griffiths
    0

    Hi Craig

    Pull your head off maybe, but at the moment it's your only option! I'd rather do that than manually copy and paste and re-link a whole ton of documents!

    I sympathise with you, upgrading is never easy with Umbraco. There's always something that rears up and gives you a headache!

    When we upgraded our site we went for MVC in v6.x. I was very surprised how well the two tech's happily run side by side. It means you can rewrite and test your new MVC stuff and whilst keeping all existing site structure intact. Then all you need to do is switch over your templates.

    Regards

    M.

  • Orbcom 10 posts 80 karma points notactivated
    Mar 18, 2020 @ 11:14
    Orbcom
    0

    For me the solution was very simples. The page simply timedout on 90%. So a page refresh showed me a 100% complete operation.

    Hope it helps

  • Wojciech Tengler 96 posts 202 karma points
    Sep 14, 2020 @ 08:43
    Wojciech Tengler
    0

    I have just similar problem with schema validation durring upgrade 6 to 7.

    I can't understand why schema is not valid because I executed queries like this:

    SELECT * FROM INFORMATION_SCHEMA.TABLES
    
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    
    SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    

    and see everywhere dbo schema.

    I use db login in connection string which has configured dbo as default schema.

    So what schema Umbraco expects?

    What differences have you noticed durring yours comparisons in SQL tools?

    Umbraco uses method like this in validation process:

    public override IEnumerable<string> GetTablesInSchema(Database db)
    {
        var items = db.Fetch<dynamic>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = (SELECT SCHEMA_NAME())");
        return items.Select(x => x.TABLE_NAME).Cast<string>().ToList();
    }
    

    When I execute the same query in SQL Server management studio for the same db login I get all tables for dbo.

    Does Umbraco change to another schema for login?

Please Sign in or register to post replies

Write your reply to:

Draft