alter table raises errno: 168 "Unknown (generic) error from engine"

[mg64ve]

Hello, I am using MariaDB official image in my environment with django. The following SQL statement:

ALTER TABLEjobs_adsearchprofileADD COLUMNprofile_task_idinteger NULL UNIQUE , ADD CONSTRAINTjobs_adsearchprofile_profile_task_id_bb62e65a_fk_django_ceFOREIGN KEY (profile_task_id) REFERENCESdjango_celery_beat_periodictask(id);

gives me the following error:

ERROR 1025 (HY000): Error on rename of './jobs/jobs_adsearchprofile' to './jobs/#sql-backup-1-41' (errno: 168 "Unknown (generic) error from engine")

mysqld is running as user mysql and the following are the file permissions:

$ ls -ltr
total 52984
-rw-rw---- 1 mysql mysql       65 Aug 22 18:29 db.opt
-rw-rw---- 1 mysql mysql     1026 Aug 22 21:08 alembic_version.frm
-rw-rw---- 1 mysql mysql     1888 Aug 22 21:08 auth_group.frm
-rw-rw---- 1 mysql mysql     1974 Aug 22 21:08 auth_group_permissions.frm
-rw-rw---- 1 mysql mysql     2568 Aug 22 21:08 auth_permission.frm
-rw-rw---- 1 mysql mysql     4210 Aug 22 21:08 auth_user.frm
-rw-rw---- 1 mysql mysql     1968 Aug 22 21:08 auth_user_groups.frm
-rw-rw---- 1 mysql mysql     1973 Aug 22 21:08 auth_user_user_permissions.frm
-rw-rw---- 1 mysql mysql     2753 Aug 22 21:08 django_admin_log.frm
-rw-rw---- 1 mysql mysql     2068 Aug 22 21:08 django_content_type.frm
-rw-rw---- 1 mysql mysql     2538 Aug 22 21:08 django_migrations.frm
-rw-rw---- 1 mysql mysql     1619 Aug 22 21:09 django_session.frm
-rw-rw---- 1 mysql mysql     1987 Aug 22 21:09 jobs_adsearchprofile_profile_keys.frm
-rw-rw---- 1 mysql mysql     1982 Aug 22 21:09 jobs_country_country_language.frm
-rw-rw---- 1 mysql mysql     1982 Aug 22 21:09 jobs_countrylanguage.frm
-rw-rw---- 1 mysql mysql     1787 Aug 22 21:09 jobs_cvkey.frm
-rw-rw---- 1 mysql mysql    98304 Oct 14 13:53 alembic_version.ibd
-rw-rw---- 1 mysql mysql   114688 Oct 14 13:53 auth_group.ibd
-rw-rw---- 1 mysql mysql   131072 Oct 14 13:53 auth_group_permissions.ibd
-rw-rw---- 1 mysql mysql   131072 Oct 14 13:53 auth_user_groups.ibd
-rw-rw---- 1 mysql mysql   131072 Oct 14 13:53 auth_user_user_permissions.ibd
-rw-rw---- 1 mysql mysql   131072 Oct 14 13:53 jobs_countrylanguage.ibd
-rw-rw---- 1 mysql mysql   131072 Oct 14 13:53 jobs_country_country_language.ibd
-rw-rw---- 1 mysql mysql     1006 Dec 21 17:20 jobs_adsearchprofile_profile_running_days.frm
-rw-rw---- 1 mysql mysql     1701 Dec 21 17:20 jobs_jobboard.frm
-rw-rw---- 1 mysql mysql     1443 Dec 21 17:20 jobs_days.frm
-rw-rw---- 1 mysql mysql     1500 Dec 21 17:20 jobs_adsearchprofile_profile_jobboard.frm
-rw-rw---- 1 mysql mysql     1499 Dec 21 17:40 jobs_adsearchprofile_profile_country.frm
-rw-rw---- 1 mysql mysql   114688 Dec 22 09:05 jobs_jobboard.ibd
-rw-rw---- 1 mysql mysql   114688 Dec 22 09:05 jobs_days.ibd
-rw-rw---- 1 mysql mysql   131072 Dec 22 09:05 jobs_country.ibd
-rw-rw---- 1 mysql mysql   114688 Dec 22 09:05 django_content_type.ibd
-rw-rw---- 1 mysql mysql   114688 Dec 22 09:05 auth_permission.ibd
-rw-rw---- 1 mysql mysql     2267 Dec 22 13:17 jobs_country.frm
-rw-rw---- 1 mysql mysql     1588 Dec 22 13:19 jobs_language.frm
-rw-rw---- 1 mysql mysql     5985 Dec 22 13:21 jobs_cv.frm
-rw-rw---- 1 mysql mysql     1877 Dec 22 13:21 jobs_status.frm
-rw-rw---- 1 mysql mysql     5779 Dec 22 13:26 jobs_coverletter.frm
-rw-rw---- 1 mysql mysql    18316 Dec 22 14:02 jobs_jobposting.frm
-rw-rw---- 1 mysql mysql     1000 Jan 13 17:16 django_celery_beat_intervalschedule.frm
-rw-rw---- 1 mysql mysql      963 Jan 13 17:16 django_celery_beat_periodictasks.frm
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 jobs_language.ibd
-rw-rw---- 1 mysql mysql   147456 Jan 13 17:31 jobs_cv.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 jobs_status.ibd
-rw-rw---- 1 mysql mysql   147456 Jan 13 17:31 jobs_coverletter.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 13 17:31 django_migrations.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 13 17:31 jobs_adsearchprofile_profile_running_days.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 jobs_adsearchprofile_profile_jobboard.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 jobs_adsearchprofile_profile_country.ibd
-rw-rw---- 1 mysql mysql   131072 Jan 13 17:31 django_admin_log.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 13 17:31 jobs_adsearchprofile.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 13 17:31 jobs_jobposting.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 django_celery_beat_periodictask.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 13 17:31 django_celery_beat_intervalschedule.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 13 17:31 django_celery_beat_crontabschedule.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 13 17:31 django_celery_beat_periodictasks.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 jobs_cvkey.ibd
-rw-rw---- 1 mysql mysql   131072 Jan 13 17:31 jobs_adsearchprofile_profile_keys.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 django_session.ibd
-rw-rw---- 1 mysql mysql   114688 Jan 13 17:31 auth_user.ibd
-rw-rw---- 1 mysql mysql     1525 Jan 14 10:01 django_celery_beat_solarschedule.frm
-rw-rw---- 1 mysql mysql   114688 Jan 14 10:01 django_celery_beat_solarschedule.ibd
-rw-rw---- 1 mysql mysql     1743 Jan 14 10:01 django_celery_beat_crontabschedule.frm
-rw-rw---- 1 mysql mysql    98304 Jan 14 10:01 django_celery_beat_clockedschedule.ibd
-rw-rw---- 1 mysql mysql     3184 Jan 14 10:02 django_celery_beat_periodictask.frm
-rw-rw---- 1 mysql mysql      963 Jan 14 10:02 django_celery_beat_clockedschedule.frm
-rw-rw---- 1 mysql mysql     1649 Jan 14 10:40 jobs_adsearchprofile.frm

the /var/lib/mysql/jobs directory has the following permissions:

drwx------ 2 mysql mysql 4096 Jan 14 10:49 jobs

is the heading pound in './jobs/#sql-backup-1-41' creating this issue?

[mg64ve]

Sorry, the following solves the issue:

SET FOREIGN_KEY_CHECKS=0;

[grooverdan]

Glad you resolved this. Generic error messages are horrible. Looking at MDEV-24511, could it be show warnings\G includes other more useful messages?

[mg64ve]

Definitely the message did not help me at all. It should be more specific.