mysql data folder corrupted

In mysql, if the datas are to be changed from one system to other each with different operating system(windows)

u can take the copy of each database in folder format & ibdata0 or ibdata1 file inside data folder. stop the service of destination system & paste them in the concerned data folder & start the service.

Instead of taking the backup and restore of each & every database u can use this method also. if ur size of ibdata file is less u cannot copy since the data may be corrupted or lost.

mysql root password security problem

In mysql if we are logging throu' username & password and the password had been forgotten
u can go thro' administrator by skipping ur username & password by clicking ctrl key .
The cancel button will be changed skip option.

startup variable -> security tab -> disable grant tables (enable them).

The username(default) & password can be changed thro' command prompt or u can go thro query browser to change the password

find min, max value with variables

select pk_id,pk_new_amt
FROM vw_pk_discount,(select @mi:=0 mi,@mx:=0 mx)R
where pk_id in(626,627,628,629,633,634,635) AND
'2013-01-29 10:06:28' BETWEEN pk_start_dt AND pk_end_dt and
(if(@mi<pk_new_amt,(select @mi:=pk_new_amt),0) or
if(@mx>pk_new_amt,(select @mx:=pk_new_amt),0));

to find min,max value using variables, when we use it in coding format,

we are using the variables as assignment operator with the condition checking

wordpress 3.5 released

word press 3.5 had been released it has lot of option like gzip compression for pages, optimise table with repair & check table.

find free space & database size

In mysql, to find used space & free space this query will be used

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

chk max(id) & insert in single query

In MYSQL, we need to query for finding max(id) & value is stored  in a variable, we need to use that variable in the query or for stored procedure.

but, we can use these both in a single query.

create table ty(i int,u varchar(20));
insert into ty(i,u) select (select ifnull(max(i),0)+1 from ty),'A';
insert into ty(i,u) select (select ifnull(max(i),0)+1 from ty),'B';
select * from ty;
drop table ty;

extractvalue: Only constant XPATH queries are supported

In mysql extractvalue is used in xml column to extract the values from it.


extractvalue cannot be implemented directly into query for attribute matching in joining a table.

for attribute matching join two tables with the coulmns with extractvalue using concat function. concat function is used to assign the variable in xml.

it can be used as a function.

CREATE FUNCTION `exv`(ky text,xpath text) RETURNS text CHARSET latin1
BEGIN
 return cast(extractvalue(ky,xpath) as char);
END $$

create table tbl_xml (u_id int,t_xml text)
insert into tbl_xml(u_id,t_xml) values(4,'<a o_id="197" amt="20.00"/><a o_id="198" amt="10.00"/>')


create table tbl_order(u_id int,o_id int,o_amt int);


insert into tbl_order(u_id,o_id,o_amt) values(1,11,20),(2,20,40),(4,197,60),(4,198,20);

select *,exv(A.t_xml,concat('sum(//a[@o_id="',B.o_id,'"]/@amt)')) from tbl_xml A join tbl_order B on A.u_id=B.u_id;


select *,exv(A.t_xml,concat('sum(//a[@o_id="',B.o_id,'"]/@amt)')) from tbl_xml A join tbl_order B on exv(A.t_xml,concat('(//a[@o_id="',B.o_id,'"]/@amt)'));