Ошибка 1091 mysql

Исходная структура данных таблицы:

Моя первоначальная идея заключалась в том, чтобы изменить имя поля Father_phone непосредственно на phone и установить его непустое, а затем удалить уникальность.

Я использую следующую инструкцию для изменения имени поля

alter table students change father_phone phone varchar(11) not null;

Результат:
По результатам выяснилось, что переименование и непустое значение поля были установлены, и следующим шагом является удаление уникальности. Я удаляю его с помощью следующего кода:

alter table students drop index phone;

Тогда возникает ошибка:

Решение:

show keys from students G;

Результат выглядит следующим образом:

На этом этапе вы можете обнаружить, что исходный Father_phone был переименован в phone. Хотя имя и непустота были изменены, владельцем этого уникального атрибута или Father_phone не является phone, поэтому будет сообщено об ошибке.
На этом этапе просто введите следующий код:

alter table students drop index father_phone;

Посмотрите на результат:

На этом этапе выясняется, что уникальный полевой телефон был удален.

I have a table which looks like this:

CREATE TABLE `articles` 
             ( 
                          `article_id` INT(10) NOT NULL auto_increment, 
                          `pubsrc_id`  INT(10) NOT NULL, 
                          `pub_id`     VARCHAR(16) NOT NULL
                          PRIMARY KEY (`article_id`), 
                          UNIQUE KEY `pubsrc_id` (`pubsrc_id`,`pub_id`), 
                          CONSTRAINT `pub_articles_ibfk_1` FOREIGN KEY (`pubsrc_id`) REFERENCES `pub_sources` (`pubsrc_id`) ON
             DELETE RESTRICT 
             ON 
             UPDATE RESTRICT, 
                    CONSTRAINT `pub_articles_ibfk_2` FOREIGN KEY (`pubsrc_id`) REFERENCES `pub_sources` (`pubsrc_id`)
             ) 

There is another table which the previous table has a key to:

         CREATE TABLE `pub_sources` 
         ( 
                      `pubsrc_id`              INT(11) NOT NULL, 
                      `pubsrc_name`            VARCHAR(32) NOT NULL
                      PRIMARY KEY (`pubsrc_id`) 
         ) 

I am trying to drop the foregin key with the command:

SET SESSION SQL_SAFE_UPDATES = 0;
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `articles` DROP FOREIGN KEY `pubsrc_id`;

but get the following error:

Error Code: 1091. Can't DROP 'pubsrc_id'; check that column/key exists

I updated MySQL from 5.5.50 to 5.6.31 on a Centos 6.8 box with Plesk 12.5.30.
Using those steps.

The upgrade was successful but I ran into trouble with mysql_upgrade. I had to rebuild the permission table manually from a mysql db backup after a failed Plesk update between 12 and 12.5.

The Database is fully functional. I can access Plesk, run PhpMyAdmin fairly normally. And I fixed a couple of issues pertaining to the mysql tables where mysql.proc had to be recreated. But a corruption issue remains somewhere with the priviledges table, causing crashes on GRANT operations, notably database and db user controls from Plesk lead to a PleskDBException error.

When I try to run mysql_fix_privilege_tables via mysql_upgrade, I get the following log:

[root@kn1 ~]# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin psa
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
ERROR 1091 (42000) at line 1892: Can't DROP 'PRIMARY'; check that column/key exists
FATAL ERROR: Upgrade failed

And this is the associated MySQL crash occurring, reported in mysqld.log as:

7:57:48 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=402653184
read_buffer_size=1048576
max_used_connections=3
max_threads=500
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1421458 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xa17a6b8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 6c1ff2cc thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x34)[0x85384c4]
/usr/sbin/mysqld(handle_fatal_signal+0x48c)[0x82811ac]
[0xb7770500]
/usr/sbin/mysqld[0x82a1219]
/usr/sbin/mysqld(_Z11mysql_grantP3THDPKcR4ListI11st_lex_userEmbb+0x77a)[0x82ab7ea]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5d81)[0x830c7b1]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x378)[0x830dcd8]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1934)[0x830f6d4]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xe2)[0x8310e52]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x165)[0x82d6885]
/usr/sbin/mysqld(handle_one_connection+0x51)[0x82d6941]
/usr/sbin/mysqld(pfs_spawn_thread+0x173)[0x85ed563]
/lib/libpthread.so.0(+0x6bc9)[0xb7751bc9]
/lib/libc.so.6(clone+0x5e)[0xb7516dee]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (663067a8): is an invalid pointer
Connection ID (thread ID): 61
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
160624 00:57:48 mysqld_safe Number of processes running now: 0
160624 00:57:48 mysqld_safe mysqld restarted

