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

Displaying posts with tag: Databases (reset)

IN Subqueries in MySQL 5.6 Are Optimized Away
+9 Vote Up -12Vote Down

In MySQL 5.6, it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY

  [Read more...]
Do we need a MySQL Cookbook?
+2 Vote Up -2Vote Down

The blog title says it all: Do we need a MySQL Cookbook? I tend to think so.

This seems to be something that is missing with current MySQL documentation. There is lots of information available but finding the appropriate bit can be quite tedious and it often requires looking in multiple places.

A lot of other software has such books, but for some reason MySQL seems to be missing one.

A recent example comes from a “documentation feature request” I posted today: http://bugs.mysql.com/bug.php?id=68171. MySQL 5.6 provides a way to “move InnoDB tables” from one server to another. There are many reasons why you may want to do it, but the documentation is currently rather sparse. A simple “example recipe” for this would be good, as would an equivalent recipe for other

  [Read more...]
New EXPLAIN Features in MySQL 5.6 – FORMAT=JSON actually adds more information!
+8 Vote Up -0Vote Down

When I heard that MySQL 5.6 was implementing EXPLAIN for writes (INSERT,UPDATE,DELETE,REPLACE), I was pretty excited. Then I heard that MySQL 5.6 also was implementing a JSON format for EXPLAIN and my thought was “I do not care about that!”

Boy, was I wrong. The JSON format does not just put the output into JSON format, it also gives extra information that’s actually pretty useful! It can tell you when you are doing an implicit cast, which parts of a composite index are being used, and when index condition pushdown are being used. None of these are shown in regular EXPLAIN (which seems odd, why could they extend the JSON format but not put the information into the regular EXPLAIN format?), so using the JSON format is actually a good idea even if you do not care about what format your output is in.

As a

  [Read more...]
Upgrading to MySQL 5.5 on Ubuntu 10.04 LTS
+4 Vote Up -0Vote Down

Ubuntu does not provide an apt-get repository package for MySQL 5.5 on this older OS, however this is still a widely used long term support version. The following steps will upgrade an existing MySQL 5.1 apt-get version to a standard MySQL 5.5 binary.

Step 1. Remove existing MySQL 5.1 retaining data and configuration

sudo su -
service mysql stop
cp -r /etc/mysql /etc/mysql.51
cp -r /var/lib/mysql /var/lib/mysql.51
which mysqld
dpkg -P mysql-server mysql-server-5.1 mysql-server-core-5.1
which mysqld
which mysql
dpkg -P mysql-client-5.1 mysql-client-core-5.1
which mysql
dpkg -P libdbd-mysql-perl libmysqlclient16 mysql-common
# This will not remove /etc/mysql if any other files are in the directory
dpkg -l | grep mysql
[ -d /etc/mysql ] && mv /etc/mysql /etc/mysql.uninstall
cp -r /etc/mysql.51 /etc/mysql

2. Prepare configuration  [Read more...]

MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation
+8 Vote Up -0Vote Down

For the past few days, I have been upgrading a few servers. We are going from Percona’s patched MySQL 5.1 to MariaDB 5.5 (the subquery optimization is excellent, and we have lots of subqueries). Our process involves upgrading a slave first, and letting it replicate for a while, and if everything’s good, update more slaves, then the master.

This has served us well in the past. And now that we are checking data integrity between masters and slaves with pt-table-checksum, the process involves checking before we start that there is data integrity. This is easy, as we checksum twice daily and have a Nagios alert if there are any discrepancies. After the upgrade, we checksum again, to be sure no data has been changed/corrupted in the process of doing a

  [Read more...]
On MySQL Memory Usage and Configuration
+1 Vote Up -0Vote Down

I saw a post on profiling memory usage today and this reminds me of several discussions I have had with different people.

Why would you want to profile the memory usage? Usually to see where memory is allocated and for what purposes, and usually you only care when memory usage is higher than expected. That is a DBA normally wants to use all available memory on a server for mysqld, whatever that size may be.

Configuration parameters may be inappropriate and need adjusting, so having a way to determine the range of memory usage based on those parameters would be most helpful.  However, the configuration parameters as a whole put no limit on memory used, so different workloads can quite easily lead to memory being

  [Read more...]
Open Database Camp at Northeast LinuxFest
+4 Vote Up -1Vote Down

I am happy and proud to announce that there will be an Open Database Camp held at this year’s Northeast LinuxFest! The venue is at Harvard University in Cambridge, MA (“our fair city”), and will take place Saturday, March 16 and Sunday, March 17, 2013.

