[InnoDB] AUTO_INCREMENT niet betrouwbaar (!) - hoe anders?

[JayVee]

Ik kom er net achter dat we een serieus probleem hebben in de relationele integriteit van onze database:
InnoDB "reset" soms de AUTO_INCREMENT! Ik zag dat toen na een ALTER TABLE statement de AUTO_INCREMENT veranderde in SELECT MAX(id) + 1. Ik dacht eerst dat hem om een bug ging, maar het blijkt een feature te zijn:

If you specify an AUTO_INCREMENT column for an InnoDB table, [...] this counter is stored only in main memory, not on disk.

InnoDB uses the following algorithm to initialize the auto-increment counter for a table [..] After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE;

Ik ben niet de enige die dit onverwachte (en raar) gedrag tegen komt, maar denk op de andere kant ook dat wel meer mensen schrikken als ze dit horen (zo kon ik in de GoT search geen topics hierover vinden!).

Het specifieke probleem hoef ik niet eens uit te leggen denk ik. Overal waar je refereert naar tuples op basis van een auto_increment veld zonder foreign key en tuples verwijderd kunnen worden kan het dus zijn dat er twee keer data op dezelfde id komt, en je referentie niet meer klopt.

De reden voor dit topic is niet alleen om jullie bang te maken , maar ook om te vragen of jullie dit zijn tegen gekomen en om te discussiëren teren wat de mogelijke oplossingen hiervoor zijn. Hier zijn alvast vier alternatieven:

1. Foreign Keys gebruiken
Uiteraard kan dat, en dat lost het probleem ook compleet op. Helaas is het niet overal mogelijk of gewenst. (Zo gebruiken wij een tabel die voor data in meerdere tabellen een centrale audit trail opslaat. Om voor elke tabel een aparte audit trail tabel aan te maken is niet gewenst, (want chaos, en lastig/onmogelijk om aggregate functions te gebruiken))

2. pre-INSERT triggers / constraints?
Ik heb niet gekeken of dit mogelijk is, maar als dit mogelijk is kun je het werk tenminste weer uit handen geven.

3. Op applicatie niveau de juiste id bepalen
Zo zouden wij in de "centrale" audit trail tabel de max id voor een tabel kunnen opvragen, en die + 1 gebruiken voor de volgende INSERT. Zeker geen ideale oplossing, je moet overal extra code schrijven.

4. Een aparte auto_increment tabel bijhouden
Ook dit is een lelijke oplossing eigenlijk, omdat je het werk doet wat je database eigenlijk voor je zou moeten doen.

Oplossingen 1 en 2 zitten in de database, 3 en 4 in de applicatie. Uiteraard is het eerste geprefereerd!

[ Voor 0% gewijzigd door JayVee op 24-10-2008 17:22 . Reden: slechte spelling ]

[NMe]

5. Niets hard verwijderen uit je database. Nooit.

[.oisyn]

Ik wilde net zeggen, de belangrijkste optie mist nog . Sowieso, of je nou letterlijk foreign key constraints gebruikt of niet, het lijkt me logisch dat als je een kolom hebt die semantisch de betekenis van een foreign key heeft, dat dat element niet ineens zomaar naar niet bestaande data mag wijzen, ookal laat je db het physiek wel toe. Het zou dus überhaupt niet mogen voorkomen dat bij recyclen van keys je ineens interne data referenties hebt die helemaal niet kloppen.

[ Voor 16% gewijzigd door .oisyn op 24-10-2008 17:49 ]

[JayVee]

Optie 5 ben ik inderdaad vergeten te noemen, maar daar had ik wel over nagedacht.

Het probleem hiermee is dat je elke SELECT uit moet breiden met een WHERE deleted = 0 of zo. Hier moet dan ook meteen een index op, anders worden praktisch al deze queries traag.

Het zou dus überhaupt niet mogen voorkomen dat bij recyclen van keys je ineens interne data referenties hebt die helemaal niet kloppen.

Akkoord, dat is een manier om dit op te lossen. Dan zul je of de db zo in moeten richten dat deze het werk af kan nemen, of je moet het op applicatie niveau doen.

Het liefst had ik natuurlijk dat de keys niet recycled worden, dan had ik het probleem niet. Maar aangezien dit (raar genoeg) niet kan zal ik inderdaad met een andere insteek moeten werken.

[NMe]

JayVee schreef op vrijdag 24 oktober 2008 @ 20:12:
Optie 5 ben ik inderdaad vergeten te noemen, maar daar had ik wel over nagedacht.

Het probleem hiermee is dat je elke SELECT uit moet breiden met een WHERE deleted = 0 of zo. Hier moet dan ook meteen een index op, anders worden praktisch al deze queries traag.

Waar tegenover staat dat je niet alleen jouw probleem oplost, maar ook het feit dat één foute druk op de knop niet meer betekent dat er data onherroepelijk verdwenen is.

JayVee schreef op vrijdag 24 oktober 2008 @ 20:12:
Akkoord, dat is een manier om dit op te lossen. Dan zul je of de db zo in moeten richten dat deze het werk af kan nemen, of je moet het op applicatie niveau doen.

Het liefst had ik natuurlijk dat de keys niet recycled worden, dan had ik het probleem niet. Maar aangezien dit (raar genoeg) niet kan zal ik inderdaad met een andere insteek moeten werken.

Het zou alsnog geen probleem op moeten leveren. Als je dan toch een record wil verwijderen uit je database, dan moet je ook alle verwijzingen daarnaar verwijderen. Als je dat gewoon doet is ook het recyclen van keys geen probleem meer; als je het niet doet staat er overbodige data in je database waar je niets meer mee kan omdat het nergens meer aan gelinkt is.

[ACM]

-NMe- schreef op vrijdag 24 oktober 2008 @ 22:41:
Het zou alsnog geen probleem op moeten leveren. Als je dan toch een record wil verwijderen uit je database, dan moet je ook alle verwijzingen daarnaar verwijderen. Als je dat gewoon doet is ook het recyclen van keys geen probleem meer; als je het niet doet staat er overbodige data in je database waar je niets meer mee kan omdat het nergens meer aan gelinkt is.

Hoewel ik dit met je eens ben, is het wel weer een functionaliteit die typerend voor MySQL net niet helemaal goed is geimplementeerd. Er zijn natuurlijk allerlei scenario's te verzinnen waarbij een waarde uiteindelijk buiten de database doorgegeven wordt en dan intern verwijderd wordt. Dan is de extern doorgegeven waarde ineens inconsistent met de huidige staat van de database, terwijl er niet een foutmelding komt ala 'dat item is verwijderd'.

In veel gevallen zal zoiets niet echt kwaad kunnen, nog afgezien van het feit dat je ook niet continu je database herstart, maar toch is het net niet helemaal zoals het hoort. En met soft-deleten voorkom je het ook nog eens helemaal.

JayVee schreef op vrijdag 24 oktober 2008 @ 20:12:
Het probleem hiermee is dat je elke SELECT uit moet breiden met een WHERE deleted = 0 of zo. Hier moet dan ook meteen een index op, anders worden praktisch al deze queries traag.

Dat is geen algemeen geldend statement. De deleted-vlag zal in de meeste scenario's relatief weinig op 1 staan, waardoor als je alle records uit je tabel nodig hebt, er nauwelijks winst valt te behalen door zo'n index te gebruiken. Datzelfde geldt ook voor alle andere statements waarbij je die vlag moet toevoegen. Het kan zeker nuttig zijn om de deleted-vlag toe te voegen aan je indexen zodat MySQL die wat efficienter kan gebruiken, maar het is zeker niet zo dat die extra boolean-check de boel ineens laat kruipen.

[EfBe]

-NMe- schreef op vrijdag 24 oktober 2008 @ 22:41:
[...]

Waar tegenover staat dat je niet alleen jouw probleem oplost, maar ook het feit dat één foute druk op de knop niet meer betekent dat er data onherroepelijk verdwenen is.

Ware het niet dat soft-deletes wel voor andere problemen zorgen: je hebt nl een veel grotere database dan je werkset op een gegeven moment, wat er voor zorgt dat je queries onnodig traag worden. Als je soft-deletes wilt, doe dat dan met een archive catalog en on delete triggers waarbij je dus de rows simpelweg copieert naar de archive catalog: hierdoor blijven de rows bewaart en heb je toch geen grotere werkset.

Ik ga zelfs zover dat het adviseren van soft deletes een fout advies is: de nadelen zijn echt vele malen groter dan de voordelen. ALS men al de functionaliteit nodig heeft dat data nooit verloren gaat (wat in veel gevallen echt niet aan de orde is), gebruik dan een catalog daarvoor waar de archive data wordt opgeslagen.

