Archive for the ‘SkySQL’ Category

MariaDB 5.5.37 Overview and Highlights

Wednesday, April 23rd, 2014

MariaDB 5.5.37 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.37/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

  • Includes all bugfixes and updates from MySQL 5.5.37
  • XtraDB updated to the version from Percona Server 5.5.36-34.0
  • TokuDB updated to version 7.1.5
  • Default compression for TokuDB is now TOKUDB_ZLIB (instead of TOKUDB_UNCOMPRESSED)
  • The MariaDB Audit Plugin now included.

Given the number of InnoDB and XtraDB fixes (in 5.5.37 and 5.5.36 respectively), if you are using InnoDB or XtraDB+ in MariaDB 5.5, then I would definitely recommend upgrading to MariaDB 5.5.37.

If interested, there is more about the 5.5.37 release here:

https://mariadb.com/kb/en/mariadb-5537-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.37 can be found here:

https://mariadb.com/kb/en/mariadb-5537-changelog/

Hope this helps.

 

WebScaleSQL on Windows? I wish, but not quite yet, it seems …

Thursday, April 17th, 2014

For fun, I tried building WebScaleSQL on Windows, even though it’s not [yet?] a support platform.

Using the following (as I would to build MySQL/MariaDB):

cd c:\mysql\webscalesql-5.6.16
mkdir bld
cd bld
cmake ..
cmake --build . --config relwithdebinfo --target package

I end up with:

...
    238 Warning(s)
    110 Error(s)
Time Elapsed 00:05:08.53

Looking through the output, the main error is this:

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h(33):
fatal error C1083: Cannot open include file: 'atomic':
No such file or directory
[C:\mysql\webscalesql-5.6.16\bld\storage\innobase\innobase.vcxproj]

Of course the directory does exist, and permissions are correct.

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h contains the following line:

#include <atomic>

And this exists:

C:\mysql\webscalesql-5.6.16\include\atomic

So there must be some reason Windows doesn’t like that include. As far as I can tell, most other includes specify an exact file, and not a directory.

I tried replacing the single include by specifying each .h file included in atomic\, but that resulted in many more errors (776).

If any ideas, please feel free to share. And/or if this is futile, fine to mention that also. :)

 

Resolving Error 1918, System Error Code 126, When Installing MySQL ODBC Driver

Thursday, April 17th, 2014

If you are installing MySQL ODBC Driver and encounter the following error:

Error 1918. Error installing ODBC driver MySQL ODBC 5.1 Driver,
ODBC error 13: The setup routines for the MySQL ODBC 5.1 Driver
could not be loaded due to system error code 126:
The specified module could not be found.
...\myodbc5S.dll).. Verify...

Then you will need to install the Microsoft Visual C++ 2010 Redistributable Package (select the appropriate one for your OS architecture below):

64-bit version:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

32-bit version:

http://www.microsoft.com/en-gb/download/details.aspx?id=5555

After installing that, then re-attempt installing the MySQL ODBC connector, and things should work smoothly.

Hope this helps.

 

Resolving MySQL ODBC “architecture mismatch” Error

Thursday, April 17th, 2014

If you attempt to use ODBC to run a MySQL application and run into the following error:

[Microsoft][ODBC Driver Manager] The specified DSN contains an
architecture mismatch between the Driver and Application

This means there is a 64-bit versus 32-bit mismatch.

Most likely, you’re running 64-bit Windows, as well as 64-bit MySQL ODBC connector, but the application is 32-bit.

If this is the case, you will also need to install the 32-bit MySQL ODBC connector, and then create the connection from the 32-bit ODBC.

odbcad32.exe is the file to create the connections. Both 64-bit and 32-bit files have the same name, just differing locations.

This is the default location for the 64-bit ODBC:

C:\Windows\System32\odbcad32.exe

This is the default location for the 32-bit ODBC:

C:\Windows\SysWOW64\odbcad32.exe

And should you need to install MySQL ODBC, there are good instructions here (both for MSI and Manual installs).

