From Newsgroup: comp.databases.mysql

I'm sorry but I don't understand the error in the object when I add data
in the next table:

CREATE TABLE recipeingredients
(
recipe_id INT(10) UNSIGNED NOT NULL,
subfoodgroup_id INT(10) UNSIGNED NOT NULL,
keepingredient ENUM ("Show","Hide"),
PRIMARY KEY (recipe_id, subfoodgroup_id),
INDEX (recipe_id),
FOREIGN KEY (recipe_id) REFERENCES recipes (recipe_id),
INDEX (subfoodgroup_id),
FOREIGN KEY (subfoodgroup_id) REFERENCES subfoodgroups (subfoodgroup_id)
)
ENGINE=INNODB;

INSERT INTO `recipeingredients` (`recipe_id`, `subfoodgroup_id`, `keepingredient`) VALUES ('5', '413', 'Show');

Obviously there's a key 5 and a key 413 in the foreign tables.

^Bart
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

^Bart wrote:

I'm sorry but I don't understand the error in the object when I add data
in the next table:

CREATE TABLE recipeingredients
(
recipe_id INT(10) UNSIGNED NOT NULL,
subfoodgroup_id INT(10) UNSIGNED NOT NULL,
keepingredient ENUM ("Show","Hide"),
PRIMARY KEY (recipe_id, subfoodgroup_id),
INDEX (recipe_id),
FOREIGN KEY (recipe_id) REFERENCES recipes (recipe_id),
INDEX (subfoodgroup_id),
FOREIGN KEY (subfoodgroup_id) REFERENCES subfoodgroups (subfoodgroup_id)
)
ENGINE=INNODB;

INSERT INTO `recipeingredients` (`recipe_id`, `subfoodgroup_id`, `keepingredient`) VALUES ('5', '413', 'Show');

Obviously there's a key 5 and a key 413 in the foreign tables.

And, according to the error message, there's already an entry in your recipeingredients table with recipe_id of 5 and subfoodgroup_id of 413.

Try this:
SELECT * FROM recipeingredients
WHERE recipe_id = 5 AND subfoodgroup_id = 413;

This should tell you what's already there.

As for your INSERT, you could always
INSERT INTO recipeingredients (recipe_id,subfoodgroup_id,keepingredient)
VALUES (5, 413, 'Show')
ON DUPLICATE KEY UPDATE keepingredient = 'Show';
--
Lew Pitcher
"In Skills, We Trust"
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 05/12/2019 20.31, ^Bart wrote:

I'm sorry but I don't understand the error in the object when I add data
in the next table:

CREATE TABLE recipeingredients
(
recipe_id INT(10) UNSIGNED NOT NULL,
subfoodgroup_id INT(10) UNSIGNED NOT NULL,
keepingredient ENUM ("Show","Hide"),
PRIMARY KEY (recipe_id, subfoodgroup_id),
INDEX (recipe_id),
FOREIGN KEY (recipe_id) REFERENCES recipes (recipe_id),
INDEX (subfoodgroup_id),
FOREIGN KEY (subfoodgroup_id) REFERENCES subfoodgroups (subfoodgroup_id)
)
ENGINE=INNODB;

INSERT INTO `recipeingredients` (`recipe_id`, `subfoodgroup_id`, `keepingredient`) VALUES ('5', '413', 'Show');

Obviously there's a key 5 and a key 413 in the foreign tables.

You have said your primary key is the combination of recipe_id and subfoodgroup_id, which means you can only have one of each combination
of recipe_id and subfoodgroup_id in the recipeingredients table.

So you can't have two rows with recipe_id = 5 and subfoodgroup_id = 413,
no matter what the value keepingredient is set to.


Of course you need the recipe_id in the recipes and the subfoodgroup_id
in subfoodgroups, if you are missing a value in those tables when you
try to inser something into recipeingredients table, then you will get
another error message than the 1062.


If you want the row to be updated if it already is there, then you can use

