Are you a Django user? There’s an upcoming Django conference in Chicago in a few months, and I know they’re looking for speakers with MySQL experience in particular. One suggestion the organizers have floated is a talk on MySQL:
I’m looking for someone to give at least one MySQL talk there. In particular, I would love a (friendly but vigorous) “Why you should use MySQL instead of PostgreSQL talk”, as PostgreSQL tends to get a lot of love and attention at Django events, and MySQL not so much.
Take a look at it and see if you are interested. Presenting at a conference is one of the best things you can do for your career, your company, and your community of open-source software. I highly encourage it if you haven’t tried it.
In MariaDB and MySQL, Cursors can only be used within a Stored Program, are slow, and have very limited functionalities. That said, they can still be useful in some cases. This page explains how to use them in action, with a trivial example.
Here is our example Stored Procedure which uses a Cursor:
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
READS SQL DATA
Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete[Read more...]
If you execute
mysqli_multi() and later you try to execute
mysqli_result(), you may get the infamous error 2014:
Commands out of sync; you can't run this command now (
SQLSTATE: HY000). This happens because you didn’t free all resultsets before executing another query. But probably, you didn’t even know that any resultset exists, because you executed statements like
DELETE, or DDL.
Well, here is a function which frees all resultsets, and an usage example:
* Free all resultsets from $dbCon.
* @param mysqli $dbCon mysqli object.
Once again, WordPress managed to silently change my code and make it unusable. And this time, it does it too well: I can’t fix it. This (code killing) is the only WP feature which works good, but I must admit that it is definitely perfect.
For this reason, please ignore the code examples below, and download this archive, which contains my Stored Functions, a Test Case for my Functions, and Arjen Lentz’s Stored Function.
I found a Levenshtein Distance function, implemented as SQL Stored Function for MySQL and MariaDB, written by Arjen Lentz. That post also contains a short but interesting discussion about[Read more...]
Original images from Flickr user jenniferwilliams
One of our clients, for various historical reasons, runs both MySQL and PostgreSQL to support their website. Information for user login lives in one database, but their customer activity lives in the other. The eventual plan is to consolidate these databases, but thus far, other concerns have been more pressing. So when they needed a report combining user account information and customer activity, the involvement of two separate databases became a significant complicating factor.
In similar situations in the past, using earlier[Read more...]
How fast is
COUNT() execution? Well, it depends from the Storage Engine.
Try to create an
INSERT some data, and execute an
EXPLAIN similar to the following:
MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM test.t1; +------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
Last week Tokutek announced that they’re open-sourcing their TokuDB storage engine for MySQL. If you’re not familiar with TokuDB, it’s an ACID-compliant storage engine with a high-performance index technology known as fractal tree indexing. Fractal trees have a number of nice characteristics, but perhaps the most interesting is that they deliver consistently high performance under varying conditions, such as when data grows much larger than memory or is updated frequently. B-tree indexes tend to get fragmented over time, and exhibit a performance cliff when data doesn’t fit in memory anymore.
The MySQL community is excited about[Read more...]
Years ago I complained bitterly about MySQL’s backwards development and release model, which made guinea pigs out of the paying customers. I think I’d be remiss if I didn’t say it’s been fixed for years. And it’s really fixed right in my opinion — much better than what I proposed.
Congratulations, and thanks, to the MySQL team for superhuman software engineering, release engineering, documentation, bug triage and analysis, and doing a million things right — in other words, making a damn good database, which is hard. You know I still have gripes occasionally, and so do most people, but in the scheme of things — wow. MySQL is awesome.
We’re ironing out a kink that’s preventing Planet MySQL from aggregating VividCortex’s blog feed, so while that’s in progress, I’ll post a quick note on what we’re up to at VividCortex, for the Planet MySQL readers.
VividCortex is a monitoring and analysis product for MySQL, provided as Software-As-A-Service, with agents that run in your systems and report back to our APIs. The agents are super-efficient and non-obtrusive (you’ve probably noticed my posts about Go recently). They gather high-resolution data about your systems and our web application helps you make sense of it.
VividCortex is shockingly easy to install — if you’re slow at the keyboard, it takes 30 seconds. In less than a minute you can get insight into what your MySQL servers are doing. We are in closed[Read more...]
InnoDB and some 3rd parties Storage Engines support transactions. But there are many places where concurrency can cause conflicts:
MyISAMonly support table-level locks, which prevent all write statements (and maybe even reads) on a whole table. When concurrency is too high, this is a problem.
VIEW, which is very different from locking a table: a
VIEWcan be a subset of a table, or a
JOINbetween table subsets.
An alternative is using some SQL functions which acquire, check and release global named “locks”. The reason why I quoted “locks” is that they don’t lock anything. All sessions are[Read more...]
If you are importing large CSV or SQL dumps to MySQL, chances are you were looking for ways to see how far the import has gone. If you know how many rows there are from the file being imported, you can do a SELECT COUNT(*) but that would take sometime for the query to finish especially on really big imports.
Using lsof, you can monitor the current file offset to which a process is reading from using the -o option. Knowing the size of the file and some snapshots of the offset, you can get a somewhat rough idea of how fast the import goes. Note though that this is only file-read-pace not actual import speed as MySQL import can vary depending on a number of conditions i.e. table growth, secondary indexes, etc.
Let’s say I am importing a 1.1G CSV file into a table.
[revin@forge msb_5_5_300]$ ls -al[Read more...]
While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic
Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:
CREATE PROCEDURE hello (IN pv_input VARCHAR(50)) BEGIN SELECT sample_id , sample_name FROM sample WHERE sample_name = pv_input; END; $$
A call to this
hello procedure will only return the row or rows where the
pv_input value matches the
sample_name column value. Any attempt to exploit it like the one below fails.
MySQL 5.6 through the eyes of a custom storage engine MySQL plugin
MySQL is famous for its pluggable storage engine architecture which allows a DBA or an application developer to choose the right engine for the task. An application uses MySQL API and is isolated from all of the low-level implementation details at the storage level. As an example, the Cloud Storage Engine (ClouSE) enables existing MySQL applications to use cloud storage such as Amazon S3 or Google Cloud Storage to store its data. The application doesn’t need to be changed or even redeployed: with ClouSE, remote cloud storage will look like a better (ultra-scalable, durable, always-on) alternative to the local storage.
As you may already know, ClouSE now supports[Read more...]
I’m really looking forward to Percona Live this year. I will be there, along with Kyle Redinger, my co-founder at VividCortex. I feel that this year the conference has come full circle. 2007 was my first year at the conference, and it was amazing. This year is tremendously exciting for me because it feels like we’re back on the 2007 trajectory.
Those were the golden days. Things were a little sad in 2010 and 2011 as the MySQL community tried to figure out the new landscape and O’Reilly decided to stop running the event, but then in 2012 we all said “we’re back!” with the exception of Oracle, who instead arranged MySQL Connect, as part of Oracle Open World. I don’t think that was a huge success. I don’t know if that’s why[Read more...]
MySQL/MariaDB identifiers are names for databases, tables, columns, etc. They can be quoted with `backticks` (AKA backquotes), and in that case they can contain characters which are normally illegal for identifiers (even spaces or the backtick itself), or they can be reserved words. Both quoting and not quoting cause some problems.
If you don’t quote names you will need to avoid illegal chars and reserved words – which is a good practice, anyway. But when you upgrade MariaDB, the new version could add some reserved words.
If you quote names, you should be sure to do it everywhere. It is a good practice, but if a developer doesn’t use backticks (or forgets to do it), he may see strange errors.
If you use[Read more...]
High Performance MySQL, 3rd Edition has been selling very well. It’s translated into many languages. O’Reilly sends me a hard-copy of the translations, and I have a whole section on my bookshelf dedicated to them. It’s really satisfying to look at it.
Today I’m happy to announce that we’re moving forward with a new batch of translations. Demand has been so strong that we want to make the book accessible to as wide an audience as possible. Plus, I get a fat check every time O’Reilly sells the translation rights.
The new languages will include Australian, l337 (“Leet”), Jive, Ebonics, Elmer Fudd, Blissymbols, and Esperanto. Here’s a sample before-and-after paragraph:
Last night at the Golang-DC meetup I spoke about building (MySQL) database applications with Go. The meetup was well attended and people were very enthusiastic about Go. I spent a few minutes talking about Go in general, how VividCortex uses Go (we’ve built our agents, API servers, and all backend processes with Go), why we like it, some of the nice things it enables like making it easy to build very resilient programs, and then I gave the presentation, which I’ve embedded below.
Afterwards the discussion ranged to a lot of related topics. This was the best part of the evening for me. There were really great questions on a variety of topics, and insightful answers from everyone.
STK/Unit 1.0 Release Candidate 1 is out!
STK stands for SQL ToolKit. It’s a family of proects for MariaDB, MySQL and Percona Server. STK/Unit is the first STK project that has been publicly release; more tools will come in the next future. The long-term purpose of STK is making SQL programming much easier and reliable on MariaDB and her sisters.
STK/Unit is a Unit Test framework for MariaDB, entirely written in SQL and inspired by SimpleTest and JUnit. Test Cases and Test Suites written by the user can set a test environment and check that all operations work as expected. The results can be retrieved as a human-readable string, in HTML format, or examined in the tables they are stored in.[Read more...]
I really don’t like running my database in “I Love Garbage” mode, so I set the following SQL_MODE:
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE, NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
Guess what WordPress does with that? It doesn’t install. If you set the SQL_MODE to empty and install WordPress, then restore the SQL_MODE, WordPress will run, but if you try to create a post you’ll see an error page that says “You are not allowed to edit this post.”
This problem was reported to WordPress at least 7 years ago. Lessons learned:
I am currently finishing some features to make a program highly resilient to occasional crashing bugs. A particular function was found to crash on queries of the form
WHERE x IN(NULL), and that crashed the entire program. Now we have a framework for intelligently recovering from arbitrary crashes. I will write more on this in the future, because I think it’s a very interesting thing to share.
In this episode, I want to focus on a related topic: how do you test a program that is supposed to be resilient to bugs you can’t predict? Many new problems are caused by writing clever code that is supposed to detect, avoid, or recover from problems, even known problems. Unknown problems are even riskier.
The approach that has given me a great deal of[Read more...]
One of the most rewarding things you can do is help someone get a great job or hire a great person for the position they need to fill. I have traveled a lot, written books, done a bunch of consulting, and spoken widely on MySQL, other databases, open source, and so forth. I’ve gotten to know a lot of people, some I’d call good friends, and many of them are leading large organizations. I think this is both a privilege and a serious responsibility.
It’s a privilege because I can ask some of these people for help or introductions or advice sometimes. It’s a responsibility because I need to be ready to do something for them, too. In many cases it’s a pay-it-forward kind of readiness.
Many, many people contact me looking for people to hire. I keep a list. When someone tells me they are on the job market, I try to[Read more...]
I was a guest on the Food Fight Show last week, along with a bevy of really smart people asking and answering tough questions on fault detection. We didn’t talk a lot about MySQL, but given that VividCortex is focusing on MySQL initially, pretty much all of my experience with zero-threshold, zero-configuration fault detection is MySQL-based.
It’s a fun conversation with a lot of insights into the industry, what’s wrong with current monitoring tools, and where monitoring is going. Also, it’s sold out now, but Monitorama is a conference you might be interested in if you’re doing monitoring (and who isn’t?)
Here is a collection of upcoming events that are interesting to me as a MySQL user (in some cases because I’m speaking). I think some of them are must-see events. I am sure I am missing a lot of events, but some of these are only publicized in specific channels, and I wanted to mention them here to help spread the word.
This coming Thursday, Charlottesville’s Neon Guild tech group will co-host a Meetup with a noted Kanban expert.
I’m joining two Meetups soon to talk about building database (MySQL) applications with the Go programming language:[Read more...]
I was discussing how to avoid surprising users and someone pointed out that what seems intuitive and rational to one person is often complete insanity for others. The mental gap between a developer and a user can often be a chasm far too wide to cross. Of all the bug reports I’ve filed against MySQL, here is my all-time favorite:
select * from t where a >= 1.0order by a;
Does not cause an error. I believe it should, because there should be a whitespace before ORDER BY.
Similar syntax errors such as “
select 1e0from dual” were also accepted as valid SQL. Much soul-searching later, the official reply from MySQL’s development team:
The server behaves properly here:
Most of the time, when people say “scalability” they mean any of dozens of things. Most of the time, when I say it I mean exactly one precisely defined thing. However, I don’t claim that’s the only correct use of “scalability.” There is another, in particular, that I think is very important to understand: the inherent limitations of the system. This second one doesn’t have a single mathematical definition, but it’s vital nonetheless.
I’ll frame the discussion by asking this: how scalable is your database?
Using the two definitions I like to use the most, I answer the question in this way.
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 tables (while others can be NULL in the second).
Otherwise no contradicting data: it was not possible for some data to be "3" in one table and "4" in the other.
pt-table-sync comes to mind: one can do a bidirectional syncing of two tables, and actually stating how to resolve ambiguities (like "greater value[Read more...]