Aan topicstarter: je gebruikt mysql, dus houd rekening met crappy pseudo-acid implementaties. Als je solide database gebruik wilt, gebruik dan een goede database, zoals postgresql of een commerciele variant. Het recyclen van identity values is een doodzonde, het ondergraaft nl. ACID: keys die tijdens een transaction zijn uitgegeven maar niet worden gebruikt ivm het terugrollen van de transaction mogen nooit worden her-uitgegeven. Ook andere redenen om een sequence, wat het in feite is, opnieuw waarden te laten uitgeven is anti-ACID.

[ Voor 19% gewijzigd door EfBe op 25-10-2008 11:16 ]

[Verwijderd]

EfBe schreef op zaterdag 25 oktober 2008 @ 11:11:
Het recyclen van identity values is een doodzonde, het ondergraaft nl. ACID: keys die tijdens een transaction zijn uitgegeven maar niet worden gebruikt ivm het terugrollen van de transaction mogen nooit worden her-uitgegeven.

Klopt. Die 'feature' van InnoDB is dus doodgewoon een bug. Of een design flaw.

[NMe]

EfBe schreef op zaterdag 25 oktober 2008 @ 11:11:
[...]

Ware het niet dat soft-deletes wel voor andere problemen zorgen: je hebt nl een veel grotere database dan je werkset op een gegeven moment, wat er voor zorgt dat je queries onnodig traag worden. Als je soft-deletes wilt, doe dat dan met een archive catalog en on delete triggers waarbij je dus de rows simpelweg copieert naar de archive catalog: hierdoor blijven de rows bewaart en heb je toch geen grotere werkset.

Ik ga zelfs zover dat het adviseren van soft deletes een fout advies is: de nadelen zijn echt vele malen groter dan de voordelen. ALS men al de functionaliteit nodig heeft dat data nooit verloren gaat (wat in veel gevallen echt niet aan de orde is), gebruik dan een catalog daarvoor waar de archive data wordt opgeslagen.

...waarmee in InnoDB dus alsnog je keys weer vrijgegeven worden om opnieuw uitgedeeld te worden?

Verder praten we over MySQL; dat heeft nu eenmaal wat vreemde eigenschappen en je kan er nu eenmaal niet alles mee wat je met SQL Server kan.

[JayVee]

-NMe- schreef op vrijdag 24 oktober 2008 @ 22:41:
[...]
Waar tegenover staat dat je niet alleen jouw probleem oplost, maar ook het feit dat één foute druk op de knop niet meer betekent dat er data onherroepelijk verdwenen is.

Daar zit een javascript confirm() omheen, dus het gebeurd niet zomaar. Daarnaast hebben we juist de audit trail waarin geregistreerd wordt welke wijzigingen zijn doorgevoerd, waaronder dus ook het verwijderen (waarbij alle waarden op het moment van verwijderen worden vastgelegd). Data gaat dus niet onherroepelijk verloren - het verdwijnt wel uit het overzicht, maar alle mutaties zijn (en blijven) vastgelegd.

Het zou alsnog geen probleem op moeten leveren. Als je dan toch een record wil verwijderen uit je database, dan moet je ook alle verwijzingen daarnaar verwijderen. Als je dat gewoon doet is ook het recyclen van keys geen probleem meer; als je het niet doet staat er overbodige data in je database waar je niets meer mee kan omdat het nergens meer aan gelinkt is.

Ik denk dat dit voor dit probleem (audit trail waarbij echte foreign key niet gewenst is) de beste oplossingsrichting is. Aangezien de verwijzing naar een bepaald id in de audit trail nutteloos is (er wordt ook niet op gesorteerd of wat dan ook) kan ik die inderdaad beter op NULL zetten.

ACM schreef op zaterdag 25 oktober 2008 @ 11:03:
[...]
Hoewel ik dit met je eens ben, is het wel weer een functionaliteit die typerend voor MySQL net niet helemaal goed is geimplementeerd. Er zijn natuurlijk allerlei scenario's te verzinnen waarbij een waarde uiteindelijk buiten de database doorgegeven wordt en dan intern verwijderd wordt. Dan is de extern doorgegeven waarde ineens inconsistent met de huidige staat van de database, terwijl er niet een foutmelding komt ala 'dat item is verwijderd'.

Er zijn inderdaad andere scenario's te bedenken waarbij het erg vervelend is dat de database hier geen goede ondersteuning biedt en je het dus op applicatie niveau moet aanpakken.

In veel gevallen zal zoiets niet echt kwaad kunnen, nog afgezien van het feit dat je ook niet continu je database herstart, maar toch is het net niet helemaal zoals het hoort.

Zoals in mijn startpost vermeld doet InnoDB dit ook na een ALTER TABLE statement (dit gedrag kon ik niet terug vinden in de documentatie trouwens!). Het komt gelukkig ook niet vaak voor, maar toch zou je willen dat je hier niet over na hoeft te denken.

Dat is geen algemeen geldend statement. De deleted-vlag zal in de meeste scenario's relatief weinig op 1 staan, waardoor als je alle records uit je tabel nodig hebt, er nauwelijks winst valt te behalen door zo'n index te gebruiken. Datzelfde geldt ook voor alle andere statements waarbij je die vlag moet toevoegen. Het kan zeker nuttig zijn om de deleted-vlag toe te voegen aan je indexen zodat MySQL die wat efficienter kan gebruiken, maar het is zeker niet zo dat die extra boolean-check de boel ineens laat kruipen.

Daar heb je gelijk in, performance is niet zo'n issue. Wat mij betreft is het wel minder mooi dat je in je applicatie er overal rekening mee moet houden.

EfBe schreef op zaterdag 25 oktober 2008 @ 11:11:
[...]
Aan topicstarter: je gebruikt mysql, dus houd rekening met crappy pseudo-acid implementaties. Als je solide database gebruik wilt, gebruik dan een goede database, zoals postgresql of een commerciele variant. Het recyclen van identity values is een doodzonde, het ondergraaft nl. ACID: keys die tijdens een transaction zijn uitgegeven maar niet worden gebruikt ivm het terugrollen van de transaction mogen nooit worden her-uitgegeven. Ook andere redenen om een sequence, wat het in feite is, opnieuw waarden te laten uitgeven is anti-ACID.

Crappy ACID implementatie is voor ons niet zo'n groot probleem, we doen ampers transacties en er zijn ook meestal niet veel gebruikers tegelijk online. Het gaat wel om kwaliteitsgegevens van onze klanten, dus integriteit van de data is wel erg belangrijk. Nu hebben sommige resultaten (tuples) een audit trail waarin staat dat het resultaat is verwijderd... en dat stinkt natuurlijk.

Verwijderd schreef op zaterdag 25 oktober 2008 @ 12:23:
[...]
Klopt. Die 'feature' van InnoDB is dus doodgewoon een bug. Of een design flaw.

Eensch!

Of het verwijderen nou een goed plan is of niet, als de AUTO_INCREMENT fatsoenlijk bijgehouden zo worden door InnoDB hadden we deze discussie niet.

[Verwijderd]

Kun je niet naar PostgreSQL of Firebird overgaan?
De eerste ondersteunt identity fields prima, de tweede niet, maar die heeft een heel mooi generator systeem dat je in je 'before insert/update' triggers kunt gebruiken.
Of als 't geld mag kosten gewoon een goede database als DB2, MSSQL, Oracle, SyBase, etc...

[Verwijderd]

-NMe- schreef op vrijdag 24 oktober 2008 @ 17:41:
5. Niets hard verwijderen uit je database. Nooit.

Kleine correctie:

5. Niets hard verwijderen uit je database. Nooit!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111oneoneoneeleven

Als je het hebt over relationele integriteit kun je never nooit wijzen naar een tupel die niet meer bestaat.

[NMe]

JayVee schreef op zaterdag 25 oktober 2008 @ 13:56:
Of het verwijderen nou een goed plan is of niet, als de AUTO_INCREMENT fatsoenlijk bijgehouden zo worden door InnoDB hadden we deze discussie niet.

Dan nog is het best practice om geen dingen te verwijderen uit je database zonder verwijzingen ernaar ook te verwijderen, en in veel gevallen is het dus ook goed om gewoon niets te verwijderen en alleen te taggen. We zouden dus deze discussie alleen in een andere vorm hebben gevoerd, maar de voedingsbodem voor die discussie is er toch.

[Verwijderd]

Verwijderd schreef op zaterdag 25 oktober 2008 @ 15:58:Kleine correctie:

5. Niets hard verwijderen uit je database. Nooit!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111oneoneoneeleven

Als je het hebt over relationele integriteit kun je never nooit wijzen naar een tupel die niet meer bestaat.

Als je foreign keys en cascading delete gebruikt hoeft daar niks mis mee te zijn.
En Belgische of Italiaanse middenstanders zullen je dankbaar zijn om die software op hun kassa...

[curry684]

Verwijderd schreef op zaterdag 25 oktober 2008 @ 16:10:
[...]

Als je foreign keys en cascading delete gebruikt hoeft daar niks mis mee te zijn.

Cascading deletes vinden de meeste mensen ook een doodzonde, dat zo ongeveer je je hele DB kunt droppen met 1 misplaatste delete, een userfoutje, of vergeten where clause.

[MSalters]

1 misplaatste delete kan een database waardeloos maken, cascading deletes of niet. Als je boekhouding niet meer op 0 uitkomt, dan zul je sowieso terug naar je backup moeten. En voor de mogelijke gevolgen van een gemiste WHERE clause hoeven we alleen naar tweakers te kijken.

[Verwijderd]

MSalters schreef op zondag 26 oktober 2008 @ 01:36:
1 misplaatste delete kan een database waardeloos maken, cascading deletes of niet. Als je boekhouding niet meer op 0 uitkomt, dan zul je sowieso terug naar je backup moeten. En voor de mogelijke gevolgen van een gemiste WHERE clause hoeven we alleen naar tweakers te kijken.

Als je bovenliggende laag nooit een DELETE uitvoert kan het ook niet per ongeluk mis gaan

Tenzij de user direct in de DB zit te rommelen, maar dan zijn all bets off.

[cariolive23]

Het gaat wel om kwaliteitsgegevens van onze klanten, dus integriteit van de data is wel erg belangrijk.

Dan valt het gebruik van MySQL per definitie af, daar zitten zoveel "features" (lees: bugs) in dat jouw data altijd onbetrouwbaar is. Een standaard geconfigureerde databaseserver geeft op veel fouten geen foutmelding, maar verknalt de data. En mocht je de server correct hebben geconfigureerd, dan kun je runtime deze configuratie settings zo aanpassen dat de boel wederom in het honderd loopt. Zie het geklooi met de diverse modes die je in MySQL kunt instellen. Je weet dus nooit waar je aan toe bent, je kent de geschiedenis van de records en settings niet.

Stap over op een database waarbij integriteit het uitgangspunt is, bv. PostgreSQL, Oracle of SQL Server.

[NMe]

MSalters schreef op zondag 26 oktober 2008 @ 01:36:
1 misplaatste delete kan een database waardeloos maken, cascading deletes of niet. Als je boekhouding niet meer op 0 uitkomt, dan zul je sowieso terug naar je backup moeten. En voor de mogelijke gevolgen van een gemiste WHERE clause hoeven we alleen naar tweakers te kijken.

Allemaal waar natuurlijk, maar met cascading deletes verwijder je niet mogelijk één record of zelfs één tabel, maar met een beetje pech je halve database. Ik ben ook een beetje huiverig voor het gebruik van cascading deletes.

Verwijderd schreef op zondag 26 oktober 2008 @ 02:40:
[...]

Als je bovenliggende laag nooit een DELETE uitvoert kan het ook niet per ongeluk mis gaan

Tenzij de user direct in de DB zit te rommelen, maar dan zijn all bets off.

Dat is ook niet helemaal waar natuurlijk; er kan een query zijn die bijna nooit gebruikt wordt en die fout gaat wanneer dat wel het geval is omdat er niet goed getest is. Een query die een update doet (en dus geen delete) waardoor allerlei data in een tabel overschreven wordt en je data gewoon "weg" is. Dat wordt zoals MSalters al zegt automatisch terugdraaien naar een backup, met alle gevolgen van dien.

cariolive23 schreef op zondag 26 oktober 2008 @ 09:57:
[...]

Dan valt het gebruik van MySQL per definitie af, daar zitten zoveel "features" (lees: bugs) in dat jouw data altijd onbetrouwbaar is. Een standaard geconfigureerde databaseserver geeft op veel fouten geen foutmelding, maar verknalt de data. En mocht je de server correct hebben geconfigureerd, dan kun je runtime deze configuratie settings zo aanpassen dat de boel wederom in het honderd loopt. Zie het geklooi met de diverse modes die je in MySQL kunt instellen. Je weet dus nooit waar je aan toe bent, je kent de geschiedenis van de records en settings niet.

Stap over op een database waarbij integriteit het uitgangspunt is, bv. PostgreSQL, Oracle of SQL Server.

Wat een onzin. Als jij als programmeur weet wat je doet kun je ook prima met MySQL de integriteit van je data garanderen, al kost dat misschien net wat meer moeite.

[EfBe]

-NMe- schreef op zondag 26 oktober 2008 @ 12:38:
Wat een onzin. Als jij als programmeur weet wat je doet kun je ook prima met MySQL de integriteit van je data garanderen, al kost dat misschien net wat meer moeite.

Hoe bedoel je, 'meer moeite' ? MySql's transactional systeem is gewoon niet correct, het kan niet ACID garanderen in 100% van de gevallen, en dus is er voor jou niet iets te doen als 'meer moeite', er is gewoon geen mogelijkheid het beter te doen: jij bouwt een query, de DB voert hem uit en garandeert dat binnen de SQL scope de query wordt uitgevoerd volgens ACID principes. MySql is daar niet voor 100% toe in staat.

[cariolive23]

-NMe- schreef op zondag 26 oktober 2008 @ 12:38:
Wat een onzin. Als jij als programmeur weet wat je doet kun je ook prima met MySQL de integriteit van je data garanderen, al kost dat misschien net wat meer moeite.

Leg mij dan eens uit hoe je dat moet doen, dit wordt door MySQL niet ondersteund. Zie de handleiding, daar staat het keurig in.

Denk eens aan de ravage die je kunt aanrichten wanneer je runtime even de mode van MySQL aanpast... De server was keurig geconfigureerd, ongeldige datums werden afgekeurd, ongeldige GROUP BY-rommel werd afgeschoten en toen kwam er een prutser die het handig vond om even voor een paar queries de mode aan te passen... SQL modes in MySQL

En heb je deze punten wel eens doorgenomen? Lees en huiver: yapf.net

MySQL is van A tot Z onbetrouwbaar, zeker wanneer er meer dan 1 persoon iets meer mag dan uitsluitend SELECT-queries uitvoeren. Hou een ander voor de gek, maar wees zelf heel voorzichtig met het gebruik van deze database. Je weet vooraf dat je hier lelijk je vingers aan kunt branden. En jouw klanten kunnen dit ook weten. Zeker wanneer er later wat fout gaat, ze weten je te vinden, op jouw advies is men hier in gestonken.

[Confusion]

JayVee schreef op vrijdag 24 oktober 2008 @ 17:18:
1. Foreign Keys gebruiken
Uiteraard kan dat, en dat lost het probleem ook compleet op. Helaas is het niet overal mogelijk of gewenst. (Zo gebruiken wij een tabel die voor data in meerdere tabellen een centrale audit trail opslaat. Om voor elke tabel een aparte audit trail tabel aan te maken is niet gewenst, (want chaos, en lastig/onmogelijk om aggregate functions te gebruiken))

Bepaald elegant vind ik die oplossing niet. Ik zou voor iedere tabel een eigen schaduwtabel met het betreffende audit trail bijhouden. Tabellen zijn gratis; een aanduiding in een audit trail tabel om aan te geven op welke tabel deze row van toepassing is, is niet gratis.

2. pre-INSERT triggers / constraints?
Ik heb niet gekeken of dit mogelijk is, maar als dit mogelijk is kun je het werk tenminste weer uit handen geven.

'Keys' in MySQL zijn eigenlijk een beetje rare dingen, want een samenraapsel van een constraint + een index. Een foreign key is in feite de pre-insert constraint die je wilt.

[Confusion]

EfBe schreef op zaterdag 25 oktober 2008 @ 11:11:
Het recyclen van identity values is een doodzonde, het ondergraaft nl. ACID: keys die tijdens een transaction zijn uitgegeven maar niet worden gebruikt ivm het terugrollen van de transaction mogen nooit worden her-uitgegeven.

"Nooit" is een beetje te strikte eis. Een RDBMS zal bijvoorbeeld na het uitdelen van 2^32 ID's vaak gewoon weer bij 0 beginnen. Geen enkel systeem is ACID compliant voor eeuwig en de vraag is wat de vereiste praktische bovengrens is.

