Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Show Processlist

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Show Processlist

      MySQL database provides a multi-user environment, that allows multiple clients to access the database at the same time. A process is defined as the operations performed by a user on the MySQL Server. Multiple processes can be run on a MySQL Server concurrently by multiple users.

      What is MySQL Process List?

      The MySQL process list is defined as the list of operations currently being performed by the set of user threads executing within the server.
      If a user has the PROCESS privilege, they can see all threads in a server, including threads of other users. But if a user does not have such privilege, non-anonymous users have access to information about their own threads only; while anonymous users have no access to thread information.
      To retrieve information about these processes running on a MySQL Server, we can use the SHOW PROCESSLIST command.

      The MySQL SHOW PROCESSLIST Command

      The MySQL SHOW PROCESSLIST command is used to display information about the current processes running on a MySQL Server.
      This statement is especially useful when dealing with a "too many connections" error, as it provides details about these connections and their operations. Additionally, MySQL reserves one extra connection for administrators with CONNECTION_ADMIN privilege (or SUPER privilege in older versions), to ensure they can always access the system.

      Syntax

      Following is the syntax of the SHOW PROCESSLIST Command 
      SHOW [FULL] PROCESSLIST
      Here, the FULL keyword is optional. But if you omit the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field.

      Example

      Let us see an example to show the usage of SHOW PROCESSLIST command. We will use the '\G' delimiter to print the information table vertically 
      SHOW PROCESSLIST\G

      Output

      Following is the output obtained 
      *************************** 1. row ***************************
           Id: 5
         User: event_scheduler
         Host: localhost
           db: NULL
      Command: Daemon
         Time: 1065209
        State: Waiting on empty queue
         Info: NULL
      *************************** 2. row ***************************
           Id: 56
         User: root
         Host: localhost:51995
           db: customers
      Command: Query
         Time: 0
        State: init
         Info: SHOW PROCESSLIST
      2 rows in set (0.00 sec)

      Example

      Now, let us also use the FULL keyword with the SHOW PROCESSLIST command as shown in the following example 
      SHOW FULL PROCESSLIST\G

      Output

      The output obtained is as shown below 
      *************************** 1. row ***************************
           Id: 5
         User: event_scheduler
         Host: localhost
           db: NULL
      Command: Daemon
         Time: 1065138
        State: Waiting on empty queue
         Info: NULL
      *************************** 2. row ***************************
           Id: 56
         User: root
         Host: localhost:51995
           db: customers
      Command: Query
         Time: 0
        State: init
         Info: SHOW FULL PROCESSLIST
      2 rows in set (0.00 sec)

      Output Explanation

      The output result-set obtained from the SHOW PROCESSLIST command has the following columns
      • Id − It is the identity of a connection.
      • User − This holds the name of a MySQL user who issued the statement.
      • Host − The host name of the client issuing the statement (except for system user, as there is no host for it). The host name for TCP/IP connections is represented in "host_name:client_port" format to make it easier to determine the actions of a client.
      • db − This is the default database for the thread, or NULL if none has been selected.
      • Command − Shows the type of command the corresponding thread is executing on behalf of the client, or shows Sleep if the session is idle.
      • Time − The time in seconds that the thread has been in its current state.
      • State − An action, event, or state that indicates what the thread is doing. Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
      • Info − The statement the thread is executing. If it is executing no statement, NULL is shown.

      Showing Process List Using Client Program

      We can also show the process list using Client Program.

      Syntax

      PHPNodeJSJavaPython
      To retrieve information about processes running on a MySQL Server, through a PHP program, we need to execute the "SHOW PROCESSLIST" command using the mysqli function query() as follows 
      $sql = "SHOW PROCESSLIST";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $db = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      //printf('Connected successfully.');
      $sql = "SHOW PROCESSLIST";
      if($result = $mysqli->query($sql)){
          printf("Show query executed successfully...!\n");
          printf("Process list: \n");
          while($row = mysqli_fetch_array($result)){
              print_r($row);
          }
      }
      if($mysqli->error){
          printf("Error message: ", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as shown below 
      Show query executed successfully...!
      Process list: 
      Array
      (
          [0] => 5
          [Id] => 5
          [1] => event_scheduler
          [User] => event_scheduler
          [2] => localhost
          [Host] => localhost
          [3] =>
          [db] =>
          [4] => Daemon
          [Command] => Daemon
          [5] => 886450
          [Time] => 886450
          [6] => Waiting on empty queue
          [State] => Waiting on empty queue
          [7] =>
          [Info] =>
      )
      Array
      (
          [0] => 602
          [Id] => 602
          [1] => root
          [User] => root
          [2] => localhost:54978
          [Host] => localhost:54978
          [3] => tutorials
          [db] => tutorials
          [4] => Sleep
          [Command] => Sleep
          [5] => 2994
          [Time] => 2994
          [6] =>
          [State] =>
          [7] =>
          [Info] =>
      )
      Array
      (
          [0] => 641
          [Id] => 641
          [1] => root
          [User] => root
          [2] => localhost:56444
          [Host] => localhost:56444
          [3] => tutorials
          [db] => tutorials
          [4] => Query
          [Command] => Query
          [5] => 0
          [Time] => 0
          [6] => init
          [State] => init
          [7] => SHOW PROCESSLIST
          [Info] => SHOW PROCESSLIST
      )