Jun 18

mysql – how to stop infinite loop in mysql trigger

i have met such situation:two triggers on two talbes,which operate on each other,lead to circular dependencies.sometimes it may get mysql query failed:

Can’t update table ‘v9_member’
in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Source code    
delimiter //
DROP TRIGGER IF EXISTS in_bbs_cms //
CREATE TRIGGER `in_bbs_cms` AFTER INSERT ON `ucenter_members`
FOR EACH ROW BEGIN
	INSERT INTO `ucenter_members` (username, password, email, regip, regdate, random, ucuserid) VALUES(NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.salt, NEW.uid);
END
//
 
-- FOR NEW RECORD
DROP TRIGGER IF EXISTS in_cms_bbs //
CREATE TRIGGER `in_cms_bbs` AFTER INSERT ON `v9_member`
FOR EACH ROW BEGIN
	INSERT INTO v9_member (username, password, email, regip, regdate, salt) VALUES (NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.encrypt);
END
//

 

to avoid this problem, we need to make use of mysql global variable as a flag to trigger.

Source code    
delimiter //
DROP TRIGGER IF EXISTS in_bbs_cms //
CREATE TRIGGER `in_bbs_cms` AFTER INSERT ON `ucenter_members`
FOR EACH ROW BEGIN
	IF @TRIGGERED = NULL THEN
		SET @TRIGGERED = TRUE;
		INSERT INTO `ucenter_members` (username, password, email, regip, regdate, random, ucuserid) VALUES(NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.salt, NEW.uid);
		SET @TRIGGERED = NULL;
	END IF;
END
//
 
-- FOR NEW RECORD
DROP TRIGGER IF EXISTS in_cms_bbs //
CREATE TRIGGER `in_cms_bbs` AFTER INSERT ON `v9_member`
FOR EACH ROW BEGIN
	IF @TRIGGERED = NULL THEN
		SET @TRIGGERED = TRUE;
		INSERT INTO v9_member (username, password, email, regip, regdate, salt) VALUES (NEW.username, NEW.password, NEW.email, NEW.regip, NEW.regdate, NEW.encrypt);
		SET @TRIGGERED = NULL;
	END IF;
END
//

 

Oct 31

mysql — Can’t connect to local MySQL server

All website in the server can not work. All of them throw out error message as below:
Can’t connect to local MySQL server

i tried to restart mysql server,but it can not be restarted.
well,i need to check the mysql log file.well,i got the problem:

14:03:00 [ERROR] /usr/sbin/mysqld: Disk is full writing ‘./mysql-bin.000047′ (Errcode: 28). Waiting for someone to free space… (Expect up to 60 secs delay for server to continue after freeing disk space)

Source code    
df -alh

i found that the /var partition is full,which is used to store mysql process file.so no new connection can be established. and you even cannot restart the mysql server.as restart mysql server also need to create process file in this partion

so the only thing to do is to free disk space, clean log file,remove uneeded backup file

Source code    
/etc/init.d/mysqld restart

 

 

 

Oct 29

mysql – Error in accept: Too many open files

One server is running both mysql and apache together,but the mysql get down every four hours.From PHP,it’s said ‘Error establshing a database connection’.From mysql error log,i got the some records like below:

’121029  2:47:38 [ERROR] Error in accept: Too many open files’

i know that mysql server has a config directive of open_files_limit which limit the number of file descripteors mysqld can use.so i need to check what the value in use for the current running mysqld server,from mysql client console,

Source code    
mysql -u root -p
>show global varaibles like 'open%'
>
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1185  |

You can also check the config with following command:

Source code    
# netstat -atnp|grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      5596/mysqld
 
# cat /proc/5596/limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            10485760             unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             200704               200704               processes
Max open files            1185                 1185                 files
Max locked memory         32768                32768                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       200704               200704               signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0

so now we need to adjust mysql server configuration to make it feel comfortalbe.You decide the open_files_limit value by considering following factor:the databases amount, the max_connections and the table_cache value. You can also increase the number to a little higher you think is appropriate,then tune it by mysql server running status.

Source code    
# vim /etc/my.cnf
[mysqld]
open_files_limit = 3000
 
save to quit and then restart msyql server
#/etc/init.d/mysqld restart
Aug 18

zen cart — recover zen cart database from mysql data folder

One of my server crashed last week. They do a hard disk rescue and i only get a copy of the mysql data folder(the physical data file of all databases).The new server is ready for use,with the mysql and cpanel updated.so i got no lucky to recover with copy the mysql folder back to the new mysql data folder.it only show the database without any table.

i found i do have a mysqldump backup on 2012 -05 -24.but the lates data(2012-08-09) was stored as mysql physical data file,not sql.how to recover all the zen cart databases with the latest data?is it possible.

There is two way to try:

The first one is to install anthoer copy of mysql.it must be same version as the one before system crashed.and same /etc/my.cnf. The you can turn off the currently running mysql ,copy the the mysql data folder to the data folder of the the old copy mysql.Start the mysql and mysqldump to backup all the zen cart database. The switch mysql to the latest mysql,import all databases;

Sometimes it’s impossible to achive the last version of mysql with same configuration.The what can we do?You can mysqldump the database schema(only structure) from a zen cart database with following command:

mysqldump –no-data zencartdb > onlyschema.sql -u root -p’password’

Then create all the databases you want to recover and import the zen cart database schema to every zen cart database.

