5

I am trying to call a stored procedure with php. The previous day this was working, but today i get an error that "The user specified as a definer ('name of the user'@'my ip the previous day') does not exist"

The procedure is located in a host and i grant to my ip access through cpanel's remote MySql. The code that i used is correct and taken from here http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php

Does the error occur because my ip is different today, even though i have access again? How can i solve it?

user1860935
  • 83
  • 1
  • 2
  • 6
  • Thanks for your responses. I could have solved it with your code i believe, but i did not had root access credentials. It was as i expected the definer was related to the ip. Previously i created these stored procedures remotely from MySQL workbench, this was the cause. The solution was to use the phpMyAdmin tool provided by cPanel and use that to create the stored procedures (So no definer limitations anymore). – user1860935 Nov 26 '13 at 16:21

3 Answers3

10

If you've found following error while using mysql database: The user specified as a definer ('root'@'%') does not exist Then you can solve it by using following :

   grant all on *.* to 'root'@'%' identified by 'password' with grant option;

Credit to http://www.lynnnayko.com/2010/07/mysql-user-specified-as-definer-root.html

Poomrokc The 3years
  • 1,099
  • 2
  • 10
  • 23
2

The user that created the procedure must've been deleted. Since your user id is mapped to an ip address, the change of the IP is most probably the cause. If you can recreate the user, that would work.

or, try this: 

grant all on *.* to 'root'@'%' identified by 'password' with grant option;

This is a slightly insecure way to handle it, but if its just local biz, it shouldn't be a problem.

Source: http://www.lynnnayko.com/2010/07/mysql-user-specified-as-definer-root.html

sidegeeks
  • 1,021
  • 3
  • 12
  • 17
0

With the MySQL Ansible Module

- name: create root@% user
  mysql_user: name=root host=% password=password priv=*.*:ALL,GRANT state=present
Andrew
  • 3,733
  • 1
  • 35
  • 36