Hope this helps.

 

Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors

Wednesday, April 16th, 2014

The biggest concern with a slave is to ensure your data is consistent with the master! End of story!

3 of the biggest things I see when dealing with out-of-sync slaves:

  1. Many users do not use the --read-only option on their slaves.
  2. Some of those who do often have numerous users with SUPER who can still perform writes.
  3. Many users simply use --slave-skip-errors=… to avoid common errors.

Of course, if you have a slave, definitely use the --read-only option.

However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).

And the use of --slave-skip-errors=… is generally just a quick fix to avoid errors on the slave, but a sure fire way to end up with inconsistent data. Please don’t just use this blindly, but rather figure out why you are receiving such errors and resolve them the correct way. (We’re happy to assist if you need help with this.)

In conclusion, if the consistency of the data on your slave matters to you/your business/your customers, and/or if you’ve already had problems with inconsistent data, then add --read-only, remove --slave-skip-errors=…, and remove SUPER from as many users that use the slave as possible. That will be a terrific start.

If you are really serious about having a “read only” slave, and you use InnoDB, then as of MySQL 5.6 and MariaDB 10.0, there is also the innodb_read_only option, which has the following effect:

  • No user will be able to modify. Only DCL (like create user/grant/revoke etc) commands will work for the root user.

Note that when both --read-only and --innodb-read-only are given, then --innodb-read-only takes effect.

There is more information on this variable, and how to set up read only InnoDB on the following two pages:

https://blogs.oracle.com/MySQL/entry/innodb_read_only_mode
http://dev.mysql.com/../innodb-read-only-instance.html

Along this same line, and in addition to InnoDB, MariaDB 10.0 also has a spider_read_only_mode option which will make spider tables read only as well.

Hope this helps.

 

MySQL 5.7.4 Overview and Highlights

Tuesday, April 15th, 2014

MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14″ or “Milestone 14″ release), and is available for download here and here.

The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

In Memoriam:

“This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html

As for the fixes, there are quite a few, which is to be expected in such an early milestone release.

The main highlights for me were:

  1. The Performance Schema now instruments prepared statements (for both the binary and text protocols). Info is available in the prepared_statements_instances table, along with performance_schema_max_prepared_statements_instances system variable, and Performance_schema_prepared_statements_lost status variable.
  2. Incompatible Change: MySQL deployments installed using RPM packages now are secure by default (single root account, ‘root’@'localhost’, no anonymous-user accounts, no test database).
  3. Incompatible Change: MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.
  4. Performance; InnoDB: InnoDB now supports multiple page_cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page_cleaner threads.
  5. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument
  6. InnoDB: InnoDB now supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances.

Of course, there were many, many more fixes/updates (InnoDB being #1, Replication #2, and Partitioning #3 with most fixed bugs), so be sure to read through the full changelog. And if you are running a previous version of *5.7*, then definitely plan on upgrading to this latest 5.7.4.

Hope this helps.

 

MySQL 5.6.17 Overview and Highlights

Thursday, April 10th, 2014

MySQL 5.6.17 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:

For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

  1. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
  2. Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES).
  3. InnoDB: MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for the following operations: OPTIMIZE TABLE, ALTER TABLE … FORCE, and ALTER TABLE … ENGINE=INNODB
  4. On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.
  5. mysql_install_db provides a more informative diagnostic message when required Perl modules are missing.
  6. The IGNORE clause for ALTER TABLE is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).
  7. Incompatible Change: Old clients (older than MySQL 5.5.7) failed to parse authentication data correctly if the server was started with the –default-authentication-plugin=sha256_password option.

In addition to those, there were 60 other bug fixes:

  • 21 InnoDB
  • 13 Replication
  • 01 Partitioning
  • 25 Miscellaneous

