Tambah medan id yang auto_increment dan primary key kepada table yang takde tapi perlu sama dengan table replicate.. aduhh

Kes dia macam ni

aku ada 2 DB yang mana satunya adalah DB master yang replicated daripada DB utama.

satu DB lagi adalah DB untuk development tetapi user exited untuk guna dan sekarang ni dah macam production. Secara positifnya, sistem flow dan structure kira dah ok la tu bila user tak komplen.


Masalahnya, dalam DB dev, aku masukkan auto_increment value dengan nama 'id' pada satu table yang agak anchor jugak. Tapi sebenarnya, kalau ikut table yang asal, field tu takde pun (legacy system).


Jadi, oleh kerana DB dev ini tidak update dan DB master tu adalah slave replication dengan server DB utama, maka bilangan row untuk table dah jadi lain.. Dan kalau aku tambah je auto_increment field dalam DB master tu, pasti sistem aku akan crush/bugged sebab sistem tu akan jadikan foreign key dalam table lain.


Objektif aku ialah:

1. Tambah kolum ID dalam table pada DB master
2. Selaraskan nilai ID dalam table dev dan table MASTER
3. bagi row yang takde dalam table di dev, nilai 0 akan digantikan dengan nilai yang unik (auto_increment)
4. setkan kolum ID dalam table DB master sebagai auto_increment, primary key.


Tapi ada satu step lagi kena tambah sebab dalam table master tu ada duplicate rows. Jadi aku kena delete dulu salah satu rows yang duplicate tadi sebelum aku setkan id sebagai primary_key.


OK,, ini yang aku buat...

katakan nama table : visits

1. tambah row ID pada table visits di master DB

SQL : 

     ALTER TABLE  `InPatient` ADD  `id` INT NOT NULL FIRST

2. selaraskan nilai 'id' pada table dev dengan table master berdasarkan params yang sama.

SQL :

         UPDATE master.visits mv
    LEFT JOIN dev.visits dv 
    ON dv.params1 = mv.params1 AND dv.params2 = mv.params2
    AND dv.params3=mv.params3
    SET mv.id = dv.id;

Dengan ini, nilai id dalam table dev akan ditiru kepada table master.

Oleh kerana table di master mengandungi lebih banyak row, maka nilai id = 0 akan wujud bagi row yang tak ada dalam table di dev.


Kita perlu update nilai id ini dengan nilai auto_increment berdasarkan nilai tertinggi id.

2.5 ini adalah khusus kepada table aku sendiri memandangkan ada duplicate row pada table di master.

paparkan semua row yang duplicate..

SQL :

                       SELECT * 
         FROM visits 
         WHERE id<>0 
         GROUP BY id 
         HAVING COUNT(id)>1;

aku ada total 48 row yang duplicate id ni. Dengan kata lain, ada 49 row yang sama. Jadi aku kena delete la satu row. Untuk ini, aku cuma gunakan phpmyadmin je, select semua row dan tekan drop.

tapi aku pastikan dulu bilangan row adalah betul sebelum dan selepas delete duplicate row tu.

total row aku ada - bil row yang duplicate = 555850-48 = 555802

jadi selepas aku delete row duplicate menggunakan phpmyadmin, bilangan total row sepatunya akan jadi seperti yang aku kira tu.

Secara selamatnya, bilangan row akan betul la. InsyaAllah.


3. Update nilai id mengikut nilai max(id) secara auto increment.

Sebelum aku update nilai id mengikut nilai max(id) yang sedia ada secara auto_increment, aku check dulu  berapa row yang aku kena update. Caranya ialah mengira berapa row dengan id = 0

SQL :

       SELECT *
       FROM visits
       WHERE id=0

Jadi aku ada 125312 row untuk diupdate.


SQL :

UPDATE master.visits mv, 
  (SELECT @n := (Select MAX(id) FROM master.visits)) n
   SET mv.id = @n := @n+1
   WHERE mv.id = 0

       *125312 rows affected

Nilai row yang dikemaskini sama dengan jumlah row dengan nilai idnya adalah 0.

Akhir sekali, pastikan tak de id yang duplicate..

SQL :

SELECT * FROM `visits`
GROUP BY id
HAVING COUNT(id)>1

* MySQL returned an empty result set

yeay!


4. Setkan id sebagai primary_key dan auto_increment.

Guna je phpmyadmin..

Setakat yang aku buat ni takde masalah. Harap dapat selesaikan masalah ni jugak..

Terima kasih..