Banner
Home News Feeds Planet MySQL
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • Proposal to deprecate MySQL INTEGER display width and ZEROFILL
    In the MySQL team we are currently discussing if we should deprecate the integer display width in numeric types. For example: CREATE TABLE my_table ( id INT(11) NOT NULL PRIMARY KEY auto_increment ); The (11) does not affect the storage size of the data type, which for an INT will always be 4 bytes. It affects the display width. Our rationale for proposing this should be deprecated is that it is a common source of confusion amongst users. We are also discussing deprecating the non-standard ZEROFILL type attribute, which is the only modern consumer of this display width meta data. For example: CREATE TABLE my_table ( id INT(11) ZEROFILL NOT NULL PRIMARY KEY auto_increment ); INSERT INTO my_table VALUES (1); mysql> SELECT * FROM my_table; +-------------+ | id | +-------------+ | 00000000001 | +-------------+ 1 row in set (0.00 sec) StackOverflow has a good example of how ZEROFILL is useful: [..] In Germany, we have 5 digit zipcodes. However, those Codes may start with a Zero, so 80337 is a valid zipcode for munic, 01067 is a zipcode of Berlin. As you see, any German citizen expects the zipcodes to be displayed as a 5 digit code, so 1067 looks strange. [..] This usage is true for any numeric values that require leading zeros, such as some phone numbers. Upgrade Paths There are two possible upgrade paths to migrate away from ZEROFILL. Option #1 - Move to CHAR/VARCHAR This option is the most transparent for applications, and changes the data type to be a string instead of numeric. For example: CREATE TABLE my_zip_codes ( id INT NOT NULL PRIMARY KEY auto_increment, zip_code INT(5) ZEROFILL ); INSERT INTO my_zip_codes (zip_code) VALUES ('01234'), ('54321'), ('00123'), ('98765'); mysql> select * from my_zip_codes; +----+----------+ | id | zip_code | +----+----------+ | 1 | 01234 | | 2 | 54321 | | 3 | 00123 | | 4 | 98765 | +----+----------+ 4 rows in set (0.00 sec) ALTER TABLE my_zip_codes CHANGE zip_code zip_code CHAR(5); In the case of a CHAR(5) the storage requirements will only be one byte higher than that of an integer. In the case of other data types (phone numbers requiring leading zeros) it might be slightly more efficient to store as an integer. Option #2 - Format integers at a different layer This option retains the storage efficiency of an integer, but moves the presentation into the application. For example: CREATE TABLE my_zip_codes ( id INT NOT NULL PRIMARY KEY auto_increment, zip_code INT(5) ZEROFILL ); INSERT INTO my_zip_codes (zip_code) VALUES ('01234'), ('54321'), ('00123'), ('98765'); ALTER TABLE my_zip_codes CHANGE zip_code zip_code INT; mysql> select * from my_zip_codes; +----+----------+ | id | zip_code | +----+----------+ | 1 | 1234 | | 2 | 54321 | | 3 | 123 | | 4 | 98765 | +----+----------+ 4 rows in set (0.00 sec) It will also technically be possible to retrofit this into legacy applications that require ZEROFILL presentation returning from MySQL. This can be done with a query rewrite plugin to modify SELECT statements to add padding: mysql> SELECT id, CONCAT(REPEAT('0', 5-LENGTH(zip_code)), zip_code) as zip_code FROM my_zip_codes; +----+----------+ | id | zip_code | +----+----------+ | 1 | 01234 | | 2 | 54321 | | 3 | 00123 | | 4 | 98765 | +----+----------+ 4 rows in set (0.01 sec) Conclusion We are seeking feedback from the community in response to this proposal. If you have found the existing behavior confusing, or will be affected by the removal of zero fill, please leave a comment or get in touch! We would love to hear from you.

  • Connector/J 5.1
    Connector/J 5.1 (5.1.36 GA, published on Thursday, 02 Jul 2015)

  • Percona Server 5.5.44-37.3 is now available
    Percona is glad to announce the release of Percona Server 5.5.44-37.3 on July 1, 2015. Based on MySQL 5.5.44, including all the bug fixes in it, Percona Server 5.5.44-37.3 is now the current stable release in the 5.5 series.Percona Server is open-source and free. Details of the release can be found in the 5.5.44-37.3 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.Bugs Fixed:Symlinks to libmysqlclient libraries were missing on CentOS 6. Bug fixed #1408500.RHEL/CentOS 6.6 OpenSSL package (1.0.1e-30.el6_6.9), containing a fix for CVE-2015-4000, changed the DH key sizes to a minimum of 768 bits. This caused an issue for MySQL as it uses 512 bit keys. Fixed by backporting an upstream 5.7 fix that increases the key size to 2048 bits. Bug fixed #1462856 (upstream #77275).innochecksum would fail to check tablespaces in compressed format. The fix for this bug has been ported from Facebook MySQL 5.1 patch. Bug fixed #1100652 (upstream #66779).Issuing SHOW BINLOG EVENTS with an invalid starting binlog position would cause a potentially misleading message in the server error log. Bug fixed #1409652 (upstream #75480).While using max_slowlog_size, the slow query log was rotated every time slow query log was enabled, not really checking if the current slow log is indeed bigger than max_slowlog_size or not. Bug fixed #1416582.If query_response_time_range_base variable was set as a command line option or in a configuration file, its value would not take effect until the first flush was made. Bug fixed #1453277 (Preston Bennes).Prepared XA transactions with update undo logs were not properly recovered. Bug fixed #1468301.Variable log_slow_sp_statements now supports skipping the logging of stored procedures into the slow log entirely with new OFF_NO_CALLS option. Bug fixed #1432846.Other bugs fixed: #1380895 (upstream #72322).(Please also note that Percona Server 5.6 series is the latest General Availability series and current GA release is 5.6.25-73.0.)Release notes for Percona Server 5.5.44-37.3 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.The post Percona Server 5.5.44-37.3 is now available appeared first on MySQL Performance Blog.

  • Percona Server 5.6.25-73.0 is now available
    Percona is glad to announce the release of Percona Server 5.6.25-73.0 on July 1, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories.Based on MySQL 5.6.25, including all the bug fixes in it, Percona Server 5.6.25-73.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – and this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release can be found in the 5.6.25-73.0 milestone on Launchpad.New Features:Percona Server has implemented support for PROXY protocol. The implementation is based on a patch developed by Thierry Fournier.Bugs Fixed:Symlinks to libmysqlclient libraries were missing on CentOS 6. Bug fixed #1408500.RHEL/CentOS 6.6 OpenSSL package (1.0.1e-30.el6_6.9), containing a fix for CVE-2015-4000, changed the DH key sizes to a minimum of 768 bits. This caused an issue for MySQL as it uses 512 bit keys. Fixed by backporting an upstream 5.7 fix that increases the key size to 2048 bits. Bug fixed #1462856 (upstream #77275).Some compressed InnoDB data pages could be mistakenly considered corrupted, crashing the server. Bug fixed #1467760 (upstream #73689) Justin Tolmer.innochecksum would fail to check tablespaces in compressed format. The fix for this bug has been ported from Facebook MySQL 5.6 patch. Bug fixed #1100652 (upstream #66779).Using concurrent REPLACE, LOAD DATA REPLACE or INSERT ON DUPLICATE KEY UPDATE statements in the READ COMMITTED isolation level or with the innodb_locks_unsafe_for_binlog option enabled could lead to a unique-key constraint violation. Bug fixed #1308016 (upstream #76927).Issuing SHOW BINLOG EVENTS with an invalid starting binlog position would cause a potentially misleading message in the server error log. Bug fixed #1409652 (upstream #75480).While using max_slowlog_size, the slow query log was rotated every time slow query log was enabled, not really checking if the current slow log is indeed bigger than max_slowlog_size or not. Bug fixed #1416582.Fixed possible server assertions when Backup Locks are used. Bug fixed #1432494.If query_response_time_range_base variable was set as a command line option or in a configuration file, its value would not take effect until the first flush was made. Bug fixed #1453277 (Preston Bennes).mysqld_safe script is now searching for libjemalloc.so.1 library, needed by TokuDB, in the basedir directory as well. Bug fixed #1462338.Prepared XA transactions could cause a debug assertion failure during the shutdown. Bug fixed #1468326.Variable log_slow_sp_statements now supports skipping the logging of stored procedures into the slow log entirely with new OFF_NO_CALLS option. Bug fixed #1432846.TokuDB HotBackup library is now automatically loaded with mysqld_safe script. Bug fixed #1467443.Other bugs fixed: #1457113, #1380895, and #1413836.Release notes for Percona Server 5.6.25-73.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker .The post Percona Server 5.6.25-73.0 is now available appeared first on MySQL Performance Blog.

  • MariaDB with Galera available on the IBM Power8 platform
    Wed, 2015-07-01 12:54anderskarlssonIt was a very long time since I wrote something in this blob, but I have been very busy this spring with MariaDB on Power. This has been a lot of work, but also a lot of fun. So, what is this MariaDB on Power thing all about, well I wrote an introduction to the Power platform late last year. Since then a lot of things has happened. One thing is that several service providers out there have adopted Power8 as a platform. To be honest, this really isn't sexy, but it is useful and as a user of one of these services, you will just see the same old Linux you are used to, but potentially it is more powerful and reliable. One such provider is OVH, whose service is more known as RunAbove. If you want to try it, you can do so for free for 7 days, just go there and off you go. Another important thing is that MariaDB is now available on Power8 running, RedHat, SUSE or Ubuntu Linux. To get access to this, pop by MariaDB and if you are not yet signed up, then do this now and then go to "My Portal", further to "Downloads" and then select "MariaDB Enterprise and MariaDB Enterprise Cluster". You are now ready to install using the operating system of your choice, but on Power you are, as I said before, limited to SUSE, RedHat and Ubuntu, and if you want to test MariaDB Enterprise Cluster, i.e. MariaDB with Galera, you have to go with Ubuntu. Installing MariaDB Enterprise Cluster on Power8 is no more complex than on Intel. There are a few thing to adjust before you can get started with this, after having installed the software. The first node has, as usual, to be configured with wsrep_cluster_adress set to gcomm:// to ensure that this first node will bootstrap without having to connect to a cluster. Once the cluster is up and running though, this variable is set to the cluster addresses. In my case, this what the Galera setting look like in /etc/mysql/my.cnf which is the location of this file on Ubuntu. # Galera wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="GaleraPower1" wsrep_cluster_address=gcomm://92.127.22.124 wsrep_node_address=92.127.22.121 wsrep_node_name=galera3 binlog_format=ROW Note in particular the binlog_format setting. This MUST be set to ROW for Galera to work. But the fact is that these settings are not particular to MariaDB on Power, this is the same even on Intel. If this isn't enough to convince you about the advantages of running MariaDB on IBM Power, then see what Foedus in Italy has to say about the combination in this video. There is more to say about running MariaDB on Power and there is more to come here, I'll look at some performance data, we'll have a look at MaxScale on Power (this is not official yet, but that isn't stopping me) as well as a blog on how to run a Power8 emulation on Intel which I have promised before. So, don't touch that dial! /Karlsson Originally posted on Karlsson on databases and stuff Tags: GaleraHigh AvailabilityMariaDB Enterprise About the Author Anders Karlsson is a Sales Engineer with a long experience from the field of database software. Anders has worked for many of the major database software companies.

Banner

 

Copyright © 2001 - 2013 K-Factor Technologies, Inc.



Site Meter

a href=