Re: Subquery returns more than 1 row (1242)
Alvaro Cobo wrote: Hi guys: I work with some grassroot communities, which we have to keep following up. And we need to know how the population changes in the different years. What I figured out is to have two tables: one to have the name of the organization, and the second which contains the changes in the time. To avoid people having to input the stored data each time (and just update from the stored data), I am trying to create a query which inserts (re-inserts) the primary key of the primary table into the Foreign Key field of the secondary table, adding the current year, and then the user just have to update the information and it has the year collected from a form variable. The query is as follows: INSERT INTO tbl_secondary( FK_ORG, year ) VALUES ( (SELECT PK_ORG FROM tbl_primary WHERE Province = 'Province1'), 2006 ) It gives me the following error message: 1242. Subquery returns more than 1 row If I try to do the following query it works, but it is not useful for me because we need to insert the data with its especific year (timestamp is not useful as well): INSERT INTO tbl_secondary( FK_ORG ) ( SELECT PK_ORG FROM tbl_primary WHERE Province = 'Province1' ) What about : INSERT INTO tbl_secondary( FK_ORG, year ) SELECT PK_ORG, 2006 FROM tbl_primary WHERE Province = 'Province1'; (If you are sure the SELECT returns only one row, and that you have a UNIQUE index on (FK_ORG,Year). ) -- 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]
Re: Replication question
- Original Message - From: Kishore Jalleda as per http://dev.mysql.com/doc/mysql/en/replication-compatibility.html there should be no problems On 8/17/05, Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Well, there are a few differences between 4.0.x and 4.1.x that might cause some problems: http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html Lists most if not all of them. At a quarter of the page the block Server changes lists differences in the way tables are built internally, differences in comparing things, etc. Half way on that page the block SQL changes list incompatibilities in query syntax, etc. For example: - DELETE from multiple tables ( In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name: ) http://dev.mysql.com/doc/mysql/en/delete.html There is no easy solution as the master (4.0) requires a different syntax than the slave (4.1). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-connector-net problem
Hello, I am running MySQL 5.0.11-bata and mysql-connector-net-1.0.4. The byte array returns when I issued the following query. SELECT 1/ 2; Perhaps, I think that it is new Precision Math. When can the new feature be used? __ Save the earth http://pr.mail.yahoo.co.jp/ondanka/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By Question
On 17/08/2005, Schimmel LCpl Robert B wrote:
If I do a select * from the table
without an order by clause, I get the results in the order which they
were entered into the table (which is how I want them).
This is not correct (e.g. on a MyISAM table in which you have done
deletes - see example below)
When I do a select [column_name] from the table, because
of MySQL's go-getter attitude, the results are sorted alphabetically
for that one column.
When [column_name] is indexed, MySQL will only use the index to get
your records (to boost performance) and in the index the records are
ordered.
How can I get just the one column of data that I
want returned in the order which it was entered into table?
You can't unless you follow the advise of the other posters in this
thread.
Example:
USE test;
DROP TABLE foo;
CREATE TABLE foo (
a CHAR(10),
b CHAR(10)
) ENGINE=MyISAM;
INSERT INTO foo VALUES
('a', 'a'), ('b', 'b'), ('d', 'd'), ('c', 'c');
SELECT a FROM foo;
SELECT * FROM foo;
ALTER TABLE foo ADD INDEX (a);
SELECT a FROM foo;
SELECT * FROM foo;
DELETE FROM foo WHERE a = 'a';
INSERT INTO foo VALUES ('x', 'x');
SELECT a FROM foo;
SELECT * FROM foo;
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query from two databases
Hello. Something like this: SELECT INET_NTOA((INET_ATON(odip)8)8) AS mask , COUNT(odip) FROM a GROUP BY mask; You can merge results from both tables using UNION. See more about INET_xxx functions at: http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html rmck [EMAIL PROTECTED] wrote: In each database I have a table with one column I want to compare and then count. So db1.a.odip and db2.aa.newip are the databases, table and coulumns. The data in each column is Ip's, so my result would be a list and count for each by subnet. So the result would be like: db1.a.odip count --- 10.10.10.30 192.168.5. 10 db2.aa.newip count --- 10.10.10. 20 192.168.5.40 I can parse two select statement to a perl script, but was wondering if this can be done in a select statement. Thanks, Rob -- 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]
Re: Replication question
Hello. According to: http://dev.mysql.com/doc/mysql/en/replication-compatibility.html you shouldn't have any problems, but 4.0.16 is a very old version. I strongly recommend you to upgrade. Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Thanks, Jeff -- 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]
Re: mysqld stops suddenly ... help please
Hello. /usr/local/mysql/libexec/mysqld: Out of memory (Needed 32704 bytes) /usr/local/mysql/libexec/mysqld: Can't read dir of '/var/tmp/' (Errcode: 11) /usr/local/mysql/libexec/mysqld: Out of memory (Needed 8156 bytes) Decrease your memory related variables. You have rather old versions of MySQL, I strongly recommend you to upgrade. See this link as well: http://dev.mysql.com/doc/mysql/en/crashing.html [EMAIL PROTECTED] wrote: Hi all a few days a go i have troubles with mysql, the service stops and this is on two boxes wiht diferents OS. The first one is a FreeBSD 5.3-RELEASE on a sparc64 the mysql Version is '4.1.5-gamma' and i got this messages in my logs: 050816 17:11:06 mysqld restarted Fatal error 'gc cannot wait for a signal' at line 194 in file /usr/src/lib/libc_r/uthread/uthread_gc.c (errno = 0) mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 050816 17:11:07 mysqld ended 050817 11:13:51 mysqld started 050817 11:13:52 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 050817 11:13:52 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050817 11:13:52 InnoDB: Flushing modified pages from the buffer pool... 050817 11:13:52 InnoDB: Started; log sequence number 0 43634 /usr/local/libexec/mysqld: ready for connections. Version: '4.1.5-gamma' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.5 The other one is a solaris 8 on a sparc64 whit mysql version 3.23.45 and i got this error messages: 050815 19:01:08 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=7 max_connections=500 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 050815 19:41:01 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=1 max_connections=500 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 336376 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 050815 19:41:02 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=16773120 record_buffer=131072 sort_buffer=524280 max_used_connections=0 max_connections=500
Re: views in 5.0.11
Hello. This is very similar to: http://bugs.mysql.com/bug.php?id=12382 Fix will appear in 5.0.12 Rich Allen [EMAIL PROTECTED] wrote: iH i have a view created in 5.0.11 on several innodb tables. when doing a select * on the view after first getting into the mysql command line, the last column has incorrect values. without running any other command and performing the same select statement, all columns are correct. is this a known issue? running mysql on mac os x 10.4 thanks Rich Allen Dare Do -- 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]
Deleted rows
If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the skipped ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: Deleted rows
Scott Hamm [EMAIL PROTECTED] wrote on 08/18/2005 08:59:00 AM: If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the skipped ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? It is possible, however because SQL is designed more to show you what is in the database better than what is not in the database, you will need to make a list of numbers and check for those records not in the list. See this thread: http://lists.mysql.com/mysql/187981 (some of which was posted just last night) describing how to fill in missing dates. Yours is the exact same problem but instead of dates, you are trying to find missing numbers. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
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.
snip
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 config.h
#endif
#include iostream
#include cstdlib
#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 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);
RE: prepared statement problems
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.
snip
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 config.h
#endif
#include iostream
#include cstdlib
#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 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];
Re: prepared statement problems
/
Developer: Darrell Cormier
Date : 10-Aug-2005
App Name : msql_conn_env
Purpose : Test MySQL connection using an environment
variable for the connection string.
/
#ifdef HAVE_CONFIG_H
#include config.h
#endif
#include iostream
#include cstdlib
#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[])
{
char product[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= str_length[0];
//bind lot
parm_bind[1].buffer_type = MYSQL_TYPE_STRING;
parm_bind[1].buffer = (void*) lot;
parm_bind[1].buffer_length = sizeof(lot);
parm_bind[1].is_null=is_null[1];
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 */
ps_meta_result = mysql_stmt_result_metadata(stmt);
if (!ps_meta_result)
{
cerr ERROR - mysql_stmt_result_metadat() failed! endl;
cerr mysql_stmt_error(stmt) endl;
exit(0);
}
col_count = mysql_num_fields(ps_meta_result);
cout Total number of columns in SELECT statement :col_count endl;
if (col_count !=1) //validate column count
{
cerrInvalid number of columns returned by MySQL!! endl;
exit(0);
}
// Execute the SELECT query
if(mysql_stmt_execute(stmt))
{
cerr mysql_stmt_execute() failed!! endl;
cerr mysql_stmt_error(stmt) endl;
exit(0);
}
memset(res_bind, 0, sizeof(res_bind));
res_bind[0].buffer_type = MYSQL_TYPE_STRING;
res_bind[0].buffer = (char*) product;
res_bind[0].buffer_length = STRING_SIZE;
res_bind[0].is_null = is_null[0];
res_bind[0].length = length;
// bind the results buffers
if (mysql_stmt_bind_result(stmt, res_bind))
{
cerr mysql_stmt_bind_result() failed endl;
cerr mysql_stmt_error(stmt) endl;
exit(0);
}
//buffer the results to the client
if (mysql_stmt_store_result(stmt)!=0)
{
cerr mysql_stmt_store_result() failed endl;
cerr mysql_stmt_error(stmt) endl;
exit(0);
}
row_count = mysql_stmt_num_rows(stmt);
//fetch all rows
fetch_row_count=0;
cout Fetching results... endl;
while(!mysql_stmt_fetch(stmt))
{
fetch_row_count++;
cout Row fetch_row_count endl;
//product (aka column1)\
cout Product : ;
if (is_null[0])
{
cout NULL endl;
}
else
{
cout product ( length ) endl;
}
Re: prepared statement problems
Sorry, the text of my message did not come through for some reason. I have included it below. DC 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger exception handling
Hello, I've been looking into converting our existing Oracle PL/SQL code to mysql. A lot of the syntax is pretty straight forward, and really doesn't require much change from what I've been testing with thus far. However, I'm trying to handle exceptions, and I cannot seem to find any documentation that shows me what I'm looking for. In oracle, we have bits of code like this: IF INSTR(:new.ACCOUNT_NUM, ' ') 0 THEN RAISE AcctNumHasSpace; END IF; and then EXCEPTION WHEN AcctNumHasSpace THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot insert space into ACCOUNT_NUM'); But I cannot seem to figure out how to replicate that behavior. Is it something that's possible to do as of yet? Or not really? Basically in this situation, we want the insert to fail if there is a space in that field. Any advice would be great. Dan. _ This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: prepared statement problems
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 config.h
#endif
#include iostream
#include cstdlib
#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= str_length[0];
//bind lot
parm_bind[1].buffer_type = MYSQL_TYPE_STRING;
parm_bind[1].buffer = (void*) lot;
parm_bind[1].buffer_length = sizeof(lot);
parm_bind[1].is_null=is_null[1];
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))
{
Re: Subquery returns more than 1 row (1242). Solved.
Thanks Phillipe: Perfect. It worked!!!. All solved with your help. And actually, the subquery returns more than one row, and it inserts all the data I need under the WHERE condition without any problem. Thanks again and best regards, Alvaro. - Original Message - From: Philippe Poelvoorde [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, August 18, 2005 1:59 AM Subject: Re: Subquery returns more than 1 row (1242) Alvaro Cobo wrote: Hi guys: I work with some grassroot communities, which we have to keep following up. And we need to know how the population changes in the different years. What I figured out is to have two tables: one to have the name of the organization, and the second which contains the changes in the time. To avoid people having to input the stored data each time (and just update from the stored data), I am trying to create a query which inserts (re-inserts) the primary key of the primary table into the Foreign Key field of the secondary table, adding the current year, and then the user just have to update the information and it has the year collected from a form variable. The query is as follows: INSERT INTO tbl_secondary( FK_ORG, year ) VALUES ( (SELECT PK_ORG FROM tbl_primary WHERE Province = 'Province1'), 2006 ) It gives me the following error message: 1242. Subquery returns more than 1 row If I try to do the following query it works, but it is not useful for me because we need to insert the data with its especific year (timestamp is not useful as well): INSERT INTO tbl_secondary( FK_ORG ) ( SELECT PK_ORG FROM tbl_primary WHERE Province = 'Province1' ) What about : INSERT INTO tbl_secondary( FK_ORG, year ) SELECT PK_ORG, 2006 FROM tbl_primary WHERE Province = 'Province1'; (If you are sure the SELECT returns only one row, and that you have a UNIQUE index on (FK_ORG,Year). ) -- 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: Deleted rows
Scott, How do I execute a query that shows missing ID's like so: SELECT id AS i FROM tbl WHERE i 1 AND NOT EXISTS( SELECT id FROM tbl WHERE id = i - 1 ); PB - Scott Hamm wrote: If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the "skipped" ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Partial Filtering
I'm having one of those slow brain days. I want a partial filter egg. All records where field1 begins with ABC any body know the where clause to do this. In Access it's where field1 = 'ABC*' but I can't find the MYSQL equivalent, or isn't there one. Regards, Justin
Re: Partial Filtering
Blue Wave Software [EMAIL PROTECTED] wrote on 18/08/2005 15:57:34: I'm having one of those slow brain days. I want a partial filter egg. All records where field1 begins with ABC any body know the where clause to do this. In Access it's where field1 = 'ABC*' but I can't find the MYSQL equivalent, or isn't there one. WHERE field1 LIKE 'ABC%' ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cost-Effective Database Scale-Out Using MySQL
I just got an email from MySQL concerning web seminar. Since I'm deaf I would like to attend, is there any accomodations that I can use to get in touch? There is a Video Relay Interpreting service online and am wondering if MySQL could use that service? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: Partial Filtering
Blue Wave Software [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I'm having one of those slow brain days. I want a partial filter egg. All records where field1 begins with ABC any body know the where clause to do this. In Access it's where field1 = 'ABC*' but I can't find the MYSQL equivalent, or isn't there one. I believe you are looking for the following syntax: SELECT id FROM table WHERE field1 LIKE 'abc%' DanB -- 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 said the following on 08/18/2005 09:33 AM: 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 I have made the changes you suggested but I still get no rows returned. Thank you for your help. I will keep searching. Regards, DC -- Darrell Cormier [EMAIL PROTECTED] Registered Linux user #370711 http://counter.li.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted rows
Peter, Your query may work for data with single-row gaps (like his example data) but it will not work if the sequence skips more than one number. Look at this sequence: 1,2,3,8,9,10 The OP would like to detect that 4,5,6, and 7 are missing from the sequence. Your query would have only found that 7 was missing. Nice try, but sorry. It just won't meet the need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Peter Brawley [EMAIL PROTECTED] wrote on 08/18/2005 10:56:34 AM: Scott, How do I execute a query that shows missing ID's like so: SELECT id AS i FROM tbl WHERE i 1 AND NOT EXISTS( SELECT id FROM tbl WHERE id = i - 1 ); PB - Scott Hamm wrote: If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the skipped ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL statement generated from prepared statement
Is there a way to get MySQL to report the SQL statement it receives from a prepared statement execution (from a program using the C-API )? I am wondering what the SQL statement looks like that is being generated from my program to help debug it. Thanks, DC -- Darrell Cormier [EMAIL PROTECTED] Registered Linux user #370711 http://counter.li.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted rows
On 8/18/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Peter, Your query may work for data with single-row gaps (like his example data) but it will not work if the sequence skips more than one number. Look at this sequence: 1,2,3,8,9,10 The OP would like to detect that 4,5,6, and 7 are missing from the sequence. Your query would have only found that 7 was missing. Nice try, but sorry. It just won't meet the need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Peter Brawley [EMAIL PROTECTED] wrote on 08/18/2005 10:56:34 AM: Scott, How do I execute a query that shows missing ID's like so: SELECT id AS i FROM tbl WHERE i 1 AND NOT EXISTS( SELECT id FROM tbl WHERE id = i - 1 ); PB - Scott Hamm wrote: If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the skipped ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Something similiar to this query for MS SQL (very time consuming) SET NOCOUNT ON DECLARE @mindate smalldatetime, @maxdate smalldatetime SET @mindate='2005-08-01' SET @maxdate='2005-08-31' CREATE TABLE #count (ID int); DECLARE @low int, @high int SET @low=( SELECT MIN(QAErrors.ID)-1 FROM QAErrors LEFT JOIN QA Q ON Q.ID=QAErrors.QAID WHERE KeyDate BETWEEN @mindate AND @maxdate ) SET @high=( SELECT MAX(QAErrors.ID) FROM QAErrors LEFT JOIN QA Q ON Q.ID=QAErrors.QAID WHERE KeyDate BETWEEN @mindate AND @maxdate ) DECLARE @counter INT SET @counter = @low WHILE @counter @high BEGIN SET @counter = @counter + 1 INSERT INTO #count VALUES (@counter) END SET NOCOUNT OFF SELECT @mindate AS 'From', @maxdate AS 'To', count(*) AS 'Total Deleted' FROM #count C LEFT JOIN QAErrors QE ON QE.ID=C.ID LEFT JOIN QA Q ON Q.ID=QE.QAID WHERE Q.ID http://Q.ID is null; DROP TABLE #count; -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html
Re: Deleted rows
Scott, Shawn, The OP would like to detect that 4,5,6, and 7 are missing from the sequence. Your query would have only found that 7 was missing. Right! For sequences longer than 1 you need something like... SELECT a.id+1 AS 'Missing From', MIN(b.id)-1 AS 'To' FROM test AS a, test AS b WHERE a.id b.id GROUP BY a.id HAVING a.id + 1 MIN(b.id) ORDER BY 1; PB - [EMAIL PROTECTED] wrote: Peter, Your query may work for data with single-row gaps (like his example data) but it will not work if the sequence skips more than one number. Look at this sequence: 1,2,3,8,9,10 The OP would like to detect that 4,5,6, and 7 are missing from the sequence. Your query would have only found that 7 was missing. Nice try, but sorry. It just won't meet the need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Peter Brawley [EMAIL PROTECTED] wrote on 08/18/2005 10:56:34 AM: Scott, How do I execute a query that shows missing ID's like so: SELECT id AS i FROM tbl WHERE i 1 AND NOT EXISTS( SELECT id FROM tbl WHERE id = i - 1 ); PB - Scott Hamm wrote: If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the "skipped" ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLDUMP Problem
Sirs, When i use mysqldump on Version 4.1.x, all tables had a one record insert generated in script. When i use mysqldump on a 4.0.x version this problem dos not occurs. How to solve it? Regards For all Carlos J Souza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how does mysql treat the these two different deleting methods?
Method A: Delete from STORIES where story_id = '1' Delete from STORIES where story_id = '3' Delete from STORIES where story_id = '5' or Method B: Delete from STORIES where story_id IN (1,3,5)...I guess this is the same as using OR's What is the state of the database if the deletion on story_id = '3' fails? In method A, 1 and 5 are deleted. in method B, is only 1 deleted? are 1 and 5 deleted, or are none of them deleted (i.e. MySQl treats this as one unit of work)? -- -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how does mysql treat the these two different deleting methods?
Comments are inline. Just a friendly reminder, you replied to an existing thread then changed the subject to send this email. So people whose email clients thread messages might not even be seeing your email if they don't look inside the thread you originally replied from. James wrote: Method A: Delete from STORIES where story_id = '1' Delete from STORIES where story_id = '3' Delete from STORIES where story_id = '5' or Method B: Delete from STORIES where story_id IN (1,3,5)...I guess this is the same as using OR's What is the state of the database if the deletion on story_id = '3' fails? In method A, 1 and 5 are deleted. That depends on how your application handles the error, which I'm sure you're aware of. in method B, is only 1 deleted? are 1 and 5 deleted, or are none of them deleted (i.e. MySQl treats this as one unit of work)? It stops deleting any records when it hits an error. You can use DELETE IGNORE, to ignore the errors and continue deleting records. The order those rows are deleted in is undefined (unless you use an ORDER BY clause). So, if Story 3 was deleted last, you would still have 3 and 5, but if it was first you would have all the records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump Problem
Sirs, When i use mysqldump on Version 4.1.x, all tables had a one record insert generated in script. When i use mysqldump on a 4.0.x version this problem does not occurs. How to solve it? Regards For all Carlos J Souza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump Problem
Carlos J Souza [EMAIL PROTECTED] wrote on 08/18/2005 03:16:15 PM: Sirs, When i use mysqldump on Version 4.1.x, all tables had a one record insert generated in script. When i use mysqldump on a 4.0.x version this problem does not occurs. How to solve it? Regards For all Carlos J Souza I think that generating the INSERT statement (the 4.1.x behavior) should not be a problem. Can you try again to describe what it is you would like to do, how you are trying to do it, and what is not happening in the manner you desire? I can tell that English is not your native language. I usually do not make this suggestion (as this is an English mailing list) but there are several on the list that do speak Portuguese (and Spanish) so perhaps you could try describing your problem that way, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
fehler
hallo ich habe eine fehler meldung von mysql: Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 88Warning: mysql_list_tables(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 202Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 88Warning: mysql_list_tables(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 202Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 88 bitte wie kann ich diesen beheben? MfG Falko Götze
Re: fehler
Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 88 I would guess that there is some problem with the syntax of your SQL. If you make it a habit to do queries like this: $sql = SELECT * FROM table; $result = mysql_query($sql) or die(mysql_error() . with query $sql); then the error usually becomes ovious. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqldump Problem
Carlos J Souza wrote: Sirs, When i use mysqldump on Version 4.1.x, all tables had a one record insert generated in script. When i use mysqldump on a 4.0.x version this problem does not occurs. How to solve it? Not 100% sure what you are looking for, but try using --skip-extended-insert. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deleted rows
I beleive the below solution will not detect rows missing from the very top of the table, try this... select ifnull((select max(a.test_id) +1 from tests a where a.test_idb.test_id),1) as 'from' ,b.test_id -1 as 'to' from tests b left outer join tests x on x.test_id=b.test_id -1 where x.test_id is NULL and b.test_id1 order by 1 Ed From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, August 18, 2005 10:18 AM To: [EMAIL PROTECTED] Cc: Scott Hamm; 'Mysql ' Subject: Re: Deleted rows Scott, Shawn, The OP would like to detect that 4,5,6, and 7 are missing from the sequence. Your query would have only found that 7 was missing. Right! For sequences longer than 1 you need something like... SELECT a.id+1 AS 'Missing From', MIN(b.id)-1 AS 'To' FROM test AS a, test AS b WHERE a.id b.id GROUP BY a.id HAVING a.id + 1 MIN(b.id) ORDER BY 1; PB - [EMAIL PROTECTED] wrote: Peter, Your query may work for data with single-row gaps (like his example data) but it will not work if the sequence skips more than one number. Look at this sequence: 1,2,3,8,9,10 The OP would like to detect that 4,5,6, and 7 are missing from the sequence. Your query would have only found that 7 was missing. Nice try, but sorry. It just won't meet the need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Peter Brawley [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on 08/18/2005 10:56:34 AM: Scott, How do I execute a query that shows missing ID's like so: SELECT id AS i FROM tbl WHERE i 1 AND NOT EXISTS( SELECT id FROM tbl WHERE id = i - 1 ); PB - Scott Hamm wrote: If I got a table as follows: ID foo 1 12345 2 12346 4 12348 6 12349 7 12388 9 12390 How do I execute a query that shows missing ID's like so: 3 5 8 I wouldn't expect for it to show deleted data that was deleted, just show the skipped ID's. That way I determine if operator deleted too much (cheating at QC) Is it possible? No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 8/17/2005
Re: Mysqldump Problem
I think what you are seeing is called the extended insert format. It's much faster to process a single large INSERT statement than several smaller ones. There are two things to remember: a) if you have a lot of data in your table, an extended insert statement may become too long to re-read into your server as a single chunk. You can tell mysqldump to create an extended insert statement in chunks of a certain size if you tell mysqldump just how big you want those chunks to be. Use the --max_allowed_packet= option to keep your extended inserts down to a certain maximum size. The largest useful packet is that which matches the max_allowed_packet variable value for the server receiving the data. Any larger than that and the restore will fail. b) you can disable the extended inserts completely if you run mysqldump with the --skip-extended-insert option To see all of the options supported by mysqldump, use the --help option like this: mysqldump --help And, one last thing, when responding to help from the list, please do not forget to CC the list. I could have been called away or unable to answer for an extended period of time and anyone else on the list could have helped in my place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Carlos J Souza [EMAIL PROTECTED] wrote on 08/18/2005 03:42:36 PM: Shawn, it sees that in the attached archive only one register was generated for each table of database. it is this that I am asking in the forum. Because with Mysql 4.0.x this does not happen. This only happens in Mysql 4.1 i use a follow sintax: mysqldump --host localhost --user root [databasename] c:\file.sql Regards Carlos J Souza On Thu, 18 Aug 2005 14:10:56 -0400, [EMAIL PROTECTED] wrote: Carlos J Souza [EMAIL PROTECTED] wrote on 08/18/2005 03:16:15 PM: Sirs, When i use mysqldump on Version 4.1.x, all tables had a one record insert generated in script. When i use mysqldump on a 4.0.x version this problem does not occurs. How to solve it? Regards For all Carlos J Souza I think that generating the INSERT statement (the 4.1.x behavior) should not be a problem. Can you try again to describe what it is you would like to do, how you are trying to do it, and what is not happening in the manner you desire? I can tell that English is not your native language. I usually do not make this suggestion (as this is an English mailing list) but there are several on the list that do speak Portuguese (and Spanish) so perhaps you could try describing your problem that way, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [attachment siexwin.sql deleted by Shawn Green/Unimin]
MySQL 4.1 full throttled?
Hi folks, I just downloaded MySQL 4.1 to see if its a go ahead with our application, I am doing a tranfser from our current datafile to MySQL, its taking about 45 min to do this process.. when I do the same process and transfer to a M$ SQL server, it takes about 2 min max. is the 4.1 of mySQL that I downloaded throttled somehow until I purchase the license for it? many thanks Andrew
Re: MySQL 4.1 full throttled?
Andrew stolarz [EMAIL PROTECTED] wrote on 08/18/2005 03:29:39 PM: Hi folks, I just downloaded MySQL 4.1 to see if its a go ahead with our application, I am doing a tranfser from our current datafile to MySQL, its taking about 45 min to do this process.. when I do the same process and transfer to a M$ SQL server, it takes about 2 min max. is the 4.1 of mySQL that I downloaded throttled somehow until I purchase the license for it? many thanks Andrew How _exactly_ are you doing a transfer from our current datafile to MySQL? Not only can technique make a difference but some of your server's settings may also need tweaking (based on the capaciity of your hardware). How beefy is your server? How did you configure your server's settings? Are you transferring this over a network or is the source file local to the server? There are so many variables to consider Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 4.1 full throttled?
Hi Shawn, I am using the MySQL ODBC driver to connect from our application to the MySQL database, The actual server setting are default stright from the origional install, I am running all this from my laptop localy (both MySQL and MS SQL tests), P4 2.8GHZ, and 512mb ram. its doing about 10 records pre second for the transfer. which is dead slow. just want to knock off the option, MySQL is not limited anyway without a license is it? Thanks Andrew Stolarz On 8/18/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Andrew stolarz [EMAIL PROTECTED] wrote on 08/18/2005 03:29:39 PM: Hi folks, I just downloaded MySQL 4.1 to see if its a go ahead with our application, I am doing a tranfser from our current datafile to MySQL, its taking about 45 min to do this process.. when I do the same process and transfer to a M$ SQL server, it takes about 2 min max. is the 4.1 of mySQL that I downloaded throttled somehow until I purchase the license for it? many thanks Andrew How _exactly_ are you doing a transfer from our current datafile to MySQL? Not only can technique make a difference but some of your server's settings may also need tweaking (based on the capaciity of your hardware). How beefy is your server? How did you configure your server's settings? Are you transferring this over a network or is the source file local to the server? There are so many variables to consider Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 4.1 full throttled?
Nope, it's not throttled by default but a bad set of INI settings can ruin your day. I also run MySQL locally on a laptop (with worse hardware figures than yours) and I have achieved Access to MySQL conversion speeds of 2000-8000 records per second (depending on the physical size of the record) through a VB Script through MyODBC. Your performance is uncharacteristically slow. Could your anti-virus be getting in your way (trying to check EVERYTHING that MySQL is writing to disk)? Are you making and breaking one connection per record? There are so many things that can affect performance, the more details you give, the more helpful we can be. (don't forget to CC the list on all responses) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew stolarz [EMAIL PROTECTED] wrote on 08/18/2005 03:53:54 PM: Hi Shawn, I am using the MySQL ODBC driver to connect from our application to the MySQL database, The actual server setting are default stright from the origional install, I am running all this from my laptop localy (both MySQL and MS SQL tests), P4 2.8GHZ, and 512mb ram. its doing about 10 records pre second for the transfer. which is dead slow. just want to knock off the option, MySQL is not limited anyway without a license is it? Thanks Andrew Stolarz On 8/18/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Andrew stolarz [EMAIL PROTECTED] wrote on 08/18/2005 03:29:39 PM: Hi folks, I just downloaded MySQL 4.1 to see if its a go ahead with our application, I am doing a tranfser from our current datafile to MySQL, its taking about 45 min to do this process.. when I do the same process and transfer to a M$ SQL server, it takes about 2 min max. is the 4.1 of mySQL that I downloaded throttled somehow until I purchase the license for it? many thanks Andrew How _exactly_ are you doing a transfer from our current datafile to MySQL? Not only can technique make a difference but some of your server's settings may also need tweaking (based on the capaciity of your hardware). How beefy is your server? How did you configure your server's settings? Are you transferring this over a network or is the source file local to the server? There are so many variables to consider Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 4.1 full throttled?
MySQL is not limited in anyway if you don't have a license. The default install is not optimal for the hardware you are running it on. The default parameters are set pretty low so as not to monopolize your system. If you are doing a big insert, you should adjust the appropriate buffers (key_buffer_size, myisam_max_sort_file_size, bulk_insert_buffer_size) . M$ SQL is kind of optimized out of the box. MySQL allows you to really fine tune the database to fit your needs, right down to using difference table types for your data. Don't expect good performance by just installing and running. You would actually want to set certain variables to one value for your bulk insert and then change them for normal use. On Aug 18, 2005, at 3:29 PM, Andrew stolarz wrote: Hi folks, I just downloaded MySQL 4.1 to see if its a go ahead with our application, I am doing a tranfser from our current datafile to MySQL, its taking about 45 min to do this process.. when I do the same process and transfer to a M$ SQL server, it takes about 2 min max. is the 4.1 of mySQL that I downloaded throttled somehow until I purchase the license for it? many thanks Andrew -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fehler
Hallo, Ich werde in english sprechen, so : You get this error usually, when your connection with the DB juste broke, or it's not working, try to test the return value of mysql_connect, and see what is going on. Or you can have this error when the server is too busy. Tschüss ! 2005/8/18, Scott Noyes [EMAIL PROTECTED]: Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /srv/www/htdocs/web1/html/inc/class/sql.php on line 88 I would guess that there is some problem with the syntax of your SQL. If you make it a habit to do queries like this: $sql = SELECT * FROM table; $result = mysql_query($sql) or die(mysql_error() . with query $sql); then the error usually becomes ovious. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Info
Could you let me know if mysql-standard-4.1.13-sun-solaris2.9-sparc works with snort-2.4.0. I was not able to dump the log from snort into mysql. Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
password(), sha1() and md5()
Hello!
I am facing the following problem:
When using
'update TABLE set FIELD=PASSWORD('foo');'
the query
'select * from TABLE where FIELD=PASSWORD('foo');'
delivers an empty set.
Same with the SHA1 or MD5 functions.
I am using Mac OS X Tiger (same problem with Panther) and MySQL
4.1.12-standard.
First I thought the problem lies in text encoding of the terminal. But
using Java and the JConnector 3.1.10 didn't help.
Trying on WinXP produced a correct behavior using PASSWORD(), but SHA1()
and MD5() failed also.
Any suggestions?
Kind regards
Martin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password(), sha1() and md5()
On 18/08/2005, Martin Schwarz wrote:
When using
'update TABLE set FIELD=PASSWORD('foo');'
the query
'select * from TABLE where FIELD=PASSWORD('foo');'
delivers an empty set.
Same with the SHA1 or MD5 functions.
What is the data type of your FIELD column?
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 full throttled?
Hello, I did a complete new install on another machine but this time I selected the dedicated server option during the first setup, reran the same process and its going about 3 times faster then it did before.. so it looks like I am on the right track! the current sizes I have for the following are: whats the ideal setup for these? key_buffer_size = 37748736 myisam_max_sort_file_size = 48234496 bulk_insert_buffer_size = 8388608 any other sugestions where to look? many thanks everyone, Andrew On 8/18/05, Brent Baisley [EMAIL PROTECTED] wrote: MySQL is not limited in anyway if you don't have a license. The default install is not optimal for the hardware you are running it on. The default parameters are set pretty low so as not to monopolize your system. If you are doing a big insert, you should adjust the appropriate buffers (key_buffer_size, myisam_max_sort_file_size, bulk_insert_buffer_size) . M$ SQL is kind of optimized out of the box. MySQL allows you to really fine tune the database to fit your needs, right down to using difference table types for your data. Don't expect good performance by just installing and running. You would actually want to set certain variables to one value for your bulk insert and then change them for normal use. On Aug 18, 2005, at 3:29 PM, Andrew stolarz wrote: Hi folks, I just downloaded MySQL 4.1 to see if its a go ahead with our application, I am doing a tranfser from our current datafile to MySQL, its taking about 45 min to do this process.. when I do the same process and transfer to a M$ SQL server, it takes about 2 min max. is the 4.1 of mySQL that I downloaded throttled somehow until I purchase the license for it? many thanks Andrew -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
mysql_hex_string()
hi everyone... i need to store binary data (corresponding to audio and video) into mysql. i'm using a longblob field. i already used the mysql_real_query() and mysql_real_escape_string(), but had no luck... :( my next idea is to try mysql_hex_string() to store the data in hex format. my question is: after the data is encoded in hex by mysql_hex_string(), how is it returned to its original format? i'm using c/c++ in linux... karima
Re: mysql_hex_string()
At 18:41 -0400 8/18/05, Karima Velasquez wrote: hi everyone... i need to store binary data (corresponding to audio and video) into mysql. i'm using a longblob field. i already used the mysql_real_query() and mysql_real_escape_string(), but had no luck... :( my next idea is to try mysql_hex_string() to store the data in hex format. my question is: after the data is encoded in hex by mysql_hex_string(), how is it returned to its original format? The server decodes the hex value when it executes the statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA 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]
Commit needed even after a select statement ??
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