There were 2 regression bugs fixed, so check if they might have affected you, and if so, then you’ll want to consider upgrading (*especially* note the 1st since it’s InnoDB and performance degradation)

  1. InnoDB: A regression introduced by Bug #14329288 (Oracle-internal) would result in a performance degradation when a compressed table does not fit into memory. (Bug #71436)
  2. Building MySQL from source on Windows using Visual Studio 2008 would fail with an identifier not found error due to a regression introduced by the patch for Bug #16249481 and Bug #18057449 (both Oracle-internal bugs).

Conclusions:

  1. If you use AES_ENCRYPT() and AES_DECRYPT(), I’d definitely investigate the changes, and plan for an upgrade (but test/check first and make and necessary changes first).
  2. Since there were 21 InnoDB bugs fixed, including 1 regression bug, I would recommend upgrading if you’re running a previous 5.6 version.
  3. Similarly, since there were 13 replication bugs, if you have a replication setup, I’d also plan to upgrade to take advantage of the fixes (and not be affected by the bugs).

The full changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Hope this helps. :)

 

MariaDB 10.0.10 Overview and Highlights

Tuesday, April 8th, 2014

MariaDB 10.0.10 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.10/

This is the first GA (“Generally Availability“, aka “recommended for production systems”) release of MariaDB 10.0, and 11th overall release of MariaDB 10.0.

Since this is the initial 10.0 GA release, this is primarily a bug-fix and polishing release.

Here are the main items of note:

  1. The Audit Plugin is now included in MariaDB (MDEV-5584)
  2. Improved XtraDB performance by fixing incorrect calculation of flushed pages (MDEV-5949)
  3. Fix for GTID duplicate key multi-master corruption bug (MDEV-5804)
  4. Default TokuDB compression is now TOKUDB_ZLIB (instead of TOKUDB_UNCOMPRESSED)
  5. Various algorithm improvements for engine-independent table statistics (EITS) (MDEV-5901, MDEV-5917, MDEV-5950, MDEV-5962, MDEV-5926)

You can read more about the 10.0.10 release here:

https://mariadb.com/kb/en/mariadb-10010-release-notes/

And if interested, you can review the full list of changes in 10.0.10 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10010-changelog/

Hope this helps.

 

MySQL 5.5.37 Overview and Highlights

Wednesday, April 2nd, 2014

MySQL 5.5.37 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.37

This release, similar to the last 5.5 release, is mostly uneventful.

There was one new feature added (Solaris-specific + obscure), and only 21 bugs fixed.

The new feature is this:

  • On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.

Out of the 21 bugs, most were benign, but there was one definitely worth mentioning (because it is a regression bug with performance degradation):

  • A regression introduced by Bug #14329288 would result in a performance degradation when a compressed table does not fit into memory. (Bug #18124788, Bug #71436)

The bug that causes this regression, bug #14329288 (Oracle-internal), was introduced in 5.5.30, thus versions 5.5.30 through 5.5.36 are affected by this. There are some more details, including some benchmarks on the performance degradation, here.

For reference, the full 5.5.37 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-37.html

Hope this helps.

 

Improve your Stored Procedure Error Handling with GET DIAGNOSTICS

Wednesday, April 2nd, 2014

In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

You may not want the error just written to the console, or perhaps you want to at least control how it is written.

It’s common to see exit handler code in the following form:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

Where the SELECT outputs something not very useful in many cases.

With GET DIAGNOSTICS, you can get all of the error information, and you should, if not already.

If you were going to use GET DIAGNOSTICS from the command line, you could use something like this (immediately following your query):

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;

However, since we want the information from within the stored procedure, we must put this within the exit handler code. So the above exit handler code, now becomes (and I added 1 more line for formatting):

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;

Which results in:

mysql> call my_proc('abc');
+--------------------------------------------------+
| @full_error                                      |
+--------------------------------------------------+
| ERROR 1146 (42S02): Table 'db1.t1' doesn't exist |
+--------------------------------------------------+
1 row in set (0.01 sec)

For more details on GET DIAGNOSTICS, I would recommend the following 2 pages:

https://mariadb.com/kb/en/get-diagnostics/
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

Hope this helps.

 


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty