U4-2209 - 6.0.5 - (Only on MySQL) - Empty recycle bin DELETES all content

Created by Jan Hansen 10 May 2013, 11:46:21 Updated by Brian Powell 20 May 2013, 16:44:32

Relates to: U4-2152

I've created a completely fresh 6.0.5 installation, created a document type with an according template, created 4 pages in the content tree and then delete two of them.

If I right click on the Recycle Bin, and select the "Empty recycle bin" node, a dialog appears, which I confirm and then the progress bar just keeps showing while nothing actually happens. A lot of ajax calls to get trash can status are performed, but the opreration never finishes. If the delete dialog is closed and the page is refreshed, every content node EXCEPT the ones in the recycle bin are deleted.

This is really critical - could someone please look into this?

We are using MySQL 5.0 as databaseengine.

3 Attachments

Download U4-2209.zip


Brian Powell 11 May 2013, 21:26:39

I am seeing the same problem with Umbraco 6.0.5 and MySQL 5.5.24. Emptying the Recycle Bin on the Content side deletes all my pages. On the Media side, it deletes at least all my recent media items (including physical files on disk.)

Morten Christensen 12 May 2013, 04:09:27

Wow, sounds like something is going horribly wrong. For items that are in the recycle bin can I please get you to check the database to verify whether these nodes has trashed = true ? The only thing that has changed is that we use the 'trashed' bool instead of the 'path' (with recycle bin as parent/ancestor) to clear out the recycle bin.

Brian Powell 12 May 2013, 04:23:00

In the umbraconode table, the items in my Recycle Bin are trashed = 1. Everything else is trashed = 0. Maybe there is something goofy with how the bool is being interpreted since MySQL doesn't have true boolean fields?

Jan Hansen 13 May 2013, 07:37:33

"The only thing that has changed..." is that we now detect deleted items in a completely new way. Did you consider testing the change on anything else but MSSQL server? MSSQL and MySQL handles boolean database values/notations differently, as older versions of the MySQL .net connector used a BYTE field of value 0 or non-0, and newer versions use a bit field set to b'0' or b'1'.

Sebastiaan Janssen 13 May 2013, 08:18:43

@Jan We do not have the resources to test every change on MySQL. I'm sorry that this is a causing you problems but we do many more queries that result in a true/false comparison in MySQL and they seem to run fine. I do remember making a different change recently that might be the source of this problem (which I'll investigate) - http://issues.umbraco.org/issue/U4-1904

Morten Christensen 13 May 2013, 08:25:59

For context: the boolean true/false vs 1/0 issue has been dealt with in a previous issue

Sebastiaan Janssen 13 May 2013, 08:52:35

Update: Even when reverting the changes too bool it's still selecting the inverse of what it should. Looking into it further.

Jan Hansen 13 May 2013, 09:42:15

@Sebastiaan: I appreciate your effort, and I understand that the team doesn't have endless resources. However, this is so vital (deleting data) that I would expect this to be tested in a degree where only minor bugs slip through. E.g. if the feature worked on MySQL 5.5 but not on 5.0 due to different handling of BYTE/bit-fields, or worked with a new MySQL .NET/connector, but not an old - it would be more "understandable".

Not knowing the fix/change that has caused this problem, it is hard to say anything - but it appears to end as a string comparison with 1 and 0 which should work. As far as I can see, this query fails to get the correct elements out:

var query = Query.Builder.Where(x => x.Trashed == true);

right? I'll see if I can debug into it.

I know we've been on the subject before, and you said that MySQL was supported as long as it was in lower case table name mode. With a bug like this, I find it difficult to trust the MySQL support. We only lost a few hours of work, because it was a small website - but what if it was a large website, where it isn't easily fixed and your file/database backup might be from "yesterday". It feels like MySQL is a second-class citizen when it comes to db-engines.

On a side note: the database is created (on a 5.0 at least) without any table relations/foreign keys. Is this also the case on datebases created on MSSQL?

Sebastiaan Janssen 13 May 2013, 09:55:00

Fixed in changeset 6686487bfda6

Sebastiaan Janssen 13 May 2013, 10:03:46

@Jan the error was due to an oversight in the querying provider for MySQL. This problem has been there since 6.0.0 but never surfaced because we apparently never had a query using a bool failing before. And since we recently updated a different queryprovider to generate the correct SQL, I assumed that would apply to this change completely. Unfortunately it did not. I appreciate your comments and generally when we do something that could affect MySQL specifically, we do test that. We're dedicated to support MySQL in v6 and consider the current implementation stable, but there's subtle differences that we can't always anticipate that make it hard for us to cover all scenario's from day one. That said, usage of MySQL is under 2%, so yes, we do focus on MSSQL / SQLCE first and MySQL second.

I've attached the bin folder with this fix in it, it is 6.0.5 with just this fix applied so it should be a completely safe update (but as always: take backups). Just copy the dlls in.

This build does include the security fixes for which the code has not yet been published (for security reasons).

Sebastiaan Janssen 13 May 2013, 10:07:45

@Jan on 5.5.28 it creates foreign keys for me.

Jan Hansen 13 May 2013, 10:24:11

Thank you for the fix! I'll look into the 5.5 database generation, or switch to MSSQL. I looked at the commit changes, but can't see what was "from". All I see is this (in green, as if it was added):

if (fieldType == typeof(bool)) return ((bool)value) ? Convert.ToString(1, CultureInfo.InvariantCulture) : Convert.ToString(0,CultureInfo.InvariantCulture);

but this i also what I see in the source I downloaded last week - what was there before?

Excuse my inability to use the code revision tool correctly :o)

Thanks again!

Sebastiaan Janssen 13 May 2013, 10:30:48

Yeah, that means it was added. So before it generated a query ending in 'True' and now it will generate a 1 instead. As I said, it was always broken.. :(

It wasn't there in the source last week, it was in the PocoToSqlExpressionHelper not in the ModelToSqlExpressionHelper.

Brian Powell 13 May 2013, 14:44:05

The new patch appears to be working correctly. It also seems to fix U4-2152.

Brian Powell 13 May 2013, 14:48:59

@Jan, @Sebastiaan: I originally installed Umbraco 4.7.x on MySQL 5.5.x before upgrading to 6.0.x. I've had the foreign keys all along.

Jan Hansen 16 May 2013, 07:41:42

@Brian: Yes, it looks like the foreign keys are created on 5.1 and up, but not on 5.0. On my 5.0, the tables are created as MyISAM tables, whereas they are created as InnoDB tables on my 5.1. MyISAM apparently doesn't support the foreign keys at all, but perhaps this is due to a default-database-engine configuration setting on our 5.0 server? I don't know. But it might be completely unrelated to the Umbraco installer.

Brian Powell 20 May 2013, 16:44:32

@Jan: It may be related to that default engine setting. I'm not sure.

Priority: Critical

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 6.0.5

Due in version: 6.0.6


Story Points: