locked
MYSQL Connection issues RRS feed

  • Question

  • Has anyone gotten LightSwitch to work with MYSQL? I downloaded the latest release of MySQL v5.5.8 and the NET Connector v6.3.6 and cannot get it to do basic insert/update operations. I have read the connection issues in which you need to connect to the database via Server Explorer to get MySQL connected initially which works correctly.

    I have a very basic setup for testing and learning purposes in that I have 1 table with ID (int AI), field1 (varchar), field2 (varchar), and field3 (varchar). The entity imports into LightSwitch fine and I created a basic CreateNew screen linked to the entity and everything seems to work OK. When I run the application and try to do an insert, I get an error saying "An error occurred while starting a transaction on the provider connection. Nested transactions are not supported."

    Thanks for the help.

    Wednesday, January 5, 2011 11:54 AM

Answers

  • Has anyone gotten LightSwitch to work with MYSQL? I downloaded the latest release of MySQL v5.5.8 and the NET Connector v6.3.6 and cannot get it to do basic insert/update operations. I have read the connection issues in which you need to connect to the database via Server Explorer to get MySQL connected initially which works correctly.

    I have a very basic setup for testing and learning purposes in that I have 1 table with ID (int AI), field1 (varchar), field2 (varchar), and field3 (varchar). The entity imports into LightSwitch fine and I created a basic CreateNew screen linked to the entity and everything seems to work OK. When I run the application and try to do an insert, I get an error saying "An error occurred while starting a transaction on the provider connection. Nested transactions are not supported."

    Thanks for the help.


    if you do a search for "mysql nested transactions are not supported" you'll see that there are a number of ongoing bugs and issues with the mySQL core engines, the .net adapters, and perhaps the EF providers as regards to nested transactions and transactions in general.

    Allegedly, the .Net connector supports nested transactions as of version 6.3:

    http://dev.mysql.com/doc/refman/5.5/en/connector-net-news-6-3-0.html

    We've been investigating this internally recently; while I cannot tell you "it definitely should work" or "it definitly will not work", our experience has been similar to yours, and we're trying to figure out what to do about it. 

    Wednesday, January 5, 2011 4:56 PM

All replies

  • Hi, you need to ensure that the MySQL Connector supports the ADO.NET Entity Framework. Visual Studio LightSwitch uses this behind the scenes to access data, so what you need is not just an ADO.NET provider for MySQL but something that supports the ADO.NET Entity Framework.

    If this is already the case, the problem might depend on the fact that LightSwitch performs some operations through the Entity Data Models that are not supported by the MySQL provider.

    I don't know much about MySQL, but this is a general consideration that targets several external data stores.


    Alessandro Del Sole
    Microsoft MVP - Visual Basic: development
    http://community.visual-basic.it/alessandro/
    Wednesday, January 5, 2011 1:28 PM
  • Sorry, I just found this previous thread:

    http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/4485c5b3-0849-4f3c-88ea-78d93177a414

    Have a look and check if it can solve your problems! Hope this helps.


    Alessandro Del Sole
    Microsoft MVP - Visual Basic: development
    http://community.visual-basic.it/alessandro/
    Wednesday, January 5, 2011 1:31 PM
  • Thank you for your time in trying to resolve this issue.

    I have been all over this forum as well as the internet to try and see why this error is occurring but cannot find any answers. I have been over the thread you mentioned countless times and in fact that thread helped me with my initial problem in getting connected to the MySQL database.

    According to MySQL, the Net Connector has had support for ADO.Net entity framework since 6.0 release in July of 2009. I have tried previous versions of this connector as well and they have all given me the same error message.

     I am curious to see if anyone has successfully used a MySQL database with LightSwitch as I cannot seem to get it to work. Reading the database works perfectly fine if I insert the data manually but I cannot do any creates or edits on that data.

    Thanks.

    Wednesday, January 5, 2011 1:41 PM
  • Has anyone gotten LightSwitch to work with MYSQL? I downloaded the latest release of MySQL v5.5.8 and the NET Connector v6.3.6 and cannot get it to do basic insert/update operations. I have read the connection issues in which you need to connect to the database via Server Explorer to get MySQL connected initially which works correctly.

    I have a very basic setup for testing and learning purposes in that I have 1 table with ID (int AI), field1 (varchar), field2 (varchar), and field3 (varchar). The entity imports into LightSwitch fine and I created a basic CreateNew screen linked to the entity and everything seems to work OK. When I run the application and try to do an insert, I get an error saying "An error occurred while starting a transaction on the provider connection. Nested transactions are not supported."

    Thanks for the help.


    if you do a search for "mysql nested transactions are not supported" you'll see that there are a number of ongoing bugs and issues with the mySQL core engines, the .net adapters, and perhaps the EF providers as regards to nested transactions and transactions in general.

    Allegedly, the .Net connector supports nested transactions as of version 6.3:

    http://dev.mysql.com/doc/refman/5.5/en/connector-net-news-6-3-0.html

    We've been investigating this internally recently; while I cannot tell you "it definitely should work" or "it definitly will not work", our experience has been similar to yours, and we're trying to figure out what to do about it. 

    Wednesday, January 5, 2011 4:56 PM