MySQL JSON UDF (Installation)

MySQL JSON UDF is (UDF - User Defined Function ) SQL function which perform query on JSON data.

Why needed this
-------------------------
We have lots of records that is in JSON format.We can use MySQL for storing it.But to process (Read/Write/Manipulate/Parse) data we either needed to build some library in PHP;i.e. not going to perform well for large data(Personal Perception).

So in my search i come to know about this MySQL library(UDF) that provides same feature to process JSON data;i.e. going to be faster in respect to some library i.e. builded on PHP(Personal Perception)

Instatllation Step
--------------------------

(
This not includes compilation part.
Machine is x64 and MySQL version is 5.6.17
)

1)It can be downloaded from

http://labs.mysql.com/

(As warned in that page not fit for production.So use it on production on your own risk)

Or

wget 'http://downloads.mysql.com/snapshots/pb/mysql-json-udfs-0.3.2/mysql-json-udfs-0.3.2-labs-json-udfs-linux-glibc2.5-x86_64.tar.gz'


2)tar -xzf  mysql-json-udfs-0.3.2-labs-json-udfs-linux-glibc2.5-x86_64.tar.gz

3)It is better to read README file inside extracted directory

4)Copy so file to the plugin directory

cp libmy_json_udf.so /usr/lib/mysql/plugin/.

5)Login into mysql.Execute following query

        CREATE FUNCTION json_valid RETURNS integer SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_search RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_extract RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_replace RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_append RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_remove RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_set RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_merge RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_safe_merge RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_deep_merge RETURNS string SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_contains_key RETURNS integer SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_depth RETURNS INTEGER SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_count RETURNS INTEGER SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_version RETURNS STRING SONAME 'libmy_json_udf.so';
        CREATE FUNCTION json_test_parser RETURNS string SONAME 'libmy_json_udf.so';

6)Test it

>SELECT json_valid('{"a":1}') ;
+-----------------------+
| json_valid('{"a":1}') |
+-----------------------+
|                     1 |
+-----------------------+

MySQL Multi-Master Single Slave Replication (via federated slave)

In my search of finding solution for that i come to know about this possibility that is federated slave.

For more reading

http://www.fromdual.com/sites/default/files/mm-single-slave-repl.pdf

http://chintakiran.blogspot.in/2011/10/multi-master-single-slave-for-mysql.html

Manually Setup WAMP on local PC

Note: If you want no pain of installing them seperately and configuring them manually.You can try EasyPhp/Xampp

1: Download apache
http://httpd.apache.org/download.cgi
Install it(As normally doing this other softwares)

2: Download php
http://windows.php.net/download/ (copy zip package that i done in my setup)
Extract it
copy that folder into c: and rename that as c:/php (If you not done this or copy it somewhere else then Configure httpd.conf as per your location)

3: Download MySQL from
http://dev.mysql.com/downloads/mysql/ (As per system Configuration)
Install it.


4:  Dont forget to add these lines in httpd.conf( For reference : http://www.php.net/manual/en/install.windows.apache2.php )
---------------------------------------------
LoadModule php5_module "c:/php/php5apache2_2.dll"
AddHandler application/x-httpd-php .php
PHPIniDir 'c:\php\'

5: Restart Apache

6: Go to Apache Directory then /htdocs
Create a simple file
index.php


phpinfo();
?>

7: Try to run it http://127.0.0.1/index.php (I configure this on local laptop so i used 127.0.0.1.Inplace of 127.0.0.1 you can use your ip server name etc)

Imagick Configuration (It is not mandory) 
Imagick configuration for php in windows


Follow this document 

http://www.elxsy.com/2009/07/installing-imagemagick-on-windows-and-using-with-php-imagick/


In Simplified Terms
1: Download and Install Imagemagick(Q-16)
2: Download php_imagick.dll and copy it into ext directory of php
3: Add this line in php.ini file (php.ini-production)
If it is  commented (; line start with semicolon)
Or there is no entry of this line in php.ini


extension=php_imagick.dll

Emulating analatic

For example you are working on a "user" table in mysql with following fields user_id,client_id,phone_no

Every day you assigned a new client id to the user phone_no.For any time when u inserting that data into new table you are taking only that user_id which client id is max for that phone_no.

Now in your table current data is

user_id|client_id|phone_no
-----------------------------
17|1013|9876543
1017|5013|9876544
10017|3013|9876543
100017|4013|9876543
1000017|13|9876544

now when you want to use this data for inserting into new table.

You need to calculate max user id for the phone no which client id is max.
It means

when u process for phone no 9876543

then you have to used user_id 100017 either it is 17,10017,100017

as well as if you process for phone_no 9876544

then you should use user_id 1017 either it is 1017,1000017 because for 1017 client_id max.

You are not going to do this manually

The part of query that gives you max user_id for all phone_no's max client id is called Emulating analatic(AKA ranking)

select user_id,client_id,phone_no from user as t1 where (select count(*) from user as t2 where t2.client_id>t1.client_id and t1.phone_no=t2.phone_no)=0)

user_id|client_id|phone_no
1017|5013|9876544
100017|4013|9876543


and after you can inner join this with user on phone_no and take user_id from user table and max user id is the user_id for that phone_no from Emulating analatic(AKA ranking).

Final query is

select user.user_id as user_id,temp.user_id as max_user_id from user inner join (select user_id,client_id,phone_no from user as t1 where (select count(*) from user as t2 where t2.client_id>t1.client_id and t1.phone_no=t2.phone_no)=0)temp on temp.phone_no=user.phone_no

And you got
user_id|max_user_id
17|100017
1017|1017
10017|100017
100017|100017
1000017|1017

Now for any other table when u want to insert the user_id you need to insert max_user_id for the corrosponding user_id.

Capitilize the first word of string and lowering other words in mysql

For example you have a table user_info in which user_name is a field and you want to capatilize the first word of user_name and other words in lower case then

Then you can do this by using mysql inbuild funcitons

CONCAT() --> For concating the string

UCASE() -->Changing string into upper case

LCASE() --> Changing String into lower case

LEFT() --> You can defined the words(substring) you want starting from left

RIGHT() -->  You can defined the words(substring) you want starting from right

LENGTH() --> You can find the length of string

Now the query

select CONCAT(UCASE(left(user_name,1)),LCASE(right(user_name,length(user_name)-1))) from user_info;

If previous output is

sAndeep

singh

BISHT

then the output became

Sandeep

Singh

Bisht

How to handle php-mysql connection timeout?

How to increase time period of mysql connection timeout through php.Or how to handle php-mysql connection time out.

By default php-mysql connection timeout is 60 sec.

It is define in php.ini.

These are the steps to change it as per requirement

Step 1: Default location of php.ini file is /etc/php.ini other wise locate it.
Step 2: In php.ini file search from "mysql.connect_timeout" it is bydefault set up to 60 sec.
If you change 60 to -1 then there is no limit on connection timeout.
Step 3: Save file and exit

For other option of mysql in php.ini file you can check in php.ini file.Other location for reference is 

w3school php-mysql functions