~drizzle-trunk/drizzle/development : contents of docs/mysql

~drizzle-trunk/drizzle/development : (revision 1945)

=========================
Notable MySQL Differences
=========================

Drizzle was forked from the (now defunct) MySQL 6.0 tree. Since then there
have been a lot of changes. Some areas are similar, others unrecognisable.

This section aims to explore some of the notable differences between MySQL
and Drizzle.

This section was originally adapted from the Drizzle Wiki.

Usage
-----
 * There is no embedded server. The Drizzle Server is not loadable as a shared
   library.
 * Drizzle is optimized for massively concurrent environments. If we have the
   choice of improving performance for 1024 simultaneous connections to the
   detriment of performance with only 64 connections, we will take that choice.
 * Designed for modern POSIX systems
 * Microsoft Windows is not a supported platform (neither is HP-UX or IRIX).
 * No timezones. Everything is UTC.

Installation
------------

 * No scripts/mysql_install_db or similar. A "just works" installation
   mentality without administrative overhead.
 * No system database that needs upgrading between versions.
 * Drizzle can listen on the Drizzle port (4427) and/or MySQL port (3306)
   and speak the respective protocols.

Architecture
------------

Drizzle is designed around the concept of being a micro-kernel. There should
be a small core of the server with most functionality being provided through
small, efficient and hard to misuse plugin interfaces. The goal is a small,
light weight kernel which is easy to maintain, understand and extend.

Drizzle is written in C++ making use of the Standard Template Library (STL)
and Boost. Only where performance or correctness proves to be inadequate will
we consider rolling our own, and our preference is to fix the upstream library
instead.

Network protocol
----------------

Pluggable network protocols allow Drizzle to speak one (or more) of several
protocols. Currently we support the MySQL protocol (compatible with existing
MySQL client libraries) and the Drizzle protocol which is still under
development but aims for several important differences:

 * Client sends first packet instead of server
 * built in sharding
 * multi statement support (without using a semicolon to separate them)
 * room for expansion to include NoSQL type commands inline with SQL.

There is also a console plugin that instead of providing access over a network
socket, allows access from the current tty.

Plugin API
----------

Existing plugin APIs inherited from MySQL have been reworked.

 * User Defined Functions (UDFs) now follow the same API as within the
   server instead of a different C API. This means that UDFs are on the
   exact same level as builtin functions.
 * Storage Engine API has had some parts extensively reworked, especially
   around transactions and DDL.
 * Logging is now pluggable
 * Authentication is pluggable
 * Replication is pluggable
 * INFORMATION_SCHEMA plugins have been replaced by the function_engine, which
   is a lot more space and time efficient.
 * Network protocols are pluggable
 * Scheduler is pluggable (multi_thread, pool_of_threads etc)
 * Plugin points for manipulating rows before/after operations: used for
   replication and the PBMS Blob Streaming plugin.

Stored Procedures
-----------------

Drizzle does not currently have any plugins implement stored procedures. We
viewed the implementation in MySQL to be non-optimal, bloating the parser
and only supporting one language (SQL2003 stored procedures) which was not
well known.

Fundamentally, stored procedures usually are not the correct architectural
decision for applications that need to scale. Pushing more computation down
into the database (which is the trickiest layer to scale) isn't a good idea.

We do recognise that the ability to reduce the time row locks are held
by using stored procedures is valuable, but think the same advantage can
be gotten with improved batching of commands over the wire instead of adding
administering stored procedures to the list of things that can go wrong in
admisistering the database.

Triggers
--------

Drizzle does not currently have any plugin that provides SQL triggers. We
have some hooks for callbacks inside the server so that plugins can hook
into points that triggers could.

Views
-----

SQL Views are not currently supported in Drizzle. We believe they should be
implemented via a query rewrite plugin. See the `Query Rewrite Blueprint <https://blueprints.launchpad.net/drizzle/+spec/query-rewrite>`_ on launchpad.

Partitioning
------------

INFORMATION_SCHEMA
------------------

The INFORMATION_SCHEMA in Drizzle is strictly ANSI compliant. If you write
a query to any of the tables in the INFORMATION_SCHEMA in Drizzle, you can
directly run these on any other ANSI compliant system.

