[php/mysql] unieke ID voor files bij meerdere servers/syncen

[js303]

momenteel ontwikkelen we een browser gestuurd mediamanager (plaatjes, pdf's, video bestanden etc) systeem met php en mysql, waarin veel bestanden geplaatst worden, waarbij elk bestand een unieke ID krijgt. deze gaan de invoerders / opvrages van het systeem gebruiken in hun communicatie (mag ik het beeld met ID x op hoge resolutie bestellen).

het systeem moet gaan draaien op verschillende servers op verschillende locaties die periodiek met elkaar synchroniseren. ik ben aan het uitzoeken wat de beste manier is om te zorgen dat elk bestand zijn eigen ID krijgt.

mogelijkheid 1
ik denk eraan om elke server waarop het systeem draait een eigen ID te geven. vervolgens wordt een nieuw bestand ingevoerd, daar rolt (uit mysql) een file-id uit en voeg ik samen met de server ID.

voorbeeld: LH.000.588
de eerste letters staan voor de server (localhost in voorbeeld), de (geformatteerde) cijfers voor het autonummerings-ID.

iemand anders voert op een andere server een ander bestand in: CM.000.588
een andere server-id maar hetzelfde autonummerings-ID.
dit lijkt me technisch 'lelijk':
het autonummeringsveld levert een nieuwe ID op bij een INSERT in de DB, je vervolgens de server_ID, bijv. "LH" moet toevoegen en wegschrijven in een apart veld als bijv. "LH.000.588", waarna je bij de synchronisatie met een andere server onderling inserts gaat maken en de autonummerings-velden niet meer gelijklopen met de samengestelde ID (insert file X van server A met ID "LH.000.588" op server B, waar vervolgens een nieuw autonummerings-ID uitrolt die niet matcht met "LH.000.588").

mogelijkheid 2
1 centrale database die unieke nummers bijhoudt (foreign keys?), waarbij elke server die centrale database moet raadplegen om een nieuwe ID te krijgen. zo krijg je dus niet situaties als LH.000.588 en CM.000.588.
lijkt me performance-wise niet handig om voor elk in te voeren beeld te checken op een centrale server wat de nieuwe ID moet worden. stel de centrale server ligt eruit dan kan de lokale server niet verder.

vraag: welke mogelijkheid is het beste of zijn voor mijn probleem standaard- en/of betere oplossingen?

thanx!

[LuCarD]

Hoe vaak worden er nieuwe bestanden gemaakt?

Hoe groot zijn de nieuwe bestanden?

Je zou bijvoorbeeld een MD5 kunnen trekken van het volledige bestand en die gebruiken als key.

[js303]

LuCarD schreef op woensdag 21 september 2005 @ 13:25:
Hoe vaak worden er nieuwe bestanden gemaakt?

Hoe groot zijn de nieuwe bestanden?

Je zou bijvoorbeeld een MD5 kunnen trekken van het volledige bestand en die gebruiken als key.

- er zullen dagelijks tegelijkertijd door meerdere mensen (circa 10) op meerdere servers (voorlopig 2) tientallen / honderden bestanden (sterk wisselend) ingevoerd worden.

- die md5 zou een een optie kunnen zijn, ware het niet dat het ID 'leesbaar' moet zijn, maw. niet te lang en enige vorm van logica moet hebben. mensen moet hiermee gaan communiceren (als ware het een ordernummer).
tja wat communiceert fijner: 0afe02facb38ef0c62cb20de72ab3187 of CM.367.501...

[ Voor 11% gewijzigd door js303 op 21-09-2005 13:31 ]

[Verwijderd]

Het lijkt me dat je in iedere database een lokale ID hebt en toewijst, die niet uniek is over alle servers genomen, maar zorgt voor referentiele integeriteit. Daarnaast introduceer je een globaal id dat je gebruikt om de servers met elkaar te laten communiceren en over alle servers uniek is. Dit implementeer je bijvoorbeeld door op een centrale server ID's uit te geven. Je kan ipv individuele ID's ook een ID range teruggeven aan een server, zodat niet voor ieder plaatje een nieuw globaal ID op een globale server aangevraagd hoeft te worden.

Evt. kun je dat aanvullen met een handmatige procedure, waarbij je zo'n unieke range handmatig kan toevoegoen op het lokale systeem bij uitval van het centrale systeem, zolang je ook andere vestigingen maar op de hoogte stelt hiervan. Ook moet natuurlijk een procedure zijn om dit in de centrale server weer hanmatig bij te werken nadat het systeem weer beschikbaar is geworden.

Evt. is het globale ID ook op een andere manier te genereren, bijv. door een locatiecode te koppelen aan een nummer. Maak echter niet de fout om de lokale ID voor referentiele integeriteit te gebruiken hiervoor, omdat het dan een betekenisvol gegeven wordt, met alle nadelige consequenties van dien.

[js303]

zou het volgende niet een idee zijn:

- op 2 servers staat dezelfde tabel files met oa. de velden server_id en file_id.
- bij elke insert op een van de servers wordt niet een autonummeringsfield als teller gebruikt voor file_id, maar wordt eerst een opvraag query uitgevoerd om de nieuwe ID te bepalen:

SELECT MAX(file_id)  FROM files 
WHERE server_id = (ID van server waarop insert plaatsvindt)

3 records ingevoerd op server A

1. server_id: A file_id: 00000005
2. server_id: A file_id: 00000006
3. server_id: A file_id: 00000007

3 records ingevoerd op server B

1. server_id: B file_id: 00000004
2. server_id: B file_id: 00000005
3. server_id: B file_id: 00000006

synchroniseren beide servers

server A bijwerken met bestanden van server B:

• MySQL DB van server A: SELECT MAX(file_id) FROM files WHERE server_id = "B"
  voorbeeld resultaat: 4 (file 00000004 van server B staat reeds in DB van server A)
• MySQL DB van server B: SELECT fields FROM files WHERE server_id = "B" AND file_id > 3
• MySQL DB van server A: insert alle fields die uit SELECT query hierboven rolden
  voorbeeld:
  

  INSERT INTO files (server_id, file_id, ...) VALUES ("B", 00000005)
  INSERT INTO files (server_id, file_id, ...) VALUES ("B", 00000006)
  

server B zelfde proces maar omgekeerd

[ Voor 8% gewijzigd door js303 op 21-09-2005 14:18 ]

[js303]

Verwijderd schreef op woensdag 21 september 2005 @ 14:06:
Je kan ipv individuele ID's ook een ID range teruggeven aan een server, zodat niet voor ieder plaatje een nieuw globaal ID op een globale server aangevraagd hoeft te worden.

begrijp ik niet helemaal. die individuele ID's cq. ID range staan toch juist los van die globale ID en dit globale moet je juist toch steeds te weten komen bij het aanmaken van nieuwe file-records?

Evt. is het globale ID ook op een andere manier te genereren, bijv. door een locatiecode te koppelen aan een nummer. Maak echter niet de fout om de lokale ID voor referentiele integeriteit te gebruiken hiervoor, omdat het dan een betekenisvol gegeven wordt, met alle nadelige consequenties van dien.

waarom zou je de locatiecode+nummer niet als globale unieke ID kunnen gebruiken? bedoel je dat dit problemen gaat geven zodra locatiecode verandert of de server op een andere locatie komt te staan?

[Creepy]

js303 schreef op woensdag 21 september 2005 @ 14:15:
zou het volgende niet een idee zijn:

- op 2 servers staat dezelfde tabel files met oa. de velden server_id en file_id.
- bij elke insert op een van de servers wordt niet een autonummeringsfield als teller gebruikt voor file_id, maar wordt eerst een opvraag query uitgevoerd om de nieuwe ID te bepalen:

En toen gingen er twee mensen tegelijk een nieuw bestand toevoegen op dezelfde server. De Max() haalt vrolijk twee keer hetzelfde ID op, en ziedaar, een dubbel ID. Pak dan gewoon alsnog een autonummering veld zodat je dat voorkomt

[js303]

Creepy schreef op woensdag 21 september 2005 @ 14:45:
[...]

En toen gingen er twee mensen tegelijk een nieuw bestand toevoegen op dezelfde server. De Max() haalt vrolijk twee keer hetzelfde ID op, en ziedaar, een dubbel ID. Pak dan gewoon alsnog een autonummering veld zodat je dat voorkomt

ok ok i got it, fout van mij. maar als ik toch met autonummering werk, kan ik er dan - tijdens de synchronisatie - vanuit gaan dat ik ook daadwerkelijk alle nieuwe files van de 'andere' server krijg mbv

SELECT MAX(file_id) FROM files WHERE server_id = "B"

? hoe kom ik er anders achter welke files ik al wel niet eerder heb gesynchroniseerd, zonder ze stuk voor stuk na te lopen? datum laatste synchronisatie met server X bijhouden en dan alle files van server X opvragen die na die datum zijn ingevoerd? ja das misschien wel een beter idee!

[ Voor 6% gewijzigd door js303 op 21-09-2005 14:55 ]

[Verwijderd]

js303 schreef op woensdag 21 september 2005 @ 14:34:
[...]

begrijp ik niet helemaal. die individuele ID's cq. ID range staan toch juist los van die globale ID en dit globale moet je juist toch steeds te weten komen bij het aanmaken van nieuwe file-records?

Ik bedoel dat je centrale server een range globale IP ID's kan afgeven.

Stel, je hebt locatie A en B, en op locatie A willen ze een aantal plaatjes in de DB zetten. In de globale app kan de server op lokatie A dan een range unieke ID's opvragen (bijv. [1001-2000]). Alle plaatjes krijgen een ID uit deze range. Als even later op locatie B een aantal plaatjes in de DB gezet wordt, vragen ze ook een range aan, en krijgen ze [2001-3000], en kunnen ze plaatjes een ID geven uit deze range.

Zodra de range op locatie A helemaal gebruikt hebben, zullen ze weer een neiuwe range aanvragen, en krijgen ze [3001-4000]. Zo wordt niet voor ieder nieuw plaatje een nieuwe aanvraag op het centrale systeem gedaan, maar wordt een voorraad unieke ID's alvast gereserveerd voor gebruik op een lokatie.

[...]
waarom zou je de locatiecode+nummer niet als globale unieke ID kunnen gebruiken? bedoel je dat dit problemen gaat geven zodra locatiecode verandert of de server op een andere locatie komt te staan?

Nee, locatiecode + nummer is prima, zolang het nummer maar niet gebruikt wordt als primairy key binnen de database voor referentiele integeriteit. Een primairy key moet namelijk betekenisloos zijn buiten de context van de database, zodat het ook, waar nodig, gewijzigd zou kunnen worden zonder dat de betekenis van de gegevens in de database wordt aangetast. Dit is een belangrijke best practice voor datamodellering.

[ Voor 3% gewijzigd door Verwijderd op 21-09-2005 15:36 ]

[js303]

Ik bedoel dat je centrale server een range globale IP's kan afgeven.

Stel, je hebt locatie A en B, en op locatie A willen ze een aantal plaatjes in de DB zetten. In de globale app kan de server op lokatie A dan een range unieke ID's opvragen (bijv. [1001-2000]). Alle plaatjes krijgen een ID uit deze range. Als even later op locatie B een aantal plaatjes in de DB gezet wordt, vragen ze ook een range aan, en krijgen ze [2001-3000], en kunnen ze plaatjes een ID geven uit deze range.

...

IP's

nee maar dat is idd slim, zo hoeft een lokale server slecht 1x per 1000 files te communiceren met de globale server (zal in onze situatie voorlopig niet meer dan 1x per dag zijn), terwijl je toch direct weet welke ID's je kunt gebruiken (en dit ook direct zichtbaar kunt maken voor de gebruiker), en dus niet met bijv. server-ID's hoeft te werken (situaties als LH.000.303 en RM.000.303).

edit:
voorwaarden zijn dan wel dat de globale applicatie absoluut zeker de juiste ranges moet uitgeven en zelf bij elke aanvraag om een nieuwe range absoluut beschikbaar moet zijn.

Nee, locatiecode + nummer is prima, zolang het nummer maar niet gebruikt wordt als primairy key binnen de database voor referentiele integeriteit. Een primairy key moet namelijk betekenisloos zijn buiten de context van de database, zodat het ook, waar nodig, gewijzigd zou kunnen worden zonder dat de betekenis van de gegevens in de database wordt aangetast. Dit is een belangrijke best practice voor datamodellering.

ik snap 'em.

[ Voor 28% gewijzigd door js303 op 21-09-2005 15:32 ]

[Verwijderd]

js303 schreef op woensdag 21 september 2005 @ 15:27:
[...]

edit:
voorwaarden zijn dan wel dat de globale applicatie absoluut zeker de juiste ranges moet uitgeven en zelf bij elke aanvraag om een nieuwe range absoluut beschikbaar moet zijn.

Je kan ook een handmatig procedure hier omheen maken, maar dat breng wel risico met zich mee.

Je zult sowieso in 1 tabel de 'gereserveerde ranges' moeten opslaan, en moeten opslaan tot welk nummer uit die range je al nummers hebt gebruikt. Ipv deze tabel te vullen met een request naar een centrale server kan je deze natuurlijk ook handmatig (liefst via een formuliertje) vullen. Ook moet je op de centrale server ranges en aan welke vestiging ze zijn uitgegeven handmatig kunnen toevoegen.

Als je deze fasciliteiten hebt, kan je handmatige procedures opzetten om alles goed te laten lopen. Bijv:

Scenario A: De centrale server is vanaf 1 vestiging niet bereikbaar
1) Bel een andere vestiging om handmatig in de centrale server een nieuwe range voor jouw vestiging te reserveren.
2) Voeg deze range handmatig toe in het systeem op jouw vestiging

