Hi Sergei!
Can you please review this patch for MDEV-11700? Details presented in the
commit message. I'm not sure which solution we want.
Possible solutions:
A) have the user fix it by first starting the server with
--skip-grant-tables to avoid "mysql.user table is damaged", then run
mysql_upgrade, to fix the problem. Without this patch, this solution fails
at mysql_upgrade.
B) Have the server understand MySQL 5.7.6 + table format where the Password
column is missing.
For 10.2 I've chosen solution A. Although this should probably also be
targeted for 10.1?
For 10.3, since we're planning on doing some authentication changes, we can
implement a variant of B.
Thoughts?
On Mon, 12 Dec 2016 at 03:50 vicentiu
revision-id: 02e74a5614b08cfce505002abc26d4634648308e (mariadb-10.2.2-138-g02e74a5) parent(s): 9320d8ae30c18420bef659618175836221d363ea author: Vicențiu Ciorbaru committer: Vicențiu Ciorbaru timestamp: 2016-12-12 03:42:11 +0200 message:
MDEV-11700: MariaDB 10.2 cannot start on MySQL 5.7 datadir:
Fatal error: mysql.user table is damaged or in unsupported 3.20 format
The problem stems from MySQL 5.7.6. According to MySQL documentation: In MySQL 5.7.6, the Password column was removed and all credentials are stored in the authentication_string column.
If opening a MySQL 5.7.6 (and up) datadir with MariaDB 10.2, the user table appears corrupted. In order to fix this, the server must be started with --skip-grant-tables and then a subsequent mysql_upgrade command must be issued.
This patch updates the mysql_upgrade command to also add the removed Password column. The password column is necessary, otherwise the mysql_upgrade script fails due to the Event_scheduler not being able to start, as it can't find Event_priv in the table where it ought to be. MySQL's version has column position 28 (0 index) vs our datadir version expects position 29.
--- scripts/mysql_system_tables_fix.sql | 6 ++++++ 1 file changed, 6 insertions(+)
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index ea1059c..80d0ef7 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -164,6 +164,12 @@ ALTER TABLE user MODIFY Host char(60) NOT NULL default '', MODIFY User char(80) NOT NULL default '', ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +# In MySQL 5.7.6 the Password column is removed. Recreate it to preserve the number +# of columns MariaDB expects in the user table. +ALTER TABLE user + ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER User; + ALTER TABLE user MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,