For information that does not fit into the standard, there is also the
DATA_DICTIONARY schema. Use of tables in DATA_DICTIONARY is non-portable.

This allows developers to easily know if the query is portable or not.

Authentication, Authorization and Access
----------------------------------------

Plugins. Currently there are PAM and HTTP AUTH plugins for authentication.
Through the PAM plugin, you can use any PAM module (such as LDAP).

Command line clients
--------------------

We've stopped the confusion: -p means port and -P means password.

No gotcha of using the unix socket when localhost is specified and then
connecting you to the wrong database server.

There is no drizzleadmin command.

Storage Engines
---------------

 * MERGE storage engine has been removed
 * FEDERATED storage engine has been removed (all current development is
   focused on FederatedX, so having FEDERATED made no sense).
 * CSV engine is now for temporary tables only. See the filesystem_engine for
   the future of reading files as database tables.
 * MyISAM is for temporary tables only.
 * ARCHIVE is fully supported
 * PBXT is merged

FRM Files
---------

There are no FRM files in Drizzle. Engines now own their own metadata.
Some choose to still store these in files on disk. These are now in a
documented file format (using the google protobuf library).

SHOW commands
-------------

Several SHOW commands have been removed, replaced with INFORMATION_SCHEMA
or DATA_DICTIONARY views. All SHOW commands are aliases to INFORMATION_SCHEMA
queries. Our INFORMATION_SCHEMA implementation does not have the drawbacks
of the MySQL implementation.

 * SHOW ENGINES: use DATA_DICTIONARY

Removed commands
----------------

 * ALTER TABLE UPGRADE
 * REPAIR TABLE
 * CREATE FUNCTION
 * CONVERT
 * SET NAMES

Operators Removed
-----------------

Bit operators: &&, >>, <<, ~, ^, |, &

Removed functions
-----------------

 * crypt()
 * bit_length()
 * bit_count()

Keywords removed
----------------
 * BIT_AND
 * BIT_OR
 * BIT_XOR
 * CIPHER
 * CLIENT
 * CODE
 * CONTRIBUTORS
 * CPU
 * DEFINER
 * DES_KEY_FILE
 * ENGINES
 * EVERY
 * IO
 * IPC
 * ISSUSER

Objects Removed
---------------

 * There is no requirement for a 'mysql' schema.
 * There is no SET datatype, use ENUM.
 * There is no SET NAMES command, UTF-8 by default
 * There is no CHARSET or CHARACTER SET commands, everything defaults to UTF8
 * There is no TIME type, use DATETIME or INT.
 * There is no TINYINT, SMALLINT or MEDIUMINT. Integer operations have been optimized around 32 and 64 bit integers.
 * There are no TINYBLOB, MEDIUMBLOB and LONGBLOB datatypes. We have optimized a single BLOB container.
 * There are no TINYTEXT, MEDIUMTEXT and LONGTEXT datatypes. Use TEXT or BLOB.
 * There is no UNSIGNED (as per the standard).
 * There are no spatial data types GEOMETRY, POINT, LINESTRING & POLYGON (go use `Postgres <http://www.postgresql.org>`_).
 * No YEAR field type.
 * There are no FULLTEXT indexes for the MyISAM storage engine (the only engine FULLTEXT was supported in). Look at either Lucene, Sphinx, or Solr.
 * No "dual" table.
 * The "LOCAL" keyword in "LOAD DATA LOCAL INFILE" is not supported

1900.2.18 by Stewart Smith add section on notable differences from MySQL	1	=========================
	2	Notable MySQL Differences
	3	=========================
	4
	5	Drizzle was forked from the (now defunct) MySQL 6.0 tree. Since then there
	6	have been a lot of changes. Some areas are similar, others unrecognisable.
	7
1900.2.19 by Stewart Smith few cleanup to Differences between Drizzle and mySQL documentation	8	This section aims to explore some of the notable differences between MySQL
	9	and Drizzle.
	10
	11	This section was originally adapted from the Drizzle Wiki.
