~drizzle-trunk/drizzle/development

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.

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.

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.

* It is designed for modern POSIX systems

* Microsoft Windows is not a supported platform (neither is HP-UX or IRIX).

* Drizzle doesn't use timezones. Everything is UTC.

Installation

------------

* No scripts/mysql_install_db or similar. Drizzle aims for a "just works" installation, 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 microkernel. 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 that is easy to maintain, understand and extend.

Drizzle is written in C++ and makes use of the Standard Template Library (STL)

and Boost. Only where performance or correctness proves to be inadequate will

we consider rolling our own; 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.

The Drizzle protocol embodies several important differences from MySQL:

* 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 -- instead of providing access over a network

socket, this plugin allows access from the current tty.

Plugin API

----------

The existing plugin APIs that Drizzle inherited from MySQL have been reworked.

* User Defined Functions (UDFs) now follow the same API as a given

server instead of a different C API. This means that UDFs are on the

exact same level as built-in 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 that 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 recognize that the ability to reduce the time row locks are held

by using stored procedures is valuable, but think we can achieve the same

advantage by improved batching of commands over the wire instead of adding and

administering stored procedures to the list of things that can go wrong in

administering the database.

Triggers

--------

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

108

implemented via a query rewrite plugin. See the `Query Rewrite Blueprint <https://blueprints.launchpad.net/Drizzle/+spec/query-rewrite>`_ on launchpad.

109

110

Partitioning

111

------------

112

113

INFORMATION_SCHEMA

114

------------------

115

116

The INFORMATION_SCHEMA provides access to database metadata.

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

130

Authentication lies in Drizzle plugins. Currently there are PAM and HTTP AUTH plugins for authentication.

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

141

There is no Drizzle admin command.

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.

159

Some still choose to store these in files on disk. These are now in a

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

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.

181

182

Operators Removed

183

-----------------

184

185

Bit operators

186

* &&

187

* >>

188

* <<

189

* ~

190

* ^

191

* '|'

192

* &

193

194

Removed functions

195

-----------------

196

197

* crypt()

198

* bit_length()

199

* bit_count()

200

201

Keywords removed

202

----------------

203

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

Older »