This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I am having trouble getting the relationships to be recognized by the designer when I drag the tables in. I know the database schema is fine because I am using it in a different project with Lightspeed. I remember having problems with it before but can't remeber what I did to get it to work. I'm using MySQL connector 6.3.5 and Visual Studio 2008 SP1. Any ideas? |
|
|
|
|
Make sure there are foreign keys in the database. You may see problems if foreign keys are also primary keys, or if associations span schemas. We're not aware of anything else that would cause an issue, but if you can provide us with some representative CREATE TABLE statements then we can investigate. |
|
|
|
|
Here is a simple MySQL schema I just made to test it. Also doesn't work.
USE `test`;
CREATE TABLE IF NOT EXISTS `test`.`main` (
`idmain` INT(11) NOT NULL ,
`idrelated` INT(11) NULL DEFAULT NULL ,
PRIMARY KEY (`idmain`) ,
INDEX `fk_main_related` (`idrelated` ASC) ,
CONSTRAINT `fk_main_related`
FOREIGN KEY (`idrelated` )
REFERENCES `test`.`related` (`idrelated` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
CREATE TABLE IF NOT EXISTS `test`.`related` (
`idrelated` INT(11) NOT NULL ,
PRIMARY KEY (`idrelated`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
|
|
|
|
|
Hmm, if I run those CREATE TABLE statements through MySQL Administrator and drag the resulting tables into VS2008 then it works for me: the association is created just fine. We have heard of a schema created using a GUI tool (I think it was MySQL Workbench) not importing correctly into LightSpeed, but I don't think we ever got to the bottom of why that was. If you are using a GUI tool, could you try running your CREATE TABLE statements through a command line tool or MySQL Administrator or something like that and see if that makes a difference? Could you also check that you are using LightSpeed 3.11 or a more recent nightly build? I don't *think* we've changed anything recently that would affect this, but if you're on a very old version then it might be possible. Another possible issue is if your MySQL server is running on Unix -- this introduces some case-sensitivity issues though I don't *think* they would affect your examples. |
|
|
|
|
Hi Ivan: Just to give you some more information. I am using the MySQL Workbench. The SQL commands I gave you were from the Synchronize Model function in that tool. I'm not sure if MySQL Administrator is still available as the functionality is rolled into Workbench. I'll have to check. I am using a Nightly Build of Lightspeed from a week ago so I am pretty current. MySQL server is running on Windows so the last comment probably doesn't apply. I do development of a couple of different systems. I just tried it on my Windows 7 system (the other was Windows XP) with the same Visual Studio, Lightspeed and MySQL and it works. I guess that is how I got it to work before. Not sure if that helps. I will try your suggestion if I can find MySQL Administrator. Thanks. |
|
|
|
|
I found out the difference. On my Windows XP system the MySQL install seem to only support MyISAM so the tables were created with that engine despite the SQL command. Interestingly, they were no error when I executed the SQL. Anyways. I changed the engine to MyISAM on my Windows 7 system and the problem shows up. Here is the new SQL.
USE `test`;
CREATE TABLE IF NOT EXISTS `test`.`main` (
`idmain` INT(11) NOT NULL ,
`idrelated` INT(11) NULL DEFAULT NULL ,
PRIMARY KEY (`idmain`) ,
INDEX `fk_main_related` (`idrelated` ASC) ,
CONSTRAINT `fk_main_related`
FOREIGN KEY (`idrelated` )
REFERENCES `test`.`related` (`idrelated` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
CREATE TABLE IF NOT EXISTS `test`.`related` (
`idrelated` INT(11) NOT NULL ,
PRIMARY KEY (`idrelated`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
|
|
|
|
|
Aha! The problem is that MyISAM does not support foreign keys -- and, it appears, just ignores the foreign key constraints rather than erroring when you try to create them: For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC.
http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-foreign-keys.html (emphasis added) Since MySQL does not even store the foreign keys for MyISAM tables, there is no way for LightSpeed to detect them and infer the relationships. Sorry, but I don't think there's anything we can do about this until they implement the change mentioned in the second sentence above. |
|
|
|
|
Thanks Ivan, that is all good to know. I intend to use InnoDB for my ORM projects so I have no issue with this. I am surprised that InnoDB is the only engine that supports foreign keys. I am working on another project that is C++ based and uses MyISAM as the engine for performance reasons. I was managing the relationships with my code which I guess was a good thing given this restriction.
Thanks, again. |
|
|
|