1900.2.18 by Stewart Smith add section on notable differences from MySQL	12
	13	Usage
	14	-----
	15	* There is no embedded server. The Drizzle Server is not loadable as a shared
	16	library.
	17	* Drizzle is optimized for massively concurrent environments. If we have the
	18	choice of improving performance for 1024 simultaneous connections to the
	19	detriment of performance with only 64 connections, we will take that choice.
	20	* Designed for modern POSIX systems
	21	* Microsoft Windows is not a supported platform (neither is HP-UX or IRIX).
	22	* No timezones. Everything is UTC.
	23
	24	Installation
	25	------------
	26
	27	* No scripts/mysql_install_db or similar. A "just works" installation
	28	mentality without administrative overhead.
	29	* No system database that needs upgrading between versions.
	30	* Drizzle can listen on the Drizzle port (4427) and/or MySQL port (3306)
	31	and speak the respective protocols.
	32
	33	Architecture
	34	------------
	35
	36	Drizzle is designed around the concept of being a micro-kernel. There should
	37	be a small core of the server with most functionality being provided through
	38	small, efficient and hard to misuse plugin interfaces. The goal is a small,
	39	light weight kernel which is easy to maintain, understand and extend.
	40
	41	Drizzle is written in C++ making use of the Standard Template Library (STL)
	42	and Boost. Only where performance or correctness proves to be inadequate will
	43	we consider rolling our own, and our preference is to fix the upstream library
	44	instead.
	45
	46	Network protocol
	47	----------------
	48
	49	Pluggable network protocols allow Drizzle to speak one (or more) of several
	50	protocols. Currently we support the MySQL protocol (compatible with existing
	51	MySQL client libraries) and the Drizzle protocol which is still under
	52	development but aims for several important differences:
	53
	54	* Client sends first packet instead of server
	55	* built in sharding
	56	* multi statement support (without using a semicolon to separate them)
	57	* room for expansion to include NoSQL type commands inline with SQL.
	58
	59	There is also a console plugin that instead of providing access over a network
	60	socket, allows access from the current tty.
	61
	62	Plugin API
	63	----------
	64
	65	Existing plugin APIs inherited from MySQL have been reworked.
	66
	67	* User Defined Functions (UDFs) now follow the same API as within the
	68	server instead of a different C API. This means that UDFs are on the
	69	exact same level as builtin functions.
	70	* Storage Engine API has had some parts extensively reworked, especially
	71	around transactions and DDL.
	72	* Logging is now pluggable
	73	* Authentication is pluggable
	74	* Replication is pluggable
	75	* INFORMATION_SCHEMA plugins have been replaced by the function_engine, which
