MySQL Forums
Forum List  »  Newbie

Re: Error 1064
Posted by: Peter Brawley
Date: November 13, 2017 11:59AM

Oh, now I see what you are trying to do.

Your command ...

UPDATE 'name_db.routinename' SET definer='username@%' WHERE definer='username@'

... gets every syntax element wrong, entirely mangling Update syntax.

For such a command you need to read carefully the manual page for Update.

The syntax is ...

Update [<dbname>]<tablename> Set <column_name> = <value> Where ...

Your target <dbname> is mysql, the table name is proc, the column name is definer. The desired username is surely not 'username'. and in any case you have quoted the user spec incorrectly, it needs to be 'myusername'@'myIPaddress'. And finally, the Where clause needs to uniquely identify the row you intend to update, so it would be something like ...

Where db='<your database name>' and name = '<your procedure name>'

As you can see, there are a lot of items to get wrong here. You found them all. And some are extremely risky: you might overwrite too many rows in this system table.

But why all this dangerous folderol? There is a command interface for creating and editing stored routines. It's way safer to use it to maintain stored routines ...

(i) in the mysql client program, execute Show Create Procedure <myprocedurename>;

(ii) copy and paste the result into your text editor

(iii) make desired changes, preface it with a Drop Procedure command, surround it with delimiter directives

(iv) copy & paste the result back into the mysql client

Options: ReplyQuote

Written By
November 10, 2017 04:46AM
November 10, 2017 08:46AM
November 12, 2017 02:52PM
November 12, 2017 06:45PM
November 12, 2017 11:40PM
November 13, 2017 09:00AM
November 13, 2017 01:47PM
Re: Error 1064
November 13, 2017 11:59AM
November 13, 2017 08:58AM
November 13, 2017 02:26AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.