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

Displaying posts with tag: MySQL 5.6 (reset)

Recalculating InnoDB Persistent Statistics - a Story of the Bug Report
+0 Vote Up -0Vote Down
One of the first posts in this blog was about reporting MySQL bugs "properly", in a way that maximizes chances for it to be processed really soon. I had written the following there:
"Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"
Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to time, and Oracle engineers who work on bugs are not exceptions. But does this mean that bug without clear test case has no value and is going to be ignored?

It should NOT be the case. Let's


  [Read more...]
time for standards 2
+2 Vote Up -0Vote Down
I was a bit wrong in my previous post. MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).

mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP(6) |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be:
mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT











  [Read more...]
time for standards
+0 Vote Up -0Vote Down
MySQL 5.6 includes support for microsecode timestamp resolution, which is a great new feature.

To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP.

mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW() | SYSDATE() | CURRENT_TIMESTAMP |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond











  [Read more...]
Exam Cram: General MySQL Syntax for Developers (Section 2)
Employee +3 Vote Up -0Vote Down

The General MySQL Syntax section of the MySQL 5.6 Developer certification exam is a bit meatier than the MySQL Architecture section covered in my last post, but it’s still likely to be very familiar to experienced MySQL developers (or DBAs):

  • Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words
  • Identify MySQL data type properties and appropriate usage
  • Recognize and use common functions and expressions for all MySQL data types
  • Identify and use comment syntax
  • Describe and utilize prepared statements
  • Describe transactions and transaction isolation levels
  [Read more...]
MySQL 5.6’s new replication features: Benefits, Limitations and Challenges
+1 Vote Up -0Vote Down

On Wednesday I’ll be leading a webinar exploring MySQL 5.6’s new replication features. And yes, as usual I’ll deliver news on the good, the bad and the ugly (that is to say the benefits, limitations and challenges).

The webinar, appropriately titled, “New Replication Features in MySQL 5.6: Benefits, Limitations, and Challenges“, is scheduled for Oct. 23 at 10 a.m. Pacific Daylight Time. You can

  [Read more...]
Using the new spatial functions in MySQL 5.6 for geo-enabled applications
+1 Vote Up -0Vote Down

Geo-enabled (or location enabled) applications are very common nowadays and many of them use MySQL. The common tasks for such applications are:

  • Find all points of interests (i.e. coffee shops) around (i.e. a 10 mile radius) the given location (latitude and longitude). For example we want to show this to a user of the mobile application when we know his/her approximate location. (This usually means we need to calculate a distance between 2 points on Earth).
  • Find a ZIP code (U.S. Postal address) for the given location or determine if this location is within the given area. Another example is to find a school district for the given property.

