Re: kill query and prepared statements
Sorry for the spam, and thanks for you suggestion Johan! http://bugs.mysql.com/bug.php?id=84470 Seb On 01/11/2017 11:21 AM, Johan De Meersman wrote: Seb, You should log a bug at http://bugs.mysql.com - this is not a developer list. /Johan - Original Message - From: "Sebastien FLAESCH" <s...@4js.com> To: "MySql" <mysql@lists.mysql.com> Sent: Tuesday, 10 January, 2017 14:55:42 Subject: kill query and prepared statements Hi all, I have reported this problem before, but I raise it again, since I still get this problem with 5.7.17 See attached code: I want to interrupt a long running statement with CTRL-C by starting a new connect to make a KILL QUERY. I am using the same technique as the mysql client code. The difference here is that my code is using PREPARED STATEMENTS with mysql_stmt_prepare() etc. Problem: After interrupting the first query with CTRL-C, the call to mysql_stmt_close() hangs... Maybe I am missing some new connection or statement option...?!? IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 5.6(.16) Please can someone from MySQL C API team try to reproduce and confirm? Thanks! Seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: kill query and prepared statements
Seb, You should log a bug at http://bugs.mysql.com - this is not a developer list. /Johan - Original Message - > From: "Sebastien FLAESCH" <s...@4js.com> > To: "MySql" <mysql@lists.mysql.com> > Sent: Tuesday, 10 January, 2017 14:55:42 > Subject: kill query and prepared statements > Hi all, > > I have reported this problem before, but I raise it again, since I still get > this problem with 5.7.17 > > See attached code: > > I want to interrupt a long running statement with CTRL-C by starting a new > connect to make a KILL QUERY. > > I am using the same technique as the mysql client code. > > The difference here is that my code is using PREPARED STATEMENTS with > mysql_stmt_prepare() etc. > > Problem: After interrupting the first query with CTRL-C, the call to > mysql_stmt_close() hangs... > > Maybe I am missing some new connection or statement option...?!? > > IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with > 5.6(.16) > > Please can someone from MySQL C API team try to reproduce and confirm? > > Thanks! > Seb > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: kill query and prepared statements
Just to be clear: This is a hint, to find the real problem, this is NOT A WORKAROUND for us! Seb On 01/10/2017 03:53 PM, Sebastien FLAESCH wrote: Investigating different API calls, to see if it makes a difference... Seems that the problem is related to the read-only cursor type option! If you comment out: unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY; s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) ); Then it works ... Seb On 01/10/2017 02:55 PM, Sebastien FLAESCH wrote: Hi all, I have reported this problem before, but I raise it again, since I still get this problem with 5.7.17 See attached code: I want to interrupt a long running statement with CTRL-C by starting a new connect to make a KILL QUERY. I am using the same technique as the mysql client code. The difference here is that my code is using PREPARED STATEMENTS with mysql_stmt_prepare() etc. Problem: After interrupting the first query with CTRL-C, the call to mysql_stmt_close() hangs... Maybe I am missing some new connection or statement option...?!? IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 5.6(.16) Please can someone from MySQL C API team try to reproduce and confirm? Thanks! Seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: kill query and prepared statements
Investigating different API calls, to see if it makes a difference... Seems that the problem is related to the read-only cursor type option! If you comment out: unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY; s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) ); Then it works ... Seb On 01/10/2017 02:55 PM, Sebastien FLAESCH wrote: Hi all, I have reported this problem before, but I raise it again, since I still get this problem with 5.7.17 See attached code: I want to interrupt a long running statement with CTRL-C by starting a new connect to make a KILL QUERY. I am using the same technique as the mysql client code. The difference here is that my code is using PREPARED STATEMENTS with mysql_stmt_prepare() etc. Problem: After interrupting the first query with CTRL-C, the call to mysql_stmt_close() hangs... Maybe I am missing some new connection or statement option...?!? IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 5.6(.16) Please can someone from MySQL C API team try to reproduce and confirm? Thanks! Seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
kill query and prepared statements
Hi all,
I have reported this problem before, but I raise it again, since I still get
this problem with 5.7.17
See attached code:
I want to interrupt a long running statement with CTRL-C by starting a new
connect to make a KILL QUERY.
I am using the same technique as the mysql client code.
The difference here is that my code is using PREPARED STATEMENTS with
mysql_stmt_prepare() etc.
Problem: After interrupting the first query with CTRL-C, the call to
mysql_stmt_close() hangs...
Maybe I am missing some new connection or statement option...?!?
IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with
5.6(.16)
Please can someone from MySQL C API team try to reproduce and confirm?
Thanks!
Seb
#include
#include
#include
#include
#include
#include
static char * c_host = "orion";
static char * c_user = "mysuser";
static char * c_auth = "fourjs";
static intc_port = 3308;
static char * c_sock = NULL;
static char * c_dbnm = "test1";
static int executing_query;
static unsigned long thread_id;
static void kill_query(void)
{
char cmd[50];
MYSQL *h;
h = mysql_init(NULL);
if (!mysql_real_connect(h, c_host, c_user, c_auth,
c_dbnm, c_port, c_sock,
CLIENT_FOUND_ROWS)) {
fprintf(stderr, "kill_query: Could not connect (err=%d)\n", mysql_errno(h));
return;
}
sprintf(cmd, "KILL QUERY %ld", thread_id);
if (mysql_query(h, cmd) != 0) {
fprintf(stderr, "Could not execute %s.", cmd);
}
mysql_close(h);
}
static void handle_ctrlc_signal(int sig)
{
fprintf(stdout, "SIGINT caught! executing_query = %d\n", executing_query);
if (executing_query) {
executing_query = 0;
kill_query();
}
return;
}
int main(int argc, char ** argv)
{
MYSQL * conn;
MYSQL_STMT * stmt;
int i, s;
unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY;
const char * sqlstmt = "select benchmark(10, md5('when will it end?'))";
signal(SIGINT, handle_ctrlc_signal);
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, c_host, c_user, c_auth,
c_dbnm, c_port, c_sock,
CLIENT_FOUND_ROWS)) {
fprintf(stderr, "Could not connect (err=%d)\n", mysql_errno(conn));
return -1;
}
thread_id = mysql_thread_id(conn);
fprintf(stdout, "MySQL thread ID: %ld\n", thread_id);
for (i=0; i<3; i++) {
fprintf(stdout, "\nRound %d:\n", i+1);
fprintf(stdout, "Allocating statement handle...");
stmt = mysql_stmt_init(conn);
if (stmt==NULL) {
fprintf(stderr, "Could not create statement handle (err=%d)\n", mysql_errno(conn));
return -1;
}
fprintf(stdout, " handle = %p\n", (void*) stmt);
fprintf(stdout, "Preparing statement %p ...\n", (void*) stmt);
s = mysql_stmt_prepare(stmt, sqlstmt, (unsigned long) strlen(sqlstmt));
if (s!=0) {
fprintf(stderr, "Could not prepare statement (err=%d)\n", mysql_errno(conn));
return -1;
}
fprintf(stdout, "Setting cursor type to read only for %p ...\n", (void*) stmt);
s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) );
if (s!=0) {
fprintf(stderr, "Could not set cursor type (err=%d)\n", mysql_errno(conn));
return -1;
}
fprintf(stdout, "Executing statement %p ...\n", (void*) stmt);
executing_query = 1;
s = mysql_stmt_execute(stmt);
if (s!=0) {
if (mysql_errno(conn) == 1317) {
fprintf(stdout, "Statement interrupted by user...\n");
} else {
fprintf(stderr, "Could not execute the query (err=%d)\n", mysql_errno(conn));
return -1;
}
}
fprintf(stdout, "Closing/freeing statement handle %p ...\n", (void*) stmt);
s = mysql_stmt_close(stmt);
if (s!=0) {
fprintf(stderr, "Could not close statement handle (err=%d)\n", mysql_errno(conn));
return -1;
}
fprintf(stdout, "Round done.\n");
}
return 0;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Hi, how did u do de-emphasis of sql statements?
Let me change your example slightly... select * from table where name’A’ versus select * from table where name’Z’ Now, let's assume you have an INDEX starting with `name` and names are distributed in the typical way. The will be perhaps 1% of the names satisfying 'Z', but 95% satisfying 'A'. The index would be very useful for Z, but a waste for A. Hence, it is a feature that MySQL does not cache execution plans. You will also find that MySQL's query analyzer is very fast (compared to the competition). Hence, there is much less need for a cache than 'they' have. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, August 13, 2012 5:49 AM To: MID.night Cc: 673575760; mysql Subject: Re: Hi, how did u do de-emphasis of sql statements? - Original Message - From: MID.night 693893...@qq.com Like select * from table where name’A’ is just The same as select * from table where name’B’. The execution plan for both statements is indeed likely (but not guaranteed!) to be the same. As far as I'm aware, though, MySQL does not bother about that, though, as there is no execution plan cache. The query result cache does not equate the statements - it works based off the EXACT query text, INCLUDING spaces and capitalization. When analyzing various logs, the Maatkit/Aspersa/Percona toolset does transform SQL statements into their canonical form, though; so if you're looking for ways to do that you can have a look at how it's done there. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Hi, how did u do de-emphasis of sql statements?
Hey man, I would like to ask a question about de-emphasis of sql statements? I Mean mysql engines have to be aware of which two statements are exactly the same Despite of variables?? values at runtime. Because in slow log, the execution counter is Maintained for each sql statement. De-emphasis is obviously behind the back. Like select * from table where name??A?? is just The same as select * from table where name??B??. How did u do this emphasis?
Re: Hi, how did u do de-emphasis of sql statements?
- Original Message - From: MID.night 693893...@qq.com Like select * from table where name’A’ is just The same as select * from table where name’B’. The execution plan for both statements is indeed likely (but not guaranteed!) to be the same. As far as I'm aware, though, MySQL does not bother about that, though, as there is no execution plan cache. The query result cache does not equate the statements - it works based off the EXACT query text, INCLUDING spaces and capitalization. When analyzing various logs, the Maatkit/Aspersa/Percona toolset does transform SQL statements into their canonical form, though; so if you're looking for ways to do that you can have a look at how it's done there. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
?????? Hi, how did u do de-emphasis of sql statements?
As I consult the oracle 9i reference , I found they actualy have values of 'similar' and 'force' to be set for cursor_shareing parameter, which allows oracle to treat hard-coded statements to bind-variable statements in order to use the same execution plans. I wonder how they do this?..._/// -- -- ??: Johan De Meersmanvegiv...@tuxera.be; : 2012??8??13??(??) 8:48 ??: MID.night693893...@qq.com; : 673575760673575...@qq.com; mysqlmysql@lists.mysql.com; : Re: Hi, how did u do de-emphasis of sql statements? - Original Message - From: MID.night 693893...@qq.com Like select * from table where name??A?? is just The same as select * from table where name??B??. The execution plan for both statements is indeed likely (but not guaranteed!) to be the same. As far as I'm aware, though, MySQL does not bother about that, though, as there is no execution plan cache. The query result cache does not equate the statements - it works based off the EXACT query text, INCLUDING spaces and capitalization. When analyzing various logs, the Maatkit/Aspersa/Percona toolset does transform SQL statements into their canonical form, though; so if you're looking for ways to do that you can have a look at how it's done there. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be
Re: UPDATE triggers with REPLACE statements
; 20111219 03:42 PM -0800, Jim McNeely Not if you are using innoDB tables. For these, you use INSERT and UPDATE triggers. Jim McNeely On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote: 2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. In my experimenting I find (version 5.5.8) that DELETE INSERT are triggered, not UPDATE. The statement There are no user-visible effects... is simply wrong. The SQL-programmer has to be ready for either outcome. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
Perfect!! This is the answer I was looking for. Thanks! I didn't know about this. Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
Good to know and good that you took time to read the manual, good approach. But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY UPDATE? The storage engine is a property of your table and you can set it and/or change it, it is the low-level layer (physical) of the database that takes care on how data is actually stored and retrieved. You can check your table with: SHOW TABLE STATUS LIKE 'your-table-name'; Manual page: http://kae.li/iiiga Cheers Claudio 2011/12/19 Jim McNeely j...@newcenturydata.com In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio
Re: UPDATE triggers with REPLACE statements
With REPLACE, you just set up the query the same as an INSERT statement but otherwise it just works. With ON DUPLICATE UPDATE you have to set up the whole query with the entire text all over again as an update. The query strings for what I'm doing are in some cases pushing enough text in medical report fields that it uses a MediumText data type, and I am watchful of bandwidth and performance, so this seems better - I'm not sending the field names and values twice. It is also something I don't have to program, I can just set the engine. The performance bottleneck is NOT likely going to be MySQL with either engine, but the processes creating these queries have some limitations. Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Jim McNeely On Dec 19, 2011, at 1:28 PM, Claudio Nanni wrote: Good to know and good that you took time to read the manual, good approach. But why bother with REPLACE if you will go with INSERT.ON DUPLICATE KEY UPDATE? The storage engine is a property of your table and you can set it and/or change it, it is the low-level layer (physical) of the database that takes care on how data is actually stored and retrieved. You can check your table with: SHOW TABLE STATUS LIKE 'your-table-name'; Manual page: http://kae.li/iiiga Cheers Claudio 2011/12/19 Jim McNeely j...@newcenturydata.com In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Does anyone know what engine this is? I can't seem to find any info via google. If I could live with the choice of engine, I could make this work with no extra programming at all. Thanks, Jim McNeely On Dec 18, 2011, at 11:26 AM, Claudio Nanni wrote: Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
Not if you are using innoDB tables. For these, you use INSERT and UPDATE triggers. Jim McNeely On Dec 19, 2011, at 11:58 AM, Halász Sándor wrote: 2011/12/19 11:30 -0800, Jim McNeely In the MySQL documentation, we find this tantalizing statement: It is possible that in the case of a duplicate-key error, a storage engine may perform the REPLACE as an update rather than a delete plus insert, but the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. Well, try it--but beware of these statements: the semantics are the same. There are no user-visible effects other than a possible difference in how the storage engine increments Handler_xxx status variables. If accurate, the triggers are those of DELETE INSERT, not UPDATE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
2011/12/19 13:55 -0800, Jim McNeely Anyway, I just thought I would share. BTW I experimented, and innoDB does updates and fires off update triggers for REPLACE statements, but MyISAM does delete/inserts. Thank you. Which version? Well, then the documentation is wrong: it is indeed visible to the user which happens. And what would innoDB do in this case, UNIQUE index X1 and UNIQUE index X2, with a VARCHAR f, too, X1 X2 f 1 2 a 2 3 b and one REPLACEs with (2, 2, 'b')? (Earlier in the section such a situation is referred to.) Delete one and UPDATE the other? User beware! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UPDATE triggers with REPLACE statements
Only if you can change the application you could use INSERTON DUPLICATE KEY UPDATE instead of REPLACE. Check Peter's post here: http://kae.li/iiigi Cheers Claudio 2011/12/17 Jim McNeely j...@newcenturydata.com Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: UPDATE triggers with REPLACE statements
2011/12/16 16:00 -0800, Jim McNeely I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little problem? I have an idea but I have a feeling there is something better out there. Exactly what do you mean? You want the DELETE-trigger to talk to the INSERT-trigger for to learn whether the field changed? Try user-defined variable. I do not like that, but the only other that comes to mind is no better, a tiny table that exists only for the DELETE-trigger to save the field value, and the INSERT-trigger to look at it. Here is a trigger that inserts into a table PaimentLog after there is inserting into a table GiftAndShare: CREATE TRIGGER newGiftAft AFTER INSERT ON GiftAndShare FOR EACH ROW BEGIN ... INSERT INTO PaimentLog SELECT NEW.MemberID, Surname, GivenName, NEW.Why, NEW.Amount, NEW.Date, NEW.Method, NEW.byWhom FROM Nam WHERE MemberID = NEW.MemberID AND Rank = 0; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UPDATE triggers with REPLACE statements
Here is a fun one! I have a set of tables that get populated and changed a lot from lots of REPLACE statements. Now, I need an ON UPDATE trigger, but of course the trigger never gets triggered because REPLACES are all deletes and inserts. The trigger is going to populate another table as a queue for a system to do something whenever a particular field changes. SO, does anyone have some slick idea how to handle this little dilemma? I have an idea but I have a feeling there is something better out there. Thanks! Jim McNeely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 04:00, Hank hes...@gmail.com wrote:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Thanks, that is a good point. I would actually prefer errors to arise
on insert then a potentially inconsistent database or bad data. I
should definitely learn to use stored procedures, I know.
That said, I do go to great lengths to validate my data. What is an
alpha value? I do check is_numeric() and null, of course.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 07:47, Reindl Harald h.rei...@thelounge.net wrote: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); all other things in the context of hand-written queries are all the nice one we read every day in the news and should NOT recommended because the next beginner reading this makes all the mistakes again Thanks, Reindi. I actually do something like this (simplified, in real code I use an array and a small custom function): $mysqlName=mysql_real_escape_string($name); Then, in the query I can see that all my variables start with $mysql* so I know that they have been sanitized. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here It's a matter of opinion. I never said the data wasn't sanitized (it is). But sometimes calculated values or bugs in PHP code end up with a null variable field. I was just suggesting the choice between two errors -- one syntax which will generate a hard failure of the query and likely whatever page, or a soft logical error, which won't. In either case, I have error trapping to catch both types of errors and alert me to them. I prefer the errors to be logical ones and not syntax errors. $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); I think what you posted is ugly style which makes reading the actual SQL in PHP code much harder to read and debug. The data validation should take place elsewhere long before it gets to constructing the SQL statement.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 16:55, schrieb Hank: what ugly style - if it is not numeric and you throw it to the database you are one of the many with a sql-injection because if you are get ivalid values until there you have done no sanitize before and do not here It's a matter of opinion. I never said the data wasn't sanitized (it is). But sometimes calculated values or bugs in PHP code end up with a null variable field. I was just suggesting the choice between two errors -- one syntax which will generate a hard failure of the query and likely whatever page, or a soft logical error, which won't. In either case, I have error trapping to catch both types of errors and alert me to them. I prefer the errors to be logical ones and not syntax errors. $sql=INSERT into table VALUES ( . (int)$id . ,' . mysql_real_escape_string($val) . '); or using a abstraction-layer (simple self written class) $sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val) . '); I think what you posted is ugly style which makes reading the actual SQL in PHP code much harder to read and debug. The data validation should take place elsewhere long before it gets to constructing the SQL statement. it is not because it is clear that it is sanitized instead hope and pray thousands of layers somewhere else did it - for a inline-query the best solution, if you are using a framework you will never have the insert into at this place! what i meant as ugly is that you are somewhere writing an inline-query and are not sure if it is a number or not - so it is NOT sanitized before because if you tell me it is you sanitze does not work if you get a non-integer at this point and you sanitze-method has to throw the error long before if it is really working signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 18:11, Reindl Harald h.rei...@thelounge.net wrote:
it is not because it is clear that it is sanitized instead hope and pray
thousands of layers somewhere else did it - for a inline-query the best
solution, if you are using a framework you will never have the insert into
at this place!
what i meant as ugly is that you are somewhere writing an inline-query and
are not sure if it is a number or not - so it is NOT sanitized before
because if you tell me it is you sanitze does not work if you get a
non-integer
at this point and you sanitze-method has to throw the error long before
if it is really working
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
The resulting SQL query is easy to read, and I know that everything is
escaped. No operations are ever to be performed on $M. I need to look
into a way of making it immutable (add and read only). I could do it
with an object but I prefer an array. Actually, an array wrapped in an
object could perform the escaping itself, making me doubly sure that
some other dev didn't forget to escape while playing with the code.
By the way, I've never gotten a godd explanation about why to wrap the
variables in PHP MySQL queries with curly brackets. I don't even
remember where I picked up the habit. Does anybody here know?
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 00:39, schrieb Dotan Cohen:
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com wrote:
Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array(); // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
I'm not sure I'm seeing why, in particular, you are using an array here?
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
signature.asc
Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
I want to be sure that all variables in the query are escaped. I don't trust myself or anyone else to do this to every variable right before the query: $someVar=mysql_real_escape_string($someVar); But you're doing exactly that right before the query anyway with: $M[username]=mysql_real_escape_string($username); You're just complicating things with the addition of an unneeded array. It seems much simpler and less cluttered to just do: $someVar=mysql_real_escape_string($someVar); before your insert. All you are doing is changing $someVar to $M[...] and then using $M[...] in the query. I really don't see the difference or benefit of using your array here. Both methods are doing exactly the same thing, except one is more convoluted. Now on the other hand, if you have several elements in the array $M to be inserted, and have a function like this to escape them all at once: for each ($M as $val) $val= mysql_real_escape_string($val); then your method starts to make more sense. -Hank
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 02:09, Hank hes...@gmail.com wrote:
I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);
But you're doing exactly that right before the query anyway with:
$M[username]=mysql_real_escape_string($username);
You're just complicating things with the addition of an unneeded array. It
seems much simpler and less cluttered to just do:
$someVar=mysql_real_escape_string($someVar);
before your insert. All you are doing is changing $someVar to $M[...]
and then using $M[...] in the query. I really don't see the difference or
benefit of using your array here. Both methods are doing exactly the same
thing, except one is more convoluted.
I know that this has been escaped:
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
This, I don't know if it has been escaped or not:
$query=INSERT INTO table (username) VALUES ('{$username}');
Now on the other hand, if you have several elements in the array $M to be
inserted, and have a function like this to escape them all at once:
for each ($M as $val) $val= mysql_real_escape_string($val);
then your method starts to make more sense.
I could foreach it. Or not. It doesn't matter. The point is having
known-safe variables being used in the query, which are also easy to
read.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote:
i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String
so you never write insert into inline and if the function is well desigend
you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database
having as simple class with $db-fetch_all(), $db-insert, $db-update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code
a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years
public function insert($table, array $data)
{
// so here you know where to look for fieldnames/fieldtypes
// prepare the data aray with escaping/intval()/doubleval()
// and generate finally the insert
//
// as return value use 0 on errors or the insert-id
}
You are right, using a class has many benefits. I might do that on a
future project. Thanks.
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password). -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 01:23, schrieb Dotan Cohen: On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net wrote: i would use a samll class holding the db-connection with insert/update-methods pass the whole record-array, lokk what field types are used in the table and use intval(), doubleval() or mysql_real_escape-String By the way, the database connection is include()ed from a file outside the webroot. This way if Apache is ever compromised or for whatever reason stops parsing the PHP, the resulting code returned to the browser won't have the daabase info (especially the password) if stops parsing - yes, but not relevant if it is in a include if the machine is compromised it does not matter someone could read your files can read also the include outside the docroot signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Personally I don't use any quotes for the numeric types, and single quotes for
everything else. Ie:
UPDATE mytable SET int_field = 5 WHERE id = 3;
SELECT id FROM mytable WHERE int_field = 5;
UPDATE mytable SET varchar_field = 'Test' WHERE id = 3;
SELECT id FROM mytable WHERE varchar_field = 'Test';
UPDATE mytable SET datetime_field = '2011-09-18 00:00:00' WHERE id = 3;
If you are using PHP you may need to escape the single quotes if your php
string is in single quotes:
$query = 'UPDATE mytable SET varchar_field = \'Test\' WHERE id = 3'
But if you are doing interpolation and your string is in double quotes, you
should not need to escape:
$query = UPDATE $table_name SET varchar_field = 'Test' WHERE id = 3
Some people prefer to use back quotes on field names such as:
$query = UPDATE `mytable` SET `varchar_field` = 'Test' WHERE `id` = 3
And some people prefer to put numeric fields in quotes as well, although it is
not necessary:
UPDATE mytable SET int_field = '5' WHERE id = '3';
On 9/18/11 5:00 AM, Dotan Cohen wrote:
I am somewhat confused as to the proper way to place quotes around
arguments in INSERT and SELECT statements. I also don't see where this
is made explicit in the fine manual.
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from PHP?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from PHP?
Is it the same for decimal and float?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from PHP?
Is it the same for text and blob?
Also, in PHP often I see code examples with the variable wrapped in
curly brackets, inside single quotes. What is the purpose of the curly
brackets? Here is such an example:
$query=INSERT INTO names (name) VALUE ('{$userName}');
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from PHP?
What if I am using the NOW() function?
If the column is type set, is it preferable to use single or double
quotes on INSERT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on SELECT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on INSERT from PHP?
If the column is type set, is it preferable to use single or double
quotes on SELECT from PHP?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote: Personally I don't use any quotes for the numeric types, and single quotes for everything else. Ie: Thanks, Brandon. I understand then that quote type is a matter of taste. I always use double quotes in PHP and I've only recently started putting ticks around table and column names. I'll stick to your convention of no quotes around numerics and single quotes around everything else. Have a terrific week! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen dotanco...@gmail.com wrote:
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com wrote:
Personally I don't use any quotes for the numeric types, and single
quotes
for everything else. Ie:
Thanks, Brandon. I understand then that quote type is a matter of
taste. I always use double quotes in PHP and I've only recently
started putting ticks around table and column names. I'll stick to
your convention of no quotes around numerics and single quotes around
everything else.
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:
INSERT into table VALUES (,'');
which would cause a syntax error. If you include the single quotes, it
becomes:
INSERT into table VALUES ('','')
which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 03:00, schrieb Hank:
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:
$sql=INSERT into table VALUES ('$id','$val');
where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error
what ugly style - if it is not numeric and you throw it to the database
you are one of the many with a sql-injection because if you are get
ivalid values until there you have done no sanitize before and do not here
$sql=INSERT into table VALUES ( . (int)$id . ,' .
mysql_real_escape_string($val) . ');
or using a abstraction-layer (simple self written class)
$sql=INSERT into table VALUES ( . (int)$id . ,' . $db-escape_string($val)
. ');
all other things in the context of hand-written queries are all the nice one we
read every
day in the news and should NOT recommended because the next beginner reading
this makes all
the mistakes again
signature.asc
Description: OpenPGP digital signature
Re: Insert blob data using prepared statements
On 7/26/2010 2:30 AM, Manasi Save wrote: Hi All, I need to insert Blob data in my table using prepared statements. But Whenever I try to insert it using prepared statement it is giving me mysql syntax error. Here's the prepared statement :- SET @stmt = Concat(Insert into ',mydb,'.MyTable(MyData, MyID) Select ','',Inputdata,'',',',InputID,';'); Prepare stmt1 From @stmt; Execute stmt1; Deallocate prepare stmt1; The executing statement looks like this :- Insert into `mydb`.MyTable(MyData, MyID) Select ** STREAM DATA **, 1; This gives me an error saying mysql syntax near ** STREAM DATA.. Can anyone give me any example how to insert blob data in database with prepared statement. First, have you tried using INSERT ... VALUES ... instead of INSERT ... SELECT ... ? Second, have you tried passing the STREAM data into the EXECUTE command as a parameter? One of the nice things about prepared statements is their ability to substitute data into the statement at runtime. For example, your statement could be 'INSERT INTO `mydb`.MyTable(MyID, MyDATA) VALUES (?,?)' and your execute could be EXECUTE stmt1 (1, 'stream data'); Depending on how you connect, you may also be able to bind one of those ? parameters to a variable in your code. That would completely eliminate the need to copy and escape your data into a quoted string literal. Third, you must always be aware of the max_allowed_packet size for the connection you are on. If you attempt to send a command larger than that size, the server will forcibly disconnect your session under the impression that you are attempting to sabotage the machine by sending queries that are too large. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Insert blob data using prepared statements
Hi All, I need to insert Blob data in my table using prepared statements. But Whenever I try to insert it using prepared statement it is giving me mysql syntax error. Here's the prepared statement :- SET @stmt = Concat(Insert into ',mydb,'.MyTable(MyData, MyID) Select ','"',Inputdata,'"',',',InputID,';'); Prepare stmt1 From @stmt; Execute stmt1; Deallocate prepare stmt1; The executing statement looks like this :- Insert into `mydb`.MyTable(MyData, MyID) Select ** STREAM DATA **, 1; This gives me an error saying mysql syntax near ** STREAM DATA.. Can anyone give me any example how to insert blob data in database with prepared statement. Thanks in advance. --Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392
Re: Prepared statements and printing Queries
Hello, mysqli_debug will help you, I think. See: http://php.chinaunix.net/manual/zh/function.mysqli-debug.php Joeri On Tue, Oct 20, 2009 at 5:26 PM, Chris W 4rfv...@cox.net wrote: I know this is more a PHP question but no one on the list at news.php.net is responding. So I was hoping someone here might know. If I am using the mysqli extension and prepared statements, after I execute bind_param, is there a away to print the actual query that gets sent to the server? I have looked through the PHP docs and can't seem to find it. Of course I could be blind. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fons...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Prepared statements and printing Queries
I know this is more a PHP question but no one on the list at news.php.net is responding. So I was hoping someone here might know. If I am using the mysqli extension and prepared statements, after I execute bind_param, is there a away to print the actual query that gets sent to the server? I have looked through the PHP docs and can't seem to find it. Of course I could be blind. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.1 Rolling back a transaction containing Create Alter Drop table statements
Hi, Is there any way to rollback a transaction in MySQL 5.1 that contains Create, Alter, Drop, table statements. i.e. is there any way to turn off the feature: Statements That Cause an Implicit Commit or even remove some of the default statements that cause a commit. I tried using the TEMPORARY KEYWORD. It is not exactly what I want. Moreover, the ALTER syntax does not accept a TEMPORARY Keyword, and ALTER a temporary table causes an explicit commit. Any thoughts on this issue, -- Hatem Nassrat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL 5.1 Rolling back a transaction containing Create Alter Drop table statements
On Mon, Mar 16, 2009 at 10:25 AM, Hatem Nassrat hatem.nass...@gmail.com wrote: Hi, Is there any way to rollback a transaction in MySQL 5.1 that contains Create, Alter, Drop, table statements. No. Sorry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How can I disable the app's reuse of prepared statements
Now mysql's version is 5.0.45 Any reply is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Prepared SQL statements - Faster performance?
I can't speak to the exact internals of MySQL, but in database practice one *generally* uses prepared statements for two reasons: 1. Security 2. Speed If your system is at all exposed to the outside world *and you at all value your data*, your biggest concern should /absolutely/ be security. By using prepared statements, you reduce the number of places to secure by letting the database do your escaping for you (e.g. database developer's know better what to escape than you). It's actually better than that, but that's a simple explanation. If you're not exposed to the Internet at large, and speed is your concern, prepared statements can give a speed improvement, but not always. The reason for the alleged speed improvement is that generally one prepares the statement once and then aggregates the cost of preparation over more than one execution. Juxtapose with reparsing and executing for every set of arguments, where the plans are largely the same: the parsing phase is duplicate work. But preparation is not always a win. Say I have a table of stored materials. Picking the perfect plan is highly dependent on what data I have, and what data I want. Take this query as an example PREPARE( SELECT material FROM stock WHERE mass = ? ); Presumably, just having an index on 'mass' will make things faster, right? That makes an assumption that I have an evenly distributed set of data. What if 5 billion items in my warehouse are 5kg, and 3 items are 10kg? If I plug in 5kg, my indexes are useless, and are in fact a loss to use. If I plug in 10kg, my indexes are a huge gain. Without knowing before hand what data I'll need, the planner will likely make a poor decision in favor of the best general decision. To answer what you can expect from planning: YMMV. I have had code bases improve from 90 minutes to 5 minutes on the same hardware. I have also had code bases show decreased performance by implementing planning. As the previous poster said, the only way you'll know what *your* speed gain/loss will be, is to do it and find out. You may be surprised. Kevin At 10:56p -0500 on 15 Jan 2008, Moon's Father wrote: To know the exact speed improvement ,you have to have a test yourself append on your hardware mathine. On Jan 15, 2008 11:39 PM, mos [EMAIL PROTECTED] wrote: At 11:25 AM 1/14/2008, Jay Pipes wrote: Are you using the PREPARE STATEMENT server-side syntax or an emulated prepared statement like in PDO? -jay Jay, Currently I'm not using prepared statements at all. Before I switch, I wanted to know how much of a speed improvement I can expect, if any. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared SQL statements - Faster performance?
At 11:25 AM 1/14/2008, Jay Pipes wrote: Are you using the PREPARE STATEMENT server-side syntax or an emulated prepared statement like in PDO? -jay Jay, Currently I'm not using prepared statements at all. Before I switch, I wanted to know how much of a speed improvement I can expect, if any. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared SQL statements - Faster performance?
To know the exact speed improvement ,you have to have a test yourself append on your hardware mathine. On Jan 15, 2008 11:39 PM, mos [EMAIL PROTECTED] wrote: At 11:25 AM 1/14/2008, Jay Pipes wrote: Are you using the PREPARE STATEMENT server-side syntax or an emulated prepared statement like in PDO? -jay Jay, Currently I'm not using prepared statements at all. Before I switch, I wanted to know how much of a speed improvement I can expect, if any. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Prepared SQL statements - Faster performance?
I would like to speed up my Select queries since I'm executing approx 5,000 of them, same syntax but the search values for 2 columns will change with each query. Will I see any performance increase if I prepare the statement and use parameters? (I don't need to use the query cache since the result set will be returned only once for each set of search values.) TIA Mike MySQL 5.024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared SQL statements - Faster performance?
Hi, Server parses the statement only once and executes them with the small difference. It reduces parsing time. Clients don't need to send the full statement again and again. It reduces traffic. Saravanan --- On Mon, 1/14/08, mos [EMAIL PROTECTED] wrote: From: mos [EMAIL PROTECTED] Subject: Prepared SQL statements - Faster performance? To: mysql@lists.mysql.com Date: Monday, January 14, 2008, 11:17 PM I would like to speed up my Select queries since I'm executing approx 5,000 of them, same syntax but the search values for 2 columns will change with each query. Will I see any performance increase if I prepare the statement and use parameters? (I don't need to use the query cache since the result set will be returned only once for each set of search values.) TIA Mike MySQL 5.024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared SQL statements - Faster performance?
Are you using the PREPARE STATEMENT server-side syntax or an emulated prepared statement like in PDO? -jay mos wrote: I would like to speed up my Select queries since I'm executing approx 5,000 of them, same syntax but the search values for 2 columns will change with each query. Will I see any performance increase if I prepare the statement and use parameters? (I don't need to use the query cache since the result set will be returned only once for each set of search values.) TIA Mike MySQL 5.024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared SQL statements - Faster performance?
At 11:11 AM 1/14/2008, Saravanan wrote: Hi, Server parses the statement only once and executes them with the small difference. It reduces parsing time. Clients don't need to send the full statement again and again. It reduces traffic. Saravanan Saravanan, Thanks for your quick reply. In theory using parameters seems like a good idea. But has anyone done any benchmarking to say it is 25% faster? 10% faster? Or is it slower because MySQL can't optimize the query as well? Most people probably use parameters to prevent SQL injection, but I'm not worried about that because there is no user input. So have you (or anyone else) noticed a significant speed improvement using parameters with MySQL 5? TIA Mike --- On Mon, 1/14/08, mos [EMAIL PROTECTED] wrote: From: mos [EMAIL PROTECTED] Subject: Prepared SQL statements - Faster performance? To: mysql@lists.mysql.com Date: Monday, January 14, 2008, 11:17 PM I would like to speed up my Select queries since I'm executing approx 5,000 of them, same syntax but the search values for 2 columns will change with each query. Will I see any performance increase if I prepare the statement and use parameters? (I don't need to use the query cache since the result set will be returned only once for each set of search values.) TIA Mike MySQL 5.024 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs -- 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: Debugging batch sql statements
Arun wrote: I have a large batch of insert sql statements, of which some of them are failing. I want to know which one is failing exactly. Is there anyway I can debug it and which statement went wrong. I have only 4 or 5 out of 200 statements that are failing. It depends on how you're running the batch, and you didn't give us any information about that. Are you doing something like this? mysql batch.sql If so, try mysql --line-numbers batch.sql This option may be enabled already. Try running mysql --help | grep 'line-numbers' to see. It's enabled by default on my machine: [EMAIL PROTECTED]:~$ mysql --help | grep 'line-numbers' --line-numbers Write line numbers for errors. -L, --skip-line-numbers line-numbers TRUE Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Debugging batch sql statements
I have a large batch of insert sql statements, of which some of them are failing. I want to know which one is failing exactly. Is there anyway I can debug it and which statement went wrong. I have only 4 or 5 out of 200 statements that are failing. -- Thanks Arun George
Re: Debugging batch sql statements
Baron, I am sourcing it. I do not know if that is the best way though.. I am running the insert statement from a mysql prompt in a linux/windows machine. My script has a single build script which sources other scripts. build.sql == source ./ddl/useraccount/useraccountddl.sql source ./mobchannel_db.sql source ./ddl/usercontent/usercontentddl.sql source ./ddl/userinfo/profilesddl.sql source ./ddl/system/systemddl.sql source ./ddl/shopping/retailitem.sql source ./ddl/shopping/shoppingddl.sql source ./ddl/rewards/userrewardsddl.sql source ./ddl/location/locationddl.sql source ./ddl/listing/listingddl.sql === I will cd into the directory which contains the sql (so that '.' gets resolved) and do a mysql -u root -p and enter password and do a mysqluse dbname; mysqlsource build.sql; ERROR 1064 (42000): 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 ')' at line 8. But I do not know which sql does that, there are so many. I cannot scroll the putty all the way also. I need to know other errors that might occur. Is there any command by which I can see last run errors, like some errors that are saved in the buffer. As I know this is the last sql statement that gets executed. On 10/5/07, Baron Schwartz [EMAIL PROTECTED] wrote: Arun wrote: I have a large batch of insert sql statements, of which some of them are failing. I want to know which one is failing exactly. Is there anyway I can debug it and which statement went wrong. I have only 4 or 5 out of 200 statements that are failing. It depends on how you're running the batch, and you didn't give us any information about that. Are you doing something like this? mysql batch.sql If so, try mysql --line-numbers batch.sql This option may be enabled already. Try running mysql --help | grep 'line-numbers' to see. It's enabled by default on my machine: [EMAIL PROTECTED]:~$ mysql --help | grep 'line-numbers' --line-numbers Write line numbers for errors. -L, --skip-line-numbers line-numbers TRUE Baron -- Thanks Arun George
Re: Debugging batch sql statements
On Friday 05 October 2007 16:50, Arun wrote: Baron, I am sourcing it. I do not know if that is the best way though.. I am running the insert statement from a mysql prompt in a linux/windows machine. My script has a single build script which sources other scripts. build.sql == source ./ddl/useraccount/useraccountddl.sql source ./mobchannel_db.sql source ./ddl/usercontent/usercontentddl.sql source ./ddl/userinfo/profilesddl.sql source ./ddl/system/systemddl.sql source ./ddl/shopping/retailitem.sql source ./ddl/shopping/shoppingddl.sql source ./ddl/rewards/userrewardsddl.sql source ./ddl/location/locationddl.sql source ./ddl/listing/listingddl.sql === I will cd into the directory which contains the sql (so that '.' gets resolved) and do a mysql -u root -p and enter password and do a mysqluse dbname; mysqlsource build.sql; ERROR 1064 (42000): 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 ')' at line 8. foreach source do source file fix any error until no errors -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debugging batch sql statements
I just want to see the original insert statement which failed, so that I can do a search on it. Is there any option for seeing the insert statement that gets executed and the error line next to next.? On 10/5/07, Baron Schwartz [EMAIL PROTECTED] wrote: Arun wrote: I have a large batch of insert sql statements, of which some of them are failing. I want to know which one is failing exactly. Is there anyway I can debug it and which statement went wrong. I have only 4 or 5 out of 200 statements that are failing. It depends on how you're running the batch, and you didn't give us any information about that. Are you doing something like this? mysql batch.sql If so, try mysql --line-numbers batch.sql This option may be enabled already. Try running mysql --help | grep 'line-numbers' to see. It's enabled by default on my machine: [EMAIL PROTECTED]:~$ mysql --help | grep 'line-numbers' --line-numbers Write line numbers for errors. -L, --skip-line-numbers line-numbers TRUE Baron -- Thanks Arun George
How to trace statements executed in a procedure called by a client?
Hi, First of all, I would like to apologize if I didn't send my message to the right mailing list. My question is related to MySQL administration, where I don't have any particular experience. I would to trace every SQL statement executed inside a procedure that is called by a client: CREATE PROCEDURE foo() NOT DETERMINISTIC MODIFIES SQL DATA COMMENT 'Generate the required number of random battles' BEGIN statement 1 statement 2 statement 3 END; The problem is that the mysql-slow.log file only contains a trace of the call to the procedure; it does not provide any trace of every subsequent statement executed by the procedure itself: # Time: 070607 1:23:04 # [EMAIL PROTECTED]: dbo[dbo] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 2 SET last_insert_id=27274; CALL foo(); Is there any way to configure mysqld to trace every statement executed by MySQL? For information, my current configuration is the following: [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english long_query_time = 1 log-queries-not-using-indexes skip-external-locking verbose log_bin_trust_function_creators = 1 Thanks a lot! -- Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Triggers to Maintain a Table to prevent complex join statements...
Cory Robin wrote: We have a statement that joins 8 different tables to pull multiple rows from each table. I have heard of people using triggers to create, update and delete records in a table based on statements in other tables. The obvious result of this would be to SPEED up results right? :) I'd love to find someone with experience with this that I can pick info from. lol Post the SQL you are using and I'll give you a hand. Cheers! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Triggers to Maintain a Table to prevent complex join statements...
We have a statement that joins 8 different tables to pull multiple rows from each table. I have heard of people using triggers to create, update and delete records in a table based on statements in other tables. The obvious result of this would be to SPEED up results right? :) I'd love to find someone with experience with this that I can pick info from. lol There isn't really a 'best practices' guide that I can find beyond what is in the normal documentation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
improve performance for query statements with LIKE keyword
Hi all, Is there some way to improve performance for query statments with LIKE keyword ? like this: mysql select id,name,title,description from books where description like '%some_strings%'; Note: Above some_strings is CJK(Chinese or Japanese or Korean). I know MySQL cann't support CJK fulltext index type. Thanks! Best Regards, Liu Xian 2007-02-07 @@@^_^@@@ - Mp3疯狂搜-新歌热歌高速下
Re: result set on prepared statements
Hello ViSolve DB Team, thank you for response. I guess I didn't write clearly enough what information I need: general usage of prepared statements I already know. The question right now is, if I get a result set containing several rows, must I fetch *all* of them, if I don't use client side cursor. Because when using classic functions instead of prepared statements, I really must fetch *all* rows to avoid memory leak on database engine. With best regards, Roland. Visolve DB Team schrieb am 17.10.2006 07:57: Hi, Hope this link will be useful: http://mirrors.sunsite.dk/mysql/doc/refman/5.1/en/c-api-prepared-statement-functions.html Thanks ViSolve DB Team - Original Message - From: Roland Volkmann [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 15, 2006 5:59 PM Subject: result set on prepared statements Hello all, using MySQL C API function |mysql_query() with a query producing a result set, I have to fetch *all* records, if I use ||mysql_use_result() to avoid a client side cursor. So it's written in the manual section |22.2.3.70. But if I want to use prepared statements with MySQL C API functions |mysql_stmt_prepare(), ||mysql_stmt_execute() and ||mysql_stmt_fetch(), I can't find anything in the manual, whether I also have to fetch *all* records, when not using ||mysql_stmt_store_result() (I don't want to use client side cursor). MySQL Version is 5.0.26 on Windows 32 Bit. Any Information is welcome. With best regards, Roland. | -- 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: result set on prepared statements
Hi, Hope this link will be useful: http://mirrors.sunsite.dk/mysql/doc/refman/5.1/en/c-api-prepared-statement-functions.html Thanks ViSolve DB Team - Original Message - From: Roland Volkmann [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, October 15, 2006 5:59 PM Subject: result set on prepared statements Hello all, using MySQL C API function |mysql_query() with a query producing a result set, I have to fetch *all* records, if I use ||mysql_use_result() to avoid a client side cursor. So it's written in the manual section |22.2.3.70. But if I want to use prepared statements with MySQL C API functions |mysql_stmt_prepare(), ||mysql_stmt_execute() and ||mysql_stmt_fetch(), I can't find anything in the manual, whether I also have to fetch *all* records, when not using ||mysql_stmt_store_result() (I don't want to use client side cursor). MySQL Version is 5.0.26 on Windows 32 Bit. Any Information is welcome. With best regards, Roland. | -- 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]
result set on prepared statements
Hello all, using MySQL C API function |mysql_query() with a query producing a result set, I have to fetch *all* records, if I use ||mysql_use_result() to avoid a client side cursor. So it's written in the manual section |22.2.3.70. But if I want to use prepared statements with MySQL C API functions |mysql_stmt_prepare(), ||mysql_stmt_execute() and ||mysql_stmt_fetch(), I can't find anything in the manual, whether I also have to fetch *all* records, when not using ||mysql_stmt_store_result() (I don't want to use client side cursor). MySQL Version is 5.0.26 on Windows 32 Bit. Any Information is welcome. With best regards, Roland. | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enums vs prepared statements
Hi,
I try to read enums from a database through the prepared statement API, as a
number.
The enum is defined e.g., as ENUM('Accept', 'Reject', 'Reject_All')
The bind structure is filled in the following way:
buffer_type = MYSQL_TYPE_LONG
buffer points to an uint32_t
buffer_length = 4
length points to an unsigned long
is_null points to a bool
is_unsigned = true
error points to a my_bool
After calling mysql_stmt_fetch() I receive MYSQL_DATA_TRUNCATED, error is
set to 1, and length is set to 4.
Could somebody explain what am I doing wrong? Inserts seem to work woth the
same setup properly, only selects have this problem.
Thx.
ImRe
P.S.: version = 5.0.22
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow log logs non-slow statements
I have specified log-slow-queries long-query-time=10 in my.cnf and restarted my server. After that I see statements logged into the slow-log-file. But it seems that mysql logs too much into that file. When executing this statement: mysql show variables like %tx%; +---+-+ | Variable_name | Value | +---+-+ | tx_isolation | REPEATABLE-READ | +---+-+ 1 row in set (0,00 sec) it immediately shows up in the slow-log: # Time: 060815 14:40:22 # [EMAIL PROTECTED]: root[root] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1 show variables like %tx%; This is also true vor simple select statements which give a result in (0,00 sec). How can I make mysql log only those slow queries, that are really slow. Thanks in advance Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow log logs non-slow statements
It is likely you are also logging any queries not using an index (doing full table scans). Check the configuration variable: log_long_format Cheers, Jay On Tue, 2006-08-15 at 14:45 +0200, Dominik Klein wrote: I have specified log-slow-queries long-query-time=10 in my.cnf and restarted my server. After that I see statements logged into the slow-log-file. But it seems that mysql logs too much into that file. When executing this statement: mysql show variables like %tx%; +---+-+ | Variable_name | Value | +---+-+ | tx_isolation | REPEATABLE-READ | +---+-+ 1 row in set (0,00 sec) it immediately shows up in the slow-log: # Time: 060815 14:40:22 # [EMAIL PROTECTED]: root[root] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1 show variables like %tx%; This is also true vor simple select statements which give a result in (0,00 sec). How can I make mysql log only those slow queries, that are really slow. Thanks in advance Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lazy and greedy control statements
Hey list; I have a case where I need to fetch a product row from a table containing price information about some products, and where every product can have multiple rows but from different suppliers (thus with different prices and stock information). However, I'm trying to create a query which fetches the row matching a product from the supplier with the best price, but where the stock is 0. If none of the suppliers has the item in stock, it should get the row with the highest price of them all. Is this possible with a SQL query, or do I need to fetch all rows for the product and then process it with my programming language of choice? :-) Best Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lazy and greedy control statements
how about something like this, which finds one row (if any) with the
lowest in-stock price, UNIONs it with the highest out-of-stock price (if
any), and then returns just one row, including whether that was an
in-stock or out-of-stock price:
(SELECT col1, price, quantityOnHand, 1 as inStock
FROM products
WHERE col1 = 'Widget3'
AND products.quantityOnHand 0
ORDER BY products.price asc
LIMIT 1)
UNION
(SELECT col1, price, quantityOnHand, 0 as inStock
FROM products
WHERE col1 = 'Widget3'
AND products.quantityOnHand = 0
ORDER BY products.price desc
LIMIT 1)
ORDER BY inStock desc
LIMIT 1
Here's some data to test with:
CREATE TABLE products (
productsID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 char(10),
price FLOAT NOT NULL DEFAULT 0,
quantityOnHand INT NOT NULL DEFAULT 0 );
INSERT INTO products (col1, price, quantityOnHand)
VALUES ('Widget1', '9.99', 1),
('Widget1', '10.99', 1),
('Widget1', '11.99', 1),
('Widget2', '3.99', 1),
('Widget2', '3.99', 1),
('Widget3', '19.99', 0),
('Widget3', '29.99', 0),
('Widget3', '39.99', 0),
('Widget3', '14.99', 1);
Obviously your structure will be more complex as you'll need to join on
a vendor/supplier table and possibly others.
Kim Christensen wrote:
Hey list;
I have a case where I need to fetch a product row from a table
containing price information about some products, and where every
product can have multiple rows but from different suppliers (thus with
different prices and stock information).
However, I'm trying to create a query which fetches the row matching a
product from the supplier with the best price, but where the stock is
0. If none of the suppliers has the item in stock, it should get the
row with the highest price of them all.
Is this possible with a SQL query, or do I need to fetch all rows for
the product and then process it with my programming language of
choice? :-)
Best Regards
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQLDUMP uses database name in the SQL statements?????!!!!!!!!!!!
I've checked the dump file I created with mysqldump on a database sfg, and it has statements like the following: DROP VIEW IF EXISTS `advAPI`; CREATE ALGORITHM=UNDEFINED VIEW `sfg`.`advAPI` AS select `sfg`.`advertiser`.`advertiserid` AS `advertiserid`,count(`sfg`.`cust`.`custid`) AS `api` from (`sfg`.`advertiser` left join `sfg`.`cust` on((`sfg`.`cust`.`advertiserid` = `sfg`.`advertiser`.`advertiserid`))) where (`sfg`.`cust`.`statusCurrent` in (20,21,22,23)) group by `sfg`.`advertiser`.`advertiserid`; Now if I want to import this dumpfile into another database sfgbackup, it won't work because the sql is referring to sfg database. I think this is the reason why I'm facing the problems (sorry for multiple threads). Can anyone confirm my suspicion and suggest a fix? I'm thinking manually changing all the entries. Is that the only way? Thanks for your help Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: MYSQLDUMP uses database name in the SQL statements?????!!!!!!!!!!!
On 6/8/06, murthy gandikota [EMAIL PROTECTED] wrote: I've checked the dump file I created with mysqldump on a database sfg, and it has statements like the following: DROP VIEW IF EXISTS `advAPI`; CREATE ALGORITHM=UNDEFINED VIEW `sfg`.`advAPI` AS select `sfg`.`advertiser`.`advertiserid` AS `advertiserid`,count(`sfg`.`cust`.`custid`) AS `api` from (`sfg`.`advertiser` left join `sfg`.`cust` on((`sfg`.`cust`.`advertiserid` = `sfg`.`advertiser`.`advertiserid`))) where (`sfg`.`cust`.`statusCurrent` in (20,21,22,23)) group by `sfg`.`advertiser`.`advertiserid`; Now if I want to import this dumpfile into another database sfgbackup, it won't work because the sql is referring to sfg database. I think this is the reason why I'm facing the problems (sorry for multiple threads). Can anyone confirm my suspicion and suggest a fix? I'm thinking manually changing all the entries. Is that the only way? Thanks for your help Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Your problem has to do with a DB rename ...read on ... The easiest way to rename a database as far I know is 1) shutdown Mysql 2) Go to your data directory, 3) make a recursive copy (with permissions intact) of database sfg, call it sfgtemp 4) rename the newly copied directory sfgtemp to sfgbackup 5) restart mysql 6) do some GRANT etc to make sure that the users have proper access to the new database sfgdump... 7) now you have a new database sfgdump !!!if thats not what you want then just repeat your dump and restore on sfgdump freshly instead of sfg Give this a try and see how it goes Kishore Jalleda http://kjalleda.googlepages.com/projects
Help With Insert Statements
Hello, I am working through ProSpring book, in which they are using Postgres. I need to convert the following inserts into Mysql 5. The table 'Test' , 'Customer' work fine, but 'CustomerAddresses' blows up on the delete cascade portion. As I was trying to figure out the error, another question was posed. The 'Constraint'. Doesn't that just tell the column that the values within the table must be unique? Or does it create an index? I have always been confused on that. Thanks, Scott drop table Test; drop table CustomerPermissions; drop table CustomerAddresses; drop table Permissions; drop table Customers; create table Test ( TestId serial not null, Name varchar(50) not null, RunDate timestamp not null, constraint PK_TestId primary key (TestId) ); create table Customers ( CustomerId serial not null, FirstName varchar(50) not null, LastName varchar(50) not null, constraint PK_CustomerId primary key (CustomerId) ); create table CustomerAddresses ( CustomerAddressId serial not null, Customer int not null, Line1 varchar(50) not null, Line2 varchar(50) not null, City varchar(50) not null, PostCode varchar(50) not null, constraint PK_CustomerAddressId primary key (CustomerAddressId), constraint FK_Customer foreign key (Customer) references Customers (CustomerId) on delete cascade on update cascade );
Re: compress/uncompress using java prepared statements
Hi, I am a bit confused by the below, hope I can get some clarification. In the database (mysql), I have image data stored in a blob. I can perform operations like: COMPRESS and UNCOMPRESS on it and the data is fine. Let's say I COMPRESS (ed) the data and then retrieve it use a SELECT UNCOMPRESS(IMAGE_DATA) FROM table, when I get this using jdbc ResultSetMetaData rsmd = rs.getMetaData(); System.out.println(SHOW THE COLUMN TYPE: + rsmd.getColumnType(1)); I am getting the Column type as VARCHARhttp://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html#VARCHAR, whereas if I don't compress the data and do a straight SELECT IMAGE_DATA ..., the resulting Column Type is a LONGVARBINARYhttp://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html#LONGVARBINARY . Can somebody explain the above? Why does this work fine in the database but I am not able to select the data into java properly? How could I conver it into a proper format? thanks, Ramesh On 4/19/06, Ramesh V [EMAIL PROTECTED] wrote: Hello, My configuration; mysql-connector-java-3.1.12-bin.jar mysql5018 I am trying to get uncompressed data from mysql using: SELECT UNCOMPRESS(FILE_DATA) FROM table WHERE id=11; I am using PreparedStatement, I get the result set and do: rs.getBytes(1); For some reason, the UNCOMPRESS doesn't seem to be working. I also tried: SELECT UNCOMPRESS(COMPRESS(FILE_DATA)) FROM table WHERE id=11; The resulting byte[] array seems to be corrupt for some reason. When I manually UNCOMPRESS the data using the UPDATE stmt in the database and then do: SELECT FILE_DATA FROM table.via java, it works fine. (Please note: data is not corrupt as I can uncompress it in the db and retrieve it) Does it have to be done differently? Appreciate any suggestions. thanks, Ramesh
on select statements
what is the difference between this: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a; and this: 'SELECT * FROM tbl LIMIT 1 And then this: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; and this: 'SELECT * FROM tbl LIMIT 1,5
Re: on select statements
There is no logical difference. There's a tiny bit of overhead in preparing and executing the statements, but if you have a query where the limit is variable, a prepared statement might be better than coding within an application, because then when ANY user or application connects it can use that prepared statement. -Sheeri On 4/3/06, Yemi Obembe [EMAIL PROTECTED] wrote: what is the difference between this: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';EXECUTE STMT USING @a; and this: 'SELECT * FROM tbl LIMIT 1 And then this: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; and this: 'SELECT * FROM tbl LIMIT 1,5 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update statements problem
I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks!
Re: update statements problem
Hi, I think your problem is that OR and AND do not have the same precedence, AND binds tighter. So what you need is probably: (products_model=5217-01 OR products_model=5217-01) AND products_um=CS and the same for PK and EA. The way you have you'll get an update as soon as products_model=5217-01 /Johan cybermalandro cybermalandro wrote: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 2006-03-17 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update statements problem
cybermalandro cybermalandro [EMAIL PROTECTED] wrote on 03/20/2006 11:00:51 AM: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! It has to do with the expression you are using to pick which row to update WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS This is parsed as WHERE products_model=5217-01 OR (products_model=521701 AND products_um=CS) But what I think you wanted to say was WHERE (products_model=5217-01 OR products_model=521701) AND products_um=CS Add the parentheses around your OR terms and you should only be changing what you wanted to change. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: update statements problem
Hey guys! thanks a bunch that really fixed my problem. Thanks for your prompt response. cybm On 3/20/06, Johan Höök [EMAIL PROTECTED] wrote: Hi, I think your problem is that OR and AND do not have the same precedence, AND binds tighter. So what you need is probably: (products_model=5217-01 OR products_model=5217-01) AND products_um=CS and the same for PK and EA. The way you have you'll get an update as soon as products_model=5217-01 /Johan cybermalandro cybermalandro wrote: I am trying to update a table with a file that has more than one update statements like this: UPDATE products set products_price=22.00 WHERE products_model=5217-01 OR products_model=521701 AND products_um=CS; UPDATE products set products_price=3 WHERE products_model=5217-01 OR products_model=521701 AND products_um=PK; UPDATE products set products_price=0.25 WHERE products_model=5217-01 OR products_model=521701 AND products_um=EA; In the products table the only record that exist with product_model=5217-01 has a products_um=CS not EA but when my which contains the update statements is executed the last statement is the one that actually makes the change therefore resulting in the record to be products_price=0.25 instead of 22. Any ideas why this is happening? Shouldn't this statements just match the record and make the update? is there another way to do this? Thanks! No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 2006-03-17
Re: showing zero counts in select statements with group by
Sorry, two errors, should be ... ...) OR m.repyear IS NULL GROUP BY c.year PB - Chris Fonnesbeck wrote: On 2/17/06, Peter Brawley [EMAIL PROTECTED] wrote: Chris, Your WHERE clause is weeding out the NULL joined entries. Try something like... select m.repyear as repyear, count(*) as count from years y left join mortality m on y.year=m.repyear where ( m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; Thanks for the recommendation; unfortunately, it does not work: mysql select - m.repyear as repyear, - count(*) as count - from years y - left join mortality m on y.year=m.repyear - where ( - m.region like 'Southwest' - and m.repyear1985 - and m.dthcode!=4 - and (m.cause like '%red tide%' or m.remarks like '%red tide%') - ) OR m.repyear IS NULL - group by m.repyear; +-+---+ | repyear | count | +-+---+ |1994 | 1 | |1996 | 145 | |1997 |15 | |1999 |12 | |2000 |14 | |2001 |16 | |2002 |36 | |2003 |91 | |2004 | 5 | |2005 |52 | +-+---+ 10 rows in set (52.92 sec) Thanks, C. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
showing zero counts in select statements with group by
I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count: mysql select count(*) as count, repyear from mortality where region like 'Southwest' and repyear1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: mysql select * from years; +--+ | year | +--+ | 1986 | | 1987 | | 1988 | | 1989 | | 1990 | | 1991 | | 1992 | | 1993 | | 1994 | | 1995 | | 1996 | | 1997 | | 1998 | | 1999 | | 2000 | | 2001 | | 2002 | | 2003 | | 2004 | | 2005 | +--+ 20 rows in set (0.00 sec) However, running a left joined query using this table gives the same result: mysql select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.12 sec) So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. Any ideas most welcome. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: showing zero counts in select statements with group by
Chris, Your WHERE clause is weeding out the NULL joined entries. Try something like... select m.repyear as repyear, count(*) as count from years y left join mortality m on y.year=m.repyear where ( m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') ) OR m.year IS NULL group by m.repyear; PB - Chris Fonnesbeck wrote: I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count: mysql select count(*) as count, repyear from mortality where region like 'Southwest' and repyear1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: mysql select * from years; +--+ | year | +--+ | 1986 | | 1987 | | 1988 | | 1989 | | 1990 | | 1991 | | 1992 | | 1993 | | 1994 | | 1995 | | 1996 | | 1997 | | 1998 | | 1999 | | 2000 | | 2001 | | 2002 | | 2003 | | 2004 | | 2005 | +--+ 20 rows in set (0.00 sec) However, running a left joined query using this table gives the same result: mysql select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.12 sec) So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. Any ideas most welcome. -- Chris Fonnesbeck + Atlanta, GA + http://trichech.us -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.10/263 - Release Date: 2/16/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: showing zero counts in select statements with group by
In the last episode (Feb 17), Chris Fonnesbeck said: I am trying to figure out how to get a comprehensive count of rows in a database by year. Unfortunately, when there are no rows for a particular year, the year is omitted from the result set, rather than showing up with a zero count: mysql select count(*) as count, repyear from mortality where region like 'Southwest' and repyear1985 and dthcode!=4 and (cause like '%red tide%' or remarks like '%red tide%') group by repyear; +---+-+ | count | repyear | +---+-+ | 1 |1994 | | 145 |1996 | |15 |1997 | |12 |1999 | |14 |2000 | |16 |2001 | |36 |2002 | |91 |2003 | | 5 |2004 | |52 |2005 | +---+-+ 10 rows in set (0.09 sec) Looking at some online help, I understand that I am supposed to create a table with the years as entries, then run a left join with the same query in order to get my zeroes included. I went ahead and did this: [...] However, running a left joined query using this table gives the same result: mysql select count(*) as count, m.repyear as repyear from years y left join mortality m on y.year=m.repyear where m.region like 'Southwest' and m.repyear1985 and m.dthcode!=4 and (m.cause like '%red tide%' or m.remarks like '%red tide%') group by m.repyear; You probably want to group by y.year here, since for any year not in your mortality table, m.year will be null (thus grouping all your unused years together). Also, select count(m.repyear) (or any other field in m) instead of count(*), since * includes nulls (and would cause all your unused years to have a count of 1. If you remove the group by clause and look at the raw table generated by the left join it may make more sense. So, I'm at a loss as to how to get a complete result set. I do find it a bit strange that sql returns an incomplete query as a default. There's no way for mysql to have guessed that you wanted to see nonexistent values in your resultset. Since the query is only looking at a subset of the table, how would you even know which values were missing? Should it extend that number past 2005 to whatever the field's maximum value is? I assume it's just an integer field, so mysql couldn't have known it was a field storing a number known to be between 1986 and 2006. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API: binding parameters to prepared statements
Hi, I am trying to convert a Oracle 7 interface (from our Constraint Logic Programming Platform ECLiPSe) to MySQL. [I guess I should also say that I don't really have any experience with programming any database API previously, so please excuse me if this seems to be a basic question] The interface includes the feature of making multiple executions of prepared statements, binding the parameters to different values before each execution. In the Oracle code, obndra() was used to bind the parameters, and I have replaced this with mysql_stmt_bind_param(). However, I noticed that there might be an important difference -- obndra() binds the *address* of data buffers for the parameters, i.e. after the call, the different paramter values for each execution are loaded into these data buffers, and then executed, without calling obndra() again. From reading the MySQL 5.0 manual, it seems that mysql_stmt_bind_param() is used to bind the *values* of parameter data for a prepared statements, i.e. I need to call mysql_stmt_bind_param() again before executing the statement again, rather than just loading different values into the buffers (i.e. buffer in MYSQL_BIND structure). Am I reading the documentation correctly, i.e. I can't simply replace obndra() with mysql_stmt_bind_param()? Thanks in advance for any help and information! Cheers, Kish Shen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collect SQL Statements
Moeller, Thorsten, AO wrote: Hi, is there a possibility to collect the sql statements issued to a mysql db to analyse them?? perhaps there is an extra tool or script for this?? Thanks for any suggestions! I don't know about collecting all queries, but we're doing this in our my.cnf file: # install slow query log long_query_time = 10 log_slow_queries = /var/log/httpd/mysql_slow_query.log On a server running 4.0.20 It's not a box I set up, so I don't know if you have to do anything special to get it working, but I've noticed that it's there, and is working. Might be what you're looking for. Yani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collect SQL Statements
Hi, is there a possibility to collect the sql statements issued to a mysql db to analyse them?? perhaps there is an extra tool or script for this?? Thanks for any suggestions!
RE: Collect SQL Statements
Hello, MySQL has a few logs that can assist you in capturing statements: 1. The query log shows client connections and executed statements 2. The binary log shows all statements that change data (also used for replication) 3. The slow log shows all queries that took more than long_query_time seconds to execute or didn't use indexes See http://dev.mysql.com/doc/refman/5.0/en/log-files.html for more info on setting up and analyzing these logs. Thanks, Jimmy Guerrero, Senior Product Manager MySQL Inc, www.mysql.com Houston, TX USA -Original Message- From: Moeller, Thorsten, AO [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 25, 2006 9:15 AM To: mysql@lists.mysql.com Subject: Collect SQL Statements Hi, is there a possibility to collect the sql statements issued to a mysql db to analyse them?? perhaps there is an extra tool or script for this?? Thanks for any suggestions! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collect SQL Statements
Hi, is there a possibility to collect the sql statements issued to a mysql db to analyse them?? perhaps there is an extra tool or script for this?? Thanks for any suggestions!
Procedure / Prepared statements error converting table
I tried using prepared statements to make a procedure that converts
every table in a schema from ARCHIVE to MyISAM (there's about three
dozen archive tables here). But for some odd reason it will give an
error after doing the first table, and abort. The really odd thing is
that the table it gives the error on does successfully get converted.
Any thoughts? Is there a better way to do this? Or am I just doing
something stupid?
This is the error directly below... and the full procedure below that.
ERROR 7 (HY000): Error on rename of './CANN_ARCH/#sql-27b4_275.ARN' to
'./CANN_ARCH/WEBLOG_2004.ARN' (Errcode: 2)
drop procedure if exists CONVERT_TABLES;
delimiter //
create procedure CONVERT_TABLES()
begin
DECLARE t char(255);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR for SELECT TABLE_NAME from
information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and ENGINE
= 'MyISAM';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t;
IF NOT done THEN
set @x = CONCAT('alter table ', t, ' engine =
myisam');
prepare z from @x;
execute z;
deallocate prepare z;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END; //
delimiter ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Procedure / Prepared statements error converting table
Burke, Dan [EMAIL PROTECTED] wrote on 01/18/2006 02:05:24 PM:
I tried using prepared statements to make a procedure that converts
every table in a schema from ARCHIVE to MyISAM (there's about three
dozen archive tables here). But for some odd reason it will give an
error after doing the first table, and abort. The really odd thing is
that the table it gives the error on does successfully get converted.
Any thoughts? Is there a better way to do this? Or am I just doing
something stupid?
This is the error directly below... and the full procedure below that.
ERROR 7 (HY000): Error on rename of './CANN_ARCH/#sql-27b4_275.ARN' to
'./CANN_ARCH/WEBLOG_2004.ARN' (Errcode: 2)
drop procedure if exists CONVERT_TABLES;
delimiter //
create procedure CONVERT_TABLES()
begin
DECLARE t char(255);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR for SELECT TABLE_NAME from
information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and ENGINE
= 'MyISAM';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t;
IF NOT done THEN
set @x = CONCAT('alter table ', t, ' engine =
myisam');
prepare z from @x;
execute z;
deallocate prepare z;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END; //
delimiter ;
Maybe you described it backwards? According to your cursor definition,
you are looking for tables of type MyISAM. Perhaps your cursor should be
looking for ENGINE='Archive' ?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Procedure / Prepared statements error converting table
Oh, I thought I had rolled all the changes back, I guess I missed that
line before posting. I had tried to see if it was because the tables
were ARCHIVE tables to being with, so I tried in another DB to convert a
bunch of tables the other way and got the same error. Either way, I'm
getting an error :-(
Correct procedure I'm trying to get working:
drop procedure if exists CONVERT_TABLES;
delimiter //
create procedure CONVERT_TABLES()
begin
DECLARE t char(255);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR for SELECT TABLE_NAME from
information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and ENGINE
= 'ARCHIVE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t;
IF NOT done THEN
set @x = CONCAT('alter table ', t, ' engine =
myisam ');
prepare z from @x;
execute z;
deallocate prepare z;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END; //
delimiter ;
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 18, 2006 2:16 PM
To: Burke, Dan
Cc: mysql@lists.mysql.com
Subject: Re: Procedure / Prepared statements error converting table
Burke, Dan [EMAIL PROTECTED] wrote on 01/18/2006 02:05:24 PM:
I tried using prepared statements to make a procedure that converts
every table in a schema from ARCHIVE to MyISAM (there's about three
dozen archive tables here). But for some odd reason it will give an
error after doing the first table, and abort. The really odd thing is
that the table it gives the error on does successfully get converted.
Any thoughts? Is there a better way to do this? Or am I just doing
something stupid?
This is the error directly below... and the full procedure below that.
ERROR 7 (HY000): Error on rename of './CANN_ARCH/#sql-27b4_275.ARN' to
'./CANN_ARCH/WEBLOG_2004.ARN' (Errcode: 2)
information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and
ENGINE
= 'MyISAM';
Maybe you described it backwards? According to your cursor definition,
you are looking for tables of type MyISAM. Perhaps your cursor should be
looking for ENGINE='Archive' ?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Procedure / Prepared statements error converting table
Dan,
I tried using prepared statements to make a procedure that
converts every table in a schema from ARCHIVE to MyISAM...
"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE,
DO, INSERT, REPLACE, SELECT, SET, UPDATE, and
most SHOW statements. Other statements
are not yet supported."
(http://dev.mysql.com/doc/refman/5.0/en/sqlps.html)
PB
-
Burke, Dan wrote:
I tried using prepared statements to make a procedure that converts
every table in a schema from ARCHIVE to MyISAM (there's about three
dozen archive tables here). But for some odd reason it will give an
error after doing the first table, and abort. The really odd thing is
that the table it gives the error on does successfully get converted.
Any thoughts? Is there a better way to do this? Or am I just doing
something stupid?
This is the error directly below... and the full procedure below that.
ERROR 7 (HY000): Error on rename of './CANN_ARCH/#sql-27b4_275.ARN' to
'./CANN_ARCH/WEBLOG_2004.ARN' (Errcode: 2)
drop procedure if exists CONVERT_TABLES;
delimiter //
create procedure CONVERT_TABLES()
begin
DECLARE t char(255);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR for SELECT TABLE_NAME from
information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and ENGINE
= 'MyISAM';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t;
IF NOT done THEN
set @x = CONCAT('alter table ', t, ' engine =
myisam');
prepare z from @x;
execute z;
deallocate prepare z;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END; //
delimiter ;
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 1/18/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BIT datatype and prepared statements
Hi there, Still tracking down the BIT problems. For a simple statement, prefixing it with b'binary value' works (as written in the documentation). Even with the STRICT mode turned ON. However, for prepared statements, how do you bind a BIT(1) or BIT(n)? This doesn't list BIT at all: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html Any pointers? 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]
Re: BIT datatype and prepared statements
Hello. This doesn't list BIT at all: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html Have you tried MYSQL_TYPE_BIT? It is listed among others in mysql_com.h. Martijn Tonies wrote: Hi there, Still tracking down the BIT problems. For a simple statement, prefixing it with b'binary value' works (as written in the documentation). Even with the STRICT mode turned ON. However, for prepared statements, how do you bind a BIT(1) or BIT(n)? This doesn't list BIT at all: http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html Any pointers? 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 -- 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]
Mysqldump INSERT statements (Was Mysqldump line endings)
(--result-file) option to save your output but use the output redirector to map the output of mysqldump to a file you specify, you will get the CRLF line endings you seek. Many thanks. One more thing: by default mysqldump uses --extended-insert=TRUE. This results in all the VALUES data being on one line. IF the table has much data this produces very long lines when viewed in a text editor. (WordPad crashes!) If --extended-insert=FALSE then multiple INSERT statements are used. IS there any way to have 1 INSERT statement with each subsequent data line on a separate line? E.g. : INSERT INTO t1 VALUES (1,2,3), (1,4,5), (1,6,7); TIA - Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not operator in Select statements
On 10/24/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... Jasper Bryant-Greene Thank you Jasper. I remember from the manual that this _should_be correct. But when it doesn't work I am inclinded to ask. I will go try to track down the cause of my disappointment with the query. Thank you. Dotan Cohen http://lyricslist.com/lyrics/artist_albums/332/mccartney_paul.php McCartney, Paul Song Lyrics
Not operator in Select statements
How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; $query = SELECT album, year FROM albums WHERE year NOT 1990 ORDER BY year ASC; Thanks in advance. Dotan Cohen http://technology-sleuth.com/question/what_is_a_cellphone.html
Re: Not operator in Select statements
On Mon, 2005-10-24 at 06:09 +0200, Dotan Cohen wrote: How do I use a not operator in the WHERE clause? The obvious != and NOT didn't work for me. Something along the lines of: $query = SELECT album, year FROM albums WHERE year!=1990 ORDER BY year ASC; The above query is syntactically correct. If it didn't work for you, then your problem lies elsewhere. Perhaps post your error message? By the way, your question would have been answered by reading the manual... -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server-side/prepared statements and CR_SERVER_GONE
It seems that the implementation of server-side or prepared statements is significantly less robust than client-side prepared statements and other connection-dependent parts of MySQL, which means that they are going to be a pain in the backside to work with. Previously MySQL features have tended to be elegant and include a certain degree of graceful recovery, consider: 8x--- snip ---x8 mysql_query(create table tmp_foo (foo int)); if (mysql_query(truncate tmp_foo)) printf(#1 failed ** unexpected\n); system(service mysql restart); if (mysql_query(truncate tmp_foo)) printf(#2 failed expected, server gone\n); if (mysql_query(truncate tmp_foo)) printf(#3 failed **unexpected\n); else printf(connection recovered so the third query went thru again); 8x--- snip ---x8 Obviously if you have a client-side prepared statement, the connection loss isn't going to affect it. On a server-side prepare statement, however, losing your connection is going to blow away the server-side prepared statement, which was specific to that connection and held transiently in memory on the server. That's fair and understandable. But on the client you have to be aware that when the connection resets ALL of your prepared statements just went away and must ALL be re-constructed before being re-used. That means an awful lot of extra management for the client application and developer, especially since there is no way to tell that a statement is no-longer valid. It seems worth the minor extra memory overhead of having the client keep the source information for setting up the statement: copy the source sql statement text and copy the bind structure. Looking at the MYSQL_STMT structure it looks as though you have enough information to, for example, go through all of the stmt's attached to a connection and set their statement id to a value that indicates reset. - Oliver -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNION - different result when statements interchanged
I have a UNION whose statements when interchanged gives a different result. I can understand the change in the order of the rows, but how is it that 'picture' gets the correct value in (a) but not in (b)? Here are the two queries and their results: (a) (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5) UNION (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5); last_name picture Kapoor avataar02.png from table u Manni 0 from table u Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a (b) (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5) UNION (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5); last_name picture Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a Kapoor 0 from table u Manni 0 from table u Thanks for your help, Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION - different result when statements interchanged
From http://dev.mysql.com/doc/mysql/en/union.html : Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT are used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT: You can either update to version 4.1.1 or later, when the problem was solved, or make sure that the first SELECT retrieves columns that are wide enough and character types. On 8/5/05, Kapoor, Nishikant [EMAIL PROTECTED] wrote: I have a UNION whose statements when interchanged gives a different result. I can understand the change in the order of the rows, but how is it that 'picture' gets the correct value in (a) but not in (b)? Here are the two queries and their results: (a) (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5) UNION (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5); last_name picture Kapoor avataar02.png from table u Manni 0 from table u Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a (b) (SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE te.person_id = a.person_id AND te.tId IN (1) LIMIT 5) UNION (SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE te.person_id = u.uId AND te.tId IN (1) LIMIT 5); last_name picture Office of Technology Assessment 0 from table a Queue Readers 0 from table a Milnes 0 from table a Kapoor 0 from table u Manni 0 from table u Thanks for your help, Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