In de database van de topicstarter bestaat al geen consistentie, doordat er geen door de database gehandhaafde afhankelijkheid bestaat tussen zijn audit trail en de tabellen waar het trail op slaat. Dit gedrag van MySql is alleen een probleem omdat die consistentie al niet bestaat.

EfBe schreef op zondag 26 oktober 2008 @ 14:49:
MySql's transactional systeem is gewoon niet correct, het kan niet ACID garanderen in 100% van de gevallen, en dus is er voor jou niet iets te doen als 'meer moeite', er is gewoon geen mogelijkheid het beter te doen: jij bouwt een query, de DB voert hem uit en garandeert dat binnen de SQL scope de query wordt uitgevoerd volgens ACID principes. MySql is daar niet voor 100% toe in staat.

Dit gedrag treedt alleen op in geval van een herstart van de server, als er nog ergens een referentie rondzwerft naar een ID, dat inmiddels uit de database is verwijderd. Als je zorgt dat dat laatste niet het geval is (en je zal daar in je code altijd los voor moeten zorgen), dan garandeert de database gewoon ACIDity.

cariolive23 schreef op zondag 26 oktober 2008 @ 17:02:
Denk eens aan de ravage die je kunt aanrichten wanneer je runtime even de mode van MySQL aanpast...

Een ravage aanrichten door runtime fundamentele instellingen te wijzigen lukt me bij Postgres, DB2 en pakweg JavaDb ook wel hoor.

En heb je deze punten wel eens doorgenomen? Lees en huiver: yapf.net

Leuk wensenlijstje, een aantal vervelende ontbrekende features, maar niets ernstigs. Het lijstje is moeilijk serieus te nemen met een lachwekkend punt als:

4.2 FLOAT is bij benadering correct (alle versies)

No shit sherlock. Float is geen infinite-precision decimal.

5.24 Geen BOOL/BIT type

Heeft DB2 ook niet. Is ook waardeloos? Hier heeft gewoon iemand zijn favoriete systeem met MySql lopen vergelijken en punten gemaakt van alle ontbrekende features en een halve handvol echt twijfelachtige dingen.

MySQL is van A tot Z onbetrouwbaar,

Vooroordelen zonder praktijkervaring en voldoende kennis FTW.

[ Voor 58% gewijzigd door Confusion op 26-10-2008 17:49 ]

[curry684]

cariolive23 schreef op zondag 26 oktober 2008 @ 17:02:
[...]

MySQL is van A tot Z onbetrouwbaar, zeker wanneer er meer dan 1 persoon iets meer mag dan uitsluitend SELECT-queries uitvoeren. Hou een ander voor de gek, maar wees zelf heel voorzichtig met het gebruik van deze database. Je weet vooraf dat je hier lelijk je vingers aan kunt branden. En jouw klanten kunnen dit ook weten. Zeker wanneer er later wat fout gaat, ze weten je te vinden, op jouw advies is men hier in gestonken.

In multi-user concurrent situaties gaat iedere DB uiteindelijk op z'n muil, ook SQL Server staat default op een isolation level waarin phantom rows e.d. kunnen voor komen, evenals Oracle. Het alternatief is deadlocks bij iedere scheet en dat wil je net zo hard niet.

Ik zal niet snel tegenspreken dat MySQL een kutproduct is als ACID een belangrijk puntje op je wensenlijst is, maar je overdrijft wel een beetje.

[ Voor 5% gewijzigd door curry684 op 26-10-2008 18:01 ]

[cariolive23]

Ik ben vrij goed bekend met MySQL en PostgreSQL (zo'n 10 jaar ervaring), vooroordelen en gebrek aan praktijkervaring zijn mij dan ook vreemd.

Dat je runtime kunt aangeven of een query een wiskundig correct resultaat vereist of zelf maar wat onzin moet gaan verzinnen, dat slaat echt alles. Dat een database bij een datum 2007-02-30 (wat echt geen geldige datum is) dit niet afkeurt, slaat nergens op. En zo kun je nog wel even verder gaan. Optimaliseren van tabellen wanneer er onvoldoende schijfruimte is, leidt tot de vernietiging van de tabel. Geen enkele waarschuwing vooraf, gewoon een verminkte database.

Wat dacht je van het mixen van verschillende engines? Geen waarschuwing dat je geen transaction kunt toepassen op een MyISAM-tabel, gewoon een corrupte tabel rijker wanneer je de transaction met een rollback ongedaan probeert te maken.

Je hebt volkomen gelijk wanneer je stelt dat niet alle punten in genoemde lijst even ernstig zijn, maar er blijven er genoeg over die de data integriteit ernstig bedreigen.

Een database als PostgreSQL zal nooit zomaar de data verminken, dat doet MySQL wél:
1) standaard configuratie: Een te grote lap tekst opslaan in een VARCHAR(10), je raakt zonder waarschuwing een deel van je data kwijt
2) goed geconfigureerde database: Een te grote lap tekst opslaan in een VARCHAR(10), lukt niet, foutmelding. Zoals het hoort, je doet iets wat niet kan.
3) goed geconfigureerde database maar nu runtime weer een foute mode selecteren: Een te grote lap tekst opslaan in een VARCHAR(10), je raakt zonder waarschuwing weer een deel van je data kwijt. Achteraf is niet meer vast te stellen wat de oorspronkelijke input was, tenzij je de query-logs wilt gaan doorspitten.

Dit soort onzin is in PostgreSQL (en andere databases) onmogelijk, een VARCHAR(10) kan hooguit 10 karakters bevatten en daarmee klaar. Probeer je er meer in te zetten, krijg je altijd een foutmelding, de database zal nooit een deel van jouw data weggooien. Soortgelijke grappen kent MySQL ook met andere datatypes, bv. de reeds genoemde datums of enums. En omdat jij niet weet of iemand met de runtime instellingen heeft lopen klooien, weet jij niet of de data in je tabel ook die data is die moest worden opgeslagen. Is 2008-03-01 écht 1 maart of eigenlijk een foute 30 februari? Is 'abcdefghij' nu écht de string die je wilde opslaan of ben je een deel van de data kwijt? Wie zal het zeggen...

Dit soort grappen vallen bij mij niet in de categorie 'betrouwbaar'. Dat je met optimaliseren het risico loopt om de boel te vernietigen, geen idee hoe je dat wilt noemen, maar betrouwbaar zal het zeker niet zijn. Hopelijk heb je goede backups bij de hand!

[curry684]

Confusion schreef op zondag 26 oktober 2008 @ 17:34:
[...]

"Nooit" is een beetje te strikte eis. Een RDBMS zal bijvoorbeeld na het uitdelen van 2^32 ID's vaak gewoon weer bij 0 beginnen. Geen enkel systeem is ACID compliant voor eeuwig en de vraag is wat de vereiste praktische bovengrens is.

SQL Server geeft zoals het hoort gewoon een conversion error als de volgende identity niet meer in het gewenste integer type past. Die je overigens tot BIGINT kunt upgraden voor 9223372036854775807 rows.

cariolive23 schreef op zondag 26 oktober 2008 @ 18:25:
[...]

Dit soort grappen vallen bij mij niet in de categorie 'betrouwbaar'. Dat je met optimaliseren het risico loopt om de boel te vernietigen, geen idee hoe je dat wilt noemen, maar betrouwbaar zal het zeker niet zijn. Hopelijk heb je goede backups bij de hand!

You're barking up the wrong tree. Er zullen maar weinig mensen hier ontkennen dat MySQL brak is, ridicuul slechte ACID ondersteuning heeft en een belachelijke keuze is om bedrijfsvitale data van multinationals in op te slaan. Tis echter wel een retesnelle makkelijk te beheren database - hoofdzakelijk omdat het 90% van de integrity en consistency checks overslaat die andere databases wel doen. En dat maakt het in veel gevallen gewoon een moeiteloos verdedigbare keuze - we sleutelen niet allemaal aan de databases van banken en beurshandelaars op dagelijkse basis, en in veruit de meeste business cases is het alleszins acceptabel dat er op een dag een keer wat mis kan gaan of er zelfs een backupje van een paar uur oud terugmoeten eens in de 5 jaar. Zie wat dat betreft ook wat ik eerder zei over default isolation levels in Oracle en MSSQL: ook die databases kunnen verkeerde data teruggeven, en als je die weer in een insert gebruikt heb je ook een corrupted DB in de ruimste zin des woords.

