MySQL Bugs: #29488: aes_encrypt() is not working when copy database from Mac OS X to Windows XP

Bug #29488	aes_encrypt() is not working when copy database from Mac OS X to Windows XP
Submitted:	2 Jul 2007 15:14	Modified:	7 Aug 2007 18:11
Reporter:	Shanwen Yu	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	5.0	OS:	MacOS (and Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	password

Description:
I have a MySQL 5.0 installed on a Mac OS X server and another one installed on a Windows XP machine. I use the MySQL Administrator to backup the database on the Mac OS X server, and it creates a dump file. Then I use the MySQL Administrator to restore the database on the Windows XP using the dump file, everything's fine except the value of the password column becomes "NULL". I use aes_encrypt() function in the database.

How to repeat:
1. Use MySQL Administrator on the Mac OS X, click the Backup icon, in the Advanced Options, check "InnoDB Online Backup" and leave everything else unchecked. Then click the "Start Backup" button to backup the database. A SQL file will be generated in the specified location.
2. Copy the SQL file to the Windows XP machine.
3. Use MySQL Administrator on the Windows XP machine, and click "Restore" icon, open the Backup file as indicated, then click "Start Restore".
4. After the restore is finished, use the following to check the password column:

"SELECT AES_DECRYPT(PASSWORD, "password") FROM USER"

and you will find that all the values of Password column become NULL.

Thank you for the report.

Please provide output of SHOW CREATE TABLE USER;

Here is the result of "Show Create Table User"

| Table | Create Table                                                                           
| user  | CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL auto_increment,
  `user_type_id` tinyint(3) unsigned default NULL,
  `username` varchar(20) default NULL,
  `password` varchar(20) default NULL,
  `firstname` varchar(50) default NULL,
  `middlename` varchar(30) default NULL,
  `lastname` varchar(50) default NULL,
  `email` varchar(100) default NULL,
  `last_login` datetime default NULL,
  `createddate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `modifieddate` datetime default NULL,
  `is_deleted` tinyint(1) default '0',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_username` (`username`),
  KEY `user_type_id` (`user_type_id`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`user_type_id`) REFERENCES `user_type` (`user_type_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
--------------------------------------------+
1 row in set (0.00 sec)

Thank you.

Thank you for the report.

I can not repeat described behavior in my environment.

Are you sure you use same 'password' on both machines? If yes, please indicate accurate versions of MySQL server on both Mac and Windows machines. Also provide configuration files for both servers.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".