Scenario B: De centrale server is offline
1) Wijs een menselijke centrale coordinator aan die zo nodig ranges uitgeeft en op papier / in excel sheet / whatever dit bijhoudt
2) Laat de centrale coordinator de ranges bijwerken in het centrale systeem zodra deze weer bereikbaar is.

Voor alles is een goede oplossing te bedenken. Je moet vooraf bedenken wat er fout kan gaan, en dan schatten hoe vaak dit zou kunnen gebeuren, hoe je het dan zou kunnen oplossen en hoeveel tijd / geld dat zou kosten. Op basis van die gegevens maak je toepasselijke ontwerpkeuzes, die niet alleen betrekking hebben op het systeem maar ook op het proces waar het systeem onderdeel van is, zodat je bijvoorbeeld bovenstaande scenario's ook ontwerpt en alvast een centrale coordinator aanwijst voordat het systeem in de lucht is.

[ Voor 16% gewijzigd door Verwijderd op 21-09-2005 15:48 ]

[js303]

ik ben even een schema aan het maken, nav het range-concept van MrX. zodra deze af is post ik die hier. iig alvast bedankt, ik ben een stuk verder gekomen!

[ Voor 23% gewijzigd door js303 op 22-09-2005 12:09 ]

[djluc]

MySQL heeft hier zelf al iets op gevonden denk ik: http://levine.sscnet.ucla...Issues%20in%20Replication