True ACID bestaat sowieso alleen in een droomwereld zonder concurrency en zonder deadlocks.

[Gomez12]

cariolive23 schreef op zondag 26 oktober 2008 @ 18:25:
Je hebt volkomen gelijk wanneer je stelt dat niet alle punten in genoemde lijst even ernstig zijn, maar er blijven er genoeg over die de data integriteit ernstig bedreigen.

Een database als PostgreSQL zal nooit zomaar de data verminken, dat doet MySQL wél:
1) standaard configuratie: Een te grote lap tekst opslaan in een VARCHAR(10), je raakt zonder waarschuwing een deel van je data kwijt
2) goed geconfigureerde database: Een te grote lap tekst opslaan in een VARCHAR(10), lukt niet, foutmelding. Zoals het hoort, je doet iets wat niet kan.
3) goed geconfigureerde database maar nu runtime weer een foute mode selecteren: Een te grote lap tekst opslaan in een VARCHAR(10), je raakt zonder waarschuwing weer een deel van je data kwijt. Achteraf is niet meer vast te stellen wat de oorspronkelijke input was, tenzij je de query-logs wilt gaan doorspitten.

Punt 1 heb je gelijk in, maar dat is te ondervangen door punt 2.
Punt 3 heb je gewoon pertinent ongelijk in. als jouw users zelf een mode kunnen selecteren dan heb je al een gigantische denkfout zelf gemaakt.
Als je standaard config selecteert kun je nog zeggen dat het de db zijn fout is, als je zelf je db goed configureert kan je zeggen dat het de user zijn fout is. Maar als je de user laat kiezen dan is het altijd en immer een prog-fout... Als jij de keuze mogelijkheid wil bieden moet jij ook alle fouten opvangen.

Dit soort onzin is in PostgreSQL (en andere databases) onmogelijk, een VARCHAR(10) kan hooguit 10 karakters bevatten en daarmee klaar. Probeer je er meer in te zetten, krijg je altijd een foutmelding, de database zal nooit een deel van jouw data weggooien.

Vergelijk foute mode selecteren eens met error-reporting uitzetten en je dbase raakt netzo goed invalid...

En omdat jij niet weet of iemand met de runtime instellingen heeft lopen klooien, weet jij niet of de data in je tabel ook die data is die moest worden opgeslagen. Is 2008-03-01 écht 1 maart of eigenlijk een foute 30 februari? Is 'abcdefghij' nu écht de string die je wilde opslaan of ben je een deel van de data kwijt? Wie zal het zeggen...

Als jij niet kan garanderen dat niemand met de runtime instellingen gaat lopen klooien moet je gewoon geen dbase gebruiken.

[quoie]
Dit soort grappen vallen bij mij niet in de categorie 'betrouwbaar'.
[/quote]
Nee, bij mij vallen die onder serverbeheer. Kan jij geen betrouwbare dbase server garanderen, dan maakt de dbase zelf weinig uit. Kan jij geen betrouwbare server toegang garanderen dan boeit het onderliggende dbase systeem totaal niet meer.

[NMe]

EfBe schreef op zondag 26 oktober 2008 @ 14:49:
[...]

Hoe bedoel je, 'meer moeite' ? MySql's transactional systeem is gewoon niet correct, het kan niet ACID garanderen in 100% van de gevallen, en dus is er voor jou niet iets te doen als 'meer moeite', er is gewoon geen mogelijkheid het beter te doen: jij bouwt een query, de DB voert hem uit en garandeert dat binnen de SQL scope de query wordt uitgevoerd volgens ACID principes. MySql is daar niet voor 100% toe in staat.

Als ik mijn database correcte data aanlever hoeft die het niet te checken. Dat betekent dat je wat meer logica in je code moet stoppen, wat ook is wat ik bedoel met "meer moeite". MySQL valt dus niet af als optie om je data op te slaan, je moet alleen wat meer op je tellen passen.

[cariolive23]

Gomez12 schreef op zondag 26 oktober 2008 @ 18:51:
[...]Punt 3 heb je gewoon pertinent ongelijk in. als jouw users zelf een mode kunnen selecteren dan heb je al een gigantische denkfout zelf gemaakt.
Als je standaard config selecteert kun je nog zeggen dat het de db zijn fout is, als je zelf je db goed configureert kan je zeggen dat het de user zijn fout is. Maar als je de user laat kiezen dan is het altijd en immer een prog-fout... Als jij de keuze mogelijkheid wil bieden moet jij ook alle fouten opvangen.

Het zijn niet de gebruikers van systemen, maar de programmeurs die dit doen. Mochten ze de kans krijgen, dit mag écht niet voorkomen. Dit soort grappen de kop indrukken, heeft alleen wel tot gevolg dat veel kant&klare systemen die gebruik maken van MySQL, niet meer werken. Die kunnen niet uit de voeten met correcte SQL-instellingen.

[...]

Vergelijk foute mode selecteren eens met error-reporting uitzetten en je dbase raakt netzo goed invalid...

Nee, dan loopt de applicatie in de soep, niet de database. De database keurt data keihard af, hoezeer jij ook je best doet om deze onzin in de database te zetten. Je kunt je ogen daarvoor sluiten, maar de data gaat er echt niet in. Je loopt dus geen corrupte database op.

[...]
Als jij niet kan garanderen dat niemand met de runtime instellingen gaat lopen klooien moet je gewoon geen dbase gebruiken.

Gokje: bij 95% van alle MySQL-database-servers is nog nooit een beheerder met enige kennis van zaken in de buurt geweest.

[quote]
Dit soort grappen vallen bij mij niet in de categorie 'betrouwbaar'.
[/quote]
Nee, bij mij vallen die onder serverbeheer. Kan jij geen betrouwbare dbase server garanderen, dan maakt de dbase zelf weinig uit. Kan jij geen betrouwbare server toegang garanderen dan boeit het onderliggende dbase systeem totaal niet meer.

Nogmaals, ik gok dat bij zo'n 95% van alle MySQL-database-servers nog nooit een beheerder met enige kennis van zaken in de buurt is geweest en dat deze servers dus gewoon de standaard configuratie gebruiken. En deze configuratie keurt vrijwel niets af, ook al is de SQL onmogelijk of de data gewoon fout. Wanneer ik dan een systeem implementeer wat niet uit de voeten kan met een fout geconfigueerde server, dan wordt ik er op aangekeken. Even de server correct configureren gaat ook niet, dan vliegen de bugs in andere systemen je ineens om de oren. En dat is alleen grappig wanneer het geen essentieele systemen zijn en wanneer er nog support op deze systemen zit. Dan heeft de leverancier nog even wat werk te doen.

Op een MySQL-database garandeer ik dan ook helemaal niets, er zijn teveel zaken waar ik geen invloed op heb.

[Janoz]

-NMe- schreef op zondag 26 oktober 2008 @ 19:04:
[...]

Als ik mijn database correcte data aanlever hoeft die het niet te checken. Dat betekent dat je wat meer logica in je code moet stoppen, wat ook is wat ik bedoel met "meer moeite". MySQL valt dus niet af als optie om je data op te slaan, je moet alleen wat meer op je tellen passen.

Het punt is dat de database hoofd verantwoordelijk is voor de integriteit van de data, niet je applicatie.

[pedorus]

Confusion schreef op zondag 26 oktober 2008 @ 17:34:
No shit sherlock. Float is geen infinite-precision decimal.

De grap is ook dat de inhoud daarvan niet klopt.

MySQL converteert FLOAT naar DOUBLE ergens in zijn berekeningen en dat gaat al sinds 2003 mis:

Met daaronder een voorbeeld waaruit blijkt dat floats gewoon floats blijven in de berekening, en juist niet tussendoor zijn omgezet naar double. Er wordt hooguit niet zinnig afgerond bij het weergeven. (Er ontbreekt in dat voorbeeld ook nog een ';')

Tijd voor een betere lijst met andere voorbeelden van AUTO_INCREMENT's betrouwbaarheid.

Janoz schreef op zondag 26 oktober 2008 @ 19:25:
Het punt is dat de database hoofd verantwoordelijk is voor de integriteit van de data, niet je applicatie.

In dit geval is die DBMS kreupel gemaakt door een datamodel te bedenken waarin de foreign keys het niet doen. Er is ook nog eens een oplossing. Je kan een hulptabel zou maken, iets als AuditableItems(ItemId). Alle IDs van de tabellen waarop je Audit Trails wil doen moeten dan een foreign key naar AuditableItems zijn. Als je dan een rij verwijderd, dan blijft de integriteit van de verwijzingen goed. Ik geef toe, het is meer werk... (En brak van MySQL.)

