Not the answer you need?
Register and ask your own question!

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

rlexrlex EntrantInactive User Role Beginner
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.

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
  • rlexrlex Entrant Inactive User Role Beginner
    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.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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.
  • rlexrlex Entrant Inactive User Role Beginner
    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
  • rlexrlex Entrant Inactive User Role Beginner
    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...
  • blackangelcblackangelc Entrant Current User Role Beginner
    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
  • blackangelcblackangelc Entrant Current User Role Beginner
    Hi,

    i could solve the problem. I installed on a docker container Percona 5.7 "Vanilla" and dumped the structure of the user table:
    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:
    > 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:
    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
  • OloremoOloremo Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.