[BUG] Error occurs when using --innodb-optimize-keys and there is a column having a foreign key and part of a generated column

[Zombaya]

Describe the bug

There is a column, which has a foreign key relation to another table. It is also used in a generated column.
When importing the database using --innodb-optimize-keys, it will trigger an error: Error restoring database.table from file (null): Cannot add foreign key on the base column of stored column.

To Reproduce

Command executed:

• mydumper -u root -a -B mydumper_bug_foreign_key_autogenerated
• myloader -d export-20210812-092600/ -u root -a -B test_mydumper_import_3 --innodb-optimize-keys

Summary of database-structure

offices

Column	Type	Description
id	int	autogenerated primary key

users

Column	Type	Description
id	int	autogenerated primary key
office_id	int	foreign key to offices.id
slogan	text	stored generated column: CONCAT('Hello world #',office_id)

Expected behavior

I expect the import to finish successfully.

When omitting the flag --innodb-optimize-keys, the import will be successful.

Log

log.txt

Backup

mydumper-export.tar.gz

Environment

• OS version: Fedora 33 (Workstation Edition)
• MyDumper version: 0.10.7, built against MySQL 5.7.33-36
• MySQL version: Ver 15.1 Distrib 10.4.20-MariaDB, for Linux (x86_64)

Additional information

For as far as I can see, all the data is actually imported, only the indexes are not applied for tables experiencing this kind of problem.

[Zombaya]

