I'm not getting the designer to pick up my foreign keys in my MySQL 4.1 InnoDB tables. I have them defined, but the designer never sees them. I've also tried to add them by hand, but it always only shows me the primary keys on both sides.

Hmm, just found

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1752

where you mention not finding the FKs using the CoreLab drivers in InnoDB tables.

However, I'd still like to create one by hand, but I can only pick the PKs. Is this by design? Am I just out of luck?

Yep, there it is in the documentation (who reads that nonsense?):

MySql v4.x (4.1 or higher with InnoDB preferred) Tables, identity columns, primary keys, unique constraints, subqueries All native MySql types except SET and ENUM (will be converted to VarChar). No support for database defined foreign keys. Foreign key constraint meta-data defined in the database are not (yet) read by the MySql driver.

(This also answers my question about ENUMs)

Still, can't I somehow create a relationship using the designer? If it would allow me to pick any field (of a similar INT type) I'd be fine.

FK's can't be read from the db schema yet, it requires the parsing of the SQL used to create the tables, which is cumbersome and error prone.

You can create relations in the designer though, just create a custom relation between 2 entities.

Yep, that's what I'm trying but no luck. I use the 'custom entity relation editor' dialog, and in the drop-down called 'foreign key fieldname (tablename)' it only shows one field, the PK field, not the field I wish to bind to the PK of the primary key side.

Are they of the same type? They have to be of the same type.

Yes, they are.

Let me try to be more specific. Two tables, 'binder' and 'website'. One binder has many websites. Binder has a PK called 'binderid', of type INTEGER, website has a PK called 'websiteid' of type INTEGER.

Website has a column called 'fkbinderid' of type INTEGER. The idea is to create a relationship within Designer linking binder.binderid to website.fkbinderid. A reminder, this is MySQL 4.1, so no FKs created using MySQL tools will be in effect.

When I'm in Designer, I go to the table 'website', and from Relations I select the 'Add new 1:1/1:n/m:1 relation...' menu. I tell it that 'website' is on the foreign key side, and is related to 'binder'. The PK for binder shows on the left of the two columns called 'Primary Key Fieldname (Binder)', but the only option on the right of the two columns, called 'Foreign key fieldname (Website)', is the column 'websiteid', which is the PK of 'website'.

