Feeds:
Posts
Comments

Archive for the ‘MySQL – DeBe’ Category

Haloo … prenz … dah lama ane gak posting .. gak ada waktu dan kena penyakit M yaitu Malas 😀

dah gak perlu banyak cat cit cot dah … Ane mau bagiin scripting buat backup/restore pake mysqldump dan teman temannya, dan ini running hanya di WINDOWS environment ya ..

——code_begin——–

@echo off

echo +—————————————————–+
echo + MySQL Database Backup Script +
echo + +
echo + By (c) Man’z, 2011 +
echo +—————————————————–+
rem — DB name area —
set mysql_db=db_hrms
rem ——————–

rem — MySQL server location —
set mysql_pos=”E:\mysql-5.1.30-winx64\bin”
rem —————————–

rem — MySQL host, user and password DB —
set mysql_server=-h<<IP_HOST>> -uroot -p<<password_root>>
rem —————————————

rem — Don’t touch this area —
set mysql_table_option=–add-drop-table –complete-insert –extended-insert –hex-blob –skip-comments –tables
set db_tables=..\%mysql_db%_tables.txt
rem —————————–

rem — Where is 7zip program for compression —
set zip_pos=”C:\Program Files (x86)\7-Zip”
rem ———————————————

rem — Where is data will be extracted —
set data_pos=e:\DataLog
rem —————————————

rem — From this point — don’t touch this area, otherwise you know what you do, take a risks —
for /f “tokens=1-5 delims=/ ” %%d in (“%date%”) do set dbdump=%mysql_db%-%%g-%%e-%%f
set pos=%data_pos%\%dbdump%

echo Creating directory [ %pos% ]
mkdir %pos%

echo Extracting Tables [ %mysql_db%_tables.txt ]
%mysql_pos%\mysql %mysql_server% %mysql_db% –execute=”show tables” > %data_pos%\%mysql_db%_tables.txt

echo dump Table [ %db_tables% ]
for /f “tokens=1-5 delims=/ ” %%a IN (%pos%\%db_tables%) do (
if %%a neq Tables_in_db_hrms (
echo Extracting Table [ %%a ]
%mysql_pos%\mysqldump.exe %mysql_server% –result-file=%pos%\%%a.sql %mysql_db% %%a )
)

echo Extracting Routine [ %mysql_db% ]
%mysql_pos%\mysqldump.exe %mysql_server% -R –no-create_info –no-data –skip-comments –triggers=FALSE –result-file=%pos%\routines.sql %mysql_db%

echo Compressing [ %data_pos%\%mysql_db% == %pos% ]
%zip_pos%\7z.exe a -mx=9 -ms=on -r %data_pos%\%mysql_db%\%dbdump% %pos%

echo Removing database directory [ %pos% ]
rd /S /Q %pos%

echo Removing Tables References [ %mysql_db%_tables.txt ]
del %data_pos%\%mysql_db%_tables.txt

echo Done

——code_finish——–

Save script diatas dengan backup_mysql.bat

Cara pakainya bagaimana ?, nih cara pakainya :

tinggal running aja backup_mysql.bat di command prompt

 

dan bagaimana untuk restoring nya :

——code_begin——–

@echo off

echo +—————————————————–+
echo + MySQL Database Restoration +
echo + +
echo + By (c) Man’z, 2011 +
echo +—————————————————–+

if “%1″==”” GOTO EmptyString_DB
if “%2″==”” GOTO EmptyString_Password
if “%3″==”” GOTO EmptyString_Data
if NOT EXIST %3 GOTO No_Files

echo + +
echo + Get the environment variables +
set mysql_pos=”c:\program files\mysql\mysql server 5.1\bin”
set data_pos=%3
set uname=root
set passwd=%2
set db=%1
set cur_pos=%CD%

echo + Processing … please wait +
for /r %cur_pos%\%data_pos% %%I in (*.*) do (
mysql –user=%uname% –password=%passwd% %db% –execute=”\. %%I”
)
echo + Finish restoring all database tables into database +
echo +—————————————————–+
goto Done

:EmptyString_DB
echo + +
echo + Message: Database not specified +
echo + +
goto Usages

:EmptyString_Data
echo + +
echo + Message: Restored data location isn’t specified +
echo + +
goto Usages

:EmptyString_Password
echo + +
echo + Message: No Password database is supplied +
echo + +
goto Usages

:No_Files
echo + +
echo + Message: No such file(s)/directories +
echo + +