Northeast LinuxFest and Open Database Camp are both free, but there is no reciprocal membership. To register for Open Database Camp, just sign up with Eventbrite. We are also soliciting session ideas ahead of time, and attendees will choose sessions during the Saturday morning planning session, as usual for Open DB Camp.

If you are interested in

  [Read more...]
Not a cool new feature for Master_Host
+1 Vote Up -1Vote Down

I was surprised to find on a customer MySQL server this new syntax for Master_host in SHOW SLAVE STATUS.

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: testdb1.xxx.com or 10.XXX.XX.XXX
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db1-354215-bin-log.000005
          Read_Master_Log_Pos: 1624
               Relay_Log_File: db2-354214-relay-log.000001

Is this a fancy new Percona Server feature? No. It’s operator error.

We read a little further to find.

             Slave_IO_Running: Connecting

                Last_IO_Errno: 2005
  [Read more...]
Installing MySQL MHA with Percona Server
+0 Vote Up -0Vote Down

MySQL MHA by Oracle ACE Director Yoshinori Matsunobu is an excellent open source tool to help in providing HA with native MySQL replication. The installation however is dependent on some Perl packages and to the untrained eye this may be an issue if you are using Percona Server as your choice of MySQL implementation.

The MHA Node page requires the perl-DBD-MySQL package to be installed. The installation on RedHat/CentOS/Oracle Linux look like this:

$ sudo yum install perl-DBD-MySQL
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.anl.gov
* extras: mirror.anl.gov
* updates: mirror.anl.gov
Setting up Install Process
  [Read more...]
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
+1 Vote Up -0Vote Down

This would have to be one of the most common MySQL error messages that is misleading to the end user developer. The MySQL Manual page confirms the broad range of possible conditions, but offers little to a PHP developer that does not speak MySQL Geek. I am commonly asked to help solve this issue from a developer.

The problem is that there are several conditions that can cause this error, and a more meaningful explanation to the end user would help in addressing the issue. In general terms, this actually means “Your SQL statement has failed because the connection to the database has been disconnected because of ???”.

Here are a few common situations and how to check for what “???” is.

1. Your MySQL server really did go away.

  [Read more...]
December News from the Mozilla Database Team
+1 Vote Up -0Vote Down

Being the end of the quarter, there has been some planning going on this month about goals for Q1 2013 as well as meeting our goals for Q4 2012. Our biggest goal was to stop using MySQL 5.0, which we have successfully done. We only have one server left on MySQL 5.0, and that has a compatible MySQL 5.1 server waiting for a few developers to get back from their well-deserved vacations to migrate off. In December, we finished upgrading 2 servers to MySQL 5.1.

- Looked at the top 30 Bugzilla queries and started to give optimization tips for MySQL.
- Did our regular purge/defrag of TinderBox PushLog.
- Worked on integrating our datazilla code with chart.io features.
- Helped change

  [Read more...]
2012 Mozilla DB Year in Graphs
+1 Vote Up -0Vote Down

I’m not a wizard with infographics, but I can do a few pie charts. I copied the data to the right of the pie charts for those that want to see the numbers. Overall, there are almost 400 databases at Mozilla, in 11 different categories. Here is how each category fares in number of databases:

Here is how each category measures up with regards to database size – clearly, our crash-stats database (which is on Postgres, not MySQL) is the largest:

So here is another pie chart with the relative sizes of the MySQL

  [Read more...]
November News From the Mozilla DB Team
+0 Vote Up -0Vote Down

You may have noticed that I stopped posting the “weekly news” from the Mozilla DB Team. After going through the Operations Report Card and applying it to DBAs in OurSQL Podcast numbers 111, 112, 114, 115 and 116, I started thinking that the updates were really more like metrics, and it would better serve my own purposes better to do the updates monthly.

The purposes of doing this type of blog post are:
0) Answering “So what does a DBA do, anyway?”
1) Answering “DBA? At Mozilla? Does Firefox have a database? Why does Mozilla have databases, and what

  [Read more...]
Learn MySQL For Free During a Virtual Self-Study Group!
+0 Vote Up -0Vote Down

Back in the summer, I heard about how Gene Babon of Boston’s PHP Meetup Group was brilliantly helping folks learn web programming through PHP Percolate, jQuery Jam and HTML5 Brunch. While he was explaining this, I thought to myself, “this needs to be done for MySQL!”

And so it will be. Starting in just over a month, January 1st, 2013, the first MySQL Marinate group will happen. We already have over 50 people signed up for the event itself, and as of right now there are 85 people in the meetup group itself, with

  [Read more...]
