Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL statement generates one. This means you can’t include any DDL statement inside a stored …

Over the last months I’ve seen lots of customers trying to tune the thread concurrency inside MySQL with the variable thread_concurrency. Our advice is: stop wasting your time, it does nothing on GNU/Linux

Some of the biggest GNU/Linux distributions includes the variable thread_concurrency in their my.cnf file by default. One example is Debian and its variants. Furthermore the default my.cnf files of MySQL like my-large.cnf, my-innodb-heavy-4G.cnf and so on have the thread_concurrency enabled with a tune advice helping to spread the confusion:

# Try number of CPU's*2 for thread_concurrency

Don’t try it. The problems with thread_concurrency are two:

1- It’s deprecated and removed on 5.6.1

http://bugs.mysql.com/bug.php?id=55001

2- It only works on old Solaris versions …

This week we sit down with Rohit Nadhani from Webyog, and talk about SQLyog as a GUI for MySQL, and why veteran commandline users would benefit from using SQLyog.

News/Events/Feedback
Conferences:
The 1st Latin American Conference about MySQL, NoSQL and Cloud technologies, will be held in Buenos Aires from Wednesday, June 27th through Friday, June 29th at the Puerto Madero Hilton Hotel. There will be live simultaneous translations for English, Portuguese and Spanish.

read more

I decided to try the claim that MySQL Cluster is a great Key-Value store. I have been thinking about trying this for some time now, my JSON import tool for MySQL was a starting point for being able to cram some JSON data from our MongoDB Key-Value store into MySQL.

I tried this tool with an InnoDB table, and that worked great, importing 100.000.000+ records at about 10k records / s, all CPUs going at full speed. As JSON is native to MongoDB, I assumed it would be faster and consume less resources there, and that was true, 22k records / s were imported into MongoDB, but I still think my tool worked well.

Now, having done all that, and having all the necessary tools ready, it was to to set up MySQL Cluster on this box. I have 8 cords and 16 Gb RAM on it, so a 20 Gb table should be too bad, at least not …

Do you want to closely follow what MySQL is doing around the globe? We've created a Lanyrd guide just for that. It's curated by the MySQL Community, Marketing, Presales and Product Management teams and with some other contributions too.
Tomorrow we'll host the MySQL Innovation Day (in streaming too) and MySQL Connect is also quickly approaching. If you'd like showcase your products and services to the MySQL Community you can take advantage of the various sponsorship opportunities.

Stay tuned for the latest and greatest updates from the MySQL team at Oracle and from our vibrant community!
…

Do not forget to register for the free Webcast of MySQL Innovation Day.

Abstracts

Better Availability with InnoDB Online Operations
Calvin Sun, Sr. Development Manager for InnoDB, Oracle Many top Web properties rely on MySQL as a critical piece of infrastructure to serve millions of users. Therefore, database availability has become increasingly important. One way to enhance availability is to allow users full access to the database during DDL operations. The online DDL operations in recent MySQL releases offer users the flexibility to perform schema changes while allowing users full access to the database, that is, with minimal delay for operations on the table, and without rebuilding the entire table. Those enhancements provide better responsiveness and availability …

The signal-to-noise ratio in the NoSQL world has made it hard to figure out what’s going on, or even who has something new. For all the talk of performance in the NoSQL world, much of the most exciting part of what’s new is really not about performance at all.

Take for example, MongoDB, which has a really great data model and MapReduce has a very handy scripting language. These are genuine and probably long-lasting contributions. Their innovation is all about finding a new language to use for interacting with data. They are about NoSQL.

The confusion comes, for me, when we get to the performance side of the equation. I explore this in detail in an article I did for Datanami recently – http://www.datanami.com/datanami/2012-05-22/the_sound_and_the_nosql_fury.html.

What if MySQL had an HTTP interface and would reply JSON? What if MySQL had server-side JavaScript? What if you could store JSON documents in MySQL? And, what if there was Map and Reduce built-in to MySQL? Make it happen, today. A proof of concept MySQL 5.6 daemon plugin presented at IPC Spring 2012.

HTTP, JSON, JavaScript, Map&Reduce built-in to MySQL
View more presentations from Ulf Wendel

Eye-opening community contributions

Mycached (2009, Cybozu) was a ground breaking achievement. A …

As described in the first article of this series, Tungsten Replicator can replicate data from MySQL to Vertica in real-time.  We use a new batch loading feature that applies transactions to data warehouses in very large blocks using COPY or LOAD DATA INFILE commands.  This second and concluding article walks through the details of setting up and testing MySQL to Vertica replication.

To keep the article reasonably short, I assume that readers are conversant with MySQL, Tungsten, and Vertica.  Basic replication setup is not hard if you follow all the steps described here, but of course there are variations in every setup.  For more information on Tungsten check out the Tungsten Replicator project at code.google.com site well as …

Real-time analytics allow companies to react rapidly to changing business conditions.   Online ad services process click-through data to maximize ad impressions.  Retailers analyze sales patterns to identify micro-trends and move inventory to meet them.  The common theme is speed: moving lots of information without delay from operational systems to fast data warehouses that can feed reports back to users as quickly as possible.

Real-time data publishing is a classic example of a big data replication problem.  In this two-part article I will describe recent work on Tungsten Replicator to move data out of MySQL into Vertica at high speed with minimal load on DBMS servers.  This feature …