Designer can't save associations to database

Hi,

I'm using LightSpeed 3.1. I do not have a license; I am testing with the limited version. I have tested with a brand new schema that contains only two tables. I am using Windows 7, Visual Studio 2010, and MySQL 5.1.48. I have the Dot Net Connector 6.3.2 beta. 

When I drop tables from my existing MySQL database into the Lightspeed Designer, it does not show the existing relationships. I can create new OneToManyAssociations within the designer, but if I try to save them to the database, I can error messages such as

Can't create table 'test.#sql-a44_1f' (errno: 150)

Any suggestions?

Thanks,

Jason

We've had a report of this from a customer who was using the MySQL Workbench beta to create his schema.  We were unable to reproduce the issue because when we ran his exported CREATE TABLE scripts everything worked fine; and when he used another MySQL admin tool instead of Workbench it worked fine for him as well.  (And we couldn't reproduce the problem when we used Workbench.)

So if you are using Workbench, it might be worth trying an alternate tool such as MySQL Administrator.

If this doesn't help, is there any way you can back up your test database so that we can attach it to our own MySQL instance?  (As mentioned above we've found that merely exporting the CREATE TABLE statements doesn't seem to allow us to repro the issue.)  We'd be very keen to track down what is going on here -- thanks!

Hi Ivan,

 

Thanks for your prompt reply. I created a test database using MySQL Administrator version 1.2.17. I don't know how to add an attachment using this forum, so I'll just paste it into the body of the message below.

As mentioned, I have LightSpeed 3.1 installed in Visual Studio 2010. I create a LightSpeed model and drop the tables "author" and "book" onto the model. I notice that it does not show the "ID" columns of either Author or Book; it only shows their Name columns.

I create a one-to-many association from the author table to the book table. An arrow appears labeled "Books" where it connects to the Author table, and "Author" where it connects to the Book table. I then right-click on the model and chose "Update Database...". I get a dialog showing with the following:

[X] Author

   +--- [X] Add column AuthorId and associated foreign key to table Book

I then get the error message: "The following updates could not be carried out: Author: Add column AuthorId and associated foreign key to table Book - Can't create table 'test.#sql-7e4_9' (errno: 150).

Here is the SQL of the original database:

-- MySQL Administrator dump 1.4

--

-- ------------------------------------------------------

-- Server version 5.1.48-community

 

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

 

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

 

 

--

-- Create schema test

--

 

CREATE DATABASE IF NOT EXISTS test;

USE test;

 

--

-- Definition of table `author`

--

 

DROP TABLE IF EXISTS `author`;

CREATE TABLE `author` (

  `ID` int(10) unsigned NOT NULL,

  `Name` varchar(45) NOT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `author`

--

 

/*!40000 ALTER TABLE `author` DISABLE KEYS */;

/*!40000 ALTER TABLE `author` ENABLE KEYS */;

 

 

--

-- Definition of table `book`

--

 

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (

  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `Name` varchar(45) NOT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `book`

--

 

/*!40000 ALTER TABLE `book` DISABLE KEYS */;

/*!40000 ALTER TABLE `book` ENABLE KEYS */;

 

 

 

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

 

Hello Jason,

Thanks for the additional detail.  After a lot of trial and error it appears that this is a problem with unsigned IDs.  When I remove the UNSIGNED modifier from your SQL statements, the Update Database command works correctly.

I think this may be due to LightSpeed trying to create the AuthorId column as an INT rather than an INT UNSIGNED.  I guess MySQL is treating this as an integrity violation (because the signed FK column type doesn't match the unsigned PK column type), but that is one cryptic way of reporting it!

Unfortunately this is probably not something we can fix because the type of the AuthorId column is inferred from the identity type of the the Author entity, and the LightSpeed designer doesn't distinguish between signed and unsigned identity types (it always treats them as signed).  So I'm afraid the only solution is to change your table definitions to use signed INT IDs instead of unsigned.

Regarding the fact that the Id column doesn't appear on the designer, this is correct.  The presence of the Id is implicit in the Entity<T> base class, so it does not appear as a separate property on the designer.

Thanks again for the very helpful repro.

Thanks, Ivan. That fixed it!

Jason