MySQL error 1044: Access denied

(3 posts) (2 voices)

Started by parentesis
Latest reply from parentesis

Tags:

parentesis, Member

I have a Grid Service with MediaTemple, no root privileges are given on MySQL (just read/write), and I get this message during the instalation (when the script tries to create de Objects)

MySQL error 1044: Access denied for user 'xxxxxx@%' to database xxxxxxx

I've tried to execute the SQL it gives in PhpMyAdmin:

CREATE PROCEDURE sp_MyDBR_FixTables ( inSchema varchar(64) )

BEGIN

declare vCnt int;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_userlogin' and column_name='authentication';

if (vCnt=0) then

	alter table mydbr_userlogin add authentication int not null default 2;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_reports' and column_name='reportgroup';

if (vCnt=0) then

	alter table mydbr_reports add reportgroup int not null default 1;

	alter table mydbr_reports add FOREIGN KEY (reportgroup) REFERENCES mydbr_reportgroups (id);

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_reports' and column_name='sortorder';

if (vCnt=0) then

	alter table mydbr_reports add sortorder int null;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_reports' and column_name='runreport';

if (vCnt=0) then

	alter table mydbr_reports add runreport varchar(50) null;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_folders' and column_name='reportgroup';

if (vCnt=0) then

	alter table mydbr_folders add reportgroup int not null default 1;

	alter table mydbr_folders add FOREIGN KEY (reportgroup) REFERENCES mydbr_reportgroups (id);

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_folders' and column_name='explanation';

if (vCnt=0) then

	alter table mydbr_folders add explanation varchar(255) null;

end if;

update mydbr_folders set name='#{MYDBR_AMAIN_HOME}' where folder_id=1;

ALTER TABLE mydbr_userlogin MODIFY COLUMN user varchar(128);

ALTER TABLE mydbr_reports_priv MODIFY COLUMN username varchar(128);

ALTER TABLE mydbr_statistics MODIFY COLUMN username varchar(128);

ALTER TABLE mydbr_groupsusers MODIFY COLUMN user varchar(128);

ALTER TABLE mydbr_log MODIFY COLUMN user varchar(128);

ALTER TABLE mydbr_options MODIFY COLUMN user varchar(128);

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_statistics' and column_name='authentication';

if (vCnt=0) then

	alter table mydbr_statistics add authentication int not null default 2;

end if;

select count(*) into vCnt

from information_schema.KEY_COLUMN_USAGE

where table_schema=inSchema and table_name='mydbr_userlogin';

if (vCnt<2) then

	ALTER TABLE mydbr_userlogin DROP PRIMARY KEY;

	ALTER TABLE mydbr_userlogin add primary key (user, authentication);

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_params' and column_name='optional';

if (vCnt=0) then

	alter table mydbr_params add optional int not null default 0;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_userlogin' and column_name='passworddate';

if (vCnt=0) then

	alter table mydbr_userlogin add passworddate datetime null;

	update mydbr_userlogin set passworddate = now();

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_userlogin' and column_name='email';

if (vCnt=0) then

	alter table mydbr_userlogin add email varchar(100) NULL;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_userlogin' and column_name='telephone';

if (vCnt=0) then

	alter table mydbr_userlogin add telephone varchar(100) NULL;

end if;

select count(*) into vCnt

from mydbr_userlogin

where admin=1;

if (vCnt=0) then

	INSERT IGNORE INTO mydbr_userlogin ( user, password, name, admin, passworddate, email, authentication, telephone)

	VALUES ('dba',md5('dba'),'myDBR Administrator',1, now(), null, 2, null);

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_licenses' and column_name='type';

if (vCnt=0) then

	alter table mydbr_licenses add type varchar(255) default null;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_licenses' and column_name='version';

if (vCnt=0) then

	alter table mydbr_licenses add version varchar(255) default null;

end if;

select count(*) into vCnt

from information_schema.columns

where table_schema=inSchema and table_name='mydbr_languages' and column_name='date_format';

if (vCnt=0) then

	alter table mydbr_languages add date_format varchar(10) null;

	alter table mydbr_languages add time_format varchar(10) null;

	alter table mydbr_languages add thousand_separator varchar(2) null;

	alter table mydbr_languages add decimal_separator varchar(2) null;

end if;

END

But I get another error:

CREATE PROCEDURE sp_MyDBR_FixTables ( inSchema varchar( 64 ) ) BEGIN declare vCnt int;

MySQL ha dicho:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

myDBR Team, Key Master
sp_MyDBR_FixTables is the first stored procedure the installer is creating. Looks like that in your MediaTemple GS service, you do not have permissions to create routines. You might want to chck this with MediaTemple.

As for phpMyAdmin, it really is not the best tool for creating procedures. You might want to try with simpler procedure to see if your permissions are correct. Also make sure you use a different delimiter than ";" when creating procedures. With delimiter $$ you can say something like this:
```
drop PROCEDURE if exists sp_test$$

CREATE PROCEDURE sp_test()

BEGIN

select 1;

END$$ 
```
Btw, MediaTemple offers great value with their (ve) Server.

--
myDBR Team
parentesis, Member

Hello

Thanks a lot for your fast response. I have no permissions to create routines. I'll think about upgrading to (ve) Server

Reply

You must log in to post.