'SQL' Tag

  • Some observations on MySQL to sqlite migration & compatibility

    January 30, 2017

    I'm experimenting with sqlite as backend database for orchestrator. While orchestrator manages MySQL replication topologies, it also uses MySQL as backend. For some deployments, and I'm looking into such one, having MySQL as backend is a considerable overhead. This sent me to the route of looking into a self contained orchestrator binary + backend DB. I would […]

  • Three wishes for a new year

    September 28, 2016

    (Almost) another new year by Jewish calendar. What do I wish for the following year? World peace Good health to all Relaxed GTID constraints I'm still not using GTID, and still see operational issues with working with GTID. As a latest example, our new schema migration solution, gh-ost, allows us to test migrations in production, […]

  • SQL mini hack of the day, inverted IN clause

    November 13, 2015

    We are used to issue queries with an IN clause of the form: However I've had a few cases where I used an inverted format. Here's one use case followed by an inverted IN clause. Dynamic query building Say we have this function: Which, based on whether given clusterName is empty or not, would return […]

  • Leader election using MySQL

    October 14, 2015

    Being a stateful, centralized datastore, MySQL can serve in negotiating leadership: a mechanism to elect a single service out of multiple services; moreover, a mechanism to promote a new leader should the existing leader cease to function. What of Zookeeper? Zookeeper makes for an excellent leader election mechanism. This is one of the most recognized uses for […]

  • Tool of the day: q

    August 8, 2013

    If you work with command line and know your SQL, q is a great tool to use: q allows you to query your text files or standard input with SQL. You can: SELECT c1, COUNT(*) FROM /home/shlomi/tmp/my_file.csv GROUP BY c1 And you can: SELECT all.c2 FROM /tmp/all_engines.txt AS all LEFT JOIN /tmp/innodb_engines.txt AS inno USING […]

  • Merging tables with INSERT...ON DUPLICATE KEY UPDATE

    February 21, 2013

    Had a case recently where I had to merge data from two identically structured tables containing nearly identical data. "Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one […]

  • Hierarchical data in INFORMATION_SCHEMA and derivatives

    January 8, 2013

    Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table: CREATE TABLE employee (   employee_id INT UNSIGNED PRIMARY KEY,   employee_name VARCHAR(100),   manager_id INT UNSIGNED,   CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id) ) engine=innodb ; +-------------+---------------+------------+ | employee_id | employee_name […]

  • Pop quiz: funny syntax

    November 5, 2012

    The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript. Can you guess the results of the following statements? Pop quiz 1 SET @x := 7; SELECT ++@x; What is the computation result? What will be the value of @x? Pop quiz 2 SET @ := […]

  • Three wishes for a new year

    September 16, 2012

    Another new year by Jewish calendar. What do I wish for the following year? World peace Good health to all Get a decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL. I mean, I like GROUP_CONCAT, and the many hacks it provides: [1], [2], [3], [4], [5], [6], [7], [8], [9], […]

  • SQL: selecting top N records per group, another solution

    August 21, 2012

    A while back I presented SQL: selecting top N records per group, a "give me the top 5 countries in each continent" type of query, and which used an external numbers table and a lot of tedious casting. Here's another solution I came up with (*). Still using GROUP_CONCAT (how else?), but no external table […]

 
Powered by Wordpress and MySQL. Theme by openark.org