Mysql – ERROR 1091 (42000): Can’t DROP ‘PRIMARY’; on “thesql_upgrade”

centoscrashMySQLmysql-5.6upgrade

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?

Best Answer

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.

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

MySql replication from 5.1 master to 5.6 slave keep crashing

Your master is not compatible with the 5.6 slave. binlog_checksum is only available on 5.6, and so is UUID. I have successfully replicated servers between 5.5 and 5.6. Perhaps you can check if these options can be disabled.

2014-11-13 14:03:07 1022 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193 2014-11-13 14:03:07 1022 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.1.71-log), even though it is on the slave (version: 5.6.21-log), Error_code: 1193

Best Answer

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

MySql replication from 5.1 master to 5.6 slave keep crashing

Related Question