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

Displaying posts with tag: Databases (reset)

Designing Multi-Tenanted Databases – Many-to-Many
+0 Vote Up -0Vote Down

I want to give you tools that you can use for building databases that can handle complex relationships. In the previous article in this series, I looked at hierarchical data. The classic example of a hierarchy is an org chart. On most org charts I’ve seen, an employee has only one boss, which is a one-to-many relationship. The other kind of segmentation is many-to-many. A good instances of this is your social circle. Most people have many friends, and those friends have multiple friends themselves.

This is a common pattern. When developing a multi-tenanted application, users

  [Read more...]
Enhancing pt-kill to Better Protect your Servers
+2 Vote Up -0Vote Down
I believe in automation as much as possible, and I'm always working to make the day to day tasks of operations as smooth as possible.  Also I try not to be afraid to take good tools and make them better.

Here in Database Ops at Box, we use pt-kill running as a service to constantly monitor our servers and help protect against long running queries.  But our thresholds are pretty generous, and in some cases it's possible for unforeseen circumstances to cause enough queries to storm the database such that we can have problems before any of them hit the threshold for "busy time."  Ditto for idle connections.

The response is that someone has to be available to manually run another copy of pt-kill with much lower thresholds to clear out these thundering herds.  But what if we could let pt-kill handle both the "normal" mode and still protect



  [Read more...]
What is FTS_BEING_DELETED.ibd
+0 Vote Up -0Vote Down

I currently have on a MySQL 5.6 database using innodb_file_per_table the following individual tablespace file.

schema/FTS_00000000000001bb_BEING_DELETED.ibd

The schema is all InnoDB tables, and there ARE NO Full Text Indexes. I cannot comment on if a developer has tried to create one previously.
I am none the wiser in explaining the ongoing use of these files, or if it can be/should be deleted.

On closer inspection there are infact a number of FTS files.

$ ls -al FTS*
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_BEING_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:20 FTS_00000000000001bb_BEING_DELETED.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:26 FTS_00000000000001bb_CONFIG.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29

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

Over the years you collect datasets you have created for various types of testing, seeding databases etc. I have always thought one needs to better manage this for future re-use. Recently I wanted to do some “Big Data” playing and again that question of what datasets can I use let me to review the past collated list at Seeking public data for benchmarks.

The types of things I was wanting to do lead me to realize a lot of content is “public domain” and Project Gutenberg is just one great source of text in multiple languages. This was just one aspect of my wish list but text based data is used from blogs, comments, articles, microblogs etc, and multiple languages was important

  [Read more...]
The GRANT/REVOKE dilemma
+0 Vote Up -0Vote Down

It is common practice to grant your application the privileges of “GRANT SELECT, INSERT, UPDATE, DELETE ON yourdb.* TO user@host”.

But what if you want to ensure you cannot DELETE data from just one table?

Ideally I want to be able to “REVOKE DELETE ON yourdb.important_table FROM user@host”. You cannot do currently this with the MySQL privilege system.

If your schema has 100 tables, and you want to remove DELETE from one, you have to define DELETE for the 99 others, and remember that for each new table, you need to remember to also modify user privileges.

MySQL Explain Explained
+0 Vote Up -0Vote Down

Whenever I have a problem query in MySQL, I say to myself, I know, I’ll use Explain Plan. Now I have two problems. I run the explain plan, but then I have to look up all the cryptic terms and read through the documentation to know what in the world is going on. Well, over the weekend, I decided to solve my own problem. I created a simple page that allows you to run an explain plan, and then each of those obscure, abbreviated terms is annotated with a tooltip that explains exactly what’s going on. I’m calling it MySQL Explain Explained.

I created a demo using the sample employees database (which you can download here). Here’s a sample query:

select gender, count(emp_no) as manager_cnt,
  [Read more...]
Designing Multi-Tenanted Databases
+0 Vote Up -0Vote Down

Designing database tables for many customers is a surprisingly foreign concept for some programmers. I’ve been in interviews where a programmer will sketch out a normal object model, and then proceed to suggest that for the database, each customer has their own set of tables, each prefixed with the customer name. What I’d like to do in this post is introduce the concept of multi-tenancy and then show methods you can use to do it right, instead of hacky ways like the one I just mentioned. Multi-tenancy is when several customers (tenants) share the same database and codebase but can only see their own data.

