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 121 to 150 of 1152 Next 30 Older Entries

Displaying posts with tag: Open Source (reset)

State of the MySQL forks: via a particular example of authentication plugins
+3 Vote Up -0Vote Down

A year ago I posted a blog on The state of MySQL forks: co-operating without co-operating. (Also Giuseppe wrote about the topic at that time, and Peter Zaitsev covers it in his conference keynotes.) So I've been wondering if it would be good to write an update on the topic now, and in that case what to write.

read more

banker’s round for mysql
+0 Vote Up -0Vote Down
for some reason, nobody has ever exposed the different rounding methods via mysql’s built-in ROUND() function, so if you want something different, you need to add it via a stored function. the function below is based on the T-SQL version here.

CREATE FUNCTION ROUND_TO_EVEN(val DECIMAL(32,16), places INT)
RETURNS DECIMAL(32,16)
BEGIN
  RETURN IF(ABS(val - TRUNCATE(val, places)) * POWER(10, places + 1) = 5 
            AND NOT CONVERT(TRUNCATE(ABS(val) * POWER(10, places), 0),
                            UNSIGNED) % 2 = 1,
            TRUNCATE(val, places), ROUND(val, places));
END;


use at your own risk. there may be edge conditions where this fails. but this matches up with the python and postgres based



  [Read more...]
More MySQL foreach()
+4 Vote Up -0Vote Down

In my previous post I've shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema.

In this part I present DBA's handy syntax for schema and table operations and maintenance.

Confession: while I love INFORMATION_SCHEMA's power, I just hate writing queries against it. It's just so much typing! Just getting the list of tables in a schema makes for this heavy duty query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sakila' AND TABLE_TYPE='BASE TABLE';

When a join is involved this really becomes a nightmare. I think it's

  [Read more...]
Could closed core prove a more robust model than open core?
+0 Vote Up -0Vote Down

When participating recently in a sprint held at Google to document four free software projects, I thought about what might have prompted Google to invest in this effort. Their willingness to provide a hotel, work space, and food for some thirty participants, along with staff support all week long, demonstrates their commitment to nurturing open source.

Google is one of several companies for which I'll coin the term "closed core." The code on which they build their business and make their money is secret. (And given the enormous infrastructure it takes to provide a search service, opening the source code wouldn't do much to stimulate competition, as I point out in a posting on O'Reilly's radar

  [Read more...]
Common Schema: dependencies routines
+3 Vote Up -1Vote Down
Are you a MySQL DBA? Checkout the common_schema project by Oracle Ace Shlomi Noach.

The common_schema is an open source MySQL schema that packs a number of utility views, functions and stored procedures. You can use these utilities to simplify MySQL database administration and development. Shlomi just released revision 178, and I'm happy and proud to be working together with Shlomi on this project.

