[Shadow]

Do you get any error message?

[Shadow]

I confirm Hughes' report and absolutely agree with his solution!

[Shadow]

The problem is that the DEFAULT settings of the CSV export are not OK, they produce such an output which cannot be re-imported by MySql's LOAD DATA INFILE command. The default export parameters (and the imports' as well…) should be changed to a valid format!

[Shadow]

Exporting data as batch file does not result in a file with delimiters. Most likely you meant exporting data as CSV file. Exporting data as batch file feature works as expected for me!

But exporting data as CSV with the default settings does not produce usable output. If I try to import the data exported with the default settings, then the import fails with err. no 1064 (syntax error)…

[Shadow]

Or you could instruct the users to set the value of the second timestamp column to NULL. BTW, I don't think it is a good idea to use SQLyog as a data-entry tool…

[Shadow]

Quote:

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

The solution is also provided in the MySql manual:

Quote:

TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

I suggest to use NOW(), though…

[Shadow]

You need to have an ODBC driver for DB2 installed on the machine that runs SQLyog…

[Shadow]

Yes, an index is compulsury, but primary keys do create an index!!!!!!

[Shadow]

BTW, giving a second look to your first sjablonen table definition, I discovered that you forgot to create an index on tabblad field, that's why the statement did not work:

DROP TABLE IF EXISTS sjablonen;

CREATE TABLE IF NOT EXISTS sjablonen (

 idsjabloon int(11) NOT NULL auto_increment,

 INDEX i_idsjabloon (idsjabloon),

 naam varchar(30) NOT NULL DEFAULT '' ,

 tabblad varchar(4) DEFAULT '' , <

---

missing index!

 sjabloon longtext ,

 omschrijving varchar(50) NOT NULL DEFAULT '' ,

 PRIMARY KEY (idsjabloon),

  FOREIGN KEY (tabblad) REFERENCES mediumtabbladen (IdTabladen)

  ON UPDATE CASCADE

)TYPE=InnoDB;

[Shadow]

If you have a primary key on mediumtabbladen.IdTabladen field, then you don't have to add another index on that field as PK itself is an index as well. This goes to the reference field in sjablonen table.

[Shadow]

MySql does not automatically create the indices required to set up a relationship.

Quote:

Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly.

For further details see sections 7.5.4.2 Foreign Key Constraints and 3.5.6 Using Foreign Keys in the MySql Manual.

Fehér, Petö, Babos…

[Shadow]

Did MySql return any error message when trying to import the file as whole?

[Shadow]

Interesting, I downloaded your SQL script and after deleting the rows the two tables contained and creating the extra needed index, I was able to create the relationship. Do you have the necessary indices on both fields (the referencing and the referenced)?

BTW, I could suggest a few names for NAC 😛

[Shadow]

The tables should not have any records when adding a foreign key constraint. Export the data, build the constraint and import the data.

[Shadow]

Right click on the table in the object browser and look for the “Change table type to” item. But let me point out that before using InnoDB you should read the related sections of the MySql manual because it's quite simple to get InnoDB working, it is difficult, however, to use and to tune it properly!

[Author]

Posts