OurSQL Episode 174: CONNECT the world

This week we discuss MariaDB's CONNECT storage engine. Ear Candy is inconsistent behavior with dates, NULLs and collations. At the Movies is a talk about the history and future of MySQL and its variants.

Sheeri:
The 2014 MySQL Community Awards are open for nominations until Sunday, 23 February 2014

Events
DB Hangops - every other Wednesay at noon Pacific time. Upcoming dates are Wednesday February 19th, March 5th and March 19th.

Upcoming MySQL events
Melbourne Tech Tour, February 25th
Auckland Tech Tour, February 27th
Canberra Tech Tour, March 4th
Brisbane Tech Tour, March 5th
Sydney Tech Tour, March 6th

Training
SkySQL Trainings

Tungsten University trainings

Oracle's MySQL Trainings

Percona Trainings

MariaDB CONNECT Engine
An introduction to MariaDB's CONNECT storage engine.
Serge Frezefond has some presentations about the MariaDB Storage engine:
CONNECT Storage engine (21 slides)
CONNECT Storage engine (37 slides)

Web-based MariaDB Repository Configuration Generator to set up your system to use a MariaDB repository.

Installing the plugin:
INSTALL PLUGIN CONNECT SONAME 'ha_connect';

SHOW PLUGINS;
+----------------------------+----------+--------------------+---------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CONNECT | ACTIVE | STORAGE ENGINE | ha_connect.so | GPL |
+----------------------------+----------+--------------------+---------------+---------+

SHOW ENGINES;
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |

| CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |

| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+

CONNECT table types, data files

Example for connecting to a CSV file:

first,last,birthday
"Gerry","Narvaja","Aug 23"
"Sheeri","Cabral","Sep 17"

CREATE TABLE users_csv (
   first  varchar(32) NOT NULL,
   last   varchar(32) NOT NULL,
   birthday  varchar(50) NOT NULL
) ENGINE=CONNECT  TABLE_TYPE =CSV  FILE_NAME ='/var/lib/mysql/users.csv' HEADER=1  SEP_CHAR=','  QUOTED=1;

SELECT * FROM users_csv;
+--------+-----------+--------+
| first   | last      | birthday  |
+--------+-----------+--------+
| Gerry  | Narvaja | Aug 23  |
| Sheeri | Cabral | Sep 17 |
+--------+-----------+--------+

Now we go a little further and try inserting data into the table:
INSERT INTO users_csv (first, last, birthday) VALUES ('Tony','Cabral','Jan 27');

SELECT * FROM users_csv;
+--------+-----------+--------+
| first   | last      | birthday  |
+--------+-----------+--------+
| Gerry  | Narvaja | Aug 23  |
| Sheeri | Cabral | Sep 17 |
| Tony   | Cabral | Jan 27 |
+--------+-----------+--------+

Example for connecting to an INI file:

CREATE TABLE `myini` (
  `section` char(16) DEFAULT NULL `FLAG`=1,
  `keyname` char(64) DEFAULT NULL `FLAG`=2,
  `keyvalue` char(255) DEFAULT NULL
) ENGINE=CONNECT `TABLE_TYPE`=INI `FILE_NAME`='/etc/mysql/my.cnf' `OPTION_LIST`='layout=row';

Look at non-commented out options:
SELECT * FROM myini WHERE keyname NOT LIKE '#%';

Example of connecting to an sqlite database:
List of Skype tables:

sqlite> .tables
Accounts          ChatMembers       DbMeta            SMSes          
Alerts            Chats             LegacyMessages    Transfers      
AppSchemaVersion  ContactGroups     MediaDocuments    VideoMessages  
CallMembers       Contacts          Messages          Videos         
Calls             Conversations     Participants      Voicemails 

Get the schema for the DbMeta table:

sqlite> .schema DbMeta
CREATE TABLE DbMeta (key TEXT NOT NULL PRIMARY KEY, value TEXT);

CREATE TABLE skype_main (
`key` varchar(255) not null,
`value` varchar(255)
) ENGINE=CONNECT TABLE_TYPE='ODBC' TABNAME='DbMeta' CONNECTION='Driver=SQLite3;Database=/var/lib/mysql/skype_main.db;Version=3’;

Another way to connect to an sqlite database or flat file using MariaDB

Ear Candy
Strange MySQL behavior with dates and collations, also known as bug 71658.

Try this on your version: SELECT DATE_ADD(NULL, INTERVAL 1 DAY) > NOW();

MariaDB v5.5.35 --> NULL
MySQL v5.6.14 --> Error Code: 1267. Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation

Example views:

mysql> create view vvv as select now() c1, date_add(now(), interval 1 day) c2;
Query OK, 0 rows affected (0.23 sec)

mysql> desc vvv;
+-------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------------------+-------+
| c1 | datetime | NO | | 0000-00-00 00:00:00 | |
| c2 | datetime | YES | | NULL | |
+-------+----------+------+-----+---------------------+-------+
2 rows in set (0.15 sec)

mysql> create view vvv2 as select now() c1, date_add(null, interval 1 day) c2;
Query OK, 0 rows affected (0.05 sec)

mysql> desc vvv2;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+-------+
| c1 | datetime | NO | | 0000-00-00 00:00:00 | |
| c2 | varchar(29) | YES | | NULL | |
+-------+-------------+------+-----+---------------------+-------+
2 rows in set (0.05 sec)

At the movies
Stewart Smith's presentation about the history and future of MySQL and its variants at Linux Conf Australia last month.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast