~hp-mdbug-team/mdbug/trunk

MDBug: A Debugger for MariaDB and MySQL Routines

This file is part of MDBug.

(c) Copyright 2012 Hewlett-Packard Development Company, L.P.

Copying and distribution of this file, with or without modification,
are permitted in any medium without royalty provided the copyright
notice and this notice are preserved. This file is offered as-is,
without any warranty.

Author: Peter Gulutzan
Last Revised: May 31 2012

MariaDB is a trademark of Monty Program Ab.
MySQL is a trademark of Oracle Corporation and/or its affiliates.
DB2 is a trademark of International Business Machines Corporation.
Other names may be trademarks of their respective owners.



Hewlett-Packard has developed a tool for debugging stored procedures
and functions written for MariaDB 5.5 or MySQL 5.5 (and later). MDBug
consists of a low-level debugger (analogous to GDB) written in SQL, and
a high-level Eclipse plugin GUI (analogous to DDD) written in Java.
This document is the original specification / design document for the
low-level debugger portion of MDBug. It is intended for developers who
are interested in the core debugger internals, perhaps with an objective
of creating their own front end for MDBug.


Contents
========

Introduction
  Introduction: The Alternative Methods: Statement-At-A-Time or Two-Connections
  Introduction: Why I gave up on statement-at-a-time
  Introduction: Surrogate Routines
  Introduction: SQL
  Introduction: Example_Procedure
  Introduction: Walk Through A Procedure
  Introduction: Terminology Notes
Install
  Install: Prerequisites
  Install: The xxxmdbug database
  Install: Privileges
  Install: Installation script
Setup
  Setup: arguments
  Setup: Privilege checks
  Setup: Create SETUP_LOG table
  Setup: Create CONNECTIONS table
  Setup: Create ROUTINES table
  Setup: Create TOKENS table
  Setup: Create STATEMENTS table
  Setup: create VARIABLES table
  Setup: create CALL_STACK table
  Setup: create PREPARED_STATEMENTS table
  Setup: create DIAGNOSTICS table
  Setup: create BREAKPOINTS table
  Setup: create STATEMENTS_EXECUTED table
  Setup: a note about concurrency
  Setup: Insert into ROUTINES table
  Setup: Insert into TOKENS table
  Setup: Insert into STATEMENTS table
  Setup: Insert into VARIABLES table
  Setup: Insert into BREAKPOINTS table
  Setup: Check Views and triggers and events
  Setup: checks and warnings
Generate
  Generate: "create [definer clause] procedure|function"
  Generate: routine name
  Generate: starter
  Generate: label
  Generate: if (statement is debuggable)
  Generate: "while (statement is being debugged)"
  Generate: "call icc_copy_variable_to_table_row"
  Generate: "call icc_core"
  Generate: "call icc_copy_table_row_to_variable"
  Generate: "if (exit) signal"
  Generate: "if (execute) iterate"
  Generate: "if (leave) leave"
  Generate: "if (skip) leave"
  Generate: "insert into statements_executed"
  Generate: handlers
  Generate: statement text
  Generate: leave
  Generate: ender
Debuggee Start
  Debuggee Start: Run the create-surrogate-routines script
  Debuggee Start: Become Debuggee Connection
  Debuggee Start: Copy user variables
  Debuggee Start: Wait for attach command
User Commands
  User Command: 'info'
  User Command: 'attach'
  User Command: 'refresh'
  User Command: 'debug'
  User Command: 'continue'
  User Command: 'break' or tbreak
  User Command: 'break' condition
  User Command: 'step'
  User Command: 'next'
  User Command: 'clear'
  User Command: 'stop'
  User Command: 'set'
  User Command: 'finish'
  User Command: 'exit'
  User Command: 'quit'
  User Command: 'execute'
  User Command: 'kill'
  User Command: 'skip'
  User Command: 'leave'
Common Routines
  Common Routines: A note about comments
  Common Routines: tokenizer
  Common Routines: determine what variables are in scope
  Common Routines: diagnostics_handler
  Common Routines: become_debugger_connection
  Common Routines: icc_get_user_command
  Common Routines: icc_process_user_command
  Common Routines: icc_process_user_command_break
  Common Routines: icc_copy_variable_to_table_row
  Common Routines: icc_copy_table_row_to_variable
  Common Routines: icc_breakpoint_check
  Common Routines: icc_send_statement_status
  Common Routines: icc_core
  Common Routines: Change statement text
  Common Routines: change statement text: special handling of SELECT
  Common Routines: Change statement text: special handling of SHOW WARNINGS
  Common Routines: Change statement text: special handling of dynamic SQL
  Common Routines: Change statement text: special handling of RETURN
  Common Routines: Change statement text: special handling of SIGNAL/RESIGNAL
  Common Routines: command
  Common Routines: send
  Common Routines: README table
ICC (Inter-Connection Communication)
  ICC: The choice of method
  ICC: A working example
  ICC: Protocol
  ICC: Notes
Appendixes
  Appendix: Executing SQL statements on Debugger Connection
  Appendix: Debugging without surrogates
  Appendix: Widgets or "Screens and Menus"
  Appendix: Feature requests for the server

Introduction
============

MDBug will enable any MariaDB or MySQL client to view and control
stored procedures and functions, by sending commands like:
* "debug", to specify a routine
* "step", that is, execute a statement at a time
* "set breakpoint", that is, say when to stop executing
  (featuring temporary breakpoints, conditional breakpoints,
  and setting breakpoints while the routine is still executing)
* "step in", that is, drop down to a routine within a routine
* "display variables" for the point where the procedure has stopped
* "set", to change variable values
* "execute", to insert dynamic SQL while at a breakpoint.

Functionally this is as good as debuggers for Microsoft SQL Server,
IBM DB2 and Oracle 11g. But it has no GUI. For Linux people I could
say "it's like GDB (the GNU Project Debugger) but not like DDD (the GUI
Data Display Debugger which is built on top of the GNU debugger)".
I touch lightly on GUI matters in an appendix.

The main sections of this document are:
"Introduction"       which you're reading now
"Install"
"Setup"
"Generate"
"Debuggee Start"
"User Commands"
"Common Routines"
"ICC (Inter-Connection Communication)"
"Appendixes"

The larger sections of this document are organized in
a way that it should be possible to see, a step at a time,
for an example debuggable routine, how the debugger sets
up tables, generates a copy of the routine, and processes
user commands.

Whenever this document uses the term "MySQL", it should be
read as "MySQL or MariaDB". References to bug numbers are
references to bugs in the MySQL bug database, http://bugs.mysql.com/

I can hope that the description will leave
readers with specific and detailed knowledge
about the requirements. I cannot hope that
the description alone is sufficient. Total
enlightenment will arrive when one uses it
along with the source code of the MDBug (Core)
debugger, a product based on this document
which I wrote for Hewlett-Packard Corporation
in the spring of 2012. At time of writing
MDBug (Core) is an alpha product and MDBug
(GUI) is in a final planning stage.


Introduction: The Alternative Methods: Statement-At-A-Time or Two-Connections
-----------------------------------------------------------------------------

The statement-at-a-time method involves taking each
statement in the stored procedure, in the order that
it would be executed, getting the server to execute
that statement alone, and stopping when it sees that
there's a breakpoint. Sometimes this method is called
"emulation", since such a debugger will act as if it's
in a stored procedure, without really being in it.

The two-connections method involves adding "pause" and
"inter-connection communication" instructions inside the
stored procedure, or inside a copy of the stored procedure.
Then it's run in a separate connection -- thus there's
Connection #1 actually running the procedure and pausing
when breakpoints exist, and there's Connection #2 getting
information from Connection #1 and telling it when it can
stop pausing.

Introduction: Why I gave up on statement-at-a-time
--------------------------------------------------

Initially I started this project by taking
a small routine and executing it, one statement
at a time, with a C client and a GTK+/Glade
interface. I got past a few hurdles, like the
difficulty of finding out which direction to
proceed after conditional statements (IF, WHILE).
But by then I realized that there would be many
more hurdles, for example
* emulating automatic locking within functions
* catching declared handlers in outer procedures
* changing privileges to match invoker privileges.

I also noticed that the in-production statement-at-a-time debuggers
(Illatis and Mydebugger) don't have multiple user testimonials
or active discussions on their sites, like dbForge
and TOAD do. It seems they had failed to impress users.

But the decisive factor is in this example procedure:
CREATE PROCEDURE p1 ()
BEGIN
  UPDATE t SET x=function1() where y=function2();
  END
With both methods it's possible to have a breakpoint
before the UPDATE statement. But only with the
two-connections method is it possible to "step in"
to function1() or recognize a breakpoint in function2().
With the statement-at-a-time method that's next to
impossible because that would require emulating the
inner workings of UPDATE.

So the debugger will use the two-connections method.

Introduction: Surrogate Routines
--------------------------------

Another decision was whether to change routines
in place, or redirect to surrogate routines.
Suppose we had
CREATE PROCEDURE test.p () DROP TABLE t;

"Changing in place" means adding the debugger calls
within test.p().

"Redirecting to surrogate routines" means making a
copy of p in the same database, say, "xxxmdbug000pp",
and adding the debugger calls in the copy. Then,
any calls to test.p() are intercepted by the debugger
and redirected to xxxmdbug000pp().

Obviously surrogate routines are harder. I believe
that changing in place is common practice, because I
read a review that says "both [JDeveloper and TOAD
for Oracle] alter the code to debug PL/SQL".
http://www.tek-tips.com/viewthread.cfm?qid=1140524

Nevertheless, I worried because with MySQL to
support "changing in place" one needs DROP and
CREATE privileges for all the items to be debugged.
I feared that administrators would be reluctant to
grant that for any user who wants to debug something.
Besides, if something goes wrong -- and in early
days something will go wrong -- the debugger could
mess up the changes and affect non-debugger users.

So the debugger will use surrogate routines.

Introduction: SQL
-----------------

Another decision was: what language to write the debugger?

I mentioned C earlier, and that's still a candidate for
some GUI application that invokes the debugger. The
current preference for MDBug is Java with Eclipse.
But the preferable language is:
* totally portable, in fact is available automatically
  to every MySQL user already
* able to handle the problem of parsing a dynamic-SQL
  statement while inside a stored procedure.

So the debugger will be written in SQL.

Introduction: Example_Procedure
-------------------------------

In the many parts of the next sections, about setting
up the debugger database (xxxmdbug) and about generating
the surrogate procedures that will stand in for the original
procedures, I will refer many times to Example_Procedure.
It looks like this. I add line numbers for later reference
and also because some User Commands refer to line numbers.
I made it in the `test` database as user Peter.

Example_Procedure ()
01 BEGIN
02   DECLARE declared_variable INT DEFAULT 5;
03   WHILE @user_variable < 5 DO
04     UPDATE t SET column1 = declared_variable;
05     END WHILE;
06   END

Introduction: Walk Through A Procedure
--------------------------------------

There are two connections.
Debugger Connection takes the user commands.
Debuggee Connection executes the stored procedures.

Debugger Connection and Debuggee Connection use a protocol for
"inter-connection communication" (ICC) which is the subject of
a later section. The effect is that Debuggee Connection will
always freeze when Debuggee Connection reaches a breakpoint,
and Debugger Connection will not tell Debuggee Connection to
continue until after Debuggee Connection reaches a breakpoint.

Debuggee Connection can access in-memory tables which have
descriptions of the current breakpoints and current variable values.
Debugger Connection can get copies of those tables.

Debuggee Connection actually runs altered copies of stored procedures,
which contain extra code to check breakpoints, update variable
values, and handle the ICC. Usually the debugger user is unaware
of the extra code, and other users are unaffected because the
altered copies of stored procedures are kept separate, and invoke
common routines in a separate 'xxxmdbug' database which is seen only
by Debugger Connection and Debuggee Connection. That is, the original
stored procedures are not changed unless the debugger user requires
extra functionality.

Suppose a procedure looks like the earlier-described Example_Procedure.
The debugger finds this text by selecting from mysql.proc.
The debugger finds the tokens and the statement
boundaries with a tokenizer() procedure written in SQL.

The debugger makes a copy of the procedure
which has extra debugger-specific statements in it:
CREATE PROCEDURE test.xxxmdbug000pExample_Procedure ()
BEGIN
  /* Statements to intercept handlers and add to call stack */
  BEGIN
    DECLARE declared_variable INT DEFAULT 5;
    WHILE @user_variable < 5 DO
      /* Statements for inter-connection communication */
      UPDATE t SET column1 = declared_variable;
      END WHILE;
  END;
  /* Statements to remove from call stack */
END