[js303]

djluc schreef op donderdag 22 september 2005 @ 12:53:
MySQL heeft hier zelf al iets op gevonden denk ik: http://levine.sscnet.ucla...Issues%20in%20Replication

interessant artikel van die levine site. ik ben het nog aan het bestuderen. replication in mysql, ik vind het nogal onduidelijk allemaal. vooralsnog heb ik (met jullie hulp) de volgende procedure bedacht voor het invoeren van een nieuw bestand.

tabellen, die zowel op de master als slave(s) staan

ws_files_server

id
ip - ip-adres van server
label - naam van server
masterserver - flag die aangeeft of dit de masterserver is
locked - flag, indien op 1 dan kunnen er geen bestanden ingevoerd worden op deze server

ws_files_ranges

id
server_id
range_first - begin nummer van range
range_last - eind nummer van range
range_current - huidige stand

ws_files_sources

id
global_id - hierin wordt de id geplaatst die uit ws_files_ranges wordt gehaald
server_id - id van de server
name, type, size etcetera - velden voor de file zelf

Alvorens nieuwe file te inserten

Check op lokale server of deze locked is
Check in ws_files_ranges voor huidige server, of locked op ‘1’ staat. Huidige server is server waar $_SERVER[‘SERVER_ADDR’] matcht met IP in tabel ws_files_servers.

