Error in procedure

Question

Error in procedure

Navigation

#1 by (0 votes)

0

I created this procedure to test if there is a field in a database, if it does not exist, the procedure would create.

  DELIMITER ||

    CREATE PROCEDURE sp_verificarLinhas(IN colunas VARCHAR(50))     BEGIN 
        IF NOT EXISTS (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = Database() AND TABLE_NAME='prt_license' AND COLUMN_NAME
    = colunas )         THEN        SET @s = CONCAT('ALTER TABLE eco_portaldb.prt_license ADD COLUMN ', colunas ,' VARCHAR(100) NULL DEFAULT NULL AFTER date_update');          PREPARE stmt FROM @s;       EXECUTE stmt;

       end if;   END ||

    DELIMITER ;

When it finds some field that does not exist, it gives me the following error:

Syntax error or access violation: 1142 ALTER command denied to user 'eco_portaluserh'@'186.206.156.42' for table 'prt_license'

After removing it and trying to recreate it, it started to give me this error:

SQLSTATE [HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci, IMPLICIT) and (utf8_unicode_ci, IMPLICIT) for operation '='

This piece has already been fixed transforming the database in general

The database, the table is as UTF8 . Already tried:

alter table 'prt_license' convert to character set utf8 collate utf8_general_ci

mysql procedure

asked by anonymous 01.08.2017 / 21:00

1 answer

Android Photo Album Connection to the slow database

score 0 · Answer 1

Gabriel, user eco_portaluserh was not allowed to execute the command ALTER , you need to execute GRANT using a user with permission level.

this way:

GRANT ALTER ON database.* TO 'username'@'interface' IDENTIFIED BY 'password';

Where:

database: name of the database where this user will have access.

username: is equal to eco_portaluserh

interface: is equal to 186.206.156.42

password: It is the same as the password for this user, defined in account creation.

After executing the above command give:

 FLUSH PRIVILEGES;

To update the permissions of the user in mysql.

To err is human, but this time I'm sure yes. I just tested as pictured above.

You are trying to give permission to the user eco_portaluserh logged in to mysql with himself. It has no privileges over its eco_portaldb_homolog database. You should use another user in the case of the image, I used my user root who has all the permissions on all my databases, note that due to the fact that user root has access to all databases, I set it as localhost that nothing else is, that the network interface 127.0.0.1; or just local access to maintain security.