Bug #100569 Unable to restore from data backup.
Submitted: 19 Aug 2020 5:12 Modified: 20 Aug 2020 2:55
Reporter: Kailasam Nagaraju Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:8.0.20 OS:Red Hat (7)
Assigned to: CPU Architecture:Any

[19 Aug 2020 5:12] Kailasam Nagaraju
Description:
Hello team,

This bug is related to http://bugs.mysql.com/100061

After upgrading from 8.0.18 to 8.0.20 we had to manually set binary-as-hex to false in the /etc/my.cnf file for AES_Encryption and decryption as follows to function properly.

[client]
binary-as-hex = false

[mysql]
binary-as-hex = false

We now see issues while restoring from a backup with the error as below:

#mysqlimport -u root -p Test_Restore /tmp /Abc.csv;
mysqlimport: [ERROR] unknown variable 'binary-as-hex=false'.

Please let us know if there are any steps to follow before restoring.

Regards.

How to repeat:
Set binary-as-hex to false in /etc/my.cnf in version 8.0.20

Try to restore from a backup using below command.
#mysqlimport -u root -p Test_Restore /tmp /Abc.csv;
[19 Aug 2020 12:27] MySQL Verification Team
Hi Mr. Nagaraju,

Thank you for your bug report.

However, what you are reporting is not a bug.

That option is used on client side only. It does not exist on the server. Also, it has nothing to do with AES encryption, what so ever .....

Your problem stem from the downgrading, as explained in your previous bug. We do not support downgrading on the same data .....
[19 Aug 2020 12:39] Kailasam Nagaraju
Hello, 

Thank you for your response.

Kindly note that we have not downgraded. We had upgraded from 8.0.18 to 8.0.20. Post upgrade we were informed that from version 8.0.19, the default value for binary-as-hex has been set to true. This caused us issues while using the AES_Encryption and decryption functions.

We had to manually set this binary-as-hex to false so that the AES_Encryption and decryption could be used by us as desired.

The issue that we now face is that we are not able to restore our data from a backup as stated above. The error is shared above too.
[19 Aug 2020 13:08] MySQL Verification Team
Hi,

You have mentioned, loud and clear, in the bug report 100061 that you have first downgraded.

Next, upgrading from 8.0.18 to 8.0.20 is not done via backup / restore as mentioned in our Reference Manual.

Next, default value for that option is false, which is clearly written in our Reference Manual. Also, this is client-only option.

Next, this option does not influence any encryption. All it does is change representation of the result set for some data types.

The fact that you can't restore is not a bug, since you did not follow procedures. 

Also, this is not a forum for free support, but for bugs with fully repeatable test cases.
[19 Aug 2020 13:33] Kailasam Nagaraju
Okay then kindly ignore the downgrade part. Please treat this as a separate issue if there is some misunderstanding.

We were not getting the correct representation of the data when binary-as-hex was set to true. Hence we set it to false ourselves.

Now we arent able to restore from a backup due to this as the error states.

Now should we revert back the binary as hex to true and then try to restore or is there any other way ?

Regards.
[19 Aug 2020 13:42] MySQL Verification Team
Hi,

First of all, in all releases of MySQL 8.0 version , client-side's default value of that variable is FALSE.

Next, mysqldump and mysqlimport do not have that option. They have, however, hex-blob option. Hence, teh error message.

Third, mysqldump has to be invoked with special arguments in order for mysqlimport to be used. 

Hence, once again, this is not a bug. Also, this is not a forum for free support.

Not a bug.
[20 Aug 2020 2:55] Kailasam Nagaraju
As per the below link, from version 8.0.19, binary as hex is enabled by default.

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_binary-as-...

mysqldump has to be invoked with special arguments in order for mysqlimport to be used. Can you share how to invoke it ?

Lastly, also guide me to a forum where I can discuss this.

Regards.
[20 Aug 2020 12:12] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.