MySQL Bugs: #67601: Error while adding a data connection in VS2012

Bug #67601	Error while adding a data connection in VS2012
Submitted:	15 Nov 2012 21:44	Modified:	23 Jan 2013 21:09
Reporter:	Robert Hood	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.6.4	OS:	Windows (7 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	Data connection error

Description:
When adding a new data connection in visual studio 2010 or 2012, I receive a series of errors.  I cannot add the connection.  

Our backed database is MySQL 5.0.95

How to repeat:
Create a new project, add a new data entity model.  Choose generate from database, and add new connection.
After I enter the server name, user name, and attempt to drop down the databases list, I recieve the error "Unable to retrieve the list of databases".  
At that point, I enter the name of the database by typing in instead of selecting.  Then, I click Test Connection where I recieve the error "Arithmetic operation resulted in an overflow".  
If at that point, I click OK, out of the error then OK on the connection dialog, I recieve another VS error that summarizes the same two previous errors:  Failed to open a connection to the database.  Error message: 'Arithmetic operation resulted in an overflow.'

Correcting the version number

correcting OS version to include 64 bit

Hi, this is most likely due to you using old style authentication in MySql (which is deprecated and its support was dropped in Connector/NET 6.6.x).

This bug is duplicated of http://bugs.mysql.com/bug.php?id=66647

And reproducing here the workaround documented in that bug:

"... the way to reproduce & fix is as follows:

Create a user with old style authentication, using these SQL as sample:

create user 'oldpassuser'@'localhost'
grant all on *.* to 'oldpassuser'@'localhost'
set password for 'oldpassuser'@'localhost' = old_password( '123' )

Then try to connect with that user in Connector/NET 6.6.2 or later. You will get the "Arithmetic operation resulted in overflow" error.

Now try this SQL to reset the password to the 4.1 authentication style

set password for 'oldpassuser'@'localhost' = password( '123' )

Connect again with same credentials in Connector/NET 6.6.2, you will have no problem..."

thanks

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".

I'm not sure that this is a duplicate of the bug mentioned. We were using OLD_PASSWORD for compatibility reasons, but I believe we can begin to move away from that.  However, after resetting the password using the following SQL statement (details have been obscured to protect the innocent):

SET PASSWORD FOR 'user'@'%' = PASSWORD('Pa$$w0rd');

I still get the same error.  I have tried now on two different servers.  First is MySql 5.0.95 on 32 bit linux, and the second is MySQL 5.1.66 64 bit linux.

Is there more to the solution, such as a server level variable that needs set?

i found it.  You have to turn off server level support for old passwords by setting old_password=0 in my.cnf, then restart mysql, THEN change the password for the user accounts