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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
'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 Replies !
Turn On The Logs
How to turn on the log exactly?? (For all type of logs). And would they all be stored in the ".../mysql/var" directory??
View Replies !
Turn On Slow_query_log
I checked the path of my.cnf: Code: # /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options' Default options are read from the following files in the given order: /etc/my.cnf ~/.my.cnf /etc/my.cnf Here is /etc/my.cnf Code: # cat my.cnf [mysqld] set-variable = max_connections=500 safe-show-database skip-innodb query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 log-slow-queries=/var/log/mysql_slow_query.log long_query_time=10 log-queries-not-using-indexes I suppose it's possible I haven't hit any long queries...but perhaps the fact that /etc/my.cnf is pointed to twice in the path is a problem? (what determines path to my.cnf anyway?) The reason I'm thinking that is the following command prints out the contents of my.cnf twice: Code: # mysqldumpslow Can't determine basedir from 'my_print_defaults mysqld' output: --set-variable=max_connections=500 --safe-show-database --skip-innodb --query_cache_limit=1M --query_cache_size=32M --query_cache_type=1 --log-slow-queries=/var/log/mysql_slow_query.log --long_query_time=10...
View Replies !
Turn Off Case
Is it possible to turn off the case sensitivity of table names in mysql. I'm connecting from an asp.net application.In the development environment the mysql db was on windows and so didn't care about case. The production environment does care though.Can I turn it off (without modifying the server) or do I have to rewrite a whole bunch of sql statements?
View Replies !
Turn Headers Off
mysql> use mysql; mysql> select host from user; +---------------+ | host | +---------------+ | somehost | | otherhost | +---------------+ How can I turn off columns headers to get: somehost otherhost
View Replies !
Is It Possible To Turn These Queries Into Just One?
UPDATE categories set orderid=1 WHERE carid=3 UPDATE categories set orderid=2 WHERE carid=5 UPDATE categories set orderid=3 WHERE carid=6 UPDATE categories set orderid=4 WHERE carid=7 UPDATE categories set orderid=5 WHERE carid=8 UPDATE categories set orderid=6 WHERE carid=1 UPDATE categories set orderid=7 WHERE carid=2 UPDATE categories set orderid=8 WHERE carid=4
View Replies !
Turn "echo
In Oracle, I can say "set echo on" to display the output of a script as it's executing so I know where an error occurs. I can't seem to find this in MySQL 4.1
View Replies !
How To Turn On Error Messages
I was wondering if some kind soul could enlighten me as to how I :: 1. turn on error message logging; 2. change the level of error message logging, and finally; 3. what is the highest level of error logging?
View Replies !
How To Turn 5 Into May (date Formatting)
i have this: SELECT MONTH(NOW()) which gives me 5 i need it to be in this format since i will be adding or subracting from this number so i cant JUST use monthname (unless May + 1 = june!) anyway..... monthname needs a whole date ie xx-05-xxxx so how to i turn this 5 into May?
View Replies !
Can't Turn On Slow Query Logging
long_query_time = 1 log-slow-queries = /var/lib/mysql/slow_queries.log Is the above syntax not correct for enabling slow query logging? All examples I've seen have the dashes in the second variable and underscores in the first. When I restart MySQL with those lines in my.cnf, it fails to start, but writes nothing to its error log. /var/lib/mysql/slow_queries.log exists, is owned by mysql, and has read/write permission.
View Replies !
How Do I Turn On Logging To View Errors In MySQL 5
I'm a new MySQL user. Just switched over from the Bill Gates world and am joining the open source side finally. I setup a website using Joomla! 1.x with PHP5 and MySQL 5 on the backend. However when I submit a post in my forum on my new site, the page goes blank yet says it's finished loading. In the Microsoft world you can echo back to the screen errors and figure out which row, record or table was causing the error. Can you do the same using MySQL or does it have a logging feature that could help me troubleshoot and see why my posts are hanging when hitting the submit button via my browser?
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 !
|
TRACKER
