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

Displaying posts with tag: sql (reset)

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