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 31 to 60 of 992 Next 30 Older Entries

Displaying posts with tag: sql (reset)

Features I’d like in MySQL: windowing functions
+3 Vote Up -0Vote Down

Continuing with my wishlist, I’ll add windowing functions. They’re enormously powerful. They allow you to extend relational logic beyond the strict boundaries of tuples. In MySQL at present, one must use ugly hacks to preserve state from one row to the next, such as user variables — which are not guaranteed to work if the optimizer changes the query plan.

And yeah, PostgreSQL and SQL Server have windowing functions too, and once you’ve used them it’s a little hard to go back. This is in fact one of the main things I hear from people who love PostgreSQL for what I consider to be legitimate reasons.

Windowing functions extend the uses of SQL (sometimes awkwardly, sometimes elegantly), into areas you can’t really go without

  [Read more...]
Features I’d like to see in MySQL: CTEs
+1 Vote Up -0Vote Down

The pace of MySQL engineering has been pretty brisk for the last few years. I think that most of the credit is due to Oracle, but one should not ignore Percona, Monty Program, Facebook, Google, Twitter, and others. Not only are these organizations (and the individuals I haven’t mentioned) innovating a lot, they’re providing pressure on Oracle to keep up the improvements, too.

But if you look back over the last few years, MySQL is still functionally a lot like it used to be. OK, we’ve got row-based binary logging — but we had binary logging and replication before, this is just a variation on a theme. Partitioning — that’s a variation on a theme (partitioned tables are a variation on non-partitioned tables). Performance — same thing, only

  [Read more...]
Can TokuDB replace partitioning?
+1 Vote Up -0Vote Down

I’ve been considering using TokuDB for a large dataset, primarily because of its high compression. The data is append-only, never updated, rarely read, and purged after a configurable time.

I use partitions to drop old data a day at a time. It’s much more efficient than deleting rows, and it lets me avoid indexing the data on the time dimension. Partitioning serves as a crude form of indexing, as well as helping purge old data.

I wondered if TokuDB supports partitioning. Then I remembered some older posts from the Tokutek blog about partitioning. The claim is that “there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.”

  [Read more...]
Free talk on MySQL and Go at Percona MySQL University DC
+1 Vote Up -0Vote Down

If you’re in the Washington, DC area on Sept 12th, be sure to attend Percona University. This is a free 1-day mini-conference to bring developers and system architects up to speed on the latest MySQL products, services and technologies. Some of the topics being covered include Continuent Tungsten; Percona XtraDB Cluster; MySQL Backups in the Real World; MariaDB 10.0; MySQL 5.6 and Percona Server 5.6; Apache Hadoop.

I’ll be speaking about using MySQL with Go. I’ll talk about idiomatic database/sql code, available drivers for MySQL, and tips and tricks that will save you time and frustration.

Continuent is sponsoring a complimentary

  [Read more...]
Stored Routines to easily work with the SQL_MODE
+1 Vote Up -0Vote Down

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the

  [Read more...]
MariaDB/MySQL: Procedures to easily work with the Diagnostics Area
+1 Vote Up -0Vote Down

Versione italiana

UPDATE 2013-08-30: I fixed 2 bugs and create a GitHub repo called sql_diagnostix. Sorry for not doing this before. If I (or someone else) find new bugs, I’ll update the repo.

The problem

To quickly see information about the errors and warnings generated by the last executed statement, we can use SHOW WARNINGS. However this statement’s results cannot be accessed via SQL, so they cannot be used for error handling within a stored program.

For that purpose we can use GET DIAGNOSTICS, which has two problems:

  • It requires a lot of code
  • There is not a
  [Read more...]
Announcing MySQL Utilities release-1.3.4 GA
Employee +3 Vote Up -0Vote Down
The MySQL Utilities Team is pleased to announce the latest GA release of
MySQL Utilities. This release marks a milestone of concentrated effort to
expand the use of utilities in more diverse installations through improved
robustness, error handling, and quality.

Many Improvements


There are number such enhancements in this release. In this post we will
highlight a few of the more significant improvements.
  • (new utility) MySQL .frm Reader (mysqlfrm) - read .frm files and generate CREATE statements with or without a server connection.
  • (revised) improved documentation including a section on example administrative tasks - see http://dev.mysql.com/doc/workbench/en/mysql-utilities.html
  • MySQL Utilities is packaged for








  [Read more...]
Speaking at Percona University Sept 12th
+1 Vote Up -0Vote Down

I’ll be joining Percona for a free day of MySQL education and insight at their upcoming Percona University Washington DC event on September 12th. My topic is accessing MySQL from Google’s Go programming language. I’ve learned a lot about this over the past year or so, and hopefully I can help you get a quick-start.

If you’re not familiar with Go, it’s the darling of the Hacker News crowd these days. Anything with “Go” in its title gets to the front page for at least a little while! Go is a great systems programming language. It’s safe to say I’ve fallen in love with it, and it’s now my favorite programming language of all those

  [Read more...]
Tool of the day: q
+1 Vote Up -0Vote Down

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 (c1, c2) WHERE inno.c3 IS NULL

And you can also combine with your favourite shell commands and tools:

grep "my_term" /tmp/my_file.txt | q "SELECT c4 FROM - JOIN /home/shlomi/static.txt USING (c1)" | xargs touch

