MySQL 5.6 Reaches General Availability

First time accepted submitter jsmyth writes "MySQL 5.6.10 has been released, marking the General Availability of version 5.6 for production." Here's more on the features of 5.6. Of possible interest to MySQL users, too, is this look at how MySQL spinoff MariaDB (from Monty, one of the three creators of MySQL) is making inroads into the MySQL market, including (as we've mentioned before) as default database system in some Linux distributions.

mysqldump - storage engine info discarded?!?

[Maow]

Can anyone explain how to get mysqldump to extract & store the storage engine of the tables?

If dumping in XML format, that info is preserved, otherwise it seems to be discarded. If a DB has a combo of MyISAM and InnoDB tables and you're backing up / replicating with mysqldump, that info is... lost. As far as I can tell. Unless I'm doing something wrong.

If one does dump to XML, what's the best way to load that into a new slave or do a restore from it?

Using 5.5.28 here...

Seriously has me considering ma

Re:

[Maow]

Seriously, read the manpage or search the internet. Slashdot is not your command line argument support forum.

Done and done.

Been considering a bug report but WTF, why not ask since this thread is about MySQL which happens to have new replication features that I'm reading about in another tab right now.

You think Slashdot is going to get filled up or something?

Idiot.

Re:mysqldump - storage engine info discarded?!?

[stdarg]

They want to deliver vast amounts of information over Slashdot. And again, Slashdot is not something that you just dump something on. It's not a big truck. It's a series of tubes. And if you don't understand, those tubes can be filled and if they are filled, when you put your message in, it gets in line and it's going to be delayed by anyone that puts into that tube enormous amounts of material, enormous amounts of material.

Re:

[K. S. Kyosuke]

You have to be especially careful not to send yourself a Slashdot, or, gods forbid, multiple Slashdots, or you'll suffer the so-called Slashdot-in-my-inbox effect.

Re:

[Maow]

Seriously, read the manpage or search the internet. Slashdot is not your command line argument support forum.

Further more, putting my question out there might just notify some sysadmins that their mysqldumped data might not be quite what they expected if they rely on a mix of storage engines' features for their various tables...

Idiot.

Re:mysqldump - storage engine info discarded?!?

[vlm]

That won't work unless he knows what to search for. I'm not running the latest mysql. Maybe they neutered mysqldump, if so that would probably be dumb.
On the other hand if you're seeing stuff like "ENGINE=innoDB" in your dump but upon restore they're importing as myisam or whatever, you're being bit by an "issue" or "bug" or whatever where innodb isn't starting for whatever reason so mysql helpfully starts up without it and tries its hardest and creates the table using myisam seeing as innodb is dead. Look for "sql_mode=NO_ENGINE_SUBSTITUTION" to disable the "best effort" and look in the logs for why innodb won't start on the new server (who knows why). Its typical of the whole mysql philosophy that it'll try best effort at all times, even if that drives people of a certain outlook bonkers. I don't think you can google for "mysql philosphy" and get this potentially useful or potentially inaccurate opinion.

On the other hand if they neutered mysql to not store engine type that would just be moronic. It won't affect me when/if I upgrade to 5.6 because I store my schemas as part of the program sourcecode (not in the sourcecode, next to it, like running mysql somedb something.sql will create the table "something" requires if its not already there. In a way this actually would save effort when converting from one DB engine to another.

The existence of one anecdote that once happened to me years ago which I might not even be correctly remembering does not imply no other cause could exist. But its a start and better than the reply of RTFM noob.

Re:

[Maow]

That won't work unless he knows what to search for. I'm not running the latest mysql. Maybe they neutered mysqldump, if so that would probably be dumb.

Interesting: did mysqldump ever support the storage engine specified? I dumped & loaded some DBs on the replication server and ... all InnoDB. Huh, some of these ought to have been MyISAM. Looked into it further, and it appears dependant on the default-storage-engine= in my.cnf.

However, specifying --xml to mysqldump forces it to specify the storage engine. I haven't yet looked into how to load the XML file(s) into MySQL (for that I'll read man pages / search internet).

