Tag: development

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 […]

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 […]

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 mysqld –help –verbose since MariaDB 10.1.0), numeric variables have […]

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 […]

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 mysql_upgrade tool).

1. Create a stored procedure with IF statements inside, call it once and drop it. This requires the user to have the CREATE ROUTINE privilege and mysql.proc table must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
2. 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,
                     server_id INTEGER UNSIGNED NOT NULL,
                     command_type VARCHAR(64) NOT NULL,
                     argument MEDIUMTEXT NOT NULL
                   ) engine=CSV CHARACTER SET utf8 comment="General log"',
                  'SET @dummy = 0');
   
   PREPARE stmt FROM @str;
   EXECUTE stmt;
   DROP PREPARE stmt;
   

   which is not very readable and doesn’t work well if you need to execute many statements conditionally.