Category Archives: Database

MySQL date range, case study

Just stumble upon situation where I need to find out if specific date in a date range.

I’ve try using lower than with greater than operator and use between in second attempt.

Quick mysql shell mode :-)

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\alam>cd c:\appserv\mysql\bin

 

C:\AppServ\mysql\bin>mysql -uroot -p

Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> select curdate();

+————+
| curdate()  |
+————+
| 2008-02-11 |
+————+
1 row in set (0.00 sec)

mysql> use pendidikan;

Database changed

mysql> select * from stmik_periode_krs where curdate()>=tgl_mulai and curdate()
<=tgl_akhir and aktif=’Y’;

+—-+————+————+————+——-+————+————–
+———-+——-+
| id | tgl_mulai  | tgl_akhir  | petugas    | jenis | tgl_buka   | tahun_ajaran
| semester | aktif |
+—-+————+————+————+——-+————+————–
+———-+——-+
| 10 | 2008-02-11 | 2008-02-13 | sri_hayati | sp    | 2008-01-23 | 2007/2008
| 3        | Y     |
+—-+————+————+————+——-+————+————–
+———-+——-+
1 row in set (0.00 sec)

mysql>

mysql> select * from stmik_periode_krs where curdate() between tgl_mulai and tgl

_akhir and aktif=’Y’;

+—-+————+————+————+——-+————+————–
+———-+——-+
| id | tgl_mulai  | tgl_akhir  | petugas    | jenis | tgl_buka   | tahun_ajaran
| semester | aktif |
+—-+————+————+————+——-+————+————–
+———-+——-+
| 10 | 2008-02-11 | 2008-02-13 | sri_hayati | sp    | 2008-01-23 | 2007/2008
| 3        | Y     |
+—-+————+————+————+——-+————+————–
+———-+——-+
1 row in set (0.01 sec)

mysql>

Both work well, I just have to adjust my computer clock for trying the result.

Export picture from mysql database to file

[amazonify]032152599X:left[/amazonify]
As our data getting mobile and copying picture is annoying for me I decide to save the picture in the database (mysql).

Another challenge came when other division need the data exported in a directory with same dimension (135 x 180 pixel).

Structure for table ‘student_pic’  :

CREATE TABLE `student_pic` (
  `image_id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(50) NOT NULL default ”,
  `image_type` varchar(50) NOT NULL default ”,
  `image` longblob NOT NULL,
  `image_size` bigint(20) NOT NULL default ‘0’,
  `image_name` varchar(255) NOT NULL default ”,
  `image_date` datetime NOT NULL default ‘0000-00-00 00:00:00’,
  UNIQUE KEY `image_id` (`image_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3385 ;

Yes, I use mysql 5 ( 5.0.45)

Continue reading

Get table diff with sqlyog

As the job area getting bigger and bigger, new tables created and others table get normalize. It’s often to see adding field or remove some field from table.

Development phase also take some place and different computer.  Need more time to get ‘whats new‘ in table structure.

An illustration for example :

Working to make database for book application. Location for developing are in the office and house. This example will show how to get table diff for table at home versus office table.

Database test1 (at office) already have table named ‘book‘.

Structure of this table contains :

id (Int 11), auto increment

book_name (varchar 100)

book_author (varchar 100)

 table diff

I dump this structure for my computer at home. At home I made database test2.

Continue reading

ERROR 2003: Can’t connect to MySQL server

I Need to add remote user for add, select, update and delete in mysql.

grant select,insert,update,delete on postfix.* to "postfix_rc"@"%" identified by "postfix_xxxxyyy" with grant option;

try to access :

-bash-2.05b$ mysql -upostfix_rc -p -h 172.88.1.5
Enter password:
ERROR 2003: Can’t connect to MySQL server on ‘172.88.1.5’ (61)

according to this, that means no tcp/ip support.

checking firewall. OK.

after check for mysql

jedimaster# ps -ax | grep mysql
  739 con- I      0:00.01 /bin/sh /usr/local/bin/mysqld_safe –defaults-extra-file=/var/db/mysql/my.cnf –user=mysql –datadi
  834 con- S      0:04.86 /usr/local/libexec/mysqld –defaults-extra-file=/var/db/mysql/my.cnf –basedir=/usr/local –datadir
59745  p1  I      0:00.01 /bin/sh /usr/local/bin/mysqld_safe –defaults-extra-file=/var/db/mysql/my.cnf –user=mysql –datadi
59769  p1  S      0:04.98 /usr/local/libexec/mysqld –defaults-extra-file=/var/db/mysql/my.cnf –basedir=/usr/local –datadir
jedimaster# ee /var/db/mysql/my.cnf

check  this line :

#skip-networking

removing # will add security as connection only allowed from localhost but in my case I need tcp/ip so I put # back.

try again :

-bash-2.05b$ mysql -upostfix_rc -p -h 172.88.1.5
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 414 to server version: 5.0.45-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

make sure minimum mysql client : mysql-client-4.1.21 Multithreaded SQL database (client)

If I use from other server :

$ mysql -upostfix_rc -p -h 172.88.1.5
Enter password:
ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
$

I use mysql 4.0.27 client :-)

got new mysql error :-). My old friend is 127 error heheheheehehe.

Migrating from mysql 4.0 to mysql 5.0 in FreeBSD

Final decision has been made, upgrade to mysql 5.0 is a must.

But how to do that?

How about user data?

After doing it by myself I found it’s easy. I try to share my experience in sbs (step by step) form :-)

Situation :

I have two server kongja and proxy :

Server kongja has mysql 4.0.27 installed

Server proxy don’t have mysql server installed.

I wanna try migration in server proxy before doing it in kongja.

Here’s the steps :

1. Install mysql40-server in proxy

cd /usr/ports/databases/mysql40-server/ && make install

2. Compress mysql directory in kongja

cd /var/db

tar cvzf mysql.tar.gz mysql

export this file (mysql.tar.gz) to proxy.

Continue reading