do i have to restart the mysql server when i change some global variables?

[Lin Chun]

hi

as the title

thanks

-- 
-
Lin Chun

Re: do i have to restart the mysql server when i change some global variables?

[Suresh Kuna]

unless and until if the variable is read-only, you don't need Lin.

On Mon, May 31, 2010 at 3:17 PM, Lin Chun  wrote:

> hi
>
> as the title
>
> thanks
>
> --
> -
> Lin Chun
>



-- 
Thanks
Suresh Kuna
MySQL DBA

Re: do i have to restart the mysql server when i change some global variables?

[Prabhat Kumar]

Yes and No.
Its depend on the type of variable you have changed.
If its Dynamic , MySQL restart not required, else its required.

System variables can be set at server startup using options on the command
> line or in an option file. Most of them can be changed dynamically while the
> server is running by means of the 
> SETstatement, which 
> enables you to modify operation of the server without
> having to stop and restart it. You can refer to system variable values in
> expressions.
>

Check the complete list of variable. :
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Thanks,


On Mon, May 31, 2010 at 3:17 PM, Lin Chun  wrote:

> hi
>
> as the title
>
> thanks
>
> --
> -
> Lin Chun
>



-- 
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

Re: Strange behavior by MySQL Stored Procedure

[Manasi Save]

mysql Version :- 5.1.42-community-log

mysql Connector/J Version :-  mysql-connector-java-5.1.6-bin.jar
 
Sample Java Code Which Calls stored procedure :- 
 
//get the connection to database
Connection dbConnection = getConnection();
 
//create the call for procedure
String procedureCallStmtStr = "Call XYZ()";
 
//create callable statement object
CallableStatement cs = conn.prepareCall(procedureCallStmtStr);
 
//execute the procedure
cs.execute();
 
//obtain resultset
ResultSet result = cs.getResultSet();
 
//Iterate to get the resultSet, if present
 
//commit transaction
conn.commit();
 
//close resultset, callableStatement
result.close();
cs.close();

 
Stored procedure which is getting called :- 
 
CREATE definer=`myus...@`localhost` PROCEDURE `AddCust`(InputUserID
BigInt, InputCustID BigInt, InputDBID BigInt, InputTimeStamp DateTime)
    DETERMINISTIC
BEGIN
 
Declare DBName Varchar(45);
 
Select InputDBID into DBName;
 
Drop Temporary Table If Exists Temp;
Create Temporary Table Temp
(
  UserID BigInt,
  CustID BigInt,
  MarkForDeletion Boolean
);
 
SET @stmt = Concat('Insert into Temp(UserID, CustID, MarkForDeletion)
            Select FK_UserID, FK_CustID, MarkForDeletion
            From `',DBName,'`.Tbl1
            Where FK_UserID = ',InputUserID,'
            and FK_CustID = ',InputCustID,';');
 
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
 
IF Exists (Select CustID From Temp Where CustID = InputCustID)
Then
 
                      SET @stmt = Concat('Update `',DBName,'`.Tbl1
                                  Set MarkForDeletion = 0,
                                  TimeStamp =
','"',InputTimeStamp,'"','
                                  Where FK_UserID =
',InputUserID,'
                                  and FK_CustID =
',InputCustID,';');
 
                      Prepare stmt1 From @stmt;
                      Execute stmt1;
                      Deallocate Prepare stmt1;
ELSE
 
                     SET @stmt = Concat('Insert into ',
'`',DBName,'`.Tbl1 (FK_CustID, FK_UserID, MarkForDeletion, TimeStamp) ',
                     'Select ', '"', InputCustID, '"', ',',
'"',InputUserID,'"',', False',',','"',InputTimeStamp,'"',';');
 
                      Prepare stmt1 From @stmt;
                      Execute stmt1;
                      Deallocate Prepare stmt1;
 
                      Select InputUserID as RecordInserted;
   
END IF;

 
Thanks in advance.
 
--
Regards,
 Manasi Save

On Fri, 28 May 2010 15:40:05  0200, Mattia Merzi  wrote:
2010/5/28 Manasi Save :
 > [...]
 > > Or am I doing something wrong?
 > probably;
 >
 > you better send us another e-mail writing at least:
 > - mysql version you are using
 > - mysql Connector/J version you are using
 > - piece of java code you are using to call the stored procedure
 > - source of the stored procedure (or part of it)
 >
 > ... probably, a subset of all of these infos will not be enough
 > to understand the problem.
 >
 > In any case, if you have troubles using the mysql jdbc driver
 > but no problem using the mysql CLI and you suspect a
 > Connector/J bug, maybe you better write to the "mysql java"
 > support mailing list: http://lists.mysql.com/java
 >
 > Greetings,
 >
 > Mattia.
 >
 >


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_66b0_0.MYI'; try to repair it

[Manasi Save]

Hi All,
 
I am getting following error when I am trying to run one stored procedure on
table which has 30 rows in it. Table Type id MyIsAM.
 
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_66b0_0.MYI'; try to
repair it
 
How to debug this error? I mean I tried to increase tmp_table_Size parameter
but, it does not affect.
 
Proc is as follows :- 
 
 
CREATE definer=`myus...@`localhost` PROCEDURE `SP_GetLogs`(InputToDate Date,
InputFromDate Date, InputFrom int, InputTo int)
    DETERMINISTIC
