WL#5291: MySQL Install / Upgrade script format

The content of this Work Log describes the fix that is necessary to resolve:
- BUG#51828 mysql_upgrade fails to build on Windows: compiler limit: string
exceeds 65535 B
- BUG#45194 mysql_upgrade deletes existing data in performance_schema
database/schema

The problem here is that the bug is not a local issue, but rather a limitation
of the current design of the tooling chain that got exposed with recent changes 
made in the install / upgrade scripts.

Important note
==============

Making any change to the install/upgrade scripts will:
- cause a build break in 5.5
- already caused a build break in 6.0, where some changes had to be removed
because the magic limit of a 64K string will be reached.

So, because of this, creating any table,
and in particular any performance schema table,
will cause a build break if this issue is not fixed.

As a result, WL#5291 is a prerequisite for any further
performance schema task that involves new tables.

WL#5291 is also a prerequisite for Roles, because grant tables are likely
to change.

1) Current situation
--------------------

1.1) Installing a new MySQL database instance.

When installing a new MySQL database instance, the content of this script is 
executed:
./scripts/mysql_system_tables.sql

1.2) Upgrading an existing MySQL instance.

When upgrading an existing MySQL database instance, the content of these scripts 
is executed:
./scripts/mysql_system_tables.sql
./scripts/mysql_system_tables_fix.sql

2) Problem
----------

2.1) Format of mysql_system_tables.sql

The format of ./scripts/mysql_system_tables.sql
is very primitive: a query *must* fit on 1 (one) line.

This leads to some lines in this file being ridiculously long: 2855 characters.

Source code in this format is hard to maintain, because:
- it can not be even read properly in an editor,
- it is harder for a developer to write or modify,
- diff and patch are likely to fail,
- sending the code or code patches by email will cause formatting errors
and every minor line wrap introduced by accident causes a bug.

The problem is that the script is used both:
- directly by mysqld --bootstrap on install,
- indirectly by the mysql_upgrade client on upgrade.

The root cause for this format is due to the limitation of
./mysqld --bootstrap

As an example of problems caused by the file format,
a developer as a choice of either writing a query on 1 line,
or work around this limitation as in:

SET @l1="CREATE TABLE performance_schema.COND_INSTANCES(";
SET @l2="NAME VARCHAR(128) not null,";
SET @l3="OBJECT_INSTANCE_BEGIN BIGINT not null";
SET @l4=")ENGINE=PERFORMANCE_SCHEMA;";

SET @cmd=concat(@l1,@l2,@l3,@l4);

Where the original intent was simply:

SET @cmd="CREATE TABLE performance_schema.COND_INSTANCES("
  "NAME VARCHAR(128) not null,"
  "OBJECT_INSTANCE_BEGIN BIGINT not null"
  ")ENGINE=PERFORMANCE_SCHEMA;";

2.2) Format of mysql_system_tables_fix.sql

The file mysql_system_tables_fix.sql is a regular sql file,
in which a query can be expressed using multiple lines.
The query delimiter is the expected ';' character.

While this seems reasonable, the content of both files:
- (a) ./scripts/mysql_system_tables.sql
- (b) ./scripts/mysql_system_tables_fix.sql
is concatenated into:
- (c) ./scripts/mysql_fix_privilege_tables.sql
which is later used as input by comp_sql to generate:
- (d) ./scripts/mysql_fix_privilege_tables_sql.c
which is compiled into the mysql_upgrade binary.

The problem here is that (a) and (b) have different formats,
and therefore there is no way to delimit queries in (c).

2.3) Format of mysql_fix_privilege_tables_sql.c

Because of this, (d) is generated as a huge string that contains
the entire script, expressed as one huge SQL multi query.

This in turn leads to the following problems:
- (e) Build failures, when the total size of the script exceeds 65535 
characters.
- (f) Sending one multi query to the server in mysql_upgrade.

(e) is BUG#51828

(f) is another point of failure in the system, and is weak, because:
- the total size of the upgrade script may not fit into one paquet,
- sending queries 1 by 1 during an install but all at once during an upgrade
  is likely to cause changes of behavior and bugs.

It would be much more robust for mysql_upgrade to send queries 1 by 1,
to match exactly what is executed during an install.

Because of all the issues listed here,
the only proper way to fix BUG#51828 is to revise the entire chain.

3) Proposed solution
--------------------

3.1) Change the format of mysql_system_tables.sql

Extend the existing format

a) A single line, terminated by a ';' character.

To become

b) Zero or more lines, not terminated by a ';',
followed by a line terminated by a ';'.

This is compatible with:
delimiter ;
query line 1
query line 2
query line 3;

Limitations

The end of request marker is a line ended with ';', with possible end of line
white space. Nothing else is supported.

The following is not supported by the bootstrap syntax:
select "can not have comments" ; # here
select "can not have comments" ; -- here

Rationale:

The format needs to be extended to be more easy to use.
However, the more flexible the format is, the more complex mysqld --bootstrap
will be. The goal is *not* to implement a fully featured mysql client embedded
into mysqld --bootstrap, so the solution proposed here is only limited.

Supporting a subset of the mysql client syntax will allow to copy and paste
code from mysql_system_tables.sql into a mysql session for testing,
so a *subset* instead of yet another syntax has some value.

Supporting the already familiar ';' delimiter will allow to use a
MySQL client to source a bootstrap script directly, if needed.

Implementing a full delimiter syntax with an arbitrary delimiter is out
of scope, here the delimiter is hard coded to the default client delimiter ';'.

Implementing a full parsing to properly handle end of line comments,
which in turn forces to parse the content of strings because of things like:
SELECT "/* oops", "*/ another oops"
SELECT "# oops"
SELECT "oops ;",
etc, is out of scope.

3.2) Change mysqld --bootstrap

Change the server implementation of the bootstrap code
to parse the new format introcuded in 3.1.

3.3) Change the format of mysql_fix_privilege_tables_sql.c

In particular, instead of generating 1 string for the entire script:

const char* mysql_fix_privilege_tables={
"file content"
};

the data generated contains 1 string per query:

const char* mysql_fix_privilege_tables[]= {
"query 1",
"query 2",
"...",
"last query",
NULL
};

3.4) Change comp_sql

Change comp_sql to parse the format described in 3.1,
and generate the data described in 3.3.

3.5) Change mysql_upgrade

mysql_upgrade needs to adjust for the change in mysql_fix_privilege_tables.