ss - oscar

My SQL Dump

MySQL musings by a self professed MySQL Geek

SQL injection in the MySQL server! (of the proxy kind)
swanhart
[this is a repost of my http://shardquery.com blog post, because it did not syndicate to planet.mysql.com]

As work on WarpSQL (Shard-Query 3) progresses, it has outgrown MySQL proxy.  MySQL proxy is a very useful tool, but it requires LUA scripting, and it is an external daemon that needs to be maintained.  The MySQL proxy module for Shard-Query works well, but to make WarpSQL into a real distributed transaction coordinator, moving the proxy logic inside of the server makes more sense.

The main benefit of MySQL proxy is that it allows a script to "inject" queries between the client and server, intercepting the results and possibly sending back new results to the client.  I would like similar functionality, but inside of the server.

For example, I would like to implement new SHOW commands, and these commands do not need to be implemented as actual MySQL SHOW commands under the covers.

For example, for this blog post I made a new example command called "SHOW PASSWORD"

Example "injection" which adds SHOW PASSWORD functionality to the server

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

-- THIS COMMAND DOES NOT EXIST
mysql> show password;
+-------------------------------------------+
| password_hash                             |
+-------------------------------------------+
| *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+-------------------------------------------+
1 row in set (0.00 sec)


Important - This isn't a MySQL proxy plugin.  There is C++ code in the SERVER to answer that query, but it isn't the normal SHOW command code.  This "plugin" (I put it in quotes because my plan is for a pluggable interface but it isn't added to the server yet) doesn't access the mysql.user table using normal internal access methods. It runs actual SQL inside of the server, on the same THD as the client connection, in the same transaction as the client connection, to get the answer!

Problem #1 - Running SQL in the server

The MySQL C client API doesn't have any methods for connecting to the server from inside of the server, except to connect to the normally available socket interfaces, authenticate, and then issue queries like a normal client.  While it is perfectly possible to connect to the server as a client in this manner, it is sub-optimal for a number of reasons.  First, it requires a second connection to the server, second, it requires that you authenticate again (which requires you have the user's password), and lastly, any work done in the second connection is not party to transactional changes in the first, and vice-versa.

The problem is communication between the client and server, which uses a mechanism called VIO.  There was work done a long time ago for external stored procedures, which never made it into the main server that would have alleviated this problem by implementing a in-server VIO layer, and making the parser re-entrant.  That work was done on MySQL 5.1 though.

It is possible to run queries without using VIO though.  You simply can't get results back, except to know if the query succeeded or not.  This means it is perfectly acceptable for any command that doesn't need a resultset, basically anything other than SELECT.  There is a loophole however, in that any changes made to the THD stay made to that THD.  Thus, if the SQL executed sets any user variables, then those variables are of course visible after query execution.

Solution  - encapsulate arbitrary SQL resultsets through a user variable

Since user variables are visible after query execution, the goal is to get the complete results of a query into a user variable, so that the resultset can be accessed from the server.  To accomplish this, first a method to get the results into the variable must be established, and then some data format for communication that is amenable to that method has to be decided upon so that the resultset can be accessed conveniently..

With a little elbow grease MySQL can convert any SELECT statement into CSV resultset.  To do so, the following are used:


  1. SELECT ... INTO @user_variable

  2. A subquery in the FROM clause (for the original query)

  3. CONCAT, REPLACE, IFNULL, GROUP_CONCAT (to encode the resultset data)

Here is the SQL that the SHOW PASSWORD command uses to get the correct password:

select authentication_string as pw,
       user 
  from mysql.user 
 where concat(user,'@',host) = USER() 
    or user = USER() 
LIMIT 1

Here is the "injected" SQL that the database generates to encapsulate the SQL resultset as CSV:

select 
  group_concat( 
    concat('"',
           IFNULL(REPLACE(REPLACE(`pw`,'"','\\"'),"\n","\\n"),"\N"),
           '"|"',
           IFNULL(REPLACE(REPLACE(`user`,'"','\\"'),"\n","\\n"),"\N"),
           '"'
    ) 
  separator "\n"
  ) 
from 
  ( select authentication_string as pw,
           user 
      from mysql.user 
      where concat(user,'@',host) = USER() 
        OR user = USER() 
    LIMIT 1
  ) the_query 
into @sql_resultset ;
Query OK, 1 row affected (0.00 sec)

Here is the actual encapsulated resultset.  If there were more than one row, they would be newline separated.

mysql> select @sql_resultset;
+----------------+
| @sql_resultset |
+----------------+
| ""|"root"      |
+----------------+
1 row in set (0.00 sec)

Injecting SQL in the server

With the ability to encapsulate resultsets into CSV in user variables, it is possible to create a cursor over the resultset data and access it in the server.  The MySQL 5.7 pre-parse rewrite plugins, however,  still run inside the parser.  The THD is not "clean" with respect to being able to run a second query.  The parser is not re-entrant.  Because I desire to run (perhaps many) queries between the time a user enters a query and the server actually answers the query (perhaps with a different query than the user entered!) the MySQL 5.7 pre-parse rewrite plugin infrastructure doesn't work for me.

I modified the server, instead, so that there is a hook in do_command() for query injections.  I called it conveniently query_injection_point() and the goal is to make it a new plugin type, but I haven't written that code yet.  Here is the current signature for query_injection_point():


bool query_injection_point(
  THD* thd, COM_DATA *com_data, enum enum_server_command command,
  COM_DATA* new_com_data, enum enum_server_command* new_command );

It has essentially the same signature as dispatch_command(), but it provides the ability to replace the command, or keep it as is.  It returns true when the command has been replaced.

Because it is not yet pluggable, here is the code that I placed in the injection point:


/* TODO: make this pluggable */
bool query_injection_point(THD* thd, COM_DATA *com_data, enum enum_server_command command,
 COM_DATA* new_com_data, enum enum_server_command* new_command)
{
 /* example rewrite rule for SHOW PASSWORD*/
 if(command != COM_QUERY)
 { return false; }
 
 /* convert query to upper case */
 std::locale loc;
 std::string old_query(com_data->com_query.query,com_data->com_query.length);
 for(unsigned int i=0;i<com_data->com_query.length;++i) {
   old_query[i] = std::toupper(old_query[i], loc);
 } 
   
 if(old_query == "SHOW PASSWORD")
 {
   std::string new_query;
   SQLClient conn(thd);
   SQLCursor* stmt;
   SQLRow* row;

   if(conn.query("pw,user",
    "select authentication_string as pw,user from mysql.user " \
    "where concat(user,'@',host) = USER() or user = USER() LIMIT 1", &stmt))
   {
     if(stmt != NULL)
     {
       if((row = stmt->next()))
       {
          new_query = "SELECT '" + row->at(0) + "' as password_hash";
       }
       } else
       {
         return false;
       }
     } else {
       return false;
     }

     /* replace the command sent to the server */
     if(new_query != "")
     {
       Protocol_classic *protocol= thd->get_protocol_classic();
       protocol->create_command(
         new_com_data, COM_QUERY, 
         (uchar *) strdup(new_query.c_str()), 
         new_query.length()
       );
       *new_command = COM_QUERY;
     } else {
       if(stmt) delete stmt;
       return false;
     }
     if(stmt) delete stmt;
     return true;
   }
 }

 /* don't replace command */
 return false;
}