Open Source Database Schemas
+0 Vote Up -0Vote Down

I am seeking the help of the community. I am working on an evaluation project about schema design in open source applications. While it’s possible for me to download the software of many popular apps, and install the software and then do a mysqldump, it takes time. Quite often there is no simple schema.sql file, but a process for creating the schema. If you are using an open source project, would you take a moment and run the following.

$ mysqldump -u[user] -p --skip-lock-tables --no-data --databases [schema]  > [schema].sql

This will only dump the table definitions, and should therefore contain nothing company specific. I have at this time:

  • WordPress
  • Drupal
  • Mediawiki
  • OS Commerce
  • Joomla
  • EzPublish
  • PHPWiki

I am open to any projects, and it doesn’t matter if the

  [Read more...]
About Master/Master Replication, from Books
+0 Vote Up -0Vote Down

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, and that master/master is not write scaling, but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication

  [Read more...]
database musings (“deep thoughts”)
Employee_Team +2 Vote Up -0Vote Down

I have a soft spot for hierarchical databases.  My first database-related job was programming in M/Mumps.  I know the standard history of databases says that hierarchical databases are a relic of the past, and that, thanks to Codd, relational databases solve many of the problems of hierarchical (and other kinds of) databases.  I like relational databases – I was an Oracle DBA, I’ve worked with DB2, Sybase, Postgres, mSQL, others, and now MySQL.  I really like InnoDB.  However, I am occasionally sad that hierachical databases seem a thing of the past.
Or are they?  Yesterday I had a thought that hierarchical databases are much more widely used than relational databases.  In fact, maybe every single computer has a hierarchical database that is used by every computer user, whether they have database software installed or not.  The file system! 

  [Read more...]
Converting Timezone-Specific Times in MySQL
+0 Vote Up -0Vote Down

Twice last week, a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT

  [Read more...]
MySQL Enterprise Monitor, my super powers and my life
+7 Vote Up -3Vote Down
Last week I had an enlightening experience. It may sound strange, but I found I have super powers... let me explain.

I had a meeting with a big customer of mine to show the value of MySQL Enterprise and Cluster CGE and to share with them the latest and greatest features available in our commercial offerings. During the discussion I said: "Imagine if your MySQL database has a problem at 3pm. Thanks to My Oracle Support and MySQL Enterprise Monitor you can quickly resolve your issues and restore the service".

MySQL Enterprise MonitorWell, this is exactly what happened at 2.50pm... I missed the prophecy by just 10m and found I

  [Read more...]
MySQL 5.6, GTID and performance_schema
+1 Vote Up -0Vote Down

Not much to add really to the bug I’ve filed here: bug#67159.

Again this GTID stuff looks good, but seems to prevent changes in the configuration of performance_schema, which I think is not appropriate, especially as P_S now has lots of extra goodies and after 5.6 will surely have even more.

MySQL-5.6, GTID and binlogs on slaves
+2 Vote Up -0Vote Down

Not much to add really to the bug I’ve filed here: bug#67099.

I personally can think of some very nasty consequences of applying this on the slaves I manage, and the reason I’m posting the bug is that while I guess this is too late to fix in 5.6 as it’s effectively a new feature, I’m sure many sites may bump into this and be somewhat disappointed if they want to use the new GTID feature and have several slaves.  Hence, if the fix/feature has to go into MySQL 5.7 then I hope it goes in sooner rather than later. We will see.

Updated: 2013-09-19

I probably should have updated this earlier but it does seem that Oracle have taken these comments on board. See: WL6559.  It looks like they

  [Read more...]
More GTID troubles in MySQL 5.6
+1 Vote Up -0Vote Down

A colleague, Kris, commented recently on a recent find I made on 5.6.7-rc1 which while not the end of the world does seem to severely limit the use of the new GTID features, or if nothing else make it a pain to use.

Today I found another related issue, when trying to check the syntax of one of the MySQL commands. That’s now reported as bug#67073.

All of these are sort of edge cases but do make 5.6 look a little rough round the edges, when the rest of the product is actually really good and a great improvement from The MySQL 5.5 GA version that everyone uses.  That’s really a shame.

I did report the Original problem as SR 3-6270525721 : MySQL 5.6.7-rc1, grants, replication and

  [Read more...]
Controlled failover simplicity with MySQL
+3 Vote Up -0Vote Down

As part of a recent engagement, I described the relative products to manage a MySQL pair (i.e. an Active/Passive MySQL masters configuration). This included the steps to undertake a controlled failover for supporting software maintenance using manual procedures. The upcoming Effective MySQL: Replication Techniques in Depth book details each step and all conditions to review over a dozen pages. While the steps are straightforward and generally well known, scripting this for your environment takes a certain amount of work to ensure your information is correct, and application connectivity loss is kept to a minimum.

