
An open MySQL bug receives a real Birthday cake - ted_turner
http://bugs.mysql.com/bug.php?id=20786
======
famousactress
I think gittip is great, and I understand this is the opposite of their
philosophy, but I want a well-used bounty program for these things.

I mentioned some Django bugs that are huge for us that were finally fixed in
1.6 in another thread [1]. I'd have personally pledged a couple hundred
dollars to have them fixed, and probably could have gotten considerably more
pledged from my company. Does this tool/platform exist and I just don't know
about it?

[1]
[https://news.ycombinator.com/item?id=5958281](https://news.ycombinator.com/item?id=5958281)

[Edit]

Thanks for the replies. Pledgie and bountysource look interesting and I
suppose really close to what I was imagining, in spirit. Fundamentally, I'd
like to see something well-executed help formalize the process behind what
motivated Andrew's awesome Django migrations kickstarter [2]. I really like
the way that came together because it had the buy-in of the team, and there-by
inspired confidence in potential backers.

[2] [http://www.kickstarter.com/projects/andrewgodwin/schema-
migr...](http://www.kickstarter.com/projects/andrewgodwin/schema-migrations-
for-django)

~~~
foobarbazqux
I'm not convinced Oracle (ORCL), listed on both the S&P-500 and the
NASDAQ-100, needs or will accept bug fixing donations, especially since MySQL
is a competitor to their flagship Oracle DB and there was quite a lot of
disgruntlement around the acquisition. Maybe if you gave money to MariaDB it
would work.

~~~
dragonwriter
> MySQL is a competitor to their flagship Oracle DB

Does it really? I mean, sure, they are both RDBMS's, but that's a pretty broad
market. Are there really that many use cases where, with the current market,
people who are using MySQL would have Oracle as their next choice or vice
versa?

I can see an argument that MySQL competes with some of the same products that
Oracle DB competes with (but in different market segments than where Oracle DB
does), but I don't see them as directly competitive in any significant sense.

~~~
xroja
My project is in that position. We have access to an Oracle site license so we
use it. However, we're considering move to a "competing" db, probably
postgresql or mysql.

I'm not a certified DBA so take this with a grain of salt, but it seems that
any non-trivial use of Oracle systems ramp up in cost very quickly. Both in
terms of hard cash and time in education/training.

~~~
dragonwriter
> We have access to an Oracle site license so we use it. However, we're
> considering move to a "competing" db, probably postgresql or mysql.

Good point; I was considering mostly the situation where there isn't something
already on hand, but it makes sense that there would be quite a lot of times
where MySQL and Oracle DB are considered alternatives for a new
project/application by shops who already have an existing installation of one
or the other.

------
aspensmonster
The video bringing cake and ice cream to this bug's 7th birthday:

[http://www.youtube.com/watch?v=oAiVsbXVP6k](http://www.youtube.com/watch?v=oAiVsbXVP6k)

It's got a real Joker laugh going on toward the end.

~~~
hkmurakami
so I see 301+ views on the video and had to go get the obligatory xkcd
[http://www.xkcd.com/1224/](http://www.xkcd.com/1224/)

Also, I wonder why he chose to record it in his bathroom. Those are definitely
shower curtains and the echo is definitely bathroom-esque. Maybe for
acoustics.

------
yxhuvud
I cried a little on the inside when I saw the proposed C regexp 'solution'.

------
gkop
The conventional workaround:

    
    
      $ mysqldump -d my_database | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > dump.sql

~~~
shdon
That's also listed in the comments for the bug, as well as some (contrived)
examples as to when it wouldn't work. Surprising it hasn't been fixed,
considering how simple the true fix is.

------
fletchowns
Using compressed air to blow out the candles was a nice touch.

~~~
kryten
Until you get a can of cheap imported "air duster" air which contains propane
propellant still and instantly results in a firey ball of death...

Only posting this because I've done that before :)

~~~
mindslight
That's still nicer than the thermal decomposition products of fluoroethanes.

~~~
GICodeWarrior
I was curious about your comment so I did a few searches, and it looks like a
major decomposition product is hydrogen fluoride.

According to Wikipedia: "Hydrogen fluoride is a highly dangerous gas, forming
corrosive and penetrating hydrofluoric acid upon contact with tissue. The gas
can also cause blindness by rapid destruction of the corneas."

------
morgo
I have a rudimentary knowledge of MySQL Internals, so I am going to take a
guess why it has taken so long.

They want to implement this feature server side, which means that they either
have to add: (1) A new server setting (SET SHOW_CREATE_TABLE_FORMAT = Blah)
-or- (2) New syntax (SHOW CREATE TABLE NOAUTOINC mytable).

Adding new settings is always evil, and adds to product confusion/complexity.

Adding new syntax is a bit evil in MySQL's case since they use a generic yacc
parser, and might have to add a new reserved word (truthfully, I'm not 100%
sure on that one). There is also a preference to only add reserved words to
major new versions.

------
erikkay
Of all of the things I've done in technology, this was not near the top of the
list of things that I thought would be on the top of hacker news (no, I didn't
do the cake, just filed the bug 7 years ago).

------
nodesocket
Today is my actual birthday, me and bug #20786 should share that delicious
cake and ice cream.

~~~
croisillon
And, cross-commenting from the Tau Manifesto post, "tau" means "for you" in
lithuanian, so go ahead and have a delicious slice of HN homepage for your
birthday.

------
binarymax
For those unclear of the reasons this is bad (aside from the assumption that
the pure raw data structure should not have any artifacts of custom data
definition) - it is probably something in code or, more likely, initialisation
data, that is expecing the Id to be something. This may be a file of SQL
inserts that is run after the database is created, with hardcoded primary
keys. This is an antipattern of course, but it doesn't excuse the fact that
pure structure dumps should remain as such. (disclaimer - I have little
experience with mysql, but have seen these same antipatterns in SQL server,
oracle, and db2 data init scripts far too often).

------
josh2600
Personally, it's the guy's laughing that does it for me.

What does this bug cause?

~~~
dangrossman
`mysqldump` is a utility included with MySQL to dump a copy of tables and
their data to a text file of SQL queries. Tables may contain a column with the
AUTO_INCREMENT property where values in that column are generated from a
sequence (i.e. 1, 2, 3, 4, 5).

The bug requests that the table definition written to the dump file by this
utility not contain the current value of the AUTO_INCREMENT sequence when the
option to dump only definitions, not data, is used.

------
dmourati
If any MySQL bug needs a cake it's this whopper I just ran into:
[http://bugs.mysql.com/bug.php?id=1341](http://bugs.mysql.com/bug.php?id=1341)

~~~
jeremycole
That's pretty silly, as any serious installation is generally using
innodb_file_per_table, and thus not really subject to this problem.

Nonetheless, it's a lot more complicated than it would seem to "just reclaim
the space".

~~~
samcrawford
I disagree that any serious installation is using innodb_file_per_table. It
does have downsides.

In our use case we're making very heavy usage of table partitioning (over 500
partitions for some of our very large tables). Because each partition is
effectively another table under the covers, if we were to use
innodb_file_per_table then we'd have tens of thousands of files. This means a
lot more file handles being opened/closed (as well as the obvious ulimit
gotchas for the uninitiated). This noticeably hampered query performance when
seeking data from random partitions.

------
druiid
Sadly MySQL has a history of bugs like this. There are a few we have run
across that have probably been open longer than this bug, and yet have not
been fixed. I'd have to look the bug numbers up from e-mail, but yeah.. there
are tons of mines out there in MySQL just waiting for you to hit them, and
upon further discovery, someone else found out about it years ago!

~~~
kryten
I've had many experiences like this as well. Even some of our paid for
software gets scared if you use a buggy MySQL version (TeamCity for example
refuses to start on certain MySQL versions).

Switched to postgresql now - just works!

~~~
raverbashing
But until you get to the point that "just works" it's a huge pain in the
behind (configuring, etc)

Also, the SQL syntax is usually a pain to get it first (MySQL is much more
tolerant for a newcomer)

And yes, PSQL commands usually work in MySQL (except for the specific stuff,
like \dt but that's not even SQL so...)

~~~
kryten
Seriously?

I find it much easier from an admin and dev perspective. It is less forgiving,
that is correct, but that's a good thing. Oh and the documentation is
wonderful compared to MySQL.

Plus it works properly.

------
lcampbell
Well, I just took 10 minutes to write an ad-hoc parser that will probably fix
the bug (it works around the tricky test cases in the thread)[1]. I don't
really understand why they want to move stuff onto the server-side, there may
be some other trickery I'm missing.

Please feel free to use as-is and integrate it into mysqldump. I can't be
bothered to create an account on bugs.mysql.com.

\--

[1] [http://pastie.org/8092330](http://pastie.org/8092330) (EDIT: The parser
wasn't fully correct, use [2])

[2] [http://pastie.org/8092403](http://pastie.org/8092403)

------
AaronBBrown
While I agree it has potential to be annoying in some environments, it's also
trivially simple to work around.

    
    
        for tbl in $(mysql -BNe "SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE EXTRA = 'auto_increment' AND TABLE_SCHEMA = 'mydb'"); do
          mysql -e "ALTER TABLE ${tbl} AUTO_INCREMENT=1" mydb
        done
    

Run that just after importing your new schema.

Note: That code will require a bit of modification if you make the mistake of
having spaces in your table name.

------
ars
I store the dump of the database in source control, so several years ago I
made a small program mysqldump_ddl to strip out all ephemeral data that
clutters up the changes:

    
    
        #!/bin/sh
        mysqldump --no-data $* | sed 's/ AUTO_INCREMENT=[0-9]*//' | egrep -v '^-- (MySQL dump|Server version|Dump completed on)'
    

Now the database diff only has actual changes. (Mostly - sometimes new
versions of mysql change other things.)

------
bcoates
I don't believe in autoincrement so I'm not up to date on the evil things you
can do with it, so I'm unclear why this bug matters: All it does is change the
arbitrarily chosen start value for new rows, what could that break?

The existing behavior makes more sense anyway, as it preserves 'truncate
table' <=> 'restore nodata dump' equivalence better.

~~~
miahi
That is also a way to solve it: make it a documented change. But it looks like
it was completely ignored. So happy birthday!

------
pud
Here is the cake: [http://youtu.be/oAiVsbXVP6k](http://youtu.be/oAiVsbXVP6k)

------
_mpu
The speed at which they get regexps out is kind of frightening, I find...

------
pagekicker
I just had occasion to use mysqldump --no-data a few days ago, and then came
across this today. Not a good feeling! C'mon, mySQL devs ... fix it already.

------
aylons
Is this bug still present in the MariaDB fork?

------
bashinator
Yep, I've been hit by that bug numerous times over the years. Sed works fine
for editing the dumps. Happy Birthday!

------
DamnYuppie
Damnit, now I want cake and ice cream!