Some of q's functionality (and indeed, SQL functionality) can be found in command line tools. You can use grep for pseudo

  [Read more...]
Working with comma separated list MySQL options
+1 Vote Up -0Vote Down
Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on



As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:
mysql> select replace(@@optimizer_switch,






  [Read more...]
MariaDB/MySQL Error HANDLERs: advanced uses
+0 Vote Up -0Vote Down

This article explains some advanced uses for MySQL and MariaDB’s error HANDLERs. Some information is in MySQL documentation, but it’s rather sparse. Also, while MySQL manual is probably the best manual ever, I’ve found a major bug in errors documentation. So, these use cases are not so obvious. At least they weren’t for me, so I had to test them.

Notes about the examples in this article

All the examples are tested on MariaDB 10.0.3, but I’m sure that there is not difference in MySQL 5.6, and there should be no difference in any 5.* version.

In these examples we need to produce an error to test the server’s behavior. We’ll always use the same method: we’ll declare a `cur`

  [Read more...]
MySQL “NOT FOUND” errors precedence (docs bug)
+0 Vote Up -0Vote Down

This page in the MySQL documentation says something totally wrong:

http://dev.mysql.com/doc/refman/5.6/en/handler-scope.html

“The precedence of NOT FOUND depends on how the condition is raised:

Normally, a condition in the NOT FOUND class can be handled by an SQLWARNING or NOT FOUND handler, with the SQLWARNING handler taking precedence if both are present. Normal occurrence of NOT FOUND takes place when a cursor used to fetch a set of rows reaches the end of the data set, or for instances of SELECT … INTO var_list such that the WHERE clause finds no rows.

If a NOT FOUND condition is raised by a SIGNAL (or RESIGNAL) statement, the condition can be handled by a NOT FOUND handler but not an SQLWARNING handler.

That page

  [Read more...]
SQL Like Comparisons
+1 Vote Up -0Vote Down

SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.

The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' = 'Tre_t'
OR     'Treet' = 'Tre_t';

Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (=)

  [Read more...]
3 Simple Patterns for Tighter MySQL Code
+0 Vote Up -0Vote Down

Join 8000 others and follow Sean Hull on twitter @hullsean. SQL is derided by many and for good reason. It’s key to scalability yet terribly difficult to write good code. Here’s a few quick tips to write tighter queries in MySQL 1. Get rid of those Subqueries! Subqueries are a standard part of SQL, unfortunately […]

The post 3 Simple Patterns for Tighter MySQL Code appeared first on Scalable Startups.

Scalability Happiness – A Quiet Query Log
+0 Vote Up -0Vote Down

Join 7500 others and follow Sean Hull on twitter @hullsean.

There’s a lot of talk on the web about scalability. Making web applications scale is not easy. The modern web architecture has so many moving parts. How can we grapple with the underlying problem?

Also: Why Are MySQL DBAs So Hard to Find?

The LAMP stack scales well

The truth that is half right. True there are a lot of moving parts, and a lot to setup. The internet stack made up of Linux, Apache, MySQL & PHP. LAMP as it’s called, was built to be resilient, dynamic, and scalable.

  [Read more...]
Eliminating duplicate users in MySQL
+3 Vote Up -0Vote Down

This is hypothetical.

What would happen if I did the following?

alter table mysql.user add unique key(User);

I’m tossing this out there for people to think about because I’ve always thought that MySQL’s authentication model is a nuisance:

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MySQL handles this by enabling you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for

  [Read more...]
Quantifying Abnormal Behavior in System Metrics
+1 Vote Up -1Vote Down

I’ve posted slides for my Velocity talk on VividCortex’s blog. The talk explained how we use exponentially weighted moving statistics to generate a meta-metric of abnormality for the time-series metrics measured from MySQL. That’s kind of a mouthful. Maybe you had to be there :-)

MariaDB’s Sequence Storage Engine
+0 Vote Up -0Vote Down

Versione italiana

MariaDB 10.0.3 introduces a new Storage Engine: Sequence. It isn’t in MySQL or in older MariaDBs versions. Sequence is a special engine, which does not create or read any table. It only generates on the fly a sequence of integers which is returned to your query, and then dies. The sequence’s bounds and increment depend from the table name.

Very quick start

To install:

INSTALL PLUGIN sequence SONAME 'ha_sequence';

NOTE: On MariaDB (unlike Oracle MySQL) there is no need to add ‘.dll’ to the command on Windows, so the command is platform-independent. Thanks

  [Read more...]
3 Ways to Optimize for Paging in MySQL
+0 Vote Up -0Vote Down
Join 6100 others and follow Sean Hull on twitter @hullsean. Lots and lots of web applications need to page through information. From customer records, to the albums in your itunes collection. So as web developers and architects, it’s important that we do all this efficiently. Start by looking at how you’re fetching information from your [...]
How to Optimize MySQL UNION For High Speed
+0 Vote Up -1Vote Down
Join 6100 others and follow Sean Hull on twitter @hullsean. There are two ways to speedup UNIONs in a MySQL database. First use UNION ALL if at all possible, and second try to push down your conditions. [mytweetlinks] 1. UNION ALL is much faster than UNION How does a UNION work? Imagine you have two [...]
Djancocon 2013 call for papers open
+0 Vote Up -0Vote Down

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.

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.

Previous 30 Newer Entries Showing entries 31 to 60 of 992 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.