SQLClient

You will notice that the code access the mysql.user table using SQL, using the SQLClient, SQLCursor, and SQLRow objects.  These are the objects that wrap around encapsulating the SQL into a CSV resultset, and actually accessing the result set.  The interface is very simple, as you can see from the example.  You create a SQLClient for a THD (one that is NOT running a query already!) and then you simply run queries and access the results.

The SQLClient uses a stored procedure to methodically encapsulate the SQL into CSV and then provides objects to access and iterate over the data that is buffered in the user variable.  Because MySQL 5.7 comes with the sys schema, I placed the stored procedure into it, as there is no other available default database that allows the creation of stored procedures.  I called it sys.sql_client().

Because the resultset is stored as text data, the SQLRow object returns all column values as std::string.

What's next?

I need to add a proper plugin type for "SQL injection plugins".  Then I need to work on a plugin for parallel queries.  Most of the work for that is already done, actually, at least to get it into an alpha quality state.  There is still quite a bit of work to be done though.

You can find the code in the internal_client branch of my fork of MySQL 5.7:

http://github.com/greenlion/warpsql-server


I am now a member of team MariaDB
swanhart
Monday I started at MariaDB working on ColumnStore. My interest in column store technology extends way back to the days as the MySQL evangelist at Kickfire, which was a compressing column store and "SQL CHIP" appliance. My initial tasks are documentation related, but I'll be helping with development, and of course testing it with Shard-Query, which is a great stress testing system.