mysqladmin create database1 -u root -p’password’

mysql database1 < onlyschema.sql -u root -p’password’

Now turn of mysql dameon process to unlock all databases and tables(if exist).copy the data folder bakup to the mysql data folder.

/etc/init.d/mysqld stop
cp -Rf  /home/bak/mysql/*  /var/lib/mysql/

And all is done. However,keep in mind if you use INNODB storage engine in your old mysql database,then the recovery may be more complicated.

Aug 15

msyql — msyqldump only dump the database schema

mysqldump can dump whole database including both data and its schema(structure).what if you only need to make a backup of the database schema.To backup only the database structure,you can use the following msyqldump command line option –no-data,for example:

Source code    
msyqldump -u root -p --no-51&q=DATA&lr=lang_en">data database1 > db_schema.sql

This will only do a backup of structure of the database database1.

Aug 13

mysql -MySQL root password in DirectAdmin

If you got a server running DirectAdmin without mysql root info. how to get root access to mysql. In fact,mysql root password is set within the DirectAdmin installation process and written down to setup.txt(usually located in /usr/local/directadmin/scripts/setup.txt). If you didn’t change this password you can find the default one in the setup.txt, use the command below to see the contents of this file:
less /usr/local/directadmin/scripts/setup.txt

your password is stored in the line starting with mysql=

If you want to access mysql from ssh or console directly create a file in /root/ dir called .my.cnf:

touch /root/.my.cnf

and edit this file with the vi editor:

vi /root/.my.cnf

Add the lines below:

[client]
user=root
password=XXXXXX

Fill the password with the pass you got from setup.txt, save the file with SHIFT+ZZ or :wq and exit. After this step just write mysql to enter MySQL console directly without password.
[root@test ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 169833
Server version: 5.0.77-log MySQL Community Edition (GPL)

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

mysql>

Aug 12

mysql — create database with mysql client command line option

mysql client is powerful with its shell like console.what about to create database or mysql user from command line option if you don’t want to log into mysql console.the mysql -e option can help with this.To create database , we can issue the following command:

mysql -u mysql_user -p mysql_pass -e “CREATE DATABASE db1;”

To create mysql user from command line option -e:

mysql -u mysql_user -p mysql_pass -e “GRANT ALL PRIVILEGES ON db1.* TO ‘db1_user’@'localhost’ IDENTIFIED BY ‘db1_pass’;”

with the help of -e option,we can batch create database and mysql user in our script.i think that’s why mysql client program support this option.

Aug 02

zen cart — Can’t create/write to file ‘/tmp/ (Errcode: 30)

one zen cart website’s front page got corrupted,the error message looks like:
Can’t create/write to file ‘/tmp/#sql_ .MYI’ (Errcode: 30)
i tried to restart mysql server with

Source code    
/etc/init.d/mysqld restart

but the problem still no solved

i found that the zen cart backend home page is ok from browse.so i tried to repair the table according to the error message,but still no.

after a while search,i got the solution as below:

It is likely that your /usr/tmpDSK is corrupted. You should stop all processes that have files open on /tmp such as MySQL and other applications using:

lsof /tmp

Run the following commands (in that order):

Source code    
/bin/umount -l /tmp    #umount tmp
/bin/umount -l /var/tmp # umount /var/tmp
/bin/rm -fv /usr/tmpDSK # remove /usr/tmpDSK

/scripts/securetmp  #recreate
This should create a new /tmp partition for you. That’s all .This will take a while to run.and keep in mind that only server with Cpanel  have  /scripts/securetmp script.You will have to recreate yourself if you are not running Cpanel.
Someone says restart the system sometimes fixes the problem.

FYI:

http://www.zen-cart.com/showthread.php?66249-Can-t-create-write-to-file-tmp-%28Errcode-30%29/page2

Apr 23

mysql – how to check if table existed with php

One of our zen cart website throw out an error after system crashed.The error message is something as below:

abc.configuration does not exist (abc is the db name)

i logged into mysql console with

mysql -u root -p

then i issue those command in the mysql console

use abc;
show tables;

The configuration table is showed in the result list.When I browse to the phpmyadmin page,i found the configuration is missing.This make me confused.I thought the configuration table may be corrupted.so it’s not shown in phpmyadmin and any sql query on it will result in error.that’s why the zen cart website got 404 error page.

I usually check the table existence with following php function which based upon ‘show table’ query.

function table_exists($link,$db,$table) {

if (empty($table)) {
die('No table name provided to check');
}
$tables = array();
$sql = "SHOW TABLES FROM $db";
$result = mysql_query($sql, $link);

if (!$result) {
echo 'DB Error, could not list tables,' . mysql_error();
exit;
}

while ($row = mysql_fetch_row($result)) {
$tables[] = $row[0];
}
return in_array('configuration', $tables);
}

it is obviously that the ‘show tables’ query will lead to error when a table is corrupted.so is there better way to check?After some seconds i got solution from google.Below is the php function to check the existence of a table.It worked upon MySQL information(the information_schema database).

function table_exists($tablename, $link, $database = false) {

if(!$database) {
  $res = mysql_query("SELECT DATABASE()", $link);
  $database = mysql_result($res, 0);
}

$res = mysql_query("SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = '$database' AND table_name = '$tablename'", $link);

return mysql_result($res, 0) == 1;
}

i thought this would be better way to go.