General Resources

Mentor:

Brandon Nesterenko

Granted to PUBLIC

Implement the standard behavior for

GRANT xxx TO PUBLIC;
REVOKE xxx FROM PUBLIC;

Also, this statement is supposed to work:

SHOW GRANTS FOR PUBLIC;

And these should not

CREATE ROLE PUBLIC;
DROP ROLE PUBLIC;
SET ROLE PUBLIC;
GRANT PUBLIC TO xxx;
REVOKE PUBLIC FROM xxx;

Note that

SHOW GRANTS FOR xxx;

should not list roles and privileges granted to PUBLIC (unless granted to xxx too), but

SHOW GRANTS;

should, arguably, list them.

Details:

Mentor:

Details:

MDEV-5215

Mentor:

Oleksandr Byelkin

Control over memory allocated for SP/PS

SP/PS (Stored Procedures / Prepared Statements) allocates memory till the PS cache of SP will be destroyed. There is no way to see how many memory allocated and if it grows with each execution (first 2 execution can lead to new memory allocation but not more)Task minimum: Status variables which count the memory used/allocated for SP/PS by thread and/or for the server.Other ideas:

Automatic stop allocation in debugging version after second execution and call exception on attempt.
Information schema by threads and SP/PS with information about allocated and used memory

Information can be collected in MEM_ROOTs of SP/PS. Storing info about status of mem_root before execution then checking after new allocated memory can be found. MEM_ROOT can be changed to have debug mode which make it read only which can be switched on after second execution.

Details:

Mentor:

Details:

Mentor:

Oleksandr Byelkin

Add JSON_NORMALIZE function to normalize JSON values

Background is this question on stackexchange:mariadb-compare-json The task is to provide a function that can be used to compare 2 json documents for equality, then name can be e.g JSON_NORMALIZE JSON_COMPACT already takes care of removing spaces, but this is not sufficient. Keys need to be (recursively) sorted , and if spaces are removed, then documents can be compared as binary strings.

Details:

Mentor:

Details:

MDEV-16375

Mentor:

Vicențiu Ciorbaru

Add linear regression functions

The following linear regression functions exist in a number of other DBMSs, such as Oracle, PostgreSQL:

REGR_SLOPE
    REGR_INTERCEPT
    REGR_COUNT
    REGR_R2
    REGR_AVGX
    REGR_AVGY
    REGR_SXX
    REGR_SYY
    REGR_SXY

Some have also been added to Columnstore.

Details:

Mentor:

Details:

MDEV-17467

Mentor:

Nikita Malyavin

Create utility to parse frm files and print their DDL

It would be useful if MariaDB had a utility that was able to parse frm files and print the DDL associated with the table. For example, it would be useful for users who performed a partial backup with mariadb-backup:partial-backup-and-restore-with-mariadb-backup But they forgot to also backup the table DDL, so they can't restore the tables using the following process:innodb-file-per-table-tablespaces mysqlfrm is a tool that already exists that does similar things:mysqlfrm.py But it doesn't seem very user-friendly. It needs to be able to contact the local MariaDB server, and it also needs to be able to spawn a server instance, and it seems to need to be able to create a bunch of files during this process. e.g.:

[ec2-user@ip-172-30-0-249 ~]$ cd /tmp
[ec2-user@ip-172-30-0-249 tmp]$ sudo mysqlfrm --server=root:@localhost:3306 /var/lib/mysql/db1/tab.frm --port=12345 --user=mysql
# Source on localhost: ... connected.
# Spawning server with --user=mysql.
# Starting the spawned server on port 12345 ... done.
# Reading .frm files
#
# Reading the tab.frm file.
#
# CREATE statement for /var/lib/mysql/db1/tab.frm:
#

CREATE TABLE `db1`.`tab` (
  `id` int(11) NOT NULL,
  `str` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#...done.

Details:

Mentor:

Details:

MDEV-18827

Mentor:

Vicențiu Ciorbaru

JSON_DETAILED output unnecessarily verbose

JSON_DETAILED function (json_detailed ) is described as

Represents JSON in the most understandable way emphasizing nested structures.

We now got a use case for it: Optimizer Trace output. Optimizer trace is too large to be copied in full, instead we use expressions like

SELECT 
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) 
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Our experience is that JSON_DETAILED has some room for improvement when it comes to the quality of automatic JSON formatting. Example:

