PlanetMysql.ru — информация о СУБД MySQL

At the IOUC leaders’ summit in San Francisco this week, key leaders from Oracle, Java and MySQL user groups world wide have been meeting. This has included the key Oracle MySQL resources from the community, marketing and product teams. The Java User Groups and MySQL User Groups have been well represented and there has been very welcoming discussion with the IOUC about how we can become active within the Oracle Community.

There has been key discussions of upcoming and proposed MySQL events including the great outreach by the Oracle MySQL team with existing Open Source conferences this year including Scale, FOSDEM and South East Linuxfest just to name a few.

You can see the upcoming events at http://mysql.com/news-and-events/events/. In February alone there will be events in North Carolina, California, Texas, Frankfurt Germany and Stockholm Sweden. I will also be speaking in Denver at 2 events and Salt Lake City.

There is a much longer list then what is shown here, and we are working on getting the full list more available.

More info by Dave Stokes at SCaLE’s MySQL Day a big hit and Keith Larson More User Groups.

As a consultant I often use the MySQL Reference Manual to provide additional information for clients. I am very happy to recognize the quality of the content in the MySQL documentation, but why is the searching of the manual so, so bad?

While reading the General Security Issues section of the MySQL 5.5 manual, I performed a search for “CREATE USER”. I was not asking for anything abstract, this is an actual SQL command. I was rather horrified to find that the results could not even list the appropriate manual page in the first page of results.

I am not an expert in full-text search, however it does not take a rocket scientist to realize that a SQL keyword, the title of a page, in the language of the current page (English) and the current version of the Manual (5.5) should be an easy result. This is a simple weighted result right? Wrong.

In my opinion, The Server Option and Variable Reference at http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html rates as my most important page. This is a consolidated index that enables a drill down to the Server Command Options, System Variables, Startup and replication specifics, as well as important information on default values and differences between versions including point releases.

However, there is another page not in the actual manual, but at http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-optvar.html which is an Options/Variables reference akin to the Reference Manual, but includes a 5.x version matrix.

Recently I was asked about some options that had to be removed from an upgrade to MySQL 5.5. Some of these were obvious, however not all. This page enabled me to confirm deprecation (as expected), and also point to important reference material.

These options where:

• default_table_type
• log_long_format
• master-connect-retry
• default-character-set

The use of table_type was a 3.x/4.x relic, replaced with engine, so I was surprised this option was still even valid. The option replaced with default_storage_engine. The page defined this as deprecated in MySQL 5.0
log_long_format is also old, and definitely modified since MySQL 5.1 with the general_log_xxx options. In fact this has been deprecated since 4.1
I have never liked the master-xxx options, in favor of a CHANGE MASTER command and synchronization issues with the master.info file and master-xx options. master-connect-retry and several other options were deprecated in 5.1.17. On a side note, if you look at this option in the MySQL 5.5 Reference Manual you get Obsolete options. The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO … statement (see Section 12.4.2.1, “CHANGE MASTER TO Syntax”).
Finally default-character-set. Initially I thought that is definitely still applicable, however the handy cross reference reminded me, this is also deprecated in MySQL 5.0 and the Reference Manual again stating default-character-set is also deprecated in 5.0 in favor of character-set-server.. The name has simply changed in newer version.

With so many options and as a consultant I work with many different versions each week, I sometimes need a refresher of the changes in the versions of the past 5 years. Definitely my second most important page.

If you have a favorite page, please let me know.

I would also like to say thank you to the Oracle/MySQL Documentation team that do a great job in providing an excellent resource to an open source product. We would all do well to appreciate this in comparison to other open source documentation in companion technologies and related tools. With every new release of a MySQL product you don’t realize that somebody reviewed, tested and wrote about features without receive the limelight.

When using the replication slave stream, or mysql command line client and mysqlbinlog output from a binary/relay log, all statements are executed in a single thread as quickly as possible.

I am seeking a tool to simulate the replay of the binary/relay log for a benchmark at a pace that is more representative to original statements. For a simple example, if the Binary Log has 3 transactions in the first second, 2 transactions in the second second, and 5 transactions in the third second, I am wanting to simulate the replay to take roughly 3 seconds, not as fast as possible (which would be sub-second). The tool should try to wait the remainder of a second before processing SQL statements in the incoming stream.

Does anybody know of a tool that currently provides this type of functionality? Any input appreciated before I create my own.