(Other than this minor issue, kudos for the --innodb-optimize-keys-flag, it's speeding up an import that used to take 8 hours to a 1h import, a difference that will only grown in time as the database is expanding. 👍 🥇 🙏 )

[davidducos]

Hi @Zombaya, Thanks for file this bug as --innodb-optimize-keys is one of the latest and most important feature, but it is also the one that has receive a lot of changes. Can you check/compile the master version? as v0.10.7 was an earlier release with this feature and v0.10.9 we slightly change it to fix several issues.
Please, let me know

[Zombaya]

I was able to compile the master-version (a330520) and reproduce the issue.

Log

$ git log --oneline | head -n 1
a330520 Merge pull request #394 from maxbube/no_dir_rewind
$ cmake . -DWITH_SSL=OFF                    # I was not able to build with SSL
$ make
$ cd ../test-directory
$ ../maxbube-mydumper/myloader -d export-20210812-092600/ -u root -a -B test_mydumper_import_7 --innodb-optimize-keys
** (myloader:42534): CRITICAL **: 14:06:48.595: Error restoring test_mydumper_import_7.users from file (null): Cannot add foreign key on the base column of stored column
$ ../maxbube-mydumper/myloader --version
myloader 0.10.8, built against MySQL 10.5.5

[Zombaya]

@davidducos, as a heads-up, I'm using MariaDB, not mysql. I forgot to mention it before. I've edited the original post as well.

Version: mysql Ver 15.1 Distrib 10.4.20-MariaDB, for Linux (x86_64)

[davidducos]

Hi @Zombaya,

This is my test case:

create database issues_395;
use issues_395;

CREATE TABLE `offices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `office_id` int(11) DEFAULT NULL,
  `slogan` text GENERATED ALWAYS AS (concat('Hello world #',`office_id`)) STORED,
  PRIMARY KEY (`id`),
  KEY `office_id` (`office_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

(Changed from Virtual to Stored)

insert into offices values();
insert into offices values();
insert into offices values();
insert into offices values();

insert into users (office_id) values (1);
insert into users (office_id) values (2);
insert into users (office_id) values (3);

mysql> select * from offices;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select * from users;
+----+-----------+----------------+
| id | office_id | slogan         |
+----+-----------+----------------+
|  1 |         1 | Hello world #1 |
|  2 |         2 | Hello world #2 |
|  3 |         3 | Hello world #3 |
+----+-----------+----------------+
3 rows in set (0.00 sec)

Then I'm exporting and exporting the database:

git clone https://github.com/maxbube/mydumper.git
cd mydumper/
cmake .
make
./mydumper -B issues_395 -o backup -v 4
./myloader -B issues_395_2 -o -d backup -v 4  --innodb-optimize-keys

I see no error on PS 5.7. It might be affecting only to MariaDB. I will be installing a MariaDB and test again.

[Zombaya]

@davidducos , with your script I'm unable to reproduce the issue so something is different. I'll try to modify the script to see what I need to change to make it fail and post my findings.

[Zombaya]

@davidducos , the script you provided, used a virtual generated column. When using this, the problem also did not occur on my end.

I was using a stored generated column, meaning the data about it will be stored on disk instead of calculated every time the data is requested.

DROP DATABASE `issues_395`;
CREATE DATABASE IF NOT EXISTS `issues_395` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `issues_395`;

CREATE TABLE `offices` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `users` (
                         `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                         `office_id` int(11) DEFAULT NULL,
                         `slogan` varchar(64) GENERATED ALWAYS AS (concat('Hello office #',`office_id`)) STORED,
                         CONSTRAINT `users_FK` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `offices` (`id`) VALUES
                                 (1),
                                 (2);

INSERT INTO `users` (`office_id`) VALUES
                                      (1),
                                      (1),
                                      (1),
                                      (2);

SELECT * FROM offices;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
SELECT * FROM users;
+----+-----------+-----------------+
| id | office_id | slogan          |
+----+-----------+-----------------+
|  1 |         1 | Hello office #1 |
|  2 |         1 | Hello office #1 |
|  3 |         1 | Hello office #1 |
|  4 |         2 | Hello office #2 |
+----+-----------+-----------------+

Exporting and importing

$ ../maxbube-mydumper/mydumper -B issues_395 -o backup -v 4 -u root -a
$ ../maxbube-mydumper/myloader -B issues_395_2 -o -d backup -v 4  --innodb-optimize-keys -u root -a
Enter MySQL Password: 
** Message: 16:06:24.642: Dropping table or view (if exists) `issues_395_2`.`offices`
** Message: 16:06:24.656: Creating table `issues_395_2`.`offices`
** Message: 16:06:24.657: Fast index creation will be use for table: issues_395_2.offices
** Message: 16:06:24.695: Dropping table or view (if exists) `issues_395_2`.`users`
** Message: 16:06:24.708: Creating table `issues_395_2`.`users`
** Message: 16:06:24.710: Fast index creation will be use for table: issues_395_2.users
** Message: 16:06:24.734: Thread 1 restoring `issues_395`.`users` part 0 of 1. Progress 1 of 2 .
** Message: 16:06:24.735: Thread 2 restoring `issues_395`.`offices` part 0 of 1. Progress 2 of 2 .
** Message: 16:06:24.739: Thread 1 restoring indexes `issues_395`.`users`
** Message: 16:06:24.740: 4 threads created
** Message: 16:06:24.752: Thread 2 restoring indexes `issues_395`.`offices`
** Message: 16:06:24.769: Thread 1 shutting down
** Message: 16:06:24.769: Thread 3 shutting down
** Message: 16:06:24.769: Thread 4 restoring contraints `issues_395`.`users`
** Message: 16:06:24.769: Thread 2 shutting down

** (myloader:46056): CRITICAL **: 16:06:24.773: Error restoring issues_395_2.users from file (null): Cannot add foreign key on the base column of stored column 

** Message: 16:06:24.773: Thread 4 shutting down
** Message: 16:06:24.773: Import timings:
** Message: 16:06:24.774: Data      	| Index    	| Total   	| Table
** Message: 16:06:24.774: 0 00:00:00	| 0 00:00:00	| 0 00:00:00	| `issues_395`.`users`
** Message: 16:06:24.774: 0 00:00:00	| 0 00:00:00	| 0 00:00:00	| `issues_395`.`offices`
** Message: 16:06:24.774: Starting table checksum verification

[davidducos]

I checked with MariaDB 10.4.21 and it is not failing. Can you execute myloader with general_log = ON and upload the file?

Thanks!

[Zombaya]

I added the general_log-setting to my mariadb-configuration. If I need to somehow add it to the myloader-command, please let me know how.

The mariadb-general.log as requested.

[davidducos]

Hi @Zombaya, I installed MariaDB 10.4.20, as I thought that it might be a version related issue, but it is working too. Can you share the configuration file of the server?
I will set this ticket as invalid for now, as this is not related to a MyDumper bug, it might be related to a MariaDB behaviour or bug.
BTW, I was able to find another issue on myloader, thank you for that!! :D

[Zombaya]

As requested, mariadb-server.cnf.txt.

For as for as I'm aware, the only thing that is non-default is the sql_mode. I've retried it myself with the default sql_mode and was able to reproduce the issue.

I'll try to toy around with docker to see if I'm able to reproduce it that way and see what influences the reproducibility. No idea when I will be able to work on this so I'm not able to specify a timespan for when you would be receiving more info from my end.

Thanks for taking the time to look into it and super that it at least lead to fixing/finding another bug :-)

[fredricj]

I can reproduce this on a default installation of mariadb-10.3 on centos 8 following the database in this comment. Adding the constraint manually with:
ALTER TABLE `issues_395_2`.`users` ADD CONSTRAINT `users_FK` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
works but when myloader runs it, it doesnt.

[fredricj]

It fails due to FOREIGN_KEY_CHECKS=0 . If FOREIGN_KEY_CHECKS=1 then the ADD CONSTRAINT works correctly

[davidducos]

@fredricj @Zombaya Ooohhhhh!!!

MariaDB [issues_395_5]> alter table users drop constraint users_ibfk_1, drop index office_id;
Query OK, 0 rows affected (0.016 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [issues_395_5]> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.000 sec)

MariaDB [issues_395_5]> ALTER TABLE `issues_395_5`.`users`   ADD  CONSTRAINT `users_FK` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 138 (0A000): Cannot add foreign key on the base column of stored column

and this is happening on Percona Server too.
I'm still not sure why I'm not able to see the error on my own myloader executions, I was able to reproduce only when I execute through mysql client.

[davidducos]

I found why my tests are working.
I have just realized that yours SHOW CREATE TABLE has a ON DELETE CASCADE ON UPDATE CASCADE on the foreign key constraint definition.

[davidducos]

I can offer 2 workarounds:
1- do not use --innodb-optimize-keys
2- delete a space in the -schema file on the constraint line. That will force to create the constraint when the table is created.

or, you can wait until we implement #389

[Zombaya]

I created a bash-function for workaround 2.

Function

function dontOptimizeInnoDBKeysForTablesWithStoredColumns()
{
    DIRNAME="$1"

    if [ -z "$DIRNAME" ]; then
        echo "Please provide dirname of export to fix"
        return 1
    fi

    if [ ! -d "$DIRNAME" ]; then
        echo "Directory '$DIRNAME' does not exist"
        return 1
    fi

    FILES=$(grep --with-filename --fixed-strings "STORED" -l "$DIRNAME"/*-schema.sql)

    for FILE in $FILES
    do
        # echo "Fixing $FILE"
        sed -i 's/^  CONSTRAINT/CONSTRAINT/g' "$FILE"
    done
}

Usage

$ mydumper -B issues_395 -o backup -u root -a
$ dontOptimizeInnoDBKeysForTablesWithStoredColumns backup
$ myloader -B issues_395_2 -o -d backup --innodb-optimize-keys -u root -a

🎉 for being able to continue for now and enjoying the speedboost for almost all tables.

[davidducos]

@Zombaya I will be closing this issue as we are going to do the fix and following on #389