David Lortscher was one of a few dozen people practicing dermatology in New Mexico when he realized a huge problem in the field: There weren’t enough dermatologists, and people were skipping out on visits because they thought it was for more affluent people or took too long. So Lortscher decided to start Curology, which provides acne and anti-aging treatment and prescriptions through… Read More
SkyGiraffe helps enterprises give their employees access to their line-of-business apps and enterprise data from virtually any device. The company today announced that it has raised a $6 million funding round led by SGVC, with participation from Trilogy Equity Partners and a number of angel investors, including Heroku founder and CEO James Lindenbaum, Lookout founder and CTO Kevin… Read More
“Identity-as-a-service” startup Auth0 (pronounced “auth zero”) has raised $15 million in Series B funding.
CEO Jon Gelsey said that for many website and mobile app developers, integrating with different login systems can turn into a big headache — and also create security risks. So the company helps those developers manage identity and authentication, whether… Read More
A San Francisco-based startup called DroneDeploy has raised $20 million in Series B funding to grow their drone data management platform. The drone tech startup closed its Series B deal just before the U.S. Federal Aviation Administration is scheduled to implement new rules governing commercial and industrial drone use. The Part 107 rules, announced by the FAA in June this year, will take… Read More
This blog discusses how to find and address badly written queries using ProxySQL.
All of us are very good in writing good queries. We know this to always be true!
But sometimes a bad query escapes our control and hits our database. There is the new guy, the probie, who just joined the company and is writing all his code using SELECT * instead of WHERE. We’ve told him “STOP” millions of times, but he refuses to listen. Or a there is a new code injection, and it will take developers some time to fix and isolate the part of the code that is sending killing queries to our database.
The above are true stories; things that happen every day in at least few environments.
Isolating the bad query isn’t the main problem: that is something that we can do very fast. The issue is identifying the code that is generating the query, and disabling that code without killing the whole application.
That part can take days.
ProxySQL allows us to act fast and stop any offending query in seconds. I will show you how.
Let us say our offending query does this:
SELECT * from history;
Where history is a table of two Tb partitioned by year in our DWH.
That query will definitely create some issue on the database. It’s easy to identify this query as badly designed.
Unfortunately, it was inserted in the ETL process that uses a multi-thread approach and auto-recovery. Now when you kill it, the process restarts it. After, it takes developers some time to stop that code. In the meantime, your reporting system serving your company in real-time is so slooow (or down).
With ProxySQL, you can stop that query in one second:
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) VALUES (89,1,'^SELECT \* from history$','Query not allowed',1); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
Done, your database never receives that query again! Now the application gets a message saying that the query is not allowed.
And look, it’s possible to do things even better:
INSERT INTO mysql_query_rules (rule_id, active, match_digest, flagOUT, apply) VALUES (89,1,'^SELECT \* FROM history', 100, 0); INSERT INTO mysql_query_rules (rule_id, active, flagIN, match_digest, destination_hostgroup, apply) VALUES (1001,1, 100, 'WHERE', 502, 1); INSERT INTO mysql_query_rules (rule_id, active, flagIN, error_msg, apply) VALUES (1002,1, 100, 'Query not allowed', 1); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
In this case, ProxySQL checks for any query having SELECT * FROM history. If the query has a WHERE clause, then it redirects it to the server for execution. If the query does not have a WHERE it stops the query and sends an error message to the application.
This is a very basic example of offending query. But I think it makes clear how ProxySQL helps any DBA in stopping them quickly in the case of an emergency.
This gives the DBAs and the developers time to coordinate a better plan of action to permanently fix the issue.
Tableau Software‘s shares soared 13 percent on Tuesday following the announcement that the data analytics provider has partnered with Narrative Science, a Chicago-based company that develops natural language generation (NLG) tools. The result of the partnership is Narratives for Tableau, a free Chrome extension that automatically creates written explanations for Tableau… Read More
One of the few new top-level domains that actually makes sense, .blog, is starting the process of registration. Automattic, which runs WordPress and a number of other useful web apps, owns .blog and is handling applications at get.blog and, confusingly, dotblog.wordpress.com. Read More
Based on MySQL 5.7.14, including all the bug fixes in it, Percona Server 5.7.14-7 is the current GA release in the Percona Server 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.14-7 milestone at Launchpad.
- Percona Server Audit Log Plugin now supports filtering by user, database, and sql_command.
- Percona Server now supports tree map file block allocation strategy for TokuDB.
- Fixed potential cardinality
0issue for TokuDB tables if
ANALYZE TABLEfinds only deleted rows and no actual logical rows before it times out. Bug fixed #1607300 (#1006, #732).
database.table.indexnames longer than 256 characters could cause a server crash if background analyze table status was checked while running. Bug fixed #1005.
- PAM Authentication Plugin would abort authentication while checking UNIX user group membership if there were more than a thousand members. Bug fixed #1608902.
DROP DATABASEwould fail to delete some of the tables in the database, the partially-executed command is logged in the binlog as
DROP TABLE t1, t2, ...for the tables for which drop succeeded. A slave might fail to replicate such
DROP TABLEstatement if there exist foreign key relationships to any of the dropped tables and the slave has a different schema from the master. Fix by checking, on the master, whether any of the database to be dropped tables participate in a Foreign Key relationship, and fail the
DROP DATABASEstatement immediately. Bug fixed #1525407 (upstream #79610).
- PAM Authentication Plugin didn’t support spaces in the UNIX user group names. Bug fixed #1544443.
- Due to security reasons
ld_preloadlibraries can now only be loaded from the system directories (
/usr/lib) and the MySQL installation base directory.
- In the client library, any
EINTRreceived during network I/O was not handled correctly. Bug fixed #1591202 (upstream #82019).
SHOW GLOBAL STATUSwas locking more than the upstream implementation which made it less suitable to be called with high frequency. Bug fixed #1592290.
- The included
.gitignorein the percona-server source distribution had a line
*.spec, which means someone trying to check in a copy of the percona-server source would be missing the spec file required to build the RPMs. Bug fixed #1600051.
- Audit Log Plugin did not transcode queries. Bug fixed #1602986.
- If the changed page bitmap redo log tracking thread stops due to any reason, then shutdown will wait for a long time for the log tracker thread to quit, which it never does. Bug fixed #1606821.
- Changed page tracking was initialized too late by InnoDB. Bug fixed #1612574.
- Fixed stack buffer overflow if
--ssl-cipherhad more than 4000 characters. Bug fixed #1596845 (upstream #82026).
- Audit Log Plugin events did not report the default database. Bug fixed #1435099.
- Canceling the TokuDB Background ANALYZE TABLE job twice or while it was in the queue could lead to server assertion. Bug fixed #1004.
- Fixed various spelling errors in comments and function names. Bug fixed #728 (Otto Kekäläinen).
- Implemented set of fixes to make PerconaFT build and run on the AArch64 (64-bit ARMv8) architecture. Bug fixed #726 (Alexey Kopytov).
Other bugs fixed:
#1542874 (upstream #80296), #1610242, #1604462 (upstream #82283), #1604774 (upstream #82307), #1606782, #1607359, #1607606, #1607607, #1607671, #1609422, #1610858, #1612551, #1613663, #1613986, #1455430, #1455432, #1581195, #998, #1003, and #730.
In-flight VR entertainment and security guard drones were amongst our favorites from the 44 startups that launched at Y Combinator’s Summer 2016 Demo Day 1. After querying investors and our writers, here are TechCrunch’s picks for the 7 most promising companies from the first half of the batch, plus an honorable mention. Check back later for all the startups and our picks from… Read More
NGINX, the company behind the popular open-source NGINX server, launched the latest version (R10) of its NGINX Plus commercial offering today. Like similar open-source companies, NGINX offers its core product for free, but then charges for more advanced features and services. Today marks the tenth major update to NGINX Plus and, as the company’s CMO Peter Guagenti and technical… Read More