• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
(SOLVED) Help Troubleshoot MySQL Query in CI App

#1
[eluser]zimco[/eluser]
I had my CI application working on my localhost osx platform running apache2, php-5, mysql-5 and then uploaded it to my hosted webserver running Apache version 1.3.41 (Unix), PHP version 5.2.5, MySQL version 4.1.22-standard and i keep getting this error for a database query:
Code:
A Database Error Occurred

Error Number: 1142

SELECT command denied to user 'myhostprefix_manager'@'localhost' for table 'race_events'

SELECT event_results.event_id, `horse_name`, SUM(`score`) AS totalscore, `score` FROM (`event_results`) JOIN scores.race_events ON event_results.event_id = race_events.event_id WHERE scores.race_events.gait = 'Pace' GROUP BY `horse_name`

I created a mysql user via mysql adminstation in the Cpanel and set it's privileges to ALL, yet for the above particular SELECT command i keep getting this error but other mysql queries within the same CI application on the same database work perfectly fine.

I've contacted the webhost company and they say "We're looking into this" and i've never heard from them again.

So, does anybody have any suggestions on what i could do to get the above query to work on the hosted platform? Or does the 4.1.22 version of mysql not allow a join as i have written it? Or is it the webhost mysql privilege settings that i have no control over?

#2
[eluser]mglinski[/eluser]
In cPanel be sure to add the user to the DB.
99% thats your problem
-Matt

#3
[eluser]zimco[/eluser]
Thanks, but i've triple-checked that: the user was added to the database, and the privileges are set to ALL and yet i keep getting the above error.

Also, i found out the webhost does NOT let me FLUSH privileges: i tried and i keep getting "access denied" and told i do not have sufficient privileges to run that procedure.

#4
[eluser]mglinski[/eluser]
start over in a different database.
Might fix it.
-Matt

#5
[eluser]zimco[/eluser]
OK, i tried your suggestion dropped the database and user, created totally new ones imported my data via phpmyadmin and i still get the same error. Thanks for trying to help, but I think it's a case of my webhost has the mysql user privileges hosed-up on the shared server and they don't know how to fix it.

My experience with cheap hosting so far: you get what you pay for....

#6
[eluser]mglinski[/eluser]
mediatemple.net.
Highly Reccomended.
-Matt

#7
[eluser]zimco[/eluser]
Solved. In case anybody runs into a similar problem in the future here was the solution to my problem:

You have to remember that your web hosting provider may add a prefix to the database name! So, if you reference the specific database.tablename like i did in a query you'll have to change all references in your model/controller to take into account the prefix!

While i named my database 'scores' and referenced it as such in the query:

Code:
scores.race_events ON event_results.event_id = race_events.event_id WHERE scores.race_events.gait

I had to change all references to the scores database in the query to:

Code:
myhostprefix_scores.race_events ON event_results.event_id = race_events.event_id WHERE myhostprefix_scores.race_events.gait

So, don't bang your head against the wall or try to blame the webhost like i did, it really was a stupid mistake on my part.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.