Permalink
Find file
Fetching contributors…
Cannot retrieve contributors at this time
434 lines (404 sloc) 21 KB
Reference for the Ocelot GUI debugger (ocelotgui)
=================================================
Before trying to read this reference, it is a good idea to
become familiar with the ocelotgui client and try out the
simple demonstration on the ocelot.ca blog
http://ocelot.ca/blog/blog/2015/03/02/the-ocelotgui-debugger/.
Statements
----------
All debug statements can be entered on the Statement widget,
optionally ending with ";".
Some debug statements can be invoked via the menu or with shortcut keys.
Like other statements, debug statements are editable, are logged,
and can cause error or warning messages to appear in the history.
Most statements can be entered even while the routine is running.
Warning: the focus can change, be sure that the cursor is blinking
on the Statement Widget and not on the Debug Widget before entering
a statement.
The rest of this section shows statements in alphabetical order.
$breakpoint
What it's for: specifying a line where execution must stop.
Syntax: $b[reakpoint [schema_identifier.]routine_identifier
line_number [condition]
Shortcuts: Alt+1, or via menu: Debug|Breakpoint, or click on line
(If a shortcut is used, the breakpoint is set for
the routine that is currently being
displayed, for the line the cursor is on.)
Requirements: The debugger must be running (see $debug).
Example: $breakpoint test.p 5;
Remarks: If $breakpoint succeeds, a red marker will appear on
the left of the line. The breakpoint identifier will
be displayed on the Debug Widget.
Users can set breakpoints on any line.
Users should not set breakpoints twice on the same line.
Later, if the user enters $continue or $next,
execution may stop when a line with a breakpoint is seen.
To turn off a breakpoint, a user may click on the line
(it is a "toggle" switch), or see $clear and $delete.
Re condition: A condition is an expression which must be true, otherwise
the breakpoint is ignored during execution. For example,
$breakpoint p20 5 x = 7;
sets a breakpoint which will only cause execution to halt
if x = 7. The expression must have the form
variable_name = literal, variable_name should be a
declared variable, and the literal should be numeric.
At time of writing, conditions are not well tested.
$clear
What it's for: removing a breakpoint
Syntax: $cl[ear] [schema_identifier.]routine_identifier
line_number
Shortcuts: Alt+6, or via menu: Debug|Clear
(If a shortcut is used, the breakpoint is removed
for the routine that is currently being displayed,
for the line that the cursor is on.)
Requirements: The debugger must be running (see $debug).
The breakpoint must exist (see $breakpoint).
Example: $clear p 5;
Remarks: The breakpoint's red marker should disappear.
If it does not disappear, and a shortcut was used:
make sure the cursor is actually on the line, not
just on the line number, which is considered separate.
$continue
What it's for: running the routine till it ends or till a breakpoint.
Syntax: $co[ntinue]
Shortcuts: Alt+6, or via menu: Debug|Continue
Requirements: The debugger must be running (see $debug).
Example: $continue;
Remarks: $continue differs from $next and $step because it
does not halt on a following statement, it goes on
until it reaches a line with a breakpoint or until
the main routine ends.
$debug
What it's for: beginning a debug session.
Syntax: $deb[ug] [schema_identifier.]routine_identifier
[(parameter list)];
Shortcuts: None, $debug is not on the Debug menu.
Requirements: The debugger must not be already running.
The routine must be set up (see $setup).
The user must have appropriate privileges.
Example: $debug procedure21;
Example: $debug function_x(7);
Remarks: If $debug is successful, then a Debug Widget
will appear (see section Debug Widget).
$exit
What it's for: ending a debug session
Syntax: $exi[t]
Shortcuts: Alt+7, or via menu: Debug|Exit
Requirements: The debugger must be already running.
Example: $exit;
$information status
What it's for: showing information about debugger status
Syntax: $inf[ormation status]
Shortcuts: Alt+8, or via menu: Debug|Information
(At time of writing, Menu does not work properly.)
Example: $information status;
select * from xxxmdbug.information_status;
Remarks: The $information status statement will populate a table
named xxxmdbug.information_status, which contains
debugger_name, debugger_version,
timestamp_of_status_message, number_of_status_message,
icc_count, schema_identifier, routine_identifier,
line_number, is_at_breakpoint, is_at_tbreakpoint,
is_at_routine_exit, stack_depth, last_command,
last_command_result, commands_count.
The most important items of information status are
displayed automatically along with the Debug Widget,
so $information status is only for advanced users.
$install
What it's for: install the debugger
Syntax: $ins[tall]
Shortcuts: None, $install is not on the Debug menu.
Requirements: The user must have appropriate privileges.
Example: $install;
Remarks: A large group of procedures and tables are
placed in a database named xxxmdbug.
Installation may take up to 20 seconds.
It is only necessary to run $install once,
but it may be redone, for example if a new
version of ocelotgui is used.
If $install is not done, none of the other
debug statements are possible.
$leave
What it's for: exit from a loop without executing
Syntax: $l[eave]
Shortcuts: None, $leave is not on the Debug menu.
Requirements: The debugger must be running (see $debug).
The procedure must be executing within a
loop.
Example: Suppose this routine is being debugged:
begin
set x = 0;
while x > 5 do
set x = x + 0;
end while;
end
The user types $continue, which gets the
program running -- in an infinite loop.
One way to break out of the loop is with
"$set x = 6;", another way is with $leave.
Remarks: If the $leave statement is used when the
routine is not in a loop, consequences are
undefined -- this is not recommended.
$next
What it's for: Making the program go to the next statement
in the current routine.
Syntax: $n[ext];
Shortcuts: Alt+3, or via menu: Debug|Next
Requirements: The debugger must be running (see $debug).
Examples: Suppose that the current line in procedure p is
select f() from t;
After $step, the current line changes to
the line following the select statement,
or (if this was the last line) routine exit.
Remarks: $next is different from $step because $step
can drop into a subroutine, $next does not.
$refresh breakpoints
What it's for: showing what breakpoints exist.
Syntax: $refresh breakpoints;
Shortcuts: None. $refresh breakpoints is not on the Debug menu.
Example: $refresh breakpoints;
select * from xxxmdbug.breakpoints;
Remarks: The $refresh breakpoints statement will populate a table
named xxxmdbug.server_breakpoints, which contains
breakpoint_identifier, schema_identifier,
routine_identifier, routine_type, line_number_minimum,
line_number_maximum, condition_identifier,
condition_operator, condition_value, hit_count,
is_temporary, is_temporary_and_to_be_cleared,
is_matching_location, call_stack_depth_when_set,
command. For simple breakpoints which were set up with
$breakpoint schema.routine line_number,
$refresh breakpoints is unnecessary because
breakpointed lines are marked in the Debug Widget.
$refresh call_stack
What it's for: showing what the routines are that got us here.
Syntax: $refresh call_stack
Shortcuts: Alt+B, or via menu: Debug|Refresh call_stack
(At time of writing, Menu does not work properly.)
Example: $refresh call_stack;
select * from xxxmdbug.call_stack;
Remarks: Using menu Debug | Refresh call_stack will
cause two statements: $refresh and select.
The $refresh statement will populate a table
named xxxmdbug.call_stack, which contains
schema_identifier and routine_identifier and
routine_type and line_number, with one row
for each level. Thus, if routine p1 calls
p2 and routine p2 calls p3, there are three rows.
All $refresh statements may be blocked.
$refresh server variables
What it's for: showing the contents of server variables.
Syntax: $refresh server_variables
Shortcuts: Alt+9, or via menu: Debug|Refresh server_variables
(At time of writing, Menu does not work properly.)
Example: $refresh server_variables;
select * from xxxmdbug.server_variables;
Remarks: Using menu Debug | Refresh server_variables will
cause two statements: $refresh and select.
The $refresh statement will populate a table
named xxxmdbug.server_variables, which contains
variable_identifier and value and old_value.
For example, regardless what the routine is,
the available information will include that
there is a server variable named @@innodb_compression_level
which originally was null but now contains 6.
All $refresh statements may be blocked.
This statement is slow.
$refresh user variables
What it's for: showing the contents of user variables.
Syntax: $refresh user_variables
Shortcuts: Alt+A, or via menu: Debug|Refresh user_variables
Example: $refresh user_variables;
select * from xxxmdbug.user_variables;
(At time of writing, Menu does not work properly.)
Remarks: Using menu Debug | Refresh user_variables will
cause two statements: $refresh and select.
The $refresh statement will populate a table
named xxxmdbug.user_variables, which contains
variable_identifier and value and old_value.
Thus, if routine p has:
1 BEGIN
2 SET @x = 1;
3 SET @x = 2;
4 END;
and execution has stopped on line 3, the
available information is that x is a bigint,
which formerly
contained 1, and now contains 2.
All $refresh statements may be blocked.
$refresh variables
What it's for: showing the contents of declared variables.
Syntax: $refresh variables
Shortcuts: Alt+B, or via menu: Debug|Refresh variables
Example: $refresh variables;
select * from xxxmdbug.variables;
Remarks: Using menu Debug | Refresh variables will
cause two statements: $refresh and select.
The $refresh statement will populate a table
named xxxmdbug.variables, which contains
schema_identifier and routine_identifier and
routine_type and token_number_of_declare and
variable_identifier and data_type and value
and old_value and is_in_scope and is_settable
and is_updated_by_set. Thus, if routine p has:
1 BEGIN
2 DECLARE x int;
3 SET x = 1;
4 SET x = 2;
5 END;
and execution has stopped on line 4, the
available information is that x is an integer
which was declared on line 2, which formerly
contained 1, and now contains 2, and is in scope.
All $refresh statements may be blocked.
$set
What it's for: changing the value of a declared variable.
Syntax: $set variable_name = value;
Shortcuts: None, $set is not on the Debug menu.
Requirements: The debugger must be running (see $debug).
The variable must be in scope.
The value must be a literal.
If it is a string literal, it must be
enclosed within two quote marks --
this is due to a temporary limitation).
Example: $set x = 55;
Example: $set y = ''k'';
Remarks: Only declared variables (variables that
are specified with "DECLARE variable_name ...")
can be changed with $set. There is no
statement for changing user variables or
system variables.
$set should only be done while at a breakpoint.
$setup
Requirements: The debugger must be installed (see $install).
Syntax: $setu[p] [schema_identifier.]routine_identifier
[... [schema_identifier.]routine_identifier]
Shortcuts: None, $setup is not on the Debug menu.
Requirements: An $install must be done first.
Only procedures and functions may be listed,
not triggers. For "$setup X.Y", the user must
have CREATE privilege in schema X and EXECUTE
privilege on routine Y.
Example: $setup p;
Example: $setup test.procedure1, test.function2;
Remarks: Unless a routine has been the object of $setup,
it cannot be debugged.
The $setup statement is slow, and if routines
are large it is very slow. However, the effect
is persistent so there is no need to run $setup
for every debug session -- it only needs to be
re-run if a routine is modified.
The maximum number of routines per setup is 10.
The $setup statement makes copies of routines,
but never never changes the original routines.
The default schema is "test".
$step
What it's for: Making the program go to the next statement
in the current routine or a subroutine.
Syntax: $st[ep]
Shortcuts: Alt+5, or via menu: Debug|Step
Requirements: The debugger must be running (see $debug).
Examples: Suppose that the current line in procedure p is
select f() from t;
After $step, the current line changes to
the first line in function f.
Remarks: $step is different from $next because $step
can drop into a subroutine, $next does not.
$tbreakpoint
What it's for: specifying a line where execution must stop, once.
Syntax: $t[breakpoint [schema_identifier.]routine_identifier
line_number
Shortcuts: None, $set is not on the Debug menu.
Requirements: The debugger must be running (see $debug).
Example: $tbreakpoint test.p 5;
Remarks: The effect is the same as for ordinary breakpoints,
see $breakpoint. However, a tbreakpoint (temporary
breakpoint) becomes disabled after being hit once.
Generally it is better to use $breakpoint and
$clear; $tbreakpoint is not recommended.
Debug Widget
------------
The Debug Widget appears at the bottom of the window after
execution of $debug, and disappears after execution of $exit.
It is a tabbed widget, that is, if $setup was done for multiple
routines, then the debug widget has multiple tabs and the user
can look at a particular routine by clicking its tab.
Above the widget is a line with information about current state.
The state information includes the current position (what routine
and line number is currently being executed), the result of the
last debug statement, and whether the execution is currently halted
(it will show "stopped at breakpoint" initially because there is
automatically a temporary breakpoint at the start) (it will show
"executing" if it is running) (it will show "routine end" at end).
For example the line may contain
"Debugger status = (Current position: `test`.`p24` line 3)
(Last debug command: tbreakpoint test.p24 3, result: OK, breakpoint_identifier=3)
(STOPPED AT BREAKPOINT)"
Below the tab is a display of the routine source, with line numbers
on the left. This will appear to be editable; however, the only
reason for that is so that $breakpoint and $clear will work.
The line which is currently being executed will be highlighted.
A line which has a breakpoint will have a mark on the left.
The Debug Widget shares the settings of the Statement Widget.
For example, if the user clicks Settings | Statement Widget and
changes the background color to yellow, then the Debug Widget
will also have a yellow background.
Maintenance
-----------
The $install statement places many routines and tables in the
xxxmdbug database; to uninstall, simply drop the database.
The $setup statement places copies of routines in the same
databases that the original routines are in. These are not
dropped automatically; users or administrators should watch
for obsolete setup results.
The $refresh statements make temporary tables, which may
become large but which should disappear at session end.
Privileges
----------
To do $install, one needs authorization to create a new
database, xxxmdbug, and populate it. The user who does $install may
then choose to grant or revoke privileges on the procedures
of xxxmdbug so that debugging is limited to certain users.
To do $setup routine_name(s), one needs authorization to execute the
named routines and to create routines in the same database.
To do $debug, one needs authorization to execute both the
original routine and the copy that was made by $setup.
Also, one needs READ or EXECUTE authorization for objects in xxxmdbug.
Also, one needs the SUPER privilege.
Security
--------
Before allowing ocelotgui in production systems, administrators should know:
The SUPER privilege https://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_super
allows ordinary users to do a few things which ordinarily they can't.
For anyone who knows the workings of the system, it is possible to
monitor or disrupt debugger activity. This means, for example, that
if a debugger user asks what is the current value of variable X,
some other user (with SUPER privilege) could see that the request
was made and could see the value.
The debugger runs in a separate thread, this separate thread will
attempt to do a CONNECT every time the user calls $debug, with the same
options and credentials as the one used for the main thread.
For passing messages between the debugger thread and the main
thread, the debugger makes heavy use of a system variable named
@@init_connect. It is our belief that the debugger will not interfere
with the ordinary usage of @@init_connect (for specifying what
to do when connecting); however, the reverse is definitely not true.
That is, if someone changes @@init_connect while the debugger is
running, the debugger could return unexpected results or even hang.
Simultaneity
------------
It is designed, but not tested in the field, that the debugger should
operate while others are simultaneously accessing the same data with
the same DBMS instance. Naturally the debugger will slow down other
operations if (say) a user chooses to stop at a breakpoint while a
table is locked -- but that is a user fault not a product flaw.
Although the debugger is not terribly fast, the majority of the time in a stored
procedure is probably taken up by the SQL statements rather than the
debugger's processing. Therefore it should be possible to run a long
routine for hours or days, occasionally monitoring it to see what the
state of the variables is.
Further information
-------------------
The low-level part of the debugger is based on a product from
Hewlett-Packard called MDBug. https://launchpad.net/mdbug
An interesting early document is http://bazaar.launchpad.net/~hp-mdbug-team/mdbug/trunk/view/head:/debugger.txt.