Error in the past

Question

I created this procedure to test whether a field exists 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 he finds some field that doesn’t exist, he gives me the following mistake:

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, he started to give me that mistake:

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 into general

The database, the table are like UTF8. I’ve tried to:

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

Answer 1

Gabriel, the user of eco_portaluserh does not have permission to execute the command ALTER, you need to run a GRANT using a user with permission level.

thus:

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

Where :

1. database: name of the database where this user will have access.
2. username: equals to eco_portaluserh
3. interface: equals to 186.206.156.42
4. password: is equal to the password of that user, defined in the account creation.

After executing the above command give :

 FLUSH PRIVILEGES;

To update the user permissions in mysql.

To err is human, but this time I’m sure yes. I just tested as the image above.

You are trying to give permission to the user eco_portaluserh mysql logged in with itself. It has no privileges over its database eco_portaldb_homolog. You must use another user in the case of the image, I used my user root which has all permissions on all my Databases, note that due to the fact of the user root have access to all Databases, I defined it as localhost which is nothing more than the network interface 127.0.0.1; or just local access to maintain security.