astralaaron Posted August 13, 2011 Share Posted August 13, 2011 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 Link to comment Share on other sites More sharing options...
astralaaron Posted August 13, 2011 Author Share Posted August 13, 2011 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? Link to comment Share on other sites More sharing options...
astralaaron Posted August 13, 2011 Author Share Posted August 13, 2011 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 More sharing options...
astralaaron Posted August 13, 2011 Author Share Posted August 13, 2011 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 More sharing options...
justsomeguy Posted August 15, 2011 Share Posted August 15, 2011 You shouldn't be inserting into the information_schema database yourself. Let MySQL do the work.http://www.google.com/search?client=opera&...channel=suggesthttp://www.google.com/search?client=opera&...125&bih=701 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.