The column 'website.fkbinderid' should be appearing (shouldn't it?), but is not. It's as though the second column is filtered only for PKs. This doesn't seem right.

I've tried indexing 'fkbinderid' (for no good reason) and renaming it to 'binderid' to get it to match. I've tried switching the table types to and from InnoDB and MyISAM. My preference is to stay at MyISAM to pick up full-text indexing, but I don't really think it matters to Designer.

Should this be expected?

Naturally I can still use the product, but I miss many of the great features, as well as the new Manager templates (which, by the way, do compile and work with MySQL.)

It checks if the types are completely the same. You indeed should see fkbinderid at the right, as long as the type is the same, i.e. INTEGER but also the size of the integer (length) should be the same.

It uses MappedField.TypeDefinition.Equals(), which uses this test: return ((_dbType==toCompareWith.DBType)&&(_length==toCompareWith.Length)&& (_precision==toCompareWith.Precision)&&(_scale==toCompareWith.Scale));

I.o.w.: if type, precision, scale and length don't match, the field isn't selected as an FK field for the current PK field.

The certainly look the same. I reviewed them in both the MySQL Administrator and myphpadmin. The only difference I can find is that one is AutoINC and the other is not, and that website.fkbinderid has a default of zero (0). For some reason it won't let me remove the default value from website.fkbinderid, and it naturally will not let me add one to binder.binderid.

We can deal with this Monday. Have a good holiday.

Ok, if you can't find it, send me the .lgp file and I'll have a look (I think it will be monday though )

Curious if you'd had any luck?

FYI, to close this thread, this issue was fixed in the latest hotfix for the MySQL drivers. There was an issue related to Designer's understanding of precision in MySQL, and it was discovered that the CoreLab handles PKs in an unexpected manner.

Indeed. Still haven't heard back from CoreLab, but as they're based in the Ukraine, perhaps they're partying right now, dunno

they're based in the Ukraine, perhaps they're partying right now

I hope every good Capitalist there is!

Looks like this bug has re-asserted itself. I'm using the SD.LLBLGen.Pro.DQE.MySql.NET11.dll build of 1/11/2005, and I'm getting the same issue where BigInts for primary keys are shown with precision 0 (zero) and other bigints are shown with precision 20. This means that no relationship can be established because they are not equal.

You had uploaded a hotfix for this file, but perhaps the code didn't make it into the main code branch.

I guess I can revert back to the hotfixed version, but I want to remind you that the issue remains.

Whoops, just saw the new 17 Jan release of the MySQL driver. Will try it...

edit No joy. Trying downloading and installing the full 20-Jan upgrade...

edit Yep, that did it. Everything's good.

Yes the mysql driver got some fixes as I neglected 'unsigned' fields. These are now handled better.

Otis wrote:

Yes the mysql driver got some fixes as I neglected 'unsigned' fields. These are now handled better.

Hi,

I'm experiencing this same exact problem, but I have MySql Driver 2.70.1 and the LLBLGen Pro from January 20th, 2005. Any ideas if I'm missing something?

All the AutoINC fields are being shown as Int64 and a precision of 20.

Thanks for any assistance.

Nirav

nirav wrote:

Otis wrote:

Yes the mysql driver got some fixes as I neglected 'unsigned' fields. These are now handled better.

Hi,

I'm experiencing this same exact problem, but I have MySql Driver 2.70.1 and the LLBLGen Pro from January 20th, 2005. Any ideas if I'm missing something?

All the AutoINC fields are being shown as Int64 and a precision of 20.

Thanks for any assistance. Nirav

Correct, as the CoreLab's mysql provider returns every value in an autoinc field as Int64... So the type for the field is set to Int64 as well.

I leave my PK as INTEGER, which, for some reason, ends up in Designer as DBType BigInt(20) and .NET type System.Int64.

You need to set the FK datatype to BIGINT(20), not as INTEGER, and make sure that FK is SIGNED (or NOT UNSIGNED).

Should work then.

It's some kind of CoreLab translation thing. I don't question it anymore because it works...

For instance, here's two tables, Person and Team. A person is on a Team, linked through Person.Teamid and Team.Teamid:

CREATE TABLE `person` (
  `personid` int(10) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  `useraccountid` bigint(20) NOT NULL default '0',
  `description` text NOT NULL,
  `timestamper` datetime NOT NULL default '0000-00-00 00:00:00',
  `teamid` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`personid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `team` (
  `teamid` int(10) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  `primarycontactpersonid` bigint(20) NOT NULL default '0',
  `description` text NOT NULL,
  `timestamper` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`teamid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

...and yes, I occasionally do a project in MySQL. Why? Why degrade myself in this way? Why lower myself from the heights of MSSQL? Why give up GUIDs, true transactions, even bit fields?

Because it's fast. And on top of that, it's fast. And if you want to do full-text indexing, it's even faster. And even when it's not doing anything, it's fast. And when it's doing lots of things at once, it's fast. And when you want to do bulk processing, and high volumes, and index rebuilds, and other of those things that make life interesting, I've got two words for you. It's fast.

Did I mention that it's fast?

heh

good stuff Scott!

Otis wrote:

Correct, as the CoreLab's mysql provider returns every value in an autoinc field as Int64... So the type for the field is set to Int64 as well.

Thanks for the quick reply guys, much appreciated.

Do you think this would make the app inefficient, instead of Int32, I'm forced to use Int64 for every primary key that has auto inc. I'm just wondering if this would be a performance issue or not.

So the database is storing everything correctly, its just getting translated through corelabs weird. But, if I'm forced to increase the size of my FK, isn't this a waste of space too?

Thanks again, this forum is great!

You're correct that there's a waste in 'overstoring' the BIGINTs. However, I'm doing a database with 8 million rows in about 4gb. If I changed it back to INT, I'd be saving about 37 cents (U.S. dollars) in hard drive space. Whoodee doop.

You're also correct that there's likely a memory overhead and perhaps speed issue as it thunks the Int64 down to a pair of 32's for each column's row. But, I have to say, I don't see it having an effect, mostly because I don't 'over-SELECT'. Perhaps we could squeeze another 10% out of it, and if CoreLab updates (or fixes) it's drivers we can do that.

The biggest advantage for us is simply full-text searches (in MyISAM.) The support for them is great, and the response time, when combined with the LIMIT keyword, is shocking; far beyond anything I was able to wring out of Microsoft. Of course, because it's MyISAM and not InnoDB, we lose true FK relations, but happily we can emulate that in LLBLGen Pro's Designer.

The advantages far outweigh the costs of going to the BIGINTs, and perhaps even that will be fixed in the future.