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
