Category Archives: Database

Database

mysql error : Can’t create/write to file ‘/tmp/#sql_1581_0.MYI’ (Errcode: 13)

December 12, 2010 – 4:13 am

Recent PHP downgrade to 5.2.10 also bring another problem with phpmyadmin. When I try to browse data it work but when I try to see table structure I have this error :

Can’t create/write to file ‘/tmp/#sql_1581_0.MYI’ (Errcode: 13)

mysql error mysql error : Cant create/write to file /tmp/#sql 1581 0.MYI (Errcode: 13)

Being curios if the problem caused by phpmyadmin I try shell way :

# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 106
Server version: 5.1.41-3ubuntu12.8 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_1581_0.MYI’ (Errcode: 13)
mysql>

Ups, seem same problem.

Looking log data

# tail -f /var/log/mysql/error.log

101217  0:10:02 [Note] /usr/sbin/mysqld: Shutdown complete

101217  0:10:02 [Note] Plugin ‘FEDERATED’ is disabled.
/usr/sbin/mysqld: Can’t create/write to file ‘/tmp/ibLwkhPZ’ (Errcode: 13)
101217  0:10:02  InnoDB: Error: unable to create temporary file; errno: 13
101217  0:10:02 [ERROR] Plugin ‘InnoDB’ init function returned error.
101217  0:10:02 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
101217  0:10:02 [Note] Event Scheduler: Loaded 0 events
101217  0:10:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: ’5.1.41-3ubuntu12.8′  socket: ‘/var/run/mysqld/mysqld.sock’  port: 3306  (Ubuntu)

I guess its permission problem and quick solution for the problem.

# ls -l / | grep tmp

drwxr-xr-x   4 root root  4096 2010-12-17 00:10 tmp
#

# chmod 777 /tmp

Recheck with shell

# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 112
Server version: 5.1.41-3ubuntu12.8 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
+———————–+———————————–+——+—–+———+——-+
| Field                 | Type                              | Null | Key | Default | Extra |
+———————–+———————————–+——+—–+———+——-+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum(‘N’,'Y’)                     | NO   |     | N       |       |
| Insert_priv           | enum(‘N’,'Y’)                     | NO   |     | N       |       |
| Update_priv           | enum(‘N’,'Y’)

more …………

great.

Tags | Permalink | Comments Off
Database

Can’t locate Compress/Zlib.pm

October 7, 2008 – 12:47 am

edp# perl /usr/local/sbin/mysqlblasy.pl
Can’t locate Compress/Zlib.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.8/BSDPAN

/usr/local/lib/perl5/site_perl/5.8.8/mach /usr/local/lib/perl5/site_perl/5.8.8

/usr/local/lib/perl5/site_perl /usr/local/lib/perl5/5.8.8/mach /usr/local/lib/perl5/5.8.8 .) at

/usr/local/lib/perl5/site_perl/5.8.8/Archive/Zip.pm line 11.

BEGIN failed–compilation aborted at /usr/local/lib/perl5/site_perl/5.8.8/Archive/Zip.pm line  11.
Compilation failed in require at /usr/local/sbin/mysqlblasy.pl line 1340.
BEGIN failed–compilation aborted at /usr/local/sbin/mysqlblasy.pl line 1340.

Solution :

edp# cd /usr/ports/archivers/

edp# cd p5-Compress-Zlib && make install
edp# rehash

Try again :

edp# perl /usr/local/sbin/mysqlblasy.pl

edp# cd /data/db2/
edp# ls
localhost_2008_10_06-16_15_30.tar.gz
edp#
Yes, it works icon smile Cant locate Compress/Zlib.pm

Tags | Permalink | Comments Off
Database

MYSQL server has gone away

August 12, 2008 – 2:52 pm

Its very rare case when I see message :

“MYSQL server has gone away” , a few things to consider are :

* Check variable max_timeout
* Check variable max_allowed_packet

These setting available in my.ini or my.cf file, edit the value and restart mysql daemon.

Example :

max_allowed_packet=16M

max_timeout=28800

Try again to import database using mysql command and see the different icon smile MYSQL server has gone away

Tags | Permalink | Comments Off
Database

Unknown SMSD service type (“MYSQL”)

July 17, 2008 – 2:33 pm

That message show up when I install gammu from source in Ubuntu server 8.04 :

root@dns:/home/edpteam# gammu –smsd MYSQL smsdrc
Unknown SMSD service type (“MYSQL”)

After dig in mailing list I have clue to install mysql library :

apt-get install libmysqlclient15-dev

apt-get install libmysqlclient15-dev
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed

and are no longer required:
debhelper intltool-debian po-debconf libgammu-common

gettext libgammu3
libbluetooth2 libmm14 html2text
Use ‘apt-get autoremove’ to remove them.
The following extra packages will be installed:
zlib1g-dev
Suggested packages:
mysql-doc-5.0
The following NEW packages will be installed:
libmysqlclient15-dev zlib1g-dev
0 upgraded, 2 newly installed, 0 to remove and 10 not

upgraded.
Need to get 7375kB of archives.
After this operation, 21.1MB of additional disk space

will be used.
Do you want to continue [Y/n]? y
Get:1 http://us.archive.ubuntu.com hardy/main

zlib1g-dev 1:1.2.3.3.dfsg-7ubuntu1 [160kB]
Get:2 http://us.archive.ubuntu.com hardy-updates/main

libmysqlclient15-dev 5.0.51a-3ubuntu5.1 [7214kB]
Get:3 http://us.archive.ubuntu.com hardy-updates/main

libmysqlclient15-dev 5.0.51a-3ubuntu5.1 [7214kB]
Fetched 2897kB in 50min1s (965B/s)
Selecting previously deselected package zlib1g-dev.
(Reading database … 39442 files and directories

currently installed.)
Unpacking zlib1g-dev (from

…/zlib1g-dev_1%3a1.2.3.3.dfsg-7ubuntu1_i386.deb) …
Selecting previously deselected package

libmysqlclient15-dev.
Unpacking libmysqlclient15-dev (from

…/libmysqlclient15-dev_5.0.51a-3ubuntu5.1_i386.deb)

…
Setting up zlib1g-dev (1:1.2.3.3.dfsg-7ubuntu1) …
Setting up libmysqlclient15-dev (5.0.51a-3ubuntu5.1)

…

Processing triggers for libc6 …
ldconfig deferred processing now taking place
root@dns:/home/edpteam#

Recompile gammu to detect libmysqlclient and try again :

root@dns:/etc# gammu –smsd MYSQL /etc/smsdrc

Yes, this time no more error message icon smile Unknown SMSD service type (MYSQL)

Tags | Permalink | Comments Off
Database

Stop GAMMU smsd (27)

June 9, 2008 – 12:28 am

C:\gammu2>gammu –smsd MYSQL smsdrc
Log filename is “smsdlog”
Stop GAMMU smsd (27)

Fri 2008/06/06 11:06:10 : Stop GAMMU smsd (27)
Fri 2008/06/06 11:06:53 : Start GAMMU smsd
Fri 2008/06/06 11:06:53 : PIN code is “1234″
Fri 2008/06/06 11:06:53 : commtimeout=1, sendtimeout=10, receivefrequency=0, resetfrequency=0
Fri 2008/06/06 11:06:53 : deliveryreport = no
Fri 2008/06/06 11:06:53 : phoneid =
Fri 2008/06/06 11:06:53 : No table for sent sms: Table ‘sentitems’ is marked as crashed and should be repaired

Fri 2008/06/06 11:06:53 : Stop GAMMU smsd (27)

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

C:\Documents and Settings\edp>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 4607
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> use sms2;
Database changed
mysql> repair table sentitems;
+—————-+——–+———-+———-+
| Table          | Op     | Msg_type | Msg_text |
+—————-+——–+———-+———-+
| sms2.sentitems | repair | status   | OK       |
+—————-+——–+———-+———-+
1 row in set (0.05 sec)

mysql>

Don’t have idea why Table ‘sentitems’ is marked as crashed.

Tags , , | Permalink | Comments Off
Database

MySQL and Null

May 16, 2008 – 3:29 pm

Just for my own record, dealing with Null type field.

Checking with :

select * from tablename where field1=”  -> this one not work

select * from tablename where field1=’Null’ -> same too

is null is the answer

select * from tablename where field1 is null

Save my day, thanks to St4r icon smile MySQL and Null

Tags , | Permalink | Comments Off
Database

Mysql Trigger Tutorial and Tools

May 9, 2008 – 3:42 pm

Deal with gammu and mysql make learn more about mysql feature in mysql 5.0 called trigger.

Books always good to start with so I choose MySQL Stored Procedure Programming book to start with but others might help to for someone looking for practical solution in cookbook form etc :

  1. High Performance MySQL: Optimization, Backups, Replication, and More Mysql Trigger Tutorial and Tools
  2. MySQL Cookbook Mysql Trigger Tutorial and Tools
  3. MySQL Stored Procedure Programming Mysql Trigger Tutorial and Tools
  4. MySQL in a Nutshell (In a Nutshell (O’Reilly)) Mysql Trigger Tutorial and Tools
  5. MySQL Administrator’s Bible (Bible (Wiley)) Mysql Trigger Tutorial and Tools
  6. Understanding MySQL Internals Mysql Trigger Tutorial and Tools

Looking around in google make me find some interesting links around trigger in mysql, here the list :

Wew, a lot of example but for deliver due project I’ll use cron. Once I get the point I’ll try out trigger icon smile Mysql Trigger Tutorial and Tools

Tags , | Permalink | Comment (1)
Database

Postfix and LOCK tables

April 1, 2008 – 11:45 pm

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 icon smile Postfix and LOCK tables

Tags , | Permalink | Comments Off
Database

MySQL date range, case study

January 25, 2008 – 11:26 am

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 icon smile MySQL date range, case study

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.

Permalink | Comments Off
Database PHP

Export picture from mysql database to file

December 11, 2007 – 4:51 am

[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 »

Tags , , | Permalink | Comments (5)

Next Page »

Next Page »