MySQL (InnoDB) setzt einen globalen lock auf alle Datenbanken
wenn man ein “DROP DATABASE testdb” Statement ausführt. Bei 100- 200ms pro Tabelle
kann es bei größeren Datenbanken zu einem Stillstand der Abarbeitung aller Anfragen
von mehreren Sekunden kommen.

Dies kann man mit folgendem kleinem bash script nachstellen.


#!/bin/bash

while true
do
    mysql -uroot -p****** anotherdb -e "SELECT NOW(), id FROM table LIMIT 1"
    sleep 1
done

Während das script läuft, kann man dann in bspw. einer 2. Konsole
das “DROP DATABASE irgendeinedb” Statement ausführen.
Man kann dann in der ersten Konsole sehen das die Abarbeitung der Anfragen
anhalten.

Nachfolgende Prozedur kann Abhilfe schaffen.
(Man muss die Prozedur an eine Datenbank binden,
am besten nicht an die, die man löschen möchte.)

Die Prozedur löscht erst alle Tabellen der DB einzeln und erst am Ende
wird auf die quasi leere DB ein DROP DATABASE Statement angewendet.

Befehl:

CALL drop_database(‘irgendeinedb’);


DROP PROCEDURE IF EXISTS drop_database;

DELIMITER $$

CREATE PROCEDURE drop_database(IN _database VARCHAR(60))
BEGIN

DECLARE _done INT DEFAULT 0;
DECLARE _table VARCHAR(60);

DECLARE _cur CURSOR FOR SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = _database;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

OPEN _cur;
REPEAT
FETCH _cur INTO _table;
IF NOT _done THEN

SET @QUERY = CONCAT('DROP TABLE ', _database, '.', _table);

PREPARE query FROM @QUERY;
EXECUTE query;

DEALLOCATE PREPARE query;

END IF;
UNTIL _done END REPEAT;
CLOSE _cur;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

SET @QUERY = CONCAT('DROP DATABASE ', _database);

PREPARE query FROM @QUERY;
EXECUTE query;

DEALLOCATE PREPARE query;

END;

$$

DELIMITER ;

Simple Garbage Collection mit MySql Event Schedule

DROP EVENT IF EXISTS GARBAGE_COLLECTION;
DELIMITER $$

CREATE EVENT GARBAGE_COLLECTION
    ON SCHEDULE EVERY 30 SECOND DO
    BEGIN
        DELETE FROM session where expire < UNIX_TIMESTAMP(NOW());
    END $$

DELIMITER ;