BEGIN

 
 
Declare Count int;
Declare AvgVal float(10,2);
 
 
 
Drop Temporary Table
If Exists norep_Temp;
Create Temporary
Table norep_Temp
(
--   CountOfXML
int,
  TaskName
Varchar(100),
  MinFinalXML Varchar(1),
  MaxFinalXML Varchar(1) -- ,
--  AvgOfXML
float(10,2)
);
If (InputToDate IS
NULL and InputFromDate IS NULL)
Then
SET @stmt =
Concat('Insert into norep_Temp(MinFinalXML)
Select distinct
FinalXML
From ClientLog
Where TaskName =
','"','Time required to complete task','"','
and FinalXML Between
1 and 10
Order By FinalXML ASC
LIMIT ',InputFrom,',',InputTo,';');
Select @stmt;
Prepare stmt1 From
@stmt;
Execute stmt1;
Deallocate Prepare
stmt1;
SET @stmt =
Concat('Insert into norep_Temp(MaxFinalXML)
Select Distinct
FinalXML
From ClientLog
Where TaskName =
','"','Time required to complete task','"','
Order By FinalXML
DESC LIMIT ',InputFrom,',',InputTo,';');
Prepare stmt1 From
@stmt;
Execute stmt1;
Deallocate prepare
stmt1;
Select AVG(FinalXML)
into AvgOfXML From ClientLog Where TaskName = 'Time required to complete
task';
Select Count(*) into
CountOfXML From ClientLog Where TaskName = 'Time required to complete
task';
-- Select Count(*) as
CountOfXML, MIN(MinFinalXML), MAX(MaxFinalXML), AvgOfXML From norep_Temp Order
By MinFinalXML, MaxFinalXML Limit 0,10;
 Select
CountOfXML, MinFinalXML, MaxFinalXML, AvgOfXML  From norep_Temp;
 
Drop Temporary Table If Exists Temp;
Create Temporary Table Temp
(
  TaskName Varchar(100),
  MinVal Varchar(100),
  MaxVal Varchar(100) -- ,
);
 
If (InputToDate IS NULL and InputFromDate IS NULL)
Then
 
SET @stmt = Concat('Insert into Temp(MinVal)
Select distinct Val
>From MyLogs
Where TaskName = ','"','Time required to complete task','"','
and Val Between 1 and 10
Order By Val ASC LIMIT ',InputFrom,',',InputTo,';');
 
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
 
 
SET @stmt = Concat('Insert into Temp(MaxVal)
Select Distinct Val
>From MyLogs
Where TaskName = ','"','Time required to complete task','"','
Order By Val DESC LIMIT ',InputFrom,',',InputTo,';');
 
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
 
Select AVG(Val) into AvgVal From MyLogs Where TaskName = 'Time required to
complete task';
 
Select Count(*) into Count From MyLogs Where TaskName = 'Time required to
complete task';
 
 Select Count, MinVal, MaxVal, AvgVal  From Temp;
 
END;
 
--Regards, Manasi Save 
 
 

Re: ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_66b0_0.MYI'; try to repair it

[Walter Heck - OlinData.com]

This kind of error usually means you ran out of disk space on your
tmp_dir drive.

Walter

On Mon, May 31, 2010 at 13:30, Manasi Save
 wrote:
> Hi All,
>
> I am getting following error when I am trying to run one stored procedure on
> table which has 30 rows in it. Table Type id MyIsAM.
>
> ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_66b0_0.MYI'; try
> to repair it
>
> How to debug this error? I mean I tried to increase tmp_table_Size parameter
> but, it does not affect.
>
> Proc is as follows :-
>
>
> CREATE definer=`myus...@`localhost` PROCEDURE `SP_GetLogs`(InputToDate Date,
> InputFromDate Date, InputFrom int, InputTo int)
>     DETERMINISTIC
> BEGIN
>
>
> Declare Count int;
> Declare AvgVal float(10,2);
>
>
>
> Drop Temporary Table If Exists norep_Temp;
> Create Temporary Table norep_Temp
> (
> --   CountOfXML int,
>   TaskName Varchar(100),
>   MinFinalXML Varchar(1),
>   MaxFinalXML Varchar(1) -- ,
> --  AvgOfXML float(10,2)
> );
> If (InputToDate IS NULL and InputFromDate IS NULL)
> Then
> SET @stmt = Concat('Insert into norep_Temp(MinFinalXML)
> Select distinct FinalXML
> From ClientLog
> Where TaskName = ','"','Time required to complete task','"','
> and FinalXML Between 1 and 10
> Order By FinalXML ASC LIMIT ',InputFrom,',',InputTo,';');
> Select @stmt;
> Prepare stmt1 From @stmt;
> Execute stmt1;
> Deallocate Prepare stmt1;
> SET @stmt = Concat('Insert into norep_Temp(MaxFinalXML)
> Select Distinct FinalXML
> From ClientLog
> Where TaskName = ','"','Time required to complete task','"','
> Order By FinalXML DESC LIMIT ',InputFrom,',',InputTo,';');
> Prepare stmt1 From @stmt;
> Execute stmt1;
> Deallocate prepare stmt1;
> Select AVG(FinalXML) into AvgOfXML From ClientLog Where TaskName = 'Time
> required to complete task';
> Select Count(*) into CountOfXML From ClientLog Where TaskName = 'Time
> required to complete task';
> -- Select Count(*) as CountOfXML, MIN(MinFinalXML), MAX(MaxFinalXML),
> AvgOfXML From norep_Temp Order By MinFinalXML, MaxFinalXML Limit 0,10;
>  Select CountOfXML, MinFinalXML, MaxFinalXML, AvgOfXML  From norep_Temp;
>
> Drop Temporary Table If Exists Temp;
> Create Temporary Table Temp
> (
>   TaskName Varchar(100),
>   MinVal Varchar(100),
>   MaxVal Varchar(100) -- ,
> );
>
> If (InputToDate IS NULL and InputFromDate IS NULL)
> Then
>
> SET @stmt = Concat('Insert into Temp(MinVal)
> Select distinct Val
> From MyLogs
> Where TaskName = ','"','Time required to complete task','"','
> and Val Between 1 and 10
> Order By Val ASC LIMIT ',InputFrom,',',InputTo,';');
>
> Prepare stmt1 From @stmt;
> Execute stmt1;
> Deallocate Prepare stmt1;
>
>
> SET @stmt = Concat('Insert into Temp(MaxVal)
> Select Distinct Val
> From MyLogs
> Where TaskName = ','"','Time required to complete task','"','
> Order By Val DESC LIMIT ',InputFrom,',',InputTo,';');
>
> Prepare stmt1 From @stmt;
> Execute stmt1;
> Deallocate prepare stmt1;
>
> Select AVG(Val) into AvgVal From MyLogs Where TaskName = 'Time required to
> complete task';
>
> Select Count(*) into Count From MyLogs Where TaskName = 'Time required to
> complete task';
>
>  Select Count, MinVal, MaxVal, AvgVal  From Temp;
>
> END;
>
> --
> Regards,
> Manasi Save
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_66b0_0.MYI'; try to repair it

[Manasi Save]

Dear Walter,
 
Thanks for quick response.
 
But When I try to remove my tmp files related to mysql. I am not
allowed to remove them. How should I make more space in that case.
 
and We have allocated 2GB space for /tmp dir. and current size of
folder is showing as 60M. Still I am getting this error.
 
Any specific reason this could happen. Any other possible scenario.
 
Any input will be a great help. 
 
Thanks in advance.

 --
Regards,
 Manasi Save

On Mon, 31 May 2010 14:29:16  0200, "Walter Heck - OlinData.com"  wrote:
This kind of error usually means you ran out of disk space on your
 > tmp_dir drive.
 >
 > Walter
 >
 > On Mon, May 31, 2010 at 13:30, Manasi Save
 >  wrote:
 > > Hi All,
 > >
 > > I am getting following error when I am trying to run one stored
procedure on
 > > table which has 30 rows in it. Table Type id MyIsAM.
 > >
 > > ERROR 126 (HY000): Incorrect key file for table
'/tmp/#sql_66b0_0.MYI'; try
 > > to repair it
 > >
 > > How to debug this error? I mean I tried to increase
tmp_table_Size parameter
 > > but, it does not affect.
 > >
 > > Proc is as follows :-
 > >
 > >
 > > CREATE definer=`myus...@`localhost` PROCEDURE
`SP_GetLogs`(InputToDate Date,
 > > InputFromDate Date, InputFrom int, InputTo int)
 > >     DETERMINISTIC
 > > BEGIN
 > >
 > >
 > > Declare Count int;
 > > Declare AvgVal float(10,2);
 > >
 > >
 > >
 > > Drop Temporary Table If Exists norep_Temp;
 > > Create Temporary Table norep_Temp
 > > (
 > > --   CountOfXML int,
 > >   TaskName Varchar(100),
 > >   MinFinalXML Varchar(1),
 > >   MaxFinalXML Varchar(1) -- ,
 > > --  AvgOfXML float(10,2)
 > > );
 > > If (InputToDate IS NULL and InputFromDate IS NULL)
 > > Then
 > > SET @stmt = Concat('Insert into norep_Temp(MinFinalXML)
 > > Select distinct FinalXML
 > > From ClientLog
 > > Where TaskName = ','"','Time required to complete task','"','
 > > and FinalXML Between 1 and 10
 > > Order By FinalXML ASC LIMIT ',InputFrom,',',InputTo,';');
 > > Select @stmt;
 > > Prepare stmt1 From @stmt;
 > > Execute stmt1;
 > > Deallocate Prepare stmt1;
 > > SET @stmt = Concat('Insert into norep_Temp(MaxFinalXML)
 > > Select Distinct FinalXML
 > > From ClientLog
 > > Where TaskName = ','"','Time required to complete task','"','
 > > Order By FinalXML DESC LIMIT ',InputFrom,',',InputTo,';');
 > > Prepare stmt1 From @stmt;
 > > Execute stmt1;
 > > Deallocate prepare stmt1;
 > > Select AVG(FinalXML) into AvgOfXML From ClientLog Where TaskName = 'Time
 > > required to complete task';
 > > Select Count(*) into CountOfXML From ClientLog Where TaskName = 'Time
 > > required to complete task';
 > > -- Select Count(*) as CountOfXML, MIN(MinFinalXML), MAX(MaxFinalXML),
 > > AvgOfXML From norep_Temp Order By MinFinalXML, MaxFinalXML Limit 0,10;
 > >  Select CountOfXML, MinFinalXML, MaxFinalXML, AvgOfXML  From
norep_Temp;
 > >
 > > Drop Temporary Table If Exists Temp;
 > > Create Temporary Table Temp
 > > (
 > >   TaskName Varchar(100),
 > >   MinVal Varchar(100),
 > >   MaxVal Varchar(100) -- ,
 > > );
 > >
 > > If (InputToDate IS NULL and InputFromDate IS NULL)
 > > Then
 > >
 > > SET @stmt = Concat('Insert into Temp(MinVal)
 > > Select distinct Val
 > > From MyLogs
 > > Where TaskName = ','"','Time required to complete task','"','
 > > and Val Between 1 and 10
 > > Order By Val ASC LIMIT ',InputFrom,',',InputTo,';');
 > >
 > > Prepare stmt1 From @stmt;
 > > Execute stmt1;
 > > Deallocate Prepare stmt1;
 > >
 > >
 > > SET @stmt = Concat('Insert into Temp(MaxVal)
 > > Select Distinct Val
 > > From MyLogs
 > > Where TaskName = ','"','Time required to complete task','"','
 > > Order By Val DESC LIMIT ',InputFrom,',',InputTo,';');
 > >
 > > Prepare stmt1 From @stmt;
 > > Execute stmt1;
 > > Deallocate prepare stmt1;
 > >
 > > Select AVG(Val) into AvgVal From MyLogs Where TaskName = 'Time
required to
 > > complete task';
 > >
 > > Select Count(*) into Count From MyLogs Where TaskName = 'Time required to
 > > complete task';
 > >
 > >  Select Count, MinVal, MaxVal, AvgVal  From Temp;
 > >
 > > END;
 > >
 > > --
 > > Regards,
 > > Manasi Save
 > >
 > >
 > >
 > >
 >
 > --
 > MySQL General Mailing List
 > For list archives: http://lists.mysql.com/mysql
 > To unsubscribe:
http://lists.mysql.com/mysql?unsub=manasi.s...@artificialmachines.com
 >
 >
 >


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

ANN: Debugger for MySQL 1.2 released

[George Schmidt]

Dear Sir!

We are glad to announce the new version of Debugger for MySQL 1.2. 

You can download the new version with this link:
http://www.mydebugger.com/download.php

Briefly about the product:
Debugger for MySQL is a handy framework to develop, test and debug MySQL 
procedures and functions.

What's new:
This version features significant improvements in the code editor.
Such capabilities as code completion and code parameters turned it
from a simple editor with syntax highlighting to a modern, powerful
development framework.

You can read more about new features here:
http://www.mydebugger.com/whatsnew.php

Thank you for your interest.

--
Best regards,
George Schmidt,
http://www.mydebugger.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Strange behavior by MySQL Stored Procedure

[Venugopal Rao]

Stored procedures are not executed like a query.  
They are executed thru a Call { procedure} method.
Please check the same or let us know how you are executing the Query/Calling 
the Procedure.
Regards,
VR Venugopal Rao


--- On Fri, 28/5/10, Manasi Save  wrote:


From: Manasi Save 
Subject: Strange behavior by MySQL Stored Procedure
To: mysql@lists.mysql.com
Date: Friday, 28 May, 2010, 5:44 PM


Dear All,
 
I have one stored procedure Which inserts data into one table.
 
But sometimes it does not insert record. This happens when I called it from 
java application. But If I called same query from mysql command line. It 
executes successfully.
 
Also I have one procedure which only retrieves data from table. and it only 
gives one row sometime even if there are 10 rows available in for matching 
condition. This too happen when I called it from Java application and if I 
called it from mysql command line it gives me proper result set of 10 rows.
 
I am not able to understand Is it something known for mysql? Or am I doing 
something wrong?

Any input will be a great help.
 
--
Thanks and Regards,
Manasi Save 

Replication testing?

[Machiel Richards]

Hi All

 

One of the managers requested me to find out about the
replication testing on MySQL as one of our clients need this to be done
soon.

 

Basically what they want to do is the following:

 

. Currently a master slave replication is in place.

. Testing will entail the following:

o   Stop master database 

o   Switch over to Slave database and confirm everything is working as
should and all Data is available.

o   Switch back to master database after it has been restored 

o   Confirm that all is working again and that all data are available on
both databases.

 

I am not yet to this part of my studying as I am fairly new to MySQL and I
am hoping someone can perhaps provide me with some info / resources on where
to get this info from.

 

Machiel Richards

MySQL DBA

Relational Database Consulting 

 

RDC_Logo