Re: mysqldump error 1064 for database Use command
Hi win.a, I tried with another user the problem still persists. I am getting same error for another user as well. -- Regards, Manasi Save On Tue, 7 Sep 2010 11:41:35 0800, win.a wrote: use another user and dump the data ,eg the root . mysqldump -uroot -p --al-databases AllNew_Databases_20100904.sql All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 8:33 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi win.a, How am I suppose put that command. Could you help me with the syntax: If I am giving it this way:- mysqldump --all-databases AllNew_Databases_20100904.sql then it says it will not work with my system user. nor with this it is working :- mysqldump -u --all-databases AllNew_Databases_20100904.sql -- Regards, Manasi Save On Mon, 6 Sep 2010 20:12:26 0800, win.a wrote: try it without username and password in command line,type it it prompts. All you best What we are struggling for ? The life or the life ? On Mon, Sep 6, 2010 at 3:28 PM, Manasi Save manasi.s...@artificialmachines.com wrote: No it does not. But when I dump database name mydb it does. but not the database with name 1. -- Regards, Manasi Save On Mon, 06 Sep 2010 09:23:21 0200, Jangita wrote: On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); - | version() | - | 5.1.22-rc-Debian_2~ppa5-log | - Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | 256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- 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=win@gmail.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: mysqldump error 1064 for database Use command
No it does not. But when I dump database name mydb it does. but not the database with name 1. -- Regards, Manasi Save On Mon, 06 Sep 2010 09:23:21 0200, Jangita wrote: On 06/09/2010 6:54 a, Manasi Save wrote: Dear Nitin, I have newly installed mysql on this server. mysql Select version(); - | version() | - | 5.1.22-rc-Debian_2~ppa5-log | - Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. -- Regards, Manasi Save Does it work when you dump only the database `1`? -- Jangita | 256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- 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
Re: mysqldump error 1064 for database Use command
Dear Nitin, I have newly installed mysql on this server. mysql Select version(); +-+ | version() | +-+ | 5.1.22-rc-Debian_2~ppa5-log | +-+ Earlier I use to run the same command on Fedora-with same mysql version. I could not possibly change the database name. There are quite a few databases I have on the system. --Regards, Manasi Save On Sat, 4 Sep 2010 21:00:22 -0700 (PDT), Nitin Mehta wrote: Hi, Have you recently upgraded your MySQL installation? 1064 in earlier days used to mean use of reserved word. Few clients have reported this error in last couple of months after they upgraded from 5.1.41 to higher versions. As of now, I don't have a solution other than changing the name (of database in your case). Hope that helps. Regards, Nitin From: Manasi Save manasi.s...@artificialmachines.comTo: "mysql@lists.mysql.com" mysql@lists.mysql.comSent: Sat, September 4, 2010 12:06:27 PMSubject: mysqldump error 1064 for database Use commandHi All,I have 10 mysql databases all the tables use MyIsAm mysql storage engine.Database names are 1,2,3,4,...10.When I use mysqldump command with --all-databases option. This gives me following error:mysqldump -u myuser -p --all-databases AllNew_Databases_20100904.sqlERROR MESSAGE :-mysqldump: Couldn't execute 'use 1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (1064)Can anyone provide any input on this. I have never got this error before. The backups uptill now was happening properly. Please let me know if I am missing out any information which should be provided to get more clear idea about this error.Any input will be a great help.Thanks in advance.--Regards,Manasi Save
mysqldump error 1064 for database Use command
Hi All, I have 10 mysql databases all the tables use MyIsAm mysql storage engine. Database names are 1,2,3,4,...10. When I use mysqldump command with --all-databases option. This gives me following error: mysqldump -u myuser -p --all-databases AllNew_Databases_20100904.sql ERROR MESSAGE :- mysqldump: Couldn't execute 'use 1': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (1064) Can anyone provide any input on this. I have never got this error before. The backups uptill now was happening properly. Please let me know if I am missing out any information which should be provided to get more clear idea about this error. Any input will be a great help. Thanks in advance. -- Regards, Manasi Save
How to unstall MySQL 5.1 on Ubuntu 8.04 LTS
Dear All, I need to install MySQL 5.1.42 on ubuntu. MySQL site does not have installer packages for ubuntu(.deb). If I need to use tar.gz is there any document available which will help me do this specific changes. Any input will be great help. As I need to set it up urgently. Thanks in advance. --Regards, Manasi Save Database Administrator
MySQL data get and set problem
Dear All, Table values are getting rollback without calling rollback. I have a table Test with some columns in it. Here's the Table Structure:- Create Table TestID ( TestID int not null, MyID int ); I am calling two procedures one is get the value of MyID column and one is to set the value after that. FirstProc :- Create procedure SP_GetMyID(TestID int) Begin Select MyID From Test Where TestID = TestID; End; Call SP_GetMyID(1); [OUTPUT = 1] If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one) Call SP_SetMyID(1,2); Create Procedure SP_SetMyID(TestID int, NewMyID int) Begin Update Test Set MyID = NewMyID Where TestID = TestID; End; After setting the value I am again calling get procedure Call SP_GetMyID(1); [OUTPUT = 2] and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1. --Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392
Re: MySQL data get and set problem
Hi Michale, I have made that parameter column name change. but I am unable to understand how even after execution of update statement and commit after that, data is getting reverted. Any inputs on this will be a great help. -- Regards, Manasi Save On Mon, 9 Aug 2010 14:11:19 -0400, Michael Dykman wrote: You might have better results if you use unique identifiers: Select MyID From Test Where TestID = TestID; I'm not sure how mysql is supposed to tell the param 'TestID' from the column name. - michael dykman On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear All, Table values are getting rollback without calling rollback. I have a table Test with some columns in it. Here's the Table Structure:- Create Table TestID ( TestID int not null, MyID int ); I am calling two procedures one is get the value of MyID column and one is to set the value after that. FirstProc :- Create procedure SP_GetMyID(TestID int) Begin Select MyID From Test Where TestID = TestID; End; Call SP_GetMyID(1); [OUTPUT = 1] If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one) Call SP_SetMyID(1,2); Create Procedure SP_SetMyID(TestID int, NewMyID int) Begin Update Test Set MyID = NewMyID Where TestID = TestID; End; After setting the value I am again calling get procedure Call SP_GetMyID(1); [OUTPUT = 2] and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1. -- Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392 -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Insert blob data using prepared statements
Hi All, I need to insert Blob data in my table using prepared statements. But Whenever I try to insert it using prepared statement it is giving me mysql syntax error. Here's the prepared statement :- SET @stmt = Concat(Insert into ',mydb,'.MyTable(MyData, MyID) Select ','"',Inputdata,'"',',',InputID,';'); Prepare stmt1 From @stmt; Execute stmt1; Deallocate prepare stmt1; The executing statement looks like this :- Insert into `mydb`.MyTable(MyData, MyID) Select ** STREAM DATA **, 1; This gives me an error saying mysql syntax near ** STREAM DATA.. Can anyone give me any example how to insert blob data in database with prepared statement. Thanks in advance. --Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392
How to decode base64 encoded text from mysql
Hi All, Is there any way I can decode base64 encoded message from mysql. I tried using mysql decode function but it did not work for me. Can anyone provide any input on how I should go about it. Thanks and 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: MySQL Replication - Master-Slave crash
Dear Nilnandan, Thanks for the quick reply. But this particular variable is not working in only one proc call...but rest all procedures creating temporary tables, does not affect my replication slaves. So I am not able to identify the problem. Here's the snapshot of my procedure which is causing a problem. Drop Temporary Table If Exists norep_Temp3; Create Temporary Table norep_Temp3 ( UserID BigInt, FirstName Varchar(45), LastName Varchar(45) ); Insert into norep_Temp3(UserID, FirstName, LastName) Select UserID From tbl1; Update norep_Temp3 T3, tbl2 Set T3.FirstName = tbl2.FirstName, T3.LastName = tbl2.LastName Where T3.UserID = tbl2.UserID; Insert into MyTbl(UserID, Name) Select UserID, Concat(FirstName,' ',LastName) From norep_Temp3; -- Regards, Manasi Save On Wed, 23 Jun 2010 18:47:19 0530, Nilnandan Joshi wrote: Hi Manasi, Please try with this one. Replicate_Wild_Ignore_Table = mydb\temp_.% Regards, Nilnandan Joshi Manasi Save wrote: Hi All, I have kept Replicate_Wild_Ignore_Table = mydb%.temp_% this is temporary table which i want should not be replicated. But still it is getting replicated and slave is getting crashed. any input will be a great help. -- Thanks and 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
MySQL Cluster Out of fragment records (increase MaxNoOfOrderedIndexes)
Dear All, I want to add one column in my existing NDB table. While adding column in to the table I am getting this error:- | Level | Code | Message Error | 1296 | Got error 904 'Out of fragment records (increase MaxNoOfOrderedIndexes)' from NDB | | Error | 1005 | Can't create table 'ChitMeRegistration.User_1' (errno: 136) Whereas my ndb_mgm - is showing me only Node 3: Index usage is 2%(376 8K pages of total 16416) Node 3: Index usage is 2%(392 8K pages of total 16416) Node 3: Data usage is 1%(800 32K pages of total 40960) Node 4: Index usage is 2%(366 8K pages of total 16416) Node 4: Index usage is 2%(382 8K pages of total 16416) Node 4: Data usage is 1%(800 32K pages of total 40960) But still I am getting this error. Can anyone help me understanding how should I go about this error. As I have only few hundred records in my table and still i m getting this error. Thanks in advance. -- Regards, Manasi Save
MySQL Replication - Master-Slave crash
Hi All, I have kept Replicate_Wild_Ignore_Table = mydb%.temp_% this is temporary table which i want should not be replicated. But still it is getting replicated and slave is getting crashed. any input will be a great help. --Thanks and Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392
Re: Replication of MySQL Stored Procedure
I think even if you ignore the mysql database from replication and set Is_Deterministic = YES then your stored procedures will be replicated. Please set it to NO if you do not wish the stored procedures will not be replicated. You can set this in mysql.proc table. -- Regards, Manasi Save On Mon, 7 Jun 2010 12:31:13 -0700, Sabika Gmail wrote: I already have mysql in the replicate wild ingore table. I am running mysql 5.1.40sp1 Could it be a bug? On Jun 7, 2010, at 8:30 AM, Rolando Edwards redwa...@logicworks.net wrote: I think this is normal because stored procedures live in mysql.proc. You would have to filter out mysql.proc by adding this to /etc/my.cnf replicate-ignore-table=mysql.proc Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Sabika Gmail [mailto:sabika.makhd...@gmail.com] Sent: Monday, June 07, 2010 11:14 AM To: mysql@lists.mysql.com Subject: Replication of MySQL Stored Procedure Hi! I have a database in the wild ignore table as table.%. Recently I created a store procedure on it and it replicated. Does any one know if this is normal bahvior? If I wanted to make sure store procedures do not replicate, what should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- 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
Re: Strange behavior by MySQL Stored Procedure
Does anyone have any sort of any idea on how to deal with this problem?
This is happening again and again and not all the time but randomly anytime.--Regards,Manasi Save
On Wed, 02 Jun 2010 06:46:56 -0400, Manasi Save
wrote:
Dear Venugopal,Here's theSample
Java Code Which Calls stored procedure :-//get the
connection to databaseConnection dbConnection = getConnection();//create the call for procedureString procedureCallStmtStr =
"Call XYZ()";//create callable statement objectCallableStatement cs = conn.prepareCall(procedureCallStmtStr);//execute the procedurecs.execute();//obtain
resultsetResultSet result = cs.getResultSet();//Iterate
to get the resultSet, if present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if
I am executing a stored procedure anywhere? Well, I am not aware of Java so
really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST),
Venugopal Rao wrote:
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
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: 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
Re: Strange behavior by MySQL Stored Procedure
Dear Venugopal,Here's theSample Java Code Which Calls
stored procedure :-//get the connection to databaseConnection dbConnection = getConnection();//create the call
for procedureString procedureCallStmtStr = "Call XYZ()";//create callable statement objectCallableStatement cs =
conn.prepareCall(procedureCallStmtStr);//execute the
procedurecs.execute();//obtain resultsetResultSet
result = cs.getResultSet();//Iterate to get the resultSet, if
present//commit transactionconn.commit();//close resultset, callableStatementresult.close();cs.close();But, can it be a problem if I am executing a stored procedure
anywhere? Well, I am not aware of Java so really cannot debug this.Thanks in advance.--Regards,Manasi SaveOn Tue, 1 Jun 2010 09:36:12 +0530 (IST), Venugopal Rao wrote:
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
manasi.s...@artificialmachines.com wrote:
From: Manasi Save manasi.s...@artificialmachines.comSubject: Strange behavior by MySQL Stored ProcedureTo:
mysql@lists.mysql.comDate: 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
Re: Strange behavior by MySQL Stored Procedure
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 manasi.s...@artificialmachines.com:
[...]
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
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
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
manasi.s...@artificialmachines.com 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
Strange behavior by MySQL Stored Procedure
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
Application lost database connection
Hi All, I am really not sure whether this question should be asked here or not but I am hoping that someone must have faced this problem. My Servers are losing connections with database because of inactivity and on next activity I am getting following error :- "The last packet successfully received from the server was40939 seconds ago.The last packet sent successfully to the server was 40939 seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem." Do I need to change in the application for this or what changes do I need to do to fix this error. Please provide me some input. Thanks in advance. --Regards, Manasi Save
Re: Using symlinks for database creation in mysql
Hi Johan, Some time back you have solved my problem of creating symlink as database was fixed. But now When I am putting special characters in symlinks like . it is not readable as database. If I am creating database with special characters from mysql command line I can create it but an symlink with special character is not readable. Is it possible that I can read a symplink with special characters as databse. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman vegiv...@tuxera.be: Given that you're talking about quite a few folders, I think it'd be good to check up on the theoretical and practical limits for your filesystem of choice, though - ext2 for example starts getting noticeably slower when you have a lot more than 10.000 entries in the same directory. One way of getting around that would be segmenting your database names in blocks of three or so (so database would be stored under dat/aba/se/datafiles). Takes a bit of management, but then you already have something in place to create the symlinks anyway. Also, as a matter of manageability, I'd not put your real database dirs somwhere where there is other stuff - make a separate directory and keep them all there. Another point of note, but I suppose you already know that, is that this will not work with InnoDB unless you have the file-per-table option enabled. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using symlinks for database creation in mysql
Hi All, When I am creating a symlink with a special character in database name it appears in mysql as somthing like this #mysql50#dbname. For Ex:- I have created a symlink in mysql data directory named User.Name which appeared to me in mysql command line client as #mysql50#User.Name Can anyone help me on how to resolve this error. Please Help. Thanks in advance. -- Regards, Manasi Save Quoting Manasi Save manasi.s...@artificialmachines.com: Hi Johan, Some time back you have solved my problem of creating symlink as database was fixed. But now When I am putting special characters in symlinks like . it is not readable as database. If I am creating database with special characters from mysql command line I can create it but an symlink with special character is not readable. Is it possible that I can read a symplink with special characters as databse. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman vegiv...@tuxera.be: Given that you're talking about quite a few folders, I think it'd be good to check up on the theoretical and practical limits for your filesystem of choice, though - ext2 for example starts getting noticeably slower when you have a lot more than 10.000 entries in the same directory. One way of getting around that would be segmenting your database names in blocks of three or so (so database would be stored under dat/aba/se/datafiles). Takes a bit of management, but then you already have something in place to create the symlinks anyway. Also, as a matter of manageability, I'd not put your real database dirs somwhere where there is other stuff - make a separate directory and keep them all there. Another point of note, but I suppose you already know that, is that this will not work with InnoDB unless you have the file-per-table option enabled. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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
Get count of number of lines from mysql stored procedure
Hi All,I want to find out number of lines are there in all stored procedure written.Is it possible to get the number of lines using a SQL query.Thanks in advance. -- Regards, Manasi Save
Re: SQL query for unique values.
Hi, A simple group by function should work for this: Select Fruit,GrownInStates From tbl1 Group By Fruit; and if you want grownstates in comma separated format then you can use Group_Concat function Select Fruit, Group_Concat(GrownInStates, SEPARATOR ',') From tbl1 Group By Fruit; Hope this will work fine. -- Regards, Manasi Save Quoting Ravishankar BV. ravishankar...@mindtree.com: Hi, I'm very new to SQL and databases. I need a query for the following: (I'm sure google would have found the answer, but I could not really frame the sentence for the task I'm looking for. Also, please let me know how do I search in google for such tasks - so that I can try it myself in future). Assume I have a table like: Sl No Fruit Grown in states 1 Apple KA 2 Orange AP 3 Banana TN 4 Jackfruit MH 5 Mango MP 6 Jackfruit MP 7 Banana AP 8 Mango KA 9 Banana TN 10 Apple MH 11 Jackfruit AP 12 Orange MH 13 Mango KA 14 Apple TN 15 Banana MP 16 Banana MH 17 Mango KA 18 Orange MP 19 Jackfruit AP 20 Apple TN From the above table, I want a SQL query which will list me the unique fruits and the states in which they are grown, like: Apple: KA, MH, TN Banana: TN, AP, MP, MH Jackfruit: MH,MP,AP Mango: MP, KA Orange: AP,MH,MP Thanks in advance for the help, Ravi. http://www.mindtree.com/email/disclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Reason for MySQL Replication Slave Crash
Dear All,I am using MySQL Master-Master Replication. Where most of the times it is happening that my slave crashes with two errors either :- 1. Duplicate Entry (Error No:- 1062)2. Does not find the row for update or delete. (Error No :- 1032)Well I google the problem but unable to find exact reason behind this behaviour of replication.I read somewhere that this can happen when relay-log.info does not get updated. But did not find any reason of how to deal with it. Does anyone faced similar type of issue in MySQL Replication. Any input will be a great help.Thanks in advance.--Regards,Manasi Save
Re: Reason for MySQL Replication Slave Crash
Dear Shawn, Thanks for the quick reply. To your points, First I have a query regarding your last line if I want to run master-master replication I should run it in active-passive mode. Does that mean that I should run only one master at a time. But I am doing it because I am not keeping two masters to distribute queries equally. For example:- I have two servers Server A and Server B I have db1 on Server A and db2 on Server B and replication db2 on Server A and db1 on Server B I am querying Server A for db1 and not Server B for db1. But I want Server B to be replicated same time with Server A. Same for Server B for db2. So ideally in this case I should not get duplicate entry error. Is that possible to be happened? I am not pretty sure that whether you have understood what I meant by above example. Please let me know if you have any questions. Thanks in advance. -- Regards, Manasi Save Quoting Shawn Green shawn.gr...@sun.com: Hello Manasi, Manasi Save wrote: Dear All, I am using MySQL Master-Master Replication. Where most of the times it is happening that my slave crashes with two errors either :- 1. Duplicate Entry (Error No:- 1062) That means that a row with the same PRIMARY or UNIQUE key value(s) already exists on this server. Somehow you are not protecting yourself against writing the same things to both servers at the same time. 2. Does not find the row for update or delete. (Error No :- 1032) Same problem, in reverse. This time, though, the row you are trying to remove has already been removed. Well I google the problem but unable to find exact reason behind this behaviour of replication. There is never an exact reason for this type of problem. It is a well-known engineering requirement that when replicating MySQL servers in a ring that you absolutely must avoid changing the same row of data (as identified by the tuple used for either the PRIMARY or UNIQUE keys) on both servers at nearly the same time. Your MASTER-MASTER configuration is simply a two-element ring configuration. I read somewhere that this can happen when relay-log.info does not get updated. But did not find any reason of how to deal with it. Does anyone faced similar type of issue in MySQL Replication. Any input will be a great help. Here are my suggestions. 1) Read how the replication systems of MySQL actually work. Only completed changes to the database are written to the binary log as either statements (to be repeated on the slave) or as row deltas (to be applied by the slave to its data). Those binary log entries are spooled asynchronously to the slave where they are buffered into the relay logs. One the slave a second thread (different than the one used to fill up the relay logs with binary log events) then steps through the relay logs one statement or change at a time. http://dev.mysql.com/doc/refman/5.1/en/replication.html http://dev.mysql.com/doc/refman/5.1/en/replication-implementation-details.html 2) Read the specific sections and FAQs about ring-based replication. There are some good things you can configure that will mitigate, but not eliminate, your exposure to the errors you reported above. http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-3-4-1-5 http://dev.mysql.com/doc/refman/5.1/en/replication-features.html 3) Then read all of the warnings from other sites that tell you how to configure this type of replication ring. Here's just one: http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Ring-based replication has many potential problems and very few benefits. It is a very difficult configuration to use properly. I do not recommend it for most purposes. The fact that you did not check the binary log entries against the actual data to detect that the duplicates or deletions were already on the table (and probably caused by another session) implies to me that your administrative skills may not yet be ready for this particular challenge. May I recommend that you switch back to the much easier to maintain master-slave replication configuration? If not that, at least use your masters in an active/passive mode, not active/active. Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using symlinks for database creation in mysql
Hi All,I am creating symlinks as database.I have mysql data directory created on /var/lib/mysql/databasename.on the same path I am creating /var/lib/mydatabaseand creating symlink from/var/lib/mydatabase to /var/lib/mysql/databasenamewill there any performance issues as there will be quite a few folders and symlinks on the same path?Thanks in advance. -- Regards, Manasi Save
Re: MySQL Replication Delete is not gettting replicated
Dear Carlos, Thanks for the response. But I haven't gave any privileges besides repl_slave priv to user replication and replication2 respectively. So does that amke any difference really? Thanks in advance. -- Regards, Manasi Save Quoting Carlos Proal carlos.pr...@gmail.com: I dont see anything unusual or missing on your config file and as the only thing missing are deletes, i think that might be a permission issue. Can you check out the grants for your replication users and see if they have full permissions granted ? mysql show grants for x; where is x is replication and replication2 respectively. Carlos On 1/18/2010 1:35 AM, Manasi Save wrote: Hi Anand, Please find below my configuration file of both the masters: ON MASTER 1: [mysqld] datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/usr/local/mysql/bin.log #binlog-do-db=database name # input the database which should be replicated binlog-ignore-db=mysql# input the database that should be ignored for replication binlog-ignore-db=test log-bin-index=/usr/local/mysql/log-bin.index log_slave_updates server-id=2 auto_increment_increment=2 auto_increment_offset=1 #information for becoming slave. master-host = 192.168.1.1 master-user = replication master-password = replication master-port = 3306 [mysql.server] user=mysql [mysqld_safe] err-log=/var/lib/mysql/mysql.log pid-file=/var/lib/mysql/mysql.privatedns.com.pid ON MASTER 2: [mysqld] datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/usr/local/mysql/bin.log #binlog-do-db=database name # input the database which should be replicated binlog-ignore-db=mysql# input the database that should be ignored for replication binlog-ignore-db=test log-bin-index=/usr/local/mysql/log-bin.index log_slave_updates server-id=1 auto_increment_increment=2 auto_increment_offset=2 #information for becoming slave. master-host = 192.168.1.2 master-user = replication2 master-password = replication2 master-port = 3306 [mysql.server] user=mysql [mysqld_safe] err-log=/var/var/lib/mysql/mysql.log pid-file=/var/lib/mysql/mysql.privatedns.com.pid Please let me know if I need to add any parameter to enable this replication. Thanks in advance. -- Regards, Manasi Save Quoting Anand kumar : can you give us the configuration(.cnf) file from both the masters ? --Anand On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save manasi.s...@artificialmachines.com mailto:manasi.s...@artificialmachines.com wrote: Hi All, I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated. But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave. Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance. -- 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: MySQL Replication Delete is not gettting replicated
Hi Anand,Please find below my configuration file of both the masters:ON MASTER 1:[mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.sockold_passwords=1log-bin=/usr/local/mysql/bin.log#binlog-do-db=database name # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationbinlog-ignore-db=testlog-bin-index=/usr/local/mysql/log-bin.indexlog_slave_updatesserver-id=2auto_increment_increment=2auto_increment_offset=1#information for becoming slave.master-host = 192.168.1.1master-user = replicationmaster-password = replicationmaster-port = 3306[mysql.server]user=mysql[mysqld_safe]err-log=/var/lib/mysql/mysql.logpid-file=/var/lib/mysql/mysql.privatedns.com.pidON MASTER 2:[mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.sockold_passwords=1log-bin=/usr/local/mysql/bin.log#binlog-do-db=database name # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationbinlog-ignore-db=testlog-bin-index=/usr/local/mysql/log-bin.indexlog_slave_updatesserver-id=1auto_increment_increment=2auto_increment_offset=2#information for becoming slave.master-host = 192.168.1.2master-user = replication2master-password = replication2master-port = 3306[mysql.server]user=mysql[mysqld_safe]err-log=/var/var/lib/mysql/mysql.logpid-file=/var/lib/mysql/mysql.privatedns.com.pidPlease let me know if I need to add any parameter to enable this replication.Thanks in advance.--Regards, Manasi Save Quoting Anand kumar <anand@gmail.com>:can you give us the configuration(.cnf) file from both the masters ? --Anand On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated. But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave. Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance. -- Regards,Manasi Save
MySQL Replication Delete is not gettting replicated
Hi All,I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated.But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave.Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance.--Regards, Manasi Save
Re: MySQL Master Master Replication and data loss
Dear Suresh, Thank you. In MySQL Replication, as the slave itself takes the writes from master but in how much time period does slave goes to master. is there any parameter where I can set this. that after every 60 seconds slave should write data from master to its own local database. -- Regards, Manasi Save Quoting Suresh Kuna sureshkumar...@gmail.com: Hi Manasi, As both are implemented by replication, there is a possibility for loosing data. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 8:20 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I want to implement MySQL Master Master Replication. But I have read in the various documentation that in mysql replication it is possible that I loose data. Is it also applicable to MySQL Master Master Replication. Thanks in advance. -- Regards, Manasi Save -- Thanks Suresh Kuna MySQL DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Master Master Replication and data loss
Hi All,I want to implement MySQL Master Master Replication. But I have read in the various documentation that in mysql replication it is possible that I loose data.Is it also applicable to MySQL Master Master Replication. Thanks in advance.--Regards, Manasi Save
Rename mysql database
Hi All,I want to rename my database. but as per the mysql documentation it says, RENAME DATABASE command has been removed from mysql 5.1.Can anyone help me with this, that is there any other way I can rename it. Thanks in advance.--Regards, Manasi Save
Re: mysql Create table in system database
Hi Johan,Is it possible to do such manipulation in NDB, the way innodb works. Thanks in advance.--Regards, Manasi Save Quoting Johan De Meersman <vegiv...@tuxera.be>:File permissions ? SE Linux ? AppArmor ?On Mon, Nov 16, 2009 at 7:48 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Shawn for the quick response. But then What I am doing is I am doing copy paste of one database and rename it to another. but I cannot read the tables inside it. Can you tell me what might be the possible reason for that. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. Hello Manasi, Manasi Save wrote: Hi All, Can anyone give me any input on How mysql create table write data into system database and where it has been stored besides information_schema. Is there any article on mysql System Databases anyone went through as I am not able to find it on Google. I want to write a table information in mysql system database. Can anyone help me on this. Thanks in advance. MySQL does not store that information within an internal table. The basic information for each table is stored within a .frm file stored in the file system. The various additional pieces of metadata for each storage engine are maintained in methods specific to those storage engine. The information you see in the many tables exposed through INFORMATION_SCHEMA is generated dynamically based on the results of polling those separate sources of metadata at the time of your query. quoting from http://dev.mysql.com/doc/refman/5.1/en/information-schema.html ~~ Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. ~~ More details are available in the manual: http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/innodb-table-and-index.html http://dev.mysql.com/doc/refman/5.1/en/se-db2.html http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html <hwebmailcleaned_tagion.html" target="_blank">http://dev.mysql.com/doc/refman/5.1/en/federated-description.html http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/blackhole-storage-engine.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
Can't execute the given command because you have active locked tables or an active transaction
Hi All,I am getting Can't execute the given command because you have active locked tables or an active transaction error when I am trying to truncate table. I am unable to understand the error as when I am using the mysql query browser then the same command is working fine but when I am doing it through NetBeans then I am getting this error. Well that is what is very strange and I am unable to solve this problem.If anyone could just help me with this. Thanks in advance.--Regards, Manasi Save
Mysql Keeps crashing
<manasi.s...@artificialmachines.com><manasi.s...@artificialmachines.com>Hi All,Sorry forgot to mention subject in earlier message.I am running normal mysql and I am trying to insert one entry in one database table.The logs I am getting are as follows:-Version: '5.1.38-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)091218 5:26:56 - mysqld got signal 11 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully help diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=8384512read_buffer_size=131072max_used_connections=1max_threads=151threads_connected=1It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338299 Kbytes of memoryHope that's ok; if not, decrease some variables in the equation.thd: 0x7ff924560400Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0x7ff9214c9f00 thread_stack 0x4/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x84a43e]/usr/sbin/mysqld(handle_segfault+0x322)[0x5bf132]/lib64/libpthread.so.0[0x321720f0f0]/usr/sbin/mysqld(trx_rseg_get_on_id+0x17)[0x7d4427]/usr/sbin/mysqld(trx_undo_get_undo_rec_low+0x57)[0x7ce187]/usr/sbin/mysqld(trx_undo_get_undo_rec+0x4a)[0x7ce39a]/usr/sbin/mysqld(trx_undo_prev_version_build+0x161)[0x7ce511]/usr/sbin/mysqld(row_vers_build_for_consistent_read+0x1b1)[0x7c10e1]/usr/sbin/mysqld(row_search_for_mysql+0x2e2e)[0x7b965e]/usr/sbin/mysqld(_ZN11ha_innobase10index_readEPhPKhj16ha_rkey_function+0x1cd)[0x730abd]/usr/sbin/mysqld(_ZN11ha_innobase11index_firstEPh+0x37)[0x72dd67]/usr/sbin/mysqld(_ZN11ha_innobase8rnd_nextEPh+0x37)[0x7302c7]/usr/sbin/mysqld(_Z13rr_sequentialP14st_read_record+0x15)[0x692455]/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0xa9)[0x622c99]/usr/sbin/mysqld[0x629750]/usr/sbin/! mysqld(_ZN4JOIN4execEv+0x90c)[0x6358bc]/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x16e)[0x63732e]/usr/sbin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x169)[0x637c49]/usr/sbin/mysqld[0x5c9d84]/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4c1)[0x5cc421]/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x1f1)[0x5d1901]/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xff0)[0x5d2910]/usr/sbin/mysqld(_Z10do_commandP3THD+0xe4)[0x5d2ea4]/usr/sbin/mysqld(handle_one_connection+0x6f0)[0x5c60e0]/lib64/libpthread.so.0[0x32172073da]/lib64/libc.so.6(clone+0x6d)[0x3216ae62bd]Trying to get some variables.Some pointers may be invalid and cause the dump to abort...thd-query at 0x1da1840 = Select * From mydb.mytblthd-thread_id=2thd-killed=NOT_KILLEDI am unable to get the root coz for this crash. The insert statement is as follows :-Insert into mydb.mytbl(ID, sub)Values (1, 'Hi This is a test msg');Well I am unable to get trace the reason behind this. can anyone please assist me on this. I am not even able to debug the problem. I can reporduce it by issuing same insert statement.Also I am using concept of symlinks in mysql data directory.Thanks in advance.--Manasi Save - End forwarded message -
Re: Delete table definition without .frm files.
Thanks Johan, I tried doing this. When I try to delete that empty table it is giving me an error saying Unknow table 'tblename'. I have created empty .frm file at folder level. can I create one from mysql. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman vegiv...@tuxera.be: Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I really don't know how to elaborate this problem because it is quite strange- I have deleted .frm files but not by issuing Drop table command I deleted the folder from backend at folder level as there was some problem. Is there any way I can delete this definition from innodb datafile? Thanks in advance. -- Regards, Manasi Save Artificial Machines Private Limited -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Delete table definition without .frm files.
Hi All,I really don't know how to elaborate this problem because it is quite strange-I have deleted .frm files but not by issuing Drop table command I deleted the folder from backend at folder level as there was some problem.Is there any way I can delete this definition from innodb datafile?Thanks in advance. --Regards, Manasi Save Artificial Machines Private Limited
Re: question regarding mysql database location
Hi Johan, It worked perfectly. Thank you so much for this explanation. I am really greatful. -- Best Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 3:42 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Make sure the directory the symlink points to has the same owner, group and permissions as your other databases. The permissions of the symlink itself are irrelevant (at least, on a Linux system. YMMV for other *nixen). *mytest1:~# cd /var/lib/mysql mytest1:/var/lib/mysql# ls -lh* total 117M -rw-r--r-- 1 root root 0 2009-11-19 12:08 debian-5.0.flag -rw-rw 1 mysql mysql 106M 2009-11-25 11:53 ibdata1 -rw-rw 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile0 -rw-rw 1 mysql mysql 5.0M 2009-11-25 11:53 ib_logfile1 drwxr-xr-x 2 mysql root 4.0K 2009-11-24 10:27 mysql -rw--- 1 root root 7 2009-11-19 12:08 mysql_upgrade_info drwx-- 2 mysql mysql 4.0K 2009-11-24 10:43 tmp *mytest1:/var/lib/mysql# mysql* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 690 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *mysql show databases;* ++ | Database | ++ | information_schema | | mysql | | tmp| ++ 10 rows in set (0.13 sec) mysql Bye *mytest1:/var/lib/mysql# mkdir /tmp/remotedatabase mytest1:/var/lib/mysql# chown mysql:mysql /tmp/remotedatabase mytest1:/var/lib/mysql# chmod u+rwX /tmp/remotedatabase mytest1:/var/lib/mysql# ln -s /tmp/remotedatabase ./ mytest1:/var/lib/mysql# mysql* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 691 Server version: 5.0.51a-24+lenny2 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. *mysql show databases;* ++ | Database | ++ | information_schema | | mysql | | remotedatabase | | tmp| ++ 11 rows in set (0.01 sec) *mysql use remotedatabase;* Database changed *mysql create table a (a int);* Query OK, 0 rows affected (0.04 sec) *mysql show tables;* +--+ | Tables_in_remotedatabase | +--+ | a| +--+ 1 row in set (0.01 sec) mysql Bye mytest1:/var/lib/mysql# Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Hi Nitin, Yes for the actually directories created mysql is the owner. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. Is mysql the owner of the directories? - Original Message From: Manasi Save manasi.s...@artificialmachines.com To: Johan De Meersman vegiv...@tuxera.be Cc: Waynn Lue waynn...@gmail.com; mysql mysql@lists.mysql.com Sent: Wed, November 25, 2009 8:12:25 PM Subject: Re: question regarding mysql database location Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ntn...@yahoo.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: question regarding mysql database location
Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. Regards, Manasi Save Artificial Machines Pvt Ltd. I fixed this by using symlinks for the directories for the underlying databases. The limit for files is significantly higher than directories. Waynn On 11/24/09, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=waynn...@gmail.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: question regarding mysql database location
Well Waynn, In this case I need to move all the existing databases to new location right. Which I don't want to do. Is it possible that I create sym link between two and use both. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. So assuming /var/lib/mysql/data/ is your mysql data directory, you could create a new directory called /var/lib/mysql/data/data1, then move all the directories from /var/lib/mysql/data/* into data1. Then you could create a symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/dir name. When mysql tries to load the data directory, it follows the symlink to the underlying directory (in /var/lib/mysql/data/data1). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). Also is it somthing that in disk1/mysql it will not create physical folder of it. /disk2/mysql/db4 (directory) /db5 (directory) /db6 (directory) -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. You don't need to move any databases. Look at this structure: /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) /disk2/mysql/db4 (directory) /db5 (directory) /db6 (directory) If your mysql data directory is set to /data/disk1/mysql, the server will pick up the symlinks there and use them as if they were just ordinary directories. On Wed, Nov 25, 2009 at 10:48 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Well Waynn, In this case I need to move all the existing databases to new location right. Which I don't want to do. Is it possible that I create sym link between two and use both. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 12:53 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Thanks Waynn, I could not get your point of using symlinks. Because as per my knowledge symlink will store same data which is there in original directory. and What do you mean by The limit for files is significantly higher than directories. Can you elaborate it more. Thanks in advance. So assuming /var/lib/mysql/data/ is your mysql data directory, you could create a new directory called /var/lib/mysql/data/data1, then move all the directories from /var/lib/mysql/data/* into data1. Then you could create a symlink in /var/lib/mysql/data/ pointing to /var/lib/mysql/data/data1/dir name. When mysql tries to load the data directory, it follows the symlink to the underlying directory (in /var/lib/mysql/data/data1). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Thanks Johan, It was really a great help. I'll try to implement it. I dont want to opt for multiple mysql instances option as thats not feasible. I'll get back to you all if it works fine. Thanks again. -- Best Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Dear Johan, Need your help again in understanding How mysql reads symlink. As you said below, I have created symlinks in default mysql directory. and try to read that symlink file as a database. But mysql is not reading that file as Database. Is there any settings which I need to change. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Wed, Nov 25, 2009 at 11:55 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi Johan, I am Sorry. If I have complicated the senerio But, this still not fix my purpose. What I want is - From your example :- /data/disk1/mysql/db1 (directory) /db2 (directory) /db3 (directory) /db4 (symlink to /data/disk2/mysql/db4) /db5 (symlink to /data/disk2/mysql/db5) /db6 (symlink to /data/disk2/mysql/db6) I dont want to create these directories here (/data/disk1/mysql/d4 /d5 /d6). They're not directories, they're symlinks, which are (to the OS) a kind of file, and thus not limited to 32000 per directory. They behave mostly identical to a directory, though, so MySQL will pick them up seamlessly, with the one hitch that you'll have to replace create database statements by mkdir and ln calls on the OS level. This is afaik the only way to do this on the MySQL level. It is impossible to specify multiple base directories. Another possible option, but higher in complexity and most likely less performant, would be to run two instances of MySQL on different ports with different data directories, and use MySQL Proxy to redirect incoming connections based on whatever criterion you could script into it - use database statements, for example. This is however going to come with it's very own set of catches and limitations. I'm not big on proxy, myself, so I'm afraid if the symlink option is not acceptable to you, I can't help you any further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How normal mysql server 5.1 uses multiple cores
Hi All, Can anyone provide me any input on How to make mysql use multiple CPU cores avaliable. I am sorry if I am souding very unclear with this. Let me know if you have any questions. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How normal mysql server 5.1 uses multiple cores
Hi Johan, Thanks for the quick response. Is there any command available in mysql using which I can check how much CPU is being used by each mysql thread. Or any article where how mysql multi-threading works. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. MySQL is already a multithreaded process, even though you only see a single process. Note that it doesn't scale very well above eight or so cores, especially InnoDB iirc. If you are wondering about parallel query execution (that is, splitting a single query over multiple cores for faster execution), that is currently not supported by MySQL. On Tue, Nov 24, 2009 at 12:02 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, Can anyone provide me any input on How to make mysql use multiple CPU cores avaliable. I am sorry if I am souding very unclear with this. Let me know if you have any questions. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
question regarding mysql database location
Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: question regarding mysql database location
Also I forgot to mention that I have gone through the innodb option of innodb_data_file_path but I can just specify it as : innodb_data_file_path=ibdata1:2048M:autoextend:max:1024M;ibdata1:2048M:autoextend:max:1024M; But not as : innodb_data_file_path=/var/lib/mysql/data/ibdata1:2048M:autoextend:max:1024M;/var/lib/mysql/data1/ibdata1:2048M:autoextend:max:1024M; Is there any wayout for this? Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. Hi All, I have asked this question before But, I think I am not able to describe it better. Sorry for asking it again. I have multiple databases but there is a limit on the folders getting created in one folder. I have mysql default directory set as /var/lib/mysql/data. Now, After 32000 folder creation I am not able to create more folders than that. Well Its not like I want to create 32000 database's in it (Which I wanted to earlier :-P). for example - I want to create 10 databases but 5 in /var/lib/mysql/data/d1 to d5 and othe 5 in /var/lib/mysql/data/d6 to d10. but I want to access all the databases that is d1-d10. as I ca change the database location after 5 databases but not able to access old five which I have created in old location. Please let me know if anymore information is needed on this. I am really looking for the solution. Please Help me. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. -- 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
mysql accessing one database from another
Hi All, I am needing to access a sub databases through main database. I have one main database and serveral sub databases. For accessing those databases I am using mysql prepared statements, But the performance I am getting because of this is very low. Can anyone suggest me any alternate way for this. Please let me know if you need any other information on this. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How NDBCluster cleans Local checkpoint
Hi All, Can anyone provide me some input on How NDB Cluster Local CheckPoint works. Can anyone provide any good article links where how NDB generates it and how to clean these logs. The senerio I am facing is I have set DataMemory as 2G and IndexMemory as 256M. and the size of my LCP folder is 2.9G. and on my management console I am getting following Message Node 3: Data usage increased to 96%(63442 32K pages of total 65536) Node 4: Data usage increased to 96%(63439 32K pages of total 65536) Need more information on this. Thanks in advance. -- 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
Strange mysql NDB Cluster error The table '' is full
Hi All, I am facing a strange error in my NDB Cluster database. When I am trying to insert record in one table using mysql command line. But when I try to insert record in same table using my java application it is getting inserted properly. Through command-line I am getting following error. | Error | 1296 | Got error 625 'Out of memory in Ndb Kernel, hash index part (increase IndexMemory)' from NDB | | Error | 1114 | The table '' is full | | Error | 1180 | Got error 136 during COMMIT | I have set index memory of 256 MB. Also I have one primary index on this table aloing with 3 unique index on it. and I have 2036778 records in that table. Let me know if anyone needs more information on this. Any input will be a great help. Thanks in advance. 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: mysql Create table in system database
Thanks Shawn for the quick response. But then What I am doing is I am doing copy paste of one database and rename it to another. but I cannot read the tables inside it. Can you tell me what might be the possible reason for that. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. Hello Manasi, Manasi Save wrote: Hi All, Can anyone give me any input on How mysql create table write data into system database and where it has been stored besides information_schema. Is there any article on mysql System Databases anyone went through as I am not able to find it on Google. I want to write a table information in mysql system database. Can anyone help me on this. Thanks in advance. MySQL does not store that information within an internal table. The basic information for each table is stored within a .frm file stored in the file system. The various additional pieces of metadata for each storage engine are maintained in methods specific to those storage engine. The information you see in the many tables exposed through INFORMATION_SCHEMA is generated dynamically based on the results of polling those separate sources of metadata at the time of your query. quoting from http://dev.mysql.com/doc/refman/5.1/en/information-schema.html ~~ Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. ~~ More details are available in the manual: http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/innodb-table-and-index.html http://dev.mysql.com/doc/refman/5.1/en/se-db2.html http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/federated-description.html http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/blackhole-storage-engine.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql Create table in system database
Hi All, Can anyone give me any input on How mysql create table write data into system database and where it has been stored besides information_schema. Is there any article on mysql System Databases anyone went through as I am not able to find it on Google. I want to write a table information in mysql system database. Can anyone help me on this. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Restore database through SP's
Hi All, I want to restore mysql database through stored procedure as I am creating databases at runtime. Is it possible? Is there any possible workaround for this? Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql dynamic database location
Hi All, Is it possible to change or create any database on a specific location. I want to specify a db path at runtime. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql prepare statement
Hi All,
I have a query about prepare statement of mysql. I want to use prepare
stmt for insert-update-delete with a dynamic dbname,So can anyone provide
any input how i can make prepare stmt and then after that provide a value
at runtime. for example :-
prepare stmt from Concat('Insert into ',?,'.IgnoreContact (testid)',
'Select ',?,',';');
Below is the procedure where i am preparing stmt at runtime. whereas i
want to pass the values at runtime.
If anyone can provide any information and links where i can find
information abt mysql prepare stmt from stored procedure.
DELIMITER $$
DROP PROCEDURE IF EXISTS `SP_Test` $$
CREATE definer=`testus...@`localhost` PROCEDURE `SP_Test`(InputTestID
Bigint, DBName Varchar(100))
BEGIN
SET @stmt = Concat('Insert into ',DBName,'.IgnoreContact (testid)',
'Select ',InputTestID,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
END $$
DELIMITER ;
Thanks in advance.
--
Regards,
Manasi Save
Artificial Machines Pvt Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sub query or something else
may be you can use IN clause:
SELECT SUM(price)*0.5 AS price1, SUM(price)*0.65 AS price2 FROM table
WHERE partner IN ('A', 'B');
--
Thanks and Regards,
Manasi Save
Artificial Machines Pvt Ltd.
I have these query:
SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A';
SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B';
Is it possible to make the queries into 1 single query? How to make it
happen? Many thanks for helps.
Willy
--
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
Re: Mysql update query gives error of lock wait timeout
Hi, You are right. The storage engine i m using is InnoDB. but then my concern is why this simple update query takes so long. Is it true that if its innodb table then even after specifying where condition it scans all indexed rows. I am bit confused as mysql.com have mentioned on there site that even if its a select it locks all the rows and it is reading with share lock mode. Can anyone give brief idea about what is exclusive lock and how it works and what is share lock mode and how it works. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Sat, Aug 29, 2009 at 12:09 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? Its depend on the storage engine used for the table. For example if the table use MyISAM then the transaction will lock entire table, but if you use InnoDB the transaction will lock only the row which used in transaction. *cmiiw* -- Muhammad Subair -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
lock wait timeout try restrating transaction
Hi All, Can anyone provide me any input on in what all senerios one can get this error. I have innodb tables, I am updating one table but I am getting error lock wait timeout try restarting transaction. Also the parameter innodb_lock_wait_timeout is set to 50 default. what will be the effect of increasing the limit of this parameter. - Thanks and 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
Mysql update query gives error of lock wait timeout
Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql Cluster data node Setup
Hi All, I have Set up MySQL Cluster This is my Config.ini file #options affecting ndbd processes on all data nodes: [ndbd default] NoOfReplicas=2# Number of replicas DataMemory=80M# How much memory to allocate for data storage IndexMemory=18M # How much memory to allocate for index storage # For DataMemory and IndexMemory, we have used the # default values. Since the world database takes up # only about 500KB, this should be more than enough for # this example Cluster setup. # TCP/IP options: [tcp default] portnumber=2202 # This the default; however, you can use any port that is free # for all the hosts in the cluster # Note: It is recommended that you do not specify the port # number at all and allow the default value to be used instead # Management process options: [ndb_mgmd] hostname=192.168.1.1 # Hostname or IP address of management node datadir=/var/lib/mysql-cluster # Directory for management node log files # Options for data node A: [ndbd] # (one [ndbd] section per data node) hostname=192.168.1.1 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files # Options for data node B: [ndbd] hostname=192.168.0.40 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files # SQL node options: [mysqld] hostname=192.168.1.1 # Hostname or IP address # (additional mysqld connections can be # specified for this node for various # purposes such as running ndb_restore) when I try to start both data nodes it is not starting any. But when I start only one data node A (192.168.1.1) after commenting node B (192.168.0.40). It is starting fine. can any one provide any input how to fix this error. also what configuaration do I need to make on node B server. I have just set Management node information on node B server. Thanks in advance. -- 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
