Update not returning any warning on failure

[Sujay Koduri]

 
I have a problem working with the stored procs.

The stored proc looks like this

create procedure unit_swap()
BEGIN
  DECLARE b INT DEFAULT 7;
  DECLARE c INT;
  DECLARE CONTINUE HANDLER FOR 1176 SET b = ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING SET b =
;

 UPDATE EMP SET STYLE='' WHERE ID='999';
 IF b =  THEN
INSERT INTO EMP (ID) VALUES ('999');
 END IF;
 
 COMMIT;

END;

The problem here is that even if update fails, meaning even if ID '999'
doesn't exist, MySQL doesn't report any error or warning (NOT FOUND error
handler is not catching this). So I have no way to declare error handler for
this statement and then taking the action accordingly. (INSETTING OR ANY
OTHER THING)

Any help on this is extremely useful.

Thank you
sujay

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Prepared grant statement?

[Sujay Koduri]

 
Yes even I have problems working with stored procs without prepared
statements support.
It would be great if someone from MySQL team can tell if they have plans to
include prepared statements in stored procs in the production release of
MySQL5.0.

Regards 
sujay 
-Original Message-
From: Adolfo Bello [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 07, 2005 4:28 PM
To: Mysql Lists
Subject: Prepared grant statement?

Hi list:

I don't know if this the right forum to ask the following questions:

Will MySQL production version 5.0 support grant in prepared statements?

The "yet" part is encouraging in "ERROR 1295 (HY000) at line 17: This
command is not supported in the prepared statement protocol yet"


Will prepared statements in stored procedures be supported? (I read that it
is disabled right now)

Regards,

Adolfo




__
Renovamos el Correo Yahoo! 
Nuevos servicios, más seguridad
http://correo.yahoo.es


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Prepared statements in MySQL

[Sujay Koduri]

Hi,

Though MySQl initially provided support for Prepared statements in MySQL
5.0, they have removed that support in the later beta versions of MySQL 5.0.
Right now MySQL does not recommend using prepared statements inside SP.
So it would be better if you go for some work around's like using 'if else'
loops.

sujay 

-Original Message-
From: Shaghayegh Sahebie [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 19, 2005 1:55 PM
To: mysql@lists.mysql.com
Subject: Prepared statements in MySQL

hi,
i've got some questions:
 
what happens when we deallocate a prepare statement?

can we use same name for 2 prepared statements in a SP by deallocating first
one before defining second one?

can we use a prepare statement in a loop?

can we use more than 1 prepared statement in a single SP?

Thanks in advance
Chagh



-
 Start your day with Yahoo! - make it your home page 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Phone Number Storage

[Sujay Koduri]

I think it is better to store the phone numbers as strings only. As phone
numbers may also include '-', if you allow entering international numbers,
it is good to store them as strings only. 

Or you can ask the area code and the actual number seperately and store them
seperately in two columns as integers. 

sujay 

-Original Message-
From: Asad Habib [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 25, 2005 11:53 PM
To: mysql@lists.mysql.com
Subject: Phone Number Storage

Is it better to store phone numbers as strings or as integers? Offcourse,
storing them as integers saves space but this requires extra processing of
the user's input (i.e. CPU time). Are there any other
advantages/disadvantages of doing it one way or the other?

- Asad

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Phone Number Storage

[Sujay Koduri]

I guess anywhere we have 3 levels of hierarchies for a phone number.
(Country code, Area code and the actual number).
The advantage of seperating them into different columns(Either an integer or
a string) is that he can group different phone numbers based on area code or
country code. 

sujay 

-Original Message-
From: Joerg Bruehe [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 26, 2005 1:34 AM
To: mysql@lists.mysql.com
Cc: Sujay Koduri; Asad Habib
Subject: Re: Phone Number Storage

Hi!

Sujay Koduri wrote (re-ordered):
> -Original Message-
> From: Asad Habib [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 25, 2005 11:53 PM
> To: mysql@lists.mysql.com
> Subject: Phone Number Storage
> 
> Is it better to store phone numbers as strings or as integers? 
> Offcourse, storing them as integers saves space but this requires 
> extra processing of the user's input (i.e. CPU time). Are there any 
> other advantages/disadvantages of doing it one way or the other?
> 
> - Asad


> I think it is better to store the phone numbers as strings only. As 
> phone numbers may also include '-', if you allow entering 
> international numbers, it is good to store them as strings only.
> 
> Or you can ask the area code and the actual number seperately and 
> store them seperately in two columns as integers.
> 

IMO, this is quite an USA-centric view in the answer: In general, phone
numbers will also contain a country code.

Outside the USA, it is quite common that codes (area or country) may begin
with a leading "0" which any numeric type would drop as not significant, so
you _must_ use strings for these.

Also: A telephone number is no numeric value, arithmetic operations do not
make sense on it. Think of extensions: phone numbers 1234-0 and
1234-56 are related, so you would order them (if at all) as strings and not
as numeric values.

The same applies to postal codes, social security numbers, part numbers etc.

While you may use a numeric type for some ID value you want to generate
yourself (using autoincrement), IMO this is on the borderline of correct
modeling. For phone numbers, you should use strings.

HTH,
Joerg

--
Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Please Give me Solution for this

[Sujay Koduri]

First check whether MySQL server is running on your machine or not.
You can use 'ps -aux | grep mysql' for checking this.

sujay

-Original Message-
From: Kumar Gaurav [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 02, 2005 10:31 AM
To: mysql@lists.mysql.com
Subject: Please Give me Solution for this

ERROR 2002: Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

I installed mysql-standard-4.1.11-pc-linux-gnu-i686] in my system.
There is no file in '/var/lib/mysql/mysql.sock'.
Thanks.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Getting the data dictionary in MySQL 4.1.13

[Sujay Koduri]

 
Hi,

Initially I used MySQL 5.0.4 for all my development. At that time I used the
following SQL query to load the whole data dictionary into my local memory
to reduce load on DB as I am using the prepared statements API, that
requires column types as one of the inputs.

select Table_Name,column_name,Data_Type from information_schema.columns;

But now we reverted back to 4.1.13 (for releasing on production). But there
is no information_schema database in MySQL 4.1.13. 
So can any one tell me how can I build the data dictionary in MySQL 4.1.13.

Thank you
sujay

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

my.cnf not found

[Sujay Koduri]

hi,
 
I tried installing MySQL4.1.13 and in the process I installed the following
packages

*   MySQL-client-4.1.13-0.i386.rpm
*   MySQL-server-4.1.13-0.i386.rpm
*   MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm

I am able to make connections to mysql and able to do update,insert,select
and everything over the database.
But i need to turnoff the auto commit option as i am planning to use INNOdb
tables, which needs to be edited in the my.cnf file (i donnow if i can
change this information anywhere else!!)
 
I looked for this file in the following locations.
 
/etc/
/var/lib/mysql/  but i am not able to find it.
please help me if i can do anything for this
 
Thank you
sujay

RE: my.cnf not found

[Sujay Koduri]

Thanks a lot stassen :)

sujay 

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 10, 2005 9:50 PM
To: Sujay Koduri
Cc: MySQL mailing list
Subject: Re: my.cnf not found

Sujay Koduri wrote:

> hi,
>  
> I tried installing MySQL4.1.13 and in the process I installed the 
> following packages
> 
> * MySQL-client-4.1.13-0.i386.rpm
> * MySQL-server-4.1.13-0.i386.rpm
> * MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm
> 
> I am able to make connections to mysql and able to do 
> update,insert,select and everything over the database.
> But i need to turnoff the auto commit option as i am planning to use 
> INNOdb tables, which needs to be edited in the my.cnf file (i donnow 
> if i can change this information anywhere else!!)
>  
> I looked for this file in the following locations.
>  
> /etc/
> /var/lib/mysql/  but i am not able to find it.
> please help me if i can do anything for this
>  
> Thank you
> sujay

my.cnf doesn't exist till you create it.  See the manual for more
<http://dev.mysql.com/doc/mysql/en/option-files.html>.

You don't need my.cnf to turn off autocommit, though.  In fact, you don't
even need to turn it off.  See the manual for details
<http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html>.

Michael

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL C API Version incompatibility

[Sujay Koduri]

hi,
 
I was connecting to MySQL 5.0.4 through the C API and it was workign fine.
But when I downgraded to MYSQL 4.1.13, the same code is giving the following
error.
 
mysql_stmt_bind_result() failed
Using unsupported buffer type: 0  (parameter: 1)
 
I tried uninstalling all the existing mysql rpm's and reinstall them again.
But this didnt help.
Please help on this .
 
Thank you
sujay

Uninstalling the MySQL cleanly.

[Sujay Koduri]

hi,
 
Can any one list the steps or give me any pointers how to cleanly uninstall
an upper version of MySQL,
so that after wards if we install lower version it should not be affected by
any left overs of the previous installation.
 
Thank you
sujay

RE: Uninstalling the MySQL cleanly.

[Sujay Koduri]

Sorry , I didn't send this in person to you intentionally.

sujay
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 11, 2005 2:42 PM
To: Sujay Koduri
Subject: Re: Uninstalling the MySQL cleanly.

.


>
> OS is RH9 and kernel is 2.4.20

I suggest you send this to the MySQL list :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server Upscene Productions http://www.upscene.com Database development
questions? Check the forum!
http://www.databasedevelopmentforum.com

> sujay
>
> -Original Message-
> From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 11, 2005 2:33 PM
> To: mysql@lists.mysql.com
> Subject: Re: Uninstalling the MySQL cleanly.
>
>
> > Can any one list the steps or give me any pointers how to cleanly
> uninstall
> > an upper version of MySQL,
> > so that after wards if we install lower version it should not be 
> > affected
> by
> > any left overs of the previous installation.
>
> What OS?
>
> With regards,
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS 
> SQL Server Upscene Productions http://www.upscene.com Database 
> development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Uninstalling the MySQL cleanly.

[Sujay Koduri]

Hi gleb,

I am including the packages which I tried to install along with the program
and the error I am getting when I tried running it.

Packages

MySQL-client-4.1.13-0.i386.rpm
MySQL-server-4.1.13-0.i386.rpm
MySQL-devel-4.1.13-0.i386.rpm
MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm

The error I am getting for the below program is 

Binding of columns failed
2036: Using unsupported buffer type: 0  (parameter: 1)

I tried running the program using the following command.
gcc -g prog_name -lmysqlclient

This is the program I am going to test.

#include 
#include 
#define INSERT "select column_name from information_schema WHERE Table_Name
= ?"
//This table is already existing. All the columns in the table are of type
varchar.

int main()
{
  MYSQL mysql;
  MYSQL_STMT *stmt;
  MYSQL_BIND bind[1];
  MYSQL_BIND bind_result[1];
  char str_in[50], str[2];
  unsigned long length[1];
  unsigned long str_in_len;
  my_bool is_null[1];

  if(!mysql_init(&mysql))
  {
exit(3);
  }

  if(!mysql_real_connect(&mysql, "localhost","user", "pass", "db", 0, NULL,
0))
  {
printf("%d: %s \n",mysql_errno(&mysql), mysql_error(&mysql));
exit();
  }

  stmt = mysql_stmt_init(&mysql);
  if(!stmt)
  {
printf("Out of memory..\n");
exit(3);
  }

  if ( mysql_stmt_prepare(stmt, INSERT, strlen(INSERT)) )
  {
printf("Prepare statement failed for insertion..:( \n");
exit(3);
  }

  bind[0].buffer_type = MYSQL_TYPE_VAR_STRING;
  bind[0].buffer = (char *)str_in;
  bind[0].buffer_length = 50;
  bind[0].is_null = 0;
  bind[0].length = &str_in_len;

  strncpy(str_in, "information_schema", 50);
  str_in_len = strlen(str_in);

  if ( mysql_stmt_bind_param(stmt,bind) )
  {
/*Failing here **/
printf("Binding of columns failed\n");
printf("%d: %s \n",mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
exit(3);
  }

  if (mysql_stmt_execute(stmt))
  {
printf("Executing prepared statement failed\n");
exit(3);
  }

  memset(bind_result, 0, sizeof(bind_result));
  bind_result[0].buffer_type = MYSQL_TYPE_STRING;
  bind_result[0].buffer = (char *)str;
  bind_result[0].buffer_length = 2;
  bind_result[0].is_null = &is_null[0];
  bind_result[0].length = &length[0];

  if (mysql_stmt_bind_result(stmt, bind_result))
  {
printf(" mysql_stmt_bind_result() failed\n");
printf(" %s\n", mysql_stmt_error(stmt));
exit(0);
  }

  /* Now buffer all results to client */
  if (mysql_stmt_store_result(stmt))
  {
printf(" mysql_stmt_store_result() failed\n");
printf(" %s\n", mysql_stmt_error(stmt));
exit(0);
  }

  while (!mysql_stmt_fetch(stmt))
  {

if (is_null[0])
  printf(" NULL\n");
else
  printf(" %s(%ld)\n", str, length[0]);

  }

  printf("The no of rows affected are %d\n",mysql_stmt_affected_rows(stmt));
  if (mysql_stmt_close(stmt))
  {
printf("Error while closing mysql statement\n");
exit(3);
  }
  mysql_close(&mysql);
  return 0;
}
 

Thank you
sujay

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 11, 2005 2:58 PM
To: mysql@lists.mysql.com
Subject: Re: Uninstalling the MySQL cleanly.

Hello.

I guess you have rpm installation, rpm -e should help you in this case.
Find installed mysql packets with `rpm -qa |grep -i mysql` command.
If you have troubles with making a choice, send the list of packets which
you got to the list.


Sujay Koduri <[EMAIL PROTECTED]> wrote:
> 
> 
> 
>>
>> OS is RH9 and kernel is 2.4.20
> 
> I suggest you send this to the MySQL list :-)
> 
> With regards,
>>
>> > Can any one list the steps or give me any pointers how to cleanly
>> uninstall
>> > an upper version of MySQL,
>> > so that after wards if we install lower version it should not be 
>> > affected
>> by
>> > any left overs of the previous installation.
>>
>> What OS?
>>


--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: prepared statement problems

[Sujay Koduri]

Hi,

The problem here is that you have two input bind variables, but you
declaring MysQL BIND array as  parm_bind[1], which can hold only one input
bind variable. Make it parm_bind[2]. That should work.

sujay

-Original Message-
From: Darrell Cormier [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 18, 2005 1:23 AM
To: mysql_list
Subject: prepared statement problems

Greetings,
I am trying to figure out prepared statements in the C-API.  The problem I
am having is passing parameters to a prepared statement.

If I hard code everything in my SQL statement, like: 
static char *sql_stmt = {
"select product "
"from lth "
"where facility = \"XTEX6\""
"and lot = \"5025267\" "
"and trn = \"LOGI\" "
};
everything works fine.  However, if I attempt to pass facility and lot as
parameters:
static char *sql_stmt = {
"select product "
"from lth "
"where facility = ? "
"and lot = ? "
"and trn = \"LOGI\" "
};

I am unable to get any data back.  I have enclosed my source code below.
Does anyone see where I have made a mistake that would prevent this from
working properly.

Sorry for the length.  I will greatly appreciate any assistance I can get.

Regards,
Darrell

--
Darrell Cormier <[EMAIL PROTECTED]>
Registered Linux user #370711 http://counter.li.org


// The following is my code//
///
#ifdef HAVE_CONFIG_H
#include 
#endif

#include 
#include 
#include "msql_conn_env.h"
#define STRING_SIZE 50
MYSQL_STMT*stmt;
MYSQL_BINDparm_bind[1], res_bind[1];
MYSQL_RES*ps_meta_result, *ps_results;
intparm_count, col_count, row_count, fetch_row_count;
unsigned longlength, str_length[2];
my_boolis_null[1];
static char *sql_stmt = {
"select product "
"from lth "
"where facility = ? "
"and lot = ? "
"and trn = \"LOGI\" "
};


using namespace std;

void print_error(MYSQL *conn, char *message)
{
cerr << message << endl;
if (conn != NULL)
{
cerr << "ERROR " << mysql_errno(conn) << ":  (" << 
mysql_error(conn) << ")" << endl;
}//end if(conn != NULL)
}//end print_error


int main(int argc, char *argv[])
{
charproduct[35], facility[6], lot[12];
string f_cility = "XTEX6";

conn=mysql_init(NULL);
cout << conn << endl;
if(conn==NULL)
{
print_error(conn,"F -- Database Login Failed!\n ");
exit(1);
} //end db init
if (!s)
{
s = getenv ("DB_LZS_CONNECT");
if (!s)
{
//error ("Umgebungs-Variable DB_LZS_CONNECT ist nicht 
definiert.");
//English translation needed:
cerr << "Environment Variable DB_LZS_CONNECT not defined!\n";
}
}
//if (mysql_real_connect(conn,s) == NULL)
if 
(mysql_real_connect(conn,my_host_name,my_user_name,my_pwd,my_db_name, 
my_port_number, my_socket_name, my_flags) == NULL)
//MySQL connection --> NULL = Failure; a successful connection would 
return first variable's value (i.e. MySQL connection handle).
{
cerr << "No connection to the data base server (LTS 
DB).\n";  // no connection to database server
}
else
{
cerr << "Connection to the LTS database was successful.\n";
}
   
stmt = mysql_stmt_init(conn);
if(!stmt)
{
cerr << "mysql_stmt_init() failure.  Possibly out of memory\n";
exit(0);
}
   
if(mysql_stmt_prepare(stmt, sql_stmt, strlen(sql_stmt)))
{
cerr << "mysql_stmt_prepare(), SELECT Failed!!\n";
cerr << mysql_stmt_error(stmt) << "\n";
exit(0);
}
cout << "mysql_stmt_prepare() was successful\n";
parm_count = mysql_stmt_param_count(stmt);
cout << "total parameters in SELECT:  " << parm_count << endl;
//NEED TO VALIDATE PARAMETERS HERE//
   
if (parm_count !=2)
{
cerr << " invalid parameter count returned by MySQL" << endl;
exit(0);
}
   
memset (parm_bind, 0, sizeof(parm_bind));
str_length[0]=6;
str_length[1]=12;
strncpy(lot, "5028368",12);
strncpy(facility, "XTEX6",6);

//bind facility
parm_bind[0].buffer_type= MYSQL_TYPE_STRING;
parm_bind[0].buffer= (char*)&facility;
parm_bind[0].buffer_length= 6;
parm_bind[0].is_null= 0;
parm_bind[0].length= &str_length[0];
//bind lot
parm_bind[1].buffer_type = MYSQL_TYPE_STRING;
parm_bind[1].buffer = (char*) &lot;
parm_bind[1].buffer_length = 12;
parm_bind[1].is_null=0;
parm_bind[1].length= &str_length[1];   
// Specify the data values for the parameters.
//strmov(szData, (char *)"venu");

//bind parameter buffers to prepared statement
if (mysql_stmt_bind_param (stmt, parm_bind))
{
cerr << " mysql_stmnt_bind_param() failed"  << endl;
cerr << mysql_stmt_error(stmt) << endl;
exit(0);
}
   
/* Fetch result set meta information */
 

RE: prepared statement problems

[Sujay Koduri]

 
Send the code if it is still not working.

sujay

-Original Message-
From: Darrell Cormier [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 18, 2005 7:16 PM
To: mysql_list
Subject: Re: prepared statement problems

Sujay Koduri said the following on 08/18/2005 12:31 AM:
>Hi,
>
>The problem here is that you have two input bind variables, but you 
>declaring MysQL BIND array as  parm_bind[1], which can hold only one 
>input bind variable. Make it parm_bind[2]. That should work.
>
>sujay
>
>  
Thank you for your response.  Unfortunately this is not the problem.  
I'm sorry I forgot to change that back when I pasted the code into this
email.  I originally had it as parm_bind[2] but then was trying to narrow my
problem and only work with one parameter in hopes of solving one at a time.
I then forgot to change it back to 2 before sending my request for help.
There must be something else here that I am missing.

Regards,
Darrell
p.s. I corrected the code below for the mistake you pointed out.


>If I hard code everything in my SQL statement, like: 
>static char *sql_stmt = {
>"select product "
>"from lth "
>"where facility = \"XTEX6\""
>"and lot = \"5025267\" "
>"and trn = \"LOGI\" "
>};
>everything works fine.  However, if I attempt to pass facility and lot 
>as
>parameters:
>static char *sql_stmt = {
>"select product "
>"from lth "
>"where facility = ? "
>"and lot = ? "
>"and trn = \"LOGI\" "
>};
>
>I am unable to get any data back.  I have enclosed my source code below.
>Does anyone see where I have made a mistake that would prevent this 
>from working properly.
>
>Sorry for the length.  I will greatly appreciate any assistance I can get.
>
>Regards,
>Darrell
>
>--
>Darrell Cormier <[EMAIL PROTECTED]>
>Registered Linux user #370711 http://counter.li.org
>
>
>// The following is my code//
>///
>#ifdef HAVE_CONFIG_H
>#include 
>#endif
>
>#include 
>#include 
>#include "msql_conn_env.h"
>#define STRING_SIZE 50
>MYSQL_STMT*stmt;
>MYSQL_BINDparm_bind[2], res_bind[1];
>MYSQL_RES*ps_meta_result, *ps_results;
>intparm_count, col_count, row_count, fetch_row_count;
>unsigned longlength, str_length[2];
>my_boolis_null[1];
>static char *sql_stmt = {
>"select product "
>"from lth "
>"where facility = ? "
>"and lot = ? "
>"and trn = \"LOGI\" "
>};
>
>
>using namespace std;
>
>void print_error(MYSQL *conn, char *message) {
>cerr << message << endl;
>if (conn != NULL)
>{
>cerr << "ERROR " << mysql_errno(conn) << ":  (" <<
>mysql_error(conn) << ")" << endl;
>}//end if(conn != NULL)
>}//end print_error
>
>
>int main(int argc, char *argv[])
>{
>charproduct[35], facility[6], lot[12];
>string f_cility = "XTEX6";
>
>conn=mysql_init(NULL);
>cout << conn << endl;
>if(conn==NULL)
>{
>print_error(conn,"F -- Database Login Failed!\n ");
>exit(1);
>} //end db init
>if (!s)
>{
>s = getenv ("DB_LZS_CONNECT");
>if (!s)
>{
>//error ("Umgebungs-Variable DB_LZS_CONNECT ist nicht 
>definiert.");
>//English translation needed:
>cerr << "Environment Variable DB_LZS_CONNECT not defined!\n";
>}
>}
>//if (mysql_real_connect(conn,s) == NULL)
>if 
>(mysql_real_connect(conn,my_host_name,my_user_name,my_pwd,my_db_name, 
>my_port_number, my_socket_name, my_flags) == NULL)
>//MySQL connection --> NULL = Failure; a successful connection would 
>return first variable's value (i.e. MySQL connection handle).
>{
>cerr << "No connection to the data base server (LTS 
>DB).\n";  // no connection to database server
>}
>else
>{
>cerr << "Connection to the LTS database was successful.\n";
>}
>   
>stmt = mysql_stmt_init(conn);
>if(!stmt)
>{
>cerr << "mysql_stmt_init() failure.  Possibly out of memory\n";
>exit(0);
>}
>   
>if(mysql_stmt_prepare(stmt, sql_stmt, strlen(sql_stmt)))
>{
>cerr

RE: prepared statement problems

[Sujay Koduri]

hi,
 
you should use strlen() instead of sizeof() to pass the length of input bind
parameter, otherwise it will read past the actual parameter and tries to
compare with the whole thing which is not intended.
 
str_length[0]= sizeof(facility);
str_length[1]=sizeof(lot);
 
is_null[0] is not initialized to either '0' or '1', which actually should
be.
STRING_SIZE should be max length which product can hold. should be 35.
 
i guess these changes would help you.
 
sujay

 

  _  

From: Darrell Cormier [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 18, 2005 7:33 PM
To: mysql_list
Cc: Sujay Koduri
Subject: Re: prepared statement problems


Sujay Koduri said the following on 08/18/2005 08:48 AM: 

 

Send the code if it is still not working.



sujay



  

Not sure what you mean by send the code since it is included at the end of
this email.  However, I have also attached the file.  Since I am not sure if
the list will accept files, I have also copied you on this message.  Please
forgive me if this is not what you intended.

Kindest regards,
Darrell Cormier



#ifdef HAVE_CONFIG_H

#include 

#endif



#include 

#include 

#include "msql_conn_env.h"

#define STRING_SIZE 50

MYSQL_STMT  *stmt;

MYSQL_BIND  parm_bind[2], res_bind[1];

MYSQL_RES   *ps_meta_result, *ps_results;

int parm_count, col_count, row_count, fetch_row_count;

unsigned long   length, str_length[2];

my_bool is_null[1];

static char  *sql_stmt = {

"select product "

"from lth "

"where facility = ? "

"and lot = ? "

"and trn = \"LOGI\" "

};





using namespace std;



void print_error(MYSQL *conn, char *message)

{

cerr << message << endl;

if (conn != NULL)

{

cerr << "ERROR " << mysql_errno(conn) << ":  (" <<
mysql_error(conn) << ")" << endl;

}//end if(conn != NULL)

}//end print_error





int main(int argc, char *argv[])

{

charproduct[35], facility[6], lot[12];

string f_cility = "XTEX6";



conn=mysql_init(NULL);

cout << conn << endl;

if(conn==NULL)

{

print_error(conn,"F -- Database Login Failed!\n ");

exit(1);

} //end db init 

if (!s)

{

s = getenv ("DB_LZS_CONNECT");

if (!s)

{

//error ("Umgebungs-Variable DB_LZS_CONNECT ist
nicht definiert.");

//English translation needed:

cerr << "Environment Variable DB_LZS_CONNECT not
defined!\n";

}

}

//if (mysql_real_connect(conn,s) == NULL) 

if
(mysql_real_connect(conn,my_host_name,my_user_name,my_pwd,my_db_name,
my_port_number, my_socket_name, my_flags) == NULL) 

//MySQL connection --> NULL = Failure; a successful connection would return
first variable's value (i.e. MySQL connection handle).

{

cerr << "No connection to the data base server (LTS DB).\n";
// no connection to database server

}

else

{

cerr << "Connection to the LTS database was successful.\n";

}



stmt = mysql_stmt_init(conn);

if(!stmt)

{

cerr << "mysql_stmt_init() failure.  Possibly out of
memory\n";

exit(0);

}



if(mysql_stmt_prepare(stmt, sql_stmt, strlen(sql_stmt)))

{

cerr << "mysql_stmt_prepare(), SELECT Failed!!\n";

cerr << mysql_stmt_error(stmt) << "\n";

exit(0);

}

cout << "mysql_stmt_prepare() was successful\n";

parm_count = mysql_stmt_param_count(stmt);

cout << "total parameters in SELECT:  " << parm_count << endl;

//NEED TO VALIDATE PARAMETERS HERE//



if (parm_count !=2)

{

cerr << " invalid parameter count returned by MySQL" << endl;

exit(0);

}



memset (parm_bind, 0, sizeof(parm_bind));

str_length[0]= sizeof(facility);

str_length[1]=sizeof(lot);

strncpy(lot, "5028368",12);

strncpy(facility, "XTEX6",6);



//bind facility

parm_bind[0].buffer_type= MYSQL_TYPE_STRING;

parm_bind[0].buffer= (void*)&facility;

parm_bind[0].buffer_length= sizeof(facility);

parm_bind[0].is_null = &is_null[0];

parm_bind[0].length= &

Commit needed even after a select statement ??

[Sujay Koduri]

hi,
 
i am using MysQL4.1.13. I tried doing the following.

*   Open a new session to DB. (Say session 1)
*   Using prepared statements provided by C API, issued a select
statement to get some rows from a table.
*   This worked absolutely fine.
*   I didnt close the session, but still using the same session to issue
the same select statement and get the results again. (while loop)
*   Before issuing the second select statement on session 1, I opened a
new session(say session 2) and updated the entries in the table. Did a
commit.
*   Now i issued the second select statement on session 1, but still it
is showing the old results. Idealy it should show the new results, which are
a result of updations done using session 2.

But when i issue a commit after the first select on session 1, and do the
rest as i mentioned above, it is showing all the results properly.
Is this the intended behaviour or do i need to make any configuration
changes or any ??
 
Thank you
sujay

RE: Commit needed even after a select statement ??

[Sujay Koduri]

 
No I am performing the second select Query only. I am including the pseudo
code which I am trying to do.

Pseudo code

Establish a connection to DB
Get statement handler
While(conditon) {
Prepare a select statement.
Bind input variable.
Execute the statement.
Bind o/p vars.
Fetch results.
Wait before I update the rows of that table in the DB using a separate
session.
}
Close  connection to DB.

-Original Message-
From: Jasper Bryant-Greene [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 19, 2005 11:48 AM
To: mysql@lists.mysql.com
Subject: Re: Commit needed even after a select statement ??

Sujay Koduri wrote:
> hi,
>  
> i am using MysQL4.1.13. I tried doing the following.
> 
> * Open a new session to DB. (Say session 1)
> * Using prepared statements provided by C API, issued a select
> statement to get some rows from a table.
> * This worked absolutely fine.
> * I didnt close the session, but still using the same session to issue
> the same select statement and get the results again. (while loop)
> * Before issuing the second select statement on session 1, I opened a
> new session(say session 2) and updated the entries in the table. Did a 
> commit.
> * Now i issued the second select statement on session 1, but still it
> is showing the old results. Idealy it should show the new results, 
> which are a result of updations done using session 2.
> 
> But when i issue a commit after the first select on session 1, and do 
> the rest as i mentioned above, it is showing all the results properly.
> Is this the intended behaviour or do i need to make any configuration 
> changes or any ??

Are you actually performing a second SELECT query, or are you just fetching
the second row from the original SELECT query. The former should show the
new results, while I believe the latter will show the old results as it has
already performed the query.

Jasper

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Commit needed even after a select statement ??

[Sujay Koduri]

Thanks a lot Philippe. Its working. :)

sujay 

-Original Message-
From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 19, 2005 12:43 PM
To: 'mysql@lists.mysql.com '
Subject: Re: Commit needed even after a select statement ??

HI,
n(say session 2) and updated the entries in the table. Did a
> commit.
> * Now i issued the second select statement on session 1, but still it
> is showing the old results. Idealy it should show the new results, 
> which are a result of updations done using session 2.
> 
> But when i issue a commit after the first select on session 1, and do 
> the rest as i mentioned above, it is showing all the results properly.
> Is this the intended behaviour or do i need to make any configuration 
> changes or any ??

I guess you are using the default behaviour which is : 'Repeatable-read'
http://dev.mysql.com/doc/mysql/en/innodb-transaction-isolation.html
try 'read uncommited', or just drop the transaction since what you are
observing is perfectly normal...

--
Philippe Poelvoorde
COS Trading Ltd.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: not null values

[Sujay Koduri]

Use the keyword 'default' to make the attributes default to the values you
want. 

Use some thing like this 
user_name varchar(50) default 'default_value' not null

sujay
-Original Message-
From: joshua pereira [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 29, 2005 5:24 PM
To: mysql@lists.mysql.com
Subject: not null values

i want to make it so that i will have to fill in all the attributes in .so i
put not null for all the attributes. Is this correct ?? when  for example do
not fill in user_name , all the other values is accepted and stored in the
databaseplease advise

create table user_details
(user_id varchar(10) not null primary key, user_name varchar(50) not null,
user_email varchar (30) not null, user_contact int not null, user_username
varchar (20) not null, user_password varchar (20) not null );

thank you...

Send instant messages to your online friends http://uk.messenger.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Major Difference in response times when using Load Infile utility

[Sujay Koduri]

hi ,
 
I am using the Load Infile utility to load data from file to MySQL DB.
When trying to load different amounts of data, I observed a notable
difference in the time taken by that.
 
Test 1
 
Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately.
 
Test2
 
Amount of data - 0.45 million rows. Time Taken - 2 mins approximately.
 
Can some one explain why this difference is coming. Also it will be great if
someone can suggest how we can improve the performance of the first test.
 
Thank you
sujay

RE: Major Difference in response times when using Load Infile uti lity

[Sujay Koduri]

Yes, there are indexes on the table. Do you mean to say index is the
culprit.

sujay 

-Original Message-
From: Peter J Milanese [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 13, 2005 4:58 PM
To: Sujay Koduri; mysql
Subject: Re: Major Difference in response times when using Load Infile
utility

Are there indexes on the table? Could be that.

--Original Message--
From: Sujay Koduri
To: mysql
Sent: Sep 13, 2005 5:24 AM
Subject: Major Difference in response times when using Load Infile utility

hi ,

I am using the Load Infile utility to load data from file to MySQL DB.
When trying to load different amounts of data, I observed a notable
difference in the time taken by that.

Test 1

Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately.

Test2

Amount of data - 0.45 million rows. Time Taken - 2 mins approximately.

Can some one explain why this difference is coming. Also it will be great if
someone can suggest how we can improve the performance of the first test.

Thank you
sujay


-
Sent from my NYPL BlackBerry Handheld.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Major Difference in response times when using Load Infile uti lity

[Sujay Koduri]

Hi

I am INNODB tables only. (I have only one big table) 
Autocommit I have already disabled.
And it doesn't have any foreign keys in it. Do I still need to set the
FOREIGN_KEY_CHECKS to '0'??

sujay
-Original Message-
From: Alan Williamson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 13, 2005 8:48 PM
To: mysql@lists.mysql.com
Subject: RE: Major Difference in response times when using Load Infile
utility

> Test 1
> Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately.
>  
> Test2
> Amount of data - 0.45 million rows. Time Taken - 2 mins approximately.

Is this an InnoDB database by any chance?  If it is, and it is a clean
import, then disable the FOREIGN_KEY_CHECKS.  

  SET AUTOCOMMIT = 0;
  SET FOREIGN_KEY_CHECKS=0;

This is a small tip i picked up on the MySQL documentation that someone had
left in the comments and has been to date one of those tips that has
literally saved DAYS of my life.

a

ps Remember to put them back on again after you finish the import

  SET AUTOCOMMIT = 1;
  SET FOREIGN_KEY_CHECKS=1;

--
 Alan Williamson, Technology Evangelist
 SpikeSource Inc.
 Daily OS News @ http://compiledby.spikesource.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Major Difference in response times when using Load Infile uti lity

[Sujay Koduri]

Hi all,

I found that load infile should not take this much time(6 hrs) to load 5.5
million queries. Some people are saying it should not even take more than
10mins. So I think I am doing something wrong in my my.cnf file. I am Using
MySQL 4.1.13 version and 2.4.20 kernel on RH9. I am including the my.cnf as
well as the 'show table status' output here. Please let me know if any extra
info is needed.

M/c config : 2G RAM and Dual CPU 32-bit.

mysql> show table status;
+++-++-++---
--+-+--+---++---
--+-++---+--
++--+
| Name   | Engine | Version | Row_format | Rows| Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation |
Checksum | Create_options | Comment  |
+++-++-++---
--+-+--+---++---
--+-++---+--
++--+
| stats  | InnoDB |   9 | Dynamic| 4237002 |   1028 |
4357881856 |NULL |150749184 | 0 |   NULL |
2005-09-12 23:16:21 | NULL| NULL   | latin1_swedish_ci |
NULL || InnoDB free: 16188416 kB |
+++-++-++---
--+-+--+---++---
--+-++---+--
++--+
1 row in set (2.41 sec)

I am running the load file utility from the server itself. (server
config..2G RAM, Dual CPU, RH9, 2.4.20 kernel, alloted 20G of disk space,
both logs and data files are on same partition). I am including the my.cnf
file which I am using now. Can someone please tell me if I am setting
anything seriously wrong.

$$cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-table-type=innodb
init_connect='SET AUTOCOMMIT=0'
transaction-isolation = READ-COMMITTED
key_buffer = 250M
wait_timeout = 10
max_connections = 400
connect_timeout = 50
table_cache = 1024
max_allowed_packet = 4M
sort_buffer_size = 2M
read_buffer_size = 2M
binlog_cache_size = 1M
max_heap_table_size = 64M
max_user_connections = 1000
thread_concurrency = 4
query_cache_type = 0
query_cache_limit = 2M
query_cache_size = 32M
#thread_stack = 96k
tmp_table_size = 32M
log_error
log_warnings = 2
log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp

# *** INNODB Specific options ***
#This conf uses 65%(Between 50 and 80%) of RAM, so glibc crash #should not
be a problem.
innodb_buffer_pool_size = 700M
innodb_data_home_dir =
innodb_data_file_path =
/data/data1:2G;/data/data2:2G;/data/data3:2G;/data/data4:2G;/data/data5:2G;/
data/data6:2G;/data/data7:2G;/data/data8:2G;/data/data9:2G;/data/data10:2G:a
utoextend
innodb_fast_shutdown = 1
innodb_file_io_threads = 4
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 90
innodb_log_file_size = 100M
innodb_log_files_in_group =5
innodb_log_group_home_dir = /logs

[mysql.server]
user=mysql
basedir=/var/lib

[mysqldump]
quick
max_allowed_packet=16M

[safe_mysqld]
default-table-type=innodb
init_connect='SET AUTOCOMMIT=0'
err-log=/var/log/mysqld.log

[mysqld_safe]
default-table-type=innodb
init_connect='SET AUTOCOMMIT=0'
err-log=/var/log/mysqld.log
open-files-limit = 4096

Thank you
sujay

-Original Message-
From: Alan Williamson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 13, 2005 8:48 PM
To: mysql@lists.mysql.com
Subject: RE: Major Difference in response times when using Load Infile
utility

> Test 1
> Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately.
>  
> Test2
> Amount of data - 0.45 million rows. Time Taken - 2 mins approximately.

Is this an InnoDB database by any chance?  If it is, and it is a clean
import, then disable the FOREIGN_KEY_CHECKS.  

  SET AUTOCOMMIT = 0;
  SET FOREIGN_KEY_CHECKS=0;

This is a small tip i picked up on the MySQL documentation that someone had
left in the comments and has been to date one of those tips that has
literally saved DAYS of my life.

a

ps Remember to put them back on again after you finish the import

  SET AUTOCOMMIT = 1;
  SET FOREIGN_KEY_CHECKS=1;

--
 Alan Williamson, Technology Evangelist
 SpikeSource Inc.
 Daily OS News @ http://compiledby.spikesource.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/my

RE: good database design

[Sujay Koduri]

My 2 cents..

Before you actually start worrying about the performance tuning of database
parameters or hardware required for the DB, you should make sure that you
have designed the database properly by taking care of all aspects  like
normalisation, denormalisation (??). If you don't take care of these logical
design aspects in the early stages properly, these things will prove you
very costly in the long run.

Th easy and recommended way to do it is

.Draw an E-R diagram
.Do any normalization.
.Identify proper datatypes for the table creation.
.Identify and add proper indexes.
.And now actually you should start worrying abt the DB Tuning and harware
requirements.

sujay

-Original Message-
From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 22, 2005 2:17 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: good database design

Tim Hayes wrote:

>I disagree completely.
>
>I prefer to have regard to the statement of requirement, which in this 
>case is a concern over performance. If following conventional design 
>rules creates performance issues, then performance related issues come 
>first when considering design.
>
>  
>
- personally, I would consider integrity, and then reliability, above
performance. But then 80% of any performance hit is in the application code.
Design a database that gives you confidence in the data it stores first and
foremost.

- ian

-- 
+---+
| Ian Sales  Database Administrator |
|   |
|  "All your database are belong to us" |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: good database design

[Sujay Koduri]

This is what I am also saying.
The effects of a bad logical DB design will effect you the most only in the
long term. In the earlier stages you always trust your own design and always
look for additional h/w resources to improve the performance. But in the
long term you will realize that there is something other than adding h/w you
have to do. That's when we actually realise the mistakes we have done in
logical design phase.

suhay 

-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 22, 2005 3:13 PM
To: mysql@lists.mysql.com
Subject: Re: good database design



> This is an interesting subject area.
>
> In a data warehousing environment, one tends to adopt table structures
such
> as snowflake layouts which lead to improved performance.
>
> Createing a perfect normalised database design may well lead to
performance
> issues.

If this is the case, go bug the database vendors :-) ... they should give us
systems that work properly ...

>The more joins you have, by far the worse the performance. You may

That's a pretty bold statement...

> need to consider horizontal or vertical table splits. You may need to 
> consider replicating certain data in child tables to avoid joins.
>
> I am not saying you do not need to carry out data analysis and gain a 
> full and first hand understanding of the data structures. It is just 
> that when
it
> comes to online performance, sometimes you have to break the rules.

But still: logical first, performance later... If at all.

I once joined a team that had a running Oracle database and an application
on top of it. We were having performance problems and there was the
"2 seconds of max response time" requirement in the contract.
We tweaked Oracle (not particularly the fastest beast on the block), we
threw hardware at it. Both options worked... for a while.
Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data -
the response time was alright. But it complicated our application, the
database design and the stored procedures using it... Not a particular
pleasant experience.

Then again... years later, I realized that the design should have been
different (better logical structure) and these problems would have been
avoided... Pity we couldn't do that part again...

Learned a lot though.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server Upscene Productions http://www.upscene.com Database development
questions? Check the forum!
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Avg row length is varying a lot from oracle to MySQL

[Sujay Koduri]

hi ,,

we are converting our oracle DB to MySQL DB. One problem i see is that the

abg row length in MySQL is much higher compared to that of Oracle.

In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as

a result, MySQL is taking more space to store the same number of records. 

Can someone please explain me if this is the intended behaviour or i am

missing out something. I am also including the o/p of desc table_name of the

same table on both the databases. 

This is a bit urgent. So any help is greatly appreciated.

ID NOT NULL VARCHAR2(50)

H0 NUMBER

H1 NUMBER

H2 NUMBER

H3 NUMBER

H4 NUMBER

H5 NUMBER

H6 NUMBER

H7 NUMBER

H8 NUMBER

H9 NUMBER

H10 NUMBER

H11 NUMBER

H12 NUMBER

H13 NUMBER

H14 NUMBER

H15 NUMBER

H16 NUMBER

H17 NUMBER

H18 NUMBER

H19 NUMBER

H20 NUMBER

H21 NUMBER

H22 NUMBER

H23 NUMBER

D1 NUMBER

D2 NUMBER

D3 NUMBER

D4 NUMBER

D5 NUMBER

D6 NUMBER

D7 NUMBER

D8 NUMBER

D9 NUMBER

D10 NUMBER

D11 NUMBER

D12 NUMBER

D13 NUMBER

D14 NUMBER

D15 NUMBER

D16 NUMBER

D17 NUMBER

D18 NUMBER

D19 NUMBER

D20 NUMBER

D21 NUMBER

D22 NUMBER

D23 NUMBER

D24 NUMBER

D25 NUMBER

D26 NUMBER

D27 NUMBER

D28 NUMBER

D29 NUMBER

D30 NUMBER

D31 NUMBER

D32 NUMBER

D33 NUMBER

D34 NUMBER

D35 NUMBER

D36 NUMBER

D37 NUMBER

D38 NUMBER

D39 NUMBER

D40 NUMBER

UPDATE_SECS NUMBER

B_UPDATE_SECS NUMBER

B1 NUMBER

B2 NUMBER

B3 NUMBER

B4 NUMBER

B5 NUMBER

B6 NUMBER

B7 NUMBER

B8 NUMBER

B9 NUMBER

B10 NUMBER

DATE_ADDED DATE

DATE_MODIFIED DATE

UPDATED DATE

ORIGINAL VARCHAR2(50)

COUNT NUMBER(10)

IPADDR VARCHAR2(16)

HI NUMBER

IM VARCHAR2(15)

ST VARCHAR2(20)

BS NUMBER

USERID NUMBER(10)

PAGE NUMBER

URL VARCHAR2(150)

DESCRIPTION VARCHAR2(100)

TAG VARCHAR2(4)

NH NUMBER

REFRESH NUMBER

POPULATE VARCHAR2(6)

LERY VARCHAR2(1)

LIST VARCHAR2(1)

LITE VARCHAR2(1)

STING_ID VARCHAR2(20)

YN VARCHAR2(1)

RY_ID VARCHAR2(9)

RATED VARCHAR2(1)

CREATED DATE

In mysql

+---+-+--+-+

---+---+

| Field | Type | Null | Key | Default

| Extra |

+---+-+--+-+

---+---+

| id | varchar(50) | | PRI | |

|

| H0 | tinyint(3) unsigned | YES | | NULL |

|

| H1 | tinyint(3) unsigned | YES | | NULL |

|

| H2 | tinyint(3) unsigned | YES | | NULL |

|

| H3 | tinyint(3) unsigned | YES | | NULL |

|

| H4 | tinyint(3) unsigned | YES | | NULL |

|

| H5 | tinyint(3) unsigned | YES | | NULL |

|

| H6 | tinyint(3) unsigned | YES | | NULL |

|

| H7 | tinyint(3) unsigned | YES | | NULL |

|

| H8 | tinyint(3) unsigned | YES | | NULL |

|

| H9 | tinyint(3) unsigned | YES | | NULL |

|

| H10 | tinyint(3) unsigned | YES | | NULL |

|

| H11 | tinyint(3) unsigned | YES | | NULL |

|

| H12 | tinyint(3) unsigned | YES | | NULL |

|

| H13 | tinyint(3) unsigned | YES | | NULL |

|

| H14 | tinyint(3) unsigned | YES | | NULL |

|

| H15 | tinyint(3) unsigned | YES | | NULL |

|

| H16 | tinyint(3) unsigned | YES | | NULL |

|

| H17 | tinyint(3) unsigned | YES | | NULL |

|

| H18 | tinyint(3) unsigned | YES | | NULL |

|

| H19 | tinyint(3) unsigned | YES | | NULL |

|

| H20 | tinyint(3) unsigned | YES | | NULL |

|

| H21 | tinyint(3) unsigned | YES | | NULL |

|

| H22 | tinyint(3) unsigned | YES | | NULL |

|

| H23 | tinyint(3) unsigned | YES | | NULL |

|

| D1 | tinyint(4) | YES | | NULL |

|

| D2 | tinyint(4) | YES | | NULL |

|

| D3 | tinyint(4) | YES | | NULL |

|

| D4 | tinyint(4) | YES | | NULL |

|

| D5 | tinyint(4) | YES | | NULL |

|

| D6 | tinyint(4) | YES | | NULL |

|

| D7 | tinyint(4) | YES | | NULL |

|

| D8 | tinyint(4) | YES | | NULL |

|

| D9 | tinyint(4) | YES | | NULL |

|

| D10 | tinyint(4) | YES | | NULL |

|

| D11 | tinyint(4) | YES | | NULL |

|

| D12 | tinyint(4) | YES | | NULL |

|

| D13 | tinyint(4) | YES | | NULL |

|

| D14 | tinyint(4) | YES | | NULL |

|

| D15 | tinyint(4) | YES | | NULL |

|

| D16 | tinyint(4) | YES | | NULL |

|

| D17 | tinyint(4) | YES | | NULL |

|

| D18 | tinyint(4) | YES | | NULL |

|

| D19 | tinyint(4) | YES | | NULL |

|

| D20 | tinyint(4) | YES | | NULL |

|

| D21 | tinyint(4) | YES | | NULL |

|

| D22 | tinyint(4) | YES | | NULL |

|

| D23 | tinyint(4) | YES | | NULL |

|

| D24 | tinyint(4) | YES | | NULL |

|

| D25 | tinyint(4) | YES | | NULL |

|

| D26 | tinyint(4) | YES | | NULL |

|

| D27 | tinyint(4) | YES | | NULL |

|

| D28 | tinyint(4) | YES | | NULL |

|

| D29 | tinyint(4) | YES | | NULL |

|

| D30 | tinyint(4) | YES | | NULL |

|

| D31 | tinyint(4) | YES | | NULL |

|

| D32 | tinyint(4) | YES | | NULL |

|

| D33 | tinyint(4) | YES | | NULL |

|

| D34 | tinyint(4) | YES | | NULL |

|

| D35 | tinyint(4) | YES | | NULL |

|

| D36 | tinyint(4) | YES | | NULL |

|

| D37 | tinyint(4) | YES | | NULL |

|

| D38 | tinyint(4) | YES | | NULL |

|

| D39 | tinyint(4) | YES | | NULL |

|

| D40 | tinyint(4) | YES | | NULL |

|

| UPDATE_SECS | int(10) uns

RE: Avg row length is varying a lot from oracle to MySQL

[Sujay Koduri]

Each row in the table takes around 600 bytes, taking every thing into
consideration and assuming every field is used to its maximum bytes. But the
major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 +
16 + 50 + 20 + 9..)
Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them
are reserved for future uses. So strictly speaking even including the space
taken by the indexes, the avg length should not come more than 250 bytes.

sujay

-Original Message-
From: Jeff [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 23, 2005 1:12 AM
To: mysql@lists.mysql.com
Subject: RE: Avg row length is varying a lot from oracle to MySQL

> -Original Message-
> From: Sujay Koduri [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 22, 2005 15:23
> To: mysql@lists.mysql.com
> Subject: Avg row length is varying a lot from oracle to MySQL
> 
> 
> hi ,,
> 
> we are converting our oracle DB to MySQL DB. One problem i see is that 
> the
> 
> abg row length in MySQL is much higher compared to that of Oracle.
> 
> In oracle it is around 180 bytes and in MySQL it is around
> 686 bytes. So as
> 
> a result, MySQL is taking more space to store the same number of 
> records.
> 
> Can someone please explain me if this is the intended behaviour or i 
> am
> 
> missing out something. I am also including the o/p of desc table_name 
> of the
> 
> same table on both the databases. 

Probably do to the way the two database store data and how much space they
reserve for specific column types.

For a way to calculate row size see this link.  Relize you must also
calculate the size of all indexes.

http://dev.mysql.com/doc/mysql/en/storage-requirements.html



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: regarding supersmack

[Sujay Koduri]

I don't think we can change/add datatypes unless you have to play with the
code.

sujay 

-Original Message-
From: Sreedhar Kharidehal [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 23, 2005 3:06 PM
To: mysql@lists.mysql.com
Subject: regarding supersmack

Can i generate date type values with super smack.The format specifiers show
only string and integer .
Thanks

--
sreedhar

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Multithread handling of Connect/Close

[Sujay Koduri]

 

I think mysql does not allow multiple threads to act on the same connection.
You have to create a connection pool and pick one for each thread

sujay 

-Original Message-
From: Lefteris Tsintjelis [mailto:[EMAIL PROTECTED]
Sent: Saturday, September 24, 2005 4:39 PM
To: mysql@lists.mysql.com
Subject: Multithread handling of Connect/Close

Hi,

In case I compile with -lpthread, can I just simply take out of the
loop the MySQLConnect/MySQLClose and run over the same connection multiple
threads? Are there any other changes required to handle multi threading?

Is there any --enable-thread-safe example?

Thnx again,

Lefteris

--- No thread safe example ---

MYSQL *sql;

int MySQLConnect(char *host, char *user, char *passwd, char *db) {
 if( !(sql=mysql_init(NULL)) )
 return(-1);

 if( !(mysql_real_connect(sql, host, user, passwd, db, 0, NULL, 0))
)
 return(-2);

 return(0);
}

void MySQLClose(void)
{
 if(sql)
 mysql_close(sql);
}

void main(void)
{

mysql_server_init(0, NULL, NULL);

while(1==1)
{
s=WaitTcpServer(srv);
if(s>=0)
{
cid=fork();
if(cid < 0)
exit(-2));
if(cid==0)
{
MySQLConnect( sqlhost, sqluser, sqlpasswd,
sqldb);
ch=ChildHandle();
MySQLClose();
waitpid(-1, &status, WNOHANG);
exit(ch);
}
close(s);
}
else
exit(-1)
}

mysql_server_end();
exit(0);

}

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Multithread handling of Connect/Close

[Sujay Koduri]

http://dev.mysql.com/doc/mysql/en/c-thread-functions.html

Have a look at this. Also if you are compiling it using the pthread library,
you can directly use the functions provided by ptherad library.
And you need not to do the forking also. This sample code might give you an
understanding..

Int hit_DB {
Create a connection here and use for whatever purpose.
}

 for (user = 0; user < concurrent_users; user++) {
  pthread_attr_init(&attr[user]);
  pthread_attr_setschedpolicy(&attr[user], SCHED_RR);
 }

 for (user = 0; user < concurrent_users; user++) {
  pthread_create(&helper[user], NULL, hit_DB, (void *)user);
 }

 for (user = 0; user < concurrent_users; user++) {
  pthread_join(helper[user], NULL);
 } 

Hope this helps...

sujay
-Original Message-
From: Lefteris Tsintjelis [mailto:[EMAIL PROTECTED] 
Sent: Saturday, September 24, 2005 5:37 PM
To: Sujay Koduri
Cc: mysql@lists.mysql.com
Subject: Re: Multithread handling of Connect/Close

Sujay Koduri wrote:

> I think mysql does not allow multiple threads to act on the same
connection.
> You have to create a connection pool and pick one for each thread

Either that or keep handling them on a per thread basis but do I have to
also do mysql_server_init/end on a per child then, or just once in the
parent proc?

while loop childid
{
fork();
...
mysql_server_init(...);
mysql_real_connect(...)

...handle thread...

mysql_close(...)
mysql_server_end();
...
}
exit(0);

or should it be something like:

mysql_server_init(...);
while loop childid
{
fork();
...
mysql_real_connect(...)

...handle thread...

mysql_close(...)
...
}
mysql_server_end();
exit(0);

Thnx,

Lefteris

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL 5.0.13-rc has been released

[Sujay Koduri]

"release candidate" is just an alias for 'gamma version' of the s/w released
from MySQL AB. What he said is that MySQL first gamma version has been
released in the 5.0 series. Till now it is in beta phase.

sujay

-Original Message-
From: Jim McAtee [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 27, 2005 2:11 AM
To: Lenz Grimmer
Cc: mysql@lists.mysql.com
Subject: Re: MySQL 5.0.13-rc has been released

Is this a "release candidate" as the version in your message subject would
imply, or is it an actual "release", as your message states?  Or does MySQL
AB even bother to differentiate the two?


- Original Message -
From: "Lenz Grimmer" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: ; <[EMAIL PROTECTED]>
Sent: Monday, September 26, 2005 1:51 PM
Subject: MySQL 5.0.13-rc has been released


> MySQL 5.0.13, a new version of the popular Open Source Database 
> Management
> System, has been released. The Community Edition is now available in 
> source
> and binary form for a number of platforms from our download pages at
> http://dev.mysql.com/downloads/ and mirror sites.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Avg row length is varying a lot from oracle to MySQL

[Sujay Koduri]

I am using the show table status command to find the average length of row
in a table. And it reported something about 686 bytes. But as I populated
more data, this number has substantially decreased to 484 bytes. But one
more thing I also learnt is the average row length returned by oracle is
just the avg length of each row in the data files ignoring the space
occupied by the indexes. But I think MySQL is giving this values taking the
space used by indexes into consideration.
So they are almost coming the same. :)

sujay

-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 27, 2005 3:59 AM
To: Sujay Koduri
Cc: mysql@lists.mysql.com
Subject: Re: Avg row length is varying a lot from oracle to MySQL

Sujay Koduri wrote:
> we are converting our oracle DB to MySQL DB. One problem i see is that 
> the abg row length in MySQL is much higher compared to that of Oracle. 
> In oracle it is around 180 bytes and in MySQL it is around 686 bytes. 
> So as a result, MySQL is taking more space to store the same number of 
> records. Can someone please explain me if this is the intended 
> behaviour or i am missing out something. I am also including the o/p 
> of desc table_name of the same table on both the databases.

How are you measuring the size of a row in mysql?  What makes you think it
is averaging 686 bytes?

Sujay Koduri also wrote:
> Each row in the table takes around 600 bytes, taking every thing into 
> consideration and assuming every field is used to its maximum bytes. 
> But the major portion of this 600 bytes are composed of varchar's (100 
> + 150 + 50 +
> 16 + 50 + 20 + 9..)
> Out of these 400 bytes we generally use only 40 to 50 bytes. Most of 
> them are reserved for future uses. So strictly speaking even including 
> the space taken by the indexes, the avg length should not come more than
250 bytes.

Umm, using about 50 bytes out of 400 in variable length columns saves about
350 bytes.  600 - 350 = 250, so you should expect about 250 bytes used
_before_ indexes.  On the other hand, you say it's only 180 in Oracle, so
perhaps the estimate is off.

OK, looking at your column definitions, I see 118 bytes worth of fixed-width
columns, plus 11 to 436 bytes worth of varchar columns, yielding 129 bytes
per row with empty varchars, 554 bytes per row with full varchars.  With 40
to 50 chars used in the varchars, that would be around 180 bytes per row,
just as in Oracle (not including any indexes).  Of course, this is assuming
you are using 1-byte chars.

I can't imagine how that could take 686 bytes per row in mysql.  It could
just be a failure of my imagination, but you haven't yet shown us how you
arrived at that number.

Michael

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Advice Required

[Sujay Koduri]

What are specifications of your DB. How much of data you have. How mant
transactions you will be getting daily. 
Without these details it will be difficult to answer ur question.

But for a centralised database server, I guess 1G RAM and 2CPU will be a
good configuration to start with.

sujay

-Original Message-
From: Vinayak Mahadevan [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 27, 2005 9:25 AM
To: mysql@lists.mysql.com
Subject: Advice Required

I am creating an application in Visual Basic 6.0 which will require a
centralised database server. All this while I had been planning to use
MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms
but not for an enterprise level rdbms. So I am planning to use MySQL as the
backend for the application. What should be the minimum system requirement
to run the database on.

Regards
Vinayak

--
Vinayak Mahadevan
Systems Engineer
Magtorq Pvt. Ltd.
58-C, Sipcot Industrial Complex
Hosur - 635-126
Mobile: 98 94 90 61 61



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can we run linux commands from inside the msql client

[Sujay Koduri]

hi..
 
I searched the documentation and googled for sometime, but didnt find
anything related to this
can we execute shell commands inside mysql client (like using ! in oracle),
and if possible please tell me how.
 
sujay

RE: Can we run linux commands from inside the msql client

[Sujay Koduri]

Thanks a ton :)

sujay 

-Original Message-
From: David Ziggy Lubowa [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 28, 2005 4:37 PM
To: mysql@lists.mysql.com
Cc: Sujay Koduri
Subject: Re: Can we run linux commands from inside the msql client


Yes you can ...  e.g  mysql>\!  vi filename   ( you would be able to edit
the 
file while within mysql.


cheers


On Wednesday 28 September 2005 13:41, Sujay Koduri wrote:
> hi..
>
> I searched the documentation and googled for sometime, but didnt find 
> anything related to this can we execute shell commands inside mysql 
> client (like using ! in oracle), and if possible please tell me how.
>
> sujay

-- 
 
 --
Fanaticism consists of redoubling your effort when you have forgotten your
aim. 
  -- George Santayana  

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Foreign key support in MyISAM

[Sujay Koduri]

does anyone have an idea when mysql guys are going to include foreign key
support in myisam tables.
i have read that it is slated for a future release, but it is not mentioned
when it is actually scheduled for.
 
reference -- http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html
 
 
sujay

RE: MyISAM to InnoDB

[Sujay Koduri]

If you think your storage requiremnets will increase in future, try to
estimate how much you will be needing in the future in the worst case and
try allocating that much of disk space now itself (Any way you have good
amount of disk space left). 
Try creating a different partition for storing the log files. This will
increase the performance

Even if you don't do this and run out of space, you just have to add more
add data files and a restart the server.

And for 2G RAM, its better to limit the innodb_bufferpool_size to 1G. You
can also look at the query_cache_size parameter and try tuning that by
running some load tests.

Apart from that everything is looking fine for me

sujay 

-Original Message-
From: Jeff [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 28, 2005 8:22 PM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: RE: MyISAM to InnoDB

>  Cut orignal thread because it was too long

Ok so I'm about to convert two tables in my database from MyISAM to InnoDB.
They are currently:

14K Sep 15 13:15 Table1.frm
2.1G Sep 28 14:15 Table1.MYD
198M Sep 28 14:15 Table1.MYI

11K Sep 20 08:45 Table2.frm
424K Sep 28 14:15 Table2.MYD
110K Sep 28 14:15 Table2.MYI

The system is only used as a database server, it's a dual processor system
with 2gig of ram.

As you can see, Table1's MyISAM data file is quite large at 2.1 gig.
Taking this into account what size InnoDB data files should I configure in
my my.cnf file?

I was thinking of this:

My.cnf



[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
server-id=70
port = 3306
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency thread_concurrency = 4
set-variable= max_connections=500

### InnoDB setup ###

# use default data directory for database innodb_data_home_dir =
/DATA/dbdata/ innodb_data_file_path =
/ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G
innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs

innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 20M
innodb_log_files_in_group = 3
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 1.5G
innodb_additional_mem_pool_size = 2M
innodb_file_io_threads = 4



But what happens if the ibdata2 fills up to the max of 2G?
I've got 50 gig available on the partition where the db data is stored.

Is there anything else here that looks incorrect?

Thanks,

Jeff






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MyISAM to InnoDB

[Sujay Koduri]

One more thing, noneed to give so much space for each logfile. The combined
size of all log files should be around 25-50% of innodb_buffer_pool size. So
you can reduce the size of each log file to 100M.

You can specify that for storing log files.

sujay 

-Original Message-
From: Jeff [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, September 28, 2005 9:04 PM
To: 'Sujay Koduri'; mysql@lists.mysql.com
Subject: RE: MyISAM to InnoDB

> -Original Message-
> From: Sujay Koduri [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 28, 2005 11:04
> To: Jeff; mysql@lists.mysql.com
> Cc: [EMAIL PROTECTED]
> Subject: RE: MyISAM to InnoDB
> 
> 
> 
> If you think your storage requiremnets will increase in future, try to 
> estimate how much you will be needing in the future in the worst case 
> and try allocating that much of disk space now itself (Any way you 
> have good amount of disk space left).
> Try creating a different partition for storing the log files. 
> This will increase the performance
> 

Well currently MySQL is set up in the default dir of /var/lib/mysql and
soft links to the database data residing on another partition
/DATA/.

Should I maybe specify:

innodb_log_group_home_dir = /var/lib/mysql/iblogs/

I have about 9 gig available on /var so 1.5 gig of logs shouldn't be too
bad.

> Even if you don't do this and run out of space, you just have 
> to add more add data files and a restart the server.
> 
> And for 2G RAM, its better to limit the 
> innodb_bufferpool_size to 1G. You can also look at the 
> query_cache_size parameter and try tuning that by running 
> some load tests.
> 
> Apart from that everything is looking fine for me
> 
> sujay 
> 
> -Original Message-
> From: Jeff [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, September 28, 2005 8:22 PM
> To: mysql@lists.mysql.com
> Cc: [EMAIL PROTECTED]
> Subject: RE: MyISAM to InnoDB
> 
> >  Cut orignal thread because it was too long
> 
> Ok so I'm about to convert two tables in my database from 
> MyISAM to InnoDB. They are currently:
> 
> 14K Sep 15 13:15 Table1.frm
> 2.1G Sep 28 14:15 Table1.MYD
> 198M Sep 28 14:15 Table1.MYI
> 
> 11K Sep 20 08:45 Table2.frm
> 424K Sep 28 14:15 Table2.MYD
> 110K Sep 28 14:15 Table2.MYI
> 
> The system is only used as a database server, it's a dual 
> processor system with 2gig of ram.
> 
> As you can see, Table1's MyISAM data file is quite large at 
> 2.1 gig. Taking this into account what size InnoDB data files 
> should I configure in my my.cnf file?
> 
> I was thinking of this:
> 
> My.cnf
> 
> 
> 
> [mysqld]
> 
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> log-bin
> server-id=70
> port = 3306
> skip-locking
> key_buffer = 384M
> max_allowed_packet = 1M
> table_cache = 512
> sort_buffer_size = 2M
> read_buffer_size = 2M
> myisam_sort_buffer_size = 64M
> thread_cache = 8
> query_cache_size = 32M
> # Try number of CPU's*2 for thread_concurrency 
> thread_concurrency = 4 set-variable= max_connections=500
> 
> ### InnoDB setup ###
> 
> # use default data directory for database 
> innodb_data_home_dir = /DATA/dbdata/ 
> innodb_data_file_path =
/ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G
> innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs
> 
> innodb_buffer_pool_size = 1G
> innodb_additional_mem_pool_size = 20M
> innodb_log_files_in_group = 3
> innodb_log_file_size = 500M
> innodb_log_buffer_size = 8M
> innodb_buffer_pool_size = 1.5G
> innodb_additional_mem_pool_size = 2M
> innodb_file_io_threads = 4
> 
> 
> 
> But what happens if the ibdata2 fills up to the max of 2G?
> I've got 50 gig available on the partition where the db data 
> is stored.
> 
> Is there anything else here that looks incorrect?
> 
> Thanks,
> 
> Jeff
> 
> 
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Access, ODBC, Oracle, MySQL

[Sujay Koduri]

I don't know how far this is going to help you. But this is what we are
following to migrate from Oracle to MySQL.

Have a flag keeporacle as long as you want oracle to be in place. 

Open connection to mysql
If (keeporacle)
{
open connection to oracle also.
}

 if (keepOracle) 
 {
found = look in Mysql db
if (not found)
{
  look in oracle db //Anyway this architecture must be in place
for you.
  insert in MySQL db 
}
 }
 else
   look in mysql and serve back, if it exists.

Close connection to mysql
If (keeporacle)
{
   close connection to oracle.
}

And once you feel confident that all the data is moved from oracle to MySQL,
take oracle out.

sujay

-Original Message-
From: Robert Citek [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 29, 2005 12:58 AM
To: MySQL
Subject: Access, ODBC, Oracle, MySQL


Is there a way to query data where one table is in an Oracle database and
another is in a MySQL database?

We have two projects.  One project is entirely in Oracle 9i on an MS Windows
2000 server and the other is in MySQL 4.1 on Linux.  It isn't worth our time
at this point to migrate entirely to one or the other.  So I was wondering
if there is a way to query data from two tables, where one table was in one
database and the other table in another?

Currently, I have hacked together a perl script which does what I need.  But
this is not a long-term solution.  So we are looking for other strategies.
One strategy would be to be to use ODBC drivers  
and MS Access to link the two tables within an MS Access database.   
Another strategy would be to link the Oracle tables into MySQL, but I don't
know if that can be done.  Any other strategies?  Pointers to documents or
search terms gladly accepted as my current searches returned a lot of hits
but nothing useful, unless I missed it.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software.  Distribute FLOSS for Windows, Linux,
*BSD, and MacOS X with BitTorrent


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Access, ODBC, Oracle, MySQL

[Sujay Koduri]

Once you get some "Here's how you do it." about option 1, please post it in
the lists also. Because we are also looking to do a similar one, but didn't
find out any good info. 

sujay 

-Original Message-
From: Robert Citek [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 29, 2005 1:32 AM
To: MySQL
Subject: Re: Access, ODBC, Oracle, MySQL


On Sep 28, 2005, at 2:37 PM, Sujay Koduri wrote:
> I don't know how far this is going to help you. But this is what we 
> are following to migrate from Oracle to MySQL.

If we were migrating, that's probably the strategy that we would use.  But
we are not, at least not yet.  For now we just want to be able to join two
tables which reside in different databases (Oracle and MySQL).  What I'm
hoping is one of the following:

1) I can link an Oracle table into MySQL using ODBC
2) I can link a MySQL table into Oracle using ODBC

I have found docs that hint that (1) is possible, but haven't found anything
that says "Here's how you do it."  As for the second option, I discovered
something called "Oracle Heterogeneous Services" which may be the ticket,
but again, I haven't found anything that says "Here's how you do it."

Personally, I would prefer option (1) over option (2) because that would
then provide a clear path and an incentive for migration to MySQL.

Regards,
- Robert
http://www.cwelug.org/downloads
Help others get OpenSource software.  Distribute FLOSS for Windows, Linux,
*BSD, and MacOS X with BitTorrent


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Global Replace

[Sujay Koduri]

Yes, I don't think you have to do lot of changes to your application to
achieve this. As scott mentioned, always try to keep minimum(whatever is
really useful) data in the DB, either for more performance or for using less
disk space.

sujay

-Original Message-
From: Scott Noyes [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 6:43 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Global Replace

> We have a database on MySql 4 and it contains many tables. In each 
> field in the table in the past were there was no data to display we 
> simply left the field blank, we now want to replace a null entry with  
> No Data

I advise you to reconsider.  You are reducing the flexibility and
usefullness of your data by doing this.  Functions designed to take
advantage of NULL values will be lost to your applications.  Your database
will consume more disk space, and probably take longer to search.

Leave the database fields as NULL, and design the output of your
applications to display "No Data" where appropriate.

--
Scott Noyes
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL Query Browser

[Sujay Koduri]

I am not saying MysQL Query Browser is anyway bad or inferior, but as for my
experience sqlyog is very good. There is a free version which you can use
for executing SQL queries, ofcourse you will be stripped of some advanced
features.

You can run multiple queries at once  using shift+F5. I suggest you to try
this out once.

sujay

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 7:14 PM
To: Rob Agar
Cc: 'Mysql '
Subject: RE: MySQL Query Browser

"Rob Agar" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:27:37 PM:

> hi Scott
> 
> > How do I run more than 1 queries in MySQL Query Browser?
> 
> The only way I've found is to put the queries in a .sql file and load 
> it via File > Open Script. It doesn't accept multiple typed in 
> queries, even if they are separated by semicolons.
> 
> hth
> Rob
> 
> 
> 
He can just start a new script tab, can't he? That's how I do it. I don't
know why they have two different types of tabs (one for multi-statements and
one for single statements) but they do.

File -> New Script Tab


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How can I access results in a singel dimentional array?

[Sujay Koduri]

Mysql_store_result stores all the results in a single buffer. But at one
time you can only access one row. It is like a array of structures, where
each structure has one row info. And the call to mysql_fetch advances this
rowcount by one. And coming to storing all the results in one char*, it is a
simple loop you can use.

While(fetch from mysql) {
Catch all columns of a row in output bind vars
Append all these to the char * using some delimiter. (actually you don't
need to know the number of rows before hand, you can do a malloc here
itself)
}
Return (char*)

sujay

-Original Message-
From: Lefteris Tsintjelis [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 30, 2005 7:13 PM
To: mysql@lists.mysql.com
Subject: How can I access results in a singel dimentional array?

Hi,

Is there an easy way to access directly the results after a SELECT and a
mysql_store_result() into a single dimentional char *array[], given that I
already know the (fields * rows) value? Are the total results stored in a
single buffer or is it done on a per row basis? How does it work?

Thnx,

Lefteris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: timing queries?

[Sujay Koduri]

If you use source absolute-pathname from mysql command prompt, then it will
execute each statement(or line) in the file individually and print the
results exactly in the format you are looking for.

sujay

-Original Message-
From: Jacek Becla [mailto:[EMAIL PROTECTED] 
Sent: Saturday, October 01, 2005 1:20 AM
To: mysql@lists.mysql.com
Subject: timing queries?

Hi,

Does anyone know how to execute SQL statements from a text file such that
the summaries "X rows in set (X.YZ sec)" are printed for each query?

Neither of these do it:
   mysql < batch-file
   mysql -e 'source batch-file'


'source batch-file' run inside mysql command-line does it, but then I need
to manually copy and paste the results.

Thanks,
Jacek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Find username password on tables

[Sujay Koduri]

Username you can get it from the user table in Mysql. But I don't think
atleast after MySQL 4.1 there is a way to retrieve paswords in MySQL as it
uses its own encryption algo to encrypt passwords. You have to reset(make
that passwd field to null in the mysql.user table) and add a new
password(use grant) to use that.

sujay

-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 05, 2005 6:39 PM
To: mysql@lists.mysql.com
Subject: Find username password on tables

Hello,

A while back, I created a database, and performed the following:

GRANT ALL ON util_db.* to XXX identified by "XXX";

Problem is, a year later, I need to find the username and password, so I can
write to these tables.


Can this be accomplished, I am the root user.


Thanks,
Scott

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: innodb locking

[Sujay Koduri]

Is this happening every time you try this, or it happened first time?
Yes you right that INNODB uses row level locks, and the only reason for that
error should be that someone else is trying to update the same row. As we
can see from the o/p of the show procee list, someone else is also trying to
update. (can't find from thet if it is the same row or not)
Also can you please tell what isolation level are you using now.

sujay

-Original Message-
From: Tony Leake [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 05, 2005 7:27 PM
To: mysql@lists.mysql.com
Subject: innodb locking

Hi, 

I have a query:

UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid =
10798

intOrderUid is the primary key

There are 25 columns in the table and a further 8 of these have indexes on
them. The table is innodb

I have just tried to run the above query 3 times and i got the follwing
error

Invalid Query Lock wait timeout exceeded; try restarting transaction

AFAIK innodb locks on row level, so does that mean that something else is
locking that row and won't let me update. If so how can i find out what? 

Here is a copy of what mytop says at the time the query is being run

Thanks for any help


MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19]
 Queries: 6.0M   qps:   17 Slow:   120.0 Se/In/Up/De(%):
68/19/01/00
 qps now:8 Slow qps: 0.0  Threads:7 (   3/   0)
77/00/00/00
 Cache Hits: 3.0M  Hits/s:  8.5 Hits now:   4.2  Ratio: 74.2% Ratio now:
70.0%
 Key Efficiency: 97.7%  Bps in/out:  6.8k/ 9.8k   Now in/out:  3.5k/
5.0k

  Id  User Host/IP DB  TimeCmd Query or
State
  --   --- --  ---
--
  554082  root   localhost   test 0  Query show full
processlist
  603034  root   localhost0  Sleep
  603086  root   localhost0  Sleep
  602989  root   localhost1  Sleep
  603301  root   localhost1  Sleep
  604008  root   localhost   11  Query UPDATE
dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde
  389249  repl   clank   133504 Binlog Has sent
all binlog to slave; waiting for binlog to be update



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: innodb locking

[Sujay Koduri]

Since you are using repeatable read, this should not be a problem. And
regarding autocommit=0, I don't think it is playing any role here. From the
show processlist, if at all, at the max there were only two processes acting
on the row. But still this should not happen as your query should be kept in
the wait queue and served back once the lock gets free. 

Also check that no I/O contention is going on(This may be the cause to wait
for longer times for getting the lock), when you get into this problem. Also
have a look at your INNODB parameters and find out if any tuning is
required.

sujay

-Original Message-
From: Tony Leake [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 05, 2005 7:55 PM
To: Sujay Koduri
Cc: mysql@lists.mysql.com
Subject: RE: innodb locking

On Wed, 2005-10-05 at 07:12 -0700, Sujay Koduri wrote:
> Is this happening every time you try this, or it happened first time?

It's not every time but this is not the first. Mostly the query is ok, but I
would like to find out why it's happening.

> Also can you please tell what isolation level are you using now.

I am using the default which i beleive is REPEATABLE READ. The particular
query that is failing is done using autocommit as it a standalone query.

Thanks

tony


> 
> sujay
> 
> -Original Message-
> From: Tony Leake [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 05, 2005 7:27 PM
> To: mysql@lists.mysql.com
> Subject: innodb locking
> 
> Hi,
> 
> I have a query:
> 
> UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid =
> 10798
> 
> intOrderUid is the primary key
> 
> There are 25 columns in the table and a further 8 of these have 
> indexes on them. The table is innodb
> 
> I have just tried to run the above query 3 times and i got the 
> follwing error
> 
> Invalid Query Lock wait timeout exceeded; try restarting transaction
> 
> AFAIK innodb locks on row level, so does that mean that something else 
> is locking that row and won't let me update. If so how can i find out
what?
> 
> Here is a copy of what mytop says at the time the query is being run
> 
> Thanks for any help
> 
> 
> MySQL on localhost (4.1.8a-Debian_1-log) up 4+08:28:06 [15:07:19]
>  Queries: 6.0M   qps:   17 Slow:   120.0 Se/In/Up/De(%):
> 68/19/01/00
>  qps now:8 Slow qps: 0.0  Threads:7 (   3/   0)
> 77/00/00/00
>  Cache Hits: 3.0M  Hits/s:  8.5 Hits now:   4.2  Ratio: 74.2% Ratio now:
> 70.0%
>  Key Efficiency: 97.7%  Bps in/out:  6.8k/ 9.8k   Now in/out:  3.5k/
> 5.0k
> 
>   Id  User Host/IP DB  TimeCmd Query or
> State
>   --   --- --  ---
> --
>   554082  root   localhost   test 0  Query show full
> processlist
>   603034  root   localhost0  Sleep
>   603086  root   localhost0  Sleep
>   602989  root   localhost1  Sleep
>   603301  root   localhost1  Sleep
>   604008  root   localhost   11  Query UPDATE
> dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde
>   389249  repl   clank   133504 Binlog Has sent
> all binlog to slave; waiting for binlog to be update
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: A question about stored procedures

[Sujay Koduri]

Yes you can do this using prepared statements inside stored proc.
Guess this helps.

http://forums.mysql.com/read.php?98,22770,31459

sujay 

-Original Message-
From: Leonardo Javier Belén [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 06, 2005 9:47 PM
To: Ryan Stille; mysql@lists.mysql.com
Subject: A question about stored procedures

Hi all,
I am wondering if I can construct a query string inside a stored
procedure and then execute it. I mean, what I want to do is given a set of
IN parameters, be able to construct a huge where statement out of them an
then complete the query string to execute. Is that possible? because if that
is possible I could be able to abstract all the data logic from the program
itself.

I've been googling and reading books and references without success so
far, but I think the functionality should be there, what I dont know is how
to use it.

Thanks in advance.

sql - query - mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: creating socket

[Sujay Koduri]

This error means that the mysql server has not started in the first place.
So try to have a look in the logs (generally this should be in
/var/lib/mysql/) and find out the reason why the server hasn't started.

sujay 

-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 10, 2005 2:44 PM
To: mysql@lists.mysql.com
Subject: creating socket

Hi,

I have installed mysql and loaded mysqld, but I cannot connect to it with
mysql because it gives an error telling that "it cannot connect using socket
/var/lib/mysql/mysql.sock".

I have checked that dir, but I don't have that socket file there.

Please tell me how to create it. I don't know Linux very well, so please
excuse my ignorance in this field.

Thank you.

Teddy


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: creating socket

[Sujay Koduri]

Add the following entry in the my.cnf to create the socket file in the
desired location.

socket=/var/lib/mysql/mysql.sock  (Any desired path can be given here)

sujay

-Original Message-
From: Octavian Rasnita [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 10, 2005 3:50 PM
To: Sujay Koduri; mysql@lists.mysql.com
Subject: Re: creating socket

From: "Sujay Koduri" <[EMAIL PROTECTED]>
> This error means that the mysql server has not started in the first place.
> So try to have a look in the logs (generally this should be in
> /var/lib/mysql/) and find out the reason why the server hasn't started.
>
> sujay
>

I have discovered that mysql creates the socket in /tmp/mysql.sock instead
of /var/lib/mysql/mysql.sock.

I have created a link from /tmp/mysql.sock to /var/lib/mysql/mysql.sock and
now MySQL works fine.

However, I think I should change somewhere in the file my.ini the location
of the socket...

Thank you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Lost .frm files

[Sujay Koduri]

I have changed the OS on my database server. At that time, I forgot to take
the backup of the .frm files in /var/lib/mysql. I have taken all the backup
of all the data files.
I can recreate all the tables. How can I map these tables to existing data
files. Is this possible. Please help me out as the data is huge. (alomost
220G )
 
Thank you
sujay

Killing a thread -- Urgent

[Sujay Koduri]

hi all..
 
I started a select count(1) command on a table in mysql, assuming it will
end soon within the off peak time..
But now the peak load time is about to start, so i tried to kill that
query..
 
logged into mysql as root.
show processlist for getting the thread id.
kill 85300 (thrad_id)
 
After doing this the state of the query has changed to killed, but still it
is showing like this
 
85300 | root  | localhost   | db_name | Killed  |  23055 |
Sending data | select count(1) from 
 
I have issued the kill command almost 1hour back..but still it is showing up
this query..And the iowait on the DB server is touching 60%..generally in
off peak times it should be 35%. (i seriously suspect this query for the
high iowaits)
 
Any help is greatly appreciated. 
 
Thank you
sujay

RE: Killing a thread -- Urgent

[Sujay Koduri]

The statement remained in the killed state for 2 hrs and then got out of the
processlist. Great sigh of relief then :).
Sorry for the late reply. But one thing I still couldn't understand is why
it took so long to move out of the processlist from the killed state.

sujay

-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Sent: Sunday, October 16, 2005 12:53 AM
To: mysql@lists.mysql.com
Subject: Re: Killing a thread -- Urgent

Hello.

Do you have other queries which are running at this moment on the server?
The show-processlist.html says that thread could be in Killed state if it is
locked by another thread.
What version of MySQL do you use? What storage engine does your table have? 



Sujay Koduri <[EMAIL PROTECTED]> wrot
>hi all..
> 
>I started a select count(1) command on a table in mysql, assuming it 
>will end soon within the off peak time..
>But now the peak load time is about to start, so i tried to kill that 
>query..
> 
>logged into mysql as root.
>show processlist for getting the thread id.
>kill 85300 (thrad_id)
> 
>After doing this the state of the query has changed to killed, but 
>still it is showing like this
> 
>85300 | root  | localhost   | db_name | Killed  |  23055 |
>Sending data | select count(1) from
> 
>I have issued the kill command almost 1hour back..but still it is 
>showing up this query..And the iowait on the DB server is touching 
>60%..generally in off peak times it should be 35%. (i seriously suspect 
>this query for the high iowaits)
> 
>Any help is greatly appreciated. 
> 
>Thank you
>sujay


--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Datatype conversion

[Sujay Koduri]

hi all,
 
i have a strange problem here. I have around 600 tables in Oracle DB(On 8i,
so i am not able to use mysql migration tool kit!!). Now i want move all of
these to mysql. I am initially trying to convert the schema from oracle to
mysql. My problem is, in the existing oracle DB, for storing both integers
as well as decimal values i am using the same dataype 'number'. But during
migration i want to distinguish if that field 'number' in oracle is actually
holding an integer or decimals. Is this possible to do or i have to manually
do it only. 
 
Thank you sujay

RE: Datatype conversion

[Sujay Koduri]

yes..exactly thats what i am planning to do now. 
 
Thank you
sujay

  _  

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 17, 2005 7:35 PM
To: Sujay Koduri
Cc: mysql@lists.mysql.com
Subject: Re: Datatype conversion




Sujay Koduri <[EMAIL PROTECTED]> wrote on 10/17/2005 06:25:58 AM:

> hi all,
>  
> i have a strange problem here. I have around 600 tables in Oracle DB(On
8i,
> so i am not able to use mysql migration tool kit!!). Now i want move all
of
> these to mysql. I am initially trying to convert the schema from oracle to
> mysql. My problem is, in the existing oracle DB, for storing both integers
> as well as decimal values i am using the same dataype 'number'. But during
> migration i want to distinguish if that field 'number' in oracle is
actually
> holding an integer or decimals. Is this possible to do or i have to
manually
> do it only. 
>  
> Thank you sujay

Unless you can somehow extract some additional information about your
fields, you will not be able to automate such a conversion. How were you
planning to make the decision by hand? You could probably write something to
dump your Oracle schema as a MySQL-ready script complete with the numeric
data type conversions (based on how you wanted to decide which columns were
integers and which ones were not). Then it becomes a simple matter of
"playing" that script through the CLI (use the "source" command) to actually
create your destination tables. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Multiple MySQL services running on the same machine

[Sujay Koduri]

I guess you should be able to run multiple instances if you start each
instance on a different port.

sujay 

-Original Message-
From: Frank Fischer [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, November 09, 2005 2:17 PM
To: mysql@lists.mysql.com
Subject: Multiple MySQL services running on the same machine

Hi all
 
i just read some postings related whether it is possible to run multiple
services of mysql on windows. One posting states, that is's not possible,
another states that is can be done using mysqld-nt for the first instance
and mysql-nt-max for the second instance and a third posting states that
this can be done for mysql >= 4.0.2.
 
Is any of this right? And if yes, how do i have to set up the services? I
guess, i need a my.cnf file for each of the instances, of course using
different datapaths and ports. But how can i install the mysqld-nt service
multiple times using different service names and refering to different
my.cnf files?
 
Thanks a lot for your help!
- Frank

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: where is the mistake in this SQL statement?

[Sujay Koduri]

You havent specified the datatype for the column 'id'.

sujay 

-Original Message-
From: Peter Matulis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 15, 2005 10:57 AM
To: mysql@lists.mysql.com
Subject: where is the mistake in this SQL statement?

{ this is a resend, forgot subject in original posting }

Hello, I am using MySQL 4.0.24 on OpenBSD 3.8.

I am having difficulty creating a SQL schema.  My attempts contain lines
like:

CREATE TABLE users (
  id PRIMARY KEY,
  priority integer NOT NULL DEFAULT '7',
  policy_id  integer unsigned NOT NULL DEFAULT '1',
  email varchar(255) NOT NULL,
  fullname varchar(255) DEFAULT NULL,
  local char(1),
  KEY email (email)
);

Resulting in this error:

ERROR 1064 at line 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 'PRIMARY KEY,  
  priority   integer  NOT NULL DEFAULT '7', 

I think there is an incompatibility between my server version and this
statement but I can't figure out what it is.  What should be changed near
PRIMARY KEY?  This is for Spamassassin and amavisd-new.






__
Find your next car at http://autos.yahoo.ca

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Pk vs index

[Sujay Koduri]

hi all..
 
i have created a table (INNODB) with a primary key column. 
And all the queries i am running are of the following type only.
 
select col1,col2... from table where primary_key_col = value
 
My question here is will mysql automatically create an index on this column
which is having the primary key constraint. (This is what i am asusming all
the time, as oracle do this)
But when i do a show table status  on this table, it is giving the index
length as 0. (does this mean there is no index on this column ?)
 
And if i create an index now on this primary key column, is it going to
increase my performance anyway.
 
I am using mysql 4.1.14 on RHEL 3.0
 
Thank you
sujay

RE: How do I?

[Sujay Koduri]

Yes, you can change it at runtime by doing the following

SET GLOBAL max_allowed_packet = 1024 (or whatever size);

Not only this varaible, whatever variables are listed here
http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html ,you
can change them at runtime without restarting the server.

Have a look at this also
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

sujay
-Original Message-
From: Mikhail Berman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 15, 2005 8:58 PM
To: mysql@lists.mysql.com
Subject: How do I?

Hello everyone,
 
I have changed a value of  "max_allowed_packet" in  "my.cnf". 
 
Do I need to restart MySQL itself for the change to take place or is there a
way to refresh this variable (parameter) without restart of MySQL?
 
I have looked through documentation and www.mysql.com and could not find the
answer.
 
Below is info on my environment:
 
mysql> status
--
mysql  Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc)
 
Connection id:  7230
Current database:
Current user:   xx
SSL:Not in use
Current pager:  stdout
Using outfile:  ''
Using delimiter:;
Server version: 4.1.9-standard
Protocol version:   10
Connection: Localhost via UNIX socket
Server characterset:latin1
Db characterset:latin1
Client characterset:latin1
Conn.  characterset:latin1
UNIX socket:/tmp/mysql.sock
Uptime: 12 days 11 hours 48 min 22 sec
 
Threads: 1  Questions: 47908924  Slow queries: 983  Opens: 4964  Flush
tables: 1  Open tables: 275  Queries per second avg: 44.389

 
Thank you in advance,
 
Mikhail Berman

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Newbie Query: Error starting MySQL..changed Data Directory

[Sujay Koduri]

>>Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT
 
This doesn't nesessarily mean that mysql hasn't started. This script waits
only for certain time to check if mysql has started or not. If it is not
started in that time, it simply says 'mysql start failed'. But mysql may
take some more time to come up. So the best practice is to have a look at
the logs (as you rightly did) to find out what exactly is going on.
And your logs anyway are saying "/usr/libexec/mysqld: ready for
connections.". This means that mysql has started without any problems and
waiting for connections. 

>>ps -aux shows that mysql is running

And for the same reason I mentioned above, you are seeing this.

Hope this helps.

sujay

-Original Message-
From: Sanjay Arora [mailto:[EMAIL PROTECTED] 
Sent: Monday, November 14, 2005 4:46 PM
To: MySql Mailing List
Subject: Newbie Query: Error starting MySQL..changed Data Directory

Hi all

First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2
with MySQL 4.1.12, rpm install.

Changed the data directory.

My /etc/my.conf
[mysqld]
datadir=/home.dbdata/mysql
socket=/home.dbdata/mysql/mysql.sock


# Default to using old password format for compatibility with mysql 3.x #
clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Ran these commands for permissions:
chown -R mysql:mysql /home.dbdata/mysql
chmod -R go-rwx  /home.dbdata/mysql

Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT

ps -aux shows that mysql is running

0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pidmysql
6184  0.2  3.4 125808 17636 pts/0  Sl   15:47   0:01 /usr/libexec/mysqld
--defaults-file=/etc/my.cnf --basedir=/usrroot  6336  0.0  0.1  2920
744 pts/0R+   15:57   0:00 ps -aux

Log shows
051114 14:51:07  mysqld started
051114 14:51:08  InnoDB: Started; log sequence number 0 43634
/usr/libexec/mysqld: ready for connections.
Version: '4.1.12'  socket: '/home.dbdata/mysql/mysql.sock'  port: 3306
Source distribution
051114 15:47:06 [Note] /usr/libexec/mysqld: Normal shutdown

051114 15:47:06  InnoDB: Starting shutdown...
051114 15:47:09  InnoDB: Shutdown completed; log sequence number 0 43634
051114 15:47:09 [Note] /usr/libexec/mysqld: Shutdown complete

051114 15:47:09  mysqld ended

051114 15:47:33  mysqld started
051114 15:47:33  InnoDB: Started; log sequence number 0 43634
/usr/libexec/mysqld: ready for connections.
Version: '4.1.12'  socket: '/home.dbdata/mysql/mysql.sock'  port: 3306
Source distribution


Please advise what I have done wrong. I suspect permissions &/or incorrect
data directory migration. Please help.

With regards.
Sanjay.






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL server Crashed while running a stored proc

[Sujay Koduri]

hi all,
I am executing a stored proc that has a top level cursor to loop over a user
set, and then execute certain transactions for each user. After processing
about 5 users, it always generates: ERROR 2013 (HY000): Lost connection to
MySQL server during query. However, if I restart from the same user again,
it processes that user, and then encounters the same error after about 5-6
users. The other interesting thing to note is that this failure occurs at
different points inside the stored proc. In essence, there is no
predictability to this situation.

On further debugging, we found the issue to be bringing the server down, the
stack info for which we are listing below, along with the mysql.cnf
properties.

Any immediate help is greatly appreciated. 

stack info (This is after executing the command resolve_stack_dump -s
/tmp/mysqld.sym -n mysqld.stack )

0x8156750 handle_segfault + 416
0xf4f420 (?)
0x3e49a4 (?)
0x825587d _ZN11sp_rcontext13set_item_evalEP3THDjPP4Item16enum_field_types +
61
0x8252ba6 _ZN12sp_instr_set9exec_coreEP3THDPj + 54
0x8252940 _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr +
304
0x8252b5e _ZN12sp_instr_set7executeEP3THDPj + 46
0x82504f4 _ZN7sp_head7executeEP3THD + 644
0x825140f _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 1215
0x816ff85 _Z21mysql_execute_commandP3THD + 19413
0x8172880 _Z11mysql_parseP3THDPcj + 304
0x8169d03 _Z16dispatch_command19enum_server_commandP3THDPcj + 1139
0x816983d _Z10do_commandP3THD + 141
0x8168d48 handle_one_connection + 600
0xa1fb80 (?)
0x83d9ce (?) 

my.cnf configiration

--

#password   = your_password
port= 3306
socket  = /var/lib/mysql/mysql.sock 

[mysqld]
datadir=/data/mysqldata/
port= 3306
socket  = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 200M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 4M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
default-table-type=innodb

innodb_log_group_home_dir = /data/logs/
innodb_log_arch_dir = /data/logs/
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
log_error
log_slow_queries

[mysqldump]
quick
max_allowed_packet = 16M 

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M 

[mysqlhotcopy]
interactive-timeout

Thank you

sujay

RE: Mysql database capacity

[Sujay Koduri]

I am giving the system configuration which we are using to run MySQL.

2-CPU 4G RAM, SAN filesystem.
MySQL version : 4.14 (INNODB)
OS : RHEL - 3
Amount of Data : 200G
No of Rows : 278 million approximately (Every day 2.5-3 million rows gets
added)
Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec
Tables : 1 (only 1)

This mysql is handling comfortably.

So I think this info might help you to get a good idea about planning your
system resources.
I am not very much sure about the maximum rows/data it can support, but I am
sure it can easily handle a billion of rows in a single table.

Regards
sujay
 

-Original Message-
From: vishwas kharajge [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 09, 2006 3:05 PM
To: mysql@lists.mysql.com
Subject: Mysql database capacity

Hi all

I am working with startup company.
Have some quries about Mysql

1. What is the maximum database storage capacity of mysql 2. What is the
maximum row capacity?
3. How much time it will take to search the record if there are consider
more than 1 billion rows in a table 4. How many records can i store in a
single table.

Please help me.

Thanks in advance
Vishwas 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query about MySQL

[Sujay Koduri]

Hi Raman,
 
>> Is mysql is able to manage this huge amount of data. 
MySQL does manage this amount of data very easily.
 
>> Can we put this much of data in single table database   OR  keeping the
data in multiple tables is the good practice.
You can keep this much amount of data in one single MySQL table. But incase
of storing data in single large table like this, please go for a horizontal
partitioning of the data as it would reduce the load very much and also the
whole application will become highly scalable in future. You can actually
wait for this partitioning feature till 5.1 or you can go ahead and
implement your own version. (See information_schema database table in 5.1 to
get a feel on how to implement this)

>> What precautions I have to taking while managing this much of data in
MySQLetc etc 
Actually 17 million rows is not too huge a data that make you worry about
performance. But try to have a machine with 2CPU and 2G RAM with decent disk
speeds. That should be enough. Make changes to your my.cnf on the similar
lines given in medium.my.cnf (or the appropriate one that suits your H/W
requirements) that is provided by MySQL along with all the distributions.
 
>> can u plz tell me tht wht is the capacity of storage of MySQL DB.
To get a very good approximate of how much space the MySQL DB is going to
take, better generate dummy data and load it into the DB and use the sql
statement 'show table status' to see the exact space usage. Or create the
table in the MySQL DB with the same structure and load a sample of 100-200
rows and find the average row length (show table status). Multiply the
average row length with the number of rows (17milliion for your case). This
also gives you a good estimate. Don't forget to account for the space used
by indexes here. Use MySQL 5.0 to see better space usage from MySQL side. It
uses the compact row format -- compress rows before storing them.
 
Hoe this helps.
 
Thank you
Sujay
  _  

From: Raman Kheterpal [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 22, 2007 2:47 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Query about MySQL



Hi All 

I got your mail id from one of the forums. 

can u plz tell me tht wht is the capacity of storage of MySQL DB. acutally
we are in the process of making a project on LAMP technology, but the
databse is too large..say 1722 records 

this much of records will remain in one table only... Can you please tell me
. 

 - Is mysql is able to manage this huge amount of data. 
 - Can we put this much of data in single table database   OR  keeping the
data in multiple tables is the good practice. 
 - What precautions I have to taking while managing this much of data in
MySQLetc etc 

kindly see the matter n reply me in this regards 

Thanx in advance 

raman

Good book on MySQL 5.0

[Sujay Koduri]

 

Can anyone please suggest a good book for MySQL 5.0 .

Also can anyone suggest how far it will be reliable if I use MySQL 5.0 to
change my database from Oracle to Mysql.
As I have a lot of Stored Procs for migration. It will also be very helpful
if someone can give an approximate date when the stable version of MySql 5.0
is going to come out.

Thank you
sujay 

Sujay Koduri
Project Trainee
Andale INDIA pvt Ltd
Bangalore


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Number of Rows in DB.

[Sujay Koduri]

hi..

 

you can use...

 

select count(1) from table_name; (it will traverse only column 1)

 

as count(*) will go through all the columns, it will take much time compared
to this which generally will take less time.

 

by the way, can you send the shell script which you are using, it might be
of some use to me.

 

Thanks 

sujay

 

-Original Message-
From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 14, 2004 4:24 PM
To: [EMAIL PROTECTED]
Subject: Number of Rows in DB.

 

Does anyone know of a quick way to get the number of rows in a database 

from the command prompt.

 

I run a cron job that does a mysql dump nightly from a prod server piped 

to a standy  server.

It's a 2 line shell script including outputting the date at time.

 

After the dump, I would like to output the number of rows in the prod 

database and the newly updated standby database to make sure these 

match.  The output is e-mailed to me, so in the morning I could simply 

see if they match and be assured that the dump went well.

 

Thanks,

Mike

 

 

-- 

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql

To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Number of Rows in DB.

[Sujay Koduri]

hi all..

 

I think there is no way to count all the rows in a DB, using a single query.

we can say..

desc table_name; (gives all the tables in the DB)

and then get no of rows for each table..

 

 

Thanks

sujay

-Original Message-
From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 14, 2004 5:00 PM
To: [EMAIL PROTECTED]
Subject: Re: Number of Rows in DB.

 

 

I'm want ALL the rows from the WHOLE DATABASE not just one table.

I could do a show tables and then loop through each one and perform the 

select count and add it up.

But I'm hoping to find a better way.

 

As for the script, simply read the mysqldump man page. It is in  the 

examples near the bottom.

In this case I needed to tweak it a bit by adding some quotes (-Q) and 

extenderd inserts etc. Otherwise I was gettting errors.

But I ran the stock example on the man page for a couple of years now on 

2 other databases.

 

Mike

 

 

 

Sujay Koduri wrote:

 

> hi..

> 

>  

> 

> you can use...

> 

>  

> 

> select */count(1) /*from table_name; (it will traverse only column 1)

> 

>  

> 

> as count(*) will go through all the columns, it will take much time 

> compared to this which generally will take less time.

> 

>  

> 

> by the way, can you send the shell script which you are using, it 

> might be of some use to me.

> 

>  

> 

> Thanks

> 

> sujay

> 

>  

> 

> -Original Message-

> From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED]

> Sent: Thursday, October 14, 2004 4:24 PM

> To: [EMAIL PROTECTED]

> Subject: Number of Rows in DB.

> 

>  

> 

> Does anyone know of a quick way to get the number of rows in a database

> 

> from the command prompt.

> 

>  

> 

> I run a cron job that does a mysql dump nightly from a prod server piped

> 

> to a standy  server.

> 

> It's a 2 line shell script including outputting the date at time.

> 

>  

> 

> After the dump, I would like to output the number of rows in the prod

> 

> database and the newly updated standby database to make sure these

> 

> match.  The output is e-mailed to me, so in the morning I could simply

> 

> see if they match and be assured that the dump went well.

> 

>  

> 

> Thanks,

> 

> Mike

> 

>  

> 

>  

> 

> -- 

> 

> MySQL General Mailing List

> 

> For list archives: http://lists.mysql.com/mysql

> 

> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

> 

 

 

 

-- 

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql

To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

stored procedure not returning multiple rows...

[Sujay Koduri]

 
hi guys..
i am new to this mailing list...i am sujay from andale...

I have a problem in getting multiple records from my store procedure when I
tried calling it using C API...

I included the flag CLIENT_MULTI_STATEMENTS..but it is still giving the same
error..

1296 :SELECT in a stored procedure must have INTO..

i am also including my code here..

  1 #include 
  2 #include "/usr/include/mysql/mysql.h"
  3
  4 int main(int argc,char *argv[])
  5 {
  6 MYSQL mysql;
  7 MYSQL_RES *result;
  8 MYSQL_ROW row;
  9
 10 long int i;
 11 if(!mysql_init(&mysql))
 12 {
 13  fprintf(stderr,"Initialization failed");
 14 exit(1);
 15 }
 16
 17
if(!mysql_real_connect(&mysql,"localhost","root","","test",0,NULL,CLIENT_MUL
TI_RESULTS))
 18 {
 19 fprintf(stderr,"%d
:%s\n",mysql_errno(&mysql),mysql_error(&mysql));
 20 exit(2);
 21 }
 22
 23 if(mysql_query(&mysql,"call sujay1()")!=0)
 24 {
 25  fprintf(stderr,"%d :%s\n",mysql_errno(&mysql),mysql_error(&mysql));
 26 }
 27
 28 else
 29 {
 30 result = mysql_store_result(&mysql);
 31 i = mysql_num_rows(result);
 32 printf("No of rows are %d\n",i);
 33 //row = mysql_fetch_row(result);
 34 while(row = mysql_fetch_row(result))
 35 {
 36 printf("%s  %s\n",row[0],row[1]);
 37 }
 38 mysql_free_result(result);
 39 }
 40
 41 mysql_close(&mysql);
 42 return 0;
 43 }//end of main

The procedure sujay1() which i am using in  my program is

 create procedure sujay1()
 begin
select name,species from pet;
 end



Thanx
sujay koduri
(plez sent back the corrected one)
I am using..

Red hat-9.0
MySQL - 5.0

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

nawal lodha..u can easily get this i think...

[Sujay Koduri]

>  
>  
> hi guys..
> i am new to this mailing list...i am sujay from andale...
> 
> I have a problem in getting multiple records from my store procedure when
> I
> tried calling it using C API...
> 
> I included the flag CLIENT_MULTI_STATEMENTS..but it is still giving the
> same
> error..
> 
> 1296 :SELECT in a stored procedure must have INTO..
> 
> i am also including my code here..
> 
>   1 #include 
>   2 #include "/usr/include/mysql/mysql.h"
>   3
>   4 int main(int argc,char *argv[])
>   5 {
>   6 MYSQL mysql;
>   7 MYSQL_RES *result;
>   8 MYSQL_ROW row;
>   9
>  10 long int i;
>  11 if(!mysql_init(&mysql))
>  12 {
>  13fprintf(stderr,"Initialization failed");
>  14   exit(1);
>  15 }
>  16
>  17
> if(!mysql_real_connect(&mysql,"localhost","root","","test",0,NULL,CLIENT_M
> UL
> TI_RESULTS))
>  18 {
>  19   fprintf(stderr,"%d
> :%s\n",mysql_errno(&mysql),mysql_error(&mysql));
>  20   exit(2);
>  21 }
>  22
>  23 if(mysql_query(&mysql,"call sujay1()")!=0)
>  24 {
>  25fprintf(stderr,"%d
> :%s\n",mysql_errno(&mysql),mysql_error(&mysql));
>  26 }
>  27
>  28 else
>  29 {
>  30 result = mysql_store_result(&mysql);
>  31 i = mysql_num_rows(result);
>  32 printf("No of rows are %d\n",i);
>  33 //row = mysql_fetch_row(result);
>  34 while(row = mysql_fetch_row(result))
>  35 {
>  36 printf("%s  %s\n",row[0],row[1]);
>  37 }
>  38 mysql_free_result(result);
>  39 }
>  40
>  41 mysql_close(&mysql);
>  42 return 0;
>  43 }//end of main
> 
> The procedure sujay1() which i am using in  my program is
> 
>  create procedure sujay1()
>  begin
>   select name,species from pet;
>  end
> 
> 
> 
> Thanx
> sujay koduri
> (plez sent back the corrected one)
> I am using..
> 
> Red hat-9.0
> MySQL - 5.0
> 
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

prepared statements in C API not working...headers needed!!

[Sujay Koduri]

people

actually this is a stupid question i suppose..but..

i tried out to run a query from my C program using prepared statements..

It is giving .."undefined reference.."..

so anyone who worked on prepared statements can give me the headers that i
should include to run them..

Thanx in advance
sujay



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]