I keep thinking that I'm missing

Re:

[Maow]

Try --create-options:
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_create-options [mysql.com]

mysqldump by default aims to dump valid SQL for any DB (which is why the MySQL-specific options are within special comments). The ENGINE= attribute of the CREATE TABLE query is MySQL-specific so left off by default, --create-options should include it.

Thanks - that's a good explanation.

However, I just tested it and it didn't work.

A-ha! I was using --compatible=ansi and that was the issue.

Thanks again for pointing me in the right direction.

"Duh"

CREATE TABLE `test` (
`kkeeyy` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(10) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL,
PRIMARY KEY (`kkeeyy`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Re:

[RevDisk]

Hey, you helped me out. Jotting down some notes.

Re:

[Zontar The Mindless]

Here's a sample created by mysqldump:

CREATE TABLE "test" (

"kkeeyy" int(11) NOT NULL AUTO_INCREMENT,

"a" varchar(10) DEFAULT NULL,

"b" varchar(10) DEFAULT NULL,

PRIMARY KEY ("kkeeyy")
);

Note that no storage engine is specified.

Here's how you see the default storage engine:

mysql> SELECT @@default_storage_engine\G
* 1. row *
@@default_storage_engine: InnoDB
1 row in set (0.00 sec)

And here's how you see which storage engine is actually being used by a given table:

mysql> CREATE TABLE test (
-> kkeeyy int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> a varchar(10) DEFAULT NULL,
-> b varchar(10) DEFAULT NULL

Re:

[StuartHankins]

I checked just now and the storage engine is definitely in my mysqldump script output. I am using an older version, 5.1.66.

mysqldump --all-databases --no-data=true --routines --lock-tables=0

Output includes a line per table that includes "ENGINE=InnoDB" or whatever storage engine I'm using.

Re:

[ByteSlicer]

If dumping in XML format, that info is preserved, otherwise it seems to be discarded. If a DB has a combo of MyISAM and InnoDB tables and you're backing up / replicating with mysqldump, that info is... lost. As far as I can tell. Unless I'm doing something wrong.

Dunno, I never had any problems with this (5.5.x). Doing a standard mysqldump in SQL format adds the storage engine to the end of each table create statement.

Re:

[Zontar The Mindless]

If one does dump to XML, what's the best way to load that into a new slave or do a restore from it?

Using 5.5.28 here...

LOAD XML INFILE [mysql.com] might be what you're looking for.

Re:

[F.Ultra]

I run 5.5.29 (the version in Ubuntu 12.04 LTS) and "mysqldump -p db table" dumps the storage engine of all my tables just fine.

Transactions per second are impressive

[schneidafunk]

If you check out the charts, the transactions per second are tripled from 5.5, when the threads approach 60.

Re:

[fatp]

More impressive is that when the threads approach 600, it will drop below 5.5.

Who cares?

[h8sg8s]

MySQL is rapidly approaching "who cares?" status. Oracle kills another one.

Re:

[blind biker]

MySQL is rapidly approaching "who cares?" status. Oracle kills another one.

This is the highest-modded post in the thread? A wholly devoid-of-information nihilistic scoff? MySQL 5.6.10 brings a ton of nice features, like online ALTER TABLE. [oracle.com] Wouldn't it be nice if there were some actual informative posts on the new MySQL release?

By the way, even as a scoff, your post is pretty weak.

Re:

[Kozz]

I was pretty sure what I'd find in the comments here, and I wasn't disappointed.

Haters gonna hate....

Re:

[Jane Q. Public]

"Haters gonna hate...."

There is very GOOD reason to distrust Oracle in this case.

Oracle actually has a history of acquiring open source projects and then killing them off. Maybe intentionally, maybe not. But a history nonetheless. It is no wonder people are concerned.

But they need not be. MariaDB is feature-compatible with MySQL, up to and including using the same commands to operate it, right down to the "mysql" and "mysqld" commands.

AND it remains open source. Plus, there have been a number of performance enhancements

Re:

[postbigbang]

>> "Insightful"?

No.

Crickets.

There, fixed it for you.

MariaDB

[eksith]

Ironically is the direction MySQL should have gone after the 4.x branch. There's a whole heap of legacy baggage in the code base and Oracle -- since we know how good they're with legacy baggage -- decided to keep doing incremental changes to it (ever try putting CURRENT_TIMESTAMP as default on two fields with the second being ON UPDATE?)

The 5.6 line is actually using a lot of improvements handed back by companies like Google, which I think initally used it for AdWords and may still be using in some capacity.

Re:

[robmv]

Google App Engine SQL feature is based on MySQL, I was hoping it to be based on PostgreSQL :(

Re:

[eksith]

Man, that would have been great. The only reason I switched to Postgres a while back was because of a licensing conflict. Happy accident it also happened to be a very consistent and stable DB.

Re:

[HRbnjR]

Agree!! I tried porting my PostgeSQL app to GAE and failed miserably, cuz MySQL sucks: https://groups.google.com/forum/#!msg/google-cloud-sql-discuss/rKtuhdBIdvI/ZLPL5LG4z1YJ [google.com]

Re:

[wonkey_monkey]

Ironically is the direction MySQL should have gone after the 4.x branch.

What does an ironic database do that the others don't?

Re:

[eksith]

The child tables ignore parent tables on every UPDATE and return rowsAffected 0... A few years later, child tables return Foreign Key Error 1005 errno 150 on INSERT, but it's too late to because the parent tables are no more, but those ear plugs and tatoos are there forever.

Hosting Providers

[stoolpigeon]

What matters more is what db cheap hosting providers have. If they all start running MariaDB we'll see a big shift. As a distro default I'm not sure if it matters so much. I guess the other side of that coin would be WordPress, Drupal, Joomla!, and so on.

Re:

[wmac1]

They will possibly not risk. There are tens of customers with hundreds of websites and possibly thousands of applications. They will possibly be worried that changing to something else might break customer software and create a support nightmare for them.

Their policy would be: "Why change something that works!"

Re:

[JImbob0i0]

Yeah it'd be crazy for major sites with millions of visitors every day to use something as untested as MariaDB [zdnet.com].

Who cares

[Kardos]

I think you'll find that we're all moving to MariaDB these days. Thanks Oracle, everything you touch turns to shit.

Re:

[drinkypoo]

There doesn't seem to be much incentive for people to move to a less mature fork.

That's why this new release from Oracle is a non-starter.

MariaDB

[greg1104]

Let me see if I have this right. Monty builds up MySQL AB into a functional project that a lot of people depend on. Then he sells it, cashes out big, and abandons it. And now people are falling for this again? Fool me twice, shame on me.

Re:

[Anonymous Coward]

Then he sells it, cashes out big, and abandons it.

To be fair: Monty wasn't the one who sold MySQL AB. When MySQL Ab was sold, original founders didn't have a deciding vote anymore. MySQL grew because there were investments in its development.

And now people are falling for this again? Fool me twice, shame on me.

What exactly have you "felt for"? You have a product (MySQL, and then MariaDB) available for you under GPL licence, at no cost. Support is available, in recent years from multiple vendors (Percona

Re:

[gmuslera]

You have problem with the person or with the platform? No matter if Monty left MySQL AB or will abandon MariaDB in a future, both codes are GPL and you or someone else could fork or maintain it. In fact what MariaDB is doing with MySql is a good example of what can be done if Oracle becomes too greedy.

Re:

[JImbob0i0]

Not quite right...

The timeline is more like MySQL AB sells out to Sun Microsystems who generally were not bad as stewards for open source products under their name.

Later on Sun hits major financial trouble and Oracle gobbles them up.

Following some appalling stewardship (hudson, openoffice, opensolaris) MariaDB took the GPL MySQL code and started work on it.

The community moves to MariaDB for the large part and MySQL is sidelined ;)

A Match From Heaven For

[Anonymous Coward]

..PHP. Two semi-pro tools for those Who Do Not Know Better.

Meanwhile, professionals use Perl, Postgresql, Python and the like.

Re:

[hazah]

Sounds like you haven't an actual clue as to what professionals use.