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

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

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