'SET AUTOCOMMIT=0' Insert + Rollback - Doesn't Work
i try to setup a simular behavior as ORACLE ie I can commit; or rollback;
after I am done with my session.
ie
'SET AUTOCOMMIT=0'
then I use BEGIN; or START TRANSACTION;
delete a row and then try to rollback - it doesn't work - it's stuck on autocommit; everything I do is directly commited and there is no way to undo my changes .
WHY?
simple test:
mysql> SET AUTOCOMMIT=0 ;
Query OK, 0 rows affected (0.00 sec)
mysql> Begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from one where id='seven';
Query OK, 1 row affected (0.00 sec)
mysql> select * from one;
+------+
| id |
+------+
| one |
| tow |
| tree |
| four |
| five |
| six |
+------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from one;
+------+
| id |
+------+
| one |
| tow |
| tree |
| four |
| five |
| six |
+------+
6 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from one where id='six';
Query OK, 1 row affected (0.00 sec)
mysql> select * from one;
+------+
| id |
+------+
| one |
| tow |
| tree |
| four |
| five |
+------+
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from one;
+------+
| id |
+------+
| one |
| tow |
| tree |
| four |
| five |
+------+
mysql>
View Complete Forum Thread with Replies
Related Forum Messages:
Can't Get Init_connect='AUTOCOMMIT=0' To Work
I've got 4.1.3 installed on Linux.. everything fine so far except for getting the init_connect string to work. I followed the instructions on http://dev.mysql.com/doc/mysql/en/Server_system_variables.html and added the below line to my '/etc/my.cnf' file under [mysqld]: init_connect='SET AUTOCOMMIT=0' When I restart the server and start mysql, I can see that the init_connect was read: mysql> select @@init_connect; +--------------------+ | @@init_connect | +--------------------+ | SET AUTOCOMMIT=0| +--------------------+ However, the Autocommit variable is still 1! mysql> select @@AUTOCOMMIT; +-----------------+ | @@AUTOCOMMIT| +-----------------+ | 1| +-----------------+ Anyone seen this before or know what's wrong?
View Replies !
Function Doesnt Work
i tried write simple function: CREATE FUNCTION `getLastNumber`(some_guy INT(50), date DATE) RETURNS float BEGIN DECLARE a FLOAT; SET a = NULL; SELECT number INTO a FROM numbers p WHERE p.number_owner = some_guy AND p.date_created <= date ORDER BY p.date DESC LIMIT 1; RETURN a; END that would return last inserted number into table for some person. When some_guy has number in table, then is everything ok and it returns last number, but when some_guy does not have row in table it returns: Query OK, -1 rows affected. Pls why? and how to fix it?
View Replies !
GROUP BY Doesnt Work
i have a table that contains the folowing fields: some_table: company_number something_number price date_price_established for a certain company i need to get all the dates that prices were established. and i dont want any date_price_established to repeat themselfs. for example, for the following database: 1,1,15.5,1.1.2006 1,1,16,1.1.2006 1,1,17.5,1.1.2006 1,1,16,3.1.2006 the results will be : 1.1.2006, 3.1.2006. this is what i've tried: SELECT date_price_established FROM myDb.some_table WHERE company_number = 1 GROUP BY date_price_established";
View Replies !
Domain Name Now Doesnt Work
I have a software script I had installed on my server that works with a mysql database. It was working fine as far as pulling information from the database under my old domain name, however, I changed to a new domain name as primary on my server and now the database does not pull anything into the template. I know this is probably simple but not sure how to fix this.
View Replies !
Where Clause Doesnt Work
im creating a website with a mysql database. ive jus got started. im havin a few problems i have a product table and i execute a simple query but it doesnt seem to give me any results: heres the query: Select * from product where ProductName = 'Age of Empires III'; the above query doesnt work but this 1 does Select * from product where ProductName LIKE 'Age%';
View Replies !
Mysqldump Doesnt Work
i try to insert the followind data to mysql database mysqlimport -u root -p anaktisi2 < phpbb_db_backup.sql But this doesnt insert anything in the db.
View Replies !
Full Text Searsh Doesnt Work
I am trying to do a Full text search, but I have two problems. 1. If I enter a word that is found under multiple entries, I get no return. But it i search a word tha's only under one enetry, then it works. 2. This query works: PHP SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice') AND `approved`=Ƈ' , but when I add WITH QUERY EXPANSION, it doesnt work. PHP SELECT * FROM `site` WHERE MATCH (site_name,keywords) AGAINST ('mice' WITH QUERY EXPANSION) AND `approved`=Ƈ' I get this error: MySQL said: #1064 - 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 'WITH QUERY EXPANSION ) AND `approved` = Ƈ' LIMIT 0, 30' at
View Replies !
Insert Entry Doesn't Work
When I make a "select" request everything runs fine. But if I want to insert an entry, nothing happens. There isn't a error-message and the privileges are set right. (Syntax is ok :-)) Where is the problem?
View Replies !
Insert Does Not Work But Editor Say That Its Ok!
I connect to a remove mysql host via the free SQLYog successfully. Now, I have the following problem. I have one table called "activeEbayListings". I open the query editor and simple type in the following query: INSERT INTO activeEbayListings (title, subtitle, quantity, current_price, store_category, item_id) VALUES ('test','test', 1, 1, 2, 'someID'); The editor says to me that the query was successfully and the 1 row was affected (inserted...). It says the following: (1 row(s) affected) (0 ms taken) OK, then I refresh the whole database in SQLYog and view the rows...but I simply cannot find the data that was previously inserted. What is wrong here?
View Replies !
AutoCommit
how can I lookup whether autocommit is set or not? I have a mysql 4.1.13 version with myisam tables.. is there actually a commit possible? If I update a record in a table, and make a select, I see the updates with a select.. but on the next day the updated record is not updated... very strange.. I hope with commits the problem will be solved.
View Replies !
WHERE Doesnt Equal
I am trying to select something my DB where a clause does not equal something but for some reason I cant seem to make it work. Here is what I have tried. PHP Code: SELECT * FROM golf_groups WHERE name!="something" but this doesnt seem to work. i had done something like this before and thought that I had done it this way, but since this does not work,
View Replies !
Default Autocommit=0?
Is there any way I can set up mysql client so that the default value for autocommit is 0? This is for when I'm running the command line client on linux/windows or the Query Browser on windows. I would feel much more comfortable that I'm not going to accidentally put in the wrong where clause and update or delete a bunch of records. I like to use the number of rows affected by my query as a double-check that I've done what I intended to do, and then commit it. I've searched around but could not find any mention of this issue -- all of the postings on autocommit that I found have to do with accessing mysqlserver from a program such as java or PHP.
View Replies !
Turn Off The AutoCommit
well this might not the first time someone is askng this question, but I coudn't find it once before. So: I'm useing InnoDB and I generally want to switch off the AutoCommit for the transactions. But I can't find any solution.
View Replies !
It Doesnt Use My Index!
`player_1` mediumint(7) unsigned NOT NULL default Ɔ' `player_2` mediumint(7) unsigned NOT NULL default Ɔ' reffering to users' id-number in another table. I have two indexes: KEY `one` (`player_1`), KEY `two` (`player_2`) The cardinality of them is 6000 and 7000. My primary-key has cardinality 150 000. When i run this query: EXPLAIN SELECT * FROM `gamebase` WHERE player_1 =1 It uses the "one"-key (doh!). But now, that I run my next query (using OR), it tells me that one and two are possible keys, but it uses neither, but loops through my entire table "Using where". EXPLAIN SELECT * FROM `gamebase` WHERE player_1 =1 OR player_2 =1
View Replies !
Its Returning A Value That Doesnt Exist
Okay here is the deal. I am running a query to find out if there is an ID in my table that has a case number that matches the one being submitted. If there isnt once its should step into an if statement that would submit a number of records. Otherwise it should jump to the else statement. I the problem comes from: the query keeps returning "Record id#2" but there is no record matching the case number being submited from the form. if( isset($_POST['Submit'])){ //Begin If statement logic $resultcheck = mysql_query("SELECT * FROM submissions WHERE wfmcase = " . $_POST['wfmc']) or die(mysql_error()); //Query string to test if wfmcase was already submitted echo "Sucess, form varibles have been passed.<br />"; echo "Resultcheck query results: $resultcheck <br />"; if ($resultcheck == ""){ //Checks to see if the query was empty. echo "There are no duplicate case escalations pending. <br />"; I added the echo statement so that i could see how far it was getting through the script. It dies after printing the varible $resultcheck which gives "Record id#2". It never enters the 2nd if statement because for what ever reason the query has the "record #id2" in it... What is causing this? I have verfied that there is no case that matches the one coming from the form and i get the same error.
View Replies !
ROLLBACK
Any reason to suspect that ROLLBACK is not working for MySQL v4.1.10a? Have reduced code to a very simple test, START TRANSACTION followed by INSERT followed by ROLLBACK, but the INSERT remains. Tried substituting SET AUTOCOMMIT=0 for START TRANSACTION, no effect, no error.
View Replies !
Commit Or Rollback?
I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; sql statement 1; sql statement 2; .. .. .. sql statement n; At this point I'd like to say, in sql, if no errors then commit; else rollback end From what I read in the manual I can do one or the other (commit or rollback) but there didn't seem to be a way of conditionally doing one or the other of them.
View Replies !
Auto ROLLBACK
I'm trying to utilize transactions during new user creation because I'm submitting information to multiple tables and using multiple pages to submit with. If they don't complete the process by filling in each page I want the whole thing to be rolled back. As I understand InnoDB will auto rollback any transaction in two cases, when the system crashes, or when the connection/session is lost or ended. Am I correct?
View Replies !
Rollback Database
we like to run a mysql database with innodb and transaction logs. we have about 20 million rows and 30- 50 users. is it possible to rollback the transactions from one specific user to a specific date/time ??
View Replies !
Implicit Rollback
I have a question regarding transaction rollback. I am using a stored procedure on InnoDB tables, invoked from a Java client (using Hibernate). I use the 'autocommit = 0' flag, and I do not want to catch errors in my procedure, but let them be thrown to my client. Doing that I cannot call rollback from my procedure. My question is, what happens when an error occurs: do the server execute implicit rollback, or do I have to call rollback explicitly? Can I call explicit rollback from my client after exception is caught? I am using server version 5.0.45 Community Edition.
View Replies !
Commit Rollback
I have to copy my local database to my global database, if its done the local database must be erased and if there is a problem it should not copy anything. So I've heard that I have to use the actions commit and rollback but I don't understand how they work,
View Replies !
Transactions - Rollback
Is it necessary to call 'ROLLBACK' statement if one of your queries fails? Or is it sufficient just NOT to call 'COMMIT'. Seems to be working both ways for me.
View Replies !
Rollback Segment Equivalent
What is the equivalent of rollback segments (from Oracle) on MySQL? And if something is being used internally, can I change the size of this? I do not want to run into problems like exceeded rollback size or something like that.
View Replies !
MyISAM Commit And Rollback
I was reading the MySQLmanual and became confused about myISAM and commit/rollback. Can someone explain what I have to do in my queries to garuanty the dataintegrity of my db in case I use myISAM? Do I have to use the commit and rollback statements or is this done by MySQL self?
View Replies !
Server Structure Rollback
I'm starting with MySQL transaction right now. I need to do some changes in MySQL server and database structure. Is it possible do some changes as rename table or alter table and so on and then if I want to cancel this operation, do somehow a rollback?
View Replies !
COMMIT And ROLLBACK With C API Mysql_query()
I:m just about to wrap my update queries in transactions and want to confirm something. I:m using the C API mysql_query() function with the connection opened in CLIENT_MULTI_STATEMENTS mode. Do I decide to COMMIT or ROLLBACK based on mysql_next_result(), mysql_store_result() and mysql_errno() checks like this: The query string basically amounts to something like this: START TRANSACTION; INSERT something somewhere; DELETE something else; UPDATE yet another thing; etc ... and this is sent to the server, >without< a COMMIT. Notice there are no SELECTs so no data is returned. Is the following code the right way to proceed, only sending the COMIT or ROLLBACK after everything has been checked? '____int action = commit; // default '____if (mysql_query(mysql_handle, query_string) != 0) '____{ '________// Error in the very first statement '________action = rollback; '____} '____else '____{ '________do '________{ '____________// Make sure each command in the SQL query string passed '____________if ((mysql_result = mysql_store_result(mysql_handle)) == NULL) '____________{ '________________if (mysql_errno(mysql_handle) != 0) '________________{ '____________________// Something went wrong - rollback '____________________action=rollback; '____________________break; '________________} '____________} '____________else '____________{ // This wont happen for just UPDATEs and INSERTs '________________mysql_free_result( mysql_result ); '____________} '____________next_results_status = mysql_next_result( mysql_handle ); '________} while (next_results_status == 0); '________ '________// Should fall out the do loop without an error '________if ( next_result_status > 0 ) '____________action = rollback; '____} '____ '____// Do whichever action is appropriate '____mysql_query( mysql_handle, (action==commit) ? "COMMIT" : "ROLLBACK" );
View Replies !
Rollback DROP Database Statement
I accidentally clicked the DROP button on phpadmin for the horde database that runs our schools email server. I am in a world of hurt if I can't undo this transaction. I haven't done anything except for read the documentation and I can't seem to find anything that would rollback or undo the last statement.
View Replies !
Automatic Rollback Transaction In Mysql
I have mysql automatically abort an entire transaction if one of the statements produces an error ? I can use procedure for checking error_count at the last line of my transaction. But if I want to automatically rollback instantly when the error occurs in my transaction instead of going to the last line of transaction. Can i do this in MySQL?
View Replies !
Data Loss On One Table Only (auto Rollback?)
I have problems with one of the tables. I can INSERT INTO this and another table no problem, can view the data in the MYSQL commmand line, but on restarting the website - going back to the index.php - the one problem table is effectively DELETEd FROM - apparently prior to any statements (and the start_session) being run (though there's only one row in the table). I newish to PHP, but am an experienced programmer, and very au-fait with programming SQL (Microsoft T-SQL) commands. My problem seems like a ROLLedBACK transaction, but there are no START TRANSACTIONS, ROLLBACK or COMMIT statements in the PHP code. I have added code to INSERT INTO two existing tables, one retains its data, the other does not! I've come across MySQL's AUTOCOMMIT, but I don't think the website/server is using it. Is there some MySQL setting on this table that is omitted / required to remove this issue? The table has an autoincrement field (as does the other working one) - I've even tried changing it to a normal field, but to no avail. Is there a global MySQL setting that may cause this scenario? Is there some issue I need to be aware of re index.php and MySQL? Is there some coding fault I'd need to be aware of which could cause this situation?
View Replies !
Transaction Deadlock When Using "autocommit"
Transaction is a big problem in MySQL --> here is what I have done Since the DB is InnoDB, I tried to "set autocommit=0" so that it can be rollback if it wants, and data will not be actually updated into the file disk unless a "commit" is performed. I typed: $autocommit = "SET autocommit=0"; $setcommit = mysql_query($autocommit); echo "<font color = yellow> Setcommit = $setcommit </font><BR>"; and then it worked once, but then for the second time, when I go to mySQL and try to do something, it came an error of : ERROR 1205: Lock wait timeout exceeded; Try restarting transaction which is probably the deadlock problem...I have to release the lock by i) show processlist; (and see which thread is sleeping) ii) kill thread_id; Does anyone know how to perform transaction with php using InnoDB?
View Replies !
SELECT Doesnt Select Anything WHERE Doule
I am running the next script SELECT * FROM flcc_Tickets WHERE 'Match No'=1; but dont get any results although there are lots of resords with 'Match No'=1 in a table. 'Match No' is DOUBLE . if run it with 'Match No'=0 i get all the table.
View Replies !
Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement: insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1) When I run the select statement alone, I get the result I want: +---+---+---------------------+---+ | 3 | 1 | software_feature_id | 1 | +---+---+---------------------+---+ | 3 | 1 | 0 | 1 | | 3 | 1 | 1 | 1 | | 3 | 1 | 2 | 1 | +---+---+---------------------+---+ But when I run the complete statement I get: 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 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1 This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.
View Replies !
Mysqldump Options :: --extended-insert --complete-insert
Adding options like extended inserts or complete inserts (or both) make no difference to the output file. Why? mysqldump -uroot -pmypass! --databases test --tables proxy_view > mytest.txt mysqldump -uroot -pmypass! --extended-insert --complete-insert --databases test --tables proxy_view > mytest.txt Both the commands mentioned above saves a same set of commands. insert into tablename (column names) values (set of values), (another set of values), (one more set) Whereas I want insert into tablename (column names) values (first set); insert into tablename (column names) values (second set); Ok. --skip-extended-insert is what I was looking for
View Replies !
Bug Work
I've got this bug http://bugs.mysql.com/bug.php?id=27861. I know they've made a fix and it's going to come out in the next version but is there any way I can fix this myself?
View Replies !
How Does Sql Work Exactly?
Well what exactly does sql do when you create a table for like a login (user) What does the tables and stuff do. Do they just create like a library to store that users information so they can log back in to site or w/e....
View Replies !
How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?
View Replies !
Subselect Does Not Work
i am trying to remove values from a list menu if the join table d= oesnt have keys when a key is selected for instance: locations locationID locations_join locationID shotlistID SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo= cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN= (select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B= Y l.location ASC. so when shotlistID is selected all the keys from the locations_join joined = to the shotlistID would be remove from the locations list please help, i'm = trying to do this in one query saving from getting all the keys into an arr= ay then checking if the values arent in the array when generating the list.
View Replies !
Date_add Does Not Work
I'm using mySQL 5.0, with a database of UNIX timestamps. Can someone explain this to me? This query works great: SELECT from_unixtime( 1132277574 ) But this one breaks: SELECT date_add(from_unixtime( 1132277574 ), interval 1 week) Telling me, oh-so-helpfully, that the error is near "'week)'". (It doesn't work when I use "1 weeks" either.)Does the from_unixtime simply not work with the date_add function, or is there some arcanity to this syntax that I'm missing?
View Replies !
Getting Log File To Work
I am putting log = "C:Program FilesMySQLMySQL Server 5.0sql.txt" into my .ini files and mysql wont start. I cant work out the syntax to tell mysql to ouput all the queries to a log file.
View Replies !
How Does Synchronizing Work?
I have a question; currently we want to run our central database from two different locations as opposed to one currently. I am trying to figure out how to synchronize the two locations. My question is: if two (or more) locations create new clients locally, each location will thus create a new client-numbers on their own active operational database. This opens the possibility that each locations will create identical client-numbers; of course being different clients altogether.
View Replies !
Mysql_connect Does Not Work
I have searched through the forum and tried the suggestions that have been made for this issue but I am about ready to give up! I have installed Apache 2.0.52, PHP 5.0.3, and MySQL 4.0.23 on Windows XP. I uncommented extension=php_mysql.dll in the php.ini file and copied libmysql.dll into both C:WindowsSystem32 and C:WindowsSystem. My phpinfo.php test works fine so I know PHP and Apache are working together. I do not see any MySQL references on that entire page. From previous posts I know I should. Needless to say, mysql_connect(...) does not work, it gives an undefined function error. I apologize if this should have been posted in the PHP forum instead. If it is a PHP issue, I will repost this there.
View Replies !
|
TRACKER