MySQL had the spatial functions originally (implementation

  [Read more...]
FAQ: InnoDB extended secondary keys
Employee +4 Vote Up -0Vote Down
MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I'll try to get rid of the confusion once and for all. Famous last words... here goes:

Q1: Do I need to do anything to enable extended secondary keys?

No, nothing at all. It's on by default and I can't see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch='use_index_extensions={on|off}'.

Q2: Does extended secondary keys only work with InnoDB?

No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say







  [Read more...]
MySQL Certification Study: Write a basic .NET application that uses MySQL
+6 Vote Up -0Vote Down
I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done.

One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL".

I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago.

I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code.

So I need a short refresh.

To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using.
  • CIL 1.0: mcs
  • CIL 2.0: gmcs
  • CIL











  [Read more...]
Exam Cram: MySQL Architecture for Developers (Section 1)
Employee +3 Vote Up -0Vote Down

The first section in the exam topics for both the MySQL 5.6 Developer and DBA exam deals is titled MySQL Architecture, but each has a different emphasis.  This blog will focus on those objectives listed for the Developer exam:

  • Use MySQL client programs to interface with the MySQL Server interactively and in batch
  • Describe SQL Modes and their impact on behavior of MySQL
  • Identify characteristics which have session scope

For those of us who have spent time pulling both DBA and developer duties, this section is likely to be relatively straight-forward – perhaps even easy.  I’ll walk through the exercises and documents I’ve used to review below to kick off my

  [Read more...]
Exam Cram: Preparing for the MySQL 5.6 certification exams
Employee +3 Vote Up -0Vote Down

As noted in earlier posts, exams for the MySQL 5.6 Developer and DBA certifications are entering a beta period, allowing candidates to register for the exams at steep discounts from normal certification exam fees.  I’ve registered to take both exams late this month, and – like Moritz – I’m starting to prepare for the exams now.  For those considering sitting for the exams, my plan is to document my exam preparation in a series of blog entries.  This won’t be a

  [Read more...]
Innotop: A real-time, advanced investigation tool for MySQL
+2 Vote Up -0Vote Down

GUI monitoring tools for MySQL are not always suitable for all our needs or situations. Most of them are designed to provide historical views into what happens to our database over time rather then real-time insight into current MySQL server status. Excellent free tools for this include Cacti, Zabbix, Ganglia, Nagios, etc. But each of them needs to be properly configured to provide details on what is going on in our MySQL instances. And setting up one of these monitoring solutions is neither

  [Read more...]
MySQL Connect HOL content posted
Employee +1 Vote Up -0Vote Down

Just a quick post to note that the content from my hands-on lab at MySQL Connect (“MySQL Enterprise Features in Practice”) has been uploaded to the content catalog, and can be found here.  This includes the 36-page lab manual and example commands and programs (mostly in Java; the package includes both compiled and source code).  For those who attended the lab, this is an opportunity to complete the exercises we didn’t get to in the 2.5 hours, and for those who missed it, an opportunity to learn more about the features and capabilities of key MySQL Enterprise products and features such as MySQL Enterprise Audit plugin, MySQL Enterprise Monitor

  [Read more...]
Why you should register for MySQL 5.6 certification exams now
Employee +3 Vote Up -0Vote Down

I’ve previously shared my excitement over the coming Oracle Certified Professional exams for MySQL 5.6 (both Developer and DBA), and I’m putting my (employer’s) money where my mouth is – I’ve registered to sit for the DBA exam in early November (some test sites have limited opportunities; interested candidates should consider booking now).  Here’s why I think others should do the same:

Low cost

During the beta period (through December 14), the cost of the exam is only $50 USD.  That’s barely more than the cost of a book or study guide these days, and a significant savings from the normal exam prices.

Full certification

Those who pass the exam will be given a full

  [Read more...]
Creating custom rules in MySQL Enterprise Monitor
Employee +3 Vote Up -0Vote Down

Quite some time ago, I published scripts to implement password policies for MySQL, and promised to show how to expose violations of that policy via MySQL Enterprise Monitor (MEM).  That stalled somewhat with other objectives, but I want to revisit it now that MEM 3.0 is GA.  If you haven’t tried MEM 3.0 yet, consider doing so – it’s quick and easy to set up.

Many people don’t realize that MEM can be extended to monitor things beyond MySQL Server health, including visibility into application state as

  [Read more...]
MySQL 5.6 Configuration Optimization Webinar, Sept. 25
+1 Vote Up -0Vote Down

This Wednesday in our next webinar I’ll share how to configure a better-performing MySQL 5.6 server. You’ll lean a practical approach to generating a sensible configuration file that sets what is needed and omits what is not.

Why dedicate an entire webinar to the new configuration settings within MySQL 5.6? Mainly because the default configuration files that come with MySQL 5.6 are not designed for high volume production use, and I’ve seen many MySQL incidents caused by poor configuration. Hopefully my

  [Read more...]
New MySQL 5.6 Developer Certification Exam
Employee +4 Vote Up -0Vote Down

You may have already seen the news: Oracle is launching an updated certification for MySQL Developers, based on MySQL Server 5.6.  This is exciting to me for several reasons that I want to share:

Only one exam

Earlier versions of the MySQL Developer certification required sitting for two separate exams.  One goal in redefining the certification process was to make it more accessible to candidates, and reducing the cost – in both time and money – developers need to invest in examinations.  This obviously presented a challenge to cover the same material – actually more, when you consider MySQL 5.6 features – in fewer questions, but we feel we’ve done it.

Content from the experts

Content creation for this exam has

  [Read more...]
Enabling crash-safe slaves with MySQL 5.6
+2 Vote Up -0Vote Down

Being able to configure slaves to be crash-safe is one of the major improvements of MySQL 5.6 with regards to replication. However we noticed confusion on how to enable this feature correctly, so let’s clarify how it should be done.

In short

1. Stop MySQL on slave
2. Add relay_log_info_repository = TABLE and relay_log_recovery = ON in my.cnf
3. Restart MySQL and relax

The gory details

To fully understand why you should change the above settings if you want crash-safe slaves, let’s first look at the reasons why replication can break when a slave crashes.

On a slave, replication involves 2 threads: the IO thread which copies the binary log of the master to a local copy called the relay log and the SQL thread which then executes the queries written in the relay log. The current



  [Read more...]
Fun with Bugs #23 - more on Optimizer bugs in MySQL 5.6
+3 Vote Up -0Vote Down
When I've sent CV to MySQL AB back in 2004 (or early 2005) I had actually wanted to become a developer there. As a person who just started to use MySQL on a regular basis and, at the same time, had to explain dozens of engineers per month how optimizers works in Oracle and Informix RDBMSes, and optimize queries for them from time to time, I was naturally interested in adding missing (but well known to me) features to MySQL optimizer (from hash joins to stored outlines, histograms and tracing, all things I've noted as extremely useful for many real life cases)... So, I wanted to work on MySQL optimizer specifically, if something related to MySQL at all.

