9 Replies - 923 Views - Last Post: 06 August 2015 - 04:56 AM

#1 kentlucky   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 85
  • Joined: 20-June 12

Need help in my new Web Hosting Provider

Posted 05 August 2015 - 07:42 PM

Hello Guys,

I don't have any problems when I was developing in my localhost computer but when I deploy the system I encounter problems in the database

This is the SQL Command:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jobslist_view` AS select `a`.`job_id` AS `job_id`,`a`.`position` AS `position`,`a`.`description` AS `description`,`a`.`date_posted` AS `date_posted`,`a`.`date_inactive` AS `date_inactive`,`a`.`number_of_vacancy` AS `number_of_vacancy`,`b`.`hospital_id` AS `hospital_id`,`b`.`hospital_name` AS `hospital_name`,`b`.`hospital_address` AS `hospital_address`,`b`.`hospital_phone` AS `hospital_phone`,`b`.`hospital_email` AS `hospital_email`,`c`.`realname` AS `realname`,`c`.`user_id` AS `user_id` from ((`jobs_tbl` `a` left join `hospitals_tbl` `b` on((`a`.`hospital_id` = `b`.`hospital_id`))) left join `users_tbl` `c` on((`a`.`user_id` = `c`.`user_id`))) where (`a`.`archive` = 0);


This is the error message

Posted Image

I don't have any problem in my local computer in performing that sql statement. But I am having problem when i upload it in my new hosting provider. I am not sure but I think this problem is from the hosting provider side because of user privilege. But I really want to ask idea from your side guys.

I try to change the user name and ipaddress that is given by the hosting provider but the outcome is still the same.

CREATE ALGORITHM=UNDEFINED DEFINER=`admin_user`@`125.5.102.200` SQL SECURITY DEFINER VIEW `jobslist_view` AS select `a`.`job_id` AS `job_id`,`a`.`position` AS `position`,`a`.`description` AS `description`,`a`.`date_posted` AS `date_posted`,`a`.`date_inactive` AS `date_inactive`,`a`.`number_of_vacancy` AS `number_of_vacancy`,`b`.`hospital_id` AS `hospital_id`,`b`.`hospital_name` AS `hospital_name`,`b`.`hospital_address` AS `hospital_address`,`b`.`hospital_phone` AS `hospital_phone`,`b`.`hospital_email` AS `hospital_email`,`c`.`realname` AS `realname`,`c`.`user_id` AS `user_id` from ((`jobs_tbl` `a` left join `hospitals_tbl` `b` on((`a`.`hospital_id` = `b`.`hospital_id`))) left join `users_tbl` `c` on((`a`.`user_id` = `c`.`user_id`))) where (`a`.`archive` = 0);


I have also try other things, you can see it on the attachments.

Thank You.

Attached ImageAttached ImageAttached ImageAttached ImageAttached ImageAttached Image

This post has been edited by kentlucky: 05 August 2015 - 07:46 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Need help in my new Web Hosting Provider

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,819
  • Joined: 12-June 08

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 07:55 PM

I am confused by your 'create view'. Why is there this whole 'create algorithm' bit?

CREATE VIEW <view name> AS <select statement>


Well. .wait a minute.. I am not sure what's going on in those bottom pictures..

Can you run the select statement by itself.. sans 'create view'
Was This Post Helpful? 1
  • +
  • -

#3 kentlucky   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 85
  • Joined: 20-June 12

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 08:08 PM

View Postmodi123_1, on 06 August 2015 - 10:55 AM, said:

I am confused by your 'create view'. Why is there this whole 'create algorithm' bit?

CREATE VIEW <view name> AS <select statement>


Well. .wait a minute.. I am not sure what's going on in those bottom pictures..

Can you run the select statement by itself.. sans 'create view'



Hello Sir,

thank you so much for your fast response.

On my Local computer, I create the "Create View" command using the phpmyadmin. you can also see it on the save2 image attachment. When i export the sql database, i open the file which hrjobs.sql and this sql is generated:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `jobslist_view` AS select `a`.`job_id` AS `job_id`,`a`.`position` AS `position`,`a`.`description` AS `description`,`a`.`date_posted` AS `date_posted`,`a`.`date_inactive` AS `date_inactive`,`a`.`number_of_vacancy` AS `number_of_vacancy`,`b`.`hospital_id` AS `hospital_id`,`b`.`hospital_name` AS `hospital_name`,`b`.`hospital_address` AS `hospital_address`,`b`.`hospital_phone` AS `hospital_phone`,`b`.`hospital_email` AS `hospital_email`,`c`.`realname` AS `realname`,`c`.`user_id` AS `user_id` from ((`jobs_tbl` `a` left join `hospitals_tbl` `b` on((`a`.`hospital_id` = `b`.`hospital_id`))) left join `users_tbl` `c` on((`a`.`user_id` = `c`.`user_id`))) where (`a`.`archive` = 0);


I try to do the same thing on the web hosting but i've got an error access denied.

I'm sorry if my explanation is not understandable but I will try my best to explain it.

Thank you so much.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,819
  • Joined: 12-June 08

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 08:12 PM

Again - I have never created a view with "ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER" that part. Remove it and try again.
Was This Post Helpful? 1
  • +
  • -

#5 kentlucky   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 85
  • Joined: 20-June 12

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 08:22 PM

Hello Sir,

I now removed the ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER and this is now my sql command:
CREATE VIEW `jobslist_view` AS select `a`.`job_id` AS `job_id`,`a`.`position` AS `position`,`a`.`description` AS `description`,`a`.`date_posted` AS `date_posted`,`a`.`date_inactive` AS `date_inactive`,`a`.`number_of_vacancy` AS `number_of_vacancy`,`b`.`hospital_id` AS `hospital_id`,`b`.`hospital_name` AS `hospital_name`,`b`.`hospital_address` AS `hospital_address`,`b`.`hospital_phone` AS `hospital_phone`,`b`.`hospital_email` AS `hospital_email`,`c`.`realname` AS `realname`,`c`.`user_id` AS `user_id` from ((`jobs_tbl` `a` left join `hospitals_tbl` `b` on((`a`.`hospital_id` = `b`.`hospital_id`))) left join `users_tbl` `c` on((`a`.`user_id` = `c`.`user_id`))) where (`a`.`archive` = 0)


But i got this error message

#1142 - CREATE VIEW command denied to user 'hradmin'@'localhost' for table 'jobslist_view'


Thank you so much for your patience.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,819
  • Joined: 12-June 08

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 08:26 PM

Does your user ID have access and permissions on all those tables?

GRANT CREATE VIEW ON *.* TO '<username>'@'<host>'; 


Barring that contact your ISP and make sure you have permissions to create views.
Was This Post Helpful? 1
  • +
  • -

#7 kentlucky   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 85
  • Joined: 20-June 12

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 08:36 PM

View Postmodi123_1, on 06 August 2015 - 11:26 AM, said:

Does your user ID have access and permissions on all those tables?

GRANT CREATE VIEW ON *.* TO '<username>'@'<host>'; 


Barring that contact your ISP and make sure you have permissions to create views.


Hi Sir,

I am not really sure if my user id has access and permissions on all tables so I try to run this sql code
GRANT CREATE VIEW ON *.* TO 'hradmin'@'localhost';


but i get this error
#1045 - Access denied for user 'hradmin'@'%' (using password: YES)


Thanks
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,819
  • Joined: 12-June 08

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 09:03 PM

Talk to your hosting.
Was This Post Helpful? 1
  • +
  • -

#9 kentlucky   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 85
  • Joined: 20-June 12

Re: Need help in my new Web Hosting Provider

Posted 05 August 2015 - 10:09 PM

View Postmodi123_1, on 06 August 2015 - 12:03 PM, said:

Talk to your hosting.


Hello Sir,

Thanks for your help. I am now sure that this is on the hosting provider side.

Thanks a lot!

Kent
Was This Post Helpful? 0
  • +
  • -

#10 no2pencil   User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 6698
  • View blog
  • Posts: 31,070
  • Joined: 10-May 07

Re: Need help in my new Web Hosting Provider

Posted 06 August 2015 - 04:56 AM

View Postkentlucky, on 06 August 2015 - 01:09 AM, said:

I am now sure that this is on the hosting provider side.

If you don't have grant privileges, you need to speak with whomever is higher up. It's sort of a sticking point.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1