:Usages
echo +—————————————————–+
echo + Usage: +
echo + restore_db db_name db_password data_pos +
echo +—————————————————–+

: Done

echo Thank you

——code_finish——–

Save file diatas dengan restore_db.bat

Cara pakai:

server> restore_bat <<databasename>> <<password>> <<nama_file_hasil_extracting>>

Catatan:

untuk restoring harus dijalankan langsung pada server yang sudah jalan mysqlnya ..

Salam,
😎
Man’z

Advertisements

Read Full Post »

Hai prenz … last post di taon 2010 nih…

Karena ada kesibukan … (ngodink) hehehehe :mrgreen: jadi ketemu deh permasalahan nya, dulu mah gak pernah terpikirkan gimana caranya untuk deleting record dengan subquerying …

Subquerying biasanya dilakukan dengan select statement mulu kalo di mysql, nah sekarang kejadian deh pengen delete record dengan kondisi yang di subquerying tersebut.

Contoh kasus nya adalah saya pengen menghapus record yang sama pada tanggal yang sama akan tetapi ada kondisi field yang berbeda.

contoh data :


Nah yang dikotak merahin itu diatas kebanyakan untuk field cause = cause_ori, maka saya ingin menghapus yang data cause = cause_ori nya

kalau di select dengan perintah dibawah ini :

select ab.nip, ab.dated, ab.cause, ab.cause_ori, count(ab.nip) as maxcount
from absence ab
inner join
( select nip, dated, cause, cause_ori
   from absence
   where cause <> cause_ori
   group by nip, dated
   order by nip, dated
) as a on ab.nip = a.nip and ab.dated = a.dated
group by ab.nip, ab.dated
having count(ab.nip) > 1
order by ab.nip, ab.dated

mendapatkan hasil seperti :

Bejibun kan datanya dan itu double kalo di scroll ke bawah bahkan ada yang triple, dan gak mungkin saya looping satu persatu untuk menghapus data tersebut.

Nah, sekarang bagaimana ingin menghapus records yang buanyak tersebut dengan menggunakan perintah SQL Statements ???

Setelah putar – puter otak dan RTFM, maka didapat SQL statemen untuk delete seperti dibawah ini :

delete deleted.* from absence as deleted
inner join
(
   select ab.ab_id
   from absence ab
   inner join
   ( select nip, dated, cause, cause_ori
     from absence
     where cause <> cause_ori
     group by nip, dated
     order by nip, dated
    ) as a on ab.nip = a.nip and ab.dated = a.dated
   group by ab.nip, ab.dated
   having count(ab.nip) > 1
   order by ab.nip, ab.dated
) as joined on deleted.ab_id = joined.ab_id

sudah deh, dengan meng-execute statement tersebut … semua data yang tidak diinginkan … lenyap dari permukaan harddisk :mrgreen:

Segetoh dulu deh … dikhawatirkan lupa, mendingan sharing dengan teman-teman… siapa tahu ada yang perlu juga :mrgreen:

Salam,
😎
Man’z

Read Full Post »

Number again in MySQL

Dear All,

Dah lama tak posting blog, ini postingan terakhir di tahun 2009, mudah – mudahan di tahun 2010 bisa lebih banyak memberikan informasi yang sedikit nyeleneh …

Lagi, kita berbicara masalah SQL yang tentunya di MySQL, karena saya berkutat di MySQL, kalo DB yang lain ??? hmm… boleh juga kali ya… cuma harus belajar lagi.

Nah, ada tuntutan dari gawean untuk memberikan penomoran kembali di laporan kapan karyawan itu masuk dan berapa orang  … ? merasa tertantang dengan gawean tersebut, saya coba aplikasikan dan putar otak terus sampe mabok dan akhirnya ada juga hasilnya nih …

Pengen hasilnya adalah seperti gambar dibawah ini :

Setelah postingan lama untuk menomorkan data dari 1 ~ n dari data awal sampe akhir, tapi kalo sekarang itu berbeda.

Data yang sama baru dihitung penomoran nya, tapi kalau misalnya ada data 4 orang, pengen nya penomoran awal dari record nomor 2 dan seterusnya. Pemodelan penomoran itu adalah dari tanggal kemudian nomor urutnya. :mrgreen: nyeleneh kan 😆