The basic idea

The core method of adding multi-tenancy to your database is adding a column to every table you’d like to segment that indicates the owner of the

  [Read more...]
Null Columns in MySQL – Part II
+0 Vote Up -0Vote Down

I’ve covered some strange default behavior around nulls in MySQL. There’s another nuance to this issue: you can still insert rows without specifying values for not null columns. MySQL will helpfully give you a default value (this is not really helpful – true help would be a quick failure with a descriptive error message). Let’s walk through this example.

mysql> Create Table NullTable (a Int Not Null, b Int Not Null);
Query OK, 0 rows affected (0.01 sec)

Here’s a table with two not null columns.

mysql> Insert Into NullTable (a) Values (1);
Query OK, 1 row affected, 1 warning (0.01 sec)

We can totally insert, even though we didn’t specify a value for b and b is not

  [Read more...]
MySQL Simplified
+0 Vote Up -0Vote Down

MySQL is the little engine that could. It powers sites like Facebook, YouTube, Twitter, and thousands of blogs, CMSes, and e-commerce sites. Its value to the world and to the development community could be measured in the hundreds of billions, and yet it’s free, and you can use it just by downloading it. Almost every programming language has drivers for it and it can run on so many operating systems and architectures, there’s really no limit on it.

Yet there’s a dark side. MySQL is full of gotchas and bugs, and it lacks features that sometimes call into question its status as a real database. The documentation is often open-ended and confusing, with gaps in key parts. If you want to run it, you have the option of using it on Linux, Mac, Solaris, or Windows and every hosting company or provider like Amazon AWS has

  [Read more...]
MongoDB in 2013 -- A Year in Review
+0 Vote Up -1Vote Down
It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an

  [Read more...]
MongoDB in 2013 -- A Year in Review
+0 Vote Up -1Vote Down
It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an

  [Read more...]
Upgrading from MySQL 5.1 to MariaDB 5.5
+2 Vote Up -0Vote Down

In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.

Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDB’s superior performance for subqueries is why we switched – and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.

It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps

  [Read more...]
A Tale of Two MySQL Upgrades
+4 Vote Up -0Vote Down

At the beginning of 2013, Mozilla’s MySQL databases were a mix of MySQL 5.0, Percona’s patched MySQL 5.1, Percona’s patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 – so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 – the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Here’s a graph showing the state of our machines – you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get a larger

  [Read more...]
Simple Settings for a Saner MySQL – InnoDB
+0 Vote Up -0Vote Down

Within MySQL, there’s a piece called a storage engine that reads and writes to disk on your behalf when you execute a query. It controls the way that your data is stored on disk. With MySQL, you can change what storage engine you use, which is helpful since every engine has different advantages and downsides, and you can select which engine to use based on your workload. There are two main engines: MyISAM and InnoDB. MyISAM was the default engine before MySQL 5.5 and it’s been there since the beginning. It’s also not crash-proof, it doesn’t have foreign keys, and it’s not transactional. InnoDB, on the other hand, has all these features.

  [Read more...]
Unexplained halts using mysql command line client
+0 Vote Up -0Vote Down

I recently came across an issue trying to connect to a MySQL server using the mysql client. It appeared as through the connection was hanging.

A subsequent connection using the -A option highlighted the problem with the previous connection stuck in the state “Waiting for table metadata lock”.

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 37
   User: root
   Host: localhost
     db: tmp
