Archive for the ‘MariaDB’ Category

MariaDB: subquery causes table scan

I got asked today to look at some slow queries on a MariaDB 10 instance. Here are the anonymized results of the investigation I did into this and how I solved the issue… There were a bunch of queries, expected to run very fast, that were taking 10-15 seconds to execute. The queries were all […]

Recover a single table from a mysqldump

I needed to recover the data, from a single table, from a mysqldump containing all the databases from an entire instance. A quick google yielded this result. This produced a nifty little sed one-liner… ?View Code BASHsed -n -e ‘/CREATE TABLE.*your_table_name/,/CREATE TABLE/p’ mysqldump_file.sql > your_table_name.sql I also wanted to import the data into a different […]

MariaDB Compound Statements Outside Stored Procedures

It’s always been a small annoyance that the MySQL / MariaDB flavour of SQL wouldn’t allow you to use if else logic or loops outside of a stored procedure or trigger. There were ways around  this but it’s not as nice if you’re coming from TSQL. This is rectified in MariaDB from 10.1.1. One thing that is […]

TokuDB file & table sizes with information_schema

Here’s a few queries using the information_schema.TokuDB_fractal_tree_info to get the on disk size in MB for TokuDB tables. This first one will sum up the on disk size for tables using the TokuDB engine. ?View Code MYSQLSELECT table_schema, table_name, SUM(ROUND(bt_size_allocated / 1024 / 1024, 2)) AS table_size_mb FROM information_schema.`TokuDB_fractal_tree_info` WHERE table_schema = ‘database_name’ GROUP BY […]

The behavior of gtid_strict_mode in MariaDB

GTIDs in MariaDB are a nice improvement to replication and make fail-over a simpler process. I struggled a little with the explanation of gtid_strict_mode and what to expect. So I thought I’d run through a simple scenario to make my own understanding clear. In this scenario I have created two instances of MySQL; primary and secondary. […]