INSERT INTO recipeingredients (recipe_id, subfoodgroup_id,
keepingredient) VALUES(5, 413, 'Show') ON DUPLICATE KEY UPDATE keepingredient='Show'

If you think that the value shouldn't be changed, then ignore the error
you got when you made your original duplicate insert (without the on duplicate...).
--

//Aho
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

And, according to the error message, there's already an entry in your recipeingredients table with recipe_id of 5 and subfoodgroup_id of 413.

MariaDB [mydb]> select * from recipeingredients;
Empty set (0.001 sec)

As for your INSERT, you could always
INSERT INTO recipeingredients (recipe_id,subfoodgroup_id,keepingredient)
VALUES (5, 413, 'Show')
ON DUPLICATE KEY UPDATE keepingredient = 'Show';

From command line I did:

MariaDB [mydb]> INSERT INTO recipeingredients (recipe_id,subfoodgroup_id,keepingredient)
-> VALUES (5, 413, 'Show')
-> ON DUPLICATE KEY UPDATE keepingredient = 'Show';
Query OK, 1 row affected (0.007 sec)

From phpmyadmin I did:

INSERT INTO `recipeingredients` (`recipe_id`, `subfoodgroup_id`, `keepingredient`) VALUES ('1', '414', 'Hide') ON DUPLICATE KEY UPDATE keepingredient = 'Hide';

I should add ON DUPLICATE KEY UPDATE keepingredient = 'VALUE' but when I
try to insert data in phpmyadmin there isn't this feature and when I try
to import from phpmyadmin I check "ON DUPLICATE KEY UPDATE" but it
doesn't work!

My server said:
10.3.18-MariaDB-0+deb10u1 - Debian 10
Apache/2.4.38 (Debian)
PHP: 7.3.11-1~deb10u1
phpMyAdmin 4.8.5 (4.9.2 avalaible)

^Bart
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

You have said your primary key is the combination of recipe_id and subfoodgroup_id, which means you can only have one of each combination
of recipe_id and subfoodgroup_id in the recipeingredients table.

So you can't have two rows with recipe_id = 5 and subfoodgroup_id = 413,
no matter what the value keepingredient is set to.

I know I can't have two rows with recipe_id = 5 and subfoodgroup_id =
413, I started with a empty table and I did this issue with the first insert... :\

Of course you need the recipe_id in the recipes and the subfoodgroup_id
in subfoodgroups, if you are missing a value in those tables when you
try to inser something into recipeingredients table, then you will get another error message than the 1062.

Yes I know it! :)

If you want the row to be updated if it already is there, then you can use

INSERT INTO recipeingredients (recipe_id, subfoodgroup_id,
keepingredient) VALUES(5, 413, 'Show') ON DUPLICATE KEY UPDATE keepingredient='Show'

If you think that the value shouldn't be changed, then ignore the error
you got when you made your original duplicate insert (without the on duplicate...).

Like what I said above I started with a empty table and I started to
import data from a *.csv in phpmyadmin and I had immediately the error
so the import didn't work...

I tried to add a single record, phpmyadmin wrotes the code without "ON DUPLICATE KEY UPDATE", I had the error but the record was in the table!

I understood I can't enable "ON DUPLICATE KEY UPDATE" when I use import feature in phpmyadmin and I should use the MariaDB's command line.

Regards.
^Bart

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 05/12/2019 22.02, ^Bart wrote:

Like what I said above I started with a empty table and I started to
import data from a *.csv in phpmyadmin and I had immediately the error
so the import didn't work...

If I don't recall it wrong, phpmyadmin uses transactions when using
innodb tables, so if you have an error while importing, it will rollback
the transaction and those the table will be empty when you look at it.

I do suggest you take a look at your csv file and I'm quite sure you
will find two entries with the recipe_id=5 and subfoodgroup_id=413.

