Mysql cache timeout

Important info I've found today:

MySQL's query cache destroys the entire cache for a given table whenever that table is changed. On a high-traffic site with updates happening many times per second, this makes the the cache practically worthless. In fact, it's often harmful to have it on, since there's a overhead to maintain the cache.

source
soon more about caching ;)

Mysql Can't create table (errno: 150)

Ok so we get this error when creating foreign key . So what now ?

create table users (
id_user int(11) unsigned not null primary key auto_increment, 
name varchar(64)
)Engine=InnoDB;

insert into users set name='Admin';


create table test_relation (
id int(11) unsigned NOT NULL primary key AUTO_INCREMENT, 
id_user int(10)  not null ) 
engine=InnoDB;

alter table test_relation add foreign key(id_user) references users(id_user);

Can't create table 'test.#sql-440_ba' (errno: 150)

This message says complete nothing ; ) To get more detailed information use

mysql> show innodb status;

and locate "LATEST FOREIGN KEY ERROR" part.

120717 15:39:19 Error in foreign key constraint of table test/#sql-440_ba:
foreign key(id_user) references test.users(id_user):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

Yeah, so we got info that there is something wrong with column types. Quick debug:

mysql> alter table test_relation modify  id_user  int(10) unsigned not null;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test.test_relation add foreign key(id_user) references test.users(id_user);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

and voila works perfect ; )

Sphinx part 3 - Php api

Ok this is third and probably the last part about sphinx search engine. So I'm gonna show how to use sphinx api with php. I'm gonna use the same database as in previous tutorials (articles)

mysql> select * from articles;
+------------+------------------------+-----------------------------------------+---------+
| id_article | title                  | content                                 | ratings |
+------------+------------------------+-----------------------------------------+---------+
|          1 | Euro 2012 in Poland    | some content about euro 2012            |       5 |
|          2 | Second article         | bla dog cat house                       |       2 |
|          3 | Euro 2012 last results | Poland loses to czech republic          |       4 |
|          4 | Fourth article         | lorem ipsum black yellow red blue green |       1 |
+------------+------------------------+-----------------------------------------+---------+

and config : sudo vim /etc/sphinxsearch/articles.conf

source src_articles
{
        type            = mysql
        sql_host        = localhost
        sql_user        = root
        sql_pass        = krzysiek2000
        sql_db          = test

        sql_attr_uint = ratings


        sql_query =  select id_article,title,content,ratings from test.articles;
        sql_query_info = select id_article,title,content,ratings 
from test.articles where id_article = $id;
}


index index_articles
{
        source = src_articles
        path = /var/lib/sphinxsearch/data/index_articles
        docinfo = extern
        charset_type = utf-8
}

indexer
{
    mem_limit = 32m
}

searchd
{

        port  = 3312
        log = /var/log/searchd/searchd.log
        query_log = /var/log/searchd/query.log
        pid_file = /var/log/searchd/searchd.pid

}

First index data:

indexer -c /etc/sphinxsearch/articles.conf index_articles

Let's start the searchd deamon

sudo searchd -c /etc/sphinxsearch/articles.conf

Check is it working :

ps aux | grep search
root      4366  0.0  0.0  14384  1020 pts/1    S    23:37   0:00 searchd -c /etc/sphinxsearch/articles.conf

Great, so quick test :

search -c /etc/sphinxsearch/articles.conf Poland
displaying matches:
1. document=1, weight=1557, ratings=5
 id_article=1
 title=Euro 2012 in Poland
 content=some content about euro 2012
 ratings=5 
2. document=3, weight=1557, ratings=4
 id_article=3
 title=Euro 2012 last results
 content=Poland loses to czech republic
 ratings=4

Everything seems to be fine ;) So now let's do the same search but we gonna use php Use can find sphinxapi.php file on offical site http://sphinxsearch.com/downloads/release/ Just download the source package. There is also api to ruby,python and java.

SetServer('localhost', 3312);
$client->SetConnectTimeout(1);
$client->SetArrayResult(true);

// Query the index
$results = $client->Query('Poland');

//output the result 
print_r($results['matches']);

?>

php articles.php 
Array
(
    [0] => Array
        (
            [id] => 1
            [weight] => 1
            [attrs] => Array
                (
                    [ratings] => 5
                )

        )

    [1] => Array
        (
            [id] => 3
            [weight] => 1
            [attrs] => Array
                (
                    [ratings] => 4
                )

        )

)

Perfect ! So now when we have the results we can foreach on array to get details data

foreach($results['matches'] as $i=>$arr){

    $id_article = $arr['id'];
    //select data about specific article
}

And that's all Of course what I show is just a little percentage of what offers you sphinx. If you are interested in the topic, ask uncle google about more information ; )

How to check linux ports

So here's quick tip. How to find out what process has which port. First logon root and to open list port use :

lsof -i

or use netstat

netstat -tulpn

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:1551            0.0.0.0:*               LISTEN      2443/sh         
tcp        0      0 0.0.0.0:1553            0.0.0.0:*               LISTEN      2445/kadu       
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1739/cupsd      
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1261/postgres   
tcp        0      0 0.0.0.0:25              0.0.0.0:*               LISTEN      1687/master     
tcp        0      0 0.0.0.0:9306            0.0.0.0:*               LISTEN      1201/searchd    
tcp        0      0 0.0.0.0:9312            0.0.0.0:*               LISTEN      1201/searchd    
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1232/mysqld     

To se what processs is bound to let say 3305:

root@work:~# lsof -i :3306

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1250 mysql   10u  IPv4   5421      0t0  TCP localhost:mysql (LISTEN)

Now let's take a closer look at proces id (PID)

root@work:~# ls -l /proc/1250/exe
lrwxrwxrwx 1 root root 0 2012-06-25 19:18 /proc/1250/exe -> /usr/sbin/mysqld

And that's all ; )