GROUP_CONCAT is very slow. So I used FastBit for the table instead! 43 seconds down to .16!
swanhart

MySQL always tries to make toast of my good ideas. This is the only time is succeeds in making toast (see bug #2). |

This time I'm working on star schema optimzation (which will be my next blog post). MySQL takes about .24 seconds to project out a list of parts that match a filter, but it takes 43 (not .43, FORTY-THREE) seconds to turn it into a CSV list using GROUP_CONCAT.

  select P_PartKey from part where where P_MFGR = 'MFGR#1' or P_MFGR='MFGR#2';
  399597 rows in set (0.24 sec)

  mysql> select group_concat(P_PartKey) into @pkey from part where P_MFGR = 'MFGR#1' or   P_MFGR='MFGR#2';
  Query OK, 1 row affected (43.25 sec)

So I decided to just stick the part table into FastBit instead (using my FastBit_UDF tools):
fb_create('/var/lib/fastbit/part', 'P_PartKey:int,P_Name:text,P_MFGR:text,P_Category:text,P_Brand:text,P_Colour:text,P_Type:text,P_Size:text,P_Container:text');

-- this loads the table, which is one time only
select fb_insert2('/var/lib/fastbit/part',P_PartKey, P_Name, P_MFGR, P_Category, P_Brand, P_Colour, P_Type, P_Size, P_Container) from part group by 1;
Query OK, 1 row affected (15 seconds)

-- create the same CSV list using fb_inlist():
select fastbit.fb_inlist('/var/lib/fastbit/part',"select P_PartKey where P_MFGR='MFGR#1' or P_MFGR='MFGR#2'") into @plist;
Query OK, 1 row affected (0.16 sec)

I think .16 seconds is a lot better than 43! :D

You can find the Fastbit_UDF tools here:

https://github.com/greenlion/FastBit_UDF


ASYNC native asynchronous query mechanism for MySQL updated for 5.6 and support non-SELECT statement
swanhart
Async, part of the Swanhart Toolkit (http://github.com/greenlion/swanhart-tools) has been updated to fully support 5.6 (the GET_LOCK I used only worked with new 5.7 GET_LOCK semantics) and it now also supports INSERT/UPDATE/DELETE/CREATE/CALL/etc (anything that can be prepared). You can only get a resultset back from SELECT statements even if CALL statements return a resultset. This is a MySQL limitation (no CALL xyx(..) AS table_name)

Now available in swanhart-tools: NATIVE asynchronous query execution for any MySQL client!
swanhart
There is often a need to run queries in the background in MySQL. This generally is accomplished using a message queue (like gearman), or by using extensions to a client (PHP has such extensions) or by using MariaDB's native async query interface (C client only I think).

While such solutions work well, they don't work for say a GO client, or for the mysql command line client itself.

I present "async"; part of the Swanhart Toolkit (http://github.com/greenlion/swanhart-tools). Async is a stored procedure and event based solution for asynchronous queries.

It consists of:

  • A queue table to hold the SQL to run, the state of execution, error messages, etc

  • A settings table that controls the number of parallel threads to use for executing queries

  • A stored routine worker that picks up SQL queries and executes them

  • An EVENT that runs the worker threads (which exits if too many are running)

  • SQL stored routines for dropping SQL into the queue (async.queue), checking the status of the query(async.check), and getting the result of a query (async.wait).

It works with MySQL 5.6 or greater (you can make it work with 5.5 if you get rid of GET DIAGNOSTICS with the tradeoff that you won't have good error messages for your broken queries.

Just run the setup.sql script, enable the event scheduler, then:
call async.queue('select sleep(50)');  -- returns the query number (also in @query_number)
call async.check(@query_number)\G
call async.wait(@query_number);

-- eight workers run by default (update the settings table to change that)
mysql> show processlist;
+------+-----------------+-----------+-------+---------+------+-----------------------------+------------------+
| Id   | User            | Host      | db    | Command | Time | State                       | Info             |
+------+-----------------+-----------+-------+---------+------+-----------------------------+------------------+
|  143 | root            | localhost | async | Query   |    0 | User sleep                  | DO SLEEP(v_wait) |
|  146 | event_scheduler | localhost | NULL  | Daemon  |    0 | Waiting for next activation | NULL             |
|  147 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  148 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  149 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  150 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  151 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  152 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
| 1525 | root            | localhost | async | Query   |    0 | starting                    | show processlist |
+------+-----------------+-----------+-------+---------+------+-----------------------------+------------------+
9 rows in set (0.00 sec)

-- queue a background query
mysql> call async.queue('select sleep(25)');
+--------------+
| QUERY_NUMBER |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- check the status
mysql> call async.check(@query_number)\G
*************************** 1. row ***************************
        q_id: 1
    sql_text: select sleep(25)
  created_on: 2016-02-24 14:09:30
  started_on: 2016-02-24 14:09:30
completed_on: NULL
      parent: NULL
   completed: 0
       state: RUNNING
       errno: NULL
      errmsg: NULL
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- wait for a query to finish (or just get the result of a finished query)
mysql> call async.wait(@query_number);
+-----------+
| sleep(25) |
+-----------+
|         0 |
+-----------+
1 row in set (10.18 sec)

Query OK, 0 rows affected (10.18 sec)

-- run a query that generates an error
mysql> call async.queue('select sleep(25) from bad_table');
+--------------+
| QUERY_NUMBER |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- get the error information for the bad query
mysql> call async.check(@query_number)\G
*************************** 1. row ***************************
        q_id: 2
    sql_text: select sleep(25) from bad_table
  created_on: 2016-02-24 14:10:04
  started_on: 2016-02-24 14:10:04
completed_on: 2016-02-24 14:10:04
      parent: NULL
   completed: 1
       state: ERROR
       errno: 42S02
      errmsg: Table 'async.bad_table' doesn't exist
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Returning to the original licenses of my tools
swanhart
I was mad with Percona. They f*cked me over big time. But they can still use my tools. I'm not an asshole, at least not permanently. Such a license change is not actually workable (the older version with the older license could always be used) and it isn't in the spirit of open source.

My apologies.

New license for Swanhart-Tools and my other MySQL projects
swanhart
The following new license is now in effect:
SWANHART OPEN LICENSE
Clause #1:
The following companies:
Percona staffing llc, and any associated parent, child or ancillary entities

May not download, retain, install, operate, or in any way associate themselves with this software. This is without exception. Any use must immediately and permanently cease. Violation of this license will be pursued in a court of law.

The remainder of the license is normal 3 clause BSD. The above preamble overrides any relevant clauses in that license with respect to rights to use the software.

Long live MariaDB!

Advanced JSON for MySQL: indexing and aggregation for highly complex JSON documents
swanhart

What is JSON


JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL


It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough JSON problems we face.

Searching:
The JSON UDF provide a number of functions that make working with JSON easier, including the ability to extract portions of a document, or search a document for a particular key. That being said, you can't use JSON_EXTRACT() or JSON_SEARCH in the WHERE clause, because it will initiate a dreaded full-table-scan (what MongoDB would call a full collection scan). This is a big problem and common wisdom is that JSON can't be indexed for efficient WHERE clauses, especially sub-documents like arrays or objects within the JSON.

Actually, however, I've come up with a technique to effectively index JSON data in MySQL (to any depth). The key lies in transforming the JSON from a format that is not easily indexed into one that is easily indexed. Now, when you think index you think B-TREE or HASH indexes (or bitmap indexes) but MySQL also supports FULLTEXT indexes.

A fulltext index is an inverted index where words (tokens) point to documents. While text indexes are great, they aren't normally usable for JSON. The reason is, MySQL splits words on whitespace and non-alphanumeric characters. A JSON document doesn't end up being usable when the name of the field (the key) can't be associated with the value. But what if we transform the JSON? You can "flatten" the JSON down into key/value pairs and use a text index to associate the key/value pairs with the document. I created a UDF called RAPID_FLATTEN_JSON using the C++ Rapid JSON library. The UDF flattens JSON documents down into key/value pairs for the specific purpose of indexing.

Here is an example JSON document:
{
	"id": "0001",
	"type": "donut",
	"name": "Cake",
	"ppu": 0.55,
	"batters":
		{
			"batter":
				[
					{ "id": "1001", "type": "Regular" },
					{ "id": "1002", "type": "Chocolate" },
					{ "id": "1003", "type": "Blueberry" },
					{ "id": "1004", "type": "Devil's Food" }
				]
		},
	"topping":
		[
			{ "id": "5001", "type": "None" },
			{ "id": "5002", "type": "Glazed" },
			{ "id": "5005", "type": "Sugar" },
			{ "id": "5007", "type": "Powdered Sugar" },
			{ "id": "5006", "type": "Chocolate with Sprinkles" },
			{ "id": "5003", "type": "Chocolate" },
			{ "id": "5004", "type": "Maple" }
		]
}


Flattened:
mysql> select RAPID_FLATTEN_JSON(load_file('/tmp/doc.json'))\G
*************************** 1. row ***************************
RAPID_FLATTEN_JSON(load_file('/tmp/doc.json')): id=0001
type=donut
name=Cake
ppu=0.55
id=1001
type=Regular
id=1002
type=Chocolate
id=1003
type=Blueberry
id=1004
type=Devil's Food
type=Devil's
type=Food
id=5001
type=None
id=5002
type=Glazed
id=5005
type=Sugar
id=5007
type=Powdered Sugar
type=Powdered
type=Sugar
id=5006
type=Chocolate with Sprinkles
type=Chocolate
type=with
type=Sprinkles
id=5003
type=Chocolate
id=5004
type=Maple
1 row in set (0.00 sec)


Obviously this is useful, because our keys are now attached to our values in an easily searchable way. All you need to do is store the flattened version of the JSON in another field (or another table), and index it with a FULLTEXT index to make it searchable. But wait, there is one more big problem: MySQL will split words on the equal sign. We don't want this as it removes the locality of the keyword and the value. To fix this problem you'll have to undertake the (actually quite easy) step of adding a new collation to MySQL (I called mine ft_kvpair_ci). I added equal (=) to the list of lower case characters as described in the manual. You just have to change two text files, no need to recompile the server or anything, and as I said, it is pretty easy. Let me know if you get stuck on this step and I can show you the 5.6.22 files I modified.

By the way, I used a UDF, because MySQL FULLTEXT indexes don't support pluggable parsers for InnoDB until 5.7. This will be much cleaner in 5.7 with a parser plugin and there will be no need to maintain an extra column.

Using the solution:
Given a table full of complex json:
create table json2(id int auto_increment primary key, doc mediumtext);


Add a column for the index data and FULLTEXT index it:
alter table json2 add flat mediumtext character set latin1 collate ft_kvpair_ci, FULLTEXT(flat);


Then populate the index. Note that you can create a trigger to keep the second column in sync, I let that up to an exercise of the reader, or you can use Flexviews to maintain a copy in a second table automatically.
mysql> update json2 set flat=RAPID_FLATTEN_JSON(doc);
Query OK, 18801 rows affected (26.34 sec)
Rows matched: 18801  Changed: 18801  Warnings: 0


Using the index:
mysql> select count(*) from json2 where match(flat) against ('last_name=Vembu');
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)


The documents I searched for that example are very complex and highly nested. Check out the full matching documents for the query here here

If you want to only index a subportion of the document, use the MySQL UDF JSON_EXTRACT to extract the portion you want to index, and only flatten that.

Aggregating:
JSON documents may contain sub-documents as mentioned a moment ago. JSON_EXTRACT can extract a portion of a document, but it is still a text document. There is no function that can extract ALL of a particular key (like invoice_price) and aggregate the results. So, if you have a document called orders which contains a varying number of items and their prices, it is very difficult (if not impossible) to use the JSON UDF to aggregate a "total sales" figure from all the order documents.

To solve this problem, I created another UDF called RAPID_EXTRACT_ALL(json, 'key'). This UDF will extract all the values for the given key. For example, if there are 10 line items with invoice_id: 30, it will extract the value (30 in this case) for each item. This UDF returns each item separated by newline. I created a few stored routines called jsum, jmin, jmax, jcount, and javg. They can process the output of rapid_extract_all and aggregate it. If you want to only RAPID_EXTRACT_ALL from a portion of a document, extract that portion with the MySQL UDF JSON_EXTRACT first, then process that with RAPID_EXTRACT_ALL.

For example:
mysql> select json_extract_all(doc,'id') ids, jsum(json_extract_all(doc,'id')) from json2 limit 1\G    
*************************** 1. row ***************************
ids: 888
889
2312
5869
8702

jsum(json_extract_all(doc,'id')): 18660.00000
1 row in set (0.01 sec)


Aggregating all of the id values in the entire collection:
mysql> select sum( jsum(json_extract_all(doc,'id')) ) from json2 ;
+-----------------------------------------+
| sum( jsum(json_extract_all(doc,'id')) ) |
+-----------------------------------------+
|                         296615411.00000 |
+-----------------------------------------+
1 row in set (2.90 sec)


Of course you could extract other fields and sort and group on them.

Where to get the tools:
You can find the UDF in the swanhart-tools github repo. I think you will find these tools very useful in working with JSON documents in MySQL.

Take the long view on the MySQL PERFORMANCE_SCHEMA with ps_history and sys_history
swanhart
The performance_schema is a powerful tool for analyzing MySQL performance and behavior. One aspect of the performance_schema is that the view of the data is "right now", and very little historical information is present. You can see that there are 10 threads connected right now, but what about five minutes ago?

ps_history
ps_history is a set of stored routines and events for periodically collecting the data in the performance_schema into another schema called ps_history. The ps_history schema contains a copy of each performance_schema view as a real table, and timestamp and server_id columns have been added to each table. Periodically (by default every 30 seconds) the performance_schema data is written into the history tables.

ps_history comes as one script (setup.sql) which will create the ps_history schema, the tables within it, and create an event for collecting data. To collect data into the history tables you must either enable the event scheduler (set global event_scheduler=1) or you must periodically run the ps_history.collect() stored routine.

ps_history automatically removes old data from the history tables. By default 1 week of history will be retained. You can change the retention period with ps_history.set_retention_period(), for example to keep 2 weeks of history use ps_history.set_retention_period('2 WEEK'); to keep 10 days, ps_history.set_retention_period('10 DAY');

Here is an example from the hosts table:
mysql> select * from ps_history.hosts limit 4\G
*************************** 1. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 2
  TOTAL_CONNECTIONS: 18588
          server_id: 33
                 ts: 2015-02-09 13:17:16.68339
*************************** 2. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 0
  TOTAL_CONNECTIONS: 6
          server_id: 33
                 ts: 2015-02-09 13:17:16.68339
*************************** 3. row ***************************
               HOST: localhost
CURRENT_CONNECTIONS: 2
  TOTAL_CONNECTIONS: 18619
          server_id: 33
                 ts: 2015-02-09 13:17:47.69614
*************************** 4. row ***************************
               HOST: NULL
CURRENT_CONNECTIONS: 0
  TOTAL_CONNECTIONS: 6
          server_id: 33
                 ts: 2015-02-09 13:17:47.69614
4 rows in set (0.00 sec)


sys_history
sys_history is a modified version of the SYS schema to use the data from the ps_history tables instead of the performance_schema. Each of the views in the SYS schema have been modified to include the timestamp (ts) field from the ps_history tables, and joins have been modified to include ts in the join.

Here is an example of one of the aggregated views:
mysql> select * from io_by_thread_by_latency limit 4\G
*************************** 1. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: page_cleaner_thread
         total: 4809257
 total_latency: 00:08:13.93
   min_latency: 66.98 ns
   avg_latency: 1.02 ms
   max_latency: 99.29 ms
     thread_id: 18
processlist_id: NULL
*************************** 2. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: io_handler_thread
         total: 1081585
 total_latency: 00:04:44.69
   min_latency: 432.51 ns
   avg_latency: 263.22 us
   max_latency: 36.52 ms
     thread_id: 8
processlist_id: NULL
*************************** 3. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: io_handler_thread
         total: 412302
 total_latency: 00:01:34.81
   min_latency: 604.82 ns
   avg_latency: 229.94 us
   max_latency: 69.59 ms
     thread_id: 11
processlist_id: NULL
*************************** 4. row ***************************
            ts: 2015-02-09 13:17:16.68339
          user: io_handler_thread
         total: 368534
 total_latency: 00:01:32.18
   min_latency: 589.63 ns
   avg_latency: 250.12 us
   max_latency: 37.70 ms
     thread_id: 10
processlist_id: NULL
4 rows in set (5.91 sec)

Announcing a new MySQL fork: TroySQL
swanhart
First, I believe that Oracle MySQL, Percona MySQL and SkySQL MySQL (MariaDB) are all great databases with their own merits. You should probably try all of them and see which one works best for you. Personally I like Percona Server, but all of them are good.

So why do we need ANOTHER fork when there are already three perfectly acceptable commercial forks?
a) Experimentation
b) Community Involvement
c) Serious feature/functionality divergence/disparity
d) Extensibility
e) Storage engines
f) OLAP focus

