ON DUPLICATE KEY UPDATE & Auto-increment
Ik krijg zo gaten in mijn auto-increment (op zich niet heel erg maar wel irritant) en het id loopt wel erg op na veel updates. Ook gebruik ik dit id in een url wat nu niet meer als favoriet kan worden opgeslagen. Niet wenselijk dus. Heeft iemand een idee hoe dit te omzeilen?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
23859
24046
24066
24296
24311
24411
24538
24819
24864
24954
24999
25003
25007
25011
25070
25091
25590
25615
25683
25695
25709
25719
25885
26174
26175
26176
26177
26178
26179
24046
24066
24296
24311
24411
24538
24819
24864
24954
24999
25003
25007
25011
25070
25091
25590
25615
25683
25695
25709
25719
25885
26174
26175
26176
26177
26178
26179
Mijn query is:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?php
$sql_insert_event = '
INSERT INTO
event
(
fb_event_id,
fb_venue_id,
fb_image_id,
user_id,
title,
description,
ticket_url,
startdate,
starttime,
create_date,
change_date,
published,
event_status
)
VALUES
(
"'.mysqli_real_escape_string($con, $eid).'",
"'.mysqli_real_escape_string($con, $fb_venue_id).'",
"'.mysqli_real_escape_string($con, $fb_image_id).'",
"'.mysqli_real_escape_string($con, 1).'",
"'.mysqli_real_escape_string($con, $event["name"]).'",
"'.mysqli_real_escape_string($con, $event["description"]).'",
"'.mysqli_real_escape_string($con, $event["ticket_uri"]).'",
"'.mysqli_real_escape_string($con, $start_date).'",
"'.mysqli_real_escape_string($con, $start_time).'",
"'.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
"'.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
"'.mysqli_real_escape_string($con, 1).'",
"'.mysqli_real_escape_string($con, 'n').'")
ON DUPLICATE KEY UPDATE
fb_event_id = "'.mysqli_real_escape_string($con, $eid).'",
fb_venue_id = "'.mysqli_real_escape_string($con, $fb_venue_id).'",
fb_image_id = "'.mysqli_real_escape_string($con, $fb_image_id).'",
user_id = "'.mysqli_real_escape_string($con, 1).'",
title = "'.mysqli_real_escape_string($con, $event["name"]).'",
description = "'.mysqli_real_escape_string($con, $event["description"]).'",
ticket_url = "'.mysqli_real_escape_string($con, $event["ticket_uri"]).'",
startdate = "'.mysqli_real_escape_string($con, $start_date).'",
starttime = "'.mysqli_real_escape_string($con, $start_time).'",
change_date = "'.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
published = "'.mysqli_real_escape_string($con, 1).'",
event_status = "'.mysqli_real_escape_string($con, 'u').'"
';
?>
$sql_insert_event = '
INSERT INTO
event
(
fb_event_id,
fb_venue_id,
fb_image_id,
user_id,
title,
description,
ticket_url,
startdate,
starttime,
create_date,
change_date,
published,
event_status
)
VALUES
(
"'.mysqli_real_escape_string($con, $eid).'",
"'.mysqli_real_escape_string($con, $fb_venue_id).'",
"'.mysqli_real_escape_string($con, $fb_image_id).'",
"'.mysqli_real_escape_string($con, 1).'",
"'.mysqli_real_escape_string($con, $event["name"]).'",
"'.mysqli_real_escape_string($con, $event["description"]).'",
"'.mysqli_real_escape_string($con, $event["ticket_uri"]).'",
"'.mysqli_real_escape_string($con, $start_date).'",
"'.mysqli_real_escape_string($con, $start_time).'",
"'.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
"'.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
"'.mysqli_real_escape_string($con, 1).'",
"'.mysqli_real_escape_string($con, 'n').'")
ON DUPLICATE KEY UPDATE
fb_event_id = "'.mysqli_real_escape_string($con, $eid).'",
fb_venue_id = "'.mysqli_real_escape_string($con, $fb_venue_id).'",
fb_image_id = "'.mysqli_real_escape_string($con, $fb_image_id).'",
user_id = "'.mysqli_real_escape_string($con, 1).'",
title = "'.mysqli_real_escape_string($con, $event["name"]).'",
description = "'.mysqli_real_escape_string($con, $event["description"]).'",
ticket_url = "'.mysqli_real_escape_string($con, $event["ticket_uri"]).'",
startdate = "'.mysqli_real_escape_string($con, $start_date).'",
starttime = "'.mysqli_real_escape_string($con, $start_time).'",
change_date = "'.mysqli_real_escape_string($con, date('Y-m-d H:i:s')).'",
published = "'.mysqli_real_escape_string($con, 1).'",
event_status = "'.mysqli_real_escape_string($con, 'u').'"
';
?>
Vreemd dat er grote gaten vallen. Zit niet een ander proces toevallig records te verwijderen? Het valt me in je reeks namelijk op dat de laatste getallen wél aangesloten zijn:
26174
26175
26176
26177
26178
26179
Ik mis trouwens een WHERE in de UPDATE. Wat wil je updaten?
Gewijzigd op 06/09/2014 10:19:48 door Ward van der Put
Hetzelfde geldt voor bulk inserts (zonder on duplicate key)
Er moet uiteraard wel iets zijn maar kan het nog niet vinden. Ik wil mijn hele script wel posten eventueel?
Toevoeging op 06/09/2014 10:22:55:
@Ger : Meen je dat nou? Dat klinkt toch niet logisch?
Toevoeging op 06/09/2014 10:25:03:
...Maar de tussenliggende AI's van mijn reeks zijn niet bekend in de database. Wat zou daarmee gebeurt kunnen zijn?
Toch wel:
Quote:
For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values without taking any lock, but only if there is no concurrent session already holding the table-level AUTO-INC lock (because that other statement will be allocating auto-increment values one-by-one as it proceeds)
Omdat niet bekend is of er een rij geinsert of geupdate moet worden, wordt toch de AI waarde met één opgehoogd, zodat een 'gelijktijdige' insert geen duplicates kan opleveren.
>> Maar de tussenliggende AI's van mijn reeks zijn niet bekend in de database. Wat zou daarmee gebeurt kunnen zijn
De AI waarde van een tabel is iets wat op server niveau wordt bijgehouden, dus er wordt niet naar een tabel gekeken wat de max waarde is.
Oke dus, bij het invoegen van een record wat al bestaat en waardoor de INSERT dus faalt wordt door MYSQL de Auto-increment WEL opgehoogd.Bij een volgende insert kunnen er dus AI's missen.
Bij een update vind ik het toch niet netjes dat dit wordt gedaan. Is de workaround hiervoor om dan toch weer met meerdere queries te werken?
Het lijkt mij logisch (maar ik kan zo snel niet vinden of dat ook daadwerkelijk zo is) dat MySQL eerst de indices checkt of de waardes die je wilt invoeren conflicteren met een key. Ik zou het onlogisch vinden als hij rücksichtlos een record gaat toevoegen en pas achteraf constateert dat het helemaal niet kan.
Ik heb nog wel een tip voor je query: in het 'on duplicate'-gedeelte zit je nu voor heel veel velden dezelfde waardes in te voeren als in het insert-gedeelte van de query. Dat kun je veel efficiënter oplossen met de values-functie:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
ON DUPLICATE KEY UPDATE
fb_event_id = values(fb_event_id),
fb_venue_id = values(fb_venue_id),
fb_image_id = values(fb_image_id),
[...]
change_date = NOW(),
published = 1,
event_status = 'u'"
fb_event_id = values(fb_event_id),
fb_venue_id = values(fb_venue_id),
fb_image_id = values(fb_image_id),
[...]
change_date = NOW(),
published = 1,
event_status = 'u'"
Verder valt het me op dat je (te) vaak de real_escape_string-functie gebruikt. In principe is dat alleen maar nodig als er tekens in je string staan waar de query zich in kan verslikken (ruwweg: newlines, backslashes en quotes) of als je niet zeker weet of die tekens erin staan (bijvoorbeeld bij user-input). Een met date() gegenereerde datum of een integer hoef je dus niet te escapen.
Sowieso is het efficiënter om de MySQL-functie NOW() te gebruiken als je het huidige tijdstip wilt invoeren in de query.
Wat is in je tabel het datatype van velden als fb_event_id en published? Het lijkt me aannemelijk dat dit INT is. Je behandelt de velden in je query echter als strings. Ook dat is niet aan te bevelen.
Dat het zwaarder is, ben ik meteen met je eens. Ik vraag me echter af of het het probleem oplost. Ik vermoed namelijk dat het niet in deze query zit, maar heel ergens anders.
Het zit hem in ON DUPLICATE KEY update, MySQL weet van te voren niet of ie kan inserten of moet updaten.
Dit geldt overigens alleen voor InnoDb tabellen, en het is instelbaar:
Code (php)
1
SET innodb_autoinc_lock_mode = 0
Dat is geen onbelangrijke toevoeging... ;-)
Klopt, speciaal voor jou ...... ;-)
Overigens:
Code (php)
1
2
2
ON DUPLICATE KEY UPDATE
change_date = NOW()
change_date = NOW()
Dan kan je nooit weten over er daadwerkelijk iets veranderd is.
In dit geval kan je aan de change_date beter een TIMESTAMP datatype meegeven, of met MySQL 5.6.5+ een DATETIME met DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
En dan de kolom uit de query weglaten.
Gewijzigd op 06/09/2014 18:05:09 door Ger van Steenderen
Als ik het goed begrijp:
In een InnoDb tabel geldt, wanneer er gebruik wordt gemaakt van ON DUPLICATE KEY UPDATE, dat:
1) Gefaalde inserts toch ervoor zorgen dat de AI wordt verhoogd.
2) Updates ook ervoor zorgen dat de AI wordt verhoogd (..dit heb ik nog niet kunnen testen)
Tenzij je SET innodb_autoinc_lock_mode = 0 uitvoert? Dan blijft de AI opeenvolgend?
Garantie tot aan de deur ......
Ik gebruik voor mijn site overal de AI om een record uniek te identificeren. Deze moet niet onderhevig zijn aan veranderingen aangezien ik deze ook gebruik in o.a. urls. Is het misschien beter om bij een insert de AI te hashen en op te slaan in een aparte kolom en dat aan te houden om een record uniek te maken of zeg ik nu iets heel stoms?
Dit zou m.i. bovenstaand probleem niet meer actueel maken.
> Deze moet niet onderhevig zijn aan veranderingen aangezien ik deze ook gebruik in o.a. urls.
Eenmaal uitgegeven wordt het id niet meer veranderd, tenzij je dat zelf doet. Ik zie het probleem dus niet, eigenlijk...