Suggest you load it in LibreOffice, sort it first on the recipe_id
column and then the subfoodgroup_id column and you will easilly find
your duplicates.
--

//Aho
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

If I don't recall it wrong, phpmyadmin uses transactions when using
innodb tables, so if you have an error while importing, it will rollback
the transaction and those the table will be empty when you look at it.

If I force the error I can upload a single record but it doesn't work on
*.csv import... :\

I do suggest you take a look at your csv file and I'm quite sure you
will find two entries with the recipe_id=5 and subfoodgroup_id=413.

Suggest you load it in LibreOffice, sort it first on the recipe_id
column and then the subfoodgroup_id column and you will easilly find
your duplicates.

My *.csv file:

,,,,,,,,,,,,,,,,,,1,52,Show
,,,,,,,,,,,,,,,,,,1,54,Show
,,,,,,,,,,,,,,,,,,1,35,Show
,,,,,,,,,,,,,,,,,,1,37,Hide
,,,,,,,,,,,,,,,,,,1,39,Hide
,,,,,,,,,,,,,,,,,,1,34,Show

I don't know why I have a lot of ,,,,, I happened just with this file, I
just tried with:

1,52,Show
1,54,Show
1,35,Show
1,37,Hide
1,39,Hide
1,34,Show

It works (I tried it on import area of phpmyadmin), maybe there's
something wrong on my *.ods file or when I save it as *.csv.

Regards.
^Bart

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

I don't know why I have a lot of ,,,,, I happened just with this file, I just tried with:

"It happened" I'm sorry but it's quite late and... I'm very tired! :)

^Bart
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 05/12/2019 23.05, ^Bart wrote:

If I don't recall it wrong, phpmyadmin uses transactions when using
innodb tables, so if you have an error while importing, it will
rollback the transaction and those the table will be empty when you
look at it.

If I force the error I can upload a single record but it doesn't work on *.csv import... :\

I do suggest you take a look at your csv file and I'm quite sure you
will find two entries with the recipe_id=5 and subfoodgroup_id=413.

Suggest you load it in LibreOffice, sort it first on the recipe_id
column and then the subfoodgroup_id column and you will easilly find
your duplicates.

My *.csv file:

,,,,,,,,,,,,,,,,,,1,52,Show
,,,,,,,,,,,,,,,,,,1,54,Show
,,,,,,,,,,,,,,,,,,1,35,Show
,,,,,,,,,,,,,,,,,,1,37,Hide
,,,,,,,,,,,,,,,,,,1,39,Hide
,,,,,,,,,,,,,,,,,,1,34,Show

sed 's/,*\([0-9].*\)/\1/' -i faultyfile.csv

this will remove the leading ,

if you want to remove the duplicates you can run

sort -u faultyfile.csv > noduplicates.csv

of course this is how you do it in the worlds most used OS or an Unix,
if you happen to have that Redmond based company's OS, then you need to install a lot of extra stuff.

I don't know why I have a lot of ,,,,, I happened just with this file, I just tried with:

1,52,Show
1,54,Show
1,35,Show
1,37,Hide
1,39,Hide
1,34,Show

It works (I tried it on import area of phpmyadmin), maybe there's
something wrong on my *.ods file or when I save it as *.csv.

Then you solved your issue.
--

//Aho

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

sed 's/,*\([0-9].*\)/\1/' -i faultyfile.csv

this will remove the leading ,

if you want to remove the duplicates you can run

sort -u faultyfile.csv > noduplicates.csv

of course this is how you do it in the worlds most used OS or an Unix,
if you happen to have that Redmond based company's OS, then you need to install a lot of extra stuff.

I'm a Debian user, I didn't know sed command, thanks to write it! :)

Then you solved your issue.

I tried on my old *.ods file to select all fields and to cancel format
to have a default layout but I solved just to make a new *.ods file and
after it I did a right *.csv export.

Thanks for all your replies! :)

^Bart
--- Synchronet 3.21d-Linux NewsLink 1.2