16:56 ronsel http://www.tecchannel.de/server/news/2024963/unterschriftensammlung_fuer_mysql/
16:57 djahandarie It would make sense since it is a mysql-distributed scrip that removes them
16:57 Server grainol hat IRC (Read error: 60 (Operation timed out)) beendet.
16:57 Server buduk hat IRC () beendet.
16:57 domas ronsel: THANKS FOR BRINGING IN THIS MADNESS
16:57 djahandarie lol @ that first sentence… “Widenius will die”
16:57 domas heh
16:58 domas nooooeeeees, we need Monty
16:58 tanj maybe someone should add “no petitions” to the topic
16:58 domas even though he causes frustration and despair
16:58 CorticalStack (here we go)
16:58 ronsel whats going on with the monty?
16:58 domas he’s still a nice guy!
16:58 tanj and besides without him we wouldn’t be here today :)
16:58 Server agib (n=aarongib@pool-74-101-78-35.nycmny.east.verizon.net) hat den Kanal betreten.
16:58 ronsel thats for sure!
16:59 ronsel this channel could have a strange name like postgres :-)
16:59 domas tanj: yep, that would be so sad, we’d be in #postgresql ;-)
16:59 domas or some other.
16:59 domas firebird!
16:59 ronsel lol
16:59 tanj hey i’m in #postgresql also
16:59 VoVo64 #nosql
16:59 domas I’m there sometimes too
16:59 TodoInTX tanj: many people have contributed to getting us to where we are today.
16:59 domas they think I’m trolling though and want to ban me
17:00 domas and all I do is careful benchmarking :)
17:00 Isotopp i have a SELECT … FROM t WHERE (room_name_id, language_code ) IN ( ( 153701, “en”) ) yields in a Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’
17:00 CorticalStack with perl pack and unpack functions you don’t really need rdbms anyway.
17:00 TodoInTX hehe
17:00 thumbs domas: to be fair, not knowing you, you could come out as a troll.
17:00 Isotopp but SELECT … FROM t WHERE `room_name_id`=153701 AND `language_code` = “en”; works just fine
17:00 tanj TodoInTX: I know. I’m just jumping on the monty bandwagon :=)
17:00 Isotopp is this a mysql tuple equivalency processing problem again?
17:01 domas isotopp: this is coerciveness issue
17:01 thumbs Isotopp: check the collation on both columns.
17:01 Server spetrunia (n=spetruni@81.29.136.33) hat den Kanal betreten.
17:01 domas isotopp: but probably a bug too
17:01 Isotopp domas: language_code is a latin1 column, and i am on a SET NAMES utf8 connection
17:01 domas isotopp: probably coerciveness doesn’t work that well with IN(())
17:01 Isotopp domas: also, IN ( ( 153701, _latin1 “en”) ) works fine
17:02 domas it assumes that whole ((),(),()) set can have different charsets
17:02 tanj implicit cast in the 2nd case, prolly
17:02 Isotopp domas: but it is unclear to me why the WHERE … AND … works, whereas the IN (()) does not
17:02 domas isotopp: ^^^
17:02 domas my assumption
17:02 domas anyway, bug, file.
17:02 Server keystr0k (n=adam@rrcs-72-45-168-194.nys.biz.rr.com) hat den Kanal betreten.
17:02 thumbs assign it to domas
17:02 ronsel !title Save MySQL from ORACLE
17:02 Isotopp thumbs: will do :)
17:03 tanj sounds like “won’t fix”
17:03 domas he can’t
17:03 domas nobody can
17:03 domas I’m not in drop-downs anymore
17:03 domas :(
17:03 thumbs :(
17:03 * domas is a user, just like Isotopp
17:03 thumbs domas: but I liked seeing your name in the drop-down.
17:03 thumbs ronsel: umm?
17:03 Server LakWork (n=no@166-70-131-166.ip.xmission.com) hat den Kanal betreten.
17:04 Server ChanServ hat domas geoppt
17:04 Server Sie wurden aus dem Kanal von domas (shut up) rausgeworfen.
17:04 Server Sie haben den Kanal betreten.
17:04 Server MySQL Support and Discussion || 5.1 GA, 5.5 Milestone || We don’t provide answers, we help you find solutions || We don’t * phpMyAdmin (PMA) -> #phpmyadmin || Sense of humour mandatory || UC CfP deadline 2010-01-27
17:04 Server domas hat das Thema zu ‚ÄúMySQL Support and Discussion || 5.1 GA, 5.5 Milestone || We don’t provide answers, we help you find solutions || We don’t * phpMyAdmin (PMA) -> #phpmyadmin || Sense of humour mandatory || UC CfP deadline 2010-01-27 | No petitions‚Äù ge√§ndert.
17:04 Server domas hat domas entoppt
17:04 domas here
17:04 Server tommyd hat IRC (Read error: 110 (Connection timed out)) beendet.
17:04 djahandarie Dude…
17:04 ronsel :-)
17:04 djahandarie You need TWO |
17:05 VoVo64 djahandarie: its all about optimization!
17:05 domas omg
17:05 domas really
17:05 Server ChanServ hat domas geoppt
17:05 Server domas hat das Thema zu ‚ÄúMySQL Support and Discussion || 5.1 GA, 5.5 Milestone || We don’t provide answers, we help you find solutions || We don’t * phpMyAdmin (PMA) -> #phpmyadmin || Sense of humour mandatory || UC CfP deadline 2010-01-27 || No petitions‚Äù ge√§ndert.
17:05 Server kjkoster5489 hat IRC () beendet.
17:05 Server djahandarie wurde aus dem Kanal von domas (never correct me in public!!!!!) rausgeworfen.
17:05 Server domas hat domas entoppt
17:05 Server djahandarie (n=darius@unaffiliated/djahandarie) hat den Kanal betreten.
17:05 djahandarie :(
17:06 domas sorry, couldn’t resist kicking someone, as I had op!
17:06 ronsel saves the whales! uhm … :-)
17:06 CorticalStack power corrupts
17:06 CorticalStack myisam corrupts absolutely
17:07 tanj maria will save us
17:07 montywi domas: By saying no petitions, you take a stand against MySQL future; Just good to know where you stand ;)
17:07 xzilla montywi: maybe he is taking a stand for its future?
17:07 montywi Removing people their voice is not democratic
17:07 TodoInTX montywi: ha!
17:07 TodoInTX who said irc was democratic ?
17:08 domas montywi: I hear people can discuss it in #helpmysql
17:08 montywi xzilla: yes, wanting to kill it is also a standpoint, I agree.
17:08 salle montywi: Petitions are fine. SPAM the way you do it is ugly
17:08 domas montywi: I’m a user of Oracle InnoDB technology, and it works fine!!!!
17:08 domas montywi: it is GPL too!
17:08 Server `djisto (n=christop@c-98-247-240-28.hsd1.wa.comcast.net) hat den Kanal betreten.
17:09 domas BDB is also Oracle technology
17:09 Server Eggy_Blastoff hat IRC (Read error: 113 (No route to host)) beendet.
17:09 montywi salle: Yes, you can use it now, but not everyone can be sure it’s developed in the future……..
17:09 domas and it is much more popular database than MySQL
17:09 montywi domas: and MySQL is my technology, and you use it too…
17:09 montywi domas: library, not database
17:10 salle montywi: Sorry. MySQL is trademark which is not yours anymore
17:10 Server the_XL (n=aschwenk@pD9E8AC6B.dip0.t-ipconnect.de) hat den Kanal betreten.
17:10 salle montywi: And never was if I remember well
17:10 Server jjulian hat IRC (Read error: 60 (Operation timed out)) beendet.
17:10 domas montywi: well, if I’d look at oprofile, I’d see lots of time spent waiting for LOCK_open! :)
17:10 Server ChanServ hat CorticalStack geoppt
17:10 montywi salle: it’s still MySQL code
17:10 archivist split hairs day
17:10 TodoInTX montywi: mysql is not a library and if it is so important GPL library why do you want it BSD license now?
17:10 montywi and MySQL is GPL and anyone can use it, but not to promote another product
17:10 salle montywi: Most of MySQL code in use nowadays is not written by you
17:11 Server proycon (n=proycon@ip116-116-173-82.adsl2.static.versatel.nl) hat den Kanal betreten.
17:11 montywi TodoInTX: I don’t want it BSD, I want MySQL to get a good home. That’s different
17:11 proycon I have a large SQL file and what to check the syntax for errors, how can I do this?
17:11 domas ‘good home’ is speculation
17:11 Server pento (n=pento__@93-39-241-227.ip78.fastwebnet.it) hat den Kanal betreten.
17:11 montywi salle: Check the bzr; 60%+ is still my handwriting (if you remove extra engines)
17:11 domas proycon: no way without loading data
17:11 Server bintut hat IRC () beendet.
17:11 thumbs proycon: import it to a dummy db.
17:11 domas proycon: you can actually run a test instance for that, but…
17:11 montywi TodoInTX: BDB is a library
17:11 salle montywi: You never answered my question: Who *else* can take care of MySQL and make sure it does not die?
17:12 Server nusch (n=nusch@pc167-199.strzecha.ds.polsl.pl) hat den Kanal betreten.
17:12 thumbs CorticalStack: you can kick domas, now.
17:12 domas montywi: would be more interesting to see actual coverage information vs bzr lines :)
17:12 proycon hmm…. I was trying to avoid having to import it (it’s a huge file, very time consuming)
17:12 domas montywi: or oprofile-based annotate
17:12 TodoInTX montywi: but you just said “if you remove extra engines”
17:12 salle montywi: Your “Anyone, but Oracle” is not an answer
17:12 Server jjulian (n=j@e177075181.adsl.alicedsl.de) hat den Kanal betreten.
17:12 proycon I want to check the syntax to prevent surprised when I actually do import it
17:12 montywi domas: and don’t forget, when you need to switch to a fork, it’s not unlikely that you will need me…
17:12 proycon surprises*
17:13 Server Supra7 (n=chatzill@nat/ibm/x-mgvigxxxiybuzquj) hat den Kanal betreten.
17:13 Isotopp domas: I made it a support case, 43721
17:13 domas montywi: I will need you, or someone else :)
17:13 Supra7 my question is here: http://dpaste.com/141346/
17:13 domas Isotopp: I won’t see it, I don’t work for Sun
17:13 montywi salle: From a competition standpoint, that is actually almost correct interpretation
17:13 the_XL proycon: change the storage engine to blackhole
17:13 proycon ah! that is an interesting tip, thanks
17:13 LinuxJedi Isotopp: domas abandoned us ;)
17:13 Server smica (n=smica@h128-254.pool212-16.dyn.tolna.net) hat den Kanal betreten.
17:13 montywi domas: someone else who will be stupid & stubborn enough to work with you, you mean ?
17:13 salle montywi: “Anyone, but Oracle” is pathetic you know
17:13 Isotopp domas: Good for you
17:13 Server [raymond] (n=rderoo@unaffiliated/rainmkr) hat den Kanal betreten.
17:14 Isotopp domas: come to us!
17:14 domas montywi: :) nono, I am stubborn, they must not be!
17:14 salle montywi: Do you claim that french company who want MySQL for 1EUR will do better job?
17:14 montywi salle: As Oracle is the company with most reasons to want to kill MySQL, it’s not stupid
17:14 montywi domas: anyone to be able to work with you must be stubborn
17:14 tanj salle: which company ? :)
17:14 domas Isotopp: you’d have to fight with my employer :)
17:14 Isotopp domas: come to the dark side! we have cookies!
17:15 domas isotopp: you know where I work now? :)
17:15 montywi salle: Even them ;)
17:15 salle montywi: That’s not the question. The question is “Who can ensure better future for MySQL?”
17:15 xzilla montywi: you think oracle has more reason to kill mysql than microsoft ?
17:15 Isotopp domas: no
17:15 domas isotopp: the big F
17:15 Isotopp que?
17:15 domas isotopp: facebook
17:15 montywi xzilla: Microsoft would have the same competition problems as Oracle, so Microsoft would not be any better for EC
17:15 Isotopp Hah!
17:15 xzilla domas: really? they’ll let anyone work for them these days
17:15 Isotopp domas: Ok, that *is* the dark side.
17:16 domas isotopp: indeed
17:16 Server ajm hat IRC (Read error: 104 (Connection reset by peer)) beendet.
17:16 TodoInTX domas: hehe
17:16 Isotopp domas: yet, no cookies.
17:16 domas xzilla: really? :)
17:16 montywi salle: Anyone that doesn’t have an economic interest to kill it.
17:16 domas montywi: everyone has economic interest to kill it, in one way or another
17:16 xzilla montywi: i actually think microsoft would want to kill it more than oracle, but of course i dont think oracle wants to kill it, so…
17:16 domas mysql has economic interest to kill it
17:16 ronsel i should save this chat log,…
17:16 Server alkos333 (n=alkos333@c-24-12-213-191.hsd1.il.comcast.net) hat den Kanal betreten.
17:16 TodoInTX domas: yes, you cannot deny the power of the dark side… I’ve seen the bugs you submit now… jeesh.
17:16 Server SixThreeOh (n=j@pdpc/supporter/professional/sixthreeoh) hat den Kanal betreten.
17:17 archivist ronsel, it is logged
17:17 SixThreeOh My replicated slave isn’t replicating all of a sudden
17:17 domas todointx: we’ve brought some “support engineering” directly at the production engineering layer :)
17:17 LinuxJedi TodoInTX: just be thankful domas doesn’t use cluster, our workloads would double overnight ;)
17:17 montywi xzilla: Oracle looses more money because of MySQL than Microsoft (The big money on databases is on Linux/Unix)
17:17 TodoInTX LOL
17:17 salle montywi: The question is “Who can ensure better future for MySQL?”
17:17 TodoInTX aye
17:17 salle montywi: You have no answer?
17:17 montywi salle: I already answered that
17:17 xzilla my problem is that i think mysql has been mismanged by its corporate interests going all the way back to mysql ab, so i dont have much faith in another company to “do the right thing” any more than oracle, and probably have less
17:17 domas Fujitsu!
17:17 salle montywi: “Anyone, but Oracle” is pathetic
17:17 LinuxJedi Walmart!
17:17 domas xzilla: exactly
17:17 domas xzilla: what has been my point since very beginning
17:18 Server hever hat IRC (Remote closed the connection) beendet.
17:18 montywi salle: I did answer that in my blog, read it!
17:18 BBHoss can someone explain why the VSZ/RSS of a mysqld instance continues to grow? I just restarted a instance that had a RSS of over 600MB, and not it is back down to 200MB
17:18 nils_ maybe General Motors?
17:18 xzilla domas: side note, fujitsu currently supports postgres. not that companies havent tossed postgres aside for mysql in the past
17:18 nils_ they need a new business model anyways.
17:18 domas nils: lol
17:18 LinuxJedi nils_: they need to fix my damn car first! ;)
17:18 domas and they’ve got Obama running them!
17:18 montywi xzilla: any other company would have an incentive to develop MySQL further
17:18 domas montywi: you should’ve approached Obama to take over MySQL!
17:19 Server gunn0r2 hat IRC (Client Quit) beendet.
17:19 SixThreeOh http://pastebin.com/d766c851c < why has my replication server stopped replicating?
17:19 Server npatil (n=npatil@115.240.95.106) hat den Kanal betreten.
17:19 montywi xzilla: Fujitsu is also buying a lot of Sun high end hardware
17:19 montywi domas: why do you think I haven’t already done that ? ;)
17:19 Server hobodave hat IRC (Remote closed the connection) beendet.
17:19 xzilla montywi: it seems that a small company in charge of mysql is just as and maybe more likely to try to develop closed source pieces as oracle would be
17:19 TodoInTX SixThreeOh: because you don’t use \G and hurt our eyes. (ick)
17:19 montywi Guess who will come to dinner ? (Haven’t you seen the movie…)
17:19 xzilla montywi: given mysql ab was headed down that path
17:20 Server windhamdavid hat den Kanal () verlassen.
17:20 * Therion examines IBM’s services revenue
17:20 montywi xzilla: MySQL Ab wasn’t going that path; The could not because of the shareholder agreement that I had that stopped them.
17:20 Server tpsetter (n=tpsetter@64.70.116.116) hat den Kanal betreten.
17:20 Server MLord (n=MLord_my@75.97.197.126.res-cmts.sth.ptd.net) hat den Kanal betreten.
17:20 xzilla montywi: sure, but they *would have* gone down that path if they could have, and nothing would stop a new owner from doing that
17:21 Server snoyes hat IRC (“Leaving.”) beendet.
17:21 SixThreeOh how the hell do I use \G?
17:21 Leithal “montywi: domas: library, not database’
17:21 xzilla montywi: however oracle has incentives not to do that, since it would be quite the PR hit for them
17:21 Leithal hah
17:21 Server gammpamm hat IRC () beendet.
17:21 tanj SixThreeOh, read the error message it’s quite self-explanatory…
17:21 Therion SELECT 1 AS one\G
17:21 domas leithal: :)
17:21 domas whoah
17:21 Leithal that’s nothing but positioning
17:21 domas quite a meeting here tonight
17:21 MLord hehe
17:21 TodoInTX SixThreeOh: SHOW SLAVE STATUS\G
17:21 Server snoyes (n=sn221688@nat/sun/x-eadfmdzsplavfreq) hat den Kanal betreten.
17:21 Server proycon hat IRC (“Client exiting”) beendet.
17:21 * domas would be relatively happy to use InnoDB as embedded solution in some lightweight shell
17:21 domas ;-)
17:21 montywi xzilla: yes, and that was why I was happy that Sun was the buyer of MySQL, because Sun is open source friendly
17:21 * lig pulls up a chair
17:22 archivist Therion, has a voice! welcome back!
17:22 Therion montywi: Also they apparently really know how to run a business.
17:22 Therion montywi: Oh, wait
17:22 montywi xzilla: Oracle has never cared about bad PR when it comes to prices
17:22 nils_ domas: hmm there is a standalone InnoDB version and I think someone already added the memcache protocol to it
17:22 thumbs Therion: welcome back!
17:22 MLord domas: Innobase was a full DB at the beginning. Something like that might be possible
17:22 Therion archivist: I still have “kick everyone from the channel” ops, too!
17:22 domas mlord: well, they have embedded solution nowadays
17:22 Server zend_fan hat IRC (“Leaving.”) beendet.
17:22 TodoInTX domas: like drizzle?
17:22 xzilla montywi: i think they have, based on what they have done with innodb and sleepycat
17:22 Server Silowyi (n=silowyi_@24-155-116-210.dyn.grandenetworks.net) hat den Kanal betreten.
17:22 domas todointx: drizzle is less lightweight, methinks, nowadays!
17:22 tpsetter Hello. I have a where clause that looks something like this: WHERE r.endDate = CURDATE()… however, I need to change this to say r.enDate + 48 Hours = CURDATE()
17:22 HarrisonF domas, add innodb as a backup to memcached!
17:22 TodoInTX ah
17:22 thumbs Therion: I saw someone do that here a few years ago.
17:22 tpsetter how can I do something like that?
17:23 Server CorticalStack hat CorticalStack entoppt
17:23 HarrisonF s/backup/backend
17:23 domas harrisonf: hahah, I have experience, true!
17:23 Server SSantos (n=SSantos@201.39.39.163) hat den Kanal betreten.
17:23 thumbs CorticalStack: awww
17:23 Server kurkale6ka (n=chatzill@gateb.mh.bbc.co.uk) hat den Kanal betreten.
17:23 domas well, we’re playing a lot nowadays with HANDLER
17:23 montywi MLord: InnoDB had a very very basic SQL, but no optimizer, only basic types, no important features etc.
17:23 TodoInTX domas: but *everything* is a module, load and unload what you want and don’t want.
17:23 CorticalStack thumbs: don’t hand me a gun, I’m unstable
17:23 Therion thumbs: we’ve done it periodically to clear out the “i’ve been idling for 6 months” folks
17:23 domas montywi: thats nearly exactly what we need ;-)
17:23 nils_ http://www.innodb.com/products/embedded-innodb/
17:23 thumbs Therion: can I do it next? Heh.
17:23 HarrisonF montywi, i’m willing to say that most people think MySQL doesn’t have an optimizer either ;)
17:23 montywi xzilla: Neither InnoDB or BDD competes with Oracle 11g for any customer cases
17:23 domas lol
17:23 nils_ http://krow.livejournal.com/635679.html
17:24 MLord tpsetter: r.enDate + INTERVAL 48 hour =
17:24 Server ChanServ hat thumbs geoppt
17:24 Server lotus (n=lotus@cpe-72-224-0-114.nycap.res.rr.com) hat den Kanal betreten.
17:24 thumbs umm, perhaps not now.
17:24 Server thumbs hat thumbs entoppt
17:24 xzilla montywi: well, innodb certainly does, because without it mysql doesnt either
17:24 domas montywi: does MySQL _without_ InnoDB compete? :)
17:24 SixThreeOh http://pastebin.com/d73930da1
17:24 montywi HarrisonF: Actually, the MySQL optimizer is quite sophisticated nowadays.
17:24 tpsetter thanks
17:24 Server realtime (n=sabbath@unaffiliated/realtime) hat den Kanal betreten.
17:24 tpsetter MLord: thanks
17:24 Leithal InnoDB had internal SP’s before MySQL
17:24 tpsetter if i want to do 2 days i can do INTERVAL 2 days
17:24 domas Leithal: missed that, were they pulled out, or are they still there?
17:24 montywi domas: Sun has a contract for InnoDB, that allows it to compete.
17:25 HarrisonF domas, they are still there, it is used for a lot of things with the innodb data dictionary
17:25 domas hah
17:25 * Leithal nods
17:25 Server Stoetel (n=Legomi@cp428447-a.dbsch1.nb.home.nl) hat den Kanal betreten.
17:25 * domas kicks data dictionary
17:25 Server grainol_ ist jetzt bekannt als grainol.
17:25 * domas gets to it, slowly slowly
17:25 MLord Leithal: yep, data dictionary, FKs, SPs… you know, database stuff ;)
17:25 SixThreeOh It’s frozen on: “Waiting for master to send event”
17:25 Therion montywi: Sun’s contract doesn’t have anything to do with whether or not MySQL sans InnoDB is ever even vaguely competitive with Oracle
17:26 Server gunn0r (n=b0b@217.82.244.57) hat den Kanal betreten.
17:26 tanj SixThreeOh: hello, there’s an error in your replication… care to read it ?
17:26 Leithal “montywi: xzilla: Neither InnoDB or BDD competes with Oracle 11g for any customer cases” o.O
17:26 SixThreeOh that’s no an error
17:26 SixThreeOh not*
17:26 SixThreeOh that table never existed in that db
17:27 Leithal montywi: perhaps you should tell the EC that! :P
17:27 SixThreeOh it shouldn’t have changed any data at all
17:27 nils_ well to be fair, InnoDB development kind of stalled
17:27 SixThreeOh it’s a mystery why the slave would even be sent such an error
17:27 domas nils: has it?
17:27 Server lmorgado_ (n=lmorgado@a83-132-38-20.cpe.netcabo.pt) hat den Kanal betreten.
17:27 SixThreeOh How can I tell it to start replicating again?
17:28 Leithal nils_: no it hasn’t
17:28 Therion SixThreeOh: cross-database query? bad replication options?
17:28 nils_ domas: well recently it has picked up, but in the time before the plugin I didn’t really see a lot from them.
17:28 tanj SixThreeOh: error : table doesn’t exist. even if no data will be changed, it’s a fatal error and the replication will stop
17:28 MLord nils_: MUCH more has been done in the last two years than was done in the previous 3
17:28 fission6 what sort of optiions are out there for middle layers to manage a sharded mysql archetecture
17:28 MLord nils_: oracle has a much bigger team working on InnoDB now than Innobase did
17:28 SixThreeOh Therion: a query tried to execute on the wrong db, nothing happened
17:28 montywi Leithal: MySQL does compete, InnoDB as a separate engine doesn’t
17:28 Server lng (n=rbo@194.105.145.69) hat den Kanal betreten.
17:28 Server Sinogn hat IRC (Read error: 104 (Connection reset by peer)) beendet.
17:28 tanj SixThreeOh: set global sql_slave_skip_counter=1; start slave;
17:28 Therion SixThreeOh: It wouldn’t be in the binlog unless something happened ;)
17:28 SixThreeOh That error has to be from the master
17:28 Server Sinogn (n=Dekan@124.43.42.157) hat den Kanal betreten.
17:28 domas montywi: MySQL separately from InnoDB does not either
17:29 salle montywi: I do have my own company Salle LTD. Do you say I can certainly manage MySQL better than Oracle?
17:29 Therion montywi: MySQL without that engine does not usefully compete with Oracle
17:29 MLord montywi: if Oracle had killed InnoDB or revoked the license than MySQL would NOT compete in any real way
17:29 Therion montywi: Otherwise, you wouldn’t have spent the last however many years on Maria
17:29 fission6 oracle is going to own MySql :(
17:29 salle montywi: You know me and know I have no interest in killing it.
17:29 domas and falcon!
17:29 lng hi! is it possible to use foreign key constraint to delete data in child tables if parent row is deleted?
17:29 MLord montywi: so MySQL only supposedly competes with Oracle because they’ve let it. Ironic isn’t it?
17:29 Therion lng: yes
17:29 domas lng: cascade
17:30 fission6 SixThreeOh: show slave status; ?
17:30 lng thanx
17:30 domas damn, therion is still faster than me, after all these years of absence
17:30 SixThreeOh http://pastebin.com/d73930da1 < here
17:30 the_XL lng: you want ON DELETE CASCADE
17:30 Server alkos333 hat IRC (Read error: 60 (Operation timed out)) beendet.
17:30 Therion domas: lol
17:30 fission6 anyone here use a shard approach with in mysql
17:30 domas fission6: a bit
17:30 lng the_XL: yea
17:30 tanj sharding
17:30 the_wench a technique used for horizontal scaling of databases. Read http://www.jurriaanpersyn.com/archives/2009/02/12/database-sharding-at-netlog-with-mysql-and-php/
17:30 xzilla fission6: sure
17:30 TodoInTX SixThreeOh: if that db doesn’t exist on the slave skip that query and move on.
17:30 Server guy (n=guy@78-63-2-47.static.zebra.lt) hat den Kanal betreten.
17:31 SixThreeOh how can I tell the slave to ignore it?
17:31 guy any ideas why this would cause SQL syntax error?
17:31 guy SELECT *, (SELECT COUNT(language) FROM hp_pages p1 WHERE p1.language = l1.id) AS active FROM hp_languages l1 ORDER BY active DESC AND l1.position;
17:31 SixThreeOh the table doesn’t exist on either.
17:31 Server PZt hat IRC (Read error: 104 (Connection reset by peer)) beendet.
17:31 Server kroepke (n=kroepke@port-11629.pppoe.wtnet.de) hat den Kanal betreten.
17:31 tanj SixThreeOh, I gave you the answer already, please move on
17:31 thumbs guy: what is the error?
17:31 guy SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND l1.position’
17:31 fission6 xzilla: are the tables across different db nodes or partitioned on a db?
17:31 domas syntax
17:31 the_wench DO NOT INVENT SYNTAX (well, unless you have commit rights). Just follow the syntax described in the manual. Thanks.
17:31 domas guy: ^^^
17:32 domas o dabar eik
17:32 Therion guy: presumably you want ORDER BY active DESC, l1.position
17:32 Server mr-John hat IRC (“This computer has gone to sleep”) beendet.
17:32 guy ah, this is annoying mistake I do.
17:32 thumbs guy: ORDER BY col1, col2
17:32 CorticalStack guy: referencing an alias in your subquery that doesn’t exist
17:32 Therion guy: it is! you should be ashamed. probably push old people down stairs.
17:32 guy that worked, thank you thumbs, Therion.
17:32 Server tcdarow hat IRC (“Nettalk6 – www.ntalk.de”) beendet.
17:33 xzilla fission6: i think i have done it both ways
17:33 xzilla fission6: but most recent was different db nodes
17:33 montywi MLord: No, MySQL competes with Oracle because we are in the same market. Oracle buying InnoDB didn’t change that (We had a contract for many years that allows us to continue to sell licenses)
17:33 Server ajm (n=ajm@adsl-99-164-82-214.dsl.wlfrct.sbcglobal.net) hat den Kanal betreten.
17:33 nils_ montywi: with no way for Oracle to kill InnoDB?
17:33 fission6 you think heh? my question being from a mysql how do i make it transparent to developers to write queries and have them execute on the right db nodes within a sharded achetcture
17:33 MLord montywi: Our original license agreement ended AFTER they bought Innobase. You know that!
17:33 Server daemoen hat IRC (Remote closed the connection) beendet.
17:33 domas fission6: generally you don’t
17:34 xzilla fission6: you dont
17:34 domas fission6: but there’re some people who try to make it
17:34 montywi nils_: they could stop developing it yes; They would however have to fix bugs.
17:34 domas montywi: for how many years?
17:34 SixThreeOh tanj thanks.
17:34 MLord montywi: at the very least they could have let it idle or die, which they didn’t.
17:34 montywi If the would stopped developing it, then MySQL could have caused a fork
17:34 xzilla domas: well yes, you can do it how skype did it, but I dont think there is a way to do it in mysql (not with any level of complex app anyway)
17:35 Server TeeMist hat IRC (Read error: 60 (Operation timed out)) beendet.
17:35 MLord montywi: exactly! The only problem is that dual licensing couldn’t be used….
17:35 Server tpsetter hat IRC (“Get MacIrssi – http://www.sysctl.co.uk/projects/macirssi/”) beendet.
17:35 MLord montywi: it sounds like you’re arguing against yourself
17:35 montywi domas: you mean licenses? For 4 years, but they extended the contract after negotiations with us
17:35 xzilla montywi: istm that innodb has actually seen more development than any other table engine between the time oracle purchased it and now
17:35 montywi MLord: they used InnoDb to stop us from growing, and they where very successful with that
17:36 Leithal xzilla: that is true
17:36 montywi xzilla: no
17:36 Leithal balls
17:36 Therion montywi: We grew enormously after their purchase. You’re not even playing in reality, here.
17:36 xzilla montywi: mysql grew under Sun, how can you say they stopped you from growing?!?
17:36 MLord montywi: how so? that makes no sense
17:36 montywi All the new storage engines, PBXT, Calpoint, Infobright is doing much more
17:36 Therion No trolling here
17:36 domas Therion: did you hire xzillla?
17:36 Therion Take it somewhere else
17:36 MLord montywi: if you’re going by the… what do people call it… oh yeah, facts.
17:36 montywi xzilla: I meant before Sun
17:36 domas montywi: PBXT is not usable for disk-bound workloads, calpoint and infobright are not OLTP
17:37 TodoInTX montywi: a meteor could fall from the sky and kill me today, doesn’t mean that I have any proof that it will.
17:37 Therion domas: alas, no
17:37 Therion domas: do you want a job?!
17:37 MLord montywi: yeah, they’re doing much more… ok
17:37 montywi it still got more development done than InnoDB and it’s slowly getting there
17:37 domas Therion: I still got mine!
17:37 Server Kai hat IRC (“Leaving…”) beendet.
17:37 Therion domas: tell me how I can change that!
17:37 Server Flusher- ist jetzt bekannt als Flusher.
17:37 MLord montywi: they all suck, they need more work :)
17:37 LinuxJedi domas: we still need a new cluster guy ;)
17:37 domas LinuxJedi: I hear that
17:37 Server FFForever-Away ist jetzt bekannt als FFForever.
17:38 domas hey, if I worked for mysql again, I couldn’t do ‘product testing’ in farmville
17:38 Server FFForever hat den Kanal (“Leaving”) verlassen.
17:38 domas unless of course zynga has a support contract!
17:38 Server freebsd_fan\work hat IRC (“Why is the alphabet in that order? Is it because of that song?”) beendet.
17:38 LinuxJedi domas: you could play chess like geert ;)
17:38 SixThreeOh is it possible to roll back replicating some events?
17:38 domas sixthreeoh: everything is roll-forward
17:38 MLord LinuxJedi: you mean greet?
17:38 Therion what do you think this is, Oracle?
17:38 ronsel geert just takes some pictures …
17:39 SixThreeOh well great I’ll have to totally re-setup the replication then
17:39 Server km (n=km@outbound-nat-131.vmware.com) hat den Kanal betreten.
17:39 LinuxJedi MLord: ??
17:39 Therion [raymond]: p i n g
17:39 Server Cymage_ (n=james@unaffiliated/cymage) hat den Kanal betreten.
17:39 Server the_XL hat IRC (“Leaving”) beendet.
17:39 SixThreeOh I didn’t realise skip event skipped all events
17:39 fission6 domas: how would a developr know what box the query should run on?
17:40 Server Cymage ist jetzt bekannt als Cybercop.
17:40 Server Cymage_ ist jetzt bekannt als Cymage.
17:40 domas fission6: by calling ShardManager::getShardByXXXId()
17:40 pgalbraith hi all
17:40 the_wench oh hai pgalbraith
17:40 TodoInTX SixThreeOh: if there are differences on the slave and master you can use mk_table_sync to repair those.
17:40 TodoInTX without restoring the whole slave from backup.
17:40 fission6 domas: what manages the ShardManager?
17:40 MLord hi pgalbraith
17:40 domas fission6: ShardManagerManager of course
17:40 fission6 is thta something within mysql or a 3rd party tool
17:40 TodoInTX SixThreeOh: http://code.google.com/p/maatkit/wiki/mk_table_sync
17:40 domas fission6: that is something you write. =)
17:40 TodoInTX wrong page…
17:41 domas fission6: sharding nearly always is deep within app logic
17:41 fission6 what about mysql proxy or spock proxy(sp?)
17:41 MLord I sharted once
17:41 LinuxJedi domas: you coming to the UC this year?
17:41 TodoInTX SixThreeOh: http://www.maatkit.org/doc/mk-table-sync.html
17:41 xzilla MLord: eww
17:41 domas linuxjedi: yup!
17:41 pgalbraith MLord : hey there ! I see you connected with tim lord
17:41 fission6 is sharding even worth it
17:41 Server senk (n=Adium@dsl254-013-253.sea1.dsl.speakeasy.net) hat den Kanal betreten.
17:41 domas linuxjedi: and I don’t even have to do a talk to come!!!!

[14:09] <attacke> domas: my mysql are rude, i cant please him :/
[14:09] <attacke> ERROR 1054 (42S22): Unknown column ‘please’ in ‘field list’
[14:09] <domas> =)
[14:09] <domas> works for me!
[14:10] <domas> DELIMITER “, please”
[14:10] <domas> :)
[14:11] <ronsel> lol
[14:11] <attacke> ooh, nice mysql :)
[14:11] <attacke> mysql> select version(), please
[14:11] <attacke> +————————–+
[14:11] <attacke> | version()                |
[14:11] <attacke> +————————–+
[14:11] <attacke> | 5.0.32-Debian_7etch6-log |
[14:11] <attacke> +————————–+
[14:11] <attacke> 1 row in set (0.00 sec)
[14:11] <domas> :)
[14:11] <alcohol> I bet people would find it very annoying if you made that the default
[14:11] <alcohol> :p

Der PRIMARY KEY `Id` ist per Definition UNIQUE, dennoch wird das DISTINCT vom Query OPTIMIZER
bei der Ausführung des Query nicht ignoriert.
Das schlägt sich natürlich erheblich auf die Ausführungszeit.

mysql> SELECT SQL_NO_CACHE COUNT(DISTINCT Id) FROM members;
+--------------------------+
| count(DISTINCT Id) |
+--------------------------+
|                    71301 |
+--------------------------+
1 row in set (0.68 sec)
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM members;
+----------+
| count(*) |
+----------+
|    71301 |
+----------+
1 row in set (0.03 sec)

Das Problem besteht zumindest noch in Version 5.0.32

mysql> SELECT VERSION();
+--------------------------+
| VERSION()                |
+--------------------------+
| 5.0.32-Debian_7etch3-log |
+--------------------------+

Um die Ergebnismenge doch noch zu verfeinern (MBR Problem)
bzw. den Abstand der gefundenen Ergebnisse zum Ursprungspunkt anzuzeigen hilft folgende
Funktion:

DROP FUNCTION IF EXISTS GEODISTANCE;

DELIMITER $$

CREATE FUNCTION GEODISTANCE (lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE) RETURNS DOUBLE UNSIGNED
DETERMINISTIC NO SQL
BEGIN
	DECLARE rlat1, rlat2, rlon1, rlon2 DOUBLE;
	DECLARE EARTH DOUBLE UNSIGNED DEFAULT 6371.0;

	SET rlat1 = RADIANS(lat1),
		rlon1 = RADIANS(lon1),
		rlat2 = RADIANS(lat2),
		rlon2 = RADIANS(lon2);

	RETURN ROUND(COALESCE(ACOS(
		(SIN(rlat2)*SIN(rlat1)) +
		(COS(rlat2)*COS(rlat1)*COS(rlon1-rlon2))
	) * EARTH, 0), 2);

END;

$$
DELIMITER ;

Geo Koordinaten Umkreis Suche unter Verwendung von Spatial (R-Tree) Indizes
und der Mysql Geometry Funktionen.

Für die Suche wird eine Tabelle mit folgender oder ähnlicher Struktur benötigt:
Dump incl. Testdaten (geo.sql)

CREATE TABLE `geo` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `coordinate` geometry NOT NULL,
   `zip` int(5) unsigned zerofill NOT NULL,
   PRIMARY KEY  (`id`),
   SPATIAL KEY `coordinate` (`coordinate`),
   KEY `zip` (`zip`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Die folgende SQL Function bekommt die Geographische Länge und Breite, den Umkreisradius in KM und die Anzahl
der Ecken des Umkreises (Genauigkeit) übergeben. Als Rückgabe liefert die Funktion ein MySQL GEOMETRY Object
welches auf die Tabelle `geo` angewendet werden kann.

DROP FUNCTION IF EXISTS GEOPOLYGON;
DELIMITER $$

CREATE FUNCTION GEOPOLYGON(lat DOUBLE,lon DOUBLE,radius MEDIUMINT,edges TINYINT) RETURNS GEOMETRY
DETERMINISTIC
BEGIN

DECLARE i MEDIUMINT DEFAULT 1;
DECLARE a,b,c DOUBLE DEFAULT NULL;
DECLARE result VARCHAR (2048) DEFAULT NULL;

IF (edges<3 || edges>360 || radius>500) THEN
    RETURN NULL;
END IF;

SET result=CONCAT(lat+radius/111.12,' ',lon,',');

WHILE (i<edges)
    SET c=RADIANS(360/edges*i);
    SET a=lat+COS(c)*radius/111.12;
    SET b=lon+SIN(c)*radius/(COS(RADIANS(lat + COS(c)*radius/111.12/111.12))*111.12);
    SET result=CONCAT(result,a,' ',b, ',');
    SET i=i+1;
END WHILE;

RETURN GEOMFROMTEXT(CONCAT('POLYGON((',result,lat+radius/111.12,' ',lon,'))'));
END;

$$
DELIMITER ;

Der Query in dem die Function GEOPOLYGON() verwendet wird, liefert
als Ergebnis eine Liste mit geo.id und geo.zip.
Im Funktionsaufruf kann Umkreis und in der WHERE Bedingung des sub- selects die Postleitzahl
des Mittelpunktes vom Umkreis definiert werden.

SELECT
	id,	zip
FROM geo
WHERE CONTAINS
(
	(
	SELECT
		GEOPOLYGON(X(coordinate),Y(coordinate), 5, 12)
	FROM geo
	WHERE zip = 06116 LIMIT 1
	), coordinate
);
+-------+-------+
| id    | zip   |
+-------+-------+
| 18617 | 06188 |
| 10723 | 06184 |
|  8090 | 06124 |
| 11434 | 06116 |
| 11435 | 06128 |
| 11436 | 06130 |
| 15246 | 06122 |
| 15991 | 06118 |
| 15992 | 06126 |
| 17664 | 06120 |
| 17665 | 06110 |
| 19574 | 06114 |
| 19947 | 06112 |
| 15654 | 06188 |
|  8092 | 06108 |
| 20785 | 06132 |
+-------+-------+
16 rows in set (0.00 sec)

MySQL implementiert die Funktion CONTAINS() unter anderen zurzeit noch nicht entsprechend der Spezifikation. Die, die implementiert werden, geben dasselbe Ergebnis wie die entsprechenden MBR-basierten Funktionen zurück.

MBR steht für “minimum bounding rectangle” (kleinstes umschließendes Rechteck).
Das berechnete Polygon welches die GEOPOLYGON Funktion zurück liefert ist also eigentlich nur das kleinstmögliche Rechteck, zumindest solange bis MySQL die Funktionen entsprechend implementiert hat, was gerade so um das Polygon herum passt.