[NMe]

Janoz schreef op zondag 26 oktober 2008 @ 19:25:
[...]

Het punt is dat de database hoofd verantwoordelijk is voor de integriteit van de data, niet je applicatie.

Nee, het punt is dat iemand MySQL afwijst op grond van dit:

cariolive23 schreef op zondag 26 oktober 2008 @ 09:57:
Dan valt het gebruik van MySQL per definitie af, daar zitten zoveel "features" (lees: bugs) in dat jouw data altijd onbetrouwbaar is.

Mijn data is niet onbetrouwbaar, ook niet als ik MySQL gebruik.

[Confusion]

curry684 schreef op zondag 26 oktober 2008 @ 18:44:
SQL Server geeft zoals het hoort gewoon een conversion error als de volgende identity niet meer in het gewenste integer type past. Die je overigens tot BIGINT kunt upgraden voor 9223372036854775807 rows.

Het default gedrag in DB2 en, als ik het me goed herinner, in Postgresql is om gewoon door te nummeren vanaf 0; dat een database van het ene op het andere moment errors gaat gooien en stopt met functioneren is ook vrij ernstig. Ik denk dat dat in meer gevallen problemen oplevert dan opnieuw bij 0 beginnen.

Bigints gebruiken is een oplossing, maar id kolommen, inclusief indexen, in afmeting wilt verdubbelen is ook niet altijd wenselijk. Ik zou dat alleen doen als ik verwacht dat opnieuw bij 0 beginnen een probleem op zou kunnen leveren.

[curry684]

Als dat de intentie is zal een goede DBA tijdig de identity value handmatig resetten

[.oisyn]

pedorus schreef op zondag 26 oktober 2008 @ 19:56:
[...]

De grap is ook dat de inhoud daarvan niet klopt.

Met daaronder een voorbeeld waaruit blijkt dat floats gewoon floats blijven in de berekening, en juist niet tussendoor zijn omgezet naar double. Er wordt hooguit niet zinnig afgerond bij het weergeven. (Er ontbreekt in dat voorbeeld ook nog een ';')

No shit sherlock. Float is geen infinite-precision decimal.

Oftewel, precies wat Confusion al zei. Wat verwacht je nu eigenlijk? Dat als je een float converteert naar double, dat het op magische wijze ineens beter het originele getal benadert? Natuurlijk niet.

1629.35 als float is 1629.3499755859375
66.59 als float is 1629.3499755859375
Na conversie naar double zijn ze nog steeds exact die waarden. En als je die bij elkaar optelt dan is dat in mijn boekje nog altijd 1695.939971923828125, oftewel precies wat MySQL zegt met afronding van de laatste 3 digits.

Gek zeg, als je je data opslaat met minder precisie dan de invoer, en je converteert het dan naar meer precisie, dat je originele data dan niet terugkomt

(Overigens, als de berekening in floats werd gedaan dan was de uitkomst 1695.93994140625 geweest. Dit komt door het verschil in orde van grootte tussen de twee getallen, waardoor de laatste paar bits in de mantissa van het kleinste getal niet in het resultaat passen, wat bij double niet het geval is)

[ Voor 21% gewijzigd door .oisyn op 26-10-2008 22:36 ]

[curry684]

.oisyn schreef op zondag 26 oktober 2008 @ 22:03:
Gek zeg, als je je data opslaat met minder precisie dan de invoer, en je converteert het dan naar meer precisie, dat je originele data dan niet terugkomt

Dat heeft me nou aan JPEG en MPEG ook al altijd gefascineerd!

[pedorus]

(offtopic over floats sommeren in MySQL)

.oisyn schreef op zondag 26 oktober 2008 @ 22:03:
Oftewel, precies wat Confusion al zei. Wat verwacht je nu eigenlijk? Dat als je een float converteert naar double, dat het op magische wijze ineens beter het originele getal benadert? Natuurlijk niet.

Maak je geen zorgen, ik weet hoe floats intern werken

1629.35 als float is 1629.3499755859375
66.59 als float is 1629.3499755859375
Na conversie naar double zijn ze nog steeds exact die waarden. En als je die bij elkaar optelt dan is dat in mijn boekje nog altijd 1695.939971923828125, oftewel precies wat MySQL zegt met afronding van de laatste 3 digits.

Mm, ze zijn dus toch omgezet naar doubles en opgeteld. Ik heb hier 4 manieren om die 2 floats op te tellen en het resultaat op te slaan:

Java:

class FloatSum {
    public static void main(String[] argv) {
        float a = 1629.35f;
        float b = 66.59f;
        float sum1 = a + b;
        float sum2 = (float) ((double) a + (double) b);
        double sum3 = a + b;
        double sum4 = (double) a + (double) b;
        System.out.println(sum1);
        System.out.println(sum2);
        System.out.println(sum3);
        System.out.println(sum4);
    }
}

output:

1695.94
1695.94
1695.93994140625
1695.9399719238281

MySQL geeft antwoord 4 met weglating van de laatste digits (terwijl ik 3 dacht). En ik moet dus toch de auteur van Yapf.net gelijk geven: dit is een fout idee, er wordt niet met floats gerekend maar ze worden gelijk omgezet naar doubles. Er wordt dus vanuit gegaan dat ze een grotere nauwkeurigheid hebben, dan dat ze daadwerkelijk hebben. Als ik integers optel, worden ze ook niet eerst omgezet naar doubles. Waarom zou dat met floats dan wel moeten gebeuren?

.oisyn schreef op zondag 26 oktober 2008 @ 23:06:
Ik ben het echt totaal niet met je eens dat rekenen met een grotere precisie dan de invoer een slecht iets is.

Ik ben van mening dat methode 1 (alleen met floats werken) het beste antwoord is. Het probleem is dat als we op dezelfde manier straks met quad precision gaan werken, alle berekeningen met doubles opeens ook andere resultaten krijgen. (Overigens kan ik met methode 4 ook prima leven hoor. Eigenlijk werk ik gewoon nooit meer met floats, alleen maar met doubles of decimals... )
Waarom moeten float-gebruikers zo geconfronteerd worden met doubles? Er mist gewoon een stukje implementatie in MySQL. Beter voorbeeld:

mysql> select * from test where abs(waarde-66.59)<.01;
+--------+
| waarde |
+--------+
|  66.59 |
+--------+
1 row in set (0.00 sec)

mysql> select min(waarde) from test;
+-----------------+
| min(waarde)     |
+-----------------+
| 66.589996337891 |
+-----------------+
1 row in set (0.00 sec)

1695.93994140625 als uitkomst is in principe gewoon fout.

Als double wel, als float niet. Ik dacht eerst dat het alleen bij het weergeven fout ging.

Je eerste 3 outputs zijn dan ook exact identiek, alleen gooit printf() roet in het eten door floats met minder digits af te drukken.

Als het meer dan 2 getallen worden, en de resultaten tussendoor worden opgeslagen, dan gaat dit niet meer op.

[ Voor 31% gewijzigd door pedorus op 27-10-2008 00:22 ]

[.oisyn]

Volgens mij heeft de auteur het erover dat er 1695.94 uit zou moeten komen. Maar dat krijg je sowieso niet, of je nou met floats werkt of met doubles (1695.94 is nou eenmaal niet representeerbaar door een double).

Ik ben het echt totaal niet met je eens dat rekenen met een grotere precisie dan de invoer een slecht iets is. Je data representeert een getal, waar je uiteindelijk een berekening mee wilt doen. Daarbij wil je die berekening doorgaans zo exact mogelijk, en dus niet gelimiteerd aan het type van de kolom. Als je gaat rekenen met waarden uit een TINYINT kolom dan wordt er óók niet gewrapped. Je opmerking "Als ik integers optel, worden ze ook niet eerst omgezet naar doubles" klopt dan ook niet. Nee, ze worden idd niet omgezet naar doubles, maar wel naar grotere integers zodat het resultaat correct is. Niets mis mee imho. 1695.93994140625 als uitkomst is in principe gewoon fout. (En fouter dan 1695.94, want dat is dan iig nog een correcte afronding)

Overigens doet je processor doorgaans exact hetzelfde. Die kent intern geen verschil tussen floats, doubles en long doubles, en rekent altijd met dezelfde precisie (die overigens instelbaar is). Je eerste 3 outputs zijn dan ook exact identiek, alleen gooit printf() roet in het eten door floats met minder digits af te drukken.

[ Voor 16% gewijzigd door .oisyn op 26-10-2008 23:26 ]

