Emergency

Column count of mysql.user is wrong. Expected 45, found 48.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Column count of mysql.user is wrong. Expected 45, found 48.

    Debian 8 and ubuntu 16.04 (same percona server version). Versions:

    ii percona-release 0.1-4.jessie all Package to install Percona gpg key and APT repo
    ii percona-server-client-5.7 5.7.18-15-1.jessie amd64 Percona Server database client binaries
    ii percona-server-common-5.7 5.7.18-15-1.jessie amd64 Percona Server database common files (e.g. /etc/mysql/my.cnf)
    ii percona-server-server-5.7 5.7.18-15-1.jessie amd64 Percona Server database server binaries

    This started to happen after upgrade to 5.7.18 (works fine on .17) - i can't edit permissions. Anything related to permissions (grant, create user, etc) will fail with "ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 48. The table is probably corrupted". And yes, i run mysql_upgrade after upgrade.

    Current mysql.user table structure:

    | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | auth_string | password_expired | is_role | default_role | max_statement_time | password_last_changed | password_lifetime | account_locked |

    Thanks in advance.

  • #2
    From which Percona Server version did you upgrade from? We would like to reproduce the issue on a local test instance so if you can share a reproducible test case with details that would help a lot.

    Comment


    • #3
      Can't really say, this is old servers. One is probably from mysql 5.0 (i know i had it from 2013), second was upgraded from 5.5.

      Comment


      • #4
        So it's an in-place upgrade from an old version to the latest 5.7 version. Can you share the details of your steps to upgrade? What operating system and other details.

        Comment


        • #5
          One is ubuntu 16.04, pretty fresh machine (around 6 month or so)
          Second is debian 8 now, was started... i think from debian 6 and dist-upgraded all the way to 6.
          Upgrade is done with apt-get safe-upgrade, mysql_upgrade, service mysql restart

          Comment


          • #6
            btw, what is my options in that situations beside dumping & restoring all databases, and recreating all permissions manually? not a mission critical servers, but if there any way i can adjust scheme without that...

            Comment


            • #7
              Hi Together,

              i migrated from MariaDB (MySQL 5.6) to Percona 5.7 cause wsrep cluster was not working well with MariaDB. Without mysql_upgrade all worked fine but i got always notices in MySQL Error Log. After i upgraded the tables with mysql_upgrade i had the same issue. Is there any solution yet to fix it?

              I guess there is something in an other table which is relating to mysql.user. Do you have any solution yet or a hint where i can look at?

              Thanks and best regards,
              blackangelc

              Comment


              • #8
                Hi,

                i could solve the problem. I installed on a docker container Percona 5.7 "Vanilla" and dumped the structure of the user table:
                Code:
                mysqldump --no-data --lock-tables=false mysql user > mysql_user_vanilla.sql
                I did the same on the host where the schema was broken and diffed the 2 servers:
                Code:
                > diff -uNp mysql_user_broken.sql mysql_user_vanilla.sql
                --- mysql_user_broken.sql    2017-09-13 18:29:18.184767699 +0200
                +++ mysql_user_vanilla.sql   2017-09-13 18:25:59.927760416 +0200
                @@ -1,8 +1,8 @@
                --- MySQL dump 10.13  Distrib 5.7.18-15, for debian-linux-gnu (x86_64)
                +-- MySQL dump 10.13  Distrib 5.7.19-17, for debian-linux-gnu (x86_64)
                 --
                 -- Host: localhost    Database: mysql
                 -- ------------------------------------------------------
                --- Server version      5.7.18-15-57-log
                +-- Server version      5.7.19-17
                
                 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
                 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
                @@ -14,11 +14,10 @@
                 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
                 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
                 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
                -/*!50717 SET @rocksdb_bulk_load_var_name='rocksdb_bulk_load' */;
                 /*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
                -/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=?', 'SELECT 0') */;
                +/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=\'rocksdb_bulk_load\'', 'SELECT 0') */;
                 /*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
                -/*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */;
                +/*!50717 EXECUTE s */;
                 /*!50717 DEALLOCATE PREPARE s */;
                 /*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */;
                 /*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
                @@ -71,12 +70,9 @@ CREATE TABLE `user` (
                   `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
                   `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
                   `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
                -  `max_user_connections` int(11) NOT NULL DEFAULT '0',
                +  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
                   `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
                   `authentication_string` text COLLATE utf8_bin,
                -  `is_role` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',
                -  `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
                -  `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000',
                   `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
                   `password_last_changed` timestamp NULL DEFAULT NULL,
                   `password_lifetime` smallint(5) unsigned DEFAULT NULL,
                @@ -98,4 +94,4 @@ CREATE TABLE `user` (
                 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
                 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
                
                --- Dump completed on 2017-09-13 18:29:18
                +-- Dump completed on 2017-09-13 16:24:55
                After it i executed the following commands and could create user again:
                Code:
                mysql> alter table user drop column is_role;
                mysql> alter table user drop column default_role;
                mysql> alter table user drop column max_statement_time;
                mysql> alter table user modify max_user_connections int(11) unsigned NOT NULL DEFAULT '0';
                mysql> flush privileges;
                I hope this way helps also other people to find out which columns are wrong.

                Best regards
                blackangelc

                Comment


                • #9
                  Hi, jrivera, in case you're still interested, I found a 100% reproducible case for this issue.

                  Scenario:
                  mariadb-10.1.14 upgrade to Percona-XtraDB-Cluster-5.7.19-rel17-29.22.1
                  Both deployed via tars, not packages.

                  1) Shutdown maria
                  2) Deploy Percona
                  3) Start percona
                  4) Run mysql_ugrade
                  5) Get the following error

                  Additionally, "sys" database can't be created via mysql_upgrade, probably because the same problem with mysql.user

                  Comment

                  Working...
                  X