Nah, setelah lihat gambar diatas, bagaimana query nya nih .. 😎 … putar – puter otak … dah yang baca blog ini gak perlu putar otak lagi, tinggal ganti table dan field yang diinginkan, trus hasilnya sama dengan gambar diatas, kalo gak sama berarti ada salah di query tuh….

Nih query yang saya buat pake MySQL :

select distinctrow mb.hire_date,
       if (t.rowId <> 0, concat(day(mb.hire_date), '.', t.rowId),
       day(mb.hire_date)) as number
from m_biodata mb
inner join
(
   select c.hire_date,
          @row := if (@hd = c.hire_date, @row + 1, 0) as rowId,
         (@hd := if (c.hire_date is null, '', c.hire_date)) as hd
   from (select @row := 0, @hd := '') as rowInit, m_biodata c
   order by c.hire_date
) as t on mb.hire_date = t.hire_date
order by mb.hire_date, number

Nah, gimana ? mudah kan … :mrgreen:

Sampai jumpa lagi InsyaAlloh di tahun 2010 dengan materi yang nyeleneh – nyeleneh … ah … dasar aya-aya wae :mrgreen:

Salam DBA,
😎
Man’z

Read Full Post »

Dear All, again MySQL again…hehehehe … simple trick from me that will save your time of work when you using looping in code … yeah code … in Delphi, VB, what ever you create the aplication.

We will trying to create crosstab or whatever you will said … in MySQL …

First, saya punya data sebagai berikut :

Tabel karyawan:
 NIP   | Nama
 ------------------------
 001   | Udin tea
 002   | Budi Kahandap
 003   | Andi Kararangge
 004   | Cecep Gorbacep
 005   | Ono Ontohod 

 Tabel master_absen:
 tipe_absen | deskripsi
 ----------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI 

 Tabel absen:
 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL

Actually we want to create the result of query like this :

 NIP | NAMA            | AL | CT | SK
 ------------------------------------
 001 | Udin tea        | 2  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0
 003 | Andi Kararangge | 0  | 0  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0

Tapi … ada tapinya juga neh …. kalau di master_absen dan di data absen ditambah datanya menjadi :

 tipe_absen | deskripsi
 --------------------------
 AL         | ALPA
 SK         | SAKIT
 CT         | CUTI
 IZ         | IZIN PULANG   <---- tambahan data

kemudian data absen jadi :

 NIP  |     TGL    | tipe_absen
 ------------------------------
 001  | 01/07/2009 | AL
 001  | 02/07/2009 | AL
 002  | 05/07/2009 | CT
 004  | 05/07/2009 | CT
 002  | 06/07/2009 | SK
 003  | 07/07/2009 | SK
 003  | 08/07/2009 | SK
 003  | 09/07/2009 | SK
 003  | 10/07/2009 | SK
 005  | 11/07/2009 | AL
 005  | 12/07/2009 | AL
 005  | 13/07/2009 | AL
 005  | 14/07/2009 | AL
 003  | 20/07/2009 | IZ    <--- tambahan data
 004  | 21/07/2009 | IZ    <--- tambahan data

hasil yang diinginkan jadi :

 NIP | NAMA            | AL | CT | IZ | SK
 -----------------------------------------
 001 | Udin tea        | 2  | 0  | 0  | 0
 002 | Budi Kahandap   | 0  | 1  | 0  | 0
 003 | Andi Kararangge | 0  | 0  | 1  | 4
 004 | Cecep Gorbacep  | 0  | 1  | 1  | 0
 005 | Ono Ontohod     | 4  | 0  | 0  | 0

Nah ada tambahan kolom IZ tuh ….

Sekarang, How to make the simple single query statements ? yeah … query statements …

This below is step … step by step we will describe it …

Kalau kita menggunakan sql query dengan link left join seperti biasa :

select k.nip, k.nama, a.tipe_absen, a.tgl
from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, a.tgl
order by k.nip

we got result like this :

Query 1

Nah itu baru memunculkan semua data yang ada.

Sekarang kita coba di otak atik sql nya dengan menggunakan power of group_concat dan select concat statement dan menjadi sql kita, untuk mendapatkan hasil berapa banyak si-dia absen dan tidak menghiraukan tanggal berapa saja, dan saya tidak akan menjelaskan untuk group_concat and select concat functions disini, bukan gag mau … tapi cape ngetiknya :mrgreen:, seperti dibawah ini :

select concat(
'select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, disini kita sudah menggunakan fitur statement dari MySQL, dan juga return statement ke variable @Absence_SQL dan kita akan lakukan execute Absences untuk mendapatkan resultset seperti dibawah ini :

Query 2

Tapi … eit … just a moment … we got false data …, jika dilihat … record no 2 dan 3, kan ada 002 untuk Budi Kahandap itu punya status CT dan SK …. gak mungkin untuk memunculkan data seperti ini dan diserahkan kepada yang berwajib … hehehehe :mrgreen:

So, we change again the sql structure to meet our requirements … like shown as below :

select concat(
'select T.nip, T.nama',
group_concat(concat(', sum(T.', m.tipe_absen,') As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''), '\n',
'from (select k.nip, k.nama',
group_concat(
concat(', sum(if(a.tipe_absen="', m.tipe_absen, '", 1, 0)) As ', m.tipe_absen, '\n')
order by m.tipe_absen separator ''),
'from karyawan k
left join absen a on k.nip = a.nip
left join master_absen m on a.tipe_absen = m.tipe_absen
group by k.nip, m.tipe_absen
order by k.nip, m.tipe_absen) As T
group by T.nip') into @Absence_SQL
from master_absen m;
prepare Absences from @Absence_SQL;
execute Absences;

Nah, so … after we concatenate the sql statements again .. what we got ???

You may see what we want … as shown below :

Query 3

Ow, … we got right what we want …. records no 2 has become one line … :mrgreen:

ck… ck… ck… just a moment … but how we added the data has been described in beginning of this post ?

Just insert the data at master_absen and data absen … and then … just running again the last sql statements, and you will get the results like this :

Query 4

Look at the columns … somethin’ differents 😎 between this result of query than before  ??? You decide :mrgreen:

Dikau friendz bisa mengubah – ubah query diatas sesuai dengan keinginan ….

Sampai jumpa di lain Query Tricky. Oh ya… don’t forget to donate :mrgreen: to make this blog live and give another tricky … :mrgreen:

Salam DBA,
😎
Man’z

Read Full Post »

Dear all, met me again …

Now, we will posting how to resolve when your MySQL Server make you headache … alias bikin sakit kepala dan mau pecah … karena MySQL menolak semua access dari all host and IP.

Actually we don’t know what this happen persist, teuing kunaon tah … langsung begitu gag mau terima koneksi dari host manapun …. dan muncul salah satu pesan seperti ini :

ERROR 1130 (HY000): Host ‘localhost’ is not allowed to connect to this MySQL server.

atawa:

ERROR 1130 (HY000): Host ‘manz’ is not allowed to connect to this MySQL server.

atawa:

ERROR 1130 (HY000): Host ‘192.168.1.200’ is not allowed to connect to this MySQL server.

But … don’t worry … be happy 😀 i already found the technique to resolve this problem … :mrgreen:

We don’t know if other site already mention …

Teknik ini saya pakai karena server saya menggunakan windows …

Langkah – langkah nya :

1. Matikan service dari MySQL :

net stop MySQL

2. Jalankan service dengan background dan skipping checking user and privilege table:

start /b mysqld –skip-grant-tables –user=root

3. Reset user table di mysql dengan memanggil mysql command:

mysql -e “insert into mysql.user(host, user, grant_priv, super_priv, ssl_cipher, x509_issuer, x509_subject) values(‘root’,’%’,’Y’,’Y’,”,”,”);”

4. Kemudian matikan MySQL yang jalan di background process tersebut:

mysqladmin -uroot shutdown

5. Jalankan service MySQL secara normal:

net start MySQL

After that , ta..da…. dikau bisa masuk ke MySQL lagi, kalo anda tidak reset password, anda bisa pake password yang lama.

Silahkan mencicipi … :mrgreen:

Salam DBA,
😎
Man’z

Read Full Post »

Dear All readers,

Back again with “Ngitung Balance” … :mrgreen:

Pada postingan dulu, saya melakukan perhitungan balance dengan menggunakan fitur yang ada FastReport dan silahkan baca disini untuk balance FastReport.

Nah, sekarang bagaimana ngitung balance dengan menggunakan MySQL atau Single Simple Query ?

Setelah sekian waktu mencoba, mengutak – mengatik … dan akhirnya ketemu dengan simple.

Silahkan coba dengan membuat table seperti ini :

CREATE TABLE accountdeposit
(
  id INTEGER(11) NOT NULL AUTO_INCREMENT,
  memCode CHAR(9) COLLATE latin1_swedish_ci NOT NULL,
  transCode CHAR(100) COLLATE latin1_swedish_ci NOT NULL,
  stat ENUM('D','C') NOT NULL,
  balance FLOAT(9,3) NOT NULL,
  transDate TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP 
            DEFAULT CURRENT_TIMESTAMP,
  author INTEGER(11) NOT NULL, PRIMARY KEY (id),
  KEY memCode (memCode)
);
INSERT INTO accountdeposit (id, memCode, transCode, stat, balance, 
                            transDate, author) VALUES
(5, '0321001', 'Buka Account', 'C', 100000, '2009-03-21 00:05:44', 1),
(6, '0321001', 'open list 3 Form', 'D', 15000, '2009-03-23 10:51:16', 1),
(7, '0321001', 'open list 4 Form', 'C', 20000, '2009-03-23 13:05:00', 1);
COMMIT;

Nah, itu untuk struktur dan data nya … itu diatas mah sample doank ya … :mrgreen:

Sekarang bagaimana untuk retrieve agar mendapatkan seperti dibawah ini ?

+------------------+------------+------------+-------------+
| transCode        | Debit      | Credit     | LastBalance |
+------------------+------------+------------+-------------+
| Buka Account     |      0.000 | 100000.000 |  100000.000 |
| open list 3 Form | -15000.000 |      0.000 |   85000.000 |
| open list 4 form |      0.000 |  20000.000 |  105000.000 |
+------------------+------------+------------+-------------+

Nih dibawah sql scriptnya :

select transCode,
case when stat='D' then (-balance) else 0 end as Debit,
case when stat='C' then balance else 0 end as Credit,
(@LB := @LB + if (stat='D', -balance, balance)) as LastBalance
from (select @LB := 0) as LastBlnc, accountdeposit
where memCode = '0321001'
group by memcode, transdate
order by transdate;

Hasilnya ? ya seperti diatas …

Thanks to bro @yayaretina that has been open my mind to create a Simple Single Queries .. :mrgreen:

Cheers,
😎
Man’z

Read Full Post »

Dear All,

Once again, MySQL make me headache to handle un-linked key that consist at temporary tables and this two table is manipulate by record-form model.

This is a scenario :

We’ve 2 table — simple table that want to inserting the key for linked between that tables, that the key from master table is generated from trigger.

for example :

create table tbl_a
(
  uid varchar(40) default '-',
  name varchar(50),
  constraint pk_tbl_a primary key (uid)
);

create table tbl_b
(
  uid varchar(40),
  qty double precision default 0,
  description varchar(100),
  constraint fk_tbl_b foreign key (uid)
    references tbl_a (uid)
    on update cascade on delete cascade
);

now, we create the trigger for this table a.

create trigger trig_tbl_a before insert on tbl_a for each row
set new.uid = uuid();

but, how to insert the tbl_b.uid ? if we use simple insert statement … ?

After we long discussed with our best friends … Now, we have one good idea.

This the resolving the problem :

drop and re-create the trigger at table a :

drop trigger trig_tbl_a;
create trigger trig_tbl_a before insert on tbl_a for each row
begin
  set new.uid = uuid();
  create temporary table if not exists t_uid(uid varchar(40));
  delete from t_uid;
  insert into t_uid values (new.uid);
end;

then, create trigger for table b:

create trigger trig_tbl_b before insert on tbl_b for each row
set new.uid = (select uid from t_uid);

after that, we can relate the data on table b using the uid key.

try this :

mysql> insert into tbl_a (name) values ('Manz');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into tbl_b (qty, description) values (10, 'Movie ordered with DVD original version');
Query OK, 1 row affected (0.03 sec)

mysql> select * from tbl_a;
+--------------------------------------+------+
| uid                                  | name |
+--------------------------------------+------+
| 4b5a8c27-0321-11de-88cd-184e3e4ece1f | Manz |
+--------------------------------------+------+
1 row in set (0.02 sec)

mysql> select * from tbl_b;
+--------------------------------------+------+-----------------------------------------+
| uid                                  | qty  | description                             |
+--------------------------------------+------+-----------------------------------------+
| 4b5a8c27-0321-11de-88cd-184e3e4ece1f |   10 | Movie ordered with DVD original version |
+--------------------------------------+------+-----------------------------------------+
1 row in set (0.00 sec)

Now, we can use the code to update my uid. And result is my headache is gone…. :mrgreen:

Cheers,
😎
Man’z

Read Full Post »

Older Posts »