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.

Its my bad for not reading database manual that lead me for doing boring task .

Dump a few database from server and dumping it in my windows xp. one by one.

Perhaps someone who see this post can learn and not doing same mistake as me.

Command to backup one database :

backup database pendidikan :

edp# mysqldump -ualam -p pendidikan > pd.sql

backup database cms_study :

edp# mysqldump -ualam -p cms_study> cms1.sql

backup database cms_stmik :

read more »

When requirement for information stored in database grow, old table structure need to adjust for flexibility.

Common task to do in  normal cycle :

1. Build new application in other place

2. Synchronize database ( tables)

3. Upload application.

Second step ( synchronize tables) often  painful when a lot of changes made at home and need to sync with server environment.

A handy tool to sync database table I often use is sqlyog.

For example I have two database :

db1 (at server)

db2 (at home)

table structure has changed.

In db1, table testing has this fields :

CREATE TABLE `testing` (
  `id` tinyint(3) NOT NULL auto_increment,

 `name` varchar(15) NOT NULL default '',

  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

 read more »

With so many function provided in mysql plus lazy, It seem like finding gem for replace my old approach

duplicate data isn’t good, I must search for exisiting data, in general : insert data to table when no data with that value exist and update table if data exist

For example :

I have table A with field :

1. id, integer 11 primary key auto increment

2. name, varchar 25

3. address, varchar 100

4. phone, varchar 20

5. username, varchar 10 unique

a common operation to insert data is :

Checking for data, ex :

$check_data = mysql_query("select * from A where username =’$user’");
if(mysql_num_rows($check_data)>0) {
do update data
mysql_query("update A set bla..bla..bla");
}
else {
insert query here
mysql_query("insert into A bla..bla..bla");
}

after find "replace()"

mysql_query("replace into A (field1,field2)  values(‘values1′,’values2′)")

Yes, I still need to do research for another ‘glitch’ possibility in current application.

From mysql manual :

As long index exist for that table, like primary key or unique for helping mysql to find duplicate data or not.

Yes, replace don’t insert please 

My friend install serendipity (blog engine) in his laptop with mysql version 4.1.21 and dumping sql of his blog. When I try to restore sql file in his PC I got error message :

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

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

C:\AppServ\mysql\bin>mysql -uroot -p alam < blog.sql
Enter password:
ERROR 1064 at line 21: You have an error in your SQL syntax. Check the manual t
hat corresponds to your MySQL server version for the right syntax to use near ‘DEFAULT CHARSET=latin1′ at line 12

C:\AppServ\mysql\bin>

Then I realize that mysql server in my friend pc using 4.0.16, DEFAULT CHARSET=latin1 is everywhere in his sql file , this part not needed in mysql 4.0.16

Quick Solution is delete that part.

Still open sql file in wordpad, I choose replace, find DEFAULT CHARSET=latin1 with nothing . Press ‘Replace All’ button. Save the file and try to restore new sql file.

Success

Simple way to insert time information in mysql is using now()

now() will automatically insert appropriate depends on type, let say we have field in mysql database in these format :

1. birth (date), now() will insert current date, ex: 2007-05-02

2. birth (datetime), now() will insert current date + current time, ex : 2007-05-2 22:54:30

3. birth (time), now() will insert only time information, ex : 22:54:30

this snippet will get same result with now()

$birth=date(‘Y’).’-’.date(‘m’).’-’.date(‘d’); // will be same as 2007-05-02

example in mysql command line :

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

C:\Documents and Settings\Administrator>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 1 to server version: 4.0.24-nt

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

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test;
Database changed
read more »

“Have good SQL query standards”

Gee, seem easy to say but often difficult to deploy.

Many people tend to use abstraction layer like adodb, pear, ezsql etc . They have coding convention and good manual.

Portable SQL will lead on good application, seamless integration with other DB.

How about You?