MySQL Bugs: #79130: mysqldbimport fails to import GRANTs for not existent users

Bug #79130	mysqldbimport fails to import GRANTs for not existent users
Submitted:	4 Nov 2015 22:53	Modified:	30 Aug 2016 13:37
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.6.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysqldbimport fails to import GRANTs which are created by mysqldbexport when such users exist in the original database, but does not in the destination database. Having Bug #79128 this is annoying and requires manual edition of the backup file, created by mysqldbexport. This affects 5.7 server.

How to repeat:
1. Start MTR.
2. Create few tables
3. Run mysqldbexport: mysqldbexport --server=root:@127.0.0.1:13000 --export=both test > mitest.sql
4. Import dump:

sveta@thinkie:~/build/mysql-5.7/mysql-test> mysqldbimport --server=root:@127.0.0.1:13000 --skip-gtid --skip-rpl mitest.sql 
WARNING: Using a password on the command line interface can be insecure.
# Source on 127.0.0.1: ... connected.
# Importing definitions from mitest.sql.
CAUTION: The following warning messages were included in the import file:
# WARNING: Using a password on the command line interface can be insecure.
# WARNING: A partial export from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all and --export=both options.
ERROR: Invalid statement:
GRANT ALTER ON `test`.* TO ''@'%'
ERROR: Query failed. 1133 (42000): Can't find any matching row in the user table

Suggested fix:
Add CREATE USER statements and fix bug #79128

This is 5.7 compatibility issue, can be any user, not existed on the destination server.

Hello Sveta,

Thank you for the report.

Thanks,
Umesh

I kludged removing 'no_create_user' from sql_mode during the import. Will it work in your case?

Roberto, well, it should help in my case too. But since this is default SQL mode in 5.7 mysqldbimport should be aware of it.

Sveta, 

how are you addressing this issue now? Are you adding the CREATE USER in the .sql or kludging?
  - adding set sql_mode in the resulting .sql?

Do you think implementing something like mysql --init-command is fine? 

You may be interested in this (patch) too http://bugs.mysql.com/bug.php?id=79711

Hi Sveta,

I've located the issue in the code, but there is a complication. Are you expecting the import to set passwords for the missing user accounts? If so, we will have to add that feature to mysqldbexport because it currently does not export user accounts - only the GRANTS (which is a problem for 5.7 servers).

Hi Chuck,

good question.

To be compatible with 5.6 you need to just create password-less users, probably with option EXPIRE PASSWORD.

Copying existent passwords can be convenient too, but requires an option, so user can choose if s/he wants to copy passwords or just allow password-less logins for the first time.