外键约束错误在执行新安装的Piranha CMS时。

Piranha CMS is a programmer oriented CMS (I prefer to write it since I've been downvoted last time!). I've spent all the afternoon trying to fix this problem : Whether I try to migrate from a Sql Compact Server Database to a SQL Server Database, or to start everything from scratch with an empty SQL Server Database, I get the following error :

Piranha CMS是一个面向程序员的CMS(我宁愿写它，因为我上次被否决了!)我花了整个下午试图解决这个问题:无论我是尝试从Sql Compact Server数据库迁移到Sql Server数据库，还是从头开始使用一个空的Sql Server数据库，我都会得到以下错误:

Introducing FOREIGN KEY constraint 'FK_dbo.category_dbo.sysuser_category_updated_by' on table 'category' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

引入外键约束'FK_dbo.category_dbo。sysuser_category_updated_by'在表'类别中可能导致循环或多个级联路径。在删除无操作或更新无操作时指定，或修改其他外键约束。

I am able to pass the first step (admin account and password creation), but this exception is thrown whenever I try to log in.

我可以传递第一步(管理帐户和密码创建)，但是每当我尝试登录时，都会抛出这个异常。

What am I doing wrong ?

我做错了什么?

Thanks for your help !

谢谢你的帮助!

UPDATE

更新

The stack indicates where the error is coming from :

堆栈指示错误来自何处:

[HttpException (0x80004005): The pre-application start initialization method 
Start on type Piranha.Web.ApplicationModule threw an exception with the following
error message: Introducing FOREIGN KEY constraint
 'FK_dbo.category_dbo.sysuser_category_updated_by' on table 'category' 
may cause cycles or multiple cascade paths. Specify ON 
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors..]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9915300
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101
System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, 
HttpContext context) +254

There seem to be a problem with the Method Start of Piranha.Web.ApplicationModule.

Piranha.Web.ApplicationModule的方法启动似乎有问题。

Also I have noticed the configuration of Entity Framework is :

我也注意到实体框架的配置是:

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>

I wonder about the :

我想知道:

invariantName="System.Data.SqlClient

Since System.Data.SqlClient is the Provider mentionned in my connection string. But I don't know if it's relevant since it may just mean that EF is used to retrieve the data. But I wonder if there might not be a conflict since both your internal scripts and EF use this connection string with this provider that is mentionned. Also, I'm not sure, but I know that with code-first EF also creates meta-data when it creates a database. Perhaps since your internal scripts doesn't create them, EF considers that the DB doesn't exist ?

因为System.Data。SqlClient是我的连接字符串中提到的提供者。但我不知道它是否相关，因为它可能意味着EF被用来检索数据。但是我不知道是否会有冲突，因为您的内部脚本和EF都使用这个连接字符串与这个提供程序(也就是上面提到的)。另外，我也不确定，但是我知道使用代码优先的EF在创建数据库时也会创建元数据。也许因为您的内部脚本没有创建它们，EF认为DB不存在?

Update 2

更新2

I have some news, I have added

“我有一些消息，”我补充说

Database.SetInitializer<Piranha.DataContext>(null);

In Application_Start to avoid Database recreation. It seems to have solved the database re-creation problem. I no longer see the exception concerning the constraint. However now the PageController throws a null reference exception after trying to log in, and I still can't see anypage of the public website (The publc site throws an internal server error). My guess is that when I set the Initializer to null, it also prevents EF from populating the DB with the basic data of the Framework. That's a good step further, so it seems that the problem comes from the Piranha DataContext Configuration.

在Application_Start中避免重新创建数据库。它似乎解决了数据库重新创建的问题。我不再看到关于约束的异常。然而，现在PageController在尝试登录后抛出了一个空引用异常，我仍然看不到公共站点的任何页面(publc站点抛出了一个内部服务器错误)。我的猜测是，当我将初始化设置为null时，它还会阻止EF使用框架的基本数据填充DB。这是一个很好的进一步，因此问题似乎来自Piranha DataContext配置。

Update 3

更新3

Just to give more details :

只是为了提供更多的细节:

When trying to log in, I'm redirected to manager/page which throws a null reference exception as I said before. This exception is thrown when I try to access most of the pages of the manager (I have tried template/pagelist, setting/userlist) however, when I try "manager/post" I get the following error :

当尝试登录时，我被重定向到manager/page，这将抛出一个空引用异常。当我尝试访问管理器的大部分页面时(我尝试过模板/页面列表、设置/userlist)，当我尝试“manager/post”时，会出现以下错误:

Invalid object name 'dbo.post'.  
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.post'.

But the 'post' Table do exist in the Database... It's a fresh install, I only changed two things : the connection string, and the Database Initializer code I added in Global Asax.

但是“post”表确实存在于数据库中……这是一个全新的安装，我只改变了两件事:连接字符串和我在全局Asax中添加的数据库初始化代码。

5 个解决方案

#1

Reason for error is Schema name in my case.

在我的例子中，错误的原因是模式名。

Solution: You require to have database objects belonging to dbo schema, if you've installed on fresh database with different schema name, it will throw this error. To fix it, change schema name of those tables to dbo and it will work like a charm.

解决方案:您需要拥有属于dbo模式的数据库对象，如果您已经安装在具有不同模式名称的新数据库上，它将抛出此错误。要修复它，请将这些表的模式名更改为dbo，它将发挥巨大的作用。

#2

As far as i can see, in version 2.2.0 there is not a constraint for table "category" with the name "FK_dbo.category_dbo.sysuser_category_updated_by". I guess something went wrong installing a fresh version.

就我所见，在2.2.0版本中，没有对名为“FK_dbo.category_dbo.sysuser_category_updated_by”的表“category”的约束。我猜在安装新版本时出了问题。

If you have access to SQL server with a management tool (like 'SQLServer Management Studio'), try to run to following query against your Piranha-database:

如果您可以使用管理工具(如“SQLServer management Studio”)访问SQL server，请尝试运行以下针对Piranha-database的查询:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='category' AND CONSTRAINT_TYPE='FOREIGN KEY'

You should see three constraints, none of them with the word 'sysuser' in its name.

您应该会看到三个约束，其中没有一个包含“sysuser”这个词。

You might want to try a fresh install again, following the instructions here: http://piranhacms.org/docs/introduction/getting-started

您可能希望再次尝试新的安装，遵循以下说明:http://piranhacms. org/docs/tion/getting-started

Before you proceed with the web installer, you should create a clean empty database using SQL Server Management Studio. You can run these SQL-commands in the Management Studio:

在继续执行web安装程序之前，应该使用SQL Server Management Studio创建一个干净的空数据库。您可以在Management Studio中运行这些sql命令:

-- create new user
CREATE LOGIN piranha2 WITH PASSWORD = 'piranha2' 
GO
-- create new empty database
CREATE DATABASE piranha2 
GO
-- select new database
USE piranha2
GO
-- assign user to database
CREATE USER piranha2 FOR LOGIN piranha2
GO
-- add role to user
EXEC sp_addrolemember 'db_owner', 'piranha2'
GO

After that, open Web.config, look for section "connectionStrings". You should see a couple of lines. Add a new line in this section with this content (don't forget to use your own servername or ip-address after "server="):

之后,开放网络。配置，查找“connectionStrings”小节。你应该看到几行。在本节中添加一行内容(不要忘记在“server=”之后使用自己的服务器名或ip地址):

<add name="piranha" connectionString="server=192.168.1.1;database=piranha2;user id=piranha2;password=piranha2;multipleactiveresultsets=true;" providerName="System.Data.SqlClient" />

Start the installer. It should find your database and create tables and constraints. Hope that will help.

启动安装程序。它应该找到您的数据库并创建表和约束。希望会有所帮助。

#3

The error message you're providing is a typical Entity Framework error when trying to create a DbContext in the database. The only problem is that Entity Framework is not responsible for creating the tables in Piranha CMS, this is done by internal SqlScripts embedded in the framework dll.

在尝试在数据库中创建DbContext时，您提供的错误消息是一个典型的实体框架错误。唯一的问题是实体框架不负责在Piranha CMS中创建表，这是由嵌入在框架dll中的内部SqlScripts来完成的。

If the tables are created successfully in the database and you still get this error when trying to log in, my only guess is that Entity Framework is for some reason using an incorrect Connection String trying to create the database somewhere else. Although I'm puzzled how this could happen through misconfiguration.

如果在数据库中成功创建了表，并且在尝试登录时仍然会出现此错误，我唯一的猜测是，实体框架出于某种原因使用了错误的连接字符串试图在其他地方创建数据库。尽管我很困惑，这是怎么通过配置错误发生的。

Can you access the public site after install?

你能在安装后进入公众网站吗?

UPDATE

更新

I've created a new Blank MVC Application, installed PiranhaCMSMvc into it, installed the database and logged into the manager without problems. I've pushed the blank project to this repo:

我创建了一个新的空白MVC应用程序，在其中安装了PiranhaCMSMvc，安装了数据库，并毫无问题地登录到管理器中。我把这个空白项目推到这个repo:

https://github.com/tidyui/Blank

https://github.com/tidyui/Blank

Download this source code and see if it works for you after you've modified the connection string. If it works, try to compare the projects to see what is causing your error. If it doesn't work then there's something else wrong with your setup that is conflicting somehow. Keep me updated!

下载这个源代码，看看它在您修改了连接字符串之后是否适用。如果它有效，尝试比较项目，看看是什么导致了您的错误。如果它不工作，那么您的设置有其他的问题，在某种程度上是冲突的。让我更新!

Regards

问候

#4

Well, I spent 2 days on it so I had no choice but to change CMS. I'm running late because of this problem. Fortunately I only had created a couple of pages. I'm currently moving to Umbraco. I've played with it this afternoon and it's actually less complicated than what I heard so it should do the trick. It's also perfectly working on my production server.

我花了两天的时间，所以我别无选择，只能更换CMS。因为这个问题，我快迟到了。幸运的是，我只创建了几页。我现在要搬到翁布洛。我今天下午玩过它，它实际上比我听到的要简单一些，所以它应该是有用的。它在我的产品服务器上也运行得很好。

However, I still have an other project that I would like to keep on Piranha CMS. Since I've planned to move it from Compact SQL to SQL Server too, I'm really concerned with this issue.

不过，我还有一个项目想继续使用Piranha CMS。因为我已经计划将它从紧凑的SQL迁移到SQL Server，所以我非常关心这个问题。

#5

I cannot add a comment yet, so have to give a second answer: My previous answer didn't make sense so i have to make something up with you. If you still want to investigate some time: to re-produce your problem i've created a VHD with Windows 7 Pro-N (un-activated), MS SQL 2012 Express and VS 2013 Express Web. You can run this as virtual machine either under Windows 7 Virtual PC or Windows 8 Hyper-V. With the Blank project of Hakan I was not able to reproduce your problem. However, if you manage to re-produce your problem in this virtual machine, you can share the VHD with Hakan so he can take a look what is happening (of course if he's willing to do so). Also, what i'm wondering: if you rename the affected table or drop it, is the error-message moving to another table and/or constraint? Of course it is not a solution to drop the table, but it might tell more about what is going on.

我还不能添加评论，所以必须给出第二个答案:我之前的回答没有意义，所以我得跟你说点什么。如果你还想调查一下:为了重现你的问题，我用Windows 7 Pro-N(未激活)、MS SQL 2012 Express和VS 2013 Express Web开发了一款VHD。您可以在Windows 7虚拟PC或Windows 8 Hyper-V下运行它作为虚拟机。由于Hakan的空白项目，我无法重现你的问题。但是，如果您设法在这个虚拟机中重新生成问题，您可以与Hakan共享VHD，这样他就可以查看正在发生的事情(当然，如果他愿意的话)。另外，我想知道的是:如果重命名受影响的表或删除它，错误消息是否会转移到另一个表和/或约束?当然，这并不是一个解决方案，但它可以告诉我们更多的事情。