×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

MySQL 5.6 Reaches General Availability

timothy posted about a year ago | from the magic-8-ball-says dept.

Databases 47

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.

cancel ×
This is a preview of your comment

No Comment Title Entered

Anonymous Coward 1 minute ago

No Comment Entered

47 comments

downloading now ... (-1)

Anonymous Coward | about a year ago | (#42796991)

cve's incoming

mysqldump - storage engine info discarded?!? (1)

Maow (620678) | about a year ago | (#42797045)

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 making the switch to PostgreSQL, although I'm not sure that it's better in that regard. It does now do asynchronous or synchronous replication at a transactional level, which looks interesting.

Re:mysqldump - storage engine info discarded?!? (-1, Offtopic)

TheNinjaroach (878876) | about a year ago | (#42797169)

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

Re:mysqldump - storage engine info discarded?!? (0)

Maow (620678) | about a year ago | (#42797317)

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?!? (4, Funny)

stdarg (456557) | about a year ago | (#42797593)

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:mysqldump - storage engine info discarded?!? (1)

K. S. Kyosuke (729550) | about a year ago | (#42800553)

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:mysqldump - storage engine info discarded?!? (2)

Maow (620678) | about a year ago | (#42797333)

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?!? (4, Informative)

vlm (69642) | about a year ago | (#42797357)

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:mysqldump - storage engine info discarded?!? (2)

Maow (620678) | about a year ago | (#42797821)

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 something obvious but ... not finding it. Almost convinced it's a bug / oversight.

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.

Restored / loaded tables have the default-storage-engine in effect.

And the earlier poster - did he think concern over the storage engine in a mixed environment, or replication, are noob topics?

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.

Re:mysqldump - storage engine info discarded?!? (0)

Anonymous Coward | about a year ago | (#42798045)

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

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.

Re:mysqldump - storage engine info discarded?!? (1)

Maow (620678) | about a year ago | (#42798213)

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:mysqldump - storage engine info discarded?!? (1)

Zontar The Mindless (9002) | about a year ago | (#42800609)

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
        -> );
Query OK, 0 rows affected (0.44 sec)

mysql> SHOW CREATE TABLE test\G
* 1. row *
              Table: test
Create Table: 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 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, ENGINE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = 'test'\G
* 1. row *
TABLE_NAME: test
TABLE_SCHEMA: test
ENGINE: InnoDB
1 row in set (0.00 sec)

Note that if you run ALTER TABLE test ENGINE=MYISAM on this table, the output from these statements will reflect the change.

Re:mysqldump - storage engine info discarded?!? (1)

StuartHankins (1020819) | about a year ago | (#42798969)

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:mysqldump - storage engine info discarded?!? (1)

ByteSlicer (735276) | about a year ago | (#42799593)

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:mysqldump - storage engine info discarded?!? (1)

F.Ultra (1673484) | about a year ago | (#42801939)

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 (3, Interesting)

schneidafunk (795759) | about a year ago | (#42797073)

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

Re:Transactions per second are impressive (2)

fatp (1171151) | about a year ago | (#42800953)

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

Who cares? (3, Insightful)

h8sg8s (559966) | about a year ago | (#42797123)

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

Re:Who cares? (0)

blind biker (1066130) | about a year ago | (#42797523)

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:Who cares? (1)

Kozz (7764) | about a year ago | (#42797571)

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

Haters gonna hate....

Re:Who cares? (1)

Jane Q. Public (1010737) | about a year ago | (#42805919)

"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 in the MariaDB fork.

As OP stated: MariaDB has become the default database for some open operating systems. I know people who have used it as a simple plug-and-play replacement for MySQL.

Re:Who cares? (0)

Anonymous Coward | about a year ago | (#42798885)

Who cares?

How about anyone that uses MythTV! You've heard of that, right?

MariaDB (4, Interesting)

eksith (2776419) | about a year ago | (#42797173)

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:MariaDB (1)

robmv (855035) | about a year ago | (#42797265)

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

Re:MariaDB (2)

eksith (2776419) | about a year ago | (#42797525)

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:MariaDB (1)

wonkey_monkey (2592601) | about a year ago | (#42797825)

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:MariaDB (1)

eksith (2776419) | about a year ago | (#42798201)

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 (1)

stoolpigeon (454276) | about a year ago | (#42797299)

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:Hosting Providers (1)

wmac1 (2478314) | about a year ago | (#42797567)

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!"

Who cares (2)

Kardos (1348077) | about a year ago | (#42797389)

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

Re:Who cares (-1)

Anonymous Coward | about a year ago | (#42800125)

I don't think you'll find that at all, excepting some very constrained values of 'we'.

There doesn't seem to be much incentive for people to move to a less mature fork. There are few large MariaDB installations, and the deployments-in-progress I've heard news of are Mozilla and Wikimedia, who probably are doing it more for the openness than for technical reasons.

Re:Who cares (0)

drinkypoo (153816) | about a year ago | (#42803841)

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 (1, Interesting)

greg1104 (461138) | about a year ago | (#42798091)

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:MariaDB (1)

Anonymous Coward | about a year ago | (#42798553)

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, SkySQL, etc). Is there anything that you were promised and then cheated on?

Re:MariaDB (0)

Anonymous Coward | about a year ago | (#42801071)

What exactly is the problem? The code is all GPL and there are plenty of other developers. Did you expect him never to move on or are you a freetard that's upset that he made money?

Re:MariaDB (1)

gmuslera (3436) | about a year ago | (#42802275)

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:MariaDB (1)

JImbob0i0 (1202835) | about a year ago | (#42809017)

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 (1)

Anonymous Coward | about a year ago | (#42798867)

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

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

Great! (0)

Anonymous Coward | about a year ago | (#42799745)

They removed the "Server Instance Configuration Wizard" utility that used to come with the ~35MB installer for Windows, so I had to download a ~170MB installer... which failed to configure it every single time!

Check for New Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Sign up for Slashdot Newsletters
Create a Slashdot Account

Loading...