Tag Archives: mysql

mysql – Can’t create/write to file ‘/tmp/

 

[email protected]:~# mysqldump -u root --all-databases > colo18.sql
mysqldump: Couldn't execute 'show fields from `crongal`': Can't create/write to file '/tmp/#sql_b8c_0.MYI' (Errcode: 13) (1)

Lets set the proper permissions to /temp

sudo chmod 1777 /tmp

It will solve the issue.

Resources:

MySQL vs/and/plus/more JSON

Here are a few things that we can use / read about MySQL and JSON.

Small benchmark tests of reading 20000

Krasimir Tsonev has made a small benchmark tests of reading 20000 from a MySQL db and from a JSON file.

I’m gonna just put the *end* results. You can read all the article on Krasimir’s blog about MySQL vs JSON file data storing benchmark results.

MySQL

ab -n 30 -c 30 http://localhost/mqsql.php
Concurrency Level:      30
Time taken for tests:   30.518 seconds

 

JSON

ab -n 30 -c 30 http://localhost/json.php
Concurrency Level:      30
Time taken for tests:   3.384 seconds
MySQL query to JSON
username email
mike [email protected]
jane [email protected]
stan [email protected]

The MySQL query

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

The JSON result

[
     {username:'mike',email:'[email protected]'},
     {username:'jane',email:'[email protected]'},
     {username:'stan',email:'[email protected]'}
]

Got this from http://www.thomasfrank.se/mysql_to_json.html.

 

 

 

 

 

 

Simply backups under Linux

Backup a folder

tar -cvpzf /BackupDirectory/backupfilename.tar.gz /ImportantData/directory/path

c =  Create
v =  Verbose mode
p = Preserving Files and Directory Permissions.
z = This will tell tar that compress the files further to reduce the size of tar file.
f =  It is allows to tar get file name.

Backup a MySQL database

mysqldump --opt -u [username] -p[password] [dbname] > [dbname.sql]

Backup all MySQL databases

mysqldump -u root -p --all-databases > all_databases.sql

ssdvirtual

“MySQL server has gone away” with CodeIgniter and MySQL

I’v stopped using CodeIgniter for 7 months, but I maintaining a product that was developed under CI.

rock ssd

Today I’v got a error saying  “MySQL server has gone away” after a long time PHP execution.
I’v managed to solve it by, on the model that was getting me the error, *reconnect* to the database .

Before the *command* i’v added $this->db->reconnect();

 $this->db->reconnect();
 $this->db->insert('photoGallery', $data);
 return $this->db->insert_id();

Hope it helps anyone out there.

MySQL: delete a row ignoring foreign key constraint

Cannot delete or update a parent row: a foreign key constraint fails 
DELETE IGNORE FROM `database`.`table` WHERE `table`.`tableId` = 16 LIMIT 1;

The IGNORE keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings.

http://dev.mysql.com/doc/refman/5.0/en/delete.html

WordPress – find and replace

I got some wordpress blogs (20+) running on some servers, and from time to time, i need to make some FIND&REPLACE on my posts…

This is the simple way! Directly on mysql.

UPDATE wp_posts SET post_content = REPLACE (  
post_content,  
'Item to replace here',  
'Replacement text here');

Please note the for security reasons the prefix wp_ is diferent… if you play by the rules you will need to see witch is is… wp-config.php has it.

mysql> UPDATE wpspt_posts SET post_content = REPLACE (
-> post_content,
-> 'static.mycdn.com/banners/',
-> 'static.mycdn.com/bnnrs/');
Query OK, 1477 rows affected (0.42 sec)
Rows matched: 10795 Changed: 1477 Warnings: 0

This is a small hack to ABP – Ad Blocker Plus…
Horray!