Styjun

All ASCII characters with a given bit count

Does a Draconic Bloodline sorcerer's doubled proficiency bonus for Charisma checks against dragons apply to all dragon types or only the chosen one?

What is the purpose of the side handle on a hand ("eggbeater") drill?

Marquee sign letters

Arriving in Atlanta (after US Preclearance in Dublin). Will I go through TSA security in Atlanta to transfer to a connecting flight?

What's parked in Mil Moscow helicopter plant?

What's the difference between using dependency injection with a container and using a service locator?

Variable does not exist: sObjectType (Task.sObjectType)

Co-worker works way more than he should

Putting Ant-Man on house arrest

Is a self contained air-bullet cartridge feasible?

How can I wire a 9-position switch so that each position turns on one more LED than the one before?

When I export an AI 300x60 art board it saves with bigger dimensions

In search of the origins of term censor, I hit a dead end stuck with the greek term, to censor, λογοκρίνω

Can gravitational waves pass through a black hole?

How to begin with a paragraph in latex

How was Lagrange appointed professor of mathematics so early?

Preserving file and folder permissions with rsync

Why aren't road bicycle wheels tiny?

/bin/ls sorts differently than just ls

Where to find documentation for `whois` command options?

"Working on a knee"

Israeli soda type drink

Why isn't everyone flabbergasted about Bran's "gift"?

MySQL 8.0 issue when deleting an item which is referenced in another table by a foreign key

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

1

Context:

I have two tables, let's call them A and B. B has a column reference for table A and a constraint (foreign key) set so that when deleting the referenced object, the value is set to null (ON DELETE SET NULL).

CREATE TABLE `A` (
`ID` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `B` (
 `COL_B` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN 
 DEFAULT NULL,
 CONSTRAINT `B_FK5` FOREIGN KEY (`COL_B`) REFERENCES `A` (`ID`) ON 
 DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `A` (`ID`) VALUES (‘1’);

INSERT INTO `B` (`COL_B`) VALUES (‘1’);

DELETE FROM A;

SELECT * FROM B;

Problem:
When deleting an item from table A, the reference in table B is not set to null (the key remains although the object with that key was deleted).

Extra info:
Up until now I used MySql 5.7 and didn't have this problem. After switching to MySql 8.0.15 I started to see this behaviour. Furthermore, this problem occurs only on local and CD machines and doesn't reproduce on cloud machines. As an impact, some tests started to fail when running CD jobs.

Another strange thing is that right before deleting the item, if a modification is made in table A (switch order of columns), the issue doesn't reproduce.

I've also tried to use a MySQL run on Docker and the issue is the same.
Really, this is a blocking issue and the fact that it happens only in this case makes it more difficult to understand. From MySQL logs I couldn't get squat.

Any clue that could help with this investigation?

mysql

share|improve this question

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

asked Mar 22 at 14:55

cozma.andicozma.andi

165

add a comment | 

0

1

Context:

I have two tables, let's call them A and B. B has a column reference for table A and a constraint (foreign key) set so that when deleting the referenced object, the value is set to null (ON DELETE SET NULL).

CREATE TABLE `A` (
`ID` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `B` (
 `COL_B` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN 
 DEFAULT NULL,
 CONSTRAINT `B_FK5` FOREIGN KEY (`COL_B`) REFERENCES `A` (`ID`) ON 
 DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `A` (`ID`) VALUES (‘1’);

INSERT INTO `B` (`COL_B`) VALUES (‘1’);

DELETE FROM A;

SELECT * FROM B;

Problem:
When deleting an item from table A, the reference in table B is not set to null (the key remains although the object with that key was deleted).

Extra info:
Up until now I used MySql 5.7 and didn't have this problem. After switching to MySql 8.0.15 I started to see this behaviour. Furthermore, this problem occurs only on local and CD machines and doesn't reproduce on cloud machines. As an impact, some tests started to fail when running CD jobs.

Another strange thing is that right before deleting the item, if a modification is made in table A (switch order of columns), the issue doesn't reproduce.

I've also tried to use a MySQL run on Docker and the issue is the same.
Really, this is a blocking issue and the fact that it happens only in this case makes it more difficult to understand. From MySQL logs I couldn't get squat.

Any clue that could help with this investigation?

mysql

share|improve this question

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

asked Mar 22 at 14:55

cozma.andicozma.andi

165

add a comment | 

Context:

I have two tables, let's call them A and B. B has a column reference for table A and a constraint (foreign key) set so that when deleting the referenced object, the value is set to null (ON DELETE SET NULL).

CREATE TABLE `A` (
`ID` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `B` (
 `COL_B` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN 
 DEFAULT NULL,
 CONSTRAINT `B_FK5` FOREIGN KEY (`COL_B`) REFERENCES `A` (`ID`) ON 
 DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `A` (`ID`) VALUES (‘1’);

INSERT INTO `B` (`COL_B`) VALUES (‘1’);

DELETE FROM A;

SELECT * FROM B;

Problem:
When deleting an item from table A, the reference in table B is not set to null (the key remains although the object with that key was deleted).

Extra info:
Up until now I used MySql 5.7 and didn't have this problem. After switching to MySql 8.0.15 I started to see this behaviour. Furthermore, this problem occurs only on local and CD machines and doesn't reproduce on cloud machines. As an impact, some tests started to fail when running CD jobs.

Another strange thing is that right before deleting the item, if a modification is made in table A (switch order of columns), the issue doesn't reproduce.

I've also tried to use a MySQL run on Docker and the issue is the same.
Really, this is a blocking issue and the fact that it happens only in this case makes it more difficult to understand. From MySQL logs I couldn't get squat.

Any clue that could help with this investigation?

mysql

share|improve this question

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

asked Mar 22 at 14:55

cozma.andicozma.andi

165

CREATE TABLE `A` (
`ID` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `B` (
 `COL_B` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN 
 DEFAULT NULL,
 CONSTRAINT `B_FK5` FOREIGN KEY (`COL_B`) REFERENCES `A` (`ID`) ON 
 DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `A` (`ID`) VALUES (‘1’);

INSERT INTO `B` (`COL_B`) VALUES (‘1’);

DELETE FROM A;

SELECT * FROM B;

share|improve this question

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

asked Mar 22 at 14:55

cozma.andicozma.andi

165

share|improve this question

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

asked Mar 22 at 14:55

cozma.andicozma.andi

165

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

edited Mar 22 at 18:53

snakecharmerb

12.5k42553

asked Mar 22 at 14:55

cozma.andicozma.andi

165

asked Mar 22 at 14:55

cozma.andicozma.andi

165