Locked:
Toon melding “lokale server is momenteel gelocked: u kunt geen nieuwe bestanden invoeren, probeer later nog eens”
[einde]

Niet locked: check op lokale server of de range nog niet vol is
Zoek in ws_files_ranges op, voor huidige server, of current range buiten de toegewezen range (range_current >= range_last) valt. Zodra dit het geval is moet er een nieuwe range aangevraagd worden bij de centrale server.

Range is nog niet vol: insert nieuwe file
[ga verder met aanmaken nieuwe file]

Range is vol: Probeer een nieuwe range aan te vragen
Zet de lockstatus van de lokale server op 1.
Stuur een $_POST / $_GET request naar de centrale server, met de volgende gegevens:
IP adres van lokale server; naam van lokale server (haal deze gegevens uit lokale DB, tabel ws_files_servers), in md5() string

Centrale server die range-aanvraag moet afhandelen is onbereikbaar: aanvraag mislukt
Het kan zijn dat de remote server down is, dat de lokale server (tijdelijk) geen internet-toegang heeft.
Toon melding “centrale server onbereikbaar”
Zet lockstatus van lokale server op 0.
[einde]

Centrale server wel bereikbaar: Check in aanvraag-script of uitgeven van ranges LOCKED is.
Het kan zijn dat er momenteel een nieuwe range wordt toegekend van de centrale aan 1 vd lokale servers of aan zichzelf. Als dit gebeurt, kunnen we geen range requesten dus moeten we het later nog eens proberen.

