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

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

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