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 1001 Next 30 Older Entries

Displaying posts with tag: sql (reset)

MySQL Synonym?
+0 Vote Up -0Vote Down

Somebody asked how to create a SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.

The following SQL statements create two databases and grant appropriate privileges to the student as the root superuser:

/* Create two databases. */
CREATE DATABASE seussdb;
CREATE DATABASE appdb;
 
/* Grant privileges to a student user. */
GRANT ALL ON seussdb.* TO student;
GRANT ALL ON appdb.* TO student;

Log out from the root superuser and reconnect as the student user. Then, the following code connects to the

  [Read more...]
MariaDB: DELETE … RETURNING
+4 Vote Up -1Vote Down

MariaDB 10.0.5 supports DELETE ... RETURNING, like PostgreSQL. It deletes some rows and returns the specified columns (or, more generally, the specified SELECT expressions) from the deleted rows. As for regular DELETEs, Com_delete is incremented and Com_select is not.

Let’s see what this feature does and what it does not.

You cannot:

  • Cannot use it with INSERT or CREATE TABLE to create a table containing data from the deleted rows (a sort of delete log, or easy-to-restore backup).
  • Cannot use it as a subquery.
  • Cannot use it as a cursor, and there is no INTO. So you cannot process the results in a stored routine,
  [Read more...]
Introducing MySQL Connector/Arduino 1.0.0 beta
Employee +4 Vote Up -0Vote Down
There is a new release of the Connector/Arduino on Launchpad! See https://launchpad.net/mysql-arduino. The new version supports a number of refinements and a few new features. These include:
  • Improved support for processing result sets
  • Conditional compilation to omit result set handling features to save program space
  • Support for the Arduino WiFi shield
  • New version() method to check version of the connector
  • Simplified download (no more patching SHA1!)

So What is It?


If you have never heard of Connector/Arduino, it is simply a library designed to allow the Arduino platform to connect to and issue queries to a MySQL Database server.

Simply add an Ethernet shield to your Arduino and use the library to connect your Arduino to a MySQL database server. Yes, no more web-based hand waving or




  [Read more...]
Announcing: New Forum for Connector/Arduino!
Employee +0 Vote Up -0Vote Down
Due to the growing popularity of Connector/Arduino, the moderator of MySQL Forums has created a forum for us to meet up and discuss the connector. Yippie!

http://forums.mysql.com/list.php?175

While the forum has been started very recently, I expect it will grow quickly as people discover the connector for the first time and experienced users find new and interesting ways to use it. I hope to moderate the new forum periodically to answer questions and respond to posts. See you there!

Note: you need an account to write to the forum. Click on "register" in the upper right hand corner of the forum page to create an account if you do not already have one.


Building Queries Systematically
Employee +0 Vote Up -0Vote Down

The SQL language is a bit like a toolkit for data. It consists of lots of little fiddly bits of syntax that, taken together, allow you to build complex edifices and return powerful results. For the uninitiated, the many tools can be quite confusing, and it's sometimes difficult to decide how to go about the process of building non-trivial queries, that is, queries that are more than a simple SELECT a, b FROM c;

A System for Building Queries

When you're building queries, you could use a system like the following: 

  • Decide which fields contain the values you want to use in our output, and how you wish to alias those fields
  • Values you want to see in your output
  • Values you want to use in calculations . For example, to calculate margin on a product, you could
  •   [Read more...]
    MySQL isn’t limited to nested-loop joins
    +2 Vote Up -0Vote Down

    I have followed the “Use the Index, Luke!” blog for a while. Today Marcus wrote that (I’ll paraphrase) MongoDB disgraces NoSQL the same way that MySQL disgraces SQL. I agree with a lot of this, actually, although I’m not sure I’d put it so strongly. People often like products for good reasons, and to think that legions of developers are stupid or ill-educated is suspect, in my opinion.

    But that wasn’t what I meant to write about. I wanted to point out something about the blog post that’s a little outdated. He wrote, and this time I’ll quote, “MySQL is rather poor at joining because is only supports nested loops joins. Most

      [Read more...]
    It happened again
    +1 Vote Up -1Vote Down

    Oracle released a bunch of MySQL stuff they’ve been working on since the last huge release, and my blog reader filled up with a few dozen posts I’m gonna have to read through so I don’t feel ignorant. Dear MySQL Engineering Team, could you take pity on me and release these gradually over the course of a month or so next time? Especially since Google discontinued Reader, and I’m using Feedly now, and it has a bug that I

      [Read more...]
    How to find unused MariaDB/MySQL accounts
    +1 Vote Up -0Vote Down

    In MariaDB and Oracle MySQL, an account is a user_name@host combination, where host can be an ip address (v4 or v6) or a hostname. Also, the host part can be specified as a LIKE pattern (such as '161.58.%'). The account used by the connection determines the privileges for that connection. When a connection matches more than one account, only one is used, and privileges assigned to other accounts will be ignored.

    It can happen that you think that a certain client uses an account, but it uses a different account. For this reason, in this post I’ll show the queries to get all defined accounts, plus the accounts that were not used since last server startup.

    The second query will use performance_schema. Audit plugins and the

      [Read more...]
    Sending the query to the data
    +0 Vote Up -0Vote Down

    It’s common wisdom that large-scale database systems require distributing the data across machines. But what seems to be missing in a lot of discussions is distributing the query processing too. By this I mean the actual computation that’s performed on the data.

    I just had a conversation with Peter Zaitsev yesterday that helped make concrete some thoughts I’ve been having about Cassandra for a while. Because Cassandra doesn’t allow you to really do any computation in the data (aggregating, evaluating expressions, and so on), if you’re going to use it for truly Big data, you’re going to fetch enormous amounts of data across the network. Sure, you’re distributing the storage and retrieval across many machines — but you’re locating your data far

      [Read more...]
    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.

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