Fri, 2014-10-24 09:21maxmether

This is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog.

Dynamic Columns within WHERE

I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause:

SELECT id, name AS 'White Items'
FROM items
WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white';

+----+----------------------+
| id | White Items          |
+----+----------------------+
|  3 | Samsung Galaxy …

Fri, 2014-10-24 09:21maxmether

This is a continuation of my previous blog, where we will focus on some more advanced features related to Dynamic Columns. For an introduction to Dynamic Columns please refer to my previous blog.

Dynamic Columns within WHERE

I started todays example by adding a few more items into my table, namely my two laptops to be able to produce more meaningful results. As with any function, dynamic column functions can generally be used in the WHERE clause of SELECT and other SQL statements which manipulate data. Let's execute another SELECT statement with COLUMN_GET() in the WHERE clause:

SELECT id, name AS 'White Items'
FROM items
WHERE COLUMN_GET(attributes, 'colour' AS CHAR) = 'white';

+----+----------------------+
| id | White Items          |
+----+----------------------+
|  3 | Samsung Galaxy …

Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does Solr automatically maintain its index against MySQL? Do you have to hit the Solr server with a specific query to keep the index ‘warm’?

There are several strategies for updating a Solr …

As a part of improving the error handling, in MySQL 5.7.5 we have re-implemented STRICT sql mode.

STRICT mode in MySQL affects the errors that arise from invalid, missing, or out of range values in DML statements such as INSERT, UPDATE, and DELETE. The new implementation aims to make the behavior of STRICT mode more consistent, yet maintain backward compatibility as much as possible.

In MySQL 5.7.5, we have made three general improvements to STRICT mode behavior:

 1. STRICT mode got simpler

It was observed that having a large number of sql modes dependent on STRICT mode creates confusion among users. Specifically, we are talking about NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes. You can se further details on the above sql modes here. These modes only …

With a last minute change to the host_summary_by_stages view for the 1.2.0 release of the sys schema, I managed to break the views without noticing it.

So, in the shortest release cycle for the sys schema ever (less than 24 hours), 1.3.0 has now been released.

This comes with a bonus though, there was also a new view that was contributed by Jesper Wisborg Krogh waiting to be merged as well, so I’ve added that to the 1.3.0 version (hence the 1.3.0 instead of 1.2.1).

This is the innodb_lock_waits view, which shows all sessions that are waiting for a lock within InnoDB, as well as the details of who is blocking them, here’s an example output:

mysql> SELECT * FROM innodb_lock_waits\G
*************************** 1. row ***************************
     waiting_trx_id: 805505 …

Introduction

Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.

1. InnoDB::ha_prepare_inplace_alter_table(..)
2. InnoDB::ha_inplace_alter_table(..)
3. InnoDB::ha_commit_inplace_alter_table(..)
4. mysql_rename_table(..)

InnoDB storage engine allocates temporal memory buffer for transaction logging in phase 1 where row changes during this phase are logged. Size of this buffer is at start sort_buffer_size and it can be grown up to innodb_online_alter_log_max size. During phase 2 thread processing the ALTER statement will copy old table’s rows to a new altered table. After this MariaDB will take exclusive lock for target table and applies row log buffer to the new altered table.

This introduces a new unpredictable failure case row log buffer overflow. MariaDB server will rollback ALTER statement if row log buffer …

In 5.7.5, as a part of the larger effort to improve error handling, we re-implemented the IGNORE clause (WL#6614). The IGNORE clause is a MySQL extension to the SQL standard. It affects the errors which occur for each row. The new implementation aims to make the behavior of the IGNORE clause more consistent.

Statements which support the IGNORE clause are:

• INSERT [ IGNORE ]
• UPDATE [ IGNORE ]
• DELETE [ IGNORE ]
• LOAD DATA [ IGNORE ]
• LOAD XML [ IGNORE ]
• CREATE TABLE… [ IGNORE ] SELECT

When the INSERT statement is used to insert a number of rows into a table, an exception during processing would normally abort the statement and return an error message. With the IGNORE keyword, rows that cause certain exceptions are ignored, but the remaining rows are inserted and the …

I will be talking at Open Source India scheduled at NIMHANS convention center at Bangalore on 7th and 8th of Nov 2014.

Oracle has been associated with the Open Source India conference for the past 4 years and this will be the 5th year when we will be taking part in this conference and talking about MySQL to the open source community.

OSI gives us a great opportunity and a platform to speak to and hear from the open source community. There have been some great interactions with developers, users, customers of MySQL and some really great people involved with the open source movement in India.

We look forward to interact with the open source community again. While we will likely learn much this conference about the progress of the open source community in India, we will also be informing you about the fantastic progress Oracle has made with MySQL in the past year. …

(Note : This an Article from last year when MySQL5.6 was released)

While Database technology is one of the oldest branches of computer science, it remains a fundamental computer technology that continues to attract new research. The current focus of Databases technology is towards adapting hot new tends like multi-core chips, solid state devices, NOSQL and Cloud. So what does a contemporary internet developer look for in a database for the internet era? And why does MySQL remain the most popular database for the web?

For a database to be useful while developing products for the Web, the most important requirements are that it should be quick and easy to download, quick to set up, powerful enough to get the job done, be fast and flexible to use and finally be scalable on the newest hardware. Compatibility with the latest technologies like the cloud also remains foremost in the minds of …

Since this is my first blog I feel it's necessary to introduce myself. I'm Tim Callaghan, I work at Tokutek (makers of TokuDB and TokuMX), and I love benchmarking. While some of the content on this blog will certainly be about Tokutek technologies, I plan on exploring a wide variety of others as well. These are strictly my own personal views and opinions, and comments/feedback are always welcome. Lets get started...

A few weeks ago I noticed an EnterpriseDB NoSQL Benchmark that measured Data Load, Insert, Select, and Size. It wasn't just a NoSQL benchmark, it was specifically calling out …