U4-3876 - MySQL cmsContentXml Migration Error on 7.0.1 Upgrade

For MySQL, collapsing the DELETE query to eliminate the subquery gets things working. I tested this query on MySQL 5.5: DELETE cmsContentXml.* FROM cmsContentXml INNER JOIN umbracoNode ON cmsContentXml.nodeId = umbracoNode.id WHERE nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C' AND path like '%-21%'

I haven't tested on MSSQL, but I presume it should work there.

I tested the fix on MySQL and it works there. I don't have a MSSQL instance to test.

I submitted Pull Request 271 with my fix. https://github.com/umbraco/Umbraco-CMS/pull/271

Thanks! I'll have to look at how we can get this working consistently across both platforms, unfortunately that query doesn't work with MSSQL :(

Can you give this a try on MySQL:

Delete a 
from cmsContentXml a
INNER JOIN umbracoNode b
on  a.nodeId = b.id
WHERE nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C' AND path like '%-21%'

That works on MSSQL

FYI We have this same issue in other places:

ContentService.RebuildXmlStructures MediaService.RebuildXmlStructures

@Shannon: In that case, maybe the best option is to check with DBMS is in use and run whichever query is appropriate for that type? It seemed like there were limited other options for MySQL and they were all pretty kludgy.

@Shannon: Your query ran fine on MySQL.

awesome, will update the other queries that have sub queries. thanks for your help (will close the PR)

I'll also need to fix this up in my new member service changes

Arghh! unfortunately that query doesn't work with sqlce! I have MySql installed now so will see what works on all of them.

strangely i cannot get that to work in mysql either

haha... apparently you can just trick mysql :) see: http://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition with the +30 answer

Ok, that query works on all 3 servers:

DELETE FROM cmsContentXml WHERE nodeId IN
	(SELECT nodeId FROM (SELECT DISTINCT cmsContentXml.nodeId FROM cmsContentXml 
	INNER JOIN umbracoNode ON cmsContentXml.nodeId = umbracoNode.id
	WHERE nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C' AND [path] LIKE '%-21%') x)

of course for mysql you need to change the [path] to be path. Now to update the rest of the places this query type is used.