NB 1: Het aanvraagscript staat op de centrale server. Check bij opstarten van aanvraagscript of huidige server-IP ($_SERVER[‘SERVER_ADDR’]) overeenkomst met IP in ws_files_servers.
NB 2: Check de lockstatus door te kijken naar ws_files_servers.locked waar masterserver = ‘1’

Centrale server is locked: range-aanvraag mislukt
Toon melding “probeer later nog eens”
Zet lockstatus van lokale server op 0.
[einde]

Centrale server niet locked: vraag nieuwe range aan
Zet op centrale server in ws_files_servers het locked-veld van huidige server (=masterserver) op ‘1’.
Selecteer max(range_last) uit ws_files_ranges en tel hier 1 bij op om de nieuwe range_first te krijgen. De nieuwe range_last wordt range_first + range_size (op te halen uit ws_files_servers.range_size) + 1.
Voeg een nieuwe record toe / update record in ws_files_ranges voor de lokale server (te halen uit de $_POST / $_GET vars. Voer deze SQL query uit op de centrale server.

Range succesvol aangemaakt op centrale server
Zet op centrale server locked weer op ‘0’.
Stuur een OK message terug aan de lokale server die het request heeft gedaan, tezamen met de nieuwe range die de lokale server heeft toegewzen gekregen (bijv. in XML formaat).
Vervolgens voert de lokale server de nieuwe range in zijn eigen ws_files_ranges in (insert of update).

Range-instellingen kopieren van centrale server naar lokale server gelukt
Zet de lockstatus van de lokale server weer op 0.
[ga verder met aanmaken nieuwe file]

Mislukt
Toon melding “fout bij synchroniseren van range-gegevens naar lokale server”
Zet de lockstatus van de lokale server weer op 0.
[einde]

Range aanmaken op centrale server mislukt
Wellicht dat er een sql-query fout is, of iets anders onvoorspelbaars.
Toon melding “error while creating new range”.
[einde]

[ Voor 10% gewijzigd door js303 op 10-10-2005 14:18 ]