Following a heavy schedule in the last month speaking in Tokyo, Beijing, Manila and Auckland it is nice to be on home soil for upcoming speaking. I will be in Denver, Colorado for RMOUG 2012 from February 14-16, 2012 where I will be speaking about the essentials of MySQL security.

I hope to also organize another presentation in the area for the local MySQL users group. More to follow.

And a friendly reminder, the annual MySQL conference is on again, same place (Santa Clara), same dates (April 10-12), same great speakers (too many to say), just a new primary sponsor. Why not submit a paper to present. Call for papers is still open, closing on December 5. More information at Percona Live MySQL Conference & Expo 2012.

Oracle has announced an open source product for the NoSQL space, the Oracle NoSQL Database. Unlike other popular products including Redis, MongoDB, Cassandra, Voldermort and many others, Oracle has set a benchmark on the features that are truly necessary for highly available data systems.

Many products in the NoSQL space have told you that consistency is not needed, eventual consistency is good enough, that transactions are not performant enough to include as a feature. No standards exists, there is no common interface for communication, or key features that products aim to meet or better. With this product, features including transactions, replicated data and failover which are built in, are features other open source NoSQL products will need to match.

Oracle NoSQL Database is a key value store, supporting a major/minor key for co-locating regularly accessed information for more consistent data retrieval. The API (built in Java) supports GET, PUT, and DEL operators. The system is designed to not have a single point of failure, and to support a node failure without impact. The replication factor is reported to enable up to 7 copies of information, which would be a feature to support cross data center management. The database driver is latency aware, so this can support load balancing operations for optimal performance.

I am excited to hear about this and looking forward to evaluating the software. I will be watching more closely how the integration of MySQL and Oracle NoSQL can be an offering for startups and Web 2.0

At Oracle Open World 2011 there has been a large number of MySQL presentations. You can download the slides of my two presentations at Explaining the MySQL Explain and Improving Performance with Better Indexes.

You can find additional supporting information for these presentations in the Effective MySQL: Optimizing SQL Statements book.

Announced on Sunday at Oracle Open World 2011 is the release of the Effective MySQL book series starting with the “Optimizing SQL Statements” title. The goal of the Effective MySQL series is a highly practical, concise and topic specific reference providing applicable knowledge to use on each page. A feedback comment provided today was “no fluff” which is great comment to re-enforce the practical nature of the series.

Details on the Effective MySQL Optimizing SQL Statements page include a sample chapter, code downloads and purchase links for print and e-books at Amazon, McGraw-Hill and Barnes & Noble.

I will be joining a stellar class of speakers at the Insight Out DB Showcase in Tokyo from Oct 19-21, 2011. This event covers several RDBMS technologies including MySQL.

My topics are:

• Better MySQL Security and Administration
• Mastering MySQL Performance Tuning

Today was another example of where a correct SQL_MODE saved customer data from being corrupted. By default, MySQL does not enforce data integrity. It allows what is called silent truncations where the result of what you INSERT or UPDATE does not represent truth. NOTE: I see very few customers ever have this correctly configured, those that do have actually listened to my advice.

If you do not read any further, your production MySQL environments should be running with at the bare minimum of SQL_MODE=STRICT_ALL_TABLES however I would also advocate for additional SQL_MODE settings.

For this example, some modified undesirable code attempted to reduce a counter by 1, however because of an UNSIGNED data type and a correctly set SQL_MODE, the application produced an error and data was not corrupted.

This is what should happen with your SQL.

mysql> update stats set loss_count=loss_count - 1 where user_id=42;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`stats`.`loss_count` - 1)'

It is interesting to note that the error message actually is misleading. The datatype for the column is SMALLINT however the error message prompted an unnecessary schema verification. Even with the calculation I would have assumed the inherited data type would be the column definition before the subtraction. If you try to set the value automatically to negative you get a better message.

mysql> update stats set loss_count=-1 where user_id=42;
ERROR 1264 (22003): Out of range value for column 'loss_count' at row 1

So why is this a big deal? If your MySQL instance is not using SQL_MODE then the result would have been a value of 0 and this would never highlight the bug in the code.

I should point out that only Drizzle of the MySQL forks and variants has tightened up this data integrity, however I have to agree to disagree with the decision to drop UNSIGNED. While I under internally the code simplification behind the decision, without check constraints in MySQL, UNSIGNED is a saving grace. In this example, Drizzle in this case would not have reported an error.