[CalEvans]

I'm using 3.11. Yes, there is a mising ). It's a known issue. Sorry, I should have corrected the code before I posted.

change:

Code:

TYPE=InnoDB

to

Code:

) TYPE=InnoDB

The the code should work.

=C=

[CalEvans]

Have you tried it the other way? Switch the databases?

And key is the proper SQL term in this case, not Index.

=C=

[CalEvans]

I was able to create the two tables in my test database and then create the relationship.

To create the relationship, I clicked on tblQuestions and then on the Manage Relationships button. Clicked the New Button, selected the two fields and the actions I wanted.

Not sure why it's not working for you. (code below.)

CREATE TABLE `tblQuestions` (

`id` int(10) NOT NULL auto_increment,

`surveyId` int(10) NOT NULL default '0',

`qNum` varchar(50) default NULL,

`order` int(10) default NULL,

`logic` varchar(50) default NULL,

`inputType` varchar(50) default NULL,

`responseId` int(10) default NULL,

`question` longtext,

PRIMARY KEY (`id`),

KEY `surveyId` (`surveyId`),

FOREIGN KEY (`surveyId`) REFERENCES `test.tblSurveys` (`surveyId`) ON DELETE CASCADE

TYPE=InnoDB

[CalEvans]

Do you get any errors when creating the table?

=C=

[CalEvans]

that user at that host does not have permission to create a database. Check your permissions in MySQL.

=C=

[CalEvans]

Son of a gun. You are correct. I never noticed this. (Shows how much I pay attention to details! 🙂

=C=

[CalEvans]

Does root have permission to login from localhost without a password? (You need to check your mysql.user table to see)

=C=

[CalEvans]

Jim,

Are there any sql commands in the window that are not terminated by a ;? SQLyog is very picky that ALL commands must be terminated by a ; if there are multiple commands in the window, EVEN IF IT'S NOT A COMMAND YOU ARE EXECUTING. Not sure why but check it out. This would explain why closing and reopening would cause it to work.

BTW, this most commonly hits me when I use ENTER to view the contents of a table. In 3.11 it puts a ; on the line BEFORE the command to make sure that any command there is properly terminated (I guess) but it does NOT terminate the select with a ;. Therefore If I do this and then try and type in a command below it I will get an error in my sql and to fix it I simply terminate the SELECT with a ;.

Clear as mud?

HTH,

=C=

[CalEvans]

1: Ignore anything you learn about databases in Access. 🙂

Let's define a couple of terms here first:

RELATIONSHIP.

A RELATIONSHIP is just what you think it is. Something stored in table B that related to table A. You can have a RELATIONSHIP in just about any database tool including Excel. (Which is technically not a databasing tool but people use it as such.) People were using RELATIONSHIPs between tables in MySQL long before InnoDB or Gemini came along.

CONSTRAINT:

This is where the database engine enforces a relationship. There are 2 kinds of constraints enforced in MySQL with InnoDB, UPDATE and DELETE. Setting up CONSTRAINTS is usually where most people get messed up because in MySQL it can be a bit confusing. When a CONSTRAINT is setup on a relationship the engine checks to make sure the CONSTRAINT will not be violated before performing the action. Or, in the case of cascading delete CONSTRAINT, it will delete all the related records in table B when the parent record in table A is deleted.

So now to use what we've learned. If you create the tables myParent and myChild. each with a PK of ID (my personal favorite naming scheme) then you can add a relationship to myChild by adding a field OF THE SAME TYPE AND SIZE as myParentID to myChild. This makes myChild.myParentID a FOREIGN KEY. Whenever you add a record to myChild you are supposed to fill in the value of the myParentID with the value of the myParent.myParentID that this new record relates to. (read it again, it makes sense, I promise.)

This is a relationship.

Now, with InnoDB, we can now define CONSTRAINTS as well. Use SQLyog to define your CONSTRAINT. It will allow you to define the type of CONSTRAINT and then it will issue the necessary ALTER TABLE to build it. If you want the myChild record to be deleted when the myParent record is deleted then you need to check ON DELETE and then select CASCADE. now you have a RELATIONSHIP with a CONSTRAINT on it.

Exactly how all of this happens depends on the tool you are using. I have a DM tool for MySQL that automatically adds the field for me when I draw a relationship line. I use some that do not do this. If you are hand-coding the SQL you have to do it all yourself.

I hope this clears up some of the confusion.

=C=

[CalEvans]

If you are on dialup then yes, your IP address changes every time you connect. They need to grant access to your database to yourlogin@%.

The other thing you can do is use ssh to tunnel the port.

1: Setup putty to tunnel 3306

2: Connect to your host with putty (ssh)

3: open sqlyog

4: connect to localhost with sqlyog

I do it for one of my databases and it works great. (I use SecureSSH from http://www.vandyke.com though)

HTH,

=C=

[CalEvans]

1: check the box SET FOREIGN_KEY_CHECKS=0 (yea, I know it's not real intuitive unless you know what that command does in mysql) That will turn off the FK checking for the import.

2: So? Other than for grins and giggles, do you ever actually READ the code generated by export? 🙂 (FWIW, I always use mysqldump for this anyhow…old habits die hard and I'm used to the way it outputs.)

HTH,

=C=

[CalEvans]

Salutations!

Add default values to the fields you are getting nulls on and re import. OR, in your programming, allow for nulls. I'm still surprised by the number of programmers who don't use null values.

=C=

[CalEvans]

Durghan,

You don't have to create indexes on ALL your fields, just the ones involved in the relationships. Since I have NEVER (Seriously, in 17 years of programming NEVER) seen a relationship on a text field I would suggest you not try to index your text fields.

If you are not new to databases then ignore this section.

1: All your tables should have a primary key field. This SHOULD be something like an autoincrement BIGINT(22) . (Phone numbers, SSN's and other pieces of information may make good CANDIDATE KEYS but for your PRIMARY KEY you don't want anything that means something)

2: Your relationships should be the PK of one table relating into another making a FK.

In the attached datamodel snippet, notice how the relationship form pageType to page is on pagetypeID, the PK for pageType.

HTH,

=C=

[CalEvans]

DFI,

No, the database sync tool is for syncing the STRUCTURES of databases, not data.

There are tools that will do this. Check http://www.freshmeat.net and search for mysql. Should be able to come up with what you need.

=C=

[CalEvans]

Have you checked your log files?

mysql generates some fairly complete log files.

I've not personally had this happen but I've not tried it on Win98. (yet) If this were Linux (where the bulk of my experience lies) I would say that your InnoDB table handler is trying to re-sync with it's logs or create it's initial DB space. The First time you bring mysql up it does this.

Is there a switch for turning InnoDB off? I would try that and see if it helps. (NOTE: I do not advocate doing this permanently as the InnoDB table handler is one of the great features of mysql.)

Keep us informed of your progress.

=C=

[Author]

Posts