Debugger Connection sends a message commanding "debug Example_Procedure".
Debuggee Connection receives the message, sees that Example_Procedure
has a surrogate routine, and so says
"CALL test.xxxmdbug000pExample_Procedure()".
It quickly reaches the "Statements for inter-connection
communication" which precede the first debuggable statement.
And now it pauses (it hasn't been ordered to "continue"),
looping, waiting for a message to appear from Debugger Connection and
calling a Sleep() function if there are no messages.

Debugger Connection sends a message commanding "continue".
Debuggee Connection receives the message; stops the
waiting and sleeping. Now it executes the statement
"UPDATE t SET column1 = declared_variable;". Over and over.
The WHILE is an infinite loop.

Debugger Connection sends a message commanding "break on
every line". This means every line of the original routine
(user commands always refer to the original routine not
the surrogate routine). Debuggee Connection receives the
message and inserts a row in a BREAKPOINTS table.
Did you notice the asynchronicity? Debuggee Connection can
accept a "break" message even while it's still in its infinite
WHILE loop, because it checks for messages every time it's
about to execute a statement.

Debuggee Connection now goes on to the next statement -- but
the "statements for inter-connection communication" include
a search of the BREAKPOINTS table. Debuggee Connection discovers
that there's a breakpoint for line 4, so once again it
enters a wait-and-sleep loop. Debugger Connection can see that's
happening because Debuggee Connection sends a message about
its statement status, which Debugger Connection can read.

Debugger Connection now looks in the message to get the
information "what statement is being executed?", and
in a table to get the information "what is the value
of @user_variable?". There's occasional need to send a message for
these "get information" requirements, because Debuggee
Connection updates the tables constantly, and Debugger
Connection needs Debuggee Connection's help to update
its own copies of those tables. Anyway, the answers are
"the UPDATE statement on line 4 is being executed" and
"the value of @user_variable is 0". (I happened to set
@user_variable=0 earlier on the debuggee connection.)

Presumably, after the user asks for "continue" a few times
and sees that the Debuggee Connection keeps stopping at the
breakpoint for line 4 every time and the value of @user_variable
is always the same, the user will say "aha, the WHILE is
always looping because its conditional variable, @user_variable,
is never changing". The bug has been found.

Debugger Connection sends a message commanding "set @user_variable = 5".
Debuggee Connection receives the message and changes the value of
@user_variable, and also changes the USER_VARIABLES table so that
Debugger Connection can read it if desired. Now, if the next
command is "continue", the loop will end because the
condition ("WHILE @user_variable < 5") is false at last.

Debugger Connection sends a message commanding "exit".
Debuggee Connection receives the message and signals an error.
Among the statements at the start of the routine,
"/* Statements to intercept handlers and add to call stack */",
there is a DECLARE EXIT HANDLER which intercepts the error
and thus ends the execution of test.xxxmdbugpExample_Procedure.

The user, meanwhile, has seen none of this.
The user has issued commands (or clicked for GUI widgets)
which cause the original procedure to appear on the screen,
made it run, made it stop again when it obviously was looping,
seen that line 4 is highlighted on the display, brought up
a variable display, changed what was causing the loop,
and ended. The details are our problem, not the user's.

Introduction: Terminology Notes
-------------------------------

Oracle prefers the terms "debug session" and "target session"
but I think "Debugger Connection" and "Debuggee Connection"
are better because they're more specific. The word "debuggee"
exists in Wiktionary.

Oracle people seem to be talking about the two-connections method
when they use the term "server-side debugging". Perhaps that's
the better term, but it doesn't distinguish from the main
alternate method, since statement-at-a-time debugging would also
send all statements to be executed on the server side.

The term "surrogate routine" is not used by anybody else.

Oracle uses the term "inter-session communication" in discussion
of DBMS_PIPE / DBMS_ALERT. I've chosen the term "inter-connection
communication" instead, because MySQL tends to use the term
"connection" or "thread" more frequently than "session",

Install
=======

I'm expecting install to be easy. We supply an SQL
script and a manual. The administrator should check
'Prerequisites' and the required 'Privileges' which
are the subject of this section. Then the
administrator runs the script.

Install: Prerequisites
----------------------

The database server must support SIGNAL + RESIGNAL.
Therefore the MariaDB/MySQL version must be 5.5.0 or later.

Install: The xxxmdbug database
------------------------------

It's not a formal naming convention, but database names
that end with "_schema" are used for system objects.
Examples are "information_schema" and "performance_schema".
So I considered using "xxxmdbug_schema" for the database
that contains objects that are necessary for debugging.
However, "xxxmdbug" alone was deemed sufficient.

We'll declare a reserved namespace thus:
"Any name that begins with 'xxxmdbug' may be used
by the debugger. We strongly recommend that users avoid
names that begin with 'xxxmdbug' in other applications."

The xxxmdbug database contains all the fixed
routines which have to be used as common utilities.
It is created by the installation script. The installed
routines are permanent, that is, there is no need to change
them unless an upgrade occurs.
It's probably going to save space if administrators add
xxxmdbug to the list of databases which are not subject
to row-based replication, using --binlog-ignore-db
or --replicate-ignore-db.

At first the plan was to put all the surrogate routines
in the xxxmdbug database or in user-specific copies thereof.
That didn't work, because a routine's "default" database
is the database it's defined in, and the defaults must be
the same for the surrogate as for the original. Therefore
the surrogate routines must be in the same database as the
original. And it must be possible to find the surrogate
name given the original name, or vice versa.

So the format of a surrogate name is always:
CONCAT('xxxmdbug',nnn,p-or-f,original name)
where 'xxxxmdbug' is our reserved-for-debug-use prefix,
where nnn is a 3-letter value between 000 and ZZZ,
where p-or-f is either P for procedure or F for function,
where original name is the name of the original routine.
For example, the first time that one creates a surrogate
for procedure example_procedure, the name will be
xxxmdbug000PExample_Procedure.

The nnn part of a surrogate procedure is the
"setup group identifier". Each time you run the
setup() procedure which creates new surrogates,
you get a new setup group identifier. The setup()
procedure never deletes or modifies existing surrogates.

The flaws in this scheme are:
1. Maybe an original name is long, and adding a prefix
   like 'xxxmdbug000P' (11 characters) will make a name
   longer than MySQL's maximum length of 64 characters.
   I don't think long names are common. But if they are:
   Use shorter original names.
2. Maybe the setup group identifier is already at its
   maximum value which is ZZZ. I don't think that's
   likely because the setup() procedure assigns ascending
   values containing any digit or Latin letter, so the
   number of possibilities is (36*36*36), which is lots.
   But if it happens:
   Destroy all surrogates and start again.

In this document I'll say "xxxmdbug000PExample_Procedure"
to mean "whatever name the setup() procedure has assigned".

Install: Privileges
-------------------

The debugger user must have extra privileges besides
the privileges needed to execute the routines.

The administrator will have to grant at least these:

/* Privileges necessary for running the installation script. */
GRANT CREATE ON xxxmdbug.* TO install_user;
GRANT SUPER ON *.* TO install_user;
GRANT SELECT,INSERT ON mysql.proc TO install_user;

/* Privileges necessary for setup() in order to create
   surrogate routines. */
GRANT CREATE TEMPORARY TABLES ON xxxmdbug.* TO setup_user;
GRANT CREATE ROUTINE ON xxxmdbug.* TO setup_user;
GRANT EXECUTE ON xxxmdbug.setup TO setup_user;

/* Privileges necessary for debuggee, assuming the
   intent is to debug procedure test.Example_Procedure. */
GRANT SELECT ON xxxmdbug.* TO debuggee_user;
GRANT EXECUTE ON test.Example_Procedure TO debuggee_user;
GRANT EXECUTE ON test.xxxmdbug000PExample_Procedure TO debuggee_user;
GRANT EXECUTE ON xxxmdbug.become_debuggee_connection TO debuggee_user;
GRANT EXECUTE ON xxxmdbug.debuggee_wait_loop TO debuggee_user;

/* Privileges necessary for debugger, assuming the
   intent is to debug procedure test.Example_Procedure. */
GRANT SELECT ON xxxmdbug.* TO debugger_user;
GRANT EXECUTE ON test.Example_Procedure TO debugger_user;
GRANT EXECUTE ON test.xxxmdbug000PExample_Procedure TO debugger_user;
GRANT EXECUTE ON xxxmdbug.command TO debugger_user;

/* Privilege useful for looking at PROCESSLIST.
   It's 'nice to have' but not necessary. */
GRANT PROCESS ON *.* TO debugger_user;

/* Resource limit necessary so that the user can
   make more than one connection, if the same user is
   both debugger and debuggee (which is normal). This should be
   at least 2. The default, '0', means 'no limit'. */
GRANT USAGE ON *.* TO debuggee_user / debugger_user;
WITH max_user_connections 0;

Actually I expect that the typical user will simply be root,
but I showed the minimal necessary privileges so that people
won't think that being root is necessary. The privileges for
debugger and debuggee will look innocuous, so I'll have to warn:
the debuggee_wait_loop() and command() routines are executed
with definer privileges. I promise that the "INSERT ON mysql.proc"
privilege will only be used to add surrogate routines, and the
"SUPER ON *.*" privilege will only be used for ICC. However,
careful administrators should verify my promises by looking
at the source code.

Install: Installation script
----------------------------

I considered the name xxxmdbug_common_routines.sql.
The name is patterned after a name in MySQL's ~/share
directory, mysql_system_tables.sql. However, the
name install.sql was deemed sufficient.

Inside install.sql is a series of CREATE statements.
The statements put routine definitions in xxxmdbug.

So, to install the debugger, the following is sufficient:

For MySQL:
mysql> source install.sql

For MariaDB:
MariaDB [(default_schema)]> source install.sql

In all futher cases, where this document shows the MySQL prompt,
it should be read as "either the MySQL prompt or the MariaDB prompt".

And that's the end of the installation process.
The next step is to call setup().

Setup
=====

Setup: arguments
----------------

Setup() is an SQL stored procedure in xxxmdbug.
It is called by an application.
Its job is to call the create* routines.

setup (switches and list of routines)

The switches will rarely be necessary,
and omitting them will cause a reasonable default.
But I'll discuss each switch because that will
reveal some of the design problems.

[ -track_user_variables ]

The possible values are '0', '1, '2'.
It saves space to say '0'.

[ -track_variables ]

The possible values are '0', '1, '2'.
It saves space to say '0'.

[ -track_system_variables ]

The possible values are '0', '1, '2'.
It saves space to say '0'.

[-signal_number]

The possible values are integers between 5000 and 9999.

The default is 5678.

The user can use a different signal_number value
if there is a warning that the default value is
used by SIGNAL statements in the original routines.
See section "Setup: checks and warnings".

I didn't implement this, I decided it's an unlikely problem.

[ -engine]

The possible values are engines listed in information_schema.engines.

The debugger's temporary tables have to be made with a storage engine.
The default could be engine=memory because:
*  I don't want ROLLBACK to affect changes that I make to tables in
   xxxmdbug, and I can't control whether ROLLBACK will happen.
   So transactional engines are inappropriate.
*  It doesn't matter if the data disappears; one can get it back
   by calling "Setup" again.
On the other hand, one might want to say engine=myisam if:
*  Everything in BLOB or TEXT columns is important. Since the
   MEMORY engine can't have BLOB or TEXT, and its storage is fixed-length
   even for VARCHAR, it will convert to easier-to-use data types.
*  The server variable MAX_HEAP_TABLE_SIZE is small and hard to change.

I didn't implement this, I decided it should be up to the designer.

[destroy_existing_objects]

The possible values are 'True' and 'False'.

The default is 'True'.

'True' means "if there's anything in this database already,
get rid of it". There might be some times when it's not
absolutely necessary, though.

I didn't implement this.

[maximum_variable_length]

The possible values are integers between 1 and 1000.

The default is 64.

If any declared-variable or user-variable value is longer than
maximum_variable_length bytes, the procedure will run but
users won't see the complete value in the VARIABLES tables.

I didn't implement this, I decided 64 ought to be plenty.

After the switches comes the list of routines.
This has to be flexible because we want to encourage users
to only call setup() for what's necessary for immediate needs.
Frankly setup() is going to be slow initially, and maybe forever.
So the list is comma-separated and wildcards % or _ are legal.

I suppose that users will assume that the listed routines
must exist, but in fact there won't be any error messages
if they don't exist, because (as the allowance of wildcards
implies) this is really a "search list" more than a "requirement
list". If the routine's there, fine. If it's not, it won't come
out in the setup_log. So users have to look at the setup log.

I assume that users will assume that the default database is
the one that they said USE for, but in fact the default database
is 'xxxmdbug' because that's where the setup routine lies.
Nobody ever wants that, so I decided: if the user doesn't
specify a schema, the arbitrary assumption is: it's `test`.

The routines that actually come out are the 'setup group'.

The setup() procedure looks approximately like this:
CREATE PROCEDURE setup ()
BEGIN
  CALL privilege_checks();
  CALL create_setup_log_table()
  CALL create_connections_table()
  CALL create_routines_table()
  CALL create_tokens_table()
  CALL create_statements_table()
  CALL create_variables_table()

  For (each routine from the list)
    CALL insert_into_routines()
    CALL insert_into_tokens() /* "tokenizer" */
    CALL insert_into_statements()
    CALL insert_into_variables()
    CALL generate()

  CALL view_and_trigger_and_event_check()
  CALL checks_and_warnings(database_name)

After the setup() procedure finishes, there optionally is
a final Setup step which could be run from an
application program, as described in section
"Debuggee Start: Run the create-surrogate-routines script".

Setup: Privilege checks
-----------------------

privilege_checks(database_name)
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to warn, with SIGNAL statements,
about problems that are detectable at the start.

These checks don't guarantee that "Setup" will succeed,
but it's worth warning the user before we go in deeply.

* max_user_connections should not be maxed out.
  Usually we'll be creating a new connection
  ("Debuggee Connection") after Setup.

* The SUPER privilege allows creation of routines with
  the same definer as the original creator. Ordinarily
  that's necessary so that the surrogate procedures will
  run exactly as the original procedures would. Also,
  the SUPER privilege is necessary for Inter-Connection
  Communication.

* The user must have CREATE privileges for the xxxmdbug
  database and all the object types (base tables, views,
  functions, procedures) that will go into it.

* It is best if the user has some privilege, at least
  EXECUTE privilege, for every routine that could possibly
  be accessed during the debug session, directly or indirectly.
  The debuggee will have to call the original routines
  rather than the surrogate routines whenever it can't see
  the metadata, and that could mean that the debuggee is
  unaware of hidden changes to user variables.

* The system variable @@innodb_lock_wait_timeout is 50 by
  default. This might be too small since a debug session
  will often cause a pause for many seconds while waiting
  for user input.

Setup: What's left unsaid
-------------------------

As a general rule, I won't describe
"If (something goes wrong), then (do this)."
Just take it that lots of things can go
wrong, and be ready to emit an error
message and tear everything down again.

If the administrator has been careful
about GRANT settings, it's possible to
make sure that the only person who can
view the database contents is the user
who is debugging.

We are now ready to create tables and procedures.

Setup: Create SETUP_LOG table
-----------------------------

create_setup_log_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to create the SETUP_LOG table.

CREATE TABLE SETUP_LOG (
  User
  Version_number_of_debugger
  Timestamp_when_setup_procedure_was_run
  Arguments_passed_to_setup
  Is_setup_done
  Comment_based_on_readme)

The SETUP_LOG table will have a note about what the
setup() procedure did.

Setup: Create ROUTINES table
----------------------------

create_routines_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to create the ROUTINES table.

CREATE TABLE ROUTINES (
  Routine_schema_of_original
  Routine_name_of_original
  Routine_name_of_surrogate
  Last_altered
  Definition_of_original_routine
  Definition_of_surrogate_routine)

The ROUTINES table will have a row for every original
routine and its surrogate routine.
See "Insert into ROUTINES table".

Setup: Create TOKENS table
--------------------------

create_tokens_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once per routine.
Its job is to create the TOKENS table.

CREATE TEMPORARY TABLE TOKENS (
  Routine_name
  Token_number
  Character_number
  Line_number
  Value)

The TOKENS table is used when "parsing" routine
definitions to find out what tokens are in the routine.
See "Insert into TOKENS table".

Setup: Create STATEMENTS table
------------------------------

create_statements_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once per routine.
Its job is to create the STATEMENTS table.

CREATE TABLE STATEMENTS (
  Statement_number
  Routine_schema
  Routine_name
  Statement_number_within_routine
  Token_number_of_first_token
  Line_number_of_start_of_first_token
  Character_number_of_start_of_first_token
  Token_number_of_last_token
  Line_number_of_end_of_last_token
  Character_number_of_end_of_last_token
  Type)

The STATEMENTS table has a row for every statement
in every routine.
See "Insert into STATEMENTS table".

Setup: Create VARIABLES table
-----------------------------

create_variables_table() is an SQL stored procedure in xxxmdbug.
It is called by setup(), once per routine.
Its job is to create the VARIABLES table.

