Assuming you have successfully compiled Drizzle , and you are ready to start for the first time, here are some beginner differences with those familiar with the current MySQL 5.1 GA version. Note: drizzle is actually an original fork of the 6.0.x codebase however the code looks very little like mysql anymore.
Getting started differences
The first default MySQL installation step scripts/install_mysql_db is gone, and there is no replacement. The ‘mysql’ schema no longer exists. Talking about schemas, ‘test’ is also gone by default.
While a bin/drizzled_safe exists to start drizzle, this presently doesn’t operate as expected . You should start drizzle with sbin/drizzled &
They still have an empty password for ‘root’. Would be nice if it was at least a default like an Oracle installation, and a password became a mandatory requirement. Speaking of passwords, trying ‘drizzle -uroot -pxxxx’ will result in an unexpected error.
$ bin/drizzle -uroot -pxxx ERROR: Non-integer value supplied for port. If you are trying to enter a password please use --password instead.
Taking a look around at some common mysql commands you may be familiar with.
Information Schema
drizzle> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) drizzle> use information_schema; drizzle> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | PLUGINS | | PROCESSLIST | | REFERENTIAL_CONSTRAINTS | | SCHEMATA | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | INNODB_CMP | | INNODB_CMP_RESET | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_LOCKS | | INNODB_TRX | +---------------------------------------+ 23 rows in set (0.00 sec)
Gone are COLUMN_PRIVILEGES ENGINES EVENTS FILES KEY_COLUMN_USAGE PARTITIONS PROFILING ROUTINES SCHEMA_PRIVILEGES TABLE_PRIVILEGES TRIGGERS USER_PRIVILEGES VIEWS. With MySQL privileges and a number of 5.x features removed, that’s expected. I was surprised to see ENGINES gone. More on that later.
SIDE NOTE: Disappointed to see (0.00 sec) still around. I would have expected the client interface could have had an overhaul for timing output, and report say 0.00 milliseconds.
Variables
drizzle> show variables +----------------------------------------+---------------------------------------------+ | Variable_name | Value | +----------------------------------------+---------------------------------------------+ | archive_aio | OFF | .. | innodb_change_buffering | inserts | | innodb_file_format | Antelope | | innodb_file_format_check | Antelope | | innodb_replication_delay | 0 | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_use_sys_malloc | ON | | innodb_version | 1.0.3 | ... | logging_query_enable | OFF | | logging_query_filename | | | logging_query_threshold_big_examined | 0 | | logging_query_threshold_big_resultset | 0 | | logging_query_threshold_slow | 0 | | logging_syslog_enable | OFF | | logging_syslog_facility | local0 | | logging_syslog_ident | drizzled | | logging_syslog_priority | info | | logging_syslog_threshold_big_examined | 0 | | logging_syslog_threshold_big_resultset | 0 | | logging_syslog_threshold_slow | 0 | ... | multi_thread_max_threads | 2048 | | myisam_block_size | 1024 | | optimizer_switch | | | optimizer_use_mrr | force | | pool_of_threads_size | 8 | | replicator_directory | | | replicator_enabled | OFF | 161 rows in set (0.01 sec)
MySQL 5.1 has some 265 variables values, so huge changes here. The big sections gone are around CHARACTER SETS, SSL, REPLICATION, QUERY_CACHE, LOG, FULL TEXT. The above list shows new variables, some of these are Innodb plugin related.
Status
drizzle> show status ... 112 rows in set (0.01 sec)
MySQL 5.1 has some 290 status and Drizzle currently 112. Same suspects, REPLICATION, SSL, QUERY_CACHE, a lot of COM_ commands, all INNODB related status and THREADS_CREATED and THREADS_RUNNING. Nothing new from Drizzle.
Engines
drizzle> show engines; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'engines' at line 1 drizzle> show plugins; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'plugins' at line 1
An initial surprise here, but knowing that all work is converting to plugins, you found this all in INFORMATION_SCHEMA.PLUGINS
drizzle> select * from plugins; +---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+ | PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_LIBRARY | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | +---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+ | binlog | 1.0 | ACTIVE | STORAGE ENGINE | NULL | MySQL AB | This is a pseudo storage engine to represent the binlog in a transaction | GPL | | compress | 1.0 | ACTIVE | UDF | NULL | Stewart Smith | UDF for compress() | GPL | | crc32 | 1.0 | ACTIVE | UDF | NULL | Stewart Smith | UDF for computing CRC32 | GPL | | logging_query | 0.2 | ACTIVE | LOGGER | NULL | Mark Atwood mark @fallenpegasus.com | Log queries to a CSV file | GPL | | logging_syslog | 0.2 | ACTIVE | LOGGER | NULL | Mark Atwood mark @fallenpegasus.com | Log to syslog | GPL | | multi_thread | 0.1 | ACTIVE | SCHEDULING | NULL | Brian Aker | One Thread Per Session Scheduler | GPL | | pool_of_threads | 0.1 | ACTIVE | SCHEDULING | NULL | Brian Aker | Pool of Threads Scheduler | GPL | | replicator | 0.1 | ACTIVE | REPLICATOR | NULL | Brian Aker | Basic replication module | GPL | | signal_handler | 0.1 | ACTIVE | DAEMON | NULL | Brian Aker | Default Signal Handler | GPL | | single_thread | 0.1 | ACTIVE | SCHEDULING | NULL | Brian Aker | Single Thread Scheduler | GPL | | uncompressed_length | 1.0 | ACTIVE | UDF | NULL | Stewart Smith | UDF for compress() | GPL | | uncompress | 1.0 | ACTIVE | UDF | NULL | Stewart Smith | UDF for compress() | GPL | | ARCHIVE | 3.5 | ACTIVE | STORAGE ENGINE | NULL | Brian Aker, MySQL AB | Archive storage engine | GPL | | CSV | 1.0 | ACTIVE | STORAGE ENGINE | NULL | Brian Aker, MySQL AB | CSV storage engine | GPL | | MEMORY | 1.0 | ACTIVE | STORAGE ENGINE | NULL | MySQL AB | Hash based, stored in memory, useful for temporary tables | GPL | | InnoDB | 1.0.1 | ACTIVE | STORAGE ENGINE | NULL | Innobase Oy | Supports transactions, row-level locking, and foreign keys | GPL | | INNODB_TRX | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | InnoDB transactions | GPL | | INNODB_LOCKS | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | InnoDB conflicting locks | GPL | | INNODB_LOCK_WAITS | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | InnoDB which lock is blocking which | GPL | | INNODB_CMP | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | Statistics for the InnoDB compression | GPL | | INNODB_CMP_RESET | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | Statistics for the InnoDB compression; reset cumulated counts | GPL | | INNODB_CMPMEM | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | Statistics for the InnoDB compressed buffer pool | GPL | | INNODB_CMPMEM_RESET | 1.0.2 | ACTIVE | INFORMATION SCHEMA | NULL | Innobase Oy | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | GPL | | MyISAM | 1.0 | ACTIVE | STORAGE ENGINE | NULL | MySQL AB | Default engine as of MySQL 3.23 with great performance | GPL | +---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+ 24 rows in set (0.00 sec)
This is where we start to see some significant differences. Storage engines are now all plugins. We see some entire new features for SCHEDULING, LOGGING and REPLICATOR.
drizzle> select plugin_type,count(*) from information_schema.plugins group by plugin_type order by 2 desc; +--------------------+----------+ | plugin_type | count(*) | +--------------------+----------+ | INFORMATION SCHEMA | 7 | | STORAGE ENGINE | 6 | | UDF | 4 | | SCHEDULING | 3 | | LOGGER | 2 | | REPLICATOR | 1 | | DAEMON | 1 | +--------------------+----------+ 7 rows in set (0.00 sec)
SHOW Commands
Drizzle supports the following SHOW Commands.
SHOW COLUMNS FROM information_schema.tables; SHOW CREATE DATABASE information_schema; SHOW DATABASES; SHOW ENGINE INNODB STATUS; SHOW ERRORS; SHOW INDEX FROM information_schema.tables; SHOW OPEN TABLES; SHOW PROCESSLIST; SHOW STATUS; SHOW TABLE STATUS; SHOW TABLES; SHOW VARIABLES; SHOW WARNINGS; SHOW CREATE SCHEMA; SHOW SCHEMAS;
The following are no longer valid.
AUTHORS, CHARACTER SET, COLLATION, CONTRIBUTORS, EVENT event_name, FUNCTION func_name, PROCEDURE proc_name, TRIGGER trigger_name, VIEW view_name, ENGINES, EVENTS, FUNCTION CODE func_name, FUNCTION STATUS, GRANTS FOR , INNODB STATUS, PLUGINS, PROCEDURE CODE proc_name, PROCEDURE STATUS, PRIVILEGES, PROFILE, PROFILES, SCHEDULER STATUS, TRIGGERS, BINARY LOGS, MASTER LOGS, BINLOG EVENTS, MASTER STATUS, SLAVE HOSTS, SLAVE STATUS,
