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_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?