Experimentation:
There are a lot of interesting and useful things in the database world that are not possible in MySQL. Many of these features have been implemented as middleware or add-ons as third party tools, but they don't get wide adoption because they are not "built-in" to the database. Two good examples are Flexviews and Shard-Query. Other experiments and worklogs have had significant work checked into other branches and forks, but that work has never been accepted into upstream. A good example of that are "perl stored procedures", or "external stored procedures". Another is allowing plugins to add variables to the THD without modifying the source. These are good contributions that are in danger of being lost. They should not languish in a tree somewhere. I think there should be an active fork to test these ideas.

Community Involvement:
If the fork encourages experimentation, then community involvement is more likely. If the fork is more accepting of code contributions (like those with alternate licenses like BSD two and three clause) this too encourages community involvement. So both are true with TroySQL. Experimentation is encouraged, and you can contribute code under any license deemed compatible with GPL V2 (LGPLv2, BSD2,BSD3). This does not mean that every feature submitted for inclusion will make it into a release or that it will work exactly as submitted, but if you want a new cool feature like user defined types, or you want to really make foreign keys work for all engines, then this would be a great place to try it out.

Serious feature/functionality divergence/disparity:
While TroySQL does not plan to diverge so far from MySQL as to be unrecognizable, there is a high price to be paid for guaranteeing compatibility with regular MySQL. This price may be acceptable in commercial forks but I'd rather say "not guaranteed compatible" and just be done with it. So TroySQL will maintain compatibility with regular MySQL where possible, but when necessary it will diverge. Given the desire for experimentation divergence is necessary and should not be considered a negative quality.

