~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

Here are some specific SHOW differences:

171

172

* SHOW ENGINES: use DATA_DICTIONARY

173

* SHOW CREATE TABLE: specifies the AUTO_INCREMENT at CREATE/ALTER TABLE time,

174

not the current AUTO_INCREMENT

175

* SHOW TABLE STATUS: only shows tables in the current table cache

176

* SHOW [ENGINE] STATUS: use the DATA_DICTIONARY tables for that engine

177

178

Removed commands

179

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

180

181

* ALTER TABLE UPGRADE

182

* REPAIR TABLE

183

* CREATE FUNCTION

184

* CONVERT

185

* SET NAMES

186

* 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.

187

188

Operators Removed

189

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

190

191

Bit operators

192

* &&

193

* >>

194

* <<

195

* ~

196

* ^

197

* '|'

198

* &

199

200

Removed functions

201

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

202

203

* crypt()

204

* bit_length()

205

* bit_count()

206

207

Keywords removed

208

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

209

210

* BIT_AND

211

* BIT_OR

212

* BIT_XOR

213

* CIPHER

214

* CLIENT

215

* CODE

216

* CONTRIBUTORS

217

* CPU

218

* DEFINER

219

* DES_KEY_FILE

220

* ENGINES

221

* EVERY

222

* IO

223

* IPC

224

* ISSUSER

225

226

Objects Removed

227

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

228

229

* There is no requirement for a 'mysql' schema.

230

* There is no SET datatype, use ENUM.

231

* There is no SET NAMES command, UTF-8 by default

232

* There is no CHARSET or CHARACTER SET commands, everything defaults to UTF8

233

* There is no TIME type, use DATETIME or INT.

234

* There is no TINYINT, SMALLINT or MEDIUMINT. Integer operations have been optimized around 32 and 64 bit integers.

235

* There are no TINYBLOB, MEDIUMBLOB and LONGBLOB datatypes. We have optimized a single BLOB container.

236

* There are no TINYTEXT, MEDIUMTEXT and LONGTEXT datatypes. Use TEXT or BLOB.

237

* There is no UNSIGNED (as per the standard).

238

* There are no spatial data types GEOMETRY, POINT, LINESTRING & POLYGON (go use `Postgres <http://www.postgresql.org>`_).

239

* No YEAR field type.

240

* There are no FULLTEXT indexes for the MyISAM storage engine (the only engine FULLTEXT was supported in). Look at either Lucene, Sphinx, or Solr.

241

* No "dual" table.

242

* The "LOCAL" keyword in "LOAD DATA LOCAL INFILE" is not supported

Older »