It happened so that MySQL Support had somehow noted my CV before anybody else, so in few months and after some serious tests and long screening talks (the longest of them was with new, at the time,

  [Read more...]
Here’s my favorite secret MySQL 5.6 feature. What’s yours?
+2 Vote Up -0Vote Down

MySQL 5.6 has a great many new features, including, but certainly not limited to a number of performance improvements. However, besides the widely talked-about features such as InnoDB support for full text search, optimizer, performance schema improvements and

  [Read more...]
Running MySQL 5.6 on Amazon RDS: Webinar followup questions answered
+1 Vote Up -0Vote Down

Thanks to everyone who attended last week’s webinar, Running MySQL 5.6 on Amazon RDS.” If you weren’t able to attend, the recording and slides are available for viewing/download (or, if you were able to attend and just want to see it again). I’ve also answered the questions I didn’t have a chance to field during the event:

Q: Would you recommend Amazon RDS over manually setting up MySQL/Percona server on an EC2 instance?
A: This depends on many factors including your data set size,

  [Read more...]
Running out of Disk Space? Move innodb-tables to another partition (with MySQL 5.6)
+0 Vote Up -0Vote Down

Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.

Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.

First get the 15 biggest tables:

root@mysqlserver [(none)]> set global innodb_stats_on_metadata=0; select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY
  [Read more...]
Cloning MySQL 5.6 instances
+1 Vote Up -0Vote Down

A tip for all those cloud users that like cloning database servers (as reported in my book Effective MySQL – Replication Techniques in Depth).

Starting with MySQL 5.6, MySQL instances have a UUID. Cloning servers to quickly create slaves will result in the following error message.

mysql> SHOW SLAVE STATUS\G
...
  Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
...

The solution is simple. Clear our the file based configuration file (located in the MySQL datadir) and restart the MySQL instance.

$ rm -f /mysql/data/auto.cnf
$ service mysql restart
Amazon RDS with MySQL 5.6 – Configuration Variables
+3 Vote Up -0Vote Down

One longstanding complaint I have heard for the past several years, and still hear today, is that Amazon’s Relational Database Service (RDS) does not allow the configuration flexibility as running MySQL in an ec2 instance. While true, this ignores the consistent work that Amazon has done to provide access to the most important configuration variables needed to tune a MySQL instance (after all, how relevant is it for a customer to set bind_address in an RDS instance).

Let’s take a look visually:

  [Read more...]
MySQL Security: Armoring Your Dolphin
+1 Vote Up -0Vote Down

My colleague and teammate Ernie Souhrada will be presenting a webinar on Wednesday, August 21, 2013 at 10 a.m. PDT titled “MySQL Security: Armoring Your Dolphin.”

This is a popular topic with news breaking routinely that yet another Internet company has leaked private data of one form or another. Ernie’s webinar will be a

  [Read more...]
Understanding max_connect_errors
Employee +3 Vote Up -0Vote Down

To only slightly misquote one of the greatest movies of all times:

You keep using that option.  I do not think it means what you think it means.

 

Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice.  I’m writing this blog post to help others who hold similar misconceptions of what this option does.

Many, if not most, MySQL DBAs are familiar with “host blocked”

  [Read more...]
Percona Server 5.6 Webinar follow-up and Q&A
+1 Vote Up -0Vote Down

Good news everyone! I recently presented a webinar: Percona Server 5.6: Enterprise Grade MySQL. It was also recorded so you can watch along or view the slide deck. As with all my talks, I am not simply reading the slides so it really is worth to listen to the audio rather than just glance through the slide deck.

There were a number of great questions asked which I’ll answer below:

Q: How does Stewart feel about this version of 5.6 taking into consideration “Stewart’s .20 rule?” (ref 2013 Percona Live Conference).

A: For those who aren’t familiar with it, I have a rule which I call “Stewart’s dot twenty rule” which I’ve posted a few times about on

  [Read more...]
Spring Cleaning: Useless protocol commands
Employee +2 Vote Up -0Vote Down

In an earlier post, I commented on clients and utility programs which seem to no longer be useful, and opened (or referenced existing) public bug reports to deprecate and remove, where appropriate.  That effort came actually was the product of a different initiative:  I was looking for clients which might leverage the full spectrum of MySQL protocol commands in an effort to understand whether certain protocol commands are in use.  I thought I would share my observations, in the hope that we might also get feedback from others regarding usage of these commands. And even though it’s no longer spring (I started this post in April), I finally found time to finish this post.

The

  [Read more...]
Practical P_S: Fixing gaps in GLOBAL STATUS
Employee +1 Vote Up -0Vote Down

Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools.  Mark Leith even provided a patch, but it’s never been incorporated.  With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:

mysql> SELECT SUM(count_star) as pings
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
|    12 |
+-------+
1 row in set (0.02 sec)


Not only does PERFORMANCE_SCHEMA provide capabilities which mirror the STATUS

  [Read more...]
Standalone MySQL Utilities Now GA! Includes running mysqlfailover as a daemon
Employee +3 Vote Up -0Vote Down

With the release of MySQL Utilities 1.3.4, the standalone (not bundled with the MySQL WorkBench GUI) package is now Generally Available and fully supported. This post will focus on a very important change (the ability to run as a daemon rather than in a terminal) to the mysqlfailover utility which allows you to build a light-weight HA database solution using MySQL Replication.

For a general overview of MySQL Utilities,

  [Read more...]
Option prefixes deprecated
Employee +0 Vote Up -0Vote Down

MySQL 5.6.13 was released earlier this week, and in that release (as well as 5.5.33) the ability to use unique option prefixes was deprecated.  This is fully removed from MySQL 5.7, and I thought it might be useful to amplify the change log notes on why this was done:

Previously, program options could be specified in full or as any unambiguous prefix. For example, the --compress option could be given to mysqldump as --compr, but not as --comp because the latter is ambiguous. Option prefixes now are deprecated. They can


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