Postfix and LOCK tables

just found error in /var/log/messages and when I try to backup postfix database.

mail# mysqldump -upostfix -p postfix > postfix.sql
Enter password:
mysqldump: Got error: 1044: Access denied for user ‘postfix’@’localhost’ to database ‘postfix’ when using LOCK TABLES

checking user privileges using information_schema

mail# mysql -upostfix -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1473
Server version: 5.0.51a-log FreeBSD port:

mysql-server-5.0.51a

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

mysql> select * from `information_schema`.`USER_PRIVILEGES`;

+———————–+—————+————-

—+————–+
| GRANTEE               | TABLE_CATALOG |

PRIVILEGE_TYPE | IS_GRANTABLE |
+———————–+—————+————-

—+————–+
| ‘postfix’@’localhost’ | NULL          | USAGE       

  | NO           |
+———————–+—————+————-

—+————–+
1 row in set (0.00 sec)

mysql> exit

Bye

mail# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1479
Server version: 5.0.51a-log FreeBSD port:

mysql-server-5.0.51a

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

mysql> grant all privileges on *.postfix to postfix@localhost identified by ‘fxxxxxyyyyy8’ with grant option;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘postfix to postfix@localhost identified by ‘fxxxxxyyyy8′ with grant option’ at line 1

mysql> grant all privileges on *.* to postfix@localhost identified by ‘fxxxxxyyyyy8’ with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mail# mysqldump -upostfix -p postfix > postfix.sql

Enter password:

mail# postfix reload

postfix/postfix-script: refreshing the Postfix mail system

Looks fine 🙂

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)

read more »

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.

read more »

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.