Among the many cool features created by Shlomi, such as foreach, repeat_exec and exec_file, there are a few %_dependencies procedures I contributed:




      [Read more...]
    common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis
    +1 Vote Up -0Vote Down

    common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.

    common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.

    There's no Perl nor Python, and no dependencies to install. It's just a schema.

    Some highlights for the new revision:

    • foreach(), aka $(): loop through a collection,
      [Read more...]
    OSSCube is now the World’s First Cloudera / Hadoop Training Partner
    +0 Vote Up -0Vote Down

    We are proud to share that OSSCube has now entered into a partnership with Cloudera, as the official training partner for Cloudera Developer Training for Apache Hadoop. The pride is, essentially, in the fact that we are the World’s First Cloudera / Hadoop Training Partner.

    Cloudera provides enterprises a powerful new data platform built on the popular Apache Hadoop open source software package.  Hadoop is a powerful data management platform for consolidating your data, storing your information inexpensively and reliably and understanding large, heterogeneous data sets in order to better comprehend the data deluge.

    OSSCube believes this partnership will deliver first-class



      [Read more...]
    OSSCube is now the World’s First Cloudera / Hadoop Training Partner
    +0 Vote Up -0Vote Down

    We are proud to share that OSSCube has now entered into a partnership with Cloudera, as the official training partner for Cloudera Developer Training for Apache Hadoop. The pride is, essentially, in the fact that we are the World’s First Cloudera / Hadoop Training Partner.

    Cloudera provides enterprises a powerful new data platform built on the popular Apache Hadoop open source software package.  Hadoop is a powerful data management platform for consolidating your data, storing your information inexpensively and reliably and understanding large, heterogeneous data sets in order to better comprehend the data deluge.

    OSSCube believes this partnership will deliver first-class Cloudera /



      [Read more...]
    Replication stars
    +3 Vote Up -0Vote Down
    Working with replication, you come across many topologies, some of them sound and established, some of them less so, and some of them still in the realm of the hopeless wishes. I have been working with replication for almost 10 years now, and my wish list grew quite big during this time. In the last 12 months, though, while working at Continuent, some of the topologies that I wanted to work with have moved from the cloud of wishful thinking to the firm land of things that happen. My quest for star replication starts with the most common topology. One master, many slaves.

    Fig 1. Master/Slave topology

      [Read more...]
    Oracle ACE
    +5 Vote Up -0Vote Down

    I am honored to have been nominated for, and to have received the Oracle ACE award.

    Nomination for this award is made by Oracle community members, and in this case those being Oracle employees Keith Larson and Dave Stokes. The award is given by Oracle for my involvement in the Oracle/MySQL community and for my contributions.

    While open source involvement is generally done in the mere purpose of sharing knowledge and solutions, recognition plays a role in it. For the most part, one who writes blogs wants them to be read, and one who writes code wants it to be downloaded and tested, which is an elemental type of recognition, and what I aim for.

    The recognition given by the Oracle ACE award makes for a wonderful complement, being given by the corporate with whose products I'm involved. It is great to

      [Read more...]
    Data Modeling
    +2 Vote Up -0Vote Down

    Dear data integration fans,

    I’m a big fan of “appropriate” data modeling prior to doing any data integration work.  For a number of folks out there that means the creation of an Enterprise Data Warehouse model in classical Bill Inmon style.  Others prefer to use modern modeling techniques like Data Vault, created by Dan Linstedt.  However, the largest group data warehouse architects use a technique called dimensional modeling championed by Ralph Kimball.

    Using a modeling technique is very important since it brings structure to your data warehouse.  The techniques used, when applied correctly of-course, are

      [Read more...]
    MySQL 101 - Replication
    +1 Vote Up -0Vote Down

    So far we've looked at many aspects of MySQL, not in any great depth, but hopefully with enough information to get you started and whet your appetite for more.  Now we start to look into areas that aren't in the basic tutorials.

    Replication is the technology that allows data to be stored on multiple servers. Typically this is used in "scale out" applications.  "Scale out" is used in contrast to "Scale up" where to scale a solution you buy a bigger box to run it on, where "scale out" means you buy more boxes.  Each has its benefits and drawbacks, with the usual benefit of scale out being that you get more bang for your buck.

    The way replication works in MySQL is pretty simple.  One server is identified as the master, and writes every transaction to a file, the binary log.  Other servers (and there may be many) act as slaves and

      [Read more...]
    Developer Week in Review: These things always happen in threes
    +0 Vote Up -0Vote Down

    Fall is being coy this year in the Northeast. We've been having on and off spells of very mild, almost summer-like weather over the last few weeks. That trend seems to be finally ending, alas, as there is possible snow forecasted for the weekend in New Hampshire. As the old joke goes, if you don't like the weather here, just wait five minutes.

    The fall also brings hunting to the area. The annual moose season just concluded (you need to enter a special lottery to get a moose permit), but deer season is just about to open. My son and I won't be participating this year, but we recently purchased the appropriate tools of the trade, a shotgun to hunt in southern NH (where you can't hunt deer with a rifle) and a Mosin Nagant 91/30 for the rest of the state. The later is probably

      [Read more...]
    From MySQL to SkySQL - Reflections
    +2 Vote Up -3Vote Down

    This month marked SkySQL's first anniversary.  It also marks my 6 months with the company, after being with MySQL AB (and then Sun and finally Oracle) for over 5 years. As good a time as any for a look back and some musings of the future.

    In late 2005 I took a position in the web team (a part of Marketing) at MySQL AB.  It was a great company to work for, with a great team of people and a truly great spirit.  The idea that they were making a difference every day was palpable. I then saw it taken over by Sun Microsystems and shortly after by Oracle Corporation.  We went from 500 employees to 30,000 and then to 100,000.  My job satisfaction started to decline.  I felt more like I was battling for every concession rather than revelling in what we were able to achieve.  I needed a

      [Read more...]
    Have you ever heard about “Read Masters” in MySQL??? Enterprise ready SchoonerSQL provides it.
    +0 Vote Up -8Vote Down

    Typical MySQL environment involves one Master receiving writes and multiple slaves to scale the reads.The “slave” term has been used in MySQL because the Slave servers have to perform every task in copying from the Master binlog, then updating their relay logs and finally committing to the Slave databases. The Master plays no role in replication here other than storing the replication events in the binlog.
    With this kind of Master- Slave set up, there are several limitations-
    -       Slave lag-       Stale or old data-       Data loss-       Manual failover which is error-prone and time consuming
    In SchoonerSQL, there is no concept of “Slaves” inside synchronous cluster. We refer to it as "Read Masters"



      [Read more...]
    Tungsten Replicator and MySQL Sandbox at Percona Live London 2011
    +4 Vote Up -0Vote Down
    I will be a speaker at Percona Live - London 2011, and I am looking forward to the event, which is packed with great content. A whopping 40 session of MySQL content, plus 3 keynotes and 14 tutorials. It's enough to keep every MySQL enthusiast busy. Continuent speakers will be particularly busy, as between me and Robert Hodges, we will be on stage four times on Tuesday, October 25th.   [Read more...]
    MySQL 101 - More Transactions
    +0 Vote Up -0Vote Down

    In our last episode we looked at transactions and how to create them.  In this episode I'll look at some of the implications of transactions, especially in a web application.

    Transactions and Replication

    We will discuss replication in depth later in the series, however it is sufficient for the moment to say that replication allows you to copy data in near real-time between MySQL servers and keep them synchronised.  What gets transferred are the changes that are made to your tables and data. So what about rolled-back (aborted) transactions?  Since the state after the rollback is essentially the same as the state before the transaction started, there seems little point in replicating those statements.  Indeed they are not replicated.

      [Read more...]
    Zendcon Presentations
    Employee +1 Vote Up -0Vote Down

    I will be at Zendcon next week with two presentations. What’s New with MySQL will be on Wednesday the 19th and The Care and Feeding of a MySQL Database on Thursday the 20th.

    Other MySQL centric session are by Bill Karwin on SQL Injection Myths and Fallacies and MySQL 5.5 InnoDB Tuning. Plus Ligaya Turmelle will present Character Sets Suck.



      [Read more...]
    MariaDB: the new MySQL? Interview with Michael Monty Widenius.
    +4 Vote Up -4Vote Down
    “I want to ensure that the MySQL code base (under the name of MariaDB) will survive as open source, in spite of what Oracle may do.” -- Michael “Monty” Widenius. Michael “Monty” Widenius is the main author of the original version of the open-source MySQL database and a founding member of the MySQL AB company. [...]
    MySQL 101 - Transactions
    +0 Vote Up -0Vote Down

    We've now come a long way since our first steps at creating our online bookshop database. Now we need to start to think about how to sell the books and store details about the sales.  This is the time we need to start understanding database transactions.

    Database transactions are very similar to real world transactions. They define a set of steps required to happen together in order for a transaction to be complete.  A real-world example might be that you buy a trinket from a store.  You find the trinket, then take it to the counter, find out the price, hand over the cash and receive your trinket.  That is a completed transaction.  Should you not have the available cash, the transaction would not be able to be completed and you would

      [Read more...]
    My three MySQL sessions at OOW 2011 - and much more
    +3 Vote Up -0Vote Down
    Oracle Open World 2011 is approaching. MySQL is very well represented. Sheeri has put together a simple table of all the MySQL sessions at OOW, which is more handy than the Oracle schedule. I will be speaking in three sessions on Sunday, October 2nd.
    • Sunday, 9am MySQL: Don't Be a Rookie Forever—Be in Command (Line)I have given this talk before, as a tutorial at the UC in 2010 and at FrOSCon one month ago. It is one of the most rewarding sessions ever. The attendee were very interested. This will be a short version of the
      [Read more...]
    MySQL 101 - Referential Integrity
    +0 Vote Up -0Vote Down

    In our last episode we learned how to modify data and table definitions.  This will come in handy as we look at building in referential integrity constraints into our database.  To begin we will need the database definition resulting from last episode's changes. You can download it here »

    A word on Storage Engines

    Before we can begin we need to understand a little about MySQL Storage Engines.  MySQL actually does only part of the job of parsing SQL, creating query plans, executing them and returning data sets.  Where the data is stored and retrieved there is a Storage Engine at work.  The original

      [Read more...]
    MySQL 101 - Changing data and schema, UPDATE, ALTER
    +0 Vote Up -0Vote Down

    In our last episode we covered sorting, searching and grouping. We found out that using the COUNT(*) can be problematic when we have unexpected NULL data. Now we look at how to resolve data issues by updating the data, and perhaps even the table schema. We'll use the same database we did for the last episode. You can download it here ».

    Updating Data

    Let's recap.  If we pull the list of books, and authors, we find that "The Broken Shore" from Peter Temple has no price.  Not that it has a zero price, but it has a NULL value.

    mysql> SELECT CONCAT_WS(' ',`author`.`first_name`,`author`.`last_name`) AS `author`,
     `book`.`title`, `book`.`price`
     FROM `author` INNER JOIN `book` ON

      [Read more...]
    MySQL 101 - Sorting and Searching: ORDER BY, WHERE, GROUP BY
    +1 Vote Up -0Vote Down

    In our last episode we were able to select some information from our bookshop database, this time we look at putting this into some semblence of order.  To fully investigate this topic we need a few more entries in our database, so rather than detail them here, I've put together this SQL file you can download and build your database to follow along.

    To install the database, unpack the SQL file from its ZIP archive, and use the SOURCE command to pull the data into your database:

    SOURCE mysql101_bookshop_20110912.sql;

    You can also pass the file to the mysql command line interpreter from the shell:


      [Read more...]
    MySQL 101 - Retrieving data: SELECT and JOIN
    +0 Vote Up -0Vote Down

    In our last episode we started building up our online bookshop database, with tables for publishers, authors, formats and books.  At the moment we only have one book in there, so before we go too far, lets add a few more:

    INSERT INTO `book` VALUES
    ( NULL, 'The Big Score', 2, 4, 1, '2007-01-01', 9781741752236, 29.95 ),
    ( NULL, 'Split', 3, 2, 1, '2003-01-01', 0732268133, 29.95 );

    So what is this NULL thing, and why have I used it?  If you remember we set the first field to an auto_increment id.  Because we don't want to supply a value for this, but let the database create the next value, we need to give a value that indicates we want this to happen.  For this instance, NULL is the value to




      [Read more...]
    common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()
    +3 Vote Up -0Vote Down

    Revision 68 of common_schema is out, and includes some interesting features:

    • eval(): Evaluates the queries generated by a given query
    • match_grantee(): Match an existing account based on user+host
    • processlist_grantees: Assigning of GRANTEEs for connected processes
    • candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
    • easter_day(): Returns DATE of easter day in given DATETIME's year.

    Let's take a slightly closer look at these:

    eval()

    I've dedicated this blog post on MySQL eval() to describe it. In simple

      [Read more...]
    MySQL 101 - Creating your first database
    +2 Vote Up -0Vote Down

    In our last episode we found out how to connect to a MySQL server.  This time we learn how to lay out a database and start creating it. For this, and following episodes, we will be looking at creating a database to support an online bookshop.

    Creating the database

    Using the mysql command line client, you can connect to the server and then create the database.  We need a name for the database, and in this case we'll call it 'bookshop'.   We'll also create a user who is specifically allowed to add and update the database, but not alter its structure:

    mysql> CREATE DATABASE `bookshop`;
    Query OK, 1 row affected (0.01 sec)

    mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON `bookshop`.* to


      [Read more...]
    MySQL 101 - Connecting to a MySQL server
    +0 Vote Up -1Vote Down

    In our last episode we looked at getting MySQL, today we will be looking at how you connect to a MySQL server and what that implies.

    Connection basics

    Before a client can connect to a MySQL server it needs a path by which that connection can be made. One method that is almost universal is the 'socket'.  As its name implies it is a way of plugging two (or more) applications together.  Sockets can either be end points for a network connection (for instance a TCP socket) or can use the same system-level functions but use a local connection.  This is sometimes called a UNIX socket, and relies on there being a special file that the two applications can use to initiate a connection.  MySQL can use both.

    For a network connection you

      [Read more...]
    NoSQL Now 2011: Review of AdHoc Analytic Architectures
    +1 Vote Up -0Vote Down

    For those that weren’t able to attend the fantastic NoSQL Now Conference in San Jose last week, but are still interested in the slides about how people are doing Ad Hoc analytics on top of NoSQL data systems, here’s my slides from my presentation:

    No sql now2011_review_of_adhoc_architectures

    View more presentations from ngoodman We obviously continue to hear from our community that LucidDB is a great solution sitting in front of a Big Data/NoSQL  [Read more...]
    MySQL 101 - Getting MySQL
    +1 Vote Up -1Vote Down

    This is the first in a series of posts on MySQL® for those new to the database, or those migrating from another DBMS.

    So you've made the decision to try MySQL.  Now you just have to get it installed. Luckily for most purposes MySQL is quite often already available.  If you have a Linux installation then chances are that both the server and client are installed.  If you are planning on using MySQL for your website, chances are the hosting provider gives you several MySQL databases for your use.

    Before diving in too deep though, let's get some background and terminology out of the way.

    What is MySQL?

    This depends.  MySQL was a company, is a trademark, is an ecosystem and is the name of arguably the most popular relational database management system (RDBMS) on the planet.  Originally developed by MySQL AB,

      [Read more...]
    Previous 30 Newer Entries Showing entries 121 to 150 of 1152 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.