Usman ur Rehman Ahmed's blog

 

The user specified as a definer ('root'@'%') does not exist

I encountered this exception today while trying to read a data set from MySql database from C# .NET using MySql connector and enterprise library. Complete stack trace of this exception is below,

{MySql.Data.MySqlClient.MySqlException: The user specified as a definer ('root'@'%') does not exist

   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()

   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)

   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)

   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)

   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName)

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command)

 This happens when you try to make connection with MySql database from within .NET and is a privileges issues. For resolution, please follow these steps,

1- Run > Command Prompt

                Move to C:\Program Files\MySql\bin folder. Path for me appears as following,

       C:\Program Files\MySQL\MySQL Server 5.5\bin> 

2- Log into My Sql by exeucting the following command,

       mysql -u root -p

                You will be prompted for password.

       Enter password:

                On successfull password input you will log into mySql console,

                mysql> _

3- Grant All Privileges

                Execute following command under my sql console,

       grant all privileges on *.* to `root`@`%` identified by 'password';

                Upon successfull completion of command you will recieve confirmation simialr to this,

                Query OK, 0 rows affected (0.05 sec)

Note: Pay special heed to position of apostrophe vs. commas. Usernameis enclosed within apostrophy (`) where as password within commas (').

4- Flush Privileges

                Execute following command to ensure the privileges take affect,

       flush privileges;

                which will result in notification similar to this,

                Query OK, 0 rows affected (0.11 sec)

                You are done. Switch back to Visual Studio to test your settings are properly taking affect. To exit mysql console type exit and press enter. Above steps are summarized in figure below,

My_sql_grant_privileges

 

Filed under: .NET Connector MySql Permission Sql c# grant privilege root user
11
To Posterous, Love Metalab