Sign in Register
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)

    Copy Link
  • Morten Christensen 596 posts 2773 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

    Copy Link
  • 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`)
    );

    Copy Link
  • 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()));

    Copy Link
  • 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

    Copy Link
  • Morten Christensen 596 posts 2773 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

    Copy Link
  • Lee Kelleher 4026 posts 15837 karma points MVP 13x 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.

    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies