WN - Få MySQL att sluta resetta auto

WN (https://www.wn.se/forum/index.php)

- Serversidans teknologier (https://www.wn.se/forum/forumdisplay.php?f=4)

- - Få MySQL att sluta resetta auto_increment? (https://www.wn.se/forum/showthread.php?t=1058131)

JesperA

2013-06-02 20:28

Få MySQL att sluta resetta auto_increment?

Tja!

Stötte på ett problem med MySQL (innodb), detta problemet tog jag för givet att det inte existerade.

Jag har 2st tabeller med liknande struktur och jag använder samma $_POST för att lägga in data i båda tabellerna. Jag använder transaktioner för att säkerhetsställa att datan verkligen läggs in i båda tabellerna, detta fungerar utmärkt.

I tabell 1 har jag ett PostID som är PRIMARY KEY och auto_increment

I tabell 2 har jag också ett PostID som använder "$PostID = $db->lastInsertID();" (i PHP) från ovanstående insert, detta gör ju att PostID bör bli samma i båda tabellerna, vilket det blir.

Problemet är att tabell 1 bara är en temporär tabell vars rader bara existerar i några timmar eller max ett par dagar innan raden tas bort, ibland riskerar den att bli tom, och råkar man då starta om servern just när tabell 1 är tom så defaultar auto_increment ner till 1 igen, detta pajar ju hela consistency grejen mellan dom 2 tabellerna.

Tabell 2 är en arkiverande tabell, jag skulle kunna sätta auto_increment på tabell 2 istället och låta tabell 1 få sitt PostID från tabells 2 "$PostID = $db->lastInsertID();", alltså sätter in i tabell 2 först, och sen i tabell 1.

Men finns ju samma risk där att om jag migrerar tabell 2 vid ett senare tillfälle så kan jag inte garantera någon consistency.

Går det inte få auto_increment att låsa sitt värde oavsett om tabellen blir tömd (alltså inte bara vid truncate utan även när varje rad tas bort 1 och 1 tills tabellen blir tom).

Conny Westh

2013-06-02 21:13

En temptabell skapas ju på nytt varje gång så jag skull använda arkiv-tabellens autoincrement som primary copy.

JesperA

2013-06-03 15:39

Jag tror du tänker på en temptabell som skapas tex när man kör subqueries osv, det är inte en sådan tabell jag syftar på, utan jag har en permanent tabell som jag dumpar tillfällig data i.

Iaf, jag kommer ju bli tvungen att köra med tabell 2 som "huvudtabell" nu istället.

Helt sjukt att jag missat detta problemet, har tagit för givet i över 10 år att auto_increment är ett sätt att garantera 100% unikt ID, men det är det ju inte, det går ju bara att garantera på en enskild tabell, inte om man tar ID från en tabell och vill använda som foreign key eller whatever på andra tabeller, ID över flera tabeller kan ju då enbart garanteras om tabellen aldrig blir tom...

FredrikMH

2013-06-03 17:57

Citat:

Ursprungligen postat av JesperA (Inlägg 20471227)

Helt sjukt att jag missat detta problemet, har tagit för givet i över 10 år att auto_increment är ett sätt att garantera 100% unikt ID, men det är det ju inte, det går ju bara att garantera på en enskild tabell, inte om man tar ID från en tabell och vill använda som foreign key eller whatever på andra tabeller, ID över flera tabeller kan ju då enbart garanteras om tabellen aldrig blir tom...

Nej tabellen kan bli tom utan att auto_increment nollställs, men det beror på hur du tömmer tabellen. TRUNCATE kommer återställa auto_increment, men jag tror inte en vanlig DELETE FROM gör det.

Vitsen är ju att om du tar bort data som används som nyckel i andra tabeller så bör du nog även ta bort data där eller fundera på ett annat databasupplägg.

JesperA

2013-06-03 18:07

Citat:

Ursprungligen postat av FredrikMH (Inlägg 20471253)

Nej tabellen kan bli tom utan att auto_increment nollställs, men det beror på hur du tömmer tabellen. TRUNCATE kommer återställa auto_increment, men jag tror inte en vanlig DELETE FROM gör det.

Som sagt, auto_increment nollställs om du har tömt raderna en och en, är då tabellen tom när man startar om servern eller mysql service så resettas auto_increment.

Citat:

Ursprungligen postat av FredrikMH (Inlägg 20471253)

Vitsen är ju att om du tar bort data som används som nyckel i andra tabeller så bör du nog även ta bort data där eller fundera på ett annat databasupplägg.

Foreign key var bara ett exempel.

FredrikMH

2013-06-03 21:06

Citat:

Ursprungligen postat av JesperA (Inlägg 20471254)

Som sagt, auto_increment nollställs om du har tömt raderna en och en, är då tabellen tom när man startar om servern eller mysql service så resettas auto_increment.

Det stämmer det du säger med InnoDB. För MyISAM stämmer det dock inte, då är auto_increment sparat trots att MySQL service startar om och tabellen är tom.

Är du bunden till InnoDBs funktioner eller skulle MyISAM kunna fungera?

Conny Westh

2013-06-03 21:53

Stopp och belägg!!!

Skapa först en databas, sen kör du Create enligt nedan med InnoDB....

Kod:

use wn;



delimiter $$



CREATE TABLE `test` (

  `idTest` int(11) NOT NULL AUTO_INCREMENT,

  `Testcol` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`idTest`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

Sen kör du denna kod dvs först rensar hela tabellen Test från alla poster med en vanlig SQL Delete...

Sen lägger du till nya poster med INSERT och sen raderar igen och skapar nya med INSERT så kommer INTE autoincrement att nollställas, tvärtom förtsätter den att öka.

Nedanstående kod kan du köra flera gånger och då kommer hela tiden primärnyckeln att öka från gång till gång, trots att alla poster raderas varje gång....

Kod:

delete from test;

insert into test(testcol) values('a');

insert into test(testcol) values('b');

insert into test(testcol) values('c');

insert into test(testcol) values('d');

insert into test(testcol) values('e');

select * from test;

delete from test;

insert into test(testcol) values('f');

insert into test(testcol) values('g');

insert into test(testcol) values('h');

insert into test(testcol) values('i');

insert into test(testcol) values('j');

insert into test(testcol) values('k');

select * from test;

lubic

2013-06-03 22:52

Conny: Tror du missade punkten om att servern startas om efter att man har tagit bort raderna. Sen tycker jag iofs det låter märkligt/dumt att autoincrement nollställs vid en omstart? Men så är det kanske tänkt att fungera av någon anledning?

http://dev.mysql.com/doc/refman/5.0/...-handling.html

Conny Westh

2013-06-04 00:10

Citat:

Ursprungligen postat av lubic (Inlägg 20471277)

OK, det missade jag.

Då kan man ju faktiskt aldrig använda auto_increment i MySQL.

Man får skapa en egen tabell som håller koll på nästa ID och anropa en SP som ger nästa lediga ID för just den tabellen.

Conny Westh

2013-06-04 02:12

Hittade följande länk (otestat): http://dev.mysql.com/doc/refman/5.0/...increment.html

Kod:

-- Posted by Thomas Mayer on January 20 2012 3:17pm

-- 

-- As InnoDb forgets its highest auto_increment after server restart, you can set it again, 

-- if you have stored it anywhere. This happens often if you archive your data in an archive 

-- table and then delete it and then restart mysql. When archiving again this will result in 

-- duplicate key entries.

-- 

-- To work around this you can create a trigger which makes sure your auto_increment is 

-- higher than the auto_increment of your archive table:



delimiter //

drop trigger if exists trigger_autoinc_tbl;

CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl

FOR EACH ROW

BEGIN

declare auto_incr1 BIGINT;

declare auto_incr2 BIGINT;

 SELECT AUTO_INCREMENT INTO auto_incr1 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl';

 SELECT AUTO_INCREMENT INTO auto_incr2 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl_archiv';

 IF (auto_incr2 > auto_incr1 and NEW.id<auto_incr2) THEN

 SET NEW.id = auto_incr2;

 END IF;

END;//

delimiter ;



-- Further reading: http://www.slicewise.net/index.php?id=82

corneliisandberg

2013-06-12 14:31

Autoincrement

Hej Jesper!
Jag tycker din fråga verkar spännande och det kan finnas en potentiell bugg i någon version av Mysql som du har hittat.

Jag kunde inte låta bli att prova med en egen tabell i en Mysql med InnoDB motor. Lade in några poster som hade en auto inkrementell räknare i sig. Därefter startade jag om tjänsten för att se om räknaren var nollställd. Det var den inte.

Du skriver att din tabells data bara existerar några timmar eller kanske bara några dagar. Hur töms den? Men truncate nollställs såklart räknaren men inte med delete.

Tycker problemet verkar konstigt snudd på barockt. Kan du om du orkar skriva ned de olika steg du gör för att "tappa" räknarens värde.

Här är min tabell och de inställningar jag körde med (efter omstart av tjänst är värdet 5 inte 1). Hur ser din tabell ut?
CREATE TABLE `slask` (
`counter` int(11) NOT NULL AUTO_INCREMENT,
`someValue` char(50) COLLATE utf8_swedish_ci DEFAULT NULL,
PRIMARY KEY (`counter`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

Serversetup
Mysql 5.5.28
Ubuntu 12.04

Sen skulle du kunna göra så att du faktiskt sparar last_insert_id i en annan tabell som du sedan alltid hämtar ifrån och sedan ändrar din tabell med ALTER TABLE tbl AUTO_INCREMENT = Ditt värde;
Men det låter som ett löjligt sätt att lösa ett okänt problem på. Det är nog bättre med en djupdykning och ta reda på orsaken.

Vänligen
Cornelii

lubic

2013-06-12 16:12

Tror inte det är en bugg, utan det är så det är tänkt att fungera, om jag inte är helt fel ute?. Alltså om du skapar upp några poster så att auto_increment ökar och sedan tar du bort dessa rader med delete så att tabellen töms (eller åtminstone så att de rader med högst id försvinner). När du sedan startar om servern och stoppar i nya rader så har auto_increment nollställts till den rad med högst id. Jag har inte själv testat detta men det är det som verkar vara "problemet"?

corneliisandberg

2013-06-13 09:11

Hej Lubic!
Gör följande.

InnoDB
1. Ta bort en post från tabellen. Inkrementellt värde ser ut att vara samma som innan. Dvs om pekar var på position 5 så står det fortfarande 5.
2. service mysql restart (t.ex. omstart av databasen)
3. Inkrementellt värde har nu minskat till 4

MyIsam (ändra samma tabell till MyIsam)
Samma procedur som ovan behåller räknarens inkrementella värde även efter omstart .

Se länk.

Citat:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

lubic

2013-06-13 13:30

Precis, då är det som jag har uppfattat det, och alltså inte en bugg som du misstänkte?

Conny Westh

2013-06-13 20:11

Jag skulle säga att det är en bugg i kravspecifikationen, för autoincrement ska inte fungera som Max(PK) utan ska bevara sitt värde vid omstart.

Westman

2013-06-14 10:04

Citat:

Ursprungligen postat av ConnyWesth (Inlägg 20471928)

Jag skulle säga att det är en bugg i kravspecifikationen, för autoincrement ska inte fungera som Max(PK) utan ska bevara sitt värde vid omstart.

Instämmer, jag skulle bra gärna vilja veta hur de tänkte när de valde den här funktionaliteten.

corneliisandberg

2013-06-14 10:57

Mmm håller med Conny och Westman. En eye-opener efter 20 år i branschen, inte så vanligt numera.

Men okej nu känner vi till skillnaden och kanske kan InnoDBs sätt att ej behålla inkrementellt värde också användas av oss nu när vi har vetskapen om denna feature.

Ha det gott nu i helgen!

JesperA

2013-06-14 17:57

Lite blandade känslor att jag stötte på denna "buggen"/speccen, hade gärna varit lyckligt ovetande om detta beteendet, enda jag kommer på varför dom inte vill spara värdet annat än i ram är för att minska overheaden vid användandet av auto_increment, det skulle ju annars bli en extra skrivning till disk för varje increment värdeökning, men ja, jag hade gärna sett att man kunde välja beteendet åtminstonde så den faktiskt sparar värdet.

Jag har nu bytt så att min arkiverande tabell sköter auto_increment och så får min tillfälliga tabell styras av den, inga problem, kunde lika gärna varit så från början egentligen.

Vill inte byta till MyISAM heller, rätt skönt att köra transaktioner i InnoDB.

Iaf, kommer inte manuellt spara auto_increment värdet, nu vet jag ju om detta beteendet och det är sällan jag startar om mysql service eller servern, sällan den krashar också, så jag kan ju manuellt sätta auto_increment värdet om något av det inträffar.

Men som sagt, jag hade hellre sett att det åtminstonde fanns ett val att spara auto_increment när man kör InnoDB, blev sjukt förvånad när jag vaknade en morgon och kollade loggarna och dom var sprängfyllda med:

"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '402' for key 'PRIMARY'"

Jag har alltid till 100% litat blint på att auto_increment skall garantera ett unikt värde, men i mitt scenario (om än går att undvika) så garanterades det inte

corneliisandberg

2013-06-14 20:54

Jesper: Lika förvånad jag. Har också blint trott på att värdena är unika och aldrig kan förekomma mer än en gång. Nu känns det lite som att varje gång vi vaknar på morgonen har vi fått ett nytt personnummer att lära oss.

bya	2013-06-20 00:05

hibernate tex, som mappar javaklasser till databas, använder en separat tabell för "sequence" , asså ett nästa värde... det har nog sina anledningar.

Alla tider är GMT +2. Klockan är nu 02:36.