76	is a lot more space and time efficient.
77	* Network protocols are pluggable
78	* Scheduler is pluggable (multi_thread, pool_of_threads etc)
79	* Plugin points for manipulating rows before/after operations: used for
80	replication and the PBMS Blob Streaming plugin.
81
82	Stored Procedures
83	-----------------
84
85	Drizzle does not currently have any plugins implement stored procedures. We
86	viewed the implementation in MySQL to be non-optimal, bloating the parser
87	and only supporting one language (SQL2003 stored procedures) which was not
88	well known.
89
90	Fundamentally, stored procedures usually are not the correct architectural
91	decision for applications that need to scale. Pushing more computation down
92	into the database (which is the trickiest layer to scale) isn't a good idea.
93
94	We do recognise that the ability to reduce the time row locks are held
95	by using stored procedures is valuable, but think the same advantage can
96	be gotten with improved batching of commands over the wire instead of adding
97	administering stored procedures to the list of things that can go wrong in
98	admisistering the database.
99
100	Triggers
101	--------
102
103	Drizzle does not currently have any plugin that provides SQL triggers. We
104	have some hooks for callbacks inside the server so that plugins can hook
105	into points that triggers could.
106
107	Views
108	-----
109
110	SQL Views are not currently supported in Drizzle. We believe they should be
111	implemented via a query rewrite plugin. See the `Query Rewrite Blueprint <https://blueprints.launchpad.net/drizzle/+spec/query-rewrite>`_ on launchpad.
112
113	Partitioning
114	------------
115
116	INFORMATION_SCHEMA
117	------------------
118
119	The INFORMATION_SCHEMA in Drizzle is strictly ANSI compliant. If you write
120	a query to any of the tables in the INFORMATION_SCHEMA in Drizzle, you can
121	directly run these on any other ANSI compliant system.
122
123	For information that does not fit into the standard, there is also the
124	DATA_DICTIONARY schema. Use of tables in DATA_DICTIONARY is non-portable.
125
126	This allows developers to easily know if the query is portable or not.
127
128	Authentication, Authorization and Access
129	----------------------------------------
130
131	Plugins. Currently there are PAM and HTTP AUTH plugins for authentication.
132	Through the PAM plugin, you can use any PAM module (such as LDAP).
133
134	Command line clients
135	--------------------
136
137	We've stopped the confusion: -p means port and -P means password.
138
139	No gotcha of using the unix socket when localhost is specified and then
140	connecting you to the wrong database server.
141
142	There is no drizzleadmin command.
143
144	Storage Engines
145	---------------
146
147	* MERGE storage engine has been removed
148	* FEDERATED storage engine has been removed (all current development is
149	focused on FederatedX, so having FEDERATED made no sense).
150	* CSV engine is now for temporary tables only. See the filesystem_engine for
151	the future of reading files as database tables.
152	* MyISAM is for temporary tables only.
153	* ARCHIVE is fully supported
154	* PBXT is merged
155
156	FRM Files
157	---------
158
159	There are no FRM files in Drizzle. Engines now own their own metadata.
160	Some choose to still store these in files on disk. These are now in a
161	documented file format (using the google protobuf library).
162
163	SHOW commands
164	-------------
165
166	Several SHOW commands have been removed, replaced with INFORMATION_SCHEMA
167	or DATA_DICTIONARY views. All SHOW commands are aliases to INFORMATION_SCHEMA
168	queries. Our INFORMATION_SCHEMA implementation does not have the drawbacks
169	of the MySQL implementation.
170
171	* SHOW ENGINES: use DATA_DICTIONARY
172
173	Removed commands
174	----------------
175
176	* ALTER TABLE UPGRADE
177	* REPAIR TABLE
178	* CREATE FUNCTION
179	* CONVERT
180	* SET NAMES
181
182	Operators Removed
183	-----------------
184
185	Bit operators: &&, >>, <<, ~, ^, \|, &
186
187	Removed functions
188	-----------------
189
190	* crypt()
191	* bit_length()
192	* bit_count()
193
194	Keywords removed
195	----------------
196	* BIT_AND
197	* BIT_OR
198	* BIT_XOR
199	* CIPHER
200	* CLIENT
201	* CODE
202	* CONTRIBUTORS
203	* CPU
204	* DEFINER
205	* DES_KEY_FILE
206	* ENGINES
207	* EVERY
208	* IO
209	* IPC
210	* ISSUSER
211
212	Objects Removed
213	---------------
214
215	* There is no requirement for a 'mysql' schema.
216	* There is no SET datatype, use ENUM.
217	* There is no SET NAMES command, UTF-8 by default
218	* There is no CHARSET or CHARACTER SET commands, everything defaults to UTF8
219	* There is no TIME type, use DATETIME or INT.
220	* There is no TINYINT, SMALLINT or MEDIUMINT. Integer operations have been optimized around 32 and 64 bit integers.
221	* There are no TINYBLOB, MEDIUMBLOB and LONGBLOB datatypes. We have optimized a single BLOB container.
222	* There are no TINYTEXT, MEDIUMTEXT and LONGTEXT datatypes. Use TEXT or BLOB.
223	* There is no UNSIGNED (as per the standard).
224	* There are no spatial data types GEOMETRY, POINT, LINESTRING & POLYGON (go use `Postgres <http://www.postgresql.org>`_).
225	* No YEAR field type.
226	* There are no FULLTEXT indexes for the MyISAM storage engine (the only engine FULLTEXT was supported in). Look at either Lucene, Sphinx, or Solr.
227	* No "dual" table.
228	* The "LOCAL" keyword in "LOAD DATA LOCAL INFILE" is not supported