Autoinc problem with master/detail (delphi)

Data Abstract
1

Hi,
I have some master/detail tables and the master table use autoinc field that is used as master field (on detail side).
The problem is that when you create a master record then a detail record, the detail record use the “temp and negative” autoinc value. When I apply update I get errors on detail, saying that it expect longword and instead it has negative value.
How to solve this case ?
Regards

2

Hi.

Unfortunately I can’t reproduce this. On simple example everything works as expected - before update values are negative but after applying update it got correct values. Can you send a test case or DDL for tables, please?

3

also in the detail table?
in my case no problem with master table. but the field of the detail table is not an autoinc so it doesn’t change the value, it keeps the negative value.

here’s the DDL:
CREATE TABLE myDB.units(
id_units INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
PRIMARY KEY (id_units)
)
ENGINE = INNODB
AUTO_INCREMENT = 5
AVG_ROW_LENGTH = 4096
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

CREATE TABLE myDB.product_familly(
id_product_familly INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60) DEFAULT NULL,
id_units INT(11) UNSIGNED NOT NULL DEFAULT 1,
nbcolumns INT(11) UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY (id_product_familly),
INDEX fk_product_familly_units1 (id_units),
CONSTRAINT fk_product_familly_units1 FOREIGN KEY (id_units)
REFERENCES myDB.units (id_units) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 19
AVG_ROW_LENGTH = 1092
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

CREATE TABLE myDB.tarifs_columns(
id_tarifs_columns INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
id_product_familly INT(11) UNSIGNED NOT NULL,
position INT(11) UNSIGNED NOT NULL DEFAULT 1,
name VARCHAR(45) DEFAULT NULL,
col_begin DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
PRIMARY KEY (id_tarifs_columns, id_product_familly, position),
INDEX fk_tarifs_columns_product_familly1 (id_product_familly),
CONSTRAINT fk_tarifs_columns_product_familly1 FOREIGN KEY (id_product_familly)
REFERENCES myDB.product_familly (id_product_familly) ON DELETE CASCADE ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 24
AVG_ROW_LENGTH = 862
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

If I insert a new record on master table product_familly, then I create a new detail record on tarifs_columns, since id_product_familly of master is negative, id_product_familly of detail is also negative.

When I apply update on detail (after master), the value is still negative on detail and it raises an error.
Regards

4

Just another question - what version of MySQL and what DB driver you are using?

5

MySQL 5.1.49 and last MyDac version.

6

note that this problem occurs also on MariaDB 5.5.25 on Windows.

7

Hello,

You define detail fields in your tables (product_familly.id_units and tarifs_columns.id_product_familly) like UNSIGNED, and DA couldn’t insert negative value in DB and get back generated by DB AutoInc value. Try to remove UNSIGNED flag for these fields.

Hope this helps

8

Removing the UNSIGNED flag removed the problem. Thanks.

9

in Fact I think it was solved but nope.
I raises an error, but if I cancel changes it’s ok on th tables.
I think the problem occurrs with foreign keys.
When I close my form I do
Master.ApplyUpdates;
Detail.ApplyUpdates;

Here the error I get :
A problem occured while inserting a record in table “tarifs_columns”, record “-1;-1;1”

#23000Cannot add or update a child row: a foreign key constraint fails (vitrine.tarifs_columns, CONSTRAINT fk_tarifs_columns_product_familly1 FOREIGN KEY (id_product_familly) REFERENCES product_familly (id_product_familly) ON DELETE CASCADE ON UPDATE N)

This error is raized just after the Master.ApplyUpdates; probably because at this time the detail still has negative values.
How to avoid this ?
Regards

10

Hello,

I attached a simple project with master/detail tables based on DDL you provide us. Adjust connection settings in schema and test the project.

Note that you don’t have to apply updates on master and then on detail table, if you don’t change default options (moCascadeApplyUpdates, dtCascadeApplyUpdates).

Hope this helps

11

Thanks for the sample,
after trying it, it works.
I have done a lot of investigation, combining all case with your sample server and client, and my project server and client.
And I have more or less isolated where the problem is.
The problem seems to come from the schema. Using the same schema on your server raizes the same error. I have tryed to rebuild with the wizard from scratch and it doesn’t solve it. Note that I have already said in others support tickets that I have some problems with the Schema modeler, like not able to see the views etc…
For privacy readons, I will resend you the sample with the “complete” schema to support email.Note that in the schema, there will be more tables than the 3 tables used here.
Regards

12

Hello,

There is a mistake in your Schema Relationships - FK_product_familly_tarifs_columns should have product_familly as MasterDatasetName and tarifs_columns as DetailDatasetName , but you have quite the contrary. Althogh I don’t get your error even in this case, just tarifs_columns’ new row is not inserted. What Delphi version you use?

13

Hi,
all Schema Relationships are all created by the Schema modeler, and after checking it I see that all master/detail are inverted.
I have already found this problem 2 years ago with MySQL and MySQL .Net driver using relativity.
Note that I am using Winter 2011 release.
I have a lot of problem also where the views are ignored by the wizards.
Was this solved since this time ?
Regards

14

I don’t have anymore this error if I edit manualy as text file the schema for inverting the master/detail stuffs that were created by the wizard.
The only problem I have found is that if I do :
Master.ApplyUpdates;
Detail.ApplyUpdates;
the first inserted record of detail is duplicated.
If I remove the Detail.ApplyUpdates; it is ok.
Regards

15

Hello,

Armindo said: all Schema Relationships are all created by the Schema modeler, and after checking it I see that all master/detail are inverted.
Thanks for the report, the issue was logged and fixed as #56857 I sent you DAMyDACDrv.dad on mail, put it into SchemaModeler folder and Relationships should be built correctly after that.
Armindo said: I don't have anymore this error if I edit manualy as text file the schema for inverting the master/detail stuffs that were created by the wizard.

You could edit master/detail relationships directly in SM also

Best regards