In Continuent Tungsten (which I have just been reviewing these past few weeks), I achieved the same result with a single command.

$ echo "switch" |
  [Read more...]
Joining the Continuent Team
+9 Vote Up -0Vote Down

This month I have joined the team at Continuent. No stranger to the MySQL ecosystem, Continuent provides replication and clustering technology for managing data between MySQL, Oracle, PostgreSQL, Vertica and a growing list of data stores.

I have known many of the team at Continuent for some time, and will again be joining Giuseppe Maxia from our days at MySQL Inc/AB starting back in 2006.

I am looking forward to taking the hard work out of administration of MySQL systems with the simplicity of Continuent Tungsten, simplifying tasks including automatic failover, multi-master and geo cluster redundancy to a single command.

Catch me speaking at the upcoming

  [Read more...]
Checking /proc/pid/numa_maps can be dangerous for mysql client connections
+1 Vote Up -0Vote Down

I’ve blogged before about the way to use numactl to start up mysqld, and thus to try to better spread the memory usage on larger memory servers. This came from an article by Jeremy Cole and is fine. I recently had some issues with mysqld seeming to run out of memory on a box which appeared to have plenty free, so it seemed like a good idea to adapt a minutely collector script I run to include the numa_maps output so that I could see if the failed memory was related to this. So far so good.

Many of the clients that connect to the database servers I manage have a very short connect timeout, typically 2 seconds. In a normal network and under

  [Read more...]
Looking for MySQL 4.1
+2 Vote Up -0Vote Down

I had need today to download a version of MySQL 4.1 to test something. The MySQL Developer Zone archives no longer provides any software before 5.0.

While this may have long reached EOL and is no longer support, customers still do run this version of MySQL.

Anybody that can help out with binaries (on several OS’s), it would be appreciated.

SPOF Internet
+2 Vote Up -0Vote Down

SPOF (i.e. Single Point of Failure) is the bane for technologists. Avoiding SPOF generally requires redundancy, and redundancy has a cost, often more then a business is prepared to pay. In the database field, I see this regularly and advise clients on how to improve availability and potential avoid disasters that can affect their business.

Today, at approximately 10:30am, the Con Edison work crew in front of my home (digging a 5″ deep trench down the road), severed multiple Time Warner Cable fibre connections. ($#&* and the lack of ownership to correct timely is another story). No Internet, no ability to work actively with clients (which I was doing), etc, etc.

As an individual that works from home, I have recognized this SPOF and have redundancy in place. That is, a Verizon MiFi HotSpot, normally used for travel, but a backup in times of Internet downtime to my

  [Read more...]
MySQL client password security
+1 Vote Up -2Vote Down

In case you missed it, MySQL 5.6.6, also known as Milestone 9, was recently released. I have yet to install this, however just one part of the MySQL 5.6.6 Release Notes makes placing installing and testing high on my TODO list.

Updated 20 Sep, 2012. Be sure to also read Todd’s post Understanding mysql_config_editor’s security aspects about a more in-depth and accurate description of this new feature. In summary, “It makes secure access via MySQL client applications easier to use”.

That is the reported improvements in password management. From the release notes:

Security Improvements

These security improvements were implemented:

  [Read more...]
When is a crashing MySQL bug not a bug?
+11 Vote Up -0Vote Down

Answer: When Oracle acknowledges the bug in 5.5.25 (to the owner only), corrects the bug in 5.5.27 (to the owner only), yet hides all information of its existence.

Recently a colleague and good friend discovered a bug in MySQL 5.5 replication that would crash MySQL. This was initially reported as Bug #65740, and after a lot of back and forth, a reproducible test case was found. Excellent work on the part of my colleague to spend the time to clearly identify the specific conditions. I remember looking at this initial thread in detail for an UPDATE statement using variables combined with an –ignore-database configuration option.

For no explanation by Oracle, this bug was subsequently marked as private (after I originally viewed the thread publicly), corrected, and the corrected bug is not

  [Read more...]
Recent Presentations in Cali, Colombia
+1 Vote Up -0Vote Down

On July 4 I gave two presentations at the OTN Tour Day, and on July 5 I have three presentations at the MySQL Training Days. This was my 3rd visit to Colombia and it was great to see a receptive audience. Thanks to Robin for organizing the events in 2010, 2011 and 2012.

You can download all presentations from the provided links.

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