i have a procedure i run quite regularly that uses this syntax:

SET FOREIGN_KEY_CHECKS=0;

but today when i upgraded from 10.5.13 to

Server version: 10.5.22-MariaDB MariaDB Server

suddenly i keep getting this error:

ERROR 1833 (HY000) at line 9: Cannot change column 'xx': used in a foreign key constraint 'xx' of table 'xx.xx'

this is how i solved the issue several years ago as per stackexchange

so today i tried this stackoverflow suggestion instead:

SET FOREIGN_KEY_CHECKS = 0;
SET GLOBAL FOREIGN_KEY_CHECKS=0;

and that did not fix it.

any thoughts or suggestions? has anybody else experienced that FOREIGN_KEY_CHECKS does not appear to disable anymore?

MariaDB Server 10.5.22 includes a fix of MDEV-31086. Please look at MDEV-31987.

Does the approach that is described in MDEV-31987 solve the problem?

hi - thanks for following up. i am currrently on the road and have not studied the solution.

however, i am not really altering the key at all, but rather just "moving" the column around with the BEFORE or AFTER suffix:

ALTER TABLE MODIFY COLUMN...... *AFTER *column-name;

so, i am wondering it it would be better for me to just move one of the non-key columns instead.

side-note: i am surprised there is not a much easier way to move an index column. but maybe this is a stack-overflow question (except they keep down-voting me)

Once you have a chance to post a self-contained test case (starting with CREATE TABLE), I should be able to help with an alternative solution.

For the record, MDEV-15562 allows the column list to be reordered without rebuilding the table. Nothing will really change in the storage format.

hello -

first off, THANK YOU for your patience with me. it appears when switching from 10.5.13 to 10.5.22, there is a new rule being enforced that was not previously enforced.

i have switched back & forth between 5.13 and 5.22, and somehow the error does not show up in 5.13. the error of course is pure carelessness on my part, but i am still surprised this error was not caught *much *earlier.

my installation instructions:

/home/mark/mariadb_repo_setup --mariadb-server-version="mariadb-10.5.22" ; dnf -y install mariadb-server ; systemctl start mariadb.service ;

mariadb --verbose < ./myTestScript.sql ; ## shows error

systemctl stop mariadb.service ; dnf --assumeyes remove mariadb-server ;

/home/mark/mariadb_repo_setup --mariadb-server-version="mariadb-10.5.13" ; dnf -y install mariadb-server ; systemctl start mariadb.service ;
mariadb --verbose < ./myTestScript.sql ; ## does NOT show error!

./myTestScript.sql below:

DROP DATABASE IF EXISTS comptonTransAnlys ;
CREATE DATABASE comptonTransAnlys;
USE comptonTransAnlys ;

– MariaDB dump 10.19 Distrib 10.5.13-MariaDB, for Linux (x86_64)
–
-- Host: localhost Database: comptonTransAnlys
– ------------------------------------------------------
– Server version 10.5.13-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

–
-- Table structure for table `stimwordPosition`
–

DROP TABLE IF EXISTS `stimwordPosition`;

CREATE TABLE `stimwordPosition` (
`stimwordPositionAutoIncr` mediumint(10) NOT NULL,
PRIMARY KEY (`stimwordPositionAutoIncr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
–
-- Table structure for table `clientStimword`
–

DROP TABLE IF EXISTS `clientStimword`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `clientStimword` (

`stimwordPositionAutoIncr` mediumint(10) NOT NULL,

`clientStimwordAutoIncr` int(10) unsigned NOT NULL AUTO_INCREMENT,
`createdAt` timestamp NULL DEFAULT NULL,
`updatedAt` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`clientStimwordAutoIncr`),

KEY `stimwordPositionAutoIncr_2_clientStimword` (`stimwordPositionAutoIncr`),
CONSTRAINT `stimwordPositionAutoIncr_2_clientStimword` FOREIGN KEY (`stimwordPositionAutoIncr`) REFERENCES `stimwordPosition` (`stimwordPositionAutoIncr`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=8288165 DEFAULT CHARSET=latin1;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2023-11-16 15:25:55

SET FOREIGN_KEY_CHECKS=0;

/*this can be fixed by changing UNSIGNED to SIGNED! */
ALTER TABLE `clientStimword` MODIFY COLUMN `stimwordPositionAutoIncr` MEDIUMINT(10) UNSIGNED NOT NULL AFTER `updatedAt` ;

SET FOREIGN_KEY_CHECKS=1 ;

edwardsmarkf, thank you for the clarification.

The last ALTER TABLE statement in your comment is not only moving the position of the column in the table definition (for things like SELECT * FROM clientStimword) but also changing the data type from MEDIUMINT (signed by default) to MEDIUMINT UNSIGNED. This would never be allowed via the native ALTER TABLE of InnoDB, because it does not implement any data format conversions, apart from adding or removing NOT NULL, extending VARCHAR or changing some character set encodings to compatible ones. It would use ALGORITHM=COPY a.k.a. copy_data_between_tables().

Before the fix of MDEV-31086, the ALGORITHM=COPY code path ignored data type mismatch in FOREIGN KEY constraints. Now, it is correctly catching the sign mismatch. I did not check how InnoDB would work when a sign mismatch is present. Because the sign bit is inverted, a 24-bit signed integer 1 would be stored as 0x800001 while an unsigned integer 1 would be stored as 0x000001. I would expect these fields to be compared as binary strings, and therefore the constraint not to work even for nonnegative integer values.

I remember from my time at Oracle when implementing online ALTER TABLE for InnoDB in MySQL 5.6 that people (including myself) would complain that the ALTER TABLE…CHANGE COLUMN and ALTER TABLE…MODIFY COLUMN are error-prone because they force the entire data type definition to be duplicated. Even a small difference, such as forgetting or adding NOT NULL or UNSIGNED would cause the column data type to be changed, if the intention was to only rename the column.

I think that CHANGE COLUMN differs from MODIFY only by the need to specify a possibly new name for the column. MariaDB got RENAME COLUMN (MDEV-16290) and RENAME INDEX (MDEV-7318) later.

serg, since you are familiar with the SQL standard, maybe you could comment on whether it would make sense to introduce ALTER TABLE…MOVE COLUMN name ⟦AS new_name⟧ ⟮FIRST|AFTER col_name⟯ syntax.

There is no "MOVE COLUMN" in the standard, there are (reverse-engineered from BNF, omitting features we don't have):

ALTER TABLE ... ALTER COLUMN ... SET DEFAULT ... ;

ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT;

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL;

ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL;

ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE ...;

ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION;

So, MOVE doesn't exist, although if we'd wanted to add it, a standard-alike syntax could've been

ALTER TABLE ... ALTER COLUMN ... SET POSITION { FIRST | AFTER ... }