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

Displaying posts with tag: sql (reset)

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...]
    The Curious Case of the Missing Binlogs
    +0 Vote Up -0Vote Down
    When you enable binlogs in the my.cnf file you can either set the log-bin flag to true, or you can set it to a path and file name prefix such as this:
    [mysqld]
    log-bin=/path/to/binlogs/mysql-binlog
    This changes the default location where binlogs are stored.  The problem is that when you connect to mysql there is currently no way to query the server to find out if that path has been changed, and what it currently is.  This means you can't be sure where any server's binlogs are actually stored.
    Ok, so they're not really missing, but it's a known issue that mysql doesn't make them easy to find.  The server obviously knows the path internally, but it doesn't make this information available. Bug #35231 has been open on this issue since 2008 and is currently being ignored.


      [Read more...]
    Introducing Data Fabric Design for Commodity SQL Databases
    +3 Vote Up -0Vote Down
    Extract from THE SCALE-OUT BLOG by Robert Hodges (CEO, Continuent)http://scale-out-blog.blogspot.com Data management is undergoing a revolution. Many businesses now depend on data sets that vastly exceed the capacity of DBMS servers. Applications operate 24x7 in complex cloud environments using small and relatively unreliable VMs. Managers need to act on new information from those systems in
    Why does MySQL’s version comment change when logging is enabled?
    +3 Vote Up -0Vote Down

    I wonder if the MySQL archaeologists out there would be willing to unearth some (presumably ancient) history for me. Why does the logging configuration merit special mention in the version_comment variable?

    The more I think about this, the more bizarre it seems. I enabled logging. The version reported by the server changed. No, really, is my server somehow a different version of MySQL now?

    
    130203 15:39:55 [Note] ./bin/mysqld: ready for connections.
    Version: '5.6.7-rc-log'
    

    I assume there’s a good story behind this somewhere. I’m thinking a priest, a rabbi, and Monty walk into a bar, and black vodka is probably involved at some point too :-)

    MySQL Auto Increment
    +1 Vote Up -1Vote Down

    Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

    CREATE TABLE elvira
    ( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
    , movie_title  varchar(60))
      ENGINE=InnoDB
      CHARSET=utf8;

    It raises this error:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
    , movie_title  varchar(60))
      [Read more...]
    Deleting millions of rows in small chunks with common_schema
    +6 Vote Up -0Vote Down

    I wrote pt-archiver for jobs like deleting or archiving rows from a big table in small chunks. These days, that’s the kind of task I like doing inside the database, and Shlomi’s magical common_schema feels a lot more suited for this than an external Perl script.

    When I say it’s magical, it really does feel magical. It’s amazing how he’s created an entire expressive scripting language that runs in MySQL and feels just right for the job.

    Right now I’m watching this kind of stuff scroll by in my terminal:

    +---------------------+
    | rows_deleted_so_far |
    +---------------------+
    |             2871119 |
    +---------------------+
    1 row in set (7 min 42.67 sec)
    
    +---------------------+
    | rows_deleted_so_far |
      [Read more...]
    MySQL 5.6 adds connection attributes
    +5 Vote Up -0Vote Down

    I enjoyed being able to add metadata to a connection in Microsoft SQL Server. I’d annotate my connections so that a DBA could learn a little bit by inspecting it. For example, what was its purpose, and from which application did it originate? The employer where I did this wasn’t perfect at managing their database user accounts and so forth, and there were many servers with hundreds of databases on each server, so this was a good way to provide some extra hints.

    That hasn’t historically been available in MySQL, but with MySQL 5.6, it will be. This is a nice addition. I assume the support for it in the connector libraries will grow over time.

    I

      [Read more...]
    Bold predictions on which NoSQL databases will survive
    +1 Vote Up -0Vote Down

    In case you’ve been living under a rock for the last 5 years, the NoSQL movement has changed. There was a time when everyone — EVERYONE — was dumping on relational databases, and MySQL in particular. Nonsense like “SQL itself is inherently unscalable” routinely came out of the mouths of otherwise usually sensible people. But that’s cooled off a little bit, thank heavens.

    And what’s the new hotness? Well, Big Data, of course! But I digress. In the world of databases, it’s move over NoSQL, heeeeeere’s NewSQL. I’m talkin’ NuoDB, Clustrix, MySQL Cluster (NDB), and so forth. A lot of people now recognize that it wasn’t SQL or the relational model that was a problem — it was the implementations that had some issues. The

      [Read more...]
    Hierarchical data in INFORMATION_SCHEMA and derivatives
    +5 Vote Up -0Vote Down

    Just how often do you encounter hierarchical data? Consider a table with some parent-child relation, like the this classic employee table:

    CREATE TABLE employee (
      employee_id INT UNSIGNED PRIMARY KEY,
      employee_name VARCHAR(100),
      manager_id INT UNSIGNED,
      CONSTRAINT `employee_manager_fk` FOREIGN KEY (manager_id) REFERENCES employee (employee_id)
    ) engine=innodb
    ;
    +-------------+---------------+------------+
    | employee_id | employee_name | manager_id |
    +-------------+---------------+------------+
    |           1 | Rachel        |       NULL |
    |           2 | John          |          1 |
    |           3 | Stan          |          1 |
    |           4 | Naomi         |          2 |
      [Read more...]
    A close look at New Relic’s scalability chart
    +1 Vote Up -0Vote Down

    I’ve written a lot about modeling MySQL with the USL, and I like it best of all the scalability models I’ve seen, but it’s not the only way to think about scalability. I was aware that New Relic supports a scalability chart, so I decided to take a peek at that. Here’s a screenshot of the chart, from their blog:

    Here’s how it works. It plots response time (or database time, or CPU) as the dependent variable, versus throughput as the independent variable. There’s a line through it to indicate the general shape. Samples are charted as points in a scatter plot. The points are color-coded by the time of day. Outliers

      [Read more...]
    Modeling scalability with the USL at concurrencies less than 1
    +0 Vote Up -1Vote Down

    Last time I said that you can set a starting value for the USL’s coefficient of performance and let your modeling software (R, gnuplot, etc) manipulate this as part of the regression to find the best fit. However, there is a subtlety in the USL model that you need to be aware of. Here is a picture of the low-end of the curve:

    The graph shows the USL model as the blue curve and linear scalability as the black line. Notice that at concurrencies less than 1, the value of the USL function is actually greater than the linear scalability function. This deserves some thought and explanation, because it can cause problems.

    If you think about

      [Read more...]
    Determining the USL’s coefficient of performance, part 2
    +0 Vote Up -0Vote Down

    Last time I said that the USL has a forgotten third coefficient, the coefficient of performance. This is the same thing as the system’s throughput at concurrency=1, or C(1). How do you determine this coefficient? There are at least three ways.

    Neil Gunther’s writings, or at least those that I’ve read and remember, say that you should set it equal to your measurement of C(1). Most of his writing discusses a handful of measurements of the system: one at concurrency 1, and at least 4 to 6 at higher concurrencies. I can’t remember a time when he’s discussed taking more than one measurement of throughput at each level of concurrency, so I think the assumption is that you’re going to take a single

      [Read more...]
    Determining the Universal Scalability Law’s coefficient of performance
    +1 Vote Up -0Vote Down

    If you’re familiar with Neil Gunther’s Universal Scalability Law, you may have heard it said that there are two coefficients, variously called alpha and beta or sigma and kappa. There are actually three coefficients, though. See?

    No, you don’t see it — but it’s actually there, as a hidden “1″ multiplied by N in the numerator on the right-hand side. When you’re using the USL to model a system’s scalability, you need to use the C(1), the “capacity at one,” as a multiplier. I call this the coefficient of performance. It’s rarely 1; it’s usually thousands.

    To illustrate why this matters, consider two systems’ throughput as load increases:

      [Read more...]
    Interesting findings about one of the Go database drivers for MySQL
    +2 Vote Up -0Vote Down

    Moral of the upcoming story: when your systems rely on someone else’s software, make sure you test it thoroughly to understand how it works.

    I found a couple of interesting things about the go-mysql-driver driver for Go. One is that it prepares every query before executing it, and then closes it afterwards, if you’re just using the db.Query() or db.QueryRow() functionality. There is zero benefit to this; Bill Karwin has probably listed it as an antipattern somewhere. I asked for one query, but I got three.

    The other is that it doesn’t open the database connection when you call db.Open(). You can call that function and get no error. The first query on the resulting “db” object will actually connect to MySQL. Thus it’s actually kind of

      [Read more...]
    The state of MySQL client libraries
    +5 Vote Up -0Vote Down

    Those who’ve been around the MySQL world are probably aware of the much-discussed topics of GPL licensing, dual licensing, and in particular, licensing of the client libraries (also called connectors or drivers) and the FOSS exception (http://www.mysql.com/about/legal/licensing/foss-exception/) to that licensing. This is newly relevant with the announcement of a permissively-licensed MySQL-compatible client library for MariaDB.

    The difference is that this time there’s been some question about the provenance and history of the source code. Some people asked me about this. Some of them were aware of a relatively obscure detail: there’ve been permissively licensed MySQL client libraries for years, in the form of libdrizzle, a BSD-licensed library for the Drizzle fork of MySQL.

    Here are some of the thoughts that seemed to be going through

      [Read more...]
    Handling MySQL’s warnings in Go code
    +2 Vote Up -0Vote Down

    I was just bitten by failing to catch a MySQL warning. It’s the old familiar tune: I inserted 100 characters into a VARCHAR(50) and it didn’t throw an error*. Of course, then subsequent SELECT statements didn’t find the value I inserted.

    What’s different this time is that I was using Go as the client. There is no single official MySQL driver for Go, although there are several good-quality community-maintained ones. I was using one of those through the official Go database interface, which is a simple and lightweight way to interact with relational databases. This interface will generate errors, but I didn’t think about warnings. This is funny, because usually I’m paranoid about capturing warnings from MySQL and treating them as errors.

    After I discovered my mistake, I

      [Read more...]
    Slides for my talk at DevIgnition
    +1 Vote Up -0Vote Down

    I spoke last Friday at DevIgnition about what’s coming in the MySQL 5.6 release. My slides are embedded below.

    Further Reading:

    The MariaDB Foundation: A turning point for MySQL
    +9 Vote Up -10Vote Down

    Back when Sun Microsystems was setting, some of the programmers who had been involved with the popular and well-known open source MySQL database started a fork of the project called MariaDB.

    read more

    Previous 30 Newer Entries Showing entries 61 to 90 of 984 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.