We have moved to GitHub Issues
Created by Matthew Jones 16 Jul 2013, 12:00:41 Updated by Matt Barlow 10 Sep 2013, 20:49:18
Relates to: U4-1802
Relates to: U4-2542
I'm having issues when trying to import a package from one Umbraco 6.1.2 instance to another Umbraco 6.1.2 instance. The checkbox prevalues never get populated on my CMS content.
When I look through the log file of the site that I installed the package to I see the following error:
(Sorry if this is in the wrong place, this is the first issue I've submitted).
2013-07-16 12:54:10,522  ERROR umbraco.DataLayer.SqlHelper
1[[System.Data.SqlClient.SqlParameter, System.Data, Version=18.104.22.168, Culture=neutral, PublicKeyToken=b77a5c561934e089]] - [Thread 138] Error executing query Select [value] from cmsDataTypeprevalues where id in (Consultant,Team,District,Field) System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Field'. Invalid column name 'District'. Invalid column name 'Team'. Invalid column name 'Consultant'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter commandParameters) at umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper.ExecuteReader(String commandText, SqlParameter parameters) at umbraco.DataLayer.SqlHelper1.ExecuteReader(String commandText, IParameter parameters)
Here's some extra information to explain the package file that I've uploaded to reproduce the issue. The package file that I have attached contains the following:
A datatype called "OurWorld Roles List", this is a checkbox list and has 5 prevalues defined:
<DataType Name="OurWorld Roles List" (snip) DatabaseType="Nvarchar">
A document type called Base, this has a property called requiredRoles which is an "OurWorld Roles List".
A document type called SiteRoot, this inherits from Base.
A SiteRoot node called "Our World". This has This is defined in the package as follows:
<SiteRoot (snip) nodeName="Our World" (snip) nodeTypeAlias="SiteRoot">
As you can see, the requiredRoles are set as "Consultant, Team, District and Field", but when this package gets imported, none of these values are present in the node, and I get the related SQL exception in the umbraco log file.
This appears to be related to this issue (http://issues.umbraco.org/issue/U4-1802) that was listed as fixed in 6.0.1.
=Update: Reopened issue with simple repro steps=
Here are the steps to reproduce the issue.
Download the 6.1.2 Umbraco zip file from this url http://our.umbraco.org/ReleaseDownload?id=77775
Right click on the extracted folder and run it with web matrix, or point IIS at the folder.
Run through the install process and select the "I want to use SQL CE 4, a free, quick-and-simple embedded database" option.
Import the sample package attached to this issue.
Click on the Our World content node and look at the Roles tab. Refer to the images attached to this issue. If there are no boxes ticked like in "Expected (incorrect) result.jpg" then you have successfully recreated the issue. The boxes should be ticked as per "desired result.jpg".
Check the UmbracoTraceLog.txt file, if you have recreated the issue you should see the following lines. You can see from the trace log that our tick boxes were not checked because of a SQL error that matches the values that should have been ticked (i.e. Consultant,Team,District,Field).
2013-07-22 12:35:18,429  ERROR umbraco.DataLayer.SqlHelper`1[[System.Data.SqlServerCe.SqlCeParameter, System.Data.SqlServerCe, Version=22.214.171.124, Culture=neutral, PublicKeyToken=89845dcd8080cc91]] - [Thread 14] Error executing query Select [value] from cmsDataTypeprevalues where id in (Consultant,Team,District,Field) SqlCE4Umbraco.SqlCeProviderException: Error running Reader: SQL Statement: Select [value] from cmsDataTypeprevalues where id in (Consultant,Team,District,Field)
Exception: System.Data.SqlServerCe.SqlCeException (0x80004005): The column name is not valid. [ Node name (if any) = ,Column name = Consultant ] 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.ExecuteReader(CommandBehavior behavior) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlCeParameter commandParameters) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlCeParameter commandParameters) at SqlCE4Umbraco.SqlCEHelper.ExecuteReader(String commandText, SqlCeParameter parameters) at umbraco.DataLayer.SqlHelper`1.ExecuteReader(String commandText, IParameter parameters)
I've added the package that I was trying to install and the trace log. Please let me know if there's any other information you require to look into the issue.
This is turning into quite a show stopper for us now, because this makes the package utility useless for pushing content to live. All of the checkbox pre values get ignored, which is a fairly big deal.
I'm afraid I'm unable to reproduce your issue on a clean 6.1.2 site. I installed your package (awesome, thanks for that!) and everything looks fine (see attached screenshots).
The related error you get is interesting and I can fix that but it shouldn't be the cause of your problem, the package installer doesn't touch that method.
Thanks for replying so quickly Sebastiaan, I had been trying to fix this problem for quite a few days before submitting a bug report (even trying a clean Umbraco 6.1.2 install). I'll go away and try a clean install again and then get back to you.
Not trying to send you away, I just can't help at the moment! :-) Make sure to reopen the issue if you have good steps to repro!
Sebastian, what type of database are you using when trying to replicate our problems? We're using SQL server 2008 R2. I'm currently running tests to see if I can find a reason for you guys not being able to replicate our issue and I have a hunch that it could be down to us using a different type of database to you.
I'm usually using SQL CE but I checked in SQL2012 as well, just in case. It shouldn't make a difference in this case (and it didn't).
Hi Sebastian, I've reopened the issue along with some simple repro steps. I hope this helps.
Thanks Matthew, fixed for version 6.1.3 in revision 775892ff5a279cc05751099388c54be009b16626
Thanks Sebastiaan, that's great. I will look forward to 6.1.3 being released then! :-)
Have re-opened this for seb to review my notes since I think this change will cause unwanted side affects since the change is changing the way that values are persisted to cache, the underlying problem is probably due to the packager/importer to deal with the values differently.
Okay, I found the actual source of the problem. The Checkboxlist datatype stores the selected Ids on the content and in the database, BUT in the umbraco.config it will store the string values of the selected items. The new PackagingService didn't account for this in the CreateContentFromXml method, so it would store the string values in the database. I've put in a (admittedly hacky..) fix for now that works. Hopefully Shannon or Morten can pretty up and/or seperate out this code a little bit.
Backwards Compatible: True
Affected versions: 6.1.2
Due in version: 6.1.3