Liveblogging: Extending MySQL by Brian “Krow” Aker
Brian wins the award for “most frequent great quotes during a talk”.
Before MySQL 5.1 a UDF was the only way to extend MySQL.
All you need in a UDF is: init() execute() deinit()
my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)
UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args
WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.
“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”
In MySQL 5.1, you can now install plugins (example is memcache_servers plugin):
mysql> INSTALL PLUGIN memcache_servers SONAME 'libmemcache_engine.so'
In the plugin architecture, there’s a callback mechanism to call a structure and then the structure is self-hosted into the environment. mysql_declare_plugin() is usually found at the bottom of the file because that’s how Brian wrote the example….
A storage engine is really about connecting to anything.
MySQL has a parser, and optimizer, and a retrieve/store part, which talks to the storage engines. The engine interface is written in C++.
Quote about the BLACKHOLE storage engine: “It’s amazing how much money can be made out of something that does NOTHING.”
What about defining features?
tables_flags()
index_flags()
handlerton flags
Table object methods:
base_ext()
create()
delete_table()
rename_table()
optimize(), repair(), etc.
As you can see, the API is pretty specific to actions that are happening at the SQL layer.
Reads
rnd_init() — scan is about to begin
rnd_next() — get the next record
(rnd = random or read next data)
index_first() — index read is about to begin
index_next() — get the next record in the index
Writes:
write_row() — “here’s new data, please store this”.
delete_row()
update_row()
on delete or update, MySQL starts reading the rows and once the predicate matches, then the delete_row() or update_row() is called. update_row() will be passed the before and after image of the row.
What about information_schema?
The information_schema API is an easy way to get more information into the information_schema tables.
(there’s a really nice example of an information_schema plugin)
A quote about his code — “That in the middle there looks like a bug….but it’s never called, so that’s OK. That’s what I get for copying and pasting my own examples.”
The plugin is responsible for all the locking.
Daemon plugins created because otherwise people would “bastardize the storage engine plugins.”
A daemon plugin has full access to the server internals, and it is a simple interface to be able to extend.
“If you’re really going to shoot yourself in the foot, you really want to go full blast.”
UDP Daemon just allows UDP packets to be put into the db (instead of TCP/IP with its darn authentication schema). Simplest interface to MySQL that exists. No connector, just UDP. The point is you can put other listeners to MySQL, such as an HTTP listener.
Daemon plugins are also a way to
code for examples
skeleton engine
Liveblogging: Extending MySQL by Brian “Krow” Aker
Brian wins the award for “most frequent great quotes during a talk”.
Before MySQL 5.1 a UDF was the only way to extend MySQL.
All you need in a UDF is: init() execute() deinit()
my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)
UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args
WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.
“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”
In MySQL 5.1, you can now install plugins (example is memcache_servers plugin):
mysql> INSTALL PLUGIN memcache_servers SONAME 'libmemcache_engine.so'
In the plugin architecture, there’s a callback mechanism to call a structure and then the structure is self-hosted into the environment. mysql_declare_plugin() is usually found at the bottom of the file because that’s how Brian wrote the example….
A storage engine is really about connecting to anything.
MySQL has a parser, and optimizer, and a retrieve/store part, which talks to the storage engines. The engine interface is written in C++.
Quote about the BLACKHOLE storage engine: “It’s amazing how much money can be made out of something that does NOTHING.”
What about defining features?
tables_flags()
index_flags()
handlerton flags
Table object methods:
base_ext()
create()
delete_table()
rename_table()
optimize(), repair(), etc.
As you can see, the API is pretty specific to actions that are happening at the SQL layer.
Reads
rnd_init() — scan is about to begin
rnd_next() — get the next record
(rnd = random or read next data)
index_first() — index read is about to begin
index_next() — get the next record in the index
Writes:
write_row() — “here’s new data, please store this”.
delete_row()
update_row()
on delete or update, MySQL starts reading the rows and once the predicate matches, then the delete_row() or update_row() is called. update_row() will be passed the before and after image of the row.
What about information_schema?
The information_schema API is an easy way to get more information into the information_schema tables.
(there’s a really nice example of an information_schema plugin)
A quote about his code — “That in the middle there looks like a bug….but it’s never called, so that’s OK. That’s what I get for copying and pasting my own examples.”
The plugin is responsible for all the locking.
Daemon plugins created because otherwise people would “bastardize the storage engine plugins.”
A daemon plugin has full access to the server internals, and it is a simple interface to be able to extend.
“If you’re really going to shoot yourself in the foot, you really want to go full blast.”
UDP Daemon just allows UDP packets to be put into the db (instead of TCP/IP with its darn authentication schema). Simplest interface to MySQL that exists. No connector, just UDP. The point is you can put other listeners to MySQL, such as an HTTP listener.
Daemon plugins are also a way to
code for examples
skeleton engine