U4-2176 - 6.0.5 Dashboard error

Created by allan sloan 01 May 2013, 21:26:18 Updated by Sebastiaan Janssen 28 May 2013, 17:26:44

Is duplicated by: U4-2153

Is duplicated by: U4-2190

Relates to: U4-2123

After upgrading various 4 versions to 6.0.5 I get an error on the dash board It seems to be an issue with mysql query that is at fault

Server Error in '/' Application.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30 USERID, NODEID, DATESTAMP, LOGHEADER, LOGCOMMENT FROM UMBRACOLOG WHERE USERID' at line 1

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30 USERID, NODEID, DATESTAMP, LOGHEADER, LOGCOMMENT FROM UMBRACOLOG WHERE USERID' at line 1

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30 USERID, NODEID, DATESTAMP, LOGHEADER, LOGCOMMENT FROM UMBRACOLOG WHERE USERID' at line 1] MySql.Data.MySqlClient.MySqlStream.ReadPacket() +278 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +73 MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +20 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +110 MySql.Data.MySqlClient.MySqlDataReader.NextResult() +742 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1743 MySql.Data.MySqlClient.MySqlHelper.ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, String commandText, MySqlParameter[] commandParameters, Boolean ExternalConn) +156 MySql.Data.MySqlClient.MySqlHelper.ExecuteReader(String connectionString, String commandText, MySqlParameter[] commandParameters) +56 umbraco.DataLayer.SqlHelpers.MySql.MySqlHelper.ExecuteReader(String commandText, MySqlParameter[] parameters) +33 umbraco.DataLayer.SqlHelper`1.ExecuteReader(String commandText, IParameter[] parameters) +87

[SqlHelperException: Umbraco Exception (DataLayer): SQL helper exception in ExecuteReader] umbraco.DataLayer.SqlHelper`1.ExecuteReader(String commandText, IParameter[] parameters) +207 umbraco.BusinessLogic.Log.GetLogReader(User user, LogTypes type, DateTime sinceDate, Int32 numberOfResults) +279 dashboardUtilities.LatestEdits.Page_Load(Object sender, EventArgs e) +140 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35 System.Web.UI.Control.OnLoad(EventArgs e) +91 System.Web.UI.Control.LoadRecursive() +74 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Control.LoadRecursive() +146 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

Comments

John Murphy 02 May 2013, 17:41:58

I had the same problem with 4.11.8. It is a bug in the SQL generated by \umbraco\dashboard\LatestEdits.ascx. If you rename the ascx file to somethign else the dashboard will load (but obviously the latest edits tab won't be populated).

The actual bug is in umbraco.businesslogic/Log.cs. That contains the method

///

/// Gets a reader of specific for the log for specific types and a specified user. ///

/// The user. /// The type of log message. /// The since date. /// Number of rows returned /// A reader for the log. [Obsolete("Use the Instance.GetLogItems method which return a list of LogItems instead")] internal static IRecordsReader GetLogReader(User user, LogTypes type, DateTime sinceDate, int numberOfResults) { return SqlHelper.ExecuteReader( "select top " + numberOfResults + " userId, NodeId, DateStamp, logHeader, logComment from umbracoLog where UserId = @user and logHeader = @logHeader and DateStamp >= @dateStamp order by dateStamp desc", SqlHelper.CreateParameter("@logHeader", type.ToString()), SqlHelper.CreateParameter("@user", user.Id), SqlHelper.CreateParameter("@dateStamp", sinceDate)); }

"top" is SQL Server specific. The MySQL way to do that is something like "select userId, NodeId, DateStamp, logHeader, logComment from umbracoLog where UserId = @user and logHeader = @logHeader and DateStamp >= @dateStamp order by dateStamp desc limit " + numberOfResults,

---

Jan Hansen 06 May 2013, 08:57:06

As we're using MySQL we're seeing the same behaviour. What is the "offical position" regarding database support? As I understand it, MSSQL is the only supported database engine, and while it "may" work on other database engines, it is not a priority to ensure.

This error shows that releases aren't tested on mysql (which is fine as long as we know it) - as the .net error occurs on the very first page shown after an install. But is there an "official" position regarding this? I've search the website but didn't find anything.

---

Sebastiaan Janssen 06 May 2013, 12:55:48

We support MySql if it's not in case-sensitive mode. And yes, I committed it without testing on MySQL, why can't it just work like SQL? :P

---

Sebastiaan Janssen 06 May 2013, 14:28:14

Fixed in changeset 870c5bb4f5f1

---

John Murphy 07 May 2013, 19:15:17

Thanks Sebastiaan. Will that change wind up in 4.11.x, too?

---

Sebastiaan Janssen 08 May 2013, 10:18:47

Thanks John, good one! Of course, fixed there now as well. :) Changeset 25f94f710e5e

---