I am not able to find anything useful on the web as far this
ERROR 1091 (42000) in direct relation to mysql_fix_privilege_tables. I’d like to try and solve this QUERY error manually for mysql_upgrade to complete, but the line alone isn’t a very helpful hint to isolate what table or column is involved.

Is there a way or useful reference for me to detect what line 1892 is. Is that part of a mysql_fix_privilege_tables.sql file I can look at?

Or any other good ideas to fix this problem?

I finally figured it out. The main reason for the crashes and the mysql_upgrade error was related to missing and/or corrupted primary keys of the mysql table.

There were also a couple missing records in the mysql.db table, which likely couldn’t update previously due to the GRANT crashes and would have been related to Plesk «Database Servers» PleskDBException errors.

I fixed/restored those manually via PhpMyAdmin in reference to the mysql_sytem_tables.sql provided by MySQL 5.6.31.

For those who might run into similar issues, the complete list to run is:

ALTER TABLE `db` DROP PRIMARY KEY, ADD PRIMARY KEY (`Host`, `Db`, `User`);
ALTER TABLE `db` ADD KEY User (`User`);
ALTER TABLE `func` ADD PRIMARY KEY(`name`);
ALTER TABLE `time_zone_name` ADD PRIMARY KEY(`Name`);
ALTER TABLE `time_zone` ADD PRIMARY KEY(`Time_zone_id`);
ALTER TABLE `time_zone_leap_second` ADD PRIMARY KEY(`Transition_time`);
ALTER TABLE `time_zone_transition` ADD PRIMARY KEY TzIdTranTime (`Time_zone_id`, `Transition_time`);
ALTER TABLE `time_zone_transition_type` ADD PRIMARY KEY TzIdTrTId (`Time_zone_id`, `Transition_type_id`);
ALTER TABLE `procs_priv` ADD PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`);
ALTER TABLE `procs_priv` ADD KEY Grantor (`Grantor`);
ALTER TABLE `plugin` ADD PRIMARY KEY(`name`);
ALTER TABLE `columns_priv` ADD PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`);
ALTER TABLE `proxies_priv` ADD PRIMARY KEY Host (`Host`,`User`,`Proxied_host`,`Proxied_user`);
ALTER TABLE `proxies_priv` ADD KEY Grantor (`Grantor`);
ALTER TABLE `help_category` ADD PRIMARY KEY(`help_category_id`);
ALTER TABLE `help_category` ADD INDEX(`name`);
ALTER TABLE `help_keyword` ADD PRIMARY KEY(`help_keyword_id`);
ALTER TABLE `help_keyword` ADD INDEX(`name`);
ALTER TABLE `help_keyword` ADD INDEX(`name`);
ALTER TABLE `help_relations` ADD PRIMARY KEY (`help_keyword_id`,`help_topic_id`);
ALTER TABLE `help_topic` ADD PRIMARY KEY(`help_topic_id`);
ALTER TABLE `help_topic` ADD INDEX(`name`);
ALTER TABLE `host` ADD PRIMARY KEY (`Host`,`Db`);
ALTER TABLE `ndb_binlog_index` ADD PRIMARY KEY(`epoch`,`orig_server_id`, `orig_epoch`);

That excludes proc which I had recreated by mysql_upgrade.
If needed for you, that’s:

ALTER TABLE `proc` DROP PRIMARY KEY, ADD PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`);
ALTER TABLE `proc` ADD KEY Grantor (`Grantor`);

ERROR 1091 was caused by primary keys (the multiple primary keys especially) that were there but somehow corrupted and couldn’t be dropped.

That was painful, but problem solved.

PS: Likely unrelated to this. Though, if you are using Plesk and run into this issue: A few repairs are likely necessary after this. I had a Trace/breakpoint trap error, when trying to use the ‘Plesk Utility’. This a recent known issue for Plesk 12.5.30 on Linux with a manual fix.