source mdev19160-data.sql
mysql> select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200\G
*************************** 1. row ***************************
JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')): [

    {
        "range_scan_alternatives": 
        [

            {
                "index": "a_b",
                "ranges": 
                [
                    "2 <= a <= 2 AND 4 <= b <= 4"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": true,
                "rows": 1,
                "cost": 1.1752,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]

Things to note:

empty lines at the start (right before/after the "range_scan_alternatives")
"analyzing_index_merge_union":[] occupies 3 lines where one would be sufficient.
the same goes for "ranges"

One can look at the JSON pretty-printer that is used by EXPLAIN FORMAT=JSON and optimizer trace. It produces a better result (but it has room for improvement, too.) Extra: in MySQL, the function is called JSON_PRETTY. We should add ability to use this name as an alias.

Details:

Mentor:

Details:

MDEV-19160

Mentor:

Vicențiu Ciorbaru

Histograms: use JSON as on-disk format

Currently, histograms are stored as array of 1-byte bucket bounds (SINGLE_PREC_HB) or 2-byte bucket bounds (DOUBLE_PREC_HB). The table storing the histograms supports different histogram formats but limits them to 256 bytes (hist_size is tinyint).

CREATE TABLE mysql.column_stats (
  min_value varbinary(255) DEFAULT NULL, 
  max_value varbinary(255) DEFAULT NULL, 
  ...
  hist_size TINYINT UNSIGNED, 
  hist_type ENUM('SINGLE_PREC_HB','DOUBLE_PREC_HB'), 
  histogram varbinary(255), 
  ...

This prevents us from supporting other kinds of histograms. The first low-hanging fruit would be to store the histogram bucket bounds precisely (like MySQL and PostgreSQL do, for example). The idea of this MDEV is to switch to JSON as storage format for histograms. If we do that, it will:

Improve the histogram precision
Allow the DBAs to examine the histograms
Enable other histogram types to be collected/used. h2. Milestone-1: Let histogram_type have another possible value, tentative name "JSON" when that is set, let ANALYZE TABLE syntax collect a JSON "histogram"

{ "hello":"world"}

that is, the following should work:

SET histogram_type='json';
ANALYZE TABLE t1 persisent FOR ALL;
SELECT histogram FROM mysql.column_stats WHERE TABLE_NAME='t1' ;

this should produce {"hello":"world"}. h2. Milestone-2: produce JSON with histogram(). &#xNAN;()- the exact format is not specified, for now, print the bucket endpoints and produce output like this:

[
  "value1",
  "value2",
  ...
]

Milestone-2, part#2: make mysql.column_stats.histogram a blob. h2. Milestone-3: Parse the JSON back into an array Figure out how to use the JSON parser. Parse the JSON data produced in Milestone-2 back. For now, just print the parsed values to stderr. (Additional input provided on Zulip re parsing valid/invalid JSON histograms) h2. Milestone-4: Make the code support different kinds of Histograms Currently, there's only one type of histogram. smaller issue: histogram lookup functions assume the histogram stores fractions, not values. bigger issue: memory allocation for histograms is de-coupled from reading the histograms. See alloc_statistics_for_table, read_histograms_for_table. The histogram object lives in a data structure that is bzero'ed first and then filled later (IIRC there was a bug (fixed) where the optimizer attempted to use bzero'ed histogram) Can histograms be collected or loaded in parallel by several threads? This was an (unintentional?) possibility but then it was disabled (see TABLE_STATISTICS_CB object and its use) h3. Step #0: Make Histogram a real class Here's the commit:3ac32917ab6c42a5a0f9ed817dd8d3c7e20ce34d h3. Step 1: Separate classes for binary and JSON histograms Need to introduce

class Histogram -- interface, no data members.
class Histogram_binary : public Histogram
class Histogram_json : public Histogram

and a factory function

Histogram *create_histogram(Histogram_type)

for now, let Histogram_json::point_selectivity() and Histogram_json::range_selectivity() return 0.1 and 0.5, respectively. h3. Step 2: Demonstrate saving/loading of histograms Now, the code already can:

collect a JSON histogram and save it.
when loading a histogram, figure from histogram_type column that this is JSON histogram being loaded, create Histogram_json and invoke the parse function. Parse function at the moment only prints to stderr. However, we should catch parse errors and make sure they are reported to the client. The test may look like this:

INSERT INTO mysql.column_stats VALUES('test','t1','column1', .... '[invalid, json, data']);
FLUSH TABLES;
# this should print some descriptive test
--error NNNN
SELECT * FROM test.t1;

h2. Milestone-5: Parse the JSON data into a structure that allows lookups. The structure is

std::vector<std::string>

and it holds the data in KeyTupleFormat (See the comments for reasoning. There was a suggestion to use in_vector (This is what IN subqueries use) but it didn't work out) h2. Milestone 5.1 (aka Milestone 44) Make a function to estimate selectivity using the data structure specified in previous milestone. h2. Make range_selectivity() accept key_range parameters. (currently, they accept fractions, which is only suitable for binary histograms) This means Histogram_binary will need to have access to min_value and max_value to compute the fractions.

Details:

Mentor:

Details:

MDEV-21130

Mentor:

Sergei Petrunia

make my_vsnprintf to use gcc-compatible format extensions

my_vsnprintf() is used internally in the server as a portable printf replacement. And it's also exported to plugins as a service. It supports a subset of printf formats and three extensions:

%s` means that a string should be quoted like an identifier
%b means that it's a binary string, not zero-terminated; printing won't stop at \0, so one should always specify the field width (like %.100b)
%M is used in error messages and prints the integer (errno) and the corresponding strerror() for it

gcc knows printf formats and check whether actual arguments match the format string and issue a warning if they don't. Unfortunately there seems to be no easy way to teach gcc our extensions, so for now we have to disable printf format checks. An better approach would be to use gcc compatible format extensions, like Linux kernel does. We should migrate to a different syntax for our extensions

%sI to mean "print as an identifier"
%sB to mean "print a binary string"
%uE to mean "print an errno"
%sT to put a "..." as truncation indicator

old formats can still be supported or they can be removed and in the latter case the major version of the service should be increased to signal an incompatible change. All error messages and all usages of my_vsnprintf should be changed to use the new syntax and gcc printf format checks should be enabled.

Details:

Mentor:

Details:

MDEV-21978

Mentor:

Sergei Golubchik

Add JSON_EQUALS function to check JSON equality

JSON_CONTAINS can be used to test for JSON object equality in some cases, but we seem to lack a clear JSON_EQUALS function.

Details:

Mentor:

Details:

MDEV-23143

Mentor:

Vicențiu Ciorbaru

Concurrent multi-reader, multi-writer buffer for IO_CACHE

IO_CACHE has basically three read/write modes: only read, only write, and a sequential read/write FIFO mode SEQ_READ_APPEND. While some performance-sensitive places, like replication slave thread, use SEQ_READ_APPEND, that may be a bottleneck. since reads and writes are sequential (and co-sequential i.e. reads and writes block each other). The task is to implement a non-blocking mode or multi-reader, multi-writer use-case through a concurrent ring buffer implementation. h2. Possible approaches h3. Lock-free n-consumer, m-producer ring buffer This implementation requires limiting a number of simultaneous accessors and reserving slots for them. Lock-free implementations can contain busy waits, but no locks, except when a number of consumers or producers is exceeded. This can be controlled by a semaphore with a capacity of a number of cores. This is an ideal way, but can be an overhaul because of complicated busy loops and slot management. This is also hard because writes can be bigger than a buffer. See buffer excess. h3. Simple rwlock-based non-blocking approach The bottleneck basically occurs because SEQ_READ_APPEND blocks the whole time during buffer copy. We can avoid it by moving the pointers first, thus allocating a place for copying, and then make a copy from/to the buffer without a lock. rwlock will be used to access the pointers, i.e. reads access IO_CACHE::end_of_file with read lock to make ensure the borders, but writers access it with write lock. h2. Buffer excess Excesses make things work sequential. When the buffer is full, a separate write buffer is created. When the write buffer is full, a flush happens. Flushes wait for all writers to finish first, then lock the write buffer for flushing. The read buffer can be flushed in a more relaxed way: no need to need to lock for flushing, but we have to lock for buffer allocation and wait for all writers. Waiting for writers can be done with another rwlock. h2. Single-readerness The real-world cases are mostly single-consumer, and it is essential for IO_CACHE: it is variable-lengthed, and has no underlying data format, so the reader always has to make at least two sequential reads (one to read size and another to read the body) Single-readerness considerations can relax some conditions and ease the implementation h2. io_cache_reserve api We can add a function to reserve the space to writing for the case of writing big objects (both bigger then the write cache and smaller then the latter, but big enough to not fit to the external buffer), for the cases like copying one cache to another. The function should return future-like object, since we have to notify IO_CACHE back that the writing is finished (to make flush for example)

Details:

Mentor:

Details:

MDEV-24676

Mentor:

Nikita Malyavin

Custom formatting of strings in MariaDB queries

Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved. It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification. A great example for such a function is the classic C printf function, which, in this context, would look something like:SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees; But it doesn't necessarily need to look this way, an alternative syntax could be Python-ish, which would leverage the fact that the server already knows the datatype of each field used in the formatting scheme:SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table; In that syntax one passes formatting options within the curly braces:

-- Print 'arg1: col1, arg2: col2'  where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) FROM table;

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Details:

Mentor:

Details:

MDEV-25015

Mentor:

Sergei Golubchik

Add autocompletion capabilities to the MariaDB Jupyter kernel

As part of the Jupyter Messaging protocol, the Jupyter frontend sends a complete_request message to the MariaDB kernel when the user invokes the code completer in a Jupyter notebook. This message is handled in the do_complete function from the MariaDBKernel class. In simpler words, whenever the user hits the key shortcut for code autocompletion in a notebook, the MariaDB kernel's do_complete function is called with a number of arguments that help the kernel understand what the user wants to autocomplete. So the autocompletion infrastructure in the MariaDB kernel is already kindly provided by Jupyter, we only need to send back to Jupyter a list of suggestions based on the arguments that do_complete receives :-). Ideally we should aim to enable at least database, table and column name completion and also SQL keyword completion. But no worries, there are plenty of possibilities to extend the functionality even more if the accepted student turns out to be very productive :D

Details:

Mentor:

Details:

Project Issue

Mentor:

Robert Bindar

Implement interacting editing of result sets in the MariaDB Jupyter kernel

At this moment the MariaDB kernel is only capable of getting the results sets from the MariaDB client in HTML format and packing them in a Jupyter compatible format. Jupyter then displays them in notebooks like it would display Python Pandas dataframes. Sure, the users can easily write SQL code to modify the content of a table like they would write in a classical command line database client. But we want to go a bit further, we would love to have the capability to edit a result set returned by a SELECT statement (i.e. double click on table cells and edit) and have a button that users can press to generate a SQL statement that will update the content of the table via the MariaDB server. Apart from interacting with the Jupyter frontend for providing this UI capability, we also have to implement a field integrity functionality so that we make sure users can't enter data that is not compatible with the datatype of the column as it is seen by the MariaDB server. The project should start with a fair bit of research to understand how we can play with the Jupyter Messaging protocol to create the UI functionality and also to check other Jupyter kernels and understand what's the right and best approach for tackling this.

Details:

Mentor:

Details:

Project Issue

Mentor:

Andreia Hendea

Make the MariaDB Jupyter kernel capable of dealing with huge SELECTs

Currently the MariaDB kernel doesn't impose any internal limits for the number of rows a user can SELECT in a notebook cell. Internally the kernel gets the result set from MariaDB and stores it in a pandas DataFrame, so users can use it with magic commands to chart data. But this DataFrame is stored in memory, so if you SELECT a huge number of rows, say 500k or 1M, it's probably not a very good idea to create such a huge DataFrame. We tested with 500k rows, and the DataFrame itself is not the biggest problem, it consumed around 500MB of memory. The problem is the amount of rows the browser needs to render, for 500k rows the browser tab with the notebook consumes around 2GB of memory, so the Jupyter frontend (JupyterLab, Jupyter Notebook) slows down considerably. A potential solution is to introduce a two new config options which would specify:

a limit for the number of rows the Jupyter notebook should render, a reasonable default value for this could 50 rows for instance (display_max_rows)
a limit for each SELECT statement, limit_max_rows, that the kernel would use to determine whether it should store the result set in memory in a DataFrame or store the result set on disk. A reasonable default value might be 100k rows.

The trickiest part of the project though is that, once the kernel writes a result set on disk, the charting magic commands need to detect that the data is not in memory, it is on disk, and they should find a smart mechanism for generating the chart from the disk data without loading the entire data in memory (which would defeat the whole purpose of the project). This might involve finding a new Python plotting library (instead of current matplotlib) that can accomplish the job.

Details:

Mentor:

Details:

Project Issue

Mentor:

Vlad Bogolin

Suggest a Task

Do you have an idea of your own, not listed above? Do let us know!

_{This page is licensed: CC BY-SA / Gnu FDL}

Google Summer of Code 2020

We participated in the Google Summer of Code 2020. The MariaDB Foundation believes we are making a better database that remains application compatible with MySQL. We also work on making LGPL connectors (currently C, ODBC, Java) and on MariaDB Galera Cluster, which allows you to scale your reads & writes. And we have MariaDB ColumnStore, which is a columnar storage engine, designed to process petabytes of data with real-time response to analytical queries.

Where to Start

Please join us on Zulip and on IRC to mingle with the community. Don't forget to subscribe to maria-developers@lists.launchpad.net (this is the main list where we discuss development).

A few handy tips for any interested students who are unsure which projects to choose:Blog post from former GSoC student & mentor

To improve your chances of being accepted, it is a good idea to submit a pull request with a bug fix to the server.

Also see the List of beginner friendly issues and issues labelled gsoc20 from the MariaDB Issue Tracker.

List of Tasks

MariaDB Server: Optimizer

Evaluate subquery predicates earlier or later depending on their SELECTIVITY

(Based on conversation with Igor) There are a lot of subquery conditions out there that are inexpensive to evaluate and have good selectivity. If we just implement MDEV-83, we may get regressions. We need to take subquery condition's selectivity into account. It is difficult to get a meaningful estimate for an arbitrary, correlated subquery predicate. One possible solution is to measure selectivity during execution and reattach predicates on the fly. We don't want to change the query plan all the time, one way to dynamically move items between item trees is to wrap them inside Item_func_trig_cond so we can switch them on and off.

Details:

Mentor:

Details:

MDEV-383

Mentor:

Igor Babaev

Histograms with equal-width bins in MariaDB

Histograms with equal-width bins are easy to construct using samples. For this it's enough to look through the given sample set and for each value from it to figure out what bin this value can be placed in. Each bin requires only one counter. Let f be a column of a table with N rows and n be the number of samples by which the equal-width histogram of k bins for this column is constructed. Let after looking through all sample rows the counters created for the histogram bins contain numbers c[1],..,c[k]. Then m[i]= c[i]/n * 100 is the percentage of the rows whose values of f are expected to be in the interval

(max(f)-min(f))/k *(i-1), max(f)-min(f))/k *i-1).

It means that if the sample rows have been chosen randomly the expected number of rows with the values of f from this interval can be approximated by the number m[i]*/100 * N. To collect such statistics it is suggested to use the following variant of the ANALYZE TABLE command:

ANALYZE FAST TABLE tbl [ WITH n ROWS ] [SAMPLING p PERCENTS ]
   PERSISTENT FOR COLUMNS (col1 [IN RANGE r] [WITH k INTERVALS],...)

Here:

'WITH n ROWS' provides an estimate for the number of rows in the table in the case when this estimate cannot be obtained from statistical data.
'SAMPLING p PERCENTS' provides the percentage of sample rows to collect statistics. If this is omitted the number is taken from the system variable samples_ratio.
'IN RANGE r' sets the range of equal-width bins of the histogram built for the column col1. If this is omitted then and min and max values for the column can be read from statistical data then the histogram is built for the range [min(col1), max(col1)]. Otherwise the range [MIN_type(col1), MAX_type(col1) is considered]. The values beyond the given range, if any, are also is taken into account in two additional bins.
WITH k INTERVALS says how many bins are included in the histogram. If it is omitted this value is taken from the system variable histogram_size.

Details:

Mentor:

Details:

MDEV-12313

Mentor:

Vicentiu Ciorbaru

Add FULL OUTER JOIN to MariaDB

Add support for FULL OUTER JOIN sql_join_full.asp One of the way how to implement is to re-write the query

SELECT t1.*, t2.* FROM t1 FULL OUTER JOIN t2 ON P(t1,t2)

into the following union all:

SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON P(t1,t2) 
UNION ALL 
SELECT t1.*,t2.* FROM t2 LEFT OUTER JOIN t1 ON P(t1,t2) WHERE t1.a IS NULL

Here t1.a is some non-nullable column of t1 (e.g. the column of single column primary key).

Details:

Mentor:

Details:

MDEV-13648

Mentor:

Igor Babaev

Recursive CTE support for UPDATE (and DELETE) statements

CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);
 
     WITH RECURSIVE prev AS (
     SELECT * FROM tree WHERE ParentNode IS NULL
     UNION
     SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 AS Depth, CONCAT(p.Lineage, t.ParentNode, '/')
     FROM tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;

     WITH RECURSIVE prev AS (
     SELECT * FROM tree WHERE ParentNode IS NULL
     UNION
     SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 AS Depth, CONCAT(p.Lineage, t.ParentNode, '/')
     FROM prev p JOIN tree t ON t.ParentNode = p.Node
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node; 

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7

supported in MySQL-8.0 and MSSQL

Details:

Mentor:

Details:

MDEV-18511

Mentor:

Igor Babaev

MariaDB Server: others

Support for GTID in mysqlbinlog

The mysqlbinlog client program needs to be updated to support GTID. Here is a suggested list of things to be done:

The --start-position and --stop-position options should be able to take GTID positions; or maybe there should be new --start-gtid and --stop-gtid options. Like --start-gtid=0-1-100,1-2-200,2-1-1000.
A GTID position means the point just after that GTID. So starting from GTID 0-1-100 and stopping at GTID 0-1-200, the first GTID output will probably be 0-1-101 and the last one 0-1-200. Note that if some domain is not specified in the position, it means to start from the begining, respectively stop immediately in that domain.
Starting and stopping GTID should work both with local files, and with --read-from-remote-server. For the latter, there are a couple of extra things that need doing in the master-slave protocol, see get_master_version_and_clock() in sql/slave.cc.
At the end of the dump, put these statements, to reduce the risk of those session variables incorrectly spilling into subsequent statements run in the same session:

SET session.server_id = @@global.server_id,
       session.gtid_domain_id=@@global.gtid_domain_id;

Details:

Mentor:

Details:

Mentor:

Andrei Elkin

connection encryption plugin support

As a follow-on to MDEV-4691 we would like GSSAPI encryption (in addition to authentication) support in MariaDB. I am told that the current plan is to create a plugin interface and then we can build GSSAPI encryption on top of that, so here is a ticket for that. From having written GSSAPI for the internal interface, there were a couple things I would like to see in the plugin encryption interface. First, GSSAPI is weird in that it does authentication before encryption (TLS/SSL are the other way around, establishing an encrypted channel and then doing authentication over it). Of course support for this is needed, but more importantly, packets must be processed in a fully serialized fashion. This is because encrypted packets may be queued while one end of the connection is still finishing up processing the authentication handshake. One way to do this is registering "handle" callbacks with connection-specific state, but there are definitely others. Additionally, for whatever conception there ends up being of authentication and encryption, it needs to be possible to share more data than just a socket between them. The same context will be used for authentication and encryption, much as an SSL context is (except of course we go from authentication to encryption and not the other way around). This ties into an issue of dependency. If authentication plugins are separate entities from encryption plugins in the final architecture, it might make sense to do mix-and-match authentication with encryption. However, there are cases - and GSSAPI is one - where doing encryption requires a certain kind of authentication (or vice versa). You can't do GSSAPI encryption without first doing GSSAPI authentication. (Whether or not it's permitted to do GSSAPI auth->encryption all over a TLS channel, for instance, is not something I'm concerned about.) Finally, encrypted messages are larger than their non-encrypted counterparts. The transport layer should cope with this so that plugins don't have to think about reassembly, keeping in mind that there may not be a way to get the size of a message when encrypted without first encrypting it. It's unfortunately been a little while since I wrote that code, but I think those were the main things that we'll need for GSSAPI. Thanks!

Details:

Mentor:

Details:

MDEV-9090

Mentor:

Vicențiu Ciorbaru

Aggregate Window Functions

With a few exceptions, most native aggregate functions are supported as window functions.aggregate-functions-as-window-functions In MDEV-7773, support for creating of custom aggregate functions was added. This task proposes to extend that feature and allow custom aggregate functions to be used as window functions An example of a creating a custom aggregate function is given below:

CREATE AGGREGATE FUNCTION agg_sum(x INT) RETURNS DOUBLE
BEGIN
  DECLARE z DOUBLE DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN z;
  LOOP
    FETCH GROUP NEXT ROW;
    SET z = z + x;
  END LOOP;
END|

This functions can be used in the following query:

CREATE TABLE balances (id INT, amount INT);
INSERT INTO balances VALUES (1, 10), (2, 20), (3, 30);
 
SELECT agg_sum(amount) FROM balances;

After this task is complete the following must also work:

SELECT agg_sum(amount) OVER (ORDER BY id);

Details:

Mentor:

Details:

MDEV-11263

Mentor:

Varun Gupta

Improve mysqltest language

mysqltest has a lot of historical problems:

ad hoc parser, weird limitations
commands added as needed with no view over the total language structure
historical code issues (e.g. casts that become unnecessary 10 years ago) etc

A lot can be done to improve it. Ideas

control structures, else in if, break and continue in while, for (or foreach) loop
proper expression support in let, if, etc
rich enough expressions to make resorting to sql unnecessary in most cases
remove unused and redundant commands (e.g. system vs exec, query_vertical vs vertical_results ONCE)
remove complex commands that do many sql statements under the hood, if they can be scripted, e.g. sync_slave_with_master
remove over-verbose treatment of rpl test failures
scoped variables
parameters for the source command
remove dead code

Details:

Mentor:

Details:

MDEV-12130

Mentor:

Sergei Golubchik

Implement multiple-table UPDATE/DELETE returning a result set

A multiple-table UPDATE first performs join operations, then it updates the matching rows. A multiple-table UPDATE returning a result set does the following:

first performs join operations
for each row of the result of the join it calculates some expressions over the columns of the join and forms from them a row of the returned result set
after this it updates the matching rows

A multiple-table DELETE first performs join operations, then it deletes the matching rows. A multiple-table DELETE returning a result set does the following:

first performs join operations
for each row of the result of the join it calculates some expressions over the columns of the join and forms from them a row of the returned result set
after this it deletes the matching rows

Details:

Mentor:

Details:

MDEV-12326

Mentor:

Igor Babaev

sort out the compression library chaos

As MariaDB is getting more storage engines and as they're getting more features, MariaDB can optionally use more and more compression libraries for various purposes. InnoDB, TokuDB, RocksDB — they all can use different sets of compression libraries. Compiling them all in would result in a lot of run-time/rpm/deb dependencies, most of which will be never used by most of the users. Not compiling them in, would result in requests to compile them in. While most users don't use all these libraries, many users use some of these libraries. A solution could be to load these libraries on request, without creating a packaging dependency. There are different ways to do it

hide all compression libraries behind a single unified compression API. Either develop our own or use something like Squash. This would require changing all engines to use this API
use the same approach as in server services — create a service per compression library, a service implementation will just return an error code for any function invocation if the corresponding library is not installed. this way — may be — we could avoid modifying all affected storage engines

Details:

Mentor:

Details:

MDEV-12933

Mentor:

Sergei Golubchik

Control over memory allocated for SP/PS

Task minimum:

Status variables which count the memory used/allocated for SP/PS by thread and/or for the server.

Other ideas:

Automatic stop allocation in debvugging version after second execution and call exception on attempt.
Information schema by threads and SP/PS with information about allocated and used memory

Details:

Mentor:

Details:

Mentor:

Oleksandr Byelkin

MariaDB ColumnStore

Full DECIMAL support in ColumnStore

MariaDB ColumnStore supports DECIMAL with some limitations:

We do not support the full DECIMAL range that is in MariaDB
In several places in the code we convert the DECIMAL to DOUBLE during execution therefore losing precision Implementing this will likely require the following:

Implementation of methods to handle MariaDB's DECIMAL format
Support for a longer than 8-byte numeric column type (there is an InfiniDB tree with work for this already)
Modification of the primitives processor for the math
Modification of the function expression processor to handle the new type
Version upgrade support for DECIMAL from the current form to the new form

Details

Mentor:

Details

List of beginner friendly bugs

Mentor:

Andrew Hutchings

Replace glibc with google's re2 for regex processing

CS as of 1.4.2 relies on glibc for regex processing. We need to replace glibc with re2 for LIKE, REGEX and other facilities that affects performance.

Identify places with glibc regex functions invocations
Pick the invocations that significantly affects timings of the query
Replace glibc regex calls with appropriate re2

Details

Mentor:

Details

MCOL-3778

Mentor:

Roman

Speed up RowGroup data access methods reducing level of inderection

RowGroup is the unit of the data sent around CS cluster. It is basically a set of records for fixed size data types + additional storage area for binary data, e.g. strings. Right now there are lots of RowGroup methods that access RowGroup fixed size data using extra level of inderection. Here is the example: return ((int64_t) &data[offsets[colIndex]]); This expression uses extra assembly instruction to calculate the effective address. We want to remove 'offsets[colIndex]' part of this and similar expressions in RowGroup code.

Details

Mentor:

Details

MCOL-3759

Mentor:

Roman

Parallel sorting 2nd phase

As of 1.4.1 CS uses two-phase sorting. Here are the phases:

Presort partial runs of data.
Merge the presorted partial runs produced during the 1st phase. Here is more detailed explanation of how sorting works as of 1.4.1

CS gets a portion of records data from previous steps of the query execution(RGData instance from the ring buffer of RGData-s) and produces a sorting run out of it using existing sorting class LimitedOrderBy. If the query contains LIMIT then we apply it at this phase. This allows to significantly reduce the data set cardinality. If the query contains LIMIT + OFFSET then CS builds a sorted run of the records that is up to LIMIT+OFFSET size. CS does this step in parallel dividing the whole data set into k runs where k is governed by a session variable - infinidb/columnstore_orderby_threads. At this phase CS tries to preallocate memory in QUEUE_RESERVE_SIZE batches. CS merges and sorts k presorted partial runs produced by a previous phase in a single thread. If the query contains DISTINCT keyword CS rebuilds a hash map to preserve uniqueness. We want to make 2nd phase also parallel using range partitioning of the presorted runs produced by the 1st phase. After 1st phase finishes we know the distribution of the sorting key values thus can divide the thread key values run into regions - buckets. Every 2nd phase thread takes values from corresponding region buckets (contains the same values region) from every 1st phase sorted run. Then all 2nd phase threads sorts its runs in parallel. In the end we put the sorted regions in asscending order of the key values into output stream.

Details

Mentor:

Details

MCOL-3758

Mentor:

Roman

Add a bitmap for NULL/empty data

We need a bitmap to store NULL/empty values instead of in-column values for this.

Details

Mentor:

Details

MCOL-3754

Mentor:

Anderw Hutchings

Engine independent statistics for Columnstore

CS now has a very rudimentary query optimization capabilities and we want to improve the situtation. We consider to use Server's optimizer for the purpose but the Server needs statistics namely values distribution histograms and Number of Distinct Values distribution histograms. There are different levels of complexity for the task:

implement standalone segment files reader that in the end populates both mysql.column_stats and mysql.table_stats using out of band mariadb client connection
implement ANALYZE TABLE functionality for Columnstore engine
implement ANALYZE TABLE and Histograms with equal-width bins for values distribution histograms(similar to MDEV-12313) together with NDV histograms to decrease I/O We expect to have both unit and regression tests but this is optional.

Details

Mentor:

Details

MCOL-2223

Mentor:

Roman

mcsapi needs a new read API Design

We need an ORM-style NoSQL read API to go along with the bulk write API of mcsapi. This will likely take the form of:

A reader in ExeMgr which will convert messages from mcsapi into jobs
Code in mcsapi to send/receive the messages Although ExeMgr can already receive messages with an execution plan the format is very complex and ABI breaks easily (we often send whole C++ objects). We should look at other ORM frameworks for inspiration as the API design. This task to do the design for this API.

Details:

Mentor:

Details:

MCOL-1151

Mentor:

Andrew Hutchings

Use JIT for aggregation functions

CS uses Volcano processing approach working on one value at a time. This is very inefficient way for analytics workload that usually uses lots of aggregation functions in projections, filtering or sorting. We are interested in using JIT for basic aggregation functions: sum, avg, count, min, max. The patch must compile and run a program that processes and returns the aggregation function result. We were written this description having LLVM in mind as it is widespread and has a lots of examples in the wild. I suggest to start looking at RowAggregation::addRowGroup() from ./utils/rowgroup/rowaggregation.cpp to get what it takmakees to get avg() function result. Here is the link on how to build fast a CS developer environment.

Details:

Mentor:

Details:

MCOL-2222

Mentor:

Roman

Engine independent statistics for Columnstore

implement standalone segment files reader that in the end populates both mysql.column_stats and mysql.table_stats using out of band mariadb client connection
implement ANALYZE TABLE functionality for Columnstore engine
implement ANALYZE TABLE and Histograms with equal-width bins for values distribution histograms(similar to MDEV-12313) together with NDV histograms to decrease I/O

We expect to have both unit and regression tests but this is optional.

Details:

Mentor:

Details:

MCOL-2223

Mentor:

Roman

Histograms: use JSON as on-disk format

CREATE TABLE mysql.column_stats (
  min_value varbinary(255) DEFAULT NULL, 
  max_value varbinary(255) DEFAULT NULL, 
  ...
  hist_size TINYINT UNSIGNED, 
  hist_type ENUM('SINGLE_PREC_HB','DOUBLE_PREC_HB'), 
  histogram varbinary(255), 
  ...

Details:

Mentor:

Details:

MDEV-21130

Mentor:

Sergei Petrunia

Suggest a Task

Do you have an idea of your own, not listed above? Do let us know!

_{This page is licensed: CC BY-SA / Gnu FDL}

Google Summer of Code 2019

We participated in the Google Summer of Code 2019. The MariaDB Foundation believes we are making a better database that remains application compatible with MySQL. We also work on making LGPL connectors (currently C, ODBC, Java) and on MariaDB Galera Cluster, which allows you to scale your reads & writes. And we have MariaDB ColumnStore, which is a columnar storage engine, designed to process petabytes of data with real-time response to analytical queries.

Where to Start

Please join us on Zulip and on IRC to mingle with the community. Don't forget to subscribe to maria-developers@lists.launchpad.net (this is the main list where we discuss development).

A few handy tips for any interested students who are unsure which projects to choose:Blog post from former GSoC student & mentor

To improve your chances of being accepted, it is a good idea to submit a pull request with a bug fix to the server.

List of Tasks

Loaded from the MariaDB issue tracker

MariaDB Server: Optimizer

Evaluate subquery predicates earlier or later depending on their SELECTIVITY

Details:

Mentor:

Details:

MDEV-383

Mentor:

Igor Babaev

Add support for Indexes on Expressions

An index on expression means something like

CREATE TABLE t1 (a INT, b INT, INDEX (a/2+b));
...
SELECT * FROM t1 WHERE a/2+b=100;

in this case the optimizer should be able to use an index. This task naturally splits in two steps:

add expression matching into the optimizer, use it for generated columns. Like in CREATE TABLE t1 (a int, b int, v INT GENERATED ALWAYS AS (a/2+b), INDEX (v));
support the syntax to create an index on expression directly, this will automatically create a hidden generated column under the hood

original task description is visible in the history

Details:

Mentor:

Details:

MDEV-6017

Mentor:

Sergei Golubchik

Histograms with equal-width bins in MariaDB

(max(f)-min(f))/k *(i-1), max(f)-min(f))/k *i-1).

ANALYZE FAST TABLE tbl [ WITH n ROWS ] [SAMPLING p PERCENTS ]
   PERSISTENT FOR COLUMNS (col1 [IN RANGE r] [WITH k INTERVALS],...)

Here:

'WITH n ROWS' provides an estimate for the number of rows in the table in the case when this estimate cannot be obtained from statistical data.
'SAMPLING p PERCENTS' provides the percentage of sample rows to collect statistics. If this is omitted the number is taken from the system variable samples_ratio.
'IN RANGE r' sets the range of equal-width bins of the histogram built for the column col1. If this is omitted then and min and max values for the column can be read from statistical data then the histogram is built for the range [min(col1), max(col1)]. Otherwise the range [MIN_type(col1), MAX_type(col1) is considered]. The values beyond the given range, if any, are also is taken into account in two additional bins.
WITH k INTERVALS says how many bins are included in the histogram. If it is omitted this value is taken from the system variable histogram_size.

Details:

Mentor:

Details:

MDEV-12313

Mentor:

Vicentiu Ciorbaru

Add FULL OUTER JOIN to MariaDB

Add support for FULL OUTER JOIN sql_join_full.asp One of the way how to implement is to re-write the query

SELECT t1.*, t2.* FROM t1 FULL OUTER JOIN t2 ON P(t1,t2)

into the following union all:

SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON P(t1,t2) 
UNION ALL 
SELECT t1.*,t2.* FROM t2 LEFT OUTER JOIN t1 ON P(t1,t2) WHERE t1.a IS NULL

Here t1.a is some non-nullable column of t1 (e.g. the column of single column primary key).

Details:

Mentor:

Details:

MDEV-13648

Mentor:

Igor Babaev

Recursive CTE support for UPDATE (and DELETE) statements

CREATE TABLE tree (
       `Node` VARCHAR(3),
       `ParentNode` VARCHAR(3),
       `EmployeeID` INTEGER,
       `Depth` INTEGER,
       `Lineage` VARCHAR(16)
     );

     INSERT INTO tree
       (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
     VALUES
       ('100', NULL, '1001', 0, '/'),
       ('101', '100', '1002', NULL, NULL),
       ('102', '101', '1003', NULL, NULL),
       ('103', '102', '1004', NULL, NULL),
       ('104', '102', '1005', NULL, NULL),
       ('105', '102', '1006', NULL, NULL);
 
     WITH RECURSIVE prev AS (
     SELECT * FROM tree WHERE ParentNode IS NULL
     UNION
     SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 AS Depth, CONCAT(p.Lineage, t.ParentNode, '/')
     FROM tree t JOIN prev p ON t.ParentNode = p.Node
     )
     SELECT * FROM prev;

     WITH RECURSIVE prev AS (
     SELECT * FROM tree WHERE ParentNode IS NULL
     UNION
     SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 AS Depth, CONCAT(p.Lineage, t.ParentNode, '/')
     FROM prev p JOIN tree t ON t.ParentNode = p.Node
     )
     UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node; 

You have an error IN your SQL syntax; CHECK the MANUAL that corresponds TO your MariaDB server VERSION FOR the RIGHT syntax TO USE near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' AT LINE 7

supported in MySQL-8.0 and MSSQL

Details:

Mentor:

Details:

MDEV-18511

Mentor:

Igor Babaev

Implement EXCEPT ALL and INTERSECT ALL operations

SQL Standard allows to use EXCEPT ALL and INTERSECT ALL as set operations. Currently MariaDB Server does not support them. The goal of this task is to support EXCEPT ALL and INTERSECT ALL

at syntax level - allow to use operators EXCEPT ALL and INTERSECT ALL in query expression body
at execution level - implement these operations employing temporary tables (the implementation could use the idea similar to that used for the existing implementation of the INTERSECT operation).

Details:

Mentor:

Details:

MDEV-18844

Mentor:

Igor Babaev

MariaDB Server: others

Implement UPDATE with result set

Add an UPDATE operation that returns a result set of the changed rows to the client.

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
RETURNING select_expr [, select_expr ...]

I'm not exactly sure how the corresponding multiple-table syntax should look like, or if it is possible at all. But already having it for single-table updates would be a nice feature.

Details:

Mentor:

Details:

MDEV-5092

Mentor:

Igor Babaev

Automatic provisioning of slave

Idea

The purpose of this task is to create an easy-to-use facility for setting up a new MariaDB replication slave. Setting up a new slave currently involves: 1) installing MariaDB with initial database; 2) point the slave to the master with CHANGE MASTER TO; 3) copying initial data from the master to the slave; and 4) starting the slave with START SLAVE. The idea is to automate step (3), which currently needs to be done manually. The syntax could be something as simple as

LOAD DATA FROM MASTER

This would then connect to the master that is currently configured. It will load a snapshot of all the data on the master, and leave the slave position at the point of the snapshot, ready for START SLAVE to continue replication from that point.

Implementation:

The idea is to do this non-blocking on the master, in a way that works for any storage engine. It will rely on row-based replication to be used between the master and the slave. At the start of LOAD DATA FROM MASTER, the slave will enter a special provisioning mode. It will start replicating events from the master at the master's current position. The master dump thread will send binlog events to the slave as normal. But in addition, it will interleave a dump of all the data on the master contained in tables, views, or stored functions. Whenever the dump thread would normally go to sleep waiting for more data to arrive in the binlog, the dump thread will instead send another chunk of data in the binlog stream for the slave to apply. A "chunk of data" can be:

A CREATE OR REPLACE TABLE / VIEW / PROCEDURE / FUNCTION
A range of N rows (N=100, for example). Each successive chunk will do a range scan on the primary key from the end position of the last chunk.

Sending data in small chunks avoids the need for long-lived table locks or transactions that could adversely affect master performance. The slave will connect in GTID mode. The master will send dumped chunks in a separate domain id, allowing the slave to process chunks in parallel with normal data. During the provisioning, all normal replication events from the master will arrive on the slave, and the slave will attempt to apply them locally. Some of these events will fail to apply, since the affected table or row may not yet have been loaded. In the provisioning mode, all such errors will be silently ignored. Proper locking (isolation mode, eg.) must be used on the master when fetching chunks, to ensure that updates for any row will always be applied correctly on the slave, either in a chunk, or in a later row event. In order to make the first version of this feature feasible to implement in a reasonable amount of time, it should set a number of reasonable restrictions (which could be relaxed in a later version of the feature):

Give up with an error if the slave is not configured for GTID mode (MASTER_USE_GTID != NO).
Give up with error if the slave receives any event in statement-based binlogging (so the master must be running in row-based replication mode, and no DDL must be done while the provisioning is running).
Give up with an error if the master has a table without primary key.
Secondary indexes will be enabled during the provisioning; this means that tables with large secondary indexes could be expensive to provision.

Details:

Mentor:

Details:

MDEV-7502

Mentor:

Andrei Elkin

connection encryption plugin support

Details:

Mentor:

Details:

MDEV-9090

Mentor:

Add RETURNING to INSERT

Please add a RETURNING option to INSERT. Example from PostgreSQL

postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100));
CREATE TABLE
postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id;
 id 
----
  1
(1 row)

INSERT 0 1

Inspired by:This could make it easier to write statements which work with both MariaDB and PostgreSQL. And this might improve compatibility with Oracle RDBMS.

Details:

Mentor:

Details:

MDEV-10014

Mentor:

Oleksandr Byelkin

Aggregate Window Functions

CREATE AGGREGATE FUNCTION agg_sum(x INT) RETURNS DOUBLE
BEGIN
  DECLARE z DOUBLE DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN z;
  LOOP
    FETCH GROUP NEXT ROW;
    SET z = z + x;
  END LOOP;
END|

This functions can be used in the following query:

CREATE TABLE balances (id INT, amount INT);
INSERT INTO balances VALUES (1, 10), (2, 20), (3, 30);
 
SELECT agg_sum(amount) FROM balances;

After this task is complete the following must also work:

SELECT agg_sum(amount) OVER (ORDER BY id);

Details:

Mentor:

Details:

MDEV-11263

Mentor:

Varun Gupta

True ALTER LOCK=NONE on slave

Currently no true LOCK=NONE exists on slave. Alter table is first committed on master then it is replicated on slaves. The purpose of this task is to create a true LOCK=NONE

Implementation Idea

Master will write BEGIN_DDL_EVENT in binlog after it hits ha_prepare_inplace_alter_table. Then master will write QUERY_EVENT on binlog with actual alter query . On commit/rollback master will write COMMIT_DDL_EVENT/ROLLBACK_DDL_EVENT. On slave there will be pool of threads(configurable global variable), which will apply these DDLs. On reciving BEGIN_DDL_EVENT slave thread will pass the QUERY_EVENT to one of the worker thread. Worker thread will execute untill ha_inplace_alter_table. Actual commit_inplace_alter will be called by sql thread. If sql thread recieve some kind of rollback event , then it will somehow signal worker thread to stop executing alter. If none of the worker threads are avaliable then event will be enqueued, then If we recieved rollback event the we will simply discard event from queue, If we recieved commit event then SQL thread will syncrolysly process DDL event.

Details:

Mentor:

Details:

MDEV-11675

Mentor:

Sachin Setiya

Improve mysqltest language

mysqltest has a lot of historical problems:

ad hoc parser, weird limitations
commands added as needed with no view over the total language structure
historical code issues (e.g. casts that become unnecessary 10 years ago) etc

A lot can be done to improve it. Ideas

control structures, else in if, break and continue in while, for (or foreach) loop
proper expression support in let, if, etc
rich enough expressions to make resorting to sql unnecessary in most cases
remove unused and redundant commands (e.g. system vs exec, query_vertical vs vertical_results ONCE)
remove complex commands that do many sql statements under the hood, if they can be scripted, e.g. sync_slave_with_master
remove over-verbose treatment of rpl test failures
scoped variables
parameters for the source command
remove dead code

Details:

Mentor:

Details:

MDEV-12130

Mentor:

Sergei Golubchik

Implement multiple-table UPDATE/DELETE returning a result set

A multiple-table UPDATE first performs join operations, then it updates the matching rows. A multiple-table UPDATE returning a result set does the following:

first performs join operations
for each row of the result of the join it calculates some expressions over the columns of the join and forms from them a row of the returned result set
after this it updates the matching rows

A multiple-table DELETE first performs join operations, then it deletes the matching rows. A multiple-table DELETE returning a result set does the following:

first performs join operations
for each row of the result of the join it calculates some expressions over the columns of the join and forms from them a row of the returned result set
after this it deletes the matching rows

Details:

Mentor:

Details:

MDEV-12326

Mentor:

Igor Babaev

sort out the compression library chaos

hide all compression libraries behind a single unified compression API. Either develop our own or use something like Squash. This would require changing all engines to use this API
use the same approach as in server services — create a service per compression library, a service implementation will just return an error code for any function invocation if the corresponding library is not installed. this way — may be — we could avoid modifying all affected storage engines

Details:

Mentor:

Details:

MDEV-12933

Mentor:

Sergei Golubchik

Control over memory allocated for SP/PS

Task minimum:

Status variables which count the memory used/allocated for SP/PS by thread and/or for the server.

Other ideas:

Automatic stop allocation in debvugging version after second execution and call exception on attempt.
Information schema by threads and SP/PS with information about allocated and used memory

Details:

Mentor:

Details:

Mentor:

Oleksandr Byelkin

MariaDB ColumnStore

Full DECIMAL support in ColumnStore

MariaDB ColumnStore supports DECIMAL with some limitations:

We do not support the full DECIMAL range that is in MariaDB
In several places in the code we convert the DECIMAL to DOUBLE during execution therefore losing precision Implementing this will likely require the following:

Implementation of methods to handle MariaDB's DECIMAL format
Support for a longer than 8-byte numeric column type (there is an InfiniDB tree with work for this already)
Modification of the primitives processor for the math
Modification of the function expression processor to handle the new type
Version upgrade support for DECIMAL from the current form to the new form

Details

Mentor:

Details

List of beginner friendly bugs

Mentor:

Andrew Hutchings

mcsapi needs a new read API Design

We need an ORM-style NoSQL read API to go along with the bulk write API of mcsapi. This will likely take the form of:

A reader in ExeMgr which will convert messages from mcsapi into jobs
Code in mcsapi to send/receive the messages Although ExeMgr can already receive messages with an execution plan the format is very complex and ABI breaks easily (we often send whole C++ objects). We should look at other ORM frameworks for inspiration as the API design. This task to do the design for this API.

Details:

Mentor:

Details:

MCOL-1151

Mentor:

Andrew Hutchings

Use JIT for aggregation functions

Details:

Mentor:

Details:

MCOL-2222

Mentor:

Roman

Engine independent statistics for Columnstore

implement standalone segment files reader that in the end populates both mysql.column_stats and mysql.table_stats using out of band mariadb client connection
implement ANALYZE TABLE functionality for Columnstore engine
implement ANALYZE TABLE and Histograms with equal-width bins for values distribution histograms(similar to MDEV-12313) together with NDV histograms to decrease I/O

We expect to have both unit and regression tests but this is optional.

Details:

Mentor:

Details:

MCOL-2223

Mentor:

Roman

Suggest a Task

Do you have an idea of your own, not listed above? Do let us know!

_{This page is licensed: CC BY-SA / Gnu FDL}

Google Summer of Code 2018

We participated in the Google Summer of Code 2018. The MariaDB Foundation believes we are making a better database that remains application compatible with MySQL. We also work on making LGPL connectors (currently C, ODBC, Java) and on MariaDB Galera Cluster, which allows you to scale your reads & writes. And we have MariaDB ColumnStore, which is a columnar storage engine, designed to process petabytes of data with real-time response to analytical queries.

Where to start

Please join us at irc.freenode.net at #maria to mingle with the community. Don't forget to subscribe to maria-developers@lists.launchpad.net (this is the main list where we discuss development).

A few handy tips for any interested students who are unsure which projects to choose:Blog post from former GSoC student & mentor

To improve your chances of being accepted, it is a good idea to submit a pull request with a bug fix to the server.

List of tasks

Loaded from the MariaDB issue tracker

Full DECIMAL support in ColumnStore/

MariaDB ColumnStore supports DECIMAL with some limitations:

We do not support the full DECIMAL range that is in MariaDB
In several places in the code we convert the DECIMAL to DOUBLE during execution therefore losing precision

Implementing this will likely require the following:

Implementation of methods to handle MariaDB's DECIMAL format
Support for a longer than 8-byte numeric column type (there is an InfiniDB tree with work for this already)
Modification of the primitives processor for the math
Modification of the function expression processor to handle the new type
Version upgrade support for DECIMAL from the current form to the new form

Details:

Mentor:

Details:

Mentor:

Andrew Hutchings

mcsapi needs a new read API Design/

We need an ORM-style NoSQL read API to go along with the bulk write API of mcsapi.

This will likely take the form of:

A reader in ExeMgr which will convert messages from mcsapi into jobs
Code in mcsapi to send/receive the messages

Although ExeMgr can already receive messages with an execution plan the format is very complex and ABI breaks easily (we often send whole C++ objects).

We should look at other ORM frameworks for inspiration as the API design.

This task to do the design for this API.

Details:

Mentor:

Details:

MCOL-1151

Mentor:

Andrew Hutchings

Support for GTID in mysqlbinlog/

The mysqlbinlog client program needs to be updated to support GTID.

Here is a suggested list of things to be done:

The --start-position and --stop-position options should be able to take GTID positions; or maybe there should be new --start-gtid and --stop-gtid options. Like --start-gtid=0-1-100,1-2-200,2-1-1000.
A GTID position means the point just after that GTID. So starting from GTID 0-1-100 and stopping at GTID 0-1-200, the first GTID output will probably be 0-1-101 and the last one 0-1-200. Note that if some domain is not specified in the position, it means to start from the begining, respectively stop immediately in that domain.
Starting and stopping GTID should work both with local files, and with
1. --read-from-remote-server. For the latter, there are a couple of extra things that need doing in the master-slave protocol, see
2. get_master_version_and_clock()insql/slave.cc.
At the end of the dump, put these statements, to reduce the risk of those session variables incorrectly spilling into subsequent statements run in the same session:

SET session.server_id = @@global.server_id,
      session.gtid_domain_id=@@global.gtid_domain_id;

Probably some more things will come up during the work, but this looks like a reasonable start.

Details:

Mentor:

Details:

Mentor:

Andrei Elkin

Implement UPDATE with result set/

Add an UPDATE operation that returns a result set of the changed rows to the client.

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
RETURNING select_expr [, select_expr ...]

I'm not exactly sure how the corresponding multiple-table syntax should look like, or if it is possible at all. But already having it for single-table updates would be a nice feature.

Details:

Mentor:

Details:

MDEV-5092

Mentor:

Igor Babaev

optimizer trace/

In MySQL, Optimizer trace is a JSON object recording the execution path through the optimizer, decisions that were made and the reasons for them. See optimizer-tracing.html

Users were asking for MariaDB to have a similar feature.

Details:

Mentor:

Details:

MDEV-6111

Mentor:

Varun Gupta

Automatic provisioning of slave/

Idea

The purpose of this task is to create an easy-to-use facility for setting up a new MariaDB replication slave.

Setting up a new slave currently involves: 1) installing MariaDB with initial database; 2) point the slave to the master with CHANGE MASTER TO; 3) copying initial data from the master to the slave; and 4) starting the slave with START SLAVE. The idea is to automate step (3), which currently needs to be done manually.

The syntax could be something as simple as

LOAD DATA FROM MASTER

Implementation:

The idea is to do this non-blocking on the master, in a way that works for any storage engine. It will rely on row-based replication to be used between the master and the slave.

At the start of LOAD DATA FROM MASTER, the slave will enter a special provisioning mode. It will start replicating events from the master at the master's current position.

The master dump thread will send binlog events to the slave as normal. But in addition, it will interleave a dump of all the data on the master contained in tables, views, or stored functions. Whenever the dump thread would normally go to sleep waiting for more data to arrive in the binlog, the dump thread will instead send another chunk of data in the binlog stream for the slave to apply.

A "chunk of data" can be:

A CREATE OR REPLACE TABLE / VIEW / PROCEDURE / FUNCTION
A range of N rows (N=100, for example). Each successive chunk will do a range scan on the primary key from the end position of the last chunk.

Sending data in small chunks avoids the need for long-lived table locks or transactions that could adversely affect master performance.

The slave will connect in GTID mode. The master will send dumped chunks in a separate domain id, allowing the slave to process chunks in parallel with normal data.

During the provisioning, all normal replication events from the master will arrive on the slave, and the slave will attempt to apply them locally. Some of these events will fail to apply, since the affected table or row may not yet have been loaded. In the provisioning mode, all such errors will be silently ignored. Proper locking (isolation mode, eg.) must be used on the master when fetching chunks, to ensure that updates for any row will always be applied correctly on the slave, either in a chunk, or in a later row event.

In order to make the first version of this feature feasible to implement in a reasonable amount of time, it should set a number of reasonable restrictions (which could be relaxed in a later version of the feature):

Give up with an error if the slave is not configured for GTID mode (MASTER_USE_GTID != NO).
Give up with error if the slave receives any event in statement-based binlogging (so the master must be running in row-based replication mode, and no DDL must be done while the provisioning is running).
Give up with an error if the master has a table without primary key.
Secondary indexes will be enabled during the provisioning; this means that tables with large secondary indexes could be expensive to provision.

Details:

Mentor:

Details:

MDEV-7502

Mentor:

Andrei Elkin

connection encryption plugin support/

From having written GSSAPI for the internal interface, there were a couple things I would like to see in the plugin encryption interface.

First, GSSAPI is weird in that it does authentication before encryption (TLS/SSL are the other way around, establishing an encrypted channel and then doing authentication over it). Of course support for this is needed, but more importantly, packets must be processed in a fully serialized fashion. This is because encrypted packets may be queued while one end of the connection is still finishing up processing the authentication handshake. One way to do this is registering "handle" callbacks with connection-specific state, but there are definitely others.

Additionally, for whatever conception there ends up being of authentication and encryption, it needs to be possible to share more data than just a socket between them. The same context will be used for authentication and encryption, much as an SSL context is (except of course we go from authentication to encryption and not the other way around).

This ties into an issue of dependency. If authentication plugins are separate entities from encryption plugins in the final architecture, it might make sense to do mix-and-match authentication with encryption. However, there are cases - and GSSAPI is one - where doing encryption requires a certain kind of authentication (or vice versa). You can't do GSSAPI encryption without first doing GSSAPI authentication. (Whether or not it's permitted to do GSSAPI auth->encryption all over a TLS channel, for instance, is not something I'm concerned about.)

Finally, encrypted messages are larger than their non-encrypted counterparts. The transport layer should cope with this so that plugins don't have to think about reassembly, keeping in mind that there may not be a way to get the size of a message when encrypted without first encrypting it.

It's unfortunately been a little while since I wrote that code, but I think those were the main things that we'll need for GSSAPI. Thanks!

Details:

Mentor:

Details:

MDEV-9090

Mentor:

Vladislav Vaintroub

Aggregate Window Functions/

Currently only a few aggregate function are supported as window functions, the list can be found here aggregate-functions-as-window-functions

So in MDEV-7773, support for creating of custom aggregate functions was added. Now this task would deal with extending that feature and make custom aggregate functions behave as window functions

An example of a creating a custom aggregate function is given below:

CREATE AGGREGATE FUNCTION agg_sum(x INT) RETURNS DOUBLE
BEGIN
  DECLARE z DOUBLE DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN z;
  LOOP
    FETCH GROUP NEXT ROW;
    SET z = z + x;
  END LOOP;
END|

Details:

Mentor:

Details:

MDEV-11263

Mentor:

Varun Gupta

True ALTER LOCK=NONE on slave/

Currently no true LOCK=NONE exists on slave. Alter table is first committed on master then it is replicated on slaves. The purpose of this task is to create a true LOCK=NONE

Implementation Idea

On slave there will be pool of threads(configurable global variable), which will apply these DDLs. On reciving BEGIN_DDL_EVENT slave thread will pass the QUERY_EVENT to one of the worker thread. Worker thread will execute untill ha_inplace_alter_table. Actual commit_inplace_alter will be called by sql thread. If sql thread recieve some kind of rollback event , then it will somehow signal worker thread to stop executing alter. If none of the worker threads are avaliable then event will be enqueued, then If we recieved rollback event the we will simply discard event from queue, If we recieved commit event then SQL thread will syncrolysly process DDL event.

Details:

Mentor:

Details:

MDEV-11675

Mentor:

Sachin Setiya

Improve mysqltest language/

mysqltest has a lot of historical problems:

ad hoc parser, weird limitations
commands added as needed with no view over the total language structure
historical code issues (e.g. casts that become unnecessary 10 years ago) etc

A lot can be done to improve it.

Ideas

control structures, else in if, break and continue in while, for (or foreach) loop
proper expression support in let, if, etc
rich enough expressions to make resorting to sql unnecessary in most cases
remove unused and redundant commands (e.g. system vs exec, query_vertical vs vertical_results ONCE)
remove complex commands that do many sql statements under the hood, if they can be scripted, e.g. sync_slave_with_master
remove over-verbose treatment of rpl test failures
scoped variables
parameters for the source command
remove dead code

Details:

Mentor:

Details:

MDEV-12130

Mentor:

Sergei Golubchik

Cassandra Storage Engine v2, based on DataStax C++ driver/

MariaDB 10.1 had Cassandra Storage Engine which was developed for Cassandra 1.1.x. Back then, Cassandra provided a Thrift API, and that was what Cassandra-SE used.

Then, Cassandra 2.0 switched to using a different network protocol (and also changed the data model).

This task is to develop a Cassandra Storage Engine V2 using DataStax's C++ client library (cpp-driver).

See also: MDEV-8947 was a previous attempt to implement this engine. Unfortunately it didn't even produce a skeleton engine.

Details:

Mentor:

Details:

MDEV-12296

Mentor:

Sergei Golubchik

Histograms with equal-width bins in MariaDB/

(max(f)-min(f))/k *(i-1), max(f)-min(f))/k *i-1).

It means that if the sample rows have been chosen randomly the expected number of rows with the values of f from this interval can be approximated by the number m[i]*/100 * N.

To collect such statistics it is suggested to use the following variant of the ANALYZE TABLE command:

ANALYZE FAST TABLE tbl [ WITH n ROWS ] [SAMPLING p PERCENTS ]
   PERSISTENT FOR COLUMNS (col1 [IN RANGE r] [WITH k INTERVALS],...)

Here:

'WITH n ROWS' provides an estimate for the number of rows in the table in the case when this estimate cannot be obtained from statistical data.
'SAMPLING p PERCENTS' provides the percentage of sample rows to collect statistics. If this is omitted the number is taken from the system variable samples_ratio.
'IN RANGE r' sets the range of equal-width bins of the histogram built for the column col1. If this is omitted then and min and max values for the column can be read from statistical data then the histogram is built for the range [min(col1), max(col1)]. Otherwise the range [MIN_type(col1), MAX_type(col1) is considered]. The values beyond the given range, if any, are also is taken into account in two additional bins.
WITH k INTERVALS says how many bins are included in the histogram. If it is omitted this value is taken from the system variable histogram_size.

Details:

Mentor:

Details:

MDEV-12313

Mentor:

Vicentiu Ciorbaru

Implement multiple-table UPDATE/DELETE returning a result set/

A multiple-table UPDATE first performs join operations, then it updates the matching rows. A multiple-table UPDATE returning a result set does the following:

first performs join operations
for each row of the result of the join it calculates some expressions over the columns of the join and forms from them a row of the returned result set
after this it updates the matching rows

A multiple-table DELETE first performs join operations, then it deletes the matching rows. A multiple-table DELETE returning a result set does the following:

first performs join operations
for each row of the result of the join it calculates some expressions over the columns of the join and forms from them a row of the returned result set
after this it deletes the matching rows

Details:

Mentor:

Details:

MDEV-12326

Mentor:

Igor Babaev

Blacklist for access control a.k.a. "negative grants"/

Currently, MariaDB privilege system only perform whiltelist check for access control to certain database, table and column. This makes it difficult if we need to block access to certain database/table/column while allow for all others. — A good solution would be to allow to REVOKE anything that a user is able to do — not only exactly those grants that were granted to a user, but also a subset. Like

GRANT SELECT ON some_database.* TO a_user@%;
REVOKE SELECT ON some_database.secret_table FROM a_user@%;

Details:

Mentor:

Details:

MDEV-14443

Mentor:

Vicentiu Ciorbaru

Add syntax to manually encrypt/decrypt InnoDB's system tablespace/

Currently, the InnoDB system tablespace can only be automatically encrypted/decrypted by the background encryption threads if innodb_encrypt_tables=ON|FORCE, innodb_encryption_threads>0, and innodb_encryption_rotate_key_age>0. There is no way to manually encrypt/decrypt the tablespace.

File-per-table tablespaces can be manually encrypted with:

ALTER TABLE tab ENCRYPTION=YES;

File-per-table tablespaces can be manually decrypted with:

ALTER TABLE tab ENCRYPTION=NO;

Some users want a similar method that would allow them to manually encrypt/decrypt the InnoDB system tablespace.

This is loosely related to MDEV-14571, since both issues are related to the fact that the system tablespace can only be encrypted/decrypted by the background threads.

Details:

Mentor:

Details:

MDEV-14610

Mentor:

Jan Lindström

Control over memory allocated for SP/PS/

Task minimum:

Status variables which count the memory used/allocated for SP/PS by thread and/or for the server.

Other ideas:

Automatic stop allocation in debvugging version after second execution and call exception on attempt.
Information schema by threads and SP/PS with information about allocated and used memory

Information can be collected in MEM_ROOTs of SP/PS. Storing info about status of mem_root before execution then checking after new allocated memory can be found.

MEM_ROOT can be changed to have debug mode which make it read only which can be switched on after second execution.

Details:

Mentor:

Details:

Mentor:

Oleksandr Byelkin

_{This page is licensed: CC BY-SA / Gnu FDL}

Google Summer of Code 2017

We participated in the Google Summer of Code 2017 (we have participated previously in 2016, 2015, 2014, and 2013). The MariaDB Foundation believes we are making a better database that remains application compatible with MySQL. We also work on making LGPL connectors (currently C, ODBC, Java) and on MariaDB Galera Cluster, which allows you to scale your reads & writes. Lately, we also have MariaDB ColumnStore, which is a columnar storage engine, designed to process petabytes of data with real-time response to analytical queries.

Where to start

Please join us at irc.freenode.net at #maria to mingle with the community. Don't forget to subscribe to maria-developers@lists.launchpad.net (this is the main list where we discuss development).

A few handy tips for any interested students who are unsure which projects to choose:Blog post from former GSoC student & mentor

List of tasks

The complete list of tasks suggested for GSoC 2017 is located in the MariaDB Jira. A subset is listed below.

Support for GTID in mysqlbinlog

The mysqlbinlog tool needs to be updated to understand the replication feature called Global Transaction IDs (GTIDs) in MariaDB 10. The current version does not support GTIDs and the MySQL variant does not speak MariaDB 10's GTIDs.

Details:

Skills:

Mentor:

Students Interested:

Details:

Skills:

C/C++

Mentor:

Students Interested:

Current Cassandra Storage Engine was developed against Cassandra 1.1 and it uses Thrift API to communicate with Cassandra. However, starting from Cassandra 1.2, the preferred way to access Cassandra database is use CQL (Cassandra Query Language) and DataStax C++ Driver (cpp-driver). Thrift-based access is deprecated and places heavy constraints on the schema.

This task is about re-implementing Cassandra Storage Engine using DataStax C++ Driver and CQL.

Details:

Skills:

Mentor:

Students Interested:

Details:

MDEV-12296

Skills:

C/C++

Mentor:

Sergei Petrunia

Students Interested:

ColumnStore: Add proper NULL support

At the moment NULL is just the maximum integer for a column (or empty string for VARCHAR/CHAR). We need a mechanism to store NULLs separately to give us full type ranges.

ColumnStore: Add full support for DECIMAL

Right now it is cast to double which is not great for obvious reasons. It will mean modifying a lot of ColumnStore's version of MariaDB's function implementations and allowing column files to store more than 8 bytes per field.

Details:

Skills:

Mentor:

Students Interested:

Details:

Skills:

C/C++

Mentor:

Andrew Hutchings

Students Interested:

ColumnStore: Full UTF8 support.

This includes collations and anything that works on the length of the string.

Suggest a task

Do you have an idea of your own, not listed above or in Jira? Do let us know!

_{This page is licensed: CC BY-SA / Gnu FDL}

Google Summer of Code 2016

We participated in the Google Summer of Code 2016 (we have participated previously in 2015, 2014, and 2013). The MariaDB Foundation believes we are making a better database that remains application compatible with MySQL. We also work on making LGPL connectors (currently in C, Java, C++ in development) and on MariaDB Galera Cluster, which allows you to scale your reads & writes. Lately, we also have MariaDB MaxScale which is a pluggable database proxy.

Where to start

Please join us at irc.freenode.net at #maria to mingle with the community. Don't forget to subscribe to maria-developers@lists.launchpad.net (this is the main list where we discuss development).

Please keep in mind that in April we travel a lot (conferences, busy time focusing on a release), so if you have a question and nobody on IRC answers, don't feel disappointed, ask in an email to maria-developers@lists.launchpad.net. Asking on the mailing list means others benefit from your Q&A too!

The complete list of tasks suggested for GSoC 2016 is located in the MariaDB Jira. A subset is listed below.

Support for GTID in mysqlbinlog

Details:

Skills:

Mentor:

Students Interested:

Details: