Jump to content

information_schema permission


astralaaron
 Share

Recommended Posts

I am trying to import a routine into information_schema. In the permissions it says that root has ALL access to the table but when I try to import the routine I keep getting:#1044 - Access denied for user 'root'@'localhost' to database 'information_schema' I tried to do it directly from Webmin but it says I need to add a primary key to the table before I can edit it. When I try to add a new field as a primary key I get an error:failed : Access denied for user 'root'@'localhost' to database 'information_schema'anyone know how to add a routine?edit I just found where to add privileges to the information_schema table in phpMyAdmin.. i logged out, back in, and checked to see if the privileges were saved, they were.... but then I tried inserting into the routines table again and I got the same message about root@localhost does not have privileges to database information_schema

Edited by astralaaron
Link to comment
Share on other sites

In the database where I am exporting this routine it looks like they added their user to the SCHEMA_PRIVILEGES table for all the different SQL commands like INSERT, SELECT, etc.. I have a feeling I need to figure out how to add root@localhost to this table but I have the same problem! no access!edit I am finding others with this same issue searching around on google and they are getting responses like: "Are you sure your privileges are set up properly?" anytime I try to set a privilege for information_schema it tells me I don't have privileges to do so...edit 2 Should I not be trying to insert it and instead trying to understand this?: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.htmlthis is what I am trying to insert. ROUTINES is a table inside of the information_schema database

INSERT INTO `ROUTINES` (`SPECIFIC_NAME`, `ROUTINE_CATALOG`, `ROUTINE_SCHEMA`, `ROUTINE_NAME`, `ROUTINE_TYPE`, `DTD_IDENTIFIER`, `ROUTINE_BODY`, `ROUTINE_DEFINITION`, `EXTERNAL_NAME`, `EXTERNAL_LANGUAGE`, `PARAMETER_STYLE`, `IS_DETERMINISTIC`, `SQL_DATA_ACCESS`, `SQL_PATH`, `SECURITY_TYPE`, `CREATED`, `LAST_ALTERED`, `SQL_MODE`, `ROUTINE_COMMENT`, `DEFINER`, `CHARACTER_SET_CLIENT`, `COLLATION_CONNECTION`, `DATABASE_COLLATION`) VALUES('GetDistance', NULL, 'thedatabasename', 'GetDistance', 'FUNCTION', 'decimal(10,5)', 'SQL', NULL, NULL, NULL, 'SQL', 'NO', 'CONTAINS SQL', NULL, 'DEFINER', '2011-06-24 20:33:24', '2011-06-24 20:33:24', '', '', 'root@localhost', 'utf8', 'utf8_general_ci', 'latin1_general_ci');

Yeah...I am sure I should not be trying to insert it like this... anyone know a tutorial to show me an example for creating it as a user defined function?

Edited by astralaaron
Link to comment
Share on other sites

I found an example of the function I need to add to my information_schema routines...source: http://johndyer.name/post/LatitudeLongitud...SQL-Server.aspx

CREATE FUNCTION CoordinateDistanceMiles(@Latitude1 float,@Longitude1 float,@Latitude2 float,@Longitude2 float)RETURNS float AS  BEGIN -- CONSTANTSDECLARE @EarthRadiusInMiles float;SET @EarthRadiusInMiles = 3963.1DECLARE @PI  float;SET @PI = PI();-- RADIANS conversionDECLARE @lat1Radians float;DECLARE @long1Radians float;DECLARE @lat2Radians float;DECLARE @long2Radians float;SET @lat1Radians = @Latitude1 * @PI / 180;SET @long1Radians = @Longitude1 * @PI / 180;SET @lat2Radians = @Latitude2 * @PI / 180;SET @long2Radians = @Longitude2 * @PI / 180;RETURN Acos(Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) + Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) + Sin(@lat1Radians) * Sin(@lat2Radians)) * @EarthRadiusInMiles;END

Is this not written for MySQL? doesn't look like it likes the @.. would it be a nightmare to convert this to be compatible with MySQL? #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 '@Latitude1 float,@Longitude1 float,@Latitude2 float,@Longitude2 float)' at line 2

Link to comment
Share on other sites

This SQL query works with the zipcode table I have:

SET @orig_lat = 121.9763;SET @orig_lon = 37.40445;SET @dist =300;SELECT b.zipcode, 3956 *2 * ASIN( SQRT( POWER( SIN( (@orig_lat - abs( b.latitude ) ) * pi( ) /180 /2 ) , 2 ) + COS( @orig_lat * pi( ) /180 ) * COS( abs( b.latitude ) * pi( ) /180 ) * POWER( SIN( (@orig_lon - b.longitude) * pi( ) /180 /2 ) , 2 ) )) AS distanceFROM tbl_zipcodes AS bHAVING distance < @distORDER BY distanceLIMIT 100

Does anyone know how to translate that math from SQL to PHP? I don't know how to make it a user defined function in SQL

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...