3

I had created all my stored procedures with [email protected] as DEFINER value. Now, since I've changed my location therefore the IP address I cannot view or export the code.

My code is on shared hosting and the support team does not want to help regarding this matter.

Any ideas on how to get to the code? Even a full database export does not contain the stored procedures code. It only has the data that is inserted into ROUTINES table

INSERT INTO `ROUTINES` (`SPECIFIC_NAME`, `ROUTINE_CATALOG`, `ROUTINE_SCHEMA`,
    `ROUTINE_NAME`, `ROUTINE_TYPE`, `DTD_IDENTIFIER`,
    `ROUTINE_BODY`, `ROUTINE_DEFINITION`, `EXTERNAL_NAME`, `EXTERNAL_LANGUAGE`,
    `PARAMETER_STYLE`, `IS_DETERMINISTIC`, `SQL_DATA_ACCESS`, `SQL_PATH`,
    `SECURITY_TYPE`, `CREATED`, `LAST_ALTERED`, `SQL_MODE`, `ROUTINE_COMMENT`,
    `DEFINER`, `CHARACTER_SET_CLIENT`, `COLLATION_CONNECTION`,
    `DATABASE_COLLATION`)
VALUES ('sp_sample_name', NULL, 'XXX',
    'sp_sample_name', 'PROCEDURE', NULL,
    'SQL', NULL, NULL, NULL,
    'SQL', 'NO', 'CONTAINS SQL', NULL,
    'DEFINER', '2012-03-26 01:01:14', '2012-03-26 01:01:14', 'STRICT_TRANS_TABLES',
    '', '[email protected]', 'latin1', 'latin1_swedish_ci',
    'latin1_swedish_ci'),

Thank you

mellamokb
  • 56,094
  • 12
  • 110
  • 136
user1359575
  • 99
  • 1
  • 3
  • 12

1 Answers1

2

You can call SHOW CREATE PROCEDURE.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • If I create a new procedure it does, but it does not return the code in this case. It returns as follows: Procedure / sql_mode / Create Procedure / charset_set_client / collation_connection / Database Collation sp_sample_name STRICT_TRANS_TABLES (null) latin1 latin1_swedish_ci latin1_swedish_ci – user1359575 Apr 27 '12 at 18:28
  • It returns (null) for Create Procedure. Not sure why. – user1359575 Apr 27 '12 at 18:31
  • `Create Procedure` is *definitely* `NULL`? It's not just that it contains line breaks so that its contents is hidden from view in your client program? Maybe try [`SHOW PROCEDURE CODE`](http://dev.mysql.com/doc/refman/5.6/en/show-procedure-code.html)? – eggyal Apr 27 '12 at 18:31
  • Yes, i've checked both DBVisualizer and phpmyadmin, both return NULL. For SHOW PROCEDURE CODE i get syntax error for SHOW PROCEDURE CODE sp_sample_name. – user1359575 Apr 27 '12 at 18:44
  • I've suggested the hosting support team to update the DEFINER values as shown here http://stackoverflow.com/questions/3206820/mysql-change-the-stored-procedure-definer but they refuse to do so. – user1359575 Apr 27 '12 at 18:46
  • Looks like http://stackoverflow.com/a/1084309/623041 is your problem here. Do you have shell access to your host (I presume you're unable to connect to MySQL a user with `SUPER` privilege)? – eggyal Apr 27 '12 at 18:48
  • Yeah it seems same as mine. And you are right I dont have the SUPER privilege. Same the comments there I'm not able neither to select or update the mysql.proc table. I dont have shell access. I dont have experience using it either. Do you have any ideas that will help? If so, I will find out a way set it up. Thanks – user1359575 Apr 27 '12 at 19:02
  • It doesn't look like they have any web-based/automated method of resolving this issue; in which case, I think your only two options are to persuade their support team either to reset your MySQL root user credentials/grant your current user the `SUPER` privilege, or else try and somehow connect again from your previous IP address. – eggyal Apr 27 '12 at 19:14
  • It seems you are right. It will be hard to get to the previous IP, so I asked them to execute SHOW CREATE PROCEDURE for all my SPs. There are more that 50 SPs and it is lot of work, but i really need the code. Thank you for you help. – user1359575 Apr 27 '12 at 19:18