Extensibility:
Instead of plugins with a C interface, I want to start moving to TRIGGERS. Binlog event triggers, user logon/logoff triggers, statement triggers, commit triggers, etc. This makes writing extensions in any language possible, as a trigger can be written in any language that the database supports. Many current plugins can be rewritten to use the trigger interface instead. There will still be a use for C plugins, particularly for storage engines.

Storage engines:
A column store is needed, and a compressing one. Index plugins too so that bitmap and text indexes can be implemented in an engine agnostic way. Proper triggers for row changes that can invoke C code will of course be useful too which can lead to proper FK support for all engines.

I would like to take the CONNECT storage engine, and implement proper SQL/MED data wrapper syntax on top of it instead of using it as a different storage engine.

OLAP focus:
None of the current forks plan on adding intra-query parallelism any time soon. This feature is necessary for good performance on large volumes of database when large volumes of data must be examined by queries. TroySQL will support query parallelism, starting with SELECT queries. Users have been asking for window functions and common table expressions. These will be added too, as well as table functions.

Materialized views, bitmap indexes, a column store, star join optimization and query rewrite are needed too. An optimizer that can use more than one join type and that has a hash join that can spill to disk would be nice too, as would sort/merge joins.

Release plan:
TroySQL will be released as "preview releases". Each preview release will have one or two new features. Some features might have a lot of depth, others might be simple. Each preview release will be named, and there may be multiple preview releases for a feature. So, for example, the goal of the first preview release is query rewrite, parallel query, php stored procedures and perl stored procedures. It will be called "TroySQL 5.6.X Green Swan PR1". Then, as development progresses, "Green Swan PR2" will be released. Once everything is well tested and feature complete, it will become "TroySQL 5.6.X Green Swan GA1" Finally, in a GA release, point release improvements (no new features) are labeled as "TroySQL 5.6.X Green Swan GA1.1, ... GA1.2, etc).

This will identify a feature set with a name. The next release will include everything in Green Swan and add new features. Thus, it is easy to identify when a test release is being used, and because the major MySQL release number is always in the name, it is clear which name is newer than the others.

Why Green Swan? Well, Black Swan would of course be apropos, but my favorite color is Green, and my name is Swanhart. And a Black Swan is something unexpected, thus Green Swan is even more unexpected. Or something like that :)

Where to find it:
https://github.com/greenlion/troysql-5.6/
Tags:

?

Log in

No account? Create an account