You can find a â€˜Quick Usage Tourâ€™ in our documentation. In this post, Iâ€™m going to walk you through that tour and elaborate on a few things. Enjoy! Things to consider Iâ€™m assuming youâ€™ve already installed MySQL. Sphinx does not require that you use MySQL, but the following examples do. Iâ€™m installing Sphinx on Ubuntu [...]
MariaDB 10.1 server is now “Galera ready” with the latest 10.1.1 release. It includes wsrep (write set replication) patch that enables server to load the wsrep provider (galera) library and interact with it to provide multi-master synchronous replication support. The patch implements hooks inside server and storage engines to populate and apply the write sets on sender and receiver nodes in a cluster respectively. The wsrep patch also adds a number of system and status variables (prefixed with wsrep) that can be used to configure and monitor the server acting as a node in Galera cluster.
Unlike older MariaDB versions, the wsrep patch is now part of regular …[Read more]
A Wolf, a Dolphin and a Sphinx walk into a bar… nevermind. We’ll skip the jokes. This post is about using SSL to set up a secure connection between MySQL and Sphinx. Serious stuff! The Idea It simple. Weâ€™re going to use Sphinx to index some data from MySQL across a secure connection. So, weâ€™ll [...]
Sometimes users ask for something that doesn’t really make sense. On the first glance. But then you start asking and realize that the user was right, you were wrong, and it is, actually, a perfectly logical and valid use case.
I’ve had one of these moments when I’ve heard about a request of making triggers to work on the slave in the row-based replication. Like, really? In RBR all changes made by triggers are replicated from the master to slaves as row events. If triggers would be fired on the slave they would do their changes twice. And anyway, assuming that one only has triggers one the slave (why?) in statement-based replication triggers would run on the slave normally, wouldn’t they?
Well, yes, they would, but one cannot always use statement-based replication. If one could, RBR would’ve never been implemented. There are many cases that statement-based replication cannot handle correctly. Galera requires RBR too. And as …[Read more]
Let me start with a little story. You sit in your house near the fireplace in the living room and need a book from the library… Eh, no, sorry, wrong century. You’re building a robotic arm that will open your beer or brew your coffee or supply you with whatever other drinks of your choice… while you’ll be building the next robotic arm. So, you — soldering iron in one hand and Arduino in another — ask your little brother to bring a box with specific resistors (that you unexpectedly run out of) from the cellar. The problem — your brother is small and cannot tell a resistor from a respirator. You explain that it’s small thing with two wires sticking out of it. And he starts going back and forth brining you boxes after boxes of different small things with two wires.
This is approximately where we were in MySQL when NDB Cluster was
just added. The use wants to find a row, say
number_of_wires=2 AND size='small' AND …
I don’t think it’ll surprise anybody if I say that MariaDB or
MySQL server knows a lot more about server system variables, then
just their values. Indeed, every variable can be session or
global only, read-only or writable, it has an associated help
text (that is printed on
mysqld --help --verbose),
certain variables only accept values from a given set of strings
(this set of allowed values is also printed in
--help --verbose since MariaDB 10.1.0), numeric variables
have lower and upper range boundaries of valid values (that are
never printed anywhere), and so on. I always thought it’s kind of
a waste that there is no way to query this information. That
could’ve been very convenient, in particular for various GUI
clients — they could show the help in tooltips, validate values
and so on.
But recently we’ve got our users asking for it — precisely, for system variable metadata, whether a variable …[Read more]
When you e.g. delete rows, these rows are just marked as deleted
not really physically deleted from indexes and free space
introduced is not returned to operating system for later reuse.
Purge thread will physically delete index keys and rows, but
still free space introduced is not returned to operating system
and this operation can lead holes on page. If you have variable
length rows, this could lead to situation where this free space
can’t be used for new rows (if these rows are larger than old
ones). User may use
OPTIMIZE TABLE or
ALTER TABLE <table> ENGINE=InnoDB to reconstruct the table.
against an InnoDB table stored in the shared …
Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.
InnoDB storage engine allocates temporal memory buffer for transaction logging in phase 1 where row changes during this phase are logged. Size of this buffer is at start sort_buffer_size and it can be grown up to innodb_online_alter_log_max size. During phase 2 thread processing the ALTER statement will copy old table’s rows to a new altered table. After this MariaDB will take exclusive lock for target table and applies row log buffer to the new altered table.
This introduces a new unpredictable failure case row log buffer overflow. MariaDB server will rollback ALTER statement if row log buffer …[Read more]
One of the most popular plugin types both in MariaDB and MySQL is INFORMATION_SCHEMA plugin type. INFORMATION_SCHEMA plugins add new tables to the INFORMATION_SCHEMA. There are lots of INFORMATION_SCHEMA plugins, because they can be used to show just anything to the user and are very easy to write.
MariaDB 10.1.1 comes with nine INFORMATION_SCHEMA plugin:
- Feedback — shows the anonymised server usage information and can optionally send it to the configured url.
- Locales — lists compiled-in server locales, implemented by Roberto Spadim
- METADATA_LOCK_INFO — Lists metadata locks in the server. Implemented by Kentoku Shiba
Every now and then there is a need to execute certain SQL
statements conditionally. Easy, if you do it from your PHP (or
Java or whatever) application. But if all you have is pure SQL?
There are two techniques that MariaDB and MySQL use in the
mysql_fix_privilege_tables.sql script (applied by
- Create a stored procedure with
IFstatements inside, call it once and drop it. This requires the user to have the
CREATE ROUTINEprivilege and
mysql.proctable must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
- Use dynamic SQL, like
SET @str = IF (@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log ( event_time TIMESTAMP(6) NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, …