[.oisyn]

Antwoorden op posts onder je erbij editen is irritant

pedorus schreef op zondag 26 oktober 2008 @ 22:45:
Ik ben van mening dat methode 1 (alleen met floats werken) het beste antwoord is.

Dus je vindt ook dat als je in een tabel een TINYINT met waarde 120 hebt en je telt daar 10 bij op dat je dan -126 moet krijgen?

[Confusion]

cariolive23 schreef op zondag 26 oktober 2008 @ 18:25:
Wat dacht je van het mixen van verschillende engines? Geen waarschuwing dat je geen transaction kunt toepassen op een MyISAM-tabel, gewoon een corrupte tabel rijker wanneer je de transaction met een rollback ongedaan probeert te maken.

Ook hier geldt: het zou aardig zijn als ze daar een warning op gaven, maar als je transactions nodig hebt, dan weet je dat je InnoDb tables nodig hebt. Fatsoenlijke creation queries specificeren dat er bij. Tijdens development levert het heel even een "WTF?" op, maar als dit in de productieomgeving voorkomt, dan heb je grotere problemen dan de tekortkomingen van MySql.

Dit soort grappen vallen bij mij niet in de categorie 'betrouwbaar'.

Het valt bij mij in de categorie: met MySql moet je nog beter dan met andere systemen weten wat je doet. Dit soort bezwaren zijn allemaal een beetje wat je in wiskundige voorbeelden 'pathetische randgevallen' zou noemen. Bijvoorbeeld inputvalidatie hoort in je applicatie plaats te vinden. Je valideert geen datum door te proberen hem in de database te stoppen; dat is veel te veel overhead voor zo'n simpele operatie en bovendien in je code onnavolgbaar. Dat je een deel van een lap tekst kwijtraakt omdat je hem in een varchar(10) probeert te stoppen, betekent dat er iets grandioos mis is. In een fatsoenlijk geteste applicatie zou MySql nooit runtime zo'n foutmelding terug hoeven te geven. Het is alleen tijdens development wel erg handig als MySql roept: euhmm, dit gaat niet. Zeer wenselijk, niet noodzakelijk.

[Voutloos]

Pfff, lees 5 minuten over een van de opvallendste features van mysql, te weten de keuze in storage engines, en je kan door de helft van dat lijstje een streep zetten met 'RTFM about Storage Engines' erachter. Iedereen die serieus met mysql bezig is, kan in ieder geval een gefundeerde keuze tussen de belangrijkste 2 engines, myISAM en innoDB, maken. De prutsers die dat niet kunnen, snappen uberhaupt niet (en hoeven dat misschien zelfs niet) wat een transaction is.
Voor de mensen die de mysql manual wel gelezen hebben blijven er maar een paar punten over in dat lijstje, en die hebben vooral te maken met wat default instellingen die helaas maar tot in de lengte van dagen op legacy gedrag blijven staan.

[EfBe]

-NMe- schreef op zondag 26 oktober 2008 @ 19:04:
[...]

Als ik mijn database correcte data aanlever hoeft die het niet te checken. Dat betekent dat je wat meer logica in je code moet stoppen, wat ook is wat ik bedoel met "meer moeite". MySQL valt dus niet af als optie om je data op te slaan, je moet alleen wat meer op je tellen passen.

Het punt is dat jij niet weet welke gaten er allemaal in MySql zitten. Als jij een query runt, dan verwacht jij een bepaald resultaat of resultset. Als die niet strookt met wat je verwacht, is of je query fout of de database brak. Aangezien queries set operaties zijn en dus bewijsbaar of ze correct danwel niet correct zijn, hoef je niet meer moeite te doen om correcte resultaten te krijgen (correct in de zin van: ik vraag X, ik krijg X). Als het resultaat dan niet is wat is verwacht, is de database dus brak. dan moet je niet concluderen: "Doe meer moeite", maar dan moet je concluderen: brakke database, we gebruiken een ander.

[Janoz]

pedorus schreef op zondag 26 oktober 2008 @ 19:56:
In dit geval is die DBMS kreupel gemaakt door een datamodel te bedenken waarin de foreign keys het niet doen. Er is ook nog eens een oplossing. Je kan een hulptabel zou maken, iets als AuditableItems(ItemId). Alle IDs van de tabellen waarop je Audit Trails wil doen moeten dan een foreign key naar AuditableItems zijn. Als je dan een rij verwijderd, dan blijft de integriteit van de verwijzingen goed. Ik geef toe, het is meer werk... (En brak van MySQL.)

Mysql dwingt niet altijd foreign keys af, maar ik heb het niet alleen over foreign keys. Kwalijker zijn bijvoorbeeld het, zonder enige indicatie, afknippen van strings of het zomaar toevoegen van null waarden in een not null kolom.

Uiteraard moet je dat allemaal controleren voordat je het de DB instuurt, maar zeker met zeer grote rpojecten waar meerdere mensen aan werken en waar niet uitgebreid (geautomatiseerd) getest wordt kan er op een gegeven moment wel eens een aanpassing maar voor de helft doorgevoerd worden. Murphy zegt dat dit ooit ergens een keer zal gaan gebeuren. In dat geval kun je dus maar beter niet enkel vertrouwen op de kundigheid van je eigen programmeurs en testers.

-NMe- schreef op zondag 26 oktober 2008 @ 20:00:
Mijn data is niet onbetrouwbaar, ook niet als ik MySQL gebruik.

Dat is meer een redenatie van programeurs eigen arrogantie . Dergelijke opmerkingen vind je niet terug in een audit rapportage. Ik kan ook wel data zeer betrouwbaar en met behoud van integriteit opslaan met een flat file systeem. Dan ben ik gewoon zelf alle controle aan het bouwen. Het punt is echter dat er vaak omgevingen zijn waarin je je correctheid drie dubbel geborgd wil hebben. In die omgevingen is de aangehaalde reden een zeer valide reden om niet voor MySQL te kiezen.

[JayVee]

Confusion schreef op zondag 26 oktober 2008 @ 17:15:
[...]
Bepaald elegant vind ik die oplossing niet. Ik zou voor iedere tabel een eigen schaduwtabel met het betreffende audit trail bijhouden. Tabellen zijn gratis; een aanduiding in een audit trail tabel om aan te geven op welke tabel deze row van toepassing is, is niet gratis.

Het is niet de meest elegante oplossing nee. Maar hij heeft zo z'n voordelen. Wij hebben nu al databases met +- 1000 tabellen. Als we aparte audit trail tabellen zouden gebruiken zou dit getal met 30% stijgen. Geen onoverkomelijk probleem, maar meer tabellen zijn gewoon lastiger te onderhouden (phpmyadmin en andere clients worden traag, mysqldump doet er langer over, "browsen" van en updates schrijven voor je db wordt steeds moeilijker), en in die zin alleen voor de server "gratis". Daarnaast kun je geen aggregate functions gebruiken (tenzij je een view maakt).

-NMe- schreef op zaterdag 25 oktober 2008 @ 16:09:
[...]
Dan nog is het best practice om geen dingen te verwijderen uit je database zonder verwijzingen ernaar ook te verwijderen, en in veel gevallen is het dus ook goed om gewoon niets te verwijderen en alleen te taggen. We zouden dus deze discussie alleen in een andere vorm hebben gevoerd, maar de voedingsbodem voor die discussie is er toch.

True, ik denk ook dat hier de oplossing voor mij ligt.

Verwijderd schreef op zaterdag 25 oktober 2008 @ 15:52:
Kun je niet naar PostgreSQL of Firebird overgaan?

Meh. Wij hosten onze applicatie bij een shared hosting club waar wij erg tevreden mee zijn. Er zullen vast ook wel hosts bestaan die PostgreSQL aanbieden (onze host heeft daar ook al eens over nagedacht), maar MySQL voldoet voor 99,5%, dus wij zullen niet gauw overstappen naar een andere database en host.

curry684 schreef op zondag 26 oktober 2008 @ 18:44:
Tis echter wel een retesnelle makkelijk te beheren database - hoofdzakelijk omdat het 90% van de integrity en consistency checks overslaat die andere databases wel doen. En dat maakt het in veel gevallen gewoon een moeiteloos verdedigbare keuze - we sleutelen niet allemaal aan de databases van banken en beurshandelaars op dagelijkse basis, en in veruit de meeste business cases is het alleszins acceptabel dat er op een dag een keer wat mis kan gaan of er zelfs een backupje van een paar uur oud terugmoeten eens in de 5 jaar.

Nuff said hierover.

Ik zou dit topic ook graag weer ontopic krijgen! Discussie over de precisie van float columns en wel of niet ondersteuning van ACID onder bepaalde omstandigheden, vergelijkingen met andere databases en zo helpen mij (en mensen met hetzelfde probleem) niet verder. Dit topic gaat over hoe omgaan met relaties naar AUTO_INCREMENT columns in InnoDB databases.
[/wannabe-mod-gezeur]

Er zijn gelukkig waardevolle punten genoemd (waarvoor ), en daar ga ik mee verder. Ik denk dat het verwijderen van tuples gepaard moet gaan met het verwijderen (op NULL zetten) van relaties naar deze tuples. Dit is een prima oplossing voor het audit trail probleem. Ik zal de rest van onze database eens doorspitten om te kijken of deze oplossing overal handig is of dat er andere situaties zijn waar een andere aanpak beter zou helpen.

[pedorus]

JayVee schreef op maandag 27 oktober 2008 @ 10:33:
Het is niet de meest elegante oplossing nee. Maar hij heeft zo z'n voordelen. Wij hebben nu al databases met +- 1000 tabellen. Als we aparte audit trail tabellen zouden gebruiken zou dit getal met 30% stijgen. Geen onoverkomelijk probleem, maar meer tabellen zijn gewoon lastiger te onderhouden (phpmyadmin en andere clients worden traag, mysqldump doet er langer over, "browsen" van en updates schrijven voor je db wordt steeds moeilijker), en in die zin alleen voor de server "gratis". Daarnaast kun je geen aggregate functions gebruiken (tenzij je een view maakt).

Die 30% sla ik niet van achterover. mysqldump denk ik niet dat je het gaat merken. Updates schrijven wordt juist iets sneller lijkt mij. Het hangt dus een beetje van het gebruik af. Ik kan me enkel wel voorstellen dat als je de union van 300 tabellen vaker nodig hebt, dat er dan iets mis is...

Meh. Wij hosten onze applicatie bij een shared hosting club waar wij erg tevreden mee zijn. Er zullen vast ook wel hosts bestaan die PostgreSQL aanbieden (onze host heeft daar ook al eens over nagedacht), maar MySQL voldoet voor 99,5%, dus wij zullen niet gauw overstappen naar een andere database en host.

Het lijkt mij ook verspilde moeite.

.oisyn schreef op maandag 27 oktober 2008 @ 00:54:
Antwoorden op posts onder je erbij editen is irritant

offtopic:
Sorry, ik probeerde even een offtopic kick te voorkomen. Geleerd van een mod geloof ik.

Dus je vindt ook dat als je in een tabel een TINYINT met waarde 120 hebt en je telt daar 10 bij op dat je dan -126 moet krijgen?

offtopic:
256 * 256 * 256 * 256 is toch 0? Nope, under- of overflows zijn bijzonder, en moeten een cast of error veroorzaken. Ook delingen kunnen bijzonder zijn, het is net welke keuze je maakt. Het lijkt me dan ook het meest te vergelijken met het delen van 2 integers, MS doet bijvoorbeeld geen cast. Voor het optellen van floats is echt geen cast vereist. MySQL doet een (volgens mij) niet-gedocumenteerde cast, en dat lijkt mij dus niet echt ok (bij MS is dat bijvoorbeeld hier gedaan). Nu het eenmaal zo is zou ik zeggen laat het maar zo, maar een heel gelukkige keuze is het volgens mij niet. Ik verwacht ook niet dat hetzelfde bij doubles gaat gebeuren als quad precission veelgebruikt is. Het voorbeeld met MIN() op een float in MySQL is volgens mij pure luiheid van de MySQL-mensen, en gewoon een bug.

[.oisyn]

pedorus schreef op maandag 27 oktober 2008 @ 11:33:
Ook delingen kunnen bijzonder zijn, het is net welke keuze je maakt. Het lijkt me dan ook het meest te vergelijken met het delen van 2 integers, MS doet bijvoorbeeld geen cast.

MySQL overigens wel. En bovendien gebruikt MS een decimal als het niet meer in een int past, waarna een deling ineens wel een kommagetal kan opleveren .

(bij MS is dat bijvoorbeeld hier gedaan).

MS doet het dus ook, die returnt altijd floats, en SQLServer kent geen doubles. Floats zijn namelijk gewoon doubles, hoewel je ze wel als 32 bits floats op kunt slaan (dmv FLOAT(n) met n <= 24, of REAL wat een synoniem is voor FLOAT(24))

Het voorbeeld met MIN() op een float in MySQL is volgens mij pure luiheid van de MySQL-mensen, en gewoon een bug.

De bug zit 'm natuurlijk in de textuele uitvoer. Ik mag hopen dat als je een fatsoenlijke API gebruikt dat je gewoon de binaire data terugkrijgt, en geen rare textuele representaties van floats die al dan niet zijn afgerond.

[winkbrace]

Overigens heeft Oracle bijvoorbeeld geen auto-increment functie. Daar gebruiken we sequences. MySQL heeft die functie niet, maar zo'n functie schrijven is natuurlijk zeer eenvoudig.
auto-increment is m.i. typisch een functie die erg handig is voor de hobbyist - ik gebruik het indien mogelijk - maar voor serieuze applicaties wil je inderdaad wat meer zekerheid.

[EfBe]

BazzPsychoNut schreef op maandag 27 oktober 2008 @ 12:16:
Overigens heeft Oracle bijvoorbeeld geen auto-increment functie. Daar gebruiken we sequences. MySQL heeft die functie niet, maar zo'n functie schrijven is natuurlijk zeer eenvoudig.
auto-increment is m.i. typisch een functie die erg handig is voor de hobbyist - ik gebruik het indien mogelijk - maar voor serieuze applicaties wil je inderdaad wat meer zekerheid.

Absoluut onbetrouwbaar, je kunt nl. nog steeds meerdere threads met gelijke values hebben.

(edit) Ik heb het uiteraard over de homebrew functie die daar gepost wordt. Sequences middels tables is t.a.t. af te raden (tenzij snapshot isolation level mogelijk is in een transaction wat neer komt op een table lock)

[ Voor 14% gewijzigd door EfBe op 27-10-2008 14:44 ]

[bigbeng]

EfBe schreef op maandag 27 oktober 2008 @ 13:04:
[...]

Absoluut onbetrouwbaar, je kunt nl. nog steeds meerdere threads met gelijke values hebben.

Bij sequences in Oracle heb ik nog nooit gelijke keys op kunnen vragen met meerdere threads. Onder welke omstandigheden heb jij dit geconstateerd?

Edit: of ging je in op zelf de functie schrijven?

[ Voor 6% gewijzigd door bigbeng op 27-10-2008 13:10 ]

[Kettrick]

bigbeng schreef op maandag 27 oktober 2008 @ 13:09:
[...]

Bij sequences in Oracle heb ik nog nooit gelijke keys op kunnen vragen met meerdere threads. Onder welke omstandigheden heb jij dit geconstateerd?

Edit: of ging je in op zelf de functie schrijven?

De link werkt op basis van een fake table welke een sequence voor moet stellen, belangrijke footnote daarbij is :

the key here is that the sequence table is myisam, and so does not respect transactions. if you use a table that respects transactions, you will block when there are multiple transactions trying to get a sequence, whereas what you want is to just get a sequence.

Sequences vallen normaal buiten transacties en worden nooit dubbel uitgedeeld.

[EfBe]

bigbeng schreef op maandag 27 oktober 2008 @ 13:09:
[...]

Bij sequences in Oracle heb ik nog nooit gelijke keys op kunnen vragen met meerdere threads. Onder welke omstandigheden heb jij dit geconstateerd?

Edit: of ging je in op zelf de functie schrijven?

Op zelf je functie schrijven, sorry dat had er bij gemoeten. Sequences in oracle zijn betrouwbaar uiteraard, zij worden op system level uitgegeven.

[curry684]

BazzPsychoNut schreef op maandag 27 oktober 2008 @ 12:16:
Overigens heeft Oracle bijvoorbeeld geen auto-increment functie. Daar gebruiken we sequences. MySQL heeft die functie niet, maar zo'n functie schrijven is natuurlijk zeer eenvoudig.
auto-increment is m.i. typisch een functie die erg handig is voor de hobbyist - ik gebruik het indien mogelijk - maar voor serieuze applicaties wil je inderdaad wat meer zekerheid.

MSSQL noemt het dan ook met opzet 'identity' en geen 'auto-increment'. Identity zit in de metadata van de tabellen en wordt transactioneel correct toegepast.