CREATE TABLE VARIABLES (
  Routine_schema
  Routine_name
  Token_number_of_declare
  Variable_name
  Data_type
  Value
  Is_changed_by_last_statement
  Is_in_scope
  Is_settable
  Is_updated_by_set

The VARIABLES table has one row for every declared
variable or parameter in every routine, and one row
for every user variable.
See "Insert into VARIABLES table".

It became necessary to split into three different tables:
VARIABLES for declared variables only, plus USER_VARIABLES,
plus SYSTEM_VARIABLES.

Setup: a note about locking and concurrency
-------------------------------------------

Conflict should be rare during a debug session.

The Debuggee Connection never writes or reads MDBug tables.
It only "writes" by appending to a user variable, and "reads"
by copying from a user variable. Thus there can be very long
user variables which are in effect pseudo-tables.

Except for SETUP_LOG, all MDBug tables are TEMPORARY tables.
The contents of TEMPORARY tables are local to a session.

Therefore multiple instances of Debugger Connection and
Debuggee Connection can read and write MDBug tables and
pseudo-tables without interfering with each other.

The ICC mechanism depends on changes to a global variable,
@@init_connect. Global variables are protected by a mutex.

To prevent two connections from both trying to do a
"Setup" at the same time, we have a "GET_LOCK('xxxmdbug_lock',1)"
statement at the start of setup(), and we release xxxmdbug_lock
at the end of setup().

To prevent a third connection (a non-debugger connection)
from changing xxxmdbug objects, we depend on the
privilege system. So this is an administrator's problem.

If a third connection alters or drops original routines,
Debuggee Connection will discover that (see section
"Generate: starter"), and end the debugging session.

Neither Debugger Connection nor Debuggee Connection will
ever use an explicit LOCK statement or GET_LOCK function,
while in a debug session.

Setup: Insert into ROUTINES table
---------------------------------

For each row in mysql.proc:
* If the 'db' column value (that is, the schema/database name)
  is 'mysql' or 'information_schema' or 'performance_schema'
  or like 'xxxmdbug%', skip it. (We don't want to make
  surrogate routines for surrogate routines.)
* If the 'db' and 'name' column values don't match what
  was requested by setup argument 'original_routine_name',
  skip it.
* If the 'language' column value is not 'SQL', skip it.
  Right now all routines are SQL, but there's a chance
  that someday somebody (maybe Antony Curtis) will add
  a feature for supporting external-language routines.
* Figure out routine_name_of_surrogate.
  Try to make this with a concatenation of 'xxxmdbug',
  a three-character setup group name (see xxxmdbug.get_setup_group_name() to see how this formed),
  a 'P' for 'Procedure' of an 'F' for 'Function',
  and the original routine name.
  If the concatenation result is too long, that's an error.
  Names that contain information are preferable because
  they might be long-lasting (see appendix
  "Debugging without surrogates").
* INSERT INTO ROUTINES.

SIDEBAR: Why use mysql.proc rather than information_schema.routines?
Generally I prefer to use SQL-standard information_schema tables.
But try this:
CREATE PROCEDURE pd () SELECT * FROM `t``v x`;
SELECT body from mysql.proc where name='pd';
SELECT routine_definition from information_schema.routines where routine_name='pd';
The first SELECT will return:  SELECT * FROM `t``v x`
The second SELECT will return: SELECT * FROM `t`v x`.
It is impossible to tokenize the return from the second SELECT. 
This is Bug#58342 ROUTINES.ROUTINE_DEFINITION removes double quotes
http://bugs.mysql.com/bug.php?id=58342.

Based on the Example_Procedure,
ROUTINES now looks like this:
#Row 1
Routine_schema_of_original            test
Routine_name_of_original              Example_Procedure
Routine_name_of_surrogate             xxxmdbug000PExample_Procedure
Definition_of_original_routine
 BEGIN
   DECLARE declared_variable INT DEFAULT 5;
   WHILE @user_variable < 5 DO
     UPDATE t SET column1 = declared_variable;
     END WHILE;
   END

Setup: Insert into TOKENS table
-------------------------------

insert_into_tokens_table()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once for each routine.
Its job is to populate the TOKENS table.

We're dealing with stored routines that already exist, so we
know that the text can be parsed -- MySQL has already done it.
However, MySQL won't supply the parse tree.
We have to do it ourselves, and this is
an SQL routine, so it's going to be slow.
Luckily, our 'parser' won't have to understand very much.
It just needs to recognize statements that are
debuggable, and recognize references to routine names.

The main job is to break up an SQL-text string
(the routine definition) into its basic components
of names and keywords and operators -- the "tokens".

More description is in section "Common Routines: tokenizer".
It can't handle more than a few hundred tokens per second,
but that should be okay.

Source is:
* routine_definition from ROUTINES table
* plus view_definition from information_schema.views
  plus action_statement from information_schema.triggers
  plus event_definition from information_schema.events
  (We only look at views and triggers and events for the
  sake of an error check, see section
  "Setup: Check Views and triggers and events".)

Each token will be inserted into the TOKENS table.

Based on the Example_Procedure,
TOKENS now looks like this:

+-------------------+---------+------------+--------+-------------------+
| Routine_name      | Token_  | Character_ | Line_  | Value             |
|                   | number  | number     | number |                   |
+-------------------+---------+------------+--------+-------------------+
| Example_procedure |       1 |          1 |      1 | BEGIN             |
| Example_procedure |       2 |          9 |      2 | DECLARE           |
| Example_procedure |       3 |         17 |      2 | declared_variable |
| Example_procedure |       4 |         35 |      2 | INT               |
| Example_procedure |       5 |         39 |      2 | DEFAULT           |
| Example_procedure |       6 |         47 |      2 | 5                 |
| Example_procedure |       7 |         48 |      2 | ;                 |
| Example_procedure |       8 |         52 |      3 | WHILE             |
| Example_procedure |       9 |         58 |      3 | @user_variable    |
| Example_procedure |      10 |         73 |      3 | <                 |
| Example_procedure |      11 |         75 |      3 | 5                 |
| Example_procedure |      12 |         77 |      3 | DO                |
| Example_procedure |      13 |         84 |      4 | UPDATE            |
| Example_procedure |      14 |         91 |      4 | t                 |
| Example_procedure |      15 |         93 |      4 | SET               |
| Example_procedure |      16 |         97 |      4 | column1           |
| Example_procedure |      17 |        105 |      4 | =                 |
| Example_procedure |      18 |        107 |      4 | declared_variable |
| Example_procedure |      19 |        124 |      4 | ;                 |
| Example_procedure |      20 |        130 |      5 | END               |
| Example_procedure |      21 |        134 |      5 | WHILE             |
| Example_procedure |      22 |        139 |      5 | ;                 |
| Example_procedure |      23 |        143 |      6 | END               |
+-------------------+---------+------------+--------+-------------------+

Setup: Insert into STATEMENTS table
-----------------------------------

insert_into_statements()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once for each routine.
Its job is to populate the STATEMENTS table.

Once you know where the tokens are, it's easy to find
where the statements start and end.

The end of a statement is:
  end of string, or
  ;

The start of a statement is:
  start of string, or
  anything that comes after ;

There are just a few sequences of tokens which we must
treat specially, if they're at the start of a statement:
BEGIN
REPEAT
LOOP
WHILE as far as DO
IF as far as THEN
ELSE
ELSEIF as far as THEN
UNTIL as far as ;
DECLARE {CONTINUE|EXIT} HANDLER as far as last condition_value
label as far as :
/* comment */
We treat the special sequences as "statements".
Although none of them are executable, we want to
separate them from the debuggable statements.

So go through the TOKENS table, and whenever you
encounter a new statement, INSERT INTO STATEMENTS.

Finding statement start/end is also possible with a parser.
But do not try to find statement start/end by checking
whether a token is a keyword that starts a statement, such
as SELECT or INSERT. That fails because some keywords
are not reserved words (for example GET and START and BACKUP),
and some keywords may or may not be statement starters
(for example CASE and CHECK).

Each token will be inserted into the STATEMENTS table.

The possible values of the Type column are:
"Declare"           DECLARE variable|handler|condition
"Flow Control"      BEGIN + END, WHILE + END WHILE,
                    LOOP + END LOOP, REPEAT + UNTIL
"Label"             label and : following label
"Debuggable"        everything else
The generate procedures check for Type="Debuggable" to
determine whether to generate ICC routines before a statement.

The Debugger Connection could use
  Token_number_of_first_token
  Line_number_of_start_of_first_token
  Character_number_of_start_of_first_token
  Token_number_of_last_token
  Line_number_of_end_of_last_token
  Character_number_of_end_of_last_token
  Type
to determine "can I set a breakpoint on this
line?" and what statement is the cursor on?".
Our implementation doesn't bother with that, though.

Based on the Example_Procedure,
STATEMENTS now looks like this:

Row #1 /* BEGIN */
  Statement_number                           1
  Routine_schema                             test
  Routine_name                               Example_Procedure
  Statement_number_within_routine            1
  Token_number_of_first_token                1
  Line_number_of_start_of_first_token        1
  Character_number_of_start_of_first_token   1
  Token_number_of_last_token                 1
  Line_number_of_end_of_last_token           1
  Character_number_of_end_of_last_token      5
  Type                                       Flow Control
Row #2 /* DECLARE declared_variable INT DEFAULT 5; */
  ...
Row #3 /* WHILE @user_variable < 5 DO */
  ...
Row #4 /* UPDATE t SET column1 = declared_variable ; */
  ...
Row #5 /* END WHILE; */
  ...
Row #6 /* END */
  ...

Setup: Insert into VARIABLES table
----------------------------------

insert_into_variables_table()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once for each routine.
Its job is to populate the VARIABLES table.

There are separate tables for declared variables, user variables,
and parameters.

The syntax for declaring parameters is
[ IN | OUT | INOUT ] param_name type
http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html
and the copy of the parameter list is in mysql.proc.param_list.

The syntax for declaring variables inside compound statements is
DECLARE var_name [, var_name] ... type [DEFAULT value];
http://dev.mysql.com/doc/refman/5.6/en/declare-local-variable.html

Don't worry about "[DEFAULT value]". There's no need to
try to "execute" a DECLARE statement. Just look for
each var_name, then look for type, then insert into Variables:
var_name, type, token-number. Value will be NULL initially.

You need to put user-defined variables in the USER_VARIABLES table.
We'll have an is_user_variable() function, which returns TRUE
for names that begin with '@', but not with '@@'
(it's feasible to include all the server variables that begin
with '@@' but there's another table for that.)
So look in the TOKENS table for distinct token names
that start with '@' and add them to the USER_VARIABLES table.
The USER_VARIABLES table will have a row for every user variable
that is referenced in a stored procedure or function,
or is referenced in a view/trigger/event that the user
has privileges for. It is possible that the user defined
other user variables, but we won't be aware of them.
See also
Bug#20307 Request SHOW command support for User-Defined Variables
http://bugs.mysql.com/bug.php?id=20307

Token_number_of_declare is NULL for a user-defined variable,
zero for a parameter, and a token_number for a DECLARED variable.
We need to know it for scope checking (see section
"Common Routines: determine what variables are in scope").

A note about the value column in the VARIABLES table.
In the routine, different variables can have different data types.
In the VARIABLES table, the value column has only one data type: VARBINARY.
This means that only one type of comparison is possible: exact equality.
For example, 'A' is never equal to 'a', and 04.0 is never equal to 4.
That affects breakpoint conditions (see "User Command: 'break' condition")
and it affects searches of the VARIABLES table with Debugger Connection.
We could improve matters by having several different
value columns in the VARIABLES table (value_if_string,
value_if_approximate_numeric, value_if_exact_numeric, etc.).
We could improve matters by casting to the original data type
before any comparison.
But I'm currently hoping that we won't need such improvements.
Let's see whether users complain that only exact comparisons are possible.

Based on the Example_Procedure,
VARIABLES now looks like this:
 Routine_          Token_   Variable_         Data_
 name              number_  name              type
                   of_
                   declare
 --------          ------- ----------         -----
 Example_Procedure       2 declared_variable  INT

USER_VARIABLES now looks like this:
 Routine_          Token_   Variable_         Data_
 name              number_  name              type
                   of_
                   declare
 --------          ------- ----------         -----
 Example_Procedure    NULL @user_variable     LONGTEXT

Setup: Check Views and triggers and events
------------------------------------------

views_and_trigger_and_event_check()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.

Ordinarily we will not debug any routines that
are invoked via views and triggers and events. 
For example, suppose a user creates thus:
CREATE TABLE t (s1 INT);
CREATE TRIGGER t AFTER UPDATE ON t FOR EACH ROW CALL p(@x);
We don't make "surrogate triggers" that would change
the "CALL p(@x)" to "CALL surrogate-routine-for-p(@x)".
The user might choose to do so (see appendix
"Debugging without surrogates").
But the sensible assumption during setup() is that
"CALL p(@x)" calls the original routine.

For each view in information_schema.views
  call insert_into_tokens(view_definition);

For each trigger in information_schema.triggers
  call insert_into_tokens(action_statement);

For each event in information_schema.events
  call insert_into_tokens(event_definition);

For each row in TOKENS
  if check("is it a user variable") = true
  or check("is it a routine name") = true
    put a warning in the DIAGNOSTICS table.
(For explanation of how to tell if a routine is
a routine, see section "Generate: statement text".)

Update: view_and_trigger_and_event_check() was never done.
It's just a stub.

Setup: checks and warnings
--------------------------

checks_and_warnings()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to pass warnings to the caller, with SIGNAL and SELECT.

At the end of the Setup, check whether at any
point during the Setup there was a warning.
This would indicate that there's a known problem,
perhaps a minor one, with the original routines.

Possible warnings are:

SIGNAL_NUMBER. See "Common Routines: Change statement text:
special handling of SIGNAL/RESIGNAL".

UNDEBUGGED FUNCTION. See
"Check views and triggers and events"

Update: checks_and_warnings() was never done.
It's just a stub.

Setup: Insert into SETUP_LOG table
----------------------------------

insert_into_setup_log()
is an SQL stored procedure in xxxmdbug.
It is called by setup(), once.
Its job is to fill in SETUP_LOG.

The Debuggee Start procedures check whether SETUP_LOG
is complete. If it isn't, Debuggee Start won't start.

Generate
========

In the "Generate" phase, we create the CREATE statements
for the surrogate routines.

For each routine /* Coming from ROUTINES table */
  Generate: "create [definer clause] procedure|function"
  Generate: routine name
  Generate: starter
  For each statement in the routine /* Coming from STATEMENTS table */
    Generate: label
    If (statement is debuggable)
      Generate: IF (statement is to be debugged) THEN
                  call icc_copy_variable_to_table_row
                  call icc_core
                  call icc_copy_table_row_to_variable
                  if (exit) signal
                  if (leave) leave
                  if (skip) leave
                  insert into statements_executed
                  END IF
    Generate: handlers
    Generate: statement text
  Generate: leave
  Generate: ender

At the end of the generation, each row in ROUTINES has
a CREATE PROCEDURE or CREATE FUNCTION statement in
its Definition_of_surrogate_routine column.

Generate: "create [definer clause] procedure|function"
------------------------------------------------------

We're looking at a row in xxxmdbug.ROUTINES.
We're going to generate a complete CREATE statement for
the surrogate routine. The first bit is just
"CREATE [definer clause] { PROCEDURE | FUNCTION }".

Sometimes "CREATE ... routine_name" needs a DEFINER
clause if the creator isn't the same, for example
DEFINER=`Peter`@`localhost`. For example purposes
I'll assume that it's necessary, but that changing
sql_mode is not necessary.

We can tell from the original routine definition whether
we have to say CREATE PROCEDURE or CREATE FUNCTION.

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE

Generate: routine name
----------------------

generate_routine_name().

We're still looking at a row in xxxmdbug.ROUTINES.
We know the database name, it's the same as the original routine's.
We know the surrogate routine name, it's Routine_name_of_surrogate.
Generate:
database_name . surrogate routine_name (parameters)
or
database_name .surrogate routine_name (parameters) RETURNS data_type
Where
debug_schema = database name of original routine
surrogate routine_name = value from row in ROUTINES table

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()

Generate: starter
-----------------

Our surrogate routine will always start with code for
* "Commenting"
* "Handling the signal_number"
* "Checking if the routine is still valid"
* "Adding to the call stack"

For "Commenting":
Somebody might come across this procedure and
wonder what it's for or how it came to be.
We could additionally use a COMMENT clause.
Avoid inline comments because clients can lose them.
Generate a comment that explains reasonably fully:
BEGIN
   DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT
   'Surrogate routine for [original routine name]
    Generated by [debugger name] [version]
    Generated on [date]';

For "Handling the signal_number":
The signal number is what we'll signal for errors that
the debugger can't handle, and for the "exit" user command.
The user can pass the signal_number value during "Setup",
but we'll assume throughout that it's the default, 5678.
Generate:
debug label:
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;

For "Checking if the routine is still valid":
If a routine is dropped or altered after the 'Setup' is done,
then the surrogate routines are invalid, and 'Setup' will
have to be done again. We check for this at the start of every
routine, and if it's so, we'll have to exit from the debugger.
Generate:
  IF (SELECT COUNT(*) FROM information_schema.routines
      WHERE last_altered = [last_altered time from xxxmdbug.routines])
     <> 1 THEN
       SIGNAL SQLSTATE '05678' mysql_errno=5678;

Adding to the call stack.

There is a pseudo-table CALL_STACK, which contains a number -- 1
is top of stack, 2 is something that's called from 1, and so on.
Debuggee Connection will insert a row in CALL_STACK when it
enters a routine, and will delete the row from CALL_STACK
when it leaves a routine.
Apparently some people think call stacks are good things, 
see Bug#36106 Stored Procedure call stack in INFORMATION_SCHEMA.

Generate:
  INSERT INTO CALL_STACK VALUES ([original routine name]);

The starter could also generate "Update CALL_STACK" to
make it contain the line number of the statement
that caused invocation of this routine. This can be found,
it's the last row of the STATEMENTS_EXECUTED table.
But that would only be a "nice to know", not a "need to know".

Most of the checks described in this section are in a common routine, xxxmdbug.routine_entry().

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;

Generate: label
---------------

The sections between "Generate: label" and
"Generate: leave" are to be done for each statement
in the STATEMENTS table.

If the statement is WHILE or LOOP or BEGIN or REPEAT,
and the previous "statement" was not "label:", add a
label now.

The name of the label is "xxxmdbug_label_n",
where n is a statement number, unique within the routine.
This will make it easy to
exit loops and compound statements by saying
"LEAVE xxxmdbug_label_n" later.

There will be more labels generated throughout the routine.

Generate: if (statement is debuggable)
--------------------------------------

We're looking at the token that starts a statement.
We want to determine whether we should generate
ICC statements, that is, whether the statement is
debuggable.

We check by looking whether STATEMENT.Type='Debuggable'.

For every statement which is not debuggable,
skip to section "Generate: statement text".
In the 'Example_Procedure' examples I'll assume
that's happened for the BEGIN and WHILE statements.

For the statements which are debuggable (UPDATE
in our example) all following sections as far as
"Generate: statement text" apply.

Generate: "while (statement is being debugged)"
--------------------------------------------

Ordinarily, the surrogate routines in xxxmdbug
are invoked by Debuggee Connection -- that's what we intended
during "Setup", that's what the surrogate routine is for.

Less ordinarily, the surrogate routines in xxxmdbug
are invoked by some connection that has nothing to do
with the current debug session. The usual reason is
that users have inserted calls to surrogate routines
in the original database, see appendix "Debugging without surrogates".
That's okay. We just want to make sure that, when it
happens, the interconnection communication calls are skipped.

How can we tell? That's what the 'attach' command is for.
If the Debugger Connection has sent not yet sent 'attach', then 
skipping occurs. The attach can happen before debugging (indeed
that's what always happens with debuggee_wait_loop), or it can
happen later.

Generate:
  label:
  IF (statement is being debugged) THEN

This is handled by the common routine xxxmdbug.is_debuggee_and_is_attached().

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO             
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      

Generate: "call icc_copy_variable_to_table_row"
-----------------------------------------------

See section "Common Routines: determine what variables are in scope"
to find out which variables the routine will need at the time
it reaches the statement that we're currently looking at.

Generate, for each variable that's in scope:
  CALL icc_copy_variable_to_table_row(variable_name,token_number_of_declare,variable_value)
This will update the VARIABLES table, effectively
UPDATE VARIABLES SET variable_value=passed-variable-value WHERE variable-name=passed-variable-name.
And save the previous value, so it will be clear it changed since last time.
See section "Common Routines: icc_copy_variable_to_table_row".
Regrettably, there's no way to generate an UPDATE dynamically
if there's a variable reference.

The purpose of the CALL is to get variables' values
into a pseudo-table so that Debuggee Connection can
pass them to Debugger Connection in response to a 'refresh' command.

This step could be avoided if we could be certain
that the last statement didn't change the variable.
Since very few statements change variables (perhaps
only FETCH and SELECT and SET and DECLARE?), we could
probably reduce drastically the number of calls to
icc_copy_variable_to_table_row(). But we don't.
(By the way, simply asking "what was the last
generated statement?" wouldn't be good enough
here, because the last executed statement might
be in a CONTINUE handler, or might have invoked
a function.)

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO            
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);

Generate: "call icc_core"
-------------------------

Generate:
CALL icc_core(line_number);

This routine is the "Inter-Connection Communication" Core procedure.
See section "Common Routines: icc_core".

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO             
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);

Generate: "if (exit) signal"
----------------------------

If execution reaches this point, that means the breakpoint check
has been passed (it was in icc_core), and we are continuing.
But the return from icc_core includes the value of the last
command from Debugger Connection. It's in @xxxmdbug_command.
If it's 'exit', then execution should stop.
We cause that by signaling 5678, we never reach the generated statement.

(This check could be in icc_core, but I put it here
because I think it makes it more obvious what 'exit' does.)

Generate:
IF @xxxmdbug_command = 'exit' THEN
  SIGNAL sqlstate '56780' SET mysql_errno = 5678;
END IF;

Generate: "call icc_copy_table_row_to_variable"
-----------------------------------------------

Generate, for every variable that's in scope,
(see "Common Routines: determine what variables are in scope"),
  CALL icc_copy_variable_to_table_row(variable_name,token_number_of_declare,variable_value)
This will select from the VARIABLES table, effectively
SET variable = value-in-variables-table;

The result will be that all variables get values from VARIABLES table.
See section "Common Routines: icc_copy_table_row_to_variable".

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO             
      WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               

Generate: "if (leave) leave"
----------------------------

If the user command was "leave", then we want to jump out of
the current (originally unlabelled) REPEAT or WHILE or BEGIN or LOOP.
So generate:
  IF @xxxmdbug_command='leave' THEN
    LEAVE debug_label_n;
    END IF;

Generate: "if (execute) iterate"
--------------------------------

Suppose the user command was to execute an SQL statement, like
'execute insert into t values (7)';
In that case, do we really want to continue right away?
I think not, I think the user would want to stay at the breakpoint.
We also have to iterate after 'set'.
So generate:
  IF @xxxmdbug_command='execute' OR @xxxmdbug_command='set' THEN
    ITERATE debug_label_of__while;
    END IF;

Generate: "if (skip) leave"
---------------------------

If the user command was "skip", then we want to get out of the
WHILE loop so that the original SQL statement won't be executed.
So generate:
  IF @xxxmdbug_command='skip' THEN
    LEAVE debug_label_of_while;
    END IF;

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               
            IF @xxxmdbug_token_value_1 = 'leave' THEN LEAVE xxxmdbug_label_3; END IF;                                                                     
            IF @xxxmdbug_breakpoint_check_result=1 AND (@xxxmdbug_token_value_1 = 'set' OR @xxxmdbug_token_value_1 = 'execute') THEN ITERATE xxxmdbug_inner_loop_label_4; END IF;                                                
            IF @xxxmdbug_token_value_1 = 'skip' THEN LEAVE xxxmdbug_inner_loop_label_4; END IF;                                                           

Generate: handlers
------------------

This is where we should generate, at least,
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND CALL diagnostics_handler();
It's a bit too general: if there's a handler for something
more specific, then this handler might be missed. I believe
the solution for that will be to insert "CALL diagnostics_handler()"
in several places.

This is a handler for the original statement,
which we will soon be generating.
If the statement ends with an error, then
diagnostics_handler() will put the error number
in a DIAGNOSTICS table.

Update: It didn't work. I even got crashes. I reported a MySQL bug (Bug#65166).

Generate: statement text
------------------------

Generate the statement itself.

The Common Routine change_statement_text()
will change the statement text, if necessary, so that

First,
CALL change_statement_text(statement text,
                           changed_statement_text_1,
                           changed_statement_text_2);
This will change routine references to surrogate-routine
references (for example "call p()" will become
"call xxxmdbug00Pp()"), and do special handling
for SELECT, SHOW, RETURN, and SIGNAL.

In our example procedure the statement is
"UPDATE t SET column1 = declared_variable;"
which has no routine references and requires no
special handling. So change_statement_text()
returns changed_statement_text_1 = the original
statement text unchanged, changed_statement_text_2 = ''.
And that's what we generate now.

Generate: "insert into statements_executed"
-------------------------------------------

If execution reaches this point, it's certain that
Debuggee Connection will actually try to do the
original statement. So put the statement number
in our "tracking" table, STATEMENTS_EXECUTED.

We won't try to do much with statistics.
Timings would be distorted anyway, because some
statements (SELECT, SHOW) get special handling.
Timings would in any case have to be done *after*
the statement, and we avoid that.

But a debugger certainly wants to know "how many times
was the DROP statement in test.procedure5 executed",
"what were the most recent executed statements", etc.

Generate:
  INSERT INTO STATEMENTS_EXECUTED VALUES (statement-number);

Generate: leave
---------------

Generate END to end the BEGIN that we generated earlier
(see section "Generate: handlers"), then generate LEAVE and END WHILE
to exit and end the WHILE that we generated earlier (see
section "Generate: "while (statement is being debugged)").

Generate:
  END
  LEAVE debug_label_of_while;
  END WHILE;

Based on the Example_Procedure,
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               
            IF @xxxmdbug_token_value_1 = 'leave' THEN LEAVE xxxmdbug_label_3; END IF;                                                                     
            IF @xxxmdbug_breakpoint_check_result=1 AND (@xxxmdbug_token_value_1 = 'set' OR @xxxmdbug_token_value_1 = 'execute') THEN ITERATE xxxmdbug_inner_loop_label_4; END IF;                                                
            IF @xxxmdbug_token_value_1 = 'skip' THEN LEAVE xxxmdbug_inner_loop_label_4; END IF;
            UPDATE t SET column1 = declared_variable;
            CALL xxxmdbug.update_statements_executed(0);
            LEAVE xxxmdbug_inner_loop_label_4;          
            END LOOP;
          ELSE
            UPDATE t SET column1 = declared_variable;
            END IF;
          END WHILE;
        END;

Generate: ender
---------------

When we reach the end of a routine, we have to
* Remove the latest item from the call stack by generating:
  DELETE FROM CALL_STACK WHERE call_stack_number = (SELECT MAX(call_stack_number) FROM CALL_STACK);
  This is required for the end of any procedure, and
  for some EXIT handlers, and for function RETURN statements.
  I am only showing it for a normal stored-procedure exit.
* Generate "END;" because we added "BEGIN" during starter.

Based on the Example_Procedure,
and taking into account that it ended with 'END WHILE; END',
we now have

CREATE DEFINER=`Peter`@`localhost` PROCEDURE
test.xxxmdbug000PExample_Procedure ()
BEGIN 
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT 
  'Surrogate routine for `test`.`Example_Procedure`
  Generated by THE MDBUG CORE DEBUGGER Version 0.3 
  Generated on 2012-04-29 11:30:32';               
  DECLARE EXIT HANDLER FOR 5678 RESIGNAL;          
  CALL xxxmdbug.routine_entry('`test`','`Example_Procedure`','PROCEDURE','0.3','2012-04-29 11:30:32','2*`declared_variable`*INT;');             
  IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN CALL xxxmdbug.xxxmdbug000Picc_core(0);END IF;                                                
  xxxmdbug_label_1:                                                     
  BEGIN                                                                  
    DECLARE declared_variable INT DEFAULT 5;
    xxxmdbug_label_3: WHILE @user_variable < 5 DO                                            
        IF xxxmdbug.is_debuggee_and_is_attached()=1 THEN                       
          xxxmdbug_inner_loop_label_4: LOOP                                      
            CALL xxxmdbug.icc_start(4,0);
            CALL xxxmdbug.icc_copy_variable_to_table_row('`declared_variable`',2,`declared_variable`);
            CALL xxxmdbug.xxxmdbug000Picc_core(4);
            IF @xxxmdbug_token_value_1 = 'exit' THEN SIGNAL sqlstate '56780' SET mysql_errno = @xxxmdbug_signal_errno; END IF;                            
            CALL xxxmdbug.icc_copy_table_row_to_variable('`declared_variable`',2,`declared_variable`);                                                    
            CALL xxxmdbug.icc_end();                                               
            IF @xxxmdbug_token_value_1 = 'leave' THEN LEAVE xxxmdbug_label_3; END IF;                                                                     
            IF @xxxmdbug_breakpoint_check_result=1 AND (@xxxmdbug_token_value_1 = 'set' OR @xxxmdbug_token_value_1 = 'execute') THEN ITERATE xxxmdbug_inner_loop_label_4; END IF;                                                
            IF @xxxmdbug_token_value_1 = 'skip' THEN LEAVE xxxmdbug_inner_loop_label_4; END IF;
            UPDATE t SET column1 = declared_variable;
            CALL xxxmdbug.update_statements_executed(0);
            LEAVE xxxmdbug_inner_loop_label_4;          
            END LOOP;
          ELSE
            UPDATE t SET column1 = declared_variable;
            END IF;
          END WHILE;
        END;
      CALL xxxmdbug.routine_exit();
    END

And we're done with the generator calls.
From the 6-line original procedure, we
have generated a 36-line surrogate procedure.
But we hide this from the users as well as
we can, so the size isn't really important.
What's important is that the surrogate procedure
has all we need for keeping track of variables,
calling ICC, handling errors, handling user
commands, and tracing.

The create-surrogate-routines script
------------------------------------

The generated routines have to go somewhere.

For simplicity, the default is to INSERT their texts in mysql.proc.
I won't UPDATE or DELETE anything in mysql.proc because I have no
guarantees about the effects on the stored procedure cache, but it
seems safe to do INSERTs. I have seen an error message (on
Windows) saying that mysql.proc has to be repaired, but REPAIR
TABLE mysql.proc fixed it, there was no actual damage.

There is another way to do it. The setup() procedure leaves the
CREATE PROCEDURE|FUNCTION texts in the table xxxmdbug.ROUTINES.
So one could use "SELECT INTO OUTFILE script-file" to dump the
CREATE statements into a script file, and then use "SOURCE script-file"
to execute them. Unfortunately I couldn't devise a simple and
automatic way to do this, it can't be done within generate() because
you can't create a stored procedure from within a stored procedure. 

But I encourage anyone doing a front end:
consider using a script file. For example,
from mysql client command line:
  source install.sql;
  UPDATE xxxmdbug.README SET Is_install_script_done = 'True';
  CALL xxxmdbug.setup('test.Example_Procedure');
  SELECT definition_of_surrogate_routine
  INTO create-surrogate-routines.sql
  FROM xxxmdbug.routines;
  source create-surrogate-routines.sql;
  UPDATE xxxmdbug.Setup_log SET Is_setup_done = 'True';


Debuggee Start
==============

After "Setup" (which includes "Generate"), all the
necessary-for-debugging tables and common routines exist
in the xxxmdbug database and in the original routine's database.
So the debug session can start. There's a protocol for that.

The connection that wishes to become Debuggee Connection says:
CALL xxxmdbug.become_debuggee_connection('channel');
which will perform some error checks to make sure that there
is hope of making a debugger-debuggee session, and then
clears a bunch of user variables, whose names always
start with @xxxmdbug.

This initialization is necessary for debuggee_wait_loop().
But it's separate from debuggee_wait_loop() because the
initialization is also necessary for debugging surrogate
routines directly. The rule: if a debugger wants to send
an 'attach' command later, then become_debuggee_connection()
has to have happened at some time in the past on the debuggee.

Debuggee Start: debuggee_wait_loop
----------------------------------

debuggee_wait_loop() is an SQL stored procedure in xxxmdbug.
Its job is to wait for the Debugger Connection to
start the debug session on its side (by sending an
"attach" user command), wait some more till the Debugger
Connection sends a 'debug xxx' command, and then invoke xxx.

To get a message from Debugger Session,
CALL xxxmdbug.icc_get_user_command();
CALL icc_process_user_command();
See section "Common Routines: icc_get_user_command"
and section "Common Routines: icc_process_user_command".

To see how the Debugger Session sends the command,
see section "Common Routines: command".

To see what the Debuggee Session will do when it
receives the "attach ..." user command,
see section "Command: 'attach'".


User Commands
=============

A User Command is a text instruction which Debugger
Connection passes to a Common Routine, xxxmdbug.command(text);

Where it's possible and appropriate, the command syntax is like GDB's.
http://sourceware.org/gdb/current/onlinedocs/gdb/

For example, if a user using a GUI right-clicks
over the second line of text on an edit window,
that might mean "set a breakpoint on line 2", causing
CALL xxxmdbug.command('break routine_name:2');

Sometimes the user commands are not much more
than wrappers for SELECT statements and for
other user commands. At other times they are
quite complicated and involve sending messages
to Debuggee Connection.

In the discussion of each command, I don't bother
saying "somehow Debugger Connection parses the
text of the command". Recall that both Debugger
Connection and Debuggee Connection can
CALL xxxmdbug.tokenizer(text);
to break up any string into its tokens.
After that it should be a straightforward matter
to loop through the TOKENS table.

User Command: 'info'
--------------------

CALL xxxmdbug.command('channel','info info-variant');

The variants of 'info' will be used for
situations where the result is a result
set, and there's no need to send the
command to Debuggee Connection. Debugger
Connection can figure out the answer itself.

These are the possible values of info-variant
and the actions that they cause:

"readme":
select * from information_schema_common.README;
The Readme table exists so that people
can see (from the metadata or by selecting
the function) what the debugger's version
number is, and any comments about the most
recent release, and a link to documentation.
See "Common Routines: README table".
Update: this wasn't implemented.

"processes"
Select from information_schema.processlist.
This is handy if you're looking for a connection
that is waiting for a message from Debugger Connection,
because processlist.info will show the statements
that icc_get_user_command() executes.
This requires the PROCESS privilege.
Update: this wasn't implemented.

"information status"
Debuggee Connection sends a status message
when it starts, when it reaches a debuggable
statement, when it reaches a breakpoint.
See section "ICC: Protocol".

In our running example with Example_Procedure,
the most recent thing that happened was that
the Debuggee Connection started and debuggee_wait_loop() happened. So
CALL xxxmdbug.command('xxx','information status');
will return a row that contains

Last_command  Status
------------  ------
(null)        Waiting for 'attach'

That's another way of saying "You can attach on channel xxx,
there's a Debuggee Connection waiting".

User Command: 'attach'
----------------------

CALL xxxmdbug.command('xxx','attach');

A connection wants to become Debugger Connection.
Check to make sure that Debuggee Connection has already
been set up (see "Start Debuggee").

The command() routine will just send 'attach'.

In our running example, Debugger Connection just found out
with the 'info' command that the debuggee is ready, so it says
CALL xxxmdbug.command('xxx','attach');
Once this is done, other user commands become legal.

The 'attach' command should perhaps fail if the Debugger Connection
and the Debuggee Connection are different users. But there
is no check for that.

User Command: 'debug'
---------------------

CALL xxxmdbug.command('debug [schema_name.]routine_name ([arguments])');

Debugger Connection checks that routine_name exists,
checks that 'attach' is done,
checks that debugging is not already going on.

If the checks succeed, it sends the command to Debuggee Connection:
CALL xxxmdbug.send('debug [schema_name.]routine_name ([arguments])');

The 'debug' will be seen by Debuggee Connection, which will
actually invoke the routine, by finding the routine's
surrogate name in the ROUTINES table, and saying
CALL surrogate_routine_name();       /* if it's a procedure */
or
DO surrogate_routine_name();         /* if it's a function */

The routine will actually start, but there is an effective automatic
temporary breakpoint on what we'll call "line 0", so a pause happens
right after the routine's initial BEGIN and DECLARE statements, if any.
We'll worry about breakpoints in a slightly later section.

In our running example with Example_Procedure, the command is
CALL xxxmdbug.command('debug Example_Procedure()');

From now on, Debuggee Connection is running the procedure and
Debugger Connection is still running the client application
program, accepting user input.

User Command: 'refresh'
-----------------------

CALL xxxmdbug.command('refresh table_name');

The table_name can be the name of a table
in xxxmdbug, such as VARIABLES, USER_VARIABLES, SYSTEM_VARIABLES,
BREAKPOINTS, etc. Values for these tables are
updated by Debuggee Connection. Debugger Connection sends
'refresh' to Debuggee Connection, waits for a response, and
fills up the table.

The waiting can take a while because the debuggee may
be busy or may even have stopped. There's a timeout, but
it's deliberately very long.

For a user, 'refresh' is useful because most of the
xxxmdbug tables have information that can
grow stale. For internal purposes, 'refresh' is
useful because there are other user commands that
might need up-to-date information.

Debugger Connection says:
CALL xxxmdbug.send('xxx','refresh table_name');

The response to 'refresh' might be slow, and if so
there are various ways to speed it up. For example,
Debugger Connection could update BREAKPOINTS
itself whenever it sees a 'break' command.

User Command: 'continue'
------------------------

CALL xxxmdbug.command('channel','continue');

The 'continue' command is legal only if the Debuggee Connection
is waiting. If it's legal, Debugger Connection merely sends it:
CALL xxxmdbug.send('xxx','continue');

When Debuggee Connection receives 'continue', it
continues. That is, it goes on executing in the
surrogate routine. Then the next statement will be
executed, and the next, until the next breakpoint.

There is no problem detecting whether Debuggee Connection
has hit a breakpoint: there will be a statement status
message. But beware. It's possible for Debugger Connection
to say 'continue' and then say 'continue' again before
Debuggee Connection has received and acknowledged the
first 'continue'. To prevent that, Debugger Connection
should always check the message's Message_number, to ensure
that it isn't reading the same message twice.

In our running example with Example_Procedure, the command is
CALL xxxmdbug.command('xxx','continue');

And Debuggee Connection halts on the first statement,
because there's a breakpoint.

User Command: 'break' or 'tbreak'
---------------------------------

CALL xxxmdbug.command('break ...')
or
CALL xxxmdbug.command(channel,'tbreak ...')

The job is to set a breakpoint, by putting a new row in the
BREAKPOINTS table.

"Tbreak" has the same effect as "break", except that the breakpoint
is temporary. Temporary breakpoints are deleted as soon as any
breakpoint is reached.

First check that the syntax is
"break|tbreak [schema_name.]routine_name line_number_minimum[-line_number_maximum] [variable=value]".
For example 'break database1.p1:5-5', or 'tbreak test.f 5-7'.
The schema_name and routine_name may be wildcards,
for example 'database1.%' means all routines in database1.
The routine_name is the name of the original routine, not the surrogate routine.

Then pass the string on to Debuggee Connection without change:
CALL xxxmdbug.send('xxx','break|tbreak location');

Debuggee Connection will get the message and call
process_user_command_break(), which performs an
INSERT INTO BREAKPOINT statement (in effect). See
"Common Routines: process_user_command_break".

In our running example,
CALL xxxmdbug.command('xxx','tbreak test.Example_Procedure:2-2')
will cause
CALL xxxmdbug.send('xxx','tbreak test.Example_Procedure:2-2')
Debuggee Connection will receive and process the message
(see "Common Routines: get_user_command" and
"Common Routines: process_user_command_break").
The end result will be that there is a row in
the BREAKPOINTS table:

Breakpoint_  Routine_           Line_   Line_
identifier   name               number_ number_
                                minimum maximum
------------ --------           ------- -------
1            Example_procedure  2       2

And later, if we "continue", Debuggee Connection will stop
when it sees that it's at such a row, see section
"Common Routines: icc_breakpoint_check".

An FAQ about the "break" command:
* Q: Is it possible to break on a label?
  A: We could find the label during the generate(),
     but typically a label is in front of a flow-control
     statement, which is not debuggable. So I think a
     break on a label wouldn't have much value.
* Q: Is "break p:9" an error if p has only 8 lines?
  A: It's not an error. The breakpoint will never be
     reached. But we allow ranges of line numbers
     like 1-99999, so we should allow any number in
     the range.
* Q: Is "break p:9" an error if it's entered twice?
  A: It's not an error. There will be two rows in
     the BREAKPOINTS table, for the same place.
* Q: Is "break p:9" an error if p doesn't exist?
  A: It's not an error. We anticipate that users
     might make reusable scripts of "break" statements.
     Sometimes those scripts will be used after a
     routine is dropped, or "setup" will be done for
     a different database. It's harmless.
* Q: What if a statement straddles a line?
  A: In reality the breakpoint has to be per statement
     not per line, because the breakpoint_check() will
     happen just before each statement. So what matters
     is "where the first executable word of the statement
     is". And if there are 10 statements on line 5, then
     "break p:5" will in reality set 10 breakpoints.
* Q: Why is the line number mandatory?
  A: Admittedly it would be pleasant to say that "break p"
     means "break on the first debuggable line of p".
     But what if that line is in an IF statement, and the
     IF condition is false? Or what if the procedure
     body is just "BEGIN END", with no debuggable lines?
     It's a flaw caused by the decision that flow-control
     statements don't have breakpoints.
* Q: How will I know that a breakpoint happened?
  A: Receive a message. You'll get a statement status
     message from Debuggee Connection.
     If additionally you want to know "which breakpoint(s)",
     then refresh the BREAKPOINTS table and then say
     SELECT * FROM BREAKPOINTS_COPY WHERE Is_matching_location='True'.

User Command: 'break' condition
-------------------------------

CALL xxxmdbug.command(channel,'break ... [condition]');
or
CALL xxxmdbug.command(channel,'tbreak ... [condition]');

This is a continuation of the discussion of "break" and "tbreak",
with the additional clause for a condition, "[variable=value]".

Users can specify a condition in addition to
a location (routine name and line number). The condition has
to be true in order for the breakpoint to have effect.

Conditions must have the form
Variable-name = Literal or
Hit_count = Literal or
Variable-name <> Old_value or
Call_stack_depth <= literal.
We could have a few variations, but not many variations.
Sorry, I wish we could handle any expression at all,
but dynamic SQL is illegal in MySQL functions.
I suppose we could say "you could use any expression
at all if you could guarantee that there are no
function calls", but the "Variable-name = literal" syntax
will handle a lot of cases and is like "Set variable_name = literal".

As is visible in icc_process_user_command_breakpoint(),
the Variable-name goes into BREAKPOINTS.Condition_name,
and the Literal goes into BREAKPOINTS.Condition_value.

We're expecting breakpoint_check() to say
  UPDATE BREAKPOINTS SET Is_matching_condition = 'True'
  WHERE CASE condition_name = 'Hit_count' Hit_count
        ELSE(SELECT value FROM VARIABLES WHERE variable_name = condition_name)
        END
        =
        condition_value

Example:
To say "break after hitting a breakpoint 5 times", say
"break test.proc1:5 Hit_count = 5".

Example:
Some debuggers allow "watch expression", which in effect means
"break whenever the expression is true regardless of location".
For this we'd just say that the location is wildcards and ranges,
which will cause icc_breakpoint_check() to get a hit every time.
So to set a watch for @f = 7, the user can say
"break %.% 1-999999 @f = 7".

Example:
For a breakpoint that happens in routine r only when @f changes,
"break r 1-99999 @f <> old_value".
The meaning is "break when the value of variable changes since
the last debuggable statement", not "since the last breakpoint".
The evaluation would take place by comparing
@f with BREAKPOINTS.Old_value and then setting Old_value to @f.

User Command: 'step'
--------------------

CALL xxxmdbug.command(channel,'step [count]');

Continue to the next source line, stopping for functions.

This is easily translated into:
Set a temporary breakpoint for "every line in every routine"
and continue.

CALL xxxmdbug.send(channel,'tbreak %.% 1-99999 Hit_count=[count]');
CALL xxxmdbug.send(channel,'continue');

SIDEBAR: "step over". "Step over" would mean "continue until
the next debuggable statement that is outside the current
compound statement". DB2 supports it. But this next debuggable
statement might be outside the current routine, or in a
handler. I think "step over" is something that we can leave out.

User Command: 'next'
--------------------

CALL xxxmdbug.command(channel,'next');

Continue to the next source line, without stopping for functions.

One might think that this can be accomplished by setting a
temporary breakpoint for the next debuggable line in the
routine. But that would fail for three reasons:
* execution might continue with a handler
* the current statement might be RETURN
* execution might reach that next line via recursion.

So what we really want is a temporary breakpoint for
the next line at the same, or higher, call stack depth.

For example: suppose the CALL_STACK table contains
'ProcedureA'
'FunctionB'
'ProcedureC'
That shows that execution started with ProcedureA which
invoked FunctionB which invoked ProcedureC, and ProcedureC
is the current routine, and call_stack depth is 3.
If ProcedureC invokes a function, call_stack depth will go up.
If ProcedureC exits to FunctionB, call_stack depth will go down.

So say
CALL xxxmdbug.send(channel,'info stack');
SET @xxxmdbug.call_stack_depth=[something from 'info stack' reply];
SET @xxxmdbug_command=CONCAT('tbreak %.% 1-99999 ',
                                 'call_stack_depth <=',
                                 @xxxmdbug_call_stack_depth);
CALL xxxmdbug.send(channel,@xxxmdbug_command);
CALL xxxmdbug.send(channel,'continue');

Unfortunately, since we can only have one condition in 'tbreak',
we cannot additionally say 'hit_count=[count]'. Therefore we
can allow the 'next' command, but not the GDB-like 'next [count]'
command.

User Command: 'delete'
----------------------

CALL xxxmdbug.command(channel,'clear [break number]);

There's an auto_increment column in the BREAKPOINTS table,
Breakpoint_identifier. So "delete 5" will cause
DELETE FROM BREAKPOINTS WHERE Breakpoint_identifier = 5
and "delete" will cause
DELETE FROM BREAKPOINTS;
See "Common Routines: icc_process_user_command".

We could also support "delete [location]", where
[location] has the same syntax as in 'break [location]'.
This would mean "delete all breakpoints whose routine
name and line numbers exactly match [location]".
But I'm guessing that real people would never need that.

There is no equivalent for GDB 'clear breakpoint'.

User Command: 'stop'
--------------------

CALL xxxmdbug.command(channel,'stop');

We can do "stop" with tbreak and wildcards.
By making a temporary breakpoint for the next statement,
no matter where the next statement is, we'll
cause Debuggee Connection to halt on the very next statement.

CALL xxxmdbug.send('xxx','tbreak %.%:1-99999');

Update: this wasn't implemented.

User Command: 'set'
-------------------

CALL xxxmdbug.command(channel,'set variable_name = value');

This is only legal while Debuggee Connection is at a breakpoint.
This causes process_user_command() to update the VARIABLES table.

First perform some error checks.
* There must be a row in the up-to-date VARIABLES table with
  Variable_name = variable_name, and Is_in_scope = 'True'
  and Is_settable = 'True'.
* The length of value must not be greater than the
  length of variables as decided by "setup [maximum_variable_length]".
  In fact it might be a good idea to insert the value
  into a temporary-table copy of VARIABLES, just to
  see whether there's a warning.

If all is well, pass the string to Debuggee Connection without change:
CALL xxxmdbug.send(channel,'set variable_name = value');

User Command: 'finish'
----------------------

CALL xxxmdbug.command(channel,'finish');

Continue until the exit point of the current routine.
Break will happen for the statement after whatever called.

Compare with the "next" command. For "next", we said that 
"So what we really want is a temporary breakpoint for
the next line at the same, or higher, call stack depth."
For "finish", the requirement is similar, we'll say that
"So what we really want is a temporary breakpoint for
the next line at *higher* call stack depth."

So say
SET @xxxmdbug_call_stack_depth=(SELECT COUNT(*) FROM CALL_STACK);
SET @xxxmdbug_command=CONCAT('tbreak %.% 1-99999 ',
                                 'call_stack_depth <=',
                                 @xxxmdbug_call_stack_depth - 1);
CALL xxxmdbug.send('xxx',@xxxmdbug_command);
CALL xxxmdbug.send('xxx','continue');

Update: this wasn't implemented.

User Command: 'exit'
--------------------

CALL xxxmdbug.command(channel,'exit');

This means "stop debug, that is,
end what you started with the 'debug' command.

If you want to get out of the debugger,
thus cancelling the effect of the 'attach' command,
you want "quit".

I thought of having a LEAVE statement for every
compound statement. That wouldn't quite work, because for
"SELECT f() FROM t;" it's not sufficient to get out of f(),
we have to stop the SELECT statement too. That can only
be done by causing an error.

So what we want Debuggee Connection to do henceforward is:
For every statement, instead of doing the statement, do
signal sqlstate '05678' set mysql_errno=5678;
I'm depending on 5678 being unused (there's a check for
that), and since "DECLARE EXIT HANDLER FOR 5678 ..."
is more specific than any "DECLARE CONTINUE HANDLER
for SQLEXCEPTION ..." it ought to be caught reliably.
See section "Generate: "if (exit) signal".

CALL xxxmdbug.send(channel,'exit');
CALL xxxmdbug.send(channel,'continue');

User Command: 'quit'
--------------------

CALL xxxmdbug.command(channel,'quit');

This is not legal if a routine is still being debugged.
You should issue an 'exit' command first.

When a routine has ended, users have the option of
starting another one (see User Command: 'debug'), or
ending the debug session with the 'quit' command.

This cancels the effects of 'attach' by Debugger Connection.
It also cancels the effects of start_debuggee_connection(),
because when Debuggee Connection sees 'quit' it will exit
from the get_user_command() loop.

CALL xxxmdbug.send('quit');

Update: this wasn't implemented.

Command: 'execute'
------------------

CALL xxxmdbug.command(channel,execute SQL-statement);

This will send "SQL-statement" to Debuggee Connection.
Debuggee Connection will PREPARE and EXECUTE SQL-statement.

This will only be legal when there is a breakpoint (Debuggee Connection
has paused and is waiting for input).

First run some error checks.
* Is dynamic SQL legal? If Debuggee Connection is currently
  in a function, then dynamic SQL is not legal. So look at
  every routine in the CALL_STACK table. If any of them is
  a function, that's an error.
* Is SQL-statement preparable? To find out, change to the
  same default database that Debuggee Connection is using,
  and say PREPARE xxxmdbug_stmt FROM [SQL-statement],
  then DEALLOCATE xxxmdbug_stmt. This not only will
  confirm that the SQL syntax is okay, it will confirm that
  max_prepared_stmt_count hasn't been exceeded
  (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_prepared_stmt_count).

If it appears that the SQL-statement might work, send it on.
CALL xxxmdbug.command('xxx','execute SQL-statement');

Debuggee Connection will tokenize the statement before preparing
and executing, and replace references to original routines with
references to surrogate routines. The technique is similar to
what we generate for "special handling of dynamic SQL"

Example:
Some debuggers allow "watch expression", which (as far as I know)
means "display the results of expression". We can do that with
CALL xxxmdbug.command('xxx','execute SELECT expression');

User Command: 'kill'
--------------------

CALL xxxmdbug.command(channel,'kill');

Debugger Connection will say "kill connection-id-of-debuggee-connection";

This is drastic. The clean way to end a debug session
is 'stop' then 'exit' then 'quit'. 'Kill' is only
justifiable when a single SQL statement is executing
so slowly that 'stop' won't work.

Update: this wasn't implemented.

User Command: 'skip'
--------------------

CALL xxxmdbug.command(channel,'skip');

This is similar to 'next', except that Debuggee Connection
skips the next SQL statement instead of executing it.

CALL xxxmdbug.send(channel,'skip');

User Command: 'leave'
---------------------

CALL xxxmdbug.command(channel,'leave');

This will cause Debuggee Connection to execute a
LEAVE statement. That brings execution out of the
current unlabelled compound statement. If it was already in
the outermost compound statement, LEAVE will exit
from the routine.

This depends on the fact that we generated a
label before WHILE or REPEAT or LOOP or
BEGIN, see section "Generate: label".

CALL xxxmdbug.send('xxx','leave');

Common Routines
===============

Common Routines are created by the Install script
in xxxmdbug.
Common Routines are called from generate(), from surrogate
routines, or from Debugger Connection.

Common Routines: A note about comments
--------------------------------------

SQL routines are code, and are protectable by the same
sort of software licenses that one sees in C programs.
Every routine that we supply must start with a comment.

Do not use inline comments. MySQL clients will strip
strings inside "/* ... */" or after "#".
See Bug#14487 Comments are being ripped out of SP's during restore.
http://bugs.mysql.com/bug.php?id=14487
Actually the mysql client has an option for not stripping,
and we recommend it, but we don't assume it's happening.

So the comment has to be a string in a statement.
And the best statement for the purpose is DECLARE,
since it can be at the start. The variable name
should begin with "xxxmdbug_", our reserved namespace.

So always include, right after the BEGIN:
"
  DECLARE xxxmdbug_comment VARCHAR(1000) DEFAULT
  'Copyright. License. Author. Version. Date.';

Common Routines: tokenizer
--------------------------

The procedure xxxmdbug.insert_into_routines() is an SQL tokenizer written in SQL.
It handles the MySQL rules for
One-byte tokens: !%&()*+,-:/=[]^|~<>
Two-byte tokens: && := <= >= << >> <> ||
Three-byte tokens: <=>
Enclosing punctuation: '' or "" or `` or /**/ or {}
In-string special handling: quotes inside quotes
Comment starter: # (not done yet)
Escapes: \' etc. (not done yet)
Inline comments
White space: space or tab or carriage-return
Line Feed: = white space but use it to count line numbers
Period: part of token if digit precedes|follows, else one-byte token
Commercial At Sign: @ or @@ begins a new token
Other: part of a token.
(The rules for "[" and "]" might change in a future MySQL version.)

It inserts each token into a TOKENS table.

Here is a sample exercise that calls tokenizer:

delimiter //
DROP TABLE tokens//
CREATE TABLE tokens (token_number int primary key auto_increment,
                     offset int,
                     line_number int,
                     value varchar(128) CHARACTER SET utf8mb4) engine=memory//
drop procedure Example_Procedure//
create procedure Example_Procedure ()
BEGIN
  DECLARE declared_variable INT DEFAULT 5;
  WHILE @user_variable < 5 DO
    UPDATE t SET column1 = declared_variable;
    END WHILE;
  END
//
select body into @body from mysql.proc where name = 'Example_Procedure'//
delete from tokens//
call insert_into_tokens(@body)//
select * from tokens order by token_number//

Here is the result of the SELECT:
"
mysql> select * from tokens order by token_number//
+--------------+--------+-------------+-------------------+
| token_number | offset | line_number | value             |
+--------------+--------+-------------+-------------------+
|            1 |      1 |           1 | BEGIN             |
|            2 |      9 |           2 | DECLARE           |
|            3 |     17 |           2 | declared_variable |
|            4 |     35 |           2 | INT               |
|            5 |     39 |           2 | DEFAULT           |
|            6 |     47 |           2 | 5                 |
|            7 |     48 |           2 | ;                 |
|            8 |     52 |           3 | WHILE             |
|            9 |     58 |           3 | @user_variable    |
|           10 |     73 |           3 | <                 |
|           11 |     75 |           3 | 5                 |
|           12 |     77 |           3 | DO                |
|           13 |     84 |           4 | UPDATE            |
|           14 |     91 |           4 | t                 |
|           15 |     93 |           4 | SET               |
|           16 |     97 |           4 | column1           |
|           17 |    105 |           4 | =                 |
|           18 |    107 |           4 | declared_variable |
|           19 |    124 |           4 | ;                 |
|           20 |    130 |           5 | END               |
|           21 |    134 |           5 | WHILE             |
|           22 |    139 |           5 | ;                 |
|           23 |    143 |           6 | END               |
+--------------+--------+-------------+-------------------+
23 rows in set (0.00 sec)
"

This tokenizer is not merely useful for debugging.
I'm expecting there will be other applications that need it.
As with all MDBug routines, that requires following the GPL license provisions.

Common Routines: determine what variables are in scope
------------------------------------------------------

determine_what_variables_are_in_scope(statement-number)

We start with a token-number for a token at the beginning
of a statement within a routine.
We want to set Is_in_scope='True' for all VARIABLES rows
where the variable is "in scope" for the statement.

We need to know this so that we can
* Generate icc_copy_variable_to_table_row() and
  icc_copy_table_row_to_variable() statements
* Decide when "set variable = name" is legal.

For example, suppose we have
CREATE PROCEDURE p ()
BEGIN
  DECLARE a,v CHAR(5);
  BEGIN
    DECLARE v DATE;
    END;
  DECLARE a INT;
  SET @x = CONCAT(v,a);
  END;
We're currently looking at the statement "SET @x = CONCAT(v,a);".
We have to use the most-relevant, in-scope, declarations
of v and a.

We'll go backward in the TOKENS table starting with "SET".
When we pass "END" we know that everything is out of scope
until we pass a corresponding "BEGIN". When we pass "DECLARE"
and it's in scope, we mark every variable associated with
that DECLARE as "in scope" -- unless another variable with
the same name has already been marked.
(If variable 'v' is declared twice, the later declaration
supersedes the earlier one.)
So in the end the variables that get marked are:
* The 'a' in "DECLARE a INT"
* Not the 'v' in "DECLARE v DATE" because (going backward)
  we pass an "END" before we see this DECLARE
* The 'v' in "DECLARE a,v CHAR(5)"
* Not the 'a' in "DECLARE a,v CHAR(5)" because we have
  already got an 'a' from the "DECLARE a INT" statement.

In a vaguely SQL-like way, the algorithm looks like

DECLARE token_cursor FOR 
SELECT FROM TOKENS
  WHERE Routine_schema = x AND Routine_name = x AND Byte_offset < (Byte_offset of SET token)
ORDER BY id DESC;
UPDATE VARIABLES SET Is_in_scope='False';
@end_count = 0;
LOOP
  FETCH x INTO @Token_Number, @Value;
  IF (not found) break;
  If @Value='END' @end_count = @end_count + 1;
  If @Value='BEGIN' @end_count = @end_count - 1;
  IF @Value='DECLARE'
    IF @end_count=0
      /* This DECLARE is in scope.
         Mark any variables associated with this DECLARE
         as "in scope", unless a variable with the same
         name has already been marked . */
      UPDATE VARIABLES SET Is_in_scope='True'
      WHERE Token_number_of_declare=(this DECLARE)
      AND (Variable_name NOT IN (SELECT Variable_name FROM VARIABLES WHERE Is_in_scope='True');

It's just a tad more complex than that, because
BEGIN/END isn't the only scope-changer. Also watch
for WHILE/END WHILE, LOOP/END LOOP, REPEAT/END REPEAT.

Also, parameters are always in scope, and are superseded
by variables with the same name. So the parameter list
should be taken as an equivalent of a "DECLARE".

Common Routines: diagnostics_handler
------------------------------------

The original statement (that is, the statement from the
original routine) might end with an error or a warning.
We want to store that in the DIAGNOSTICS table, so that
Debugger Connection can just say 'refresh' and 'select
from get_diagnostics' to see the error or warning.
Later, when users encounter SHOW WARNINGS | ERRORS,
the "diagnostics special handling" can make a result
set out of the DIAGNOSTICS table for them.
But also, it should be possible for Debugger Connection
to read those diagnostics any time.

The first fear is that an error might be grabbed
by an error handler from the original routine,
before we could store the error.
We try to take care of that by generating our own
DECLARE ... HANDLER statement immediately before
the generated statement, so that errors will be
caught by the debugger first and processed in
diagnostics_handler().

The second fear is that the surrogate routine has
many extra SQL statements which might clear the
error message list. We try to handle that too.
Consider this script:

DELIMITER //
DROP PROCEDURE p1//
DROP PROCEDURE p2//
DROP TABLE x//
DROP TABLE variables//
CREATE TABLE diagnostics (errno INT)//
CREATE PROCEDURE diagnostics_handler ()
BEGIN
  DECLARE EXIT HANDLER FOR 1051 BEGIN INSERT INTO diagnostics VALUES (1051); RESIGNAL; END;
  RESIGNAL;
  END//
CREATE PROCEDURE p2 ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION CALL diagnostics_handler();
  DROP TABLE no_such_table;
  END//
CALL p2()//

The result is:
"
mysql> CALL p2()//
+------+
| 1051 |
+------+
| 1051 |
+------+
1 row in set (0.00 sec)

ERROR 1051 (42S02): Unknown table 'test.no_such_table'
"

That proves that it's possible to store a statement's diagnostics
in a table, without losing the diagnostics. However, the way that
we did it was to say "if error is 1051 then put in 1051". We'll
have to do the same thing for every possible error number.
(There's no GET DIAGNOSTICS support in MySQL 5.5.)
That's every error described in error.h, and
every error that has appeared in a SIGNAL or RESIGNAL
statement in any of the user routines (search the STATEMENTS
table to see the texts of all SIGNAL or RESIGNAL statements).
See also
http://rpbouman.blogspot.com/2009/06/mysql-stored-functions-impact-of.html
So picture many DECLARE EXIT HANDLER statements here.

Some things to watch out for when doing this are:
* The SHOW WARNINGS and SHOW ERRORS and (someday)
  GET DIAGNOSTICS statements, plus SIGNAL/RESIGNAL,
  might need to be handled differently.
* Sometimes it will be difficult to tell whether it's an error or a warning, so do it as
   DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN
     DECLARE CONTINUE HANDLER FOR 1234 BEGIN
       /* save 'warning', '1234' */;
       RESIGNAL;
       END;
     RESIGNAL;
     END;
* This is still not enough for "notes", since they won't be intercepted.
* You'll also have to save @@warning_count and intercept SHOW (COUNT *) WARNINGS.
* Beware of mysql_warning_count(), max_error_count, and sql_notes.

Update: this wasn't implemented.

Common Routines: icc_get_user_command
-------------------------------------

icc_get_user_command()
is what Debuggee Connection will call from
icc_core() multiple times in order to get messages from
Debugger Connection.

Its job is to get a user command. To see how that's
done, read the "ICC" chapter, particularly
"ICC: A working example".

Return with @xxxmdbug_message is NULL (no
messages pending), or @xxxmdbug_message = the first command
in the message queue.

Common Routines: icc_process_user_command
-----------------------------------------

The possible messages are
break|tbreak, clear, refresh, set, execute, continue, exit, leave, skip.
One can say 'break|tbreak' and 'clear' and 'refresh' any time.
One can say 'set' and 'execute' and 'continue' and 'exit' and
'leave' and 'skip'
only while Debuggee Connection is stopped at a breakpoint.
One can say 'debug' only when debugging isn't in progress.

First call the tokenizer.

For most commands, the real explanation is in the section
"User Commands". The following IFs are just to show that
icc_process_user_command() will have a bunch of IFs.

If "break" THEN
  see section "Common Routines: icc_process_user_command_break"
If "clear" THEN
  DELETE FROM BREAKPOINTS
  WHERE breakpoint_identifier = (id from "clear id")
If "set" THEN
  UPDATE VARIABLES
  SET VARIABLES.Value = (value from "set variable = value"),
      VARIABLES.Is_updated_by_set = 'True'
  WHERE VARIABLES.Variable_name = (variable from "set variable = value")
If "exit" THEN
  /* just return with @xxxmdbug_command = 'exit'
If "execute" THEN
  /* Debugger Connection has already checked for errors,
     but has not replaced routine references with
     surrogate-routine references.
     Call change_statement_text() first.
     Errors will end up in DIAGNOSTICS table.
     PREPARE xxxmdbug_stmt FROM execute-string.
     EXECUTE xxxmdbug_stmt.
     See comments in "Command: 'execute'" and in section
     "Common Routines: Change statement text: special handling of dynamic SQL" */
If "continue" THEN
  /* just return with @xxxmdbug_command = 'continue'
If "debug" THEN
  See discussion in section "Command: 'debug'"
If "refresh" THEN
  See discussion in section "Command: 'refresh'"
If "skip" then
  /* just return with @xxxmdbug_command = 'skip'
If "leave" then
  /* just return with @xxxmdbug_command = 'leave'

Common Routines: icc_process_user_command_break
-----------------------------------------------

The syntax is
"break|tbreak [schema_name.]routine_name line_number_minimum[-line_number_maximum] [variable=value]"

Given that we can call tokenizer(),
and knowing that default schema_name = current_database,
and knowing that default maximum_line_number = minimum_line_number,
and knowing that default condition = '',
we can "parse" to extract variable values for:
@xxxmdbug_schema_name,
@xxxmdbug_routine_name,
@xxxmdbug_line_number_minimum,
@xxxmdbug_line_number_maximum,
@xxxmdbug_condition_name,
@xxxmdbug_condition_value,
@xxxmdbug_is_temporary.

And use those variables in an INSERT statement:
INSERT INTO BREAKPOINTS
(schema_name,routine_name,line_number_minimum,
 line_number_maximum,condition_name,condition_value,xxxmdbug_is_temporary)
VALUES
(@xxxmdbug_schema_name,@xxxmdbug_routine_name,
 @xxxmdbug_line_number_minimum,@xxxmdbug_line_number_maximum,
 @xxxmdbug_condition_name,@xxxmdbug_condition_value,
 @xxxmdbug_is_temporary);

Common Routines: icc_copy_variable_to_table_row
-----------------------------------------------

icc_copy_variable_to_table_row() is called from generated routines.
It can happen at a breakpoint, so the user might change variable values
with the "set" command.

UPDATE VARIABLES SET value = variable-value WHERE variable_name = variable-name
AND statement_number_of_declare = statement-number-of-declare;
It would probably be good to add "AND Is_in_scope = 'True'".

The UPDATE might cause a warning, such as "Data truncated for column 'value'".
In that case, we know that VARIABLES does not have a perfect
copy of the variable, so set Is_settable = 'False'. When a variable
has Is_settable = 'False', the 'set' command is illegal.

Common Routines: icc_copy_table_row_to_variable
-----------------------------------------------

icc_copy_table_row_to_variable(variable, token_number_of_declare, variable_name)
is called from generated routines.
This is the reverse of icc_copy_variable_to_table_row.
It happens after a breakpoint.
(The user can only update variables when there's a breakpoint.)

We only need to copy if the user updated variables, with the "set" command.
So in icc_process_user_command(), when handling "set",
we set Is_updated_by_set = 'True'. We check it
now: "update ... where Is_updated_by_set = 'True'.
And set Is_updated_by_set back to 'False'.

SET variable = (SELECT value FROM VARIABLES WHERE variable_name = 'variable_name');

Common Routines: icc_breakpoint_check
-------------------------------------

Debuggee Connection will call icc_breakpoint_check() from
icc_core() once in order to check whether Debuggee Connection's
current location and current variable values are matches
for the location and condition columns in one or more
rows of the BREAKPOINTS table.

The BREAKPOINTS table has rows added
due to "INSERT INTO BREAKPOINTS ..." statements
earlier. The icc_core() routine will know the routine name
and line number from the original routine (that is part
of the "Generate:" work). So we can search BREAKPOINTS
for matching routine_names and line numbers. If any
rows are found, mark what they were and return a value
greater than zero. Otherwise return zero, that is,
there's no breakpoint here.

For example, suppose BREAKPOINTS contains this row:
  Breakpoint_identifier              1
  Routine_schema                     test
  Routine_name                       proc1
  Line_number_minimum                5
  Line_number_maximum                5
  Condition_name                     @f
  Condition_value                    7
  Old_value                          NULL
  Hit_count                          0
  Is_temporary                       N (not temporary)
  Is_temporary_and_to_be_cleared     N (not temporary)
  Is_matching_location               N
  Is_matching_condition              N
This means "if you're at line 5 in routine test.proc1,
and the current value of @f is equal to 7, then
increment hit_count and set Is_matching_location to 'True'
and set Is_matching_condition to 'True'.

There are actually two sets of criteria:
1. Is the location a match? The location columns are
   Routine_schema and Routine_name and Line_number_minimum
   and Line_number_maximum. They can be compared with the
   current  routine schema and routine name and line number
   (we know these due to 'Generate: call icc_core()' parameters).
   If location is a match, we increment "hit_count"
   ("hit" is a DB2 term), and we set "Is_matching_location"
   to "Y".
2. Is the condition a match? original condition expression had
   to have the form "name = literal" or "hit_count = literal"
   (see "Command: 'break' or 'tbreak'").
   We only check the condition if the
   earlier check ("is the location a match?") is true.
   If a condition is a match, we set Is_matching_condition='True'.

Those criteria, in SQL terms, look something like this:
  UPDATE BREAKPOINTS SET Is_matching_location='False',Is_matching_condition='False';
  UPDATE BREAKPOINTS SET Hit_count=Hit_count+1,Is_matching_location='True'
    WHERE Routine_schema LIKE current-routine-schema
    AND   Routine_name LIKE current-routine-name
    AND   current-line-number BETWEEN Line_number_minimum AND Line_number_maximum;
  UPDATE BREAKPOINTS SET Is_matching_condition='True'
  WHERE Is_matching_condition='True' AND [Condition is true]";
  RETURN (count of changed rows);

For the words "[Condition is true]", one must substitute the
somewhat cryptic-looking expression
  CASE condition_name = 'Hit_count' Hit_count
       condition_name = 'Call_stack_depth' /* see later paragraph */
  ELSE (SELECT value FROM VARIABLES WHERE variable_name = condition_name)
  END
  =
  condition_value
The first part of the expression works because Hit_count is a
column of the VARIABLES table. The second part of the expression
works because BREAKPOINTS.condition_name must be a variable name
(since it's not Hit_count), and VARIABLES.variable_name of course
is also a variable name. Of course, it's not quite that easy
because declared-variable names aren't unique.

If (count of changed rows > 0)
  UPDATE BREAKPOINTS SET Is_temporary_and_to_be_cleared='True' WHERE Is_temporary='True';

(NB: This is checked *before* the statement execution,
so if statement#x causes a change, the breakpoint happens
on the next statement.)

Matching the current routine with BREAKPOINTS.routine_schema
and BREAKPOINTS.routine_name should be simple. Just remember
it's a LIKE search, and beware that LIKE searches use '_' as
a wildcard by default.

Notice that you have to set Old_value at the time that
you do the INSERT INTO BREAKPOINTS:
INSERT INTO BREAKPOINTS VALUES ((SELECT Value FROM VARIABLES ...), etc.).

Finally, re the condition "call_stack_depth <= [n]":
it's of course possible to get call stack depth by
SELECT COUNT(*) FROM CALL_STACK;
but probably it would be better to have a permanent
user variable, @xxxmdbug_call_stack_depth,
incremented and decremented by starter and ender.

Common Routines: icc_send_statement_status
------------------------------------------

icc_send_statement_status(routine_schema, routine_name, line_number)
is called from icc_core, and from debuggee_wait_loop.

Create a "statement status" message.
Send it to Debugger Connection.
Put it in CONNECTIONS table.

The message contains, in addition to the usual ornaments:

'statement status': a literal
breakpoint: whether a breakpoint has been reached
location: schema_name, routine_name, line_number, statement_number
message_number: of last received message
datetime: current_timestamp

This is a no-wait message, the Debuggee Connection does
not expect a reply.

This is a temporary message, it is overwritten the next
time that icc_send_statement_status() is called.

Common Routines: icc_core
-------------------------

icc_core(routine_schema, routine_name, line_number, statement_number)
is called from generated routines, once per debuggable statement.

Its first job is to get and process any user commands that are pending.
Some user commands ('break', 'refresh', etc.) can be
sent by Debugger Connection at any time, and they go in a
queue until icc_core() handles them now.

Its second job is to check if it's at a breakpoint, for example,
if the pending statement is at line 5 of the original routine,
and there's a breakpoint for line 5 in the BREAKPOINTS table.
If that happens, then it goes into a loop, waiting for new
commands, and processing them. Some user commands ('continue',
'set', etc.) cannot be sent by Debugger Connection at any time,
they can only be sent while Debuggee Connection is in this loop,
signaling "I am at a breakpoint". The loop ends when Debuggee
Connection receives a command saying 'continue' or 'exit'.

REPEAT
  /* get and process any pending messages from Debugger Connection */
  CALL icc_get_user_command()
  CALL icc_process_user_command()
  Until there are no more messages from Debugger Connection
IF breakpoint_check() = 0 THEN
  /* We're not at a breakpoint, so continue */
  CALL icc_send_statement_status('continuing');
  RETURN
/* We're at a breakpoint */
CALL icc_send_statement_status('break');
REPEAT
  DO SLEEP(0.1 seconds)
  CALL get_user_command();
  CALL process_user_command();
  Until command = 'continue' or 'exit'
/* We're continuing from a breakpoint. */
/* Clear obsolete temporary breakpoints, and set
   breakpoints which are 'matching' to 'not matching'.
   We delayed this step till now so that Debugger
   Connection could examine what breakpoint caused
   execution to stop.*/
DELETE FROM BREAKPOINTS WHERE Is_temporary_and_to_be_cleared='True';
UPDATE BREAKPOINTS SET Is_condition_match='False',Is_location_match='False';
CALL icc_send_statement_status('continuing');

Common Routines: Change statement text
--------------------------------------

change_statement_text(input_statement,
                      output_statement_1,
                      output_statement_2)
This is a common routine.

It is called during generation (see "Generate: statement text"),
for dynamic SQL, and for the "execute" User Command.

If input_statement contains references to routines,
change them to references to surrogate routines.
The result will be one or two output statements.

Given that we have a tokenizer but not a parser,
how do we know if a token is a reference to a routine?
I think the answer is:
"If token matches a function name in the ROUTINES table,
and next token is '(', it's a function reference.
If token matches a procedure name in the ROUTINES table,
and previous token is 'CALL', it's a procedure reference."
I realize this answer will need tweaking for special
bits of MySQL syntax, especially after CREATE or JOIN.

For example, suppose input_string is
SELECT * FROM t WHERE x.y() = 5;

Scan the TOKENS table until you see '('.
Let @xxxmdbug_possible_routine_name = the previous token,
which is 'y'.
If the token before that is '.',
  Let @xxxmdbug_possible_routine_schema = the previous token,
  which is 'x'.
Otherwise @xxxmdbug_possible_routine_schema = database().
SELECT ...
FROM ROUTINES
WHERE @xxxmdbug_possible_routine_schema = Routine_schema_of_original
AND   @xxxmdbug_possible_routine_name = Routine_name_of_original
If there's a matching row
  If none of the preceding 3 tokens is CREATE or JOIN
    Let the output string = the input string,
    except that 'x.y' is replaced by xxxmdbug_name,Routine_name_of_surrogate.
Thus, the output string is
SELECT * FROM t WHERE xxxmdbug_1.x_y() = 5;

Common Routines: change statement text: special handling of SELECT
------------------------------------------------------------------

Debuggee Connection cannot handle a result set,
because it's inside a procedure -- it
cannot call mysql_result(). Therefore
Debuggee Connection must pass the result set
to Debugger Connection for handling. This is
possible by selecting into a table.
For example:
CREATE PROCEDURE p ()
BEGIN
  SELECT * FROM t;
  END
should become
CREATE PROCEDURE p ()
BEGIN
  CALL icc();
  BEGIN
    DROP TABLE IF EXISTS xxxmdbug.RESULTSET;
    CREATE TABLE xxxmdbug.RESULTSET AS SELECT * FROM t;
    END;
  END

Debugger Connection will be able to say
SELECT * FROM xxxmdbug.RESULTSET;
and use the result set from that.

The only little problems are:
* Debuggee Connection will drop RESULTSET when it encounters
  another SELECT statement. If users want to be sure that
  they can see result sets before they disappear, they will
  have to put breakpoints right after the SELECTs.
* SELECT isn't the only statement that can produce a
  result set. We have to replace every SHOW, and a few
  other minor statements, with the SELECTs that can
  produce the same result.

Update: this wasn't implemented.

Common Routines: Change statement text: special handling of SHOW WARNINGS
-------------------------------------------------------------------------

This is also known as the late diagnostics problem.

SHOW WARNINGS (and related statements like SHOW ERRORS)
will select from the xxxmdbug.DIAGNOSTICS table.
For details see the description of diagnostics_handler()
in section "Common Routines: diagnostics_handler".

Update: this wasn't implemented.

Common Routines: Change statement text: special handling of dynamic SQL
-----------------------------------------------------------------------

PREPARE and EXECUTE and DEALLOCATE require special handling.

The problem is that MySQL won't reveal what statements were prepared.
See also Bug#39791 "MySQL needs a SHOW PREPARED STATEMENTS report".
We do our best to solve that by saving the statement text
at PREPARE time, in the PREPARED_STATEMENTS table.
If (the statement is PREPARE stmt1 FROM @x)
  DELETE FROM PREPARED_STATEMENTS WHERE statement_name = 'stmt1';
  Insert into PREPARED_STATEMENTS('stmt1',@x);
If (the statement is EXECUTE stmt1)
  SELECT statement_text FROM PREPARED_STATEMENTS where statement_name = 'stmt1'
  /* If it's not found:
     This is a problem that we can't solve.
     If the user prepared stmt1 inside a debugged procedure,
     we know what it is. But otherwise, we just have to cross
     our fingers and hope the statement isn't changing variables
     or calling functions that are not being debugged. Or, we could generate an
     error. I suppose this can be the subject of a warning at
     the end of "Setup", and I suppose this can be an option
     (error-on-dynamic-sql-problem) when starting "Setup". */
  Tokenize(statement_text)
  If there are new variables, add them to VARIABLES.
  If there are function references, change to surrogate-function references.
  If this is a SELECT, don't forget "Special handling for SELECT"
  (We have a similar technique for User command "[Debuggee Connection] SQL statement".)
If (the statement is DEALLOCATE stmt1)
  Delete from PREPARED_STATEMENTS where statement_name = 'stmt1'

For example, if the statement is DEALLOCATE stmt1, we'd generate:
DELETE FROM PREPARED_STATEMENTS WHERE statement_name = 'stmt1';
DEALLOCATE stmt1;

Common Routines: Change statement text: special handling of RETURN
------------------------------------------------------------------

If the statement we're about to generate is RETURN,
we have to delete from CALL_STACK before returning.

Common Routines: Change statement text: special handling of SIGNAL/RESIGNAL
---------------------------------------------------------------------------

If the statement we're about to generate is SIGNAL or RESIGNAL,
we'll check whether the values might conflict with the
signal values that the debugger uses for its own purposes.

Recall that the user can begin with
"call setup (...signal_number...)"
and signal_number has a special meaning for the
debugger. For example, if signal_number = 5678
(the default), we will generate "SIGNAL ... mysql_errno=5678"
at the start of every surrogate routine.
It would confuse the debugger if the original
routine used the same mysql_errno value for signals.

So if the word in the STATEMENTS table is 'SIGNAL' or 'RESIGNAL',
and if any token in the TOKENS table is '5678' or '05678',
add a warning in the DIAGNOSTICS table. Warnings produced
during setup() are displayed when setup() ends.

Search the STATEMENTS table for any statement
that starts with 'SIGNAL' or 'RESIGNAL', search
the TOKENS table to see whether that statement
contains any token value equal to Signal_number,
and generate a warning if you find one.

Update: this wasn't implemented.

Common Routines: command
------------------------

command(channel,user command) is called by the user's
application on Debugger Connection.

Somehow be ready for a request to 'interrupt'
(like control-C), but I don't think command()
has any loops.

For any commands that require sending messages,
check first: is Debuggee Connection moribund?

Parse the command.

Do a SELECT, send a message, or whatever.
The basic explanations are in section "User Commands".

Common Routines: send
---------------------

send(message text)
passes a message between Debugger Connection
and Debuggee Connection. See the "ICC" sections.

Common Routines: README table
-----------------------------

The README table, a permanent table in xxxmdbug,
has data about the debugger and about the installation.
It's made thus:
CREATE TABLE README (
  Name
  Authors
  Version of debugger
  Date of Last Revision of debugger
  Copyright
  Release Notes
  Link to documentation
  Is_install_script_done)

"INSERT INTO README (fixed information)"
is done by the install script, install.sql.
The final column, Is_install_script_done, is set when
the install script is complete.

ICC (Inter-Connection Communication)
====================================

A foundation stone of the two-connections method is
that two connections must be able to communicate.
This is not what we could call "obviously possible".
MySQL has no equivalent of the DBMS_PIPE found in
Oracle 11g and EnterpriseDB. So I'd better show that
ICC (Inter-Connection Communication) is possible in
this context.

ICC: The choice of method
-------------------------

But how do Debugger Connection and Debuggee Connection
exchange messages?

* By updating tables in xxxmdbug? No.
The MySQL Reference Manual says:
"A stored function acquires table locks before executing,
to avoid inconsistency in the binary log due to mismatch
of the order in which statements execute and when they
appear in the log."
http://dev.mysql.com/doc/refman/5.5/en/stored-program-restrictions.html
... So, while Debuggee Connection is working with a function,
Debugger Connection cannot update any xxxmdbug tables
that Debuggee Connection might read.

* With GET_LOCK() and IS_USED_LOCK()? No.
TOAD found a use for GET_LOCK(), and Beat Vontobel
recommended it in his debugger suggestions.
But a connection can have a maximum of one (1) lock.
(This is Bug#1118 "Allow multiple concurrent locks with GET_LOCK()"
http://bugs.mysql.com/bug.php?id=1118.). So this
would fail if the debugged routine contained GET_LOCK,
and would convey too little information to be generally useful.

* With information_schema.processlist? No.
Debugger Connection could say "SELECT SLEEP(0.5),'message';" and
Debuggee Connection could see it by looking at
information_schema.processlist.Info. But this requires
dynamic SQL, and dynamic SQL is illegal in functions.

* With a file? No.
Debugger Connection can write to a file. But I can't see how
Debuggee Connection could read it. LOAD DATA doesn't work in
stored procedures. One could almost get something useful with
SELECT INTO OUTFILE and LOAD_FILE() but the inability to overwrite
files stopped me.

* With CREATE VIEW? No.
Debugger Connection could CREATE VIEW xxxmdbug.v
AS SELECT 'message'; and Debuggee Connection could see it by looking
at information_schema.views.view_definition. But this
requires dynamic SQL, and dynamic SQL is illegal in functions.

* With a global variable? Yes.
It's odd, but it's all we've got.
Debugger Connection could change a dynamic global variable,
and Debuggee Connection would see the change.
This requires a SUPER privilege, but that's required anyway.

The appropriate global variable is @@init_connect.
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect
It's dynamic, it's a string, and its contents can include.
/* inline comments */. 

Thus, to send messages, a connection appends them to
@@init_connect. Another connection, to receive messages,
extracts and strips them from @@init_connect with INSTR()
and SUBSTRING() functions.

ICC: A working example
----------------------

This is an example, an example that actually works,
of inter-connection communication using the method
described in the previous section. You can try it now.

You will need a default database (`test` is good enough).
You will need to start two connections, which I'll call
Connection #1 and Connection #2.

Using either connection, on the default database,
create two functions, named send() and recv():

DELIMITER //
CREATE FUNCTION send (debug_schema_n VARCHAR(64),
                      Message_number INT,
                      Message VARCHAR(64)) RETURNS INT>
BEGIN
  SET @debug_schema_n='debug_schema_1';
  SET @debug_schema_message_number = 1;
  SET @debug_schema_message = 'break Example_Procedure:5';
  SET GLOBAL init_connect = CONCAT(@@init_connect,
  '/*',@debug_schema_n,' Message=',Message_number,' ',Message,'*/');
  RETURN 0;
  END//
DELIMITER ;

DELIMITER //
USE test
DROP FUNCTION recv//
CREATE FUNCTION recv (debug_schema_n VARCHAR(64)) RETURNS VARCHAR(64)
BEGIN
  SET @debug_schema_message_start = INSTR(@@init_connect,CONCAT('/*',debug_schema_n,' Message='));
  SET @debug_schema_message_length = INSTR(SUBSTRING(@@init_connect FROM @debug_schema_message_start),'*/')+2;
  SET @debug_schema_message = SUBSTRING(@@init_connect FROM @debug_schema_message_start+2
                                      FOR @debug_schema_message_length-5);
  SET GLOBAL init_connect =
  CONCAT(
  LEFT(@@init_connect,@debug_schema_message_start+1),
  RIGHT(@@init_connect,LENGTH(@@init_connect)-(@debug_schema_message_start+@debug_schema_message_length)));
  RETURN @debug_schema_message;
  END//
DELIMITER ;

On Connection #1, which will be the 'sender',
send two messages:
'break Example_Procedure:5', and
'continue'. Say:
CALL send('debug_schema_1',1,'break Example_Procedure:5');
CALL send('debug_schema_1',2,'continue');

On Connection #2, which will be the 'receiver',
receive two messages. Say:
SELECT recv('debug_schema_1');
SELECT recv('debug_schema_1');

The result that you will see on Connection #2 is:
"
mysql> SELECT recv('debug_schema_1');
+----------------------------------------------------+
| recv('debug_schema_1')                             |
+----------------------------------------------------+
| debug_schema_1 Message=1 break Example_Procedure:5 |
+----------------------------------------------------+
1 row in set (0.00 sec)                               

mysql> SELECT recv('debug_schema_1');
+-----------------------------------+
| recv('debug_schema_1')            |
+-----------------------------------+
| debug_schema_1 Message=2 continue |
+-----------------------------------+
1 row in set (0.00 sec)
"

This proves messages can be passed and received,
from within functions, without requiring dynamic
SQL, without requiring tables.

ICC: Protocol
-------------

As we've seen, Debuggee Connection always stops
when it reaches a breakpoint and it's time to wait for Debugger Connection.
Debugger Connection doesn't have to wait for Debuggee Connection,
except that it can't say "continue" or "exit" or "set" until Debuggee Connection reaches a breakpoint.
The messages just keep being appended to @@init_connect,
so it's a message queue.

Debuggee Connection accepts messages from Debugger
Connection, processes them, and doesn't necessarily
respond.

At start:
Debuggee sends: "Debuggee Start is done".
Debugger sends: "attach.
Debuggee sends: "ok" or "not ok".

At any time, for 'break':
Debugger Connection sends. It doesn't wait for a reply.

At any time, for 'check if Debuggee is at a breakpoint':
Debugger Connection receives. If there's no message,
that means Debuggee isn't at a breakpoint. If there's
a message, it must be "Debuggee halted at a breakpoint".

If Debuggee is halted at a breakpoint:
Repeat
  Debugger Connection sends, e.g. 'set variable'.
  Debuggee Connection receives and replies: 'ack'.
  Until Debugger Connection has no more messages.
Debugger sends: 'continue'. It doesn't wait for a reply.

The only messages that Debuggee Connection sends are:
"at breakpoint":   Sent by Debuggee Connection.
                   Debugger Connection needs to
                   know this so that it can send
                   user commands which are only
                   possible during a breakpoint,
                   such as "set variable".
"debuggee start":  When Debuggee Connections finishes
                   the Debuggee Start procedures, it
                   sends a message:
                   Debug_schema name
                   User Name
                   Debuggee Connection's connection_id()
                   Start Time
"ack (ok/not ok)": response to Debugger sending.
"statement status":See Common Routines: icc_send_statement_status.

ICC: notes
----------

In reality the send() and recv() functions will be more
complex than the working example from the previous section.
These improvements will be necessary.

Allow for the chance that two debug sessions are
happening simultaneously. That's why one of the
components of a message is a channel like 'xxx', so
that messages for other debug sessions can be
detected and ignored.

Put in escapes. For example, it would be a disaster
if the message contained '/*' or '*/'. Such sequences
must be replaced with innocuous escape sequences.

While creating the surrogate routines, emit a
warning if any original routine changes @@init_connect.

Debugger Connection can send multiple messages as
separate appended comments. Debuggee Connection
will see them when it's about to handle any
debuggable statement, regardless whether it's at
a breakpoint or not.

Put in a message sequence number, but it doesn't get
much use. I expect it makes some error tests possible.
It also might prevent confusion when Debuggee responds
to a message: it indicates which message is it
responding to, if there's a queue.

Since @@init_connect has other uses, we have
to allow for mutex contention, getting junk
in the binlog, making @@init_connect too big,
cleaning up at the end of a session, and
recovering if some non-debug connection
wrecks the message-passing system by erasing
the inline comments.

Appendixes
==========

Appendix: Executing SQL statements on Debugger Connection
---------------------------------------------------------

On Debugger Connection, it's legal to execute any SQL statement,
as well as send User Commands. We will encourage this.
It's particularly useful that users can SELECT from copies
of xxxmdbug tables (BREAKPOINTS etc.) while
Debuggee Connection is running and is not at a breakpoint.
But watch out for some things.

Do not alter or drop an original routine.
This will cause the Debuggee Connection to abort
when it enters the surrogate routine.
You will have to run setup() again.

Expect concurrency issues due to locking.
If Debugger Connection says "SELECT * FROM t"
while Debuggee Connection is holding a lock on t,
Debugger Connection will wait until Debuggee
Connection releases the lock. Ordinarily that's
okay. But if Debuggee Connection now hits a
breakpoint, then it will wait for a command
from Debugger Connection -- which will never come.

Expect concurrency issues due to transactions.
SELECT statements will cause different results when
executed on Debugger Connection rather than Debuggee Connection.
For example,
SELECT * FROM t;
may differ from
CALL xxxmdbug.command("SELECT * FROM t;");
because Debuggee Connection has inserted into t but
not committed yet.

Appendix: Debugging without surrogates
--------------------------------------

Sometimes we will fail to catch an invocation of routine X and
change it to an invocation of the surrogate routine xxxmdbug000_X.
This causes us to miss breakpoints for that particular routine.
If the routine makes new user variables or prepared statements,
we'll also miss those changes.

There are four failure situations, involving privileges,
views, triggers, events, and attaching.

PRIVILEGES. We might not have the right to see the routine_definition.
This will never happen with a routine that we debug directly,
because if we don't have EXECUTE privilege on the routine then
we can't debug it anyway. But it can happen with a routine that
we debug indirectly, for example we say "debug P1()" and P1
contains a statement "CALL P2()" without definer privileges.

VIEWS. A view might contain a reference to a function, for example
"CREATE VIEW v AS SELECT * FROM t WHERE c=f()". We don't see that
when we're looking at a statement like "DELETE FROM v".
We could parse the view definitions and make "surrogate views",
but that's a harder job than making surrogate routines, because
(a) it's hard to find view references inside a DML statement
(b) view names are case sensitive
(c) we'd need more privileges.

TRIGGERS. A trigger is bound to a table. There's no way to
change that binding, except altering the table or replacing
the trigger definition. Incidentally SQL Server also had a
problem with triggers, and the proposed solution does not
look like ours. From SQL Server Limitations page:
  "Triggers must be fired to be debugged: you cannot debug
  triggers directly. Instead start debugging in a stored
  procedure that will cause the trigger to fire."
  http://msdn.microsoft.com/en-us/library/kkyhd4yb%28v=VS.80%29.aspx

EVENTS. We don't control the execution path that leads to
the activation of an event. It would actually be rather
easy to make "surrogate events" and generate code just as
we do for procedures and functions. But the Debugger
Connection couldn't "attach" to such an event because it
doesn't automatically call a "Debuggee start" process.
And as a general rule we can't say "debug x" if we can't
invoke x ourselves.

The solution is essentially similar for any of the failure situations.
An administrator -- someone who has the power to change definitions --
must change all the relevant routine code so that it invokes
surrogate routines rather than original-schema routines,
and/or so that the original-schema code calls surrogate routines.

For example, the administrator must change:

CREATE VIEW v AS SELECT schema1.function1() FROM t;
to
CREATE VIEW v AS SELECT schema1.xxxmdbug000Ffunction1() FROM t;

CREATE EVENT e AS CALL p();
to
CREATE EVENT e AS CALL xxxmdbug000Pp();

This is "intrusive", it changes the original code.
However, it's only visible to the administrator.
To the person running a debug session, it's still transparent.
And if somebody calls a surrogate routine while a debug
session isn't happening, the debug stuff is simply ignored,
the procedure runs as if the debug stuff isn't there.

We expect that some administrators will make scripts to
change original routines, or to change back to original routines.

One has to be careful about automatically dropping
surrogate routines, because there might be references to them,
if somebody has decided to do debugging without surrogates.

Appendix: Widgets or "Screens and Menus"
----------------------------------------

For two reasons this part of the description
will be much briefer than other sections:
1. Integration.
   Commonly the debugger is part of a larger package
   For example Oracle debugging can be integrated with Visual Studio.
   For example DB2 debugging can be integrated as "integrated
   stored procedure debugger in DB2® Developer Workbench Version 9.1."
   And dbForge and TOAD debuggers are part of packages.
   I expect that to happen for our debugger too.
   Therefore the elements of the GUI -- the
   screens and menus -- are already decided.
   For example, if F8 already means "exit program"
   in the larger package, then we're not going to
   be able to say that F8 means "clear breakpoint"
   in the debugger component.
2. Prior Knowledge.
   Publicly documented GUI debuggers exist.
   To find inspiration for most situations,
   we'll merely run DDD or read the SQL Server
   manual.

The program which controls the GUI is also
connected to MySQL as "Debugger Connection".

Routine definitions are in non-editable resizable
text boxes. Users can choose routines via
list boxes (listing all routines or all routines
in the call stack or all routines previously seen).
When a breakpoint is hit, the focus is the
box with the routine with the breakpoint.
... This part is simple since the texts are in
information_schema.routines.

A new event has to be programmed: "message exists".
It has to be activated when Debuggee sends a new message.

To set or clear a breakpoint, users choose a line in
the routine-definition box (by right clicking
or by hovering) and enter a function key.
... This would cause a User command: "break".

Click A to see current breakpoints.
... This would cause "SELECT ... FROM BREAKPOINTS".

Click B to see the call stack.
... This would cause "SELECT ... FROM CALL_STACK".

Click C to edit a watch list.
... This would bring up a list box that's populated
via "SELECT ... FROM VARIABLES".

Click D to stop.
... This would cause a User command "stop".

Click E to finish.
... This would cause a User command "finish".

Click F to change a variable.
... This would cause a User command "set".

Click G to change fonts, colors, languages,
widget sizes, widget positions, and widget visibility.

Click H to step.
... This would cause a User command "step".

Click I to next.
... This would cause a User command "next".

Click J to run to cursor.
... This would cause setting of a temporary
breakpoint with the User command "tbreak ...
[line number where cursor is at])", followed
by the User command "continue".

Click K to 'attach'.

Click L to animate.
... This would cause a slow-motion run ("steps" with
05-second sleeps), displaying the constantly-changing
values of statement status messages from the debuggee,
changing the text box and highlighting the current position (again this
comes from statement status messages), and updating the watchlist
(by selecting from VARIABLES). Snazzy effects would
include turning a watchlist item red when its value
changes, increasing a statement's font size when it
has been executed many times, and slowing down when
there are errors in execution (seen by selecting
from DIAGNOSTICS).

Click M to repeat a history.
... This would involve storing the last set of
User commands, and re-running them after re-starting.

Click N to get information about what's at the cursor.
... If it's over a variable name, display the value
and data type (by selecting from VARIABLES). If it's
over a statement, show how many times the statement
was hit (by selecting from STATEMENTS_EXECUTED).

Click O to bring up a dialog box to enter SQL statements.
... Debugger Connection can do whatever the user wants, as
long as it avoids anything that's locked by Debuggee Connection.

Or, just as DDD allows GDB input, go to the
bottom of the screen and enter any of the
items mentioned in the User Commands section.

Appendix: Feature requests for the server
-----------------------------------------

This document has described a debugger that would
work with MySQL 5.5 or an equivalent. It's possible
to change some of the techniques so that we depend
on features that are only available in an enhanced
variant of the server, such as MariaDB.
The enhanced variant would have any or all of:

Message passing, so that Debugger Connection could send
to Debuggee Connection without the "@@init_connect" tricks
that are currently in the proposition for the
interconnection communication. Or, don't require
that a function must lock tables when the tables
have engine=memory and they're in a database that
isn't being replicated.

Getting a token list as a result set from an SQL statement.

Allowing dynamic SQL within a function.

Allowing dynamic SQL to refer to declared variables.

Allowing a procedure to create a procedure.

Redirecting so that, for example, activations of trigger
"test.x" automatically go to trigger "xxxmdbug000Tx".

GET DIAGNOSTICS as in MySQL 5.6.

Automatically disabling replication for xxxmdbug databases.

Bug#1118 Allow multiple concurrent locks with GET_LOCK()
Bug#14487 Comments are being ripped out of SP's during restore.
Bug#17034 Error messages from stored routines should name their source
Bug#20307 Request SHOW command support for User-Defined Variables
Bug#22012 Store procedure: support LEAVE without label
Bug#39791 MySQL needs a SHOW PREPARED STATEMENTS report
Bug#48379 Cursors on SHOW are not working
Bug#58342 ROUTINES.ROUTINE_DEFINITION removes double quotes
Bug#61261 lock tables information