27.3.5 JavaScript Stored Programs—Obtaining Session Information

For general information about stored routines in MySQL, see Section 27.2, “Using Stored Routines”.

The MLE component provides two loadable functions, listed here:

• mle_session_reset()

  Calling this function cleans up the current MLE session state, removing any observable output from mle_session_state(). It also resets the session time zone, so that subsequent calls to JavaScript stored routines use the time zone set most recently in the session.

• mle_session_state()

  Use this loadable function to obtain session information about the MLE stored program that was most recently executed. mle_session_state() takes one argument, a session state key (a string), and displays a session state value. A session state value is limited to a maximum size of 64K (equivalent to 16000 4-byte characters). This is a cyclic buffer; when the available space has been used up, a new entry overwrites the oldest one. Possible session state keys are listed here, with their descriptions:

  • is_active: Returns 1 if the current MySQL user session is an MLE session, otherwise 0.

  • stdout: Output anything written by the stored program using console.log().

  • stderr: Output anything written by the stored program using console.error().

  • stack_trace: If execution of the MLE stored program is unsuccessful, this contains a stack trace which may help in pinpointing the source of the error.

    Syntax and similar errors encountered by an unsuccessful CREATE FUNCTION or CREATE PROCEDURE statement are not written here, only runtime errors raised during execution of a stored function or stored procedure.

  • stored_functions: Returns the number of currently cached stored functions in the current session.

  • stored_procedures: Returns the number of currently cached stored procedures in the current session.

  • stored_programs: Returns the number of currently cached stored programs (stored functions and stored procedures) in the current session.

  The session state key is a literal string value and must be quoted.

  Prior to the invocation of any MLE stored programs, all three of these session state values are empty. Exiting the client and restarting the session clears all of them.

  The next two examples illustrate retrieval of the session state values. We begin by creating a stored procedure mle_states() that displays all session state values, like this:

  mysql> delimiter //
  mysql> CREATE PROCEDURE mle_states()
      -> BEGIN
      ->   SELECT
      ->     mle_session_state("is_active") AS '-ACTIVE-',
      ->     mle_session_state("stdout") AS '-STDOUT-',
      ->     mle_session_state("stderr") AS '-STDERR-',
      ->     mle_session_state("stack_trace") AS '-STACK-',
      ->     mle_session_state("stored_functions") AS '-FUNCS-',
      ->     mle_session_state("stored_procedures") AS '-PROCS-',
      ->     mle_session_state("stored_programs") AS '-PROGS-';
      -> END//
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> delimiter ;

  Prior to running any MLE stored programs, all of the values in the output from mle_states() are 0 or empty, as shown here:

  mysql> CALL mle_states();
  +----------+----------+----------+---------+---------+---------+---------+
  | -ACTIVE- | -STDOUT- | -STDERR- | -STACK- | -FUNCS- | -PROCS- | -PROGS- |
  +----------+----------+----------+---------+---------+---------+---------+
  | 0        |          |          |         | 0       | 0       | 0       |
  +----------+----------+----------+---------+---------+---------+---------+
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

  Now we create a JavaScript stored procedure pc1() that uses console.log() and console.error() in a short loop to write multiple times to stdout and to stderr, like this:

  mysql> CREATE PROCEDURE pc1()
      -> LANGUAGE JAVASCRIPT AS
      -> $$
      $>   let x = 0
      $>   
      $>   do  {
      $>     console.log(`This is message #${++x} to stdout.`)
      $>     console.error(`This is message #${x} to stderr.`)
      $>   }  
      $>   while(x < 3)
      $> $$
      -> ;
  Query OK, 0 rows affected (0.02 sec)

  Following the execution of the CREATE PROCEDURE statement just shown, mle_states() shows an active MLE session. No stored programs have yet been run, so none have been cached; this means the columns reflecting JavaSCript stored functions, procedures, and programs all show 0. The output is shown here:

  mysql> CALL mle_states;
  +----------+----------+----------+---------+---------+---------+---------+
  | -ACTIVE- | -STDOUT- | -STDERR- | -STACK- | -FUNCS- | -PROCS- | -PROGS- |
  +----------+----------+----------+---------+---------+---------+---------+
  | 1        |          |          |         | 0       | 0       | 0       |
  +----------+----------+----------+---------+---------+---------+---------+
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

  Note

  Quoting strings with backtick (`) characters allows us to use variable interpolation in the output. If you are unfamiliar with this quoting mechanism, see Template Literals at Mozilla Developer for more information.

  Invoking pc1() followed by mle_states() produces the result shown here:

  mysql> CALL pc1();
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> CALL mle_states()\G
  *************************** 1. row ***************************
  -ACTIVE-: 1
  -STDOUT-: This is message #1 to stdout.
  This is message #2 to stdout.
  This is message #3 to stdout.
  
  -STDERR-: This is message #1 to stderr.
  This is message #2 to stderr.
  This is message #3 to stderr.
  
   -STACK-: 
   -FUNCS-: 0
   -PROCS-: 1
   -PROGS-: 1
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

  Executing the stored procedure starts an MLE session, so is_active (-ACTIVE-) is now 1.

  Successive writes to stdout or stderr within the same session are appended to any existing content. To see this, call pc1() again, then check the output from mle_states(), as shown here:

  mysql> CALL pc1();
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> CALL mle_states()\G
  *************************** 1. row ***************************
  -ACTIVE-: 1
  -STDOUT-: This is message #1 to stdout.
  This is message #2 to stdout.
  This is message #3 to stdout.
  This is message #1 to stdout.
  This is message #2 to stdout.
  This is message #3 to stdout.
  
  -STDERR-: This is message #1 to stderr.
  This is message #2 to stderr.
  This is message #3 to stderr.
  This is message #1 to stderr.
  This is message #2 to stderr.
  This is message #3 to stderr.
  
   -STACK-: 
   -FUNCS-: 0
   -PROCS-: 1
   -PROGS-: 1
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

  Since no errors were produced by pc1(), the stack trace remains empty. To test the stack trace, we can create a modified copy of pc1() in which we change the reference to console.log() to the undefined function console.lob(), like this:

  mysql> CREATE PROCEDURE pc2()
      -> LANGUAGE JAVASCRIPT AS
      -> $$
      $>   let x = 0
      $>   do  {
      $>     console.lob(`This is message #${++x} to stdout.`)
      $>     console.error(`This is message #${x} to stderr.`)
      $>   }  
      $>   while(x < 3)
      $> $$
      -> ;
  Query OK, 0 rows affected (0.02 sec)

  CREATE PROCEDURE pc2()
  LANGUAGE JAVASCRIPT AS
    $$
     let x = 0
     do  {
       console.lob(`This is message #${++x} to stdout.`)
       console.error(`This is message #${x} to stderr.`)
     }  
     while(x < 3)
    $$
  ;

  mysql> CREATE PROCEDURE pc2()
      -> LANGUAGE JAVASCRIPT AS
      -> $$
      $>   let x = 0
      $>   do  {
      $>     console.lob(`This is message #${++x} to stdout.`)
      $>     console.error(`This is message #${x} to stderr.`)
      $>   }  
      $>   while(x < 3)
      $> $$
      -> ;
  Query OK, 0 rows affected (0.02 sec)

  The CREATE PROCEDURE statement runs successfully, but when we attempt to invoke pc2(), an error results, as shown here:

  mysql> CALL pc2();
  ERROR 6113 (HY000): JavaScript> TypeError: (intermediate value).lob is not a function

  Following this, when we invoke mle_states() again, we see that, since we are within the same session, the stdout and stderr fields still contain the content written to them previously. The stack trace from the error just shown is displayed in the last column of the output:

  mysql> CALL mle_states()\G
  *************************** 1. row ***************************
  -ACTIVE-: 1
  -STDOUT-: This is message #1 to stdout.
  This is message #2 to stdout.
  This is message #3 to stdout.
  This is message #1 to stdout.
  This is message #2 to stdout.
  This is message #3 to stdout.
  
  -STDERR-: This is message #1 to stderr.
  This is message #2 to stderr.
  This is message #3 to stderr.
  This is message #1 to stderr.
  This is message #2 to stderr.
  This is message #3 to stderr.
  
  -STACK-: <js> pc2:3:6-54
  
  -FUNCS-: 0
  -PROCS-: 2
  -PROGS-: 2
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

  In addition the values of the stored_functions, stored_procedures, and stored_programs keys for mle_session_state() are are 0, 2, and 2, respectively—we have created 2 stored JavaScript procedures, and no JavaScript stored functions, for a total of 2 JavaScript stored programs.

  The stack trace does not persist between JavaScript stored program invocations.

  To clear all information from all fields in the output of mle_states(), call mle_session_reset(), like this:

  mysql> SELECT mle_session_reset();
  mysql> SELECT mle_session_reset();
  +------------------------------------------+
  | mle_session_reset()                      |
  +------------------------------------------+
  | The session state is successfully reset. |
  +------------------------------------------+
  1 row in set (0.00 sec)

  Invoking mle_states() again produces the same as the initial result, before any stored JavaScript stored programs had been used.

  mysql> CALL mle_states;
  +----------+----------+----------+---------+---------+---------+---------+
  | -ACTIVE- | -STDOUT- | -STDERR- | -STACK- | -FUNCS- | -PROCS- | -PROGS- |
  +----------+----------+----------+---------+---------+---------+---------+
  | 0        |          |          |         | 0       | 0       | 0       |
  +----------+----------+----------+---------+---------+---------+---------+
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)

Alternatively, you can clear stdout and stderr from within a JavaScript routine using console.clear().