MySQL 5.7: Unknown column 'password' in 'field list' about common_schema

[solomonty]

I installed version 2.3 on a 5.6 master and replication failed on a 5.7 slave.

Last_SQL_Error: Error 'Unknown column 'password' in 'field list'' on query. Default database: 'common_schema'. Query: 'CREATE OR REPLACE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `_sql_accounts_base` AS SELECT 
    user,
    host,
    mysql_grantee(user, host) AS grantee,
    password,
    password = '' or password rlike '^[?]{41}$' as is_empty_password,
    password rlike '^[*][0-9a-fA-F]{40}$' or password rlike '^[0-9a-fA-F]{40}[*]$' as is_new_password,
    password rlike '^[0-9a-fA-F]{16}$' or password rlike '^[~]{25}[0-9a-fA-F]{16}$' as is_old_password,
    password rlike '^[0-9a-fA-F]{40}[*]$' or password rlike '^[~]{25}[0-9a-fA-F]{16}$' or password rlike '^[?]{41}$' as is_blocked,
    REVERSE(password) AS reversed_password,
    REPLACE(password, '~', '') AS untiled_password,
    CONCAT(REPEAT('~', IF(CHAR_LENGTH(password) = 16, 25, 0)), password) AS tiled_password
  FROM
    mysql.user'

[ikewalker]

@Preetam @solomonty What specific versions of 5.7 are you using?

[Preetam]

I believe 5.7.11 right now.

[ikewalker]

@Preetam is that 5.7.11 Community? It looks like Percona Server may still have the password column in the mysql.user table, but Community version does not.

[Preetam]

Yes, MySQL Community. We're not using Percona Server.

[solomonty]

@ikewalker Percona Server 5.7.11. It is a superset of MySQL Community.

CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `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) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin,
  `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,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

[solomonty]

The installation fails on Percona Server 5.7.11.

# mysql < common_schema-2.3.sql 
ERROR 1054 (42S22) at line 12498: Unknown column 'password' in 'field list'

[grypyrg]

@solomonty : Yeah, installing common_schema on 5.6 and having slaves in 5.7 will break replication as the view _sql_accounts_base for example is a select on mysql.user where there is a big schema difference between versions.

[grypyrg]

How can we solve this permanently (and bring 5.7 support)?
Quick thoughts:

• skip replication during installation of common_schema SQL_LOG_BIN=0. Can we do this? Will it cause more things to break if a replica does not have (the same) common_schema?
• add a mysql version check in common schema for this view (and maybe other views) (I'm quite new to the code, and couldn't find anything immediately, are there any checks like this already somewhere?)

[ikewalker]

I'll try updating the code for _sql_accounts_base to check whether the password column exists or not, and use the authentication_string column in its place if password does not exist.

[ikewalker]

Created PR #12

[tersmitten]

Any progress on this @ikewalker?

[minluo94587]

Has the issue been fixed yet? We need run common_schema 2.3 on MySQL 5.7, but hit the same issue.

[majost-colombia]

Hi, i'm getting the same error on mysql 5.7.24 server with versión 2.3, with 2.2 i have a lot of errors.