Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Frej Raahede Nielsen 26 posts 59 karma points
    Feb 12, 2010 @ 15:54
    Frej Raahede Nielsen
    0

    Error installing package (in MySQL database)

    Hi,

    I have a Umbraco 4.0.3 installation on a MySQL Database. When installing this package i got the following error message, and apparently no tables were created in the database.

    Package installation status:

    Error in Execute SQL Package action for Sitereactor Standard Values package error:
    umbraco.DataLayer.SqlHelperException: Umbraco Exception (DataLayer): SQL helper exception in ExecuteNonQuery ---> 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 '`INT` NOT NULL, `NODEID` `INT` NOT NULL, CONSTRAINT `PK_CMSSTANDARDVALUES` PRIMA' at line 1 at MySql.Data.MySqlClient.MySqlStream.OpenPacket() at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId) at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet() at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at MySql.Data.MySqlClient.MySqlHelper.ExecuteNonQuery(MySqlConnection connection, String commandText, MySqlParameter[] commandParameters) at MySql.Data.MySqlClient.MySqlHelper.ExecuteNonQuery(String connectionString, String commandText, MySqlParameter[] parms) at umbraco.DataLayer.SqlHelpers.MySql.MySqlHelper.ExecuteNonQuery(String commandText, MySqlParameter[] parameters) in d:\TeamCity\buildAgent\work\7380c184e9fcd3ea\umbraco\datalayer\SqlHelpers\MySql\MySqlHelper.cs:line 70 at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters) in d:\TeamCity\buildAgent\work\7380c184e9fcd3ea\umbraco\datalayer\SqlHelper.cs:line 217 --- End of inner exception stack trace --- at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters) in d:\TeamCity\buildAgent\work\7380c184e9fcd3ea\umbraco\datalayer\SqlHelper.cs:line 221 at Sitereactor.StandardValues.businesslogic.StandardValue.ExecuteSql(String sql, String returnText)

  • Morten Christensen 595 posts 2768 karma points admin hq c-trib
    Feb 12, 2010 @ 20:05
    Morten Christensen
    0

    Hi Frej,

    Thanks for the bug report. I haven't tried out the package with Umbraco running on MySQL.

    The app inserts should be pretty standard sql, please feel free to try them out (manually):

    "INSERT INTO umbracoAppTree ([appAlias], [treeAlias], [treeSilent], [treeInitialize], [treeSortOrder], [treeTitle], [treeIconClosed], [treeIconOpen], [treeHandlerAssembly], [treeHandlerType]) VALUES (N'settings', N'standardValues', 0, 1, 7, N'Standard Values', N'.sprTreeFolder', N'.sprTreeFolder_o', N'Sitereactor.StandardValues', N'Trees.LoadStandardValues')"
    "CREATE TABLE cmsStandardValues ([docTypeId] [int] NOT NULL, [nodeId] [int] NOT NULL, CONSTRAINT [PK_cmsStandardValues] PRIMARY KEY CLUSTERED ([docTypeId] ASC))"

    I'm not sure when I will have time to look into MySQL support, but I will put it on my todo-list. If you see anything that might not work with MySQL in the above, please let me know and I'll change it.

    - Morten

  • Nalle Jacobsson 32 posts 55 karma points
    Mar 18, 2010 @ 20:39
    Nalle Jacobsson
    0

    MySQL Version, a bit different =)

    INSERT INTO
      `umbracoapptree`
    (
      `APPALIAS`,
      `TREEALIAS`,  
      `TREESILENT`,
      `TREEINITIALIZE`,
      `TREESORTORDER`,
      `TREETITLE`,
      `TREEICONCLOSED`,
      `TREEICONOPEN`,
      `TREEHANDLERASSEMBLY`,
      `TREEHANDLERTYPE`
    )
    VALUE (
      'settings',
      'standardValues',
      0,
      1,
      7,
      'Standard Values',
      '.sprTreeFolder',
      '.sprTreeFolder_o',
      'Sitereactor.StandardValues',
      'Trees.LoadStandardValues'
    );

    CREATE TABLE `cmsstandardvalues` (
      `docTypeId` INT(11) NOT NULL,
      `nodeId` INT(11) NOT NULL,
      PRIMARY KEY (`docTypeId`)
    );

  • Nalle Jacobsson 32 posts 55 karma points
    Mar 18, 2010 @ 21:32
    Nalle Jacobsson
    0

    This will cause trouble also:

    IF OBJECT_ID('cmsStandardValues', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'

    For MySQL (5+) :

    SELECT IF(COUNT(*)!=0, 'true', 'false') FROM information_schema.TABLES WHERE (Table_Name='cmsstandardvalues' AND TABLE_SCHEMA=(SELECT DATABASE()));

  • Peter Dijksterhuis 1442 posts 1722 karma points
    Mar 18, 2010 @ 21:52
    Peter Dijksterhuis
    0

    Morten,

    aren't you using the datalayer? The ExecuteNonQuery in the datalayer should take care of that insert and create table definition.

    Regards,

    Peter

  • Morten Christensen 595 posts 2768 karma points admin hq c-trib
    Mar 19, 2010 @ 14:12
    Morten Christensen
    0

    @Peter I do use SqlHelper.ExecuteNonQuery(sql); when creating the table, but as Nalle noted the IF OBJECT_ID('cmsStandardValues', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false' will probably be a problem for MySQL. But I can implement both queries to test for both MSSQL and MySQL.

    Here is the Sql I use for creating the table:

    string sql =
    "CREATE TABLE cmsStandardValues ([docTypeId] [int] NOT NULL, [nodeId] [int] NOT NULL, CONSTRAINT [PK_cmsStandardValues] PRIMARY KEY CLUSTERED ([docTypeId] ASC))";

    - Morten

  • Lee Kelleher 3887 posts 14641 karma points MVP 9x admin c-trib
    Mar 15, 2011 @ 10:06
    Lee Kelleher
    0

    Just another one to add to your to-do list Morten... support for SQL CE4?

    I got an error installing one Umbraco 4.6.1 with SQL CE4. (No biggie for me at the moment ... was just a local dev site - for testing my packages)

    Cheers, Lee.

Please Sign in or register to post replies

Write your reply to:

Draft