Client Login Area
Main Menu
Results 1 to 9 of 9

Thread: Need some help!

  1. #1
    Verified Community Member
    Join Date
    March 8, 2010
    Location
    Lakewood, CO
    Posts
    700

    Default Need some help!

    MySQL said:

    #1227 - Access denied; you need the SUPER privilege for this operation

    The following is what im running

    Post is to long look at second post for the mysql im trying to execute.
    Living to Kill, Killing to Live

    Come learn more about Project NLIXD!

  2. #2
    Verified Community Member
    Join Date
    March 8, 2010
    Location
    Lakewood, CO
    Posts
    700

    Default

    Code:
    --
    -- Dumping routines for database 
    /*!50003 DROP FUNCTION IF EXISTS `CreateUserOnAccount` */;
    /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
    /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
    /*!50003 SET @saved_col_connection = @@collation_connection */ ;
    /*!50003 SET character_set_client  = utf8 */ ;
    /*!50003 SET character_set_results = utf8 */ ;
    /*!50003 SET collation_connection  = utf8_general_ci */ ;
    /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
    /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
    DELIMITER ;;
    /*!50003 CREATE*/ /*!50020 DEFINER=`username here`@`ip here`*/ /*!50003 FUNCTION `CreateUserOnAccount`(accountName VARCHAR(50), characterName VARCHAR(30), characterID INT) RETURNS varchar(100) CHARSET latin1
    BEGIN
    		
    		DECLARE character_count INT DEFAULT 0;
    		DECLARE max_character_count INT DEFAULT 3;
    		DECLARE is_id_free INT DEFAULT 0;
    		DECLARE is_name_free INT DEFAULT 0;
    		DECLARE errorMsg VARCHAR(100) DEFAULT "";
    		DECLARE accountID INT DEFAULT NULL;
    
    		SELECT `id` INTO accountID FROM `account` WHERE `name` = accountName;
    
    		IF ISNULL(accountID) THEN
    			SET errorMsg = "Account with the specified name does not exist.";
    		ELSE
    			SELECT COUNT(*) INTO character_count FROM `character` WHERE `account_id` = accountID;
    			SELECT `max_characters_per_account` INTO max_character_count FROM `game_constant`;
    
    			IF character_count > max_character_count THEN
    				SET errorMsg = "No free character slots available in the account.";
    			ELSE
    				SELECT COUNT(*) INTO is_id_free FROM `character` WHERE `id` = characterID;
    				
    				IF is_id_free > 0 THEN
    					SET errorMsg = "The specified CharacterID is not available for use.";
    				ELSE
    					SELECT COUNT(*) INTO is_name_free FROM `user_character` WHERE `name` = characterName;
    						
    					IF is_name_free > 0 THEN
    						SET errorMsg = "The specified character name is not available for use.";
    					ELSE
    						INSERT INTO `character` SET `id` = characterID, `name`	= characterName, `account_id`= 	accountID;
    					END IF;
    				END IF;
    			END IF;
    		END IF;
    				
    		RETURN errorMsg;
      
    END */;;
    DELIMITER ;
    /*!50003 SET sql_mode              = @saved_sql_mode */ ;
    /*!50003 SET character_set_client  = @saved_cs_client */ ;
    /*!50003 SET character_set_results = @saved_cs_results */ ;
    /*!50003 SET collation_connection  = @saved_col_connection */ ;
    /*!50003 DROP PROCEDURE IF EXISTS `Rebuild_Views` */;
    /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
    /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
    /*!50003 SET @saved_col_connection = @@collation_connection */ ;
    /*!50003 SET character_set_client  = utf8 */ ;
    /*!50003 SET character_set_results = utf8 */ ;
    /*!50003 SET collation_connection  = utf8_general_ci */ ;
    /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
    /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
    DELIMITER ;;
    /*!50003 CREATE*/ /*!50020 DEFINER=`username`@`ip`*/ /*!50003 PROCEDURE `Rebuild_Views`()
    BEGIN
    	
    	CALL Rebuild_View_NPC_Character();
        
    END */;;
    DELIMITER ;
    /*!50003 SET sql_mode              = @saved_sql_mode */ ;
    /*!50003 SET character_set_client  = @saved_cs_client */ ;
    /*!50003 SET character_set_results = @saved_cs_results */ ;
    /*!50003 SET collation_connection  = @saved_col_connection */ ;
    /*!50003 DROP PROCEDURE IF EXISTS `Rebuild_View_NPC_Character` */;
    /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
    /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
    /*!50003 SET @saved_col_connection = @@collation_connection */ ;
    /*!50003 SET character_set_client  = latin1 */ ;
    /*!50003 SET character_set_results = latin1 */ ;
    /*!50003 SET collation_connection  = latin1_swedish_ci */ ;
    /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
    /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
    DELIMITER ;;
    /*!50003 CREATE*/ /*!50020 DEFINER=`username`@`ip`*/ /*!50003 PROCEDURE `Rebuild_View_NPC_Character`()
    BEGIN
    	
    	DROP VIEW IF EXISTS `npc_character`;
    	CREATE ALGORITHM=UNDEFINED DEFINER=`username`@`ip` SQL SECURITY DEFINER VIEW `npc_character` AS SELECT *FROM `character` WHERE `account_id` IS NULL;
        
    END */;;
    DELIMITER ;
    /*!50003 SET sql_mode              = @saved_sql_mode */ ;
    /*!50003 SET character_set_client  = @saved_cs_client */ ;
    /*!50003 SET character_set_results = @saved_cs_results */ ;
    /*!50003 SET collation_connection  = @saved_col_connection */ ;
    /*!50003 DROP PROCEDURE IF EXISTS `TestProc` */;
    /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
    /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
    /*!50003 SET @saved_col_connection = @@collation_connection */ ;
    /*!50003 SET character_set_client  = utf8 */ ;
    /*!50003 SET character_set_results = utf8 */ ;
    /*!50003 SET collation_connection  = utf8_general_ci */ ;
    /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
    /*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
    DELIMITER ;;
    /*!50003 CREATE*/ /*!50020 DEFINER=`username`@`ip`*/ /*!50003 PROCEDURE `TestProc`(a INT)
    BEGIN
    		UPDATE `test` SET `fvalue`="asdfasdfasdfasdf" WHERE `id`=a;
    END */;;
    DELIMITER ;
    /*!50003 SET sql_mode              = @saved_sql_mode */ ;
    /*!50003 SET character_set_client  = @saved_cs_client */ ;
    /*!50003 SET character_set_results = @saved_cs_results */ ;
    /*!50003 SET collation_connection  = @saved_col_connection */ ;
    
    --
    -- Final view structure for view `npc_character`
    --
    
    /*!50001 DROP TABLE IF EXISTS `npc_character`*/;
    /*!50001 DROP VIEW IF EXISTS `npc_character`*/;
    /*!50001 SET @saved_cs_client          = @@character_set_client */;
    /*!50001 SET @saved_cs_results         = @@character_set_results */;
    /*!50001 SET @saved_col_connection     = @@collation_connection */;
    /*!50001 SET character_set_client      = latin1 */;
    /*!50001 SET character_set_results     = latin1 */;
    /*!50001 SET collation_connection      = latin1_swedish_ci */;
    /*!50001 CREATE ALGORITHM=UNDEFINED */
    /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
    /*!50001 VIEW `npc_character` AS select `character`.`id` AS `id`,`character`.`account_id` AS `account_id`,`character`.`character_template_id` AS `character_template_id`,`character`.`name` AS `name`,`character`.`map_id` AS `map_id`,`character`.`shop_id` AS `shop_id`,`character`.`chat_dialog` AS `chat_dialog`,`character`.`ai_id` AS `ai_id`,`character`.`x` AS `x`,`character`.`y` AS `y`,`character`.`respawn_map` AS `respawn_map`,`character`.`respawn_x` AS `respawn_x`,`character`.`respawn_y` AS `respawn_y`,`character`.`body_id` AS `body_id`,`character`.`cash` AS `cash`,`character`.`level` AS `level`,`character`.`exp` AS `exp`,`character`.`statpoints` AS `statpoints`,`character`.`hp` AS `hp`,`character`.`mp` AS `mp`,`character`.`stat_maxhp` AS `stat_maxhp`,`character`.`stat_maxmp` AS `stat_maxmp`,`character`.`stat_minhit` AS `stat_minhit`,`character`.`stat_maxhit` AS `stat_maxhit`,`character`.`stat_defence` AS `stat_defence`,`character`.`stat_agi` AS `stat_agi`,`character`.`stat_int` AS `stat_int`,`character`.`stat_str` AS `stat_str` from `character` where isnull(`character`.`account_id`) */;
    /*!50001 SET character_set_client      = @saved_cs_client */;
    /*!50001 SET character_set_results     = @saved_cs_results */;
    /*!50001 SET collation_connection      = @saved_col_connection */;
    
    --
    -- Final view structure for view `user_character`
    --
    
    /*!50001 DROP TABLE IF EXISTS `user_character`*/;
    /*!50001 DROP VIEW IF EXISTS `user_character`*/;
    /*!50001 SET @saved_cs_client          = @@character_set_client */;
    /*!50001 SET @saved_cs_results         = @@character_set_results */;
    /*!50001 SET @saved_col_connection     = @@collation_connection */;
    /*!50001 SET character_set_client      = latin1 */;
    /*!50001 SET character_set_results     = latin1 */;
    /*!50001 SET collation_connection      = latin1_swedish_ci */;
    /*!50001 CREATE ALGORITHM=UNDEFINED */
    /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
    /*!50001 VIEW `user_character` AS select `character`.`id` AS `id`,`character`.`account_id` AS `account_id`,`character`.`character_template_id` AS `character_template_id`,`character`.`name` AS `name`,`character`.`map_id` AS `map_id`,`character`.`shop_id` AS `shop_id`,`character`.`chat_dialog` AS `chat_dialog`,`character`.`ai_id` AS `ai_id`,`character`.`x` AS `x`,`character`.`y` AS `y`,`character`.`respawn_map` AS `respawn_map`,`character`.`respawn_x` AS `respawn_x`,`character`.`respawn_y` AS `respawn_y`,`character`.`body_id` AS `body_id`,`character`.`cash` AS `cash`,`character`.`level` AS `level`,`character`.`exp` AS `exp`,`character`.`statpoints` AS `statpoints`,`character`.`hp` AS `hp`,`character`.`mp` AS `mp`,`character`.`stat_maxhp` AS `stat_maxhp`,`character`.`stat_maxmp` AS `stat_maxmp`,`character`.`stat_minhit` AS `stat_minhit`,`character`.`stat_maxhit` AS `stat_maxhit`,`character`.`stat_defence` AS `stat_defence`,`character`.`stat_agi` AS `stat_agi`,`character`.`stat_int` AS `stat_int`,`character`.`stat_str` AS `stat_str` from `character` where (`character`.`account_id` is not null) */;
    /*!50001 SET character_set_client      = @saved_cs_client */;
    /*!50001 SET character_set_results     = @saved_cs_results */;
    /*!50001 SET collation_connection      = @saved_col_connection */;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    Living to Kill, Killing to Live

    Come learn more about Project NLIXD!

  3. #3
    Verified Community Member
    Join Date
    March 8, 2010
    Location
    Lakewood, CO
    Posts
    700

    Default

    I figured out what you (if you will accept) need to do to allow my stored procedures to go through. These stored procedures are for this application that I'll be running on my windows box at home

    http://www.netgore.com

    All ill be using is mysql for the database. I'll be hosting the server on my extra box. Now only problem is this

    GRANT SUPER ON * . * TO 'username'@'%' IDENTIFIED BY 'password'
    If you would run that as the root person on your MySQL server (need a few changes to match my needs) but that will allow me to use the stored procedure. I got fed up of getting MySQL to work remotely on my Linux box at my house.

    http://www.netgore.com/forums/post/c...ml#comment-139

    explains i need super enabled. :'( Stupid system.
    Living to Kill, Killing to Live

    Come learn more about Project NLIXD!

  4. #4
    ASPnix Administration Roma's Avatar
    Join Date
    June 27, 2005
    Location
    Denver, CO
    Posts
    4,066

    Default

    The % is applied automatically on all users. It means you can access server from any IP. Obviously your users can't be a super root ;)
    Roma
    Friendly ASPnix Administrator


    https://www.aspnix.com
    roma@aspnix.com



    ASPnix on Facebook

  5. #5
    Verified Community Member
    Join Date
    March 8, 2010
    Location
    Lakewood, CO
    Posts
    700

    Default

    couldn't we just replace % with the ip of my mysql database? Or does it not work like that? If not i guess im off to work on getting remote mysql to work with my linuxe machine :'(
    Living to Kill, Killing to Live

    Come learn more about Project NLIXD!

  6. #6
    ASPnix Administration Roma's Avatar
    Join Date
    June 27, 2005
    Location
    Denver, CO
    Posts
    4,066

    Default

    % means ALL ips. no point of replacing with just 1. you should be able to access and work with it.
    Roma
    Friendly ASPnix Administrator


    https://www.aspnix.com
    roma@aspnix.com



    ASPnix on Facebook

  7. #7
    Verified Community Member
    Join Date
    March 8, 2010
    Location
    Lakewood, CO
    Posts
    700

    Default

    Thanks for the info roma. Working on my linux box. Got the box to accept remote connections. Just need to get privileges set up correctly now lol

    EDIT: but im not giving super to all my users. just the program where im coming from which would be my ip and my ip only.
    Last edited by crzyone9584; May 4th, 2010 at 04:43 AM.
    Living to Kill, Killing to Live

    Come learn more about Project NLIXD!

  8. #8
    ASPnix Administration Roma's Avatar
    Join Date
    June 27, 2005
    Location
    Denver, CO
    Posts
    4,066

    Default

    Very strange. I haven't seen such issue before...
    Roma
    Friendly ASPnix Administrator


    https://www.aspnix.com
    roma@aspnix.com



    ASPnix on Facebook

  9. #9
    Verified Community Member
    Join Date
    March 8, 2010
    Location
    Lakewood, CO
    Posts
    700

    Default

    I know. It is very annoying. Thankfully I found a solution for now.
    Living to Kill, Killing to Live

    Come learn more about Project NLIXD!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •