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.

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.

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.

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.

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

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…

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

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

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