Command: Query
   Time: 90
  State: preparing
   Info: create table missing as select id from AK where id not in (select id ..
*************************** 2. row ***************************
     Id: 38
   User: root
   Host: localhost
     db: tmp
Command: Field List
   Time: 50
  State: Waiting for table metadata lock
   Info:
*************************** 3. row ***************************
  [Read more...]
Next Madrid MySQL Users Group meeting to take place on 16th January 2014
+1 Vote Up -0Vote Down

Yesterday we had our third Madrid MySQL users group meeting. That was quite interesting.  Thanks go to Juan for his presentation.

We plan the next meeting on January 16th after the New Year is out of the way. If you are interested in MySQL and happen to be in Madrid please consider coming to see us.

More information about the next meeting can be found on the group’s web page. Note: The meeting will be in Spanish. I look forward to seeing you.

Giving thanks to MySQL authors challenge
+5 Vote Up -1Vote Down

Next week the US celebrates Thanksgiving Day. For those that are American or live here, this is a significant event. Three different experiences recently have lead me to write this request for ALL MySQL community members to give thanks to those that have contributed to the MySQL ecosystem. I have made a commitment to myself, and I would like to challenge others to write one book review per week in December, that’s 4 book reviews to the MySQL books that I have on my bookshelf that have made an impact in some way. I ask others to give it a go too.

It only takes a few minutes to pen a comment on Amazon, or a publishers site, but to authors it means so much more. I can only speak for myself, but any comment; good, bad or ugly; helps to know you are out there and you took the time to acknowledge somebody’s work of art (in this case a

  [Read more...]
Kick all the tires before you buy the product
+2 Vote Up -0Vote Down

Translating theory to practice is never easy. Morgan gives us the right steps in a play environment to move from dev.mysql.com native MySQL rpm’s to the new MySQL yum repository. I thought I would try it out.

1. Confirming existing packages

A necessary step, however immediately I have more dependencies including Perl DBD (used in several utilities) including MHA.

$ sudo su -
$ rpm -qa | grep -i mysql
MySQL-devel-5.6.13-1.el6.x86_64
MySQL-test-5.6.13-1.el6.x86_64
MySQL-shared-compat-5.6.13-1.el6.x86_64
MySQL-server-5.6.13-1.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
MySQL-client-5.6.13-1.el6.x86_64
MySQL-embedded-5.6.13-1.el6.x86_64
MySQL-shared-5.6.13-1.el6.x86_64
mha4mysql-node-0.54-1.el5.noarch

A further trap

  [Read more...]
What SQL is running in MySQL
+3 Vote Up -0Vote Down

Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.

The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.

use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest;
do sleep(60);
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from
  [Read more...]
Monitoring an online MySQL ALTER TABLE using Performance Schema
+2 Vote Up -0Vote Down

Recently a client asked me how long it would take for an ALTER TABLE to complete. Generally the answer is “it depends”. While this was running on a production system I tried with the Performance Schema in MySQL 5.6 to work out some answer to this question. While I never got to investigate various tests using INPLACE and COPY for comparison, Morgan Tocker made the request for experiences with online ALTER in A closer look at Online DDL in MySQL 5.6. Hopefully somebody with more time can expand on my preliminary observations.

Using Mark Leith’s ps_helper (older version) I monitored the File I/O to see if I could determine when using innodb_file_per_table the percentage of

  [Read more...]
MySQL RPMS and the new yum repository
+1 Vote Up -0Vote Down

I was really pleased to see the announcement by Oracle MySQL yum repositories that they have now produced a yum repository from where the MySQL RPMs they provide can be downloaded. This makes keeping up to date much easier. Many companies setup internal yum repositories with the software they need as then updating servers is much easier and can be done with a simple command. For many people at home that means you set this up once and don’t need to check for updates and do manual downloads, but can do a quick yum update xxxx and you get the latest version. Great!  This new yum repository only covers RHEL6 did not include RHEL5 which is not yet end of life and still used by me and probably quite a lot of other people. I filed

  [Read more...]
MySQL shutdown via service reporting ERROR
+0 Vote Up -0Vote Down

Working with MySQL 5.6 under CentOS 6.4 I came across the following problem with MySQL reporting it did not shutdown successfully.

$ sudo su -
$ service mysql stop
Shutting down MySQL................................................................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................................................................
  [Read more...]
Updating MySQL using official repositories
+0 Vote Up -0Vote Down

The announcement of the MySQL Yum repositories was a long overdue request on my wish list. While it was possible to find MySQL at http://public-yum.oracle.com/ it was not the GA version MySQL 5.6. (As I write this post, I check and find that indeed it now appears this may be possible http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL56/).

I have previously installed MySQL 5.6.13 via rpms downloaded from MySQL Downloads, so I expected the upgrade to be straightforward. Following Chapter 3. Upgrading MySQL with the MySQL Yum

  [Read more...]
MySQL performance schema threads
+3 Vote Up -0Vote Down

A trap for those new to the MySQL Performance Schema is the expectation that thread_id in tables such as events_statements_current matches the id you find in the MySQL processlist. This is NOT TRUE.

If we look at the INFORMATION_SCHEMA.PROCESSLIST table we will find information like:

mysql> select id,db,command,state from information_schema.processlist order by id;
-----------+--------------------+---------+------------------------------------------------------------------+
| id        | db                 | command | state                                                            |
+-----------+--------------------+---------+------------------------------------------------------------------+
|         1 | NULL               | Connect | Slave has read all relay
  [Read more...]
What causes LOST_EVENTS error in the MySQL binary log?
+2 Vote Up -0Vote Down

Using MySQL 5.6.13 under CentOS 6.4, I had a replication failure on one master/slave topology because the master binary log had the following entry that was intentionally written by the MySQL server.

$ mysqlbinlog --start-position=244670849 mysql-bin.000029
...
# at 244670849
#131028 19:31:38 server id 39  end_log_pos 244670906 CRC32 0xc5d084ec
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 244670906
...

The question is why? I do not know the answer. Searching online indicates this can occur in a MySQL Cluster environment, and can occur around the use of GRANT statements. Neither of these situations are applicable here. This site runs 4 different master/slave topologies all running the same version, and this has been the only occurrence.

The message, if taken literally could indicate that the

  [Read more...]
MySQL Workbench “Clean up SQL” Feature
+1 Vote Up -0Vote Down

I was playing around with MySQL Workbench earlier in the week, and ran across the “clean up SQL” feature, which I thought was neat. Here’s a picture-based demonstration – you can click on the pictures to make them bigger, so they are more readable.

Here is a typical complex query that looks pretty good formatted in the results from a performance schema query:

Simply click the “broom” icon and watch as your SQL is cleaned up, with one field in the SELECT per line and the JOINs indented and formatted prettily:

Pretty cool, for just the click of a button!

MySQL/NoSQL/Cloud Conference Latin America
+1 Vote Up -0Vote Down

Last week I was a guest speaker at the second annual MySQL/NoSQL/Cloud Conference held in Buenos Aires, Argentina. Thanks to Santiago Lertora from Binlogic who has taken on the responsibility of organizing a event for the MySQL community in South America.

My presentations slides for my 3 talks.

While the MySQL community and ecosystem has changed

  [Read more...]
Simple Settings for a Saner MySQL – Character Sets
+0 Vote Up -0Vote Down

Character sets are like the force: they surround us and penetrate us, binding all our digital world together. A character set is how we convert the 1’s and 0’s that the computer understands into human-readable characters like ABC. In one of the first character sets, ASCII, the number 97 is translated to “a” and 63 is the question mark (?).


“Are there other languages besides English?”
“Don’t think so, Bob.”

English-Only Please

The trouble with ASCII is that it was created back in the ‘60s by a bunch of Americans and they were not thinking about French or German, they were thinking about English. Guess what? ASCII works great for American



  [Read more...]
Simple Settings for a Saner MySQL – Part I
+0 Vote Up -0Vote Down

One day, after taking a job using MySQL, I was writing a query quietly at my desk. It was around quittin’ time, and I was whipping up a new report on the monthly sales figures that the CEO needed for his board meeting in the morning. I, in my ignorance, wrote a nice query with a simple little sum() function. What I did not realize is this caused all my rows to sum up to one row when it really should’ve show several rows. This made my report completely inaccurate. Blindly trusting me as I had blindly trusted MySQL, the CEO presented my report to the board who decided that the company was grossly unprofitable and voted to shut it down. Only when I was waiting in the bread line at my local workhouse did I realize my mistake.

  [Read more...]
MySQL Connect presentations on MySQL Fabric available on SlideShare
Employee +1 Vote Up -0Vote Down
Going to MySQL Connect was truly a blast. We got a lot of good questions and feedback in the sessions and there were a lot of interest in both MySQL Fabric and the MySQL Applier for Hadoop.

A big thank you to all that attended the talks, I got a lot of good questions and comments that will help us build good solutions.

The talks are available on SlideShare:





  [Read more...]
Previous 30 Newer Entries Showing entries 31 to 60 of 1146 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.