Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 61 to 90 of 1001 Next 30 Older Entries

Displaying posts with tag: sql (reset)

MariaDB/MySQL Cursors: a brief Tutorial
+0 Vote Up -0Vote Down

Versione italiana

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:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN

  [Read more...]
MySQL Query Patterns, Optimized – Webinar questions followup
+3 Vote Up -0Vote Down

On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.

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...]
MariaDB/MySQL: “Commands out of sync” error (2014)
+0 Vote Up -0Vote Down

Versione italiana

If you execute mysqli_multi() and later you try to execute mysqli_query() or 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 INSERT, DELETE, or DDL.

Well, here is a function which frees all resultsets, and an usage example:

<?php
    
/**
 *	Free all resultsets from $dbCon.
 *	@param		mysqli		$dbCon	mysqli object.
 *

  [Read more...]
On PostgreSQL. Interview with Tom Kincaid.
+0 Vote Up -1Vote Down
“Application designers need to start by thinking about what level of data integrity they need, rather than what they want, and then design their technology stack around that reality. Everyone would like a database that guarantees perfect availability, perfect consistency, instantaneous response times, and infinite throughput, but it´s not possible to create a product with [...]
levenshtein and levenshtein_ratio Functions for MySQL
+0 Vote Up -0Vote Down

!!!WARNING!!!

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...]
Foreign Data Wrappers
+0 Vote Up -0Vote Down

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...]
MariaDB/MySQL: Performances of COUNT()
+0 Vote Up -0Vote Down

Versione italiana

How fast is COUNT() execution? Well, it depends from the Storage Engine.

Try to create an Aria or MyISAM table, 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 |
  [Read more...]
What TokuDB might mean for MongoDB
+5 Vote Up -0Vote Down

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...]
MySQL’s release process works
+14 Vote Up -0Vote Down

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.

Introduction to VividCortex
+1 Vote Up -1Vote Down

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...]
MariaDB/MySQL: GET_LOCK, RELEASE_LOCK, etc
+0 Vote Up -0Vote Down

InnoDB and some 3rd parties Storage Engines support transactions. But there are many places where concurrency can cause conflicts:

  • MEMORY, Aria and MyISAM only 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.
  • You may want to lock a VIEW, which is very different from locking a table: a VIEW can be a subset of a table, or a JOIN between table subsets.
  • DDL statements are not affected by locks. This is even true on tables which support transactions.

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...]
Check (Rough) Progress of Your CSV Import to MySQL
+0 Vote Up -0Vote Down

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...]
SQL Injection Risks
+1 Vote Up -0Vote Down

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 WHERE clauses.

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.

CALL
  [Read more...]
MySQL 5.6: an inside perspective
+2 Vote Up -0Vote Down

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...]
Percona Live MySQL Conference in a few weeks!
+0 Vote Up -0Vote Down

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...]
Quoting MySQL & MariaDB identifiers
+0 Vote Up -0Vote Down

Versione italiana

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...]
New translations of High Performance MySQL
+3 Vote Up -0Vote Down

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:

Isolating

  [Read more...]
Building MySQL Database Applications with Go
+1 Vote Up -0Vote Down

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 released
+0 Vote Up -0Vote Down

Annuncio italiano

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...]
WordPress and MySQL’s strict mode
+2 Vote Up -0Vote Down

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:

      [Read more...]
    Crash injection for writing resilient software
    +1 Vote Up -0Vote Down

    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...]
    Playing matchmaker for job seekers and recruiters
    +2 Vote Up -1Vote Down

    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...]
    Adaptive Fault Detection food fight
    +1 Vote Up -0Vote Down

    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?)

    Upcoming events of interest for MySQLers
    +1 Vote Up -0Vote Down

    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.

    Upcoming Meetups

    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...]
    Seeing things from the user’s point of view
    +3 Vote Up -2Vote Down

    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:
    - “1″

      [Read more...]
    How scalable is your database?
    +1 Vote Up -1Vote Down

    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.

  • Scalability in terms of the Universal Scalability Law is the degree to which you can add more workers (or units of hardware) and get equal
  •   [Read more...]
    Merging tables with INSERT...ON DUPLICATE KEY UPDATE
    +3 Vote Up -0Vote Down

    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.

    How do you create a merge of the tables, such that all missing rows are completed, and NULLs replaced by actual values when possible?

    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...]
    Sessions I’d like to see at Percona Live in April
    +4 Vote Up -0Vote Down

    I’m really looking forward to this year’s Percona Live MySQL Conference. This is always THE event of the year for me in the MySQL conference circuit. It’s also the first year I haven’t been a speaker! I’ve been a speaker since 2007 but this year things were too uncertain for me to submit a proposal in time.

    As usual, the real highlight of the conference is seeing and talking to everyone. Technical sessions are also great, but honestly I can usually study up on technical things without going to a conference. However, nothing can replace the benefit of meeting all the dedicated MySQL community members in the hallways and at meals, and talking to MySQL-related businesses in the expo hall. Year after year, this conference has been what makes things happen:

      [Read more...]
    Efficient Partial Table Scans
    +0 Vote Up -0Vote Down
    There's a pretty common MySQL recipe for performance that if you want to efficiently scan through lots of rows in small chunks that LIMIT with OFFSET is right out.  Using OFFSET, MySQL will have to scan all the rows until it finds the starting position before it starts reading results to return.   Just to be clear, these statements look like:

    SELECT id FROM foo ORDER BY id LIMIT 10 OFFSET 1000;


    If you were trying to read all rows in table then this would  be a very slow and expensive way to do that (in terms of MySQL resources.) The most common optimization is to switch to an algorithm where you remember the last highest id value for each chunk of rows, and then add that to the WHERE clause.

    SELECT id FROM foo WHERE id > 100000  ORDER BY id LIMIT 10;


     This would be a much more efficient way







      [Read more...]
    The Data Day, Two days: February 7/8 2013
    +0 Vote Up -0Vote Down

    Teradata results. Funding for DataXu. The chemistry of data. And more.

    For 451 Research clients: Oracle launches major update to MySQL open source database bit.ly/TSONAt

    — Matt Aslett (@maslett) February 8, 2013

    For 451 clients: Analyzing the chemistry of data bit.ly/TSOV2R By @451wendy Treating sensitive data like dangerous chemicals

    — Matt Aslett (@maslett) February 8, 2013

    Teradata: Q4 net income $112m on revenue up 10% to $740m, FY net income $419m on revenue up 13% to $2.7bn. bit.ly/14FNS8L

      [Read more...]
    Previous 30 Newer Entries Showing entries 61 to 90 of 1001 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.