SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    ERROR 1109: Unknown table 'processlist' in information_schema

    Hello there.
    First of all I must say that I am a newbie when it comes to MySQL Database.

    Here is my problem in the STORED PROCEDURE kill_run_aways.
    Code:
    ERROR 1109: Unknown table 'processlist' in information_schema
    But if I try:
    Code:
    mysql> SHOW FULL PROCESSLIST;
    +----+------+----------------+--------------------+---------+------+-------+-----------------------+
    | Id | User | Host           | db                 | Command | Time | State | Info                  |
    +----+------+----------------+--------------------+---------+------+-------+-----------------------+
    | 40 | root | localhost:4049 | db_xxx_clone       | Sleep   |  198 |       | NULL                  |
    | 41 | root | localhost:4050 | db_xxx_clone       | Sleep   |  198 |       | NULL                  |
    | 42 | root | localhost:4074 | NULL               | Sleep   |  503 |       | NULL                  |
    | 43 | root | localhost:4075 | db_xxx_clone       | Sleep   |  491 |       | NULL                  |
    | 44 | root | localhost:4076 | db_xxx_clone       | Sleep   |  467 |       | NULL                  |
    | 46 | root | localhost:4101 | information_schema | Sleep   |  400 |       | NULL                  |
    | 47 | root | localhost:4102 | information_schema | Sleep   |  373 |       | NULL                  |
    | 49 | root | localhost:4192 | information_schema | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
    +----+------+----------------+--------------------+---------+------+-------+-----------------------+
    8 rows in set
    
    mysql>
    If you have link for similar task, please give it me.
    Can you explain any one or any sample code related this.

    Your help would be very appreciated.
    thanks for your time and hints.

    Thanks in advance,
    Chevy.


    Code:
    SQL Statement:
    
    CREATE PROCEDURE `db_xxx_clone`.`kill_run_aways` (IN runtime TINYINT UNSIGNED)
    
            LANGUAGE SQL
    
            NOT DETERMINISTIC
    
            BEGIN
    
            DECLARE done INT DEFAULT 0;
    
            DECLARE connid INT UNSIGNED;
    
            DECLARE cur1 CURSOR FOR SELECT ID FROM information_schema.PROCESSLIST 
    
                WHERE CONCAT ("'", USER, "'@'", SUBSTRING_INDEX(HOST,':',1), "'") IN 
    
                    ( SELECT `USER_PRIVILEGES`.`GRANTEE` AS `GRANTEE` FROM `information_schema`.`USER_PRIVILEGES` 
    
                        WHERE NOT(`USER_PRIVILEGES`.`GRANTEE` IN 
    
                            (SELECT GRANTEE FROM `information_schema`.`USER_PRIVILEGES` 
    
                                WHERE `USER_PRIVILEGES`.`PRIVILEGE_TYPE` = 'SUPER' GROUP BY `USER_PRIVILEGES`.`GRANTEE`))) 
    
                AND COMMAND ='Query'
    
                AND TIME >= runtime;
    
    
    
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    
    
    
            OPEN cur1;
    
    
    
            REPEAT
    
                    FETCH cur1 INTO connid;
    
                    IF NOT done THEN
    
                            KILL connid;
    
                    END IF;
    
            UNTIL done END REPEAT;
    
            CLOSE cur1;
    
            END
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Check your version of the database. This was a table added in MySQL 5.1.7.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for help.

    This is the MySQL version:
    Code:
    mysql> SELECT VERSION();
    +-------------------------+
    | VERSION()               |
    +-------------------------+
    | 5.0.45-community-nt-log |
    +-------------------------+
    1 row in set
    
    mysql>
    You can not upgrade to later version of MySQL
    How can I solve this problem?

    I hope new help.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The version you are using is pretty far out of date. If you can't upgrade with your current host then you need to look elsewhere.
    Not sure what else to tell you, it is pretty hard to query a table that doesn't exist.

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for help.
    I have contacted the provider for upgrade MySQL from 5.0.45-community-nt-log to MySQL last version.
    I hope an answer.

    Greetings
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

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