Problems with indexes on Date/DateTime fields
Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] We have an index for dateField in tableX. However Query-1 is using the index but Query-2 is not. We have tested a lot of different combinations and every time we use any of these operators =, , , = Mysql stop using the index. We are using Mysql 4.1.12 binary distribution running on linux. Any help will be really appreciated. Thanks Javier ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with indexes on Date/DateTime fields
Hi Aftab Thanks for your prompt answer. Yes, second query is doing full scan. I don't understand why a change in the operator can make the parser think a full scan will be better than use the index. Nevertheless we know the best option is to use the index, unfortunately we can not use FORCE INDEX as what we really need to do are DELETE operations, and it seems FORCE INDEX is valid only for SELECT statements. Basically we have a huge database where we track different operations and every day we need to do some maintenance and delete records which are older than specific date. Thx Javier -Original Message- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:03 To: Javier Diaz Subject: Re: Problems with indexes on Date/DateTime fields Is not the second quary doing a full table scan? The parser may find this better than using the Index. --- Javier Diaz [EMAIL PROTECTED] wrote: Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] We have an index for dateField in tableX. However Query-1 is using the index but Query-2 is not. We have tested a lot of different combinations and every time we use any of these operators =, , , = Mysql stop using the index. We are using Mysql 4.1.12 binary distribution running on linux. Any help will be really appreciated. Thanks Javier ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with indexes on Date/DateTime fields
Thanks Aftab We already think of that option as possible solution but I was just wondering why is Mysql changing the logic when the operator changes. I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries Thx Javier -Original Message- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:25 To: Javier Diaz Subject: RE: Problems with indexes on Date/DateTime fields I agree. Some time full table scan is faster than using the index and going row by row. Any way, try using a subquery and get the result on to a temp table or just del usnig it . Some thing like this: Del from table where Key field in (Sel Key from tabl1 where stored datesome date); --- Javier Diaz [EMAIL PROTECTED] wrote: Hi Aftab Thanks for your prompt answer. Yes, second query is doing full scan. I don't understand why a change in the operator can make the parser think a full scan will be better than use the index. Nevertheless we know the best option is to use the index, unfortunately we can not use FORCE INDEX as what we really need to do are DELETE operations, and it seems FORCE INDEX is valid only for SELECT statements. Basically we have a huge database where we track different operations and every day we need to do some maintenance and delete records which are older than specific date. Thx Javier -Original Message- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:03 To: Javier Diaz Subject: Re: Problems with indexes on Date/DateTime fields Is not the second quary doing a full table scan? The parser may find this better than using the Index. --- Javier Diaz [EMAIL PROTECTED] wrote: Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] We have an index for dateField in tableX. However Query-1 is using the index but Query-2 is not. We have tested a lot of different combinations and every time we use any of these operators =, , , = Mysql stop using the index. We are using Mysql 4.1.12 binary distribution running on linux. Any help will be really appreciated. Thanks Javier ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with indexes on Date/DateTime fields
Hi Jigal Thanks a lot for your answer. Sorry for the confusion about DELETE and SELECT. What we are trying to optimize are some DELETE statements, it was just that while investigating we found this behaviour of Mysql not using some date indexes if we change from using = operator to use = or = Please find below all the details. Mysql version: 4.1.12 Operating System: Linux Table Type: InnoDB Number of records in table: More than 10 million process_times CREATE TABLE `process_times` ( `ID` bigint(20) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', `time` time NOT NULL default '00:00:00', `subagent` char(3) NOT NULL default '', `client_id` varchar(128) NOT NULL default '', `status` int(11) NOT NULL default '0', `process_time` double NOT NULL default '0', `host` varchar(20) NOT NULL default '', `process` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `client_idx` (`client_id`), KEY `status_idx` (`status`), KEY `subag_idx` (`subagent`), KEY `mias_idx2` (`host`), KEY `date_idx` (`date`), KEY `process` (`process`), KEY `date_proc_idx` (`date`,`process`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DELETE queries we want to optimize: DELETE FROM process_times WHERE (date = date_sub(now(), INTERVAL VariableX VariableY)) AND (process=VariableZ) While investigating we tried different SELECT statement to check is Mysql was using the Index: date_proc_idx See below what we found 1- Select using = operator. As you will see in the explain response not index is being used EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) Explain results: data row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeALL/type possible_keysdate_idx,date_proc_idx/possible_keys key(NULL)/key key_len(NULL)/key_len ref(NULL)/ref rows10778561/rows ExtraUsing where/Extra /row /data 2- Select using = operator. date_proc_idx index is used EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) Explain results: data row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeref/type possible_keysdate_idx,date_proc_idx/possible_keys keydate_idx/key key_len3/key_len refconst/ref rows1863456/rows ExtraUsing where/Extra /row /data 3- Adding condition for process field. EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 Explain results: - data - row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeref/type possible_keysdate_idx,process,date_proc_idx/possible_keys keydate_proc_idx/key key_len7/key_len refconst,const/ref rows550726/rows ExtraUsing where/Extra /row /data 4 - Again with condition for process field and changing operator from = to =. This cause Mysql to use a diffent index, in this case the index for process EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 Explain results: - data - row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeref/type possible_keysdate_idx,process,date_proc_idx/possible_keys keyprocess/key key_len4/key_len refconst/ref rows1830334/rows ExtraUsing where/Extra /row /data After seeing this we are now worry about the fact maybe many queries we have based on date fields are not using the indexes in the way we were expecting. I would like also ask if is valid to expect that the results of the Explain statement for a query like this SELECT * FROM TABLE-X WHERE [CONDITIONS] Are valid for the equivalent DELETE FROM TABLE-X WHERE [CONDITIONS] Thanks a lot for your help Javier -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 14:39 To: Javier Diaz Cc: Aftab Khan; mysql@lists.mysql.com Subject: Re: Problems with indexes on Date/DateTime fields Javier Diaz wrote: I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries If you do a select instead of a delete, will the index be used? (You can check this by using EXPLAIN SELECT) If the index is used in that case MySQL must have a reason for not using the index for deleting a range. With MyISAM tables deleting a single date
RE: C API : Problem using multi-statements
Hi
Instead of use two INSERT statements, try something like this:
INSERT INTO Table table1 VALUES (list of values1), (list of values2)
-Original Message-
From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED]
Sent: 06 May 2005 17:19
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: C API : Problem using multi-statements
Answer is simple. Can't do that.
- Original Message -
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, May 06, 2005 5:40 AM
Subject: C API : Problem using multi-statements
Hello,
I have some problems using multiple queries in a databased driven project,
therefore I wrote a little testprogram which
causes the same problems.
I am using the C-API of MySQL 4.1.11 on a Gentoo Linux 3.3.2-r5,
propolice-3.3-7 with 2.4.27 kernel.
I connect to the server (on localhost) with mysql_real_connect and the flag
CLIENT_MULTI_STATEMENTS,
I submit multiple queries (two INSERTS seperated by ;) on the existing
connection.
Executing the multistatement with mysql_query in a loop (i.e. 10 times),
I get a lot of lost connection during query errors, but sending a single
INSERT query in a loop causes no errors !!!
Thanks in advance for help
regards Tinosch Ganjineh
The following program operates on a simple table structure created with the
following statement :
CREATE TABLE BIGTABLE (myoid char(40), mykey char(40), myval char(40), myint
bigint) TYPE = InnoDB;
/** mysqltest.cpp
*/
#include iostream
#include mysql.h
#include sstream
#include string
using namespace std;
string itos(long long i) {
ostringstream sstream;
sstream i;
return sstream.str();
}
int main(int argc, char** argv) {
MYSQL* conn;
if(conn = mysql_init(NULL)) {
if(mysql_real_connect(conn, localhost, root, x, test, 0, NULL,
CLIENT_MULTI_STATEMENTS )) {
int loop=100;
for(int i=0; iloop; ++i) {
int e=0;
string query;
query = string(INSERT INTO BIGTABLE VALUES () + 'object- +itos(i)+',
'foo', 'bar', NULL);\
INSERT INTO BIGTABLE VALUES ( + 'object- +itos(i+1000)+', 'bar, 'foo',
NULL);
e = mysql_query(conn, query.c_str());
if(e) {
cerr *Query failed*: e - mysql_error(conn)
endl;
} else {
MYSQL_RES* result = mysql_store_result(conn);
if(result) {
// .. parse result set ...
} else {
//cerr Could not fetch Results from DB: mysql_error(conn);
}
mysql_free_result(result);
}
}
} else {
cerr Could not connect to MySQL database: mysql_error(conn)
endl;
}
} else {
cerr Could not initialize MySQL: mysql_error(conn) endl;
}
}
/** mysqltest.cpp
*/
--
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]
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Hi We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? I would appreciate any ideas you can have, we really need this ASAP. Thanks Javier` -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 July 2004 10:42 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Concurrency Question Javier Diaz [EMAIL PROTECTED] wrote on 02/07/2004 10:28:32: We have some tables to record different data of the activity in our website. The number of INSERT, DELETE and UPDATE operations in these tables is huge (it could be more than 3000 a second). So far we don't have any speed problems, all these tables are optimised using the right indexes and everything is working ok. But now we are writing a Report Tool to get some stats and figures from these tables. We have been doing a few tests and any SELECT query taking more than one second or a few simultaneous SELECT, and we have a real mess, lots of LOCKS. We definitely can not afford to slow down the web site, and we have been thinking in a few possible solutions 1- Create a duplicate once a day of each of the tables we need to connect from the Report Tool. We can do this in the period of less activity in the site. This a safest solution because we will be running all the SELECT's against the duplicates and there are no risks to cause problems in the site but we will have the inconvenience that we can not get latest figures only the previous days. Sounds like a nasty kludge to me. I really wouldn't be keen on it at all. 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good enough It strikes me that this is what InnoDB is designed for. In your situation, this is the first thing I would try. 3- Replication of these tables to another server ?? This would also work: it just uses more resources (another computer of nearly equal power) and more development time (scripts to stop and start replication. If you can borrow the replicated machine, you could test the InnoDB solution on that: change the tables on the replicated server to InnoDB and try running your report generator on that. If the replications doesn't fall behind, the main server will probably handle it. Alec This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Hi I really don't like the idea to set innodb_flush_log_at_trx_commit to 2, the information in these tables is important. On the other hand there is nothing I can do from the point of view of the number of transactions. Each process run its own set of INSERTs and UPDATEs statements, so I can not reduce the number of transactions being executed. Looking to the MySQL documentation: Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167th/second if the disk does not fool the operating system And that we are doing a LOT MORE INSERTs by second, I'm afraid maybe the only solution is go back to MyISAM :-( By the way this figure of 167 revolutions/second is based on what kind of hard disk? thanks Javier -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: 05 July 2004 17:58 To: Javier Diaz Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Concurrency Question On Mon, 5 Jul 2004 16:07:58 +0100 , Javier Diaz [EMAIL PROTECTED] wrote: We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? That all depends on how you are using transactions. If you are trying to do each of these operations in a separate transaction, then definitely that will be a problem since transactions inherently have a certain cost to them since they need to commit changes to durable storage. If this is the case, then a horribly ugly now you don't have durability any more in your transactions hack you could try is setting innodb_flush_log_at_trx_commit to 2, see the docs for details. Be warned that doing so means you can loose committed transactions if the machine crashes. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concurrency Question
Hi everyone We have some tables to record different data of the activity in our website. The number of INSERT, DELETE and UPDATE operations in these tables is huge (it could be more than 3000 a second). So far we don't have any speed problems, all these tables are optimised using the right indexes and everything is working ok. But now we are writing a Report Tool to get some stats and figures from these tables. We have been doing a few tests and any SELECT query taking more than one second or a few simultaneous SELECT, and we have a real mess, lots of LOCKS. We definitely can not afford to slow down the web site, and we have been thinking in a few possible solutions 1- Create a duplicate once a day of each of the tables we need to connect from the Report Tool. We can do this in the period of less activity in the site. This a safest solution because we will be running all the SELECT's against the duplicates and there are no risks to cause problems in the site but we will have the inconvenience that we can not get latest figures only the previous days. 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good enough 3- Replication of these tables to another server ?? We would appreciate a lot any ideas Thanks a lot Javier This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rows Counter
Hi everyone I need have a row counter in a query but I'm not sure if there is any way to do this. In essence all I need is get a result like this: Counter column-A column-B 1 A-1 B-1 2 A-2 B-2 : : : : : : where A, B are real columns and Counter in just a consecutive for each row in the query result. Thanks Javier This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inno DB Question
Heikki,
Thanks for your answer. I was yesterday making other tests using dbExpress
components and the only way that I found so far to get the dbExpress
components working on transactions on MySQL is using the method ExcuteDirect
of the TSQLConnection. This is the code :
TSQLConnection *Conn;
Conn = SQLConnection1-CloneConnection();
Conn-Connected=true;
Conn-ExecuteDirect(BEGIN);
Conn-ExecuteDirect(INSERT INTO regn_info VALUES('t3','test1'));
Conn-ExecuteDirect(INSERT INTO regn_info VALUES('t4','test2'));
Conn-ExecuteDirect(COMMIT);
But I don't know why is impossible to do the same using the normal
TSQLQuery. I already sent the message to the borland newsgroups.
Thanks
Javier
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: 02 September 2002 20:06
To: [EMAIL PROTECTED]
Subject: Re: Inno DB Question
Javier,
it may be that the dbExpress interface is still lagging behind MySQL
development.
If I recall right, people have complained loudly in Borland newsgroups that
dbExpress created a new connection for each SQL query it issues to MySQL.
That makes the use of transactions impossible with it.
If this has not been fixed in the latest version of dbExpress, it would be
good if you would write to Borland people about this, or to the Borland
newsgroups.
Regards,
Heikki
Innobase Oy
- Original Message -
From: Javier Diaz [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Monday, September 02, 2002 3:06 PM
Subject: Inno DB Question
Hi all,
I'm using MySQL 4.0.2 alpha-max with Inno DB and Borland Builder 6.0 to
connect to MySQL. I'm trying to use the new dbExpress component for
Borland
to connect to MySQL using the libmysql.dll
I have been making some tests with transactions using the dbExpress
components and the normal ODBC but I can't get the ROLLBACK function to
work.
Here are some examples:
using dbExpress
TSQLConnection Q;
Q= new TSQLQuery(Application);
Q-SQLConnection=SQLConnection1;
/* I test with and without BEGIN
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
*/
Q-SQL-Clear();
Q-SQL-Add(SET AUTOCOMMIT=0);
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(ROLLBACK);
Q-ExecSQL();
//--
Using ODBC
TQuery *Q_ODBC;
Q_ODBC= new TQuery(Application);
Q_ODBC-DatabaseName=salsadb;
/*
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
*/
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(SET AUTOCOMMIT=0);
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(ROLLBACK);
Q_ODBC-ExecSQL();
In both cases the ROLLBACK dosen't work and I get the inserted values in
the
table.
Any idea what can be happening?
Thanks a lot
Javier Diaz
--
This e-mail is intended for the named addressee only. It may contain
confidential and/or privileged information. If you have received this
message in error, please let us know and then delete this message from your
system. You should not copy the message, use it for any purpose or disclose
its contents to anyone.
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
*Scanned for all known viruses by Messagelabs*
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
Inno DB Question
Hi all,
I'm using MySQL 4.0.2 alpha-max with Inno DB and Borland Builder 6.0 to
connect to MySQL. I'm trying to use the new dbExpress component for Borland
to connect to MySQL using the libmysql.dll
I have been making some tests with transactions using the dbExpress
components and the normal ODBC but I can't get the ROLLBACK function to
work.
Here are some examples:
using dbExpress
TSQLConnection Q;
Q= new TSQLQuery(Application);
Q-SQLConnection=SQLConnection1;
/* I test with and without BEGIN
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
*/
Q-SQL-Clear();
Q-SQL-Add(SET AUTOCOMMIT=0);
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(ROLLBACK);
Q-ExecSQL();
//--
Using ODBC
TQuery *Q_ODBC;
Q_ODBC= new TQuery(Application);
Q_ODBC-DatabaseName=salsadb;
/*
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
*/
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(SET AUTOCOMMIT=0);
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(ROLLBACK);
Q_ODBC-ExecSQL();
In both cases the ROLLBACK dosen't work and I get the inserted values in the
table.
Any idea what can be happening?
Thanks a lot
Javier Diaz
--
This e-mail is intended for the named addressee only. It may contain confidential
and/or privileged information. If you have received this message in error, please let
us know and then delete this message from your system. You should not copy the
message, use it for any purpose or disclose its contents to anyone.
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Inno DB Question
Hi Tod, thanks for your answer.
I changed the code to this
Q-SQL-Clear();
Q-SQL-Add(SET AUTOCOMMIT=0);
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(ROLLBACK);
Q-ExecSQL();
And now for the ODBC connection is working fine, but the same code using
TSQLQuery (dbExpress Component) dosen't work. Do you have any idea what can
be the reason?
Thanks
Javier
-Original Message-
From: Tod Harter [mailto:[EMAIL PROTECTED]]
Sent: 02 September 2002 15:58
To: Javier Diaz
Subject: Re: Inno DB Question
On Monday 02 September 2002 07:37 am, you wrote:
I would think you would want to set autocommit BEFORE you call BEGIN since
that starts your transaction. My guess would be that setting autocommit
clears any existing transaction. In the case without the begin and
autocommit set to 0 I don't really know what is going to happen...
Essentially you're telling the database I'll tell you when to create a
transaction and then you don't create one. I can only assume the behaviour
is that mysql goes back to the 'autocommit 1' state at that point since
otherwise whatever you're doing makes no sense.
Hi all,
I'm using MySQL 4.0.2 alpha-max with Inno DB and Borland Builder 6.0 to
connect to MySQL. I'm trying to use the new dbExpress component for
Borland
to connect to MySQL using the libmysql.dll
I have been making some tests with transactions using the dbExpress
components and the normal ODBC but I can't get the ROLLBACK function to
work.
Here are some examples:
using dbExpress
TSQLConnection Q;
Q= new TSQLQuery(Application);
Q-SQLConnection=SQLConnection1;
/* I test with and without BEGIN
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
*/
Q-SQL-Clear();
Q-SQL-Add(SET AUTOCOMMIT=0);
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q-ExecSQL();
Q-SQL-Clear();
Q-SQL-Add(ROLLBACK);
Q-ExecSQL();
//--
Using ODBC
TQuery *Q_ODBC;
Q_ODBC= new TQuery(Application);
Q_ODBC-DatabaseName=salsadb;
/*
Q-SQL-Clear();
Q-SQL-Add(BEGIN);
Q-ExecSQL();
*/
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(SET AUTOCOMMIT=0);
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(INSERT INTO regn_info VALUES('t1','test1'));
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(INSERT INTO regn_info VALUES('t2','test2'));
Q_ODBC-ExecSQL();
Q_ODBC-SQL-Clear();
Q_ODBC-SQL-Add(ROLLBACK);
Q_ODBC-ExecSQL();
In both cases the ROLLBACK dosen't work and I get the inserted values in
the table.
Any idea what can be happening?
Thanks a lot
Javier Diaz
--
This e-mail is intended for the named addressee only. It may contain
confidential and/or privileged information. If you have received this
message in error, please let us know and then delete this message from
your
system. You should not copy the message, use it for any purpose or
disclose its contents to anyone.
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
--
This e-mail is intended for the named addressee only. It may contain confidential
and/or privileged information. If you have received this message in error, please let
us know and then delete this message from your system. You should not copy the
message, use it for any purpose or disclose its contents to anyone.
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working
