Set Autocommit=0 Not Working
I had set autocommit to 0 but it still commits. Any ideas why?
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 !
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 !
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 !
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 !
LIKE '[0-9]%' Not Working
Here is the SQL query: Code: SELECT * FROM member WHERE username LIKE '[0-9]%' Returns 0 rows. I've tried this on 4.1.7-nt as well as 4.0.22-standard; neither work. And yes, there are records in the table that should match that pattern.
View Replies !
LIKE '%$var%' Not Working
I am trying to create an sql statement to return values on either side of the variable. Code: $sql = "SELECT * FROM principals LIKE companyName = '%{$_POST['companyName']}%'"; The error was: Error: 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 'LIKE companyName = '%Mike%'' at line 1
View Replies !
Not Working
I have 4.1.10a on my local machine and the following WHERE clause works fine: Code: WHERE wk1 IN (select max(wk1) from wkpts where teamid = w.teamid) But when I upload it to my web site, I get an sytax error at 'select max(wk1) from...' My web host has MySQL version 4.0.20-standard. Can someone tell me why it would work locally, but not on my web host's server? Thank you.
View Replies !
!= Not Working?
SELECT d.cache_diff, d.cache_name, d.cache_type, d.cache_last_found FROM geocaches d LEFT JOIN logvisit ON d.cache_id=logvisit.cache_id WHERE d.cache_status = 0 && logvisit.log_type != 'Found' && logvisit.log_owner_id = 64 This returns zero rows. Its true that records in logvisit.log_type for this user are only FOUND but I am trying to display cache information for all other caches (in geocaches table) that the user did not log FOUND in logvisit. So, basically if the user logged against 3 caches and there are 10 caches available to log agains then I want to display only the remaining 7. Is JOIN the way to go on this one?
View Replies !
Working With 'SET'
I would like to know how I can work with a PHP Array and a set. How can i get all rows where a set where at least one item oft the php Array is inside the set column?
View Replies !
Mysql Not Working
im trying to install bugzilla (im on rh9) ive gotto the point where you run ./checksetup.PL. Thing is its saying it cant connect to the database - but the database is there and the settings n the config files are right.
View Replies !
Datetime Not Working
I am trying to add a date that a blog post was posted. However, the date is not changing.. it is remaining as 0000-00-00 00:00:00 This is my code: PHP Code:  $sql = "insert into entries (id, cat_id, dateposted, subject, body) values ('$_POST[id]', '$_POST[cat_id]', '$_POST[dateposted]', '$_POST[subject]', '$_POST[body]')"; mysql_query($sql); I have a feeling it is something to do with the $_POST[dateposted] as there is not actually a field in my form for dateposted
View Replies !
Why Isn't Load_file() Working
Today I learned how you're supposed to put pictures and other binary data into databases. Problem is, no matter if I use load_file('pic.jpg') or a script-side method, the value in the db ends up NULL. Why is this? I've verified it is null by running the "is null" statement. I have the picture in the same folder as the /bin dir, so the command "insert into... pic=load_file('pic.jpg') sholud work. Can anyone tell me why it's not? This doesn't just happen with pictures btw. Why won't it import? I'm doing this as root user, by the way.
View Replies !
ORDER BY Not Working?
I have a users table with first name and last name as well as several other entries. I also have a table for permissions called user_levels. For some reason, I can't get ORDER BY to return results in alphabetical order by last name. Here's the query: Code: SELECT * FROM users u LEFT JOIN user_levels ul ON u.level_id = ul.level_id ORDER BY 'u.last' Is there any reason this shouldn't work? I've done this a dozen times for other projects and never had a problem. I'm wondering if I'm code-fried and/or too close to see it
View Replies !
Rand() Not Working In Php?
I'm trying to get the rand from the mysql. When I use the MySQL Front software (SELECT * FROM keyword ORDER BY RAND() LIMIT 2, it gives me random which is working fine. But when I use the php and it doesn't give me random. What wrong with php? $query = "SELECT * FROM keyword ORDER BY rand() LIMIT 2"; $result = mysql_query($query) or die("Query failed : " . mysql_error()); echo "<PRE>"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { print_r($line); } echo "</pre>"; mysql_free_result($result);
View Replies !
Select Where Not Working
i've been testing on a select statement on mySql 4.0.18 with the following statement and it worked fine $result = mysql_query("SELECT * FROM rate WHERE 1 AND country LIKE 'Kenya' LIMIT 0, 30",$db); however i'm now using a friend's isp and they have mySql 4.0.20 and the statement above doesn't work, a select * from rate however does work.I need to select a specific country from a single table and output the results onto the screen.
View Replies !
UCASE Not Working
I have to change some rows to be uppercase. I recently changed the column I'm trying to work with to VARBINARY because I'll need to support case sensitivity soon. Before I do that I need to make everything currently in this column uppercase, so: Code: SELECT UPPER(patientID) AS pID FROM prescriptions WHERE something... gives me the expected results, but not in uppercase!
View Replies !
Working With Comments
I have never dealt with comments before and was wondering if works much like this way. Let's say I want to have comments over a article, then the comments table would be something like: commendtid | articleid | userid | username | comment | datetime Then I believe we have to query the comments table and retrieve all comments where the comment's articleid matches the article's articleid. Also maybe placing the userid and username over the comments table will spare me from new querys over the users table.
View Replies !
Working With Relevance
I'm trying to put up a search engine in my website, using MySQL fultext search capabilities. I can handle the queries, but I'm puzzled by the "relevance" parameter. What are the values I should expect in this index? I know I can get 0 (where no match is found) and value increases up to... 1? More than 1? Yes, I sometimes got values higher than 1. I even think I sometimes got higher than 3 or 4. So this is not a correlation. What is it then? How do I use it to retrieve a percentage or to draw a longer bar when results are more pertinent? Has anybody any clue about what the numbers in the "relevance" field may mean when using MATCH AGAINST ?
View Replies !
Foreign Key Not Working
I am using mysql server 5 version, and heidisql version 3.0. i am adding a foreign key to an already existing table the exact command is ALTER TABLE mailer_contact_details ADD CONSTRAINT mcd_fk FOREIGN KEY (address_id) REFERENCES addresses(id) on delete set null there is no error, and i am getting query ok, but the foreign key functionality is not happening, i am able to enter the value in the child table which is not present in the parent table, and still no error message is getting displayed i even created an index for address_id field, and then again giving the above command, still foreign key functionality is not working.. where is the problem?
View Replies !
Max_allowed_packet Not Working
NOt sure if this is the right forum even for this question but on my unix shell box i am trying to adjust the max_allowed_packet to the database because i'm transfering a 145mb sql file. I have mysql 4.23 on the box and I have already tried the command msq> --max_allowed_packet=200m and it doesn't do anything.
View Replies !
My.ini Configurations Not Working
I just start with mysql. I read that in my.ini in the windows directory, I can adjust variables. I added for example basedir=D:/mysql. When I now want to check if the server takes this change I tried mysql>show variables like "%base%"; I can see the variable but with the content d:programme/xampp/mysql. same happend with the variable server-id=2 in the my.ini. On the server I found server_id=0. Is it possible that the my.ini file was not executed during startup of mysql? How can I check that my.ini is respected?
View Replies !
My Cursors Are Not Working
I have this code: create procedure pruebacursor () begin declare a char(5); declare done int default 0; DECLARE cur1 cursor for select substring(link,1,3) from animals; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; open cur1; repeat fetch cur1 into a; insert into names values (a); until done end repeat; close cur1;
View Replies !
Recordcount Not Working = -1
Why wont this work? The Recordcount is > 90. I can get the records, do select, update, and delete. WinXP pro ASP 3.0, VBscript MySQL ODBC 3.51 Driver MySQL 5 code: <% Set DBConn = Server.CreateObject ("ADODB.Connection") DBConn.Open emailDSN set RS = Server.CreateObject("ADODB.Recordset") sqlQuery = "select * from web_email" RS.Open sqlQuery, DBConn, 3, 1 Response.Write("<h1>Recordcount = " & RS.Recordcount & "</h1>") RS.close set RS=nothing DBConn.Close set DBConn = nothing %>
View Replies !
AUTO_INCREMENT NOT WORKING
I have set all my foreign keys to AUTO_INCREMENT, but when I insert a new record I have to insert the next number manually. Does anyone know why the number isnt automatically incrementing?
View Replies !
MATCH AGAINST Not Working
I am trying to return results with the MATCH AGAINST queries but for some reason the results are inconsistent, i have the FULLTEXT index for the appropriate field but it seems sketchy. I've instead been using a regular expression in my query for time being, but as you can imagine this is getting highly impractical as the database grows and grows every minute. Here is an example:
View Replies !
Limit Is Not Working
I have created a trigger: I am using if not exists(select field1,field2 from table1 where name1=?name and section1=? section limit 1) then .....start insert process......on a certain table end if; I am using limit 1 to return only 1 row to start insertion, but it seems that limit doesn't work when we are using if not exists... If anyone has any other suggestion, it would be great. Also I used: but it doesn't work with this var variable: ///////////////////////////////////////////////////////////////////////// declare var varchar(400); set var=null; set var=select field1,field2 from table1 where name1=?name and section1=? section limit 1; //why set it doesn't work here, to check nullity if(var=null) then //we check nullity like this?????????? insert into table 2(field11,field22) values( 'x','y'); end if;
View Replies !
PHP Not Working With MySQL
Just starting out and I have PHP, Mysql, and Apache installed. PHP and MYSQL dont seem to be working together. Everything seems to be running fine and I edited the httpd.conf in order to allow apache to process php requests. This is what I am doing when I start mysql... c:/net start mysql c:/mysql/bin/mysql -u root -p Then I log in and it accepts the log in. When I create a sample php program like "Hello", it works. But when I do a simple sql connect: <?php $conn = mysql_connect("localhost","root","tntboy"); echo $conn; ?> I get nothing! Nothing at all comes up on the screen. Any ideas? Why is it not connecting to the database?
View Replies !
RPAD Is Working
I am working with Japanese data and the character set of my database is utf8. I want to store some Japanese names in Database with space padded at end. so I am using this syntax: set f_name = rpad(f_name,40,' '); its not working for Japanese Names while the same is working fine with English names.
View Replies !
Working With Dates...
How would I finish a query like this: SELECT * FROM table WHERE datetime_field BETWEEN NOW() AND <tonight at midnight> I'm not sure how to calculate "tonight at midnight" in a format that can be compared to the result of NOW(). Any ideas?
View Replies !
CONCAT Not Working
This query seems structurally correct to me but the mysql query builder says it is not! SELECT CONCAT (firstname, lastname) as fullname FROM candidates WHERE candidateid = 12345 Any ideas whats wrong with it?
View Replies !
SELECT Not Working
SELECT U.*, G.Name AS groupname, C.FirstName, C.LastName, (Select count * from Account Acc where Acc.User_ID = U.User_ID) as Account_ID FROM ((Users U LEFT JOIN Groups G ON U.Group_ID = G.Group_ID) LEFT JOIN Customers C ON U.Customer_ID = C.Customer_ID) WHERE 1 = 1 The above returns a 1064 Error on SUSE Linux MySQL 4.0.18. But on WinXP MySQL5.0 all runs fine. Can anyone give me advice on how to fix the above to work? If I remove the second select from the query all works fine.
View Replies !
Mysqldump Not Working
I created a backup of my databse using: mysqldump --opt mydb > "c:ackup.sql" when I try to restore the database with mysql mydb < "c:ackup.sql" I get an error saying 'c:ackup.sql' incorrect database name.....
View Replies !
Working With Images.
mysql 4.0.24 I have an existing database that has image information. It stores the path, filename and some other items. I'd like a way to store the thumbnail of the image in the database. I still want the image as a file. I'm having troubles figuring out exactly how to add the thumbnail. I can manually use convert to create the thumbnail. What I've found on the net are just methods for uploading a single image, then adding it to the db. This will be a one time shot to update the DB. Pick a language, I'll figure something out if I can get the key to this.
View Replies !
Still Cannot Get MySQL Working
I have the MySQL server running. I have installed MyODBC. I cannot get either the Migration Toolkit or the MySQL Administrator to connect. I've turned off the Windows Firewall and put exclusions in Norton to allow port 3306 and MySQL. With the MySQL Administrator I get an error message of Cannot Connect to MySQL Server on localhost. With the Migration Toolkit I get this error log: Code:
View Replies !
INSERT Not Working
I have the following: mysql_query("UPDATE tblmembers SET loginDateTime = NOW(), MemberApproved='A' WHERE ID = '".$_SESSION['mid']."'"); mysql_query("INSERT INTO tbllogins (MembersID, loginDate) VALUES('".$_SESSION['mid']."', NOW()"); The top line works perfectly, UPDATE'S both columns in table tblmembers, the second line doesn't work at all... It isn't INSERTing any data into tbllogins...
View Replies !
UPDATE Not Working.
The page that sends information to the following page has a series of text inputs. The name of the input corresponds to the name of the column in the in the mysql table. There's also a hidden input, "id", that supplies the unique id number of the entry. My script to enter data into the table and to view the table work fine. The update script runs without any errors, but doesn't alter any values. I also tried printing the value of $query and everything appears to be fine. Anyone know why this is happening? Code:
View Replies !
PhpMyAdmin Isn't Working
OK, phpMyAdmin can't create a table, it produced this code: CREATE TABLE `photos` ( `name` VARCHAR( 255 ) NOT NULL , `desc` TEXT( 500 ) NOT NULL , `url` VARCHAR( 255 ) NOT NULL , `cat` INT( 4 ) NOT NULL , `id` INT( 4 ) NOT NULL , INDEX ( `name` , `desc` , `url` , `cat` , `id` ) ) and 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 '(500) NOT NULL, `url` VARCHAR(255) NOT NULL, `cat` INT(4) NOT N .
View Replies !
Working With Dates
After taking a break from this project for a bit I find myself confused about how to pull specific date periods from a database. The timestamps are stored in UNIX format. I'm creating a report builder to pull details about issues that my techs have entered (ISP support center). I can pull reports from calls entered today or yesterday, but getting them from say, mon-fri of last week is throwing me for a loop. Can anyone point me in the right direction? This is what I have thus far: //echo "DateRange: ".$daterange."<br>"; // begin building our query $query = "SELECT * FROM issues WHERE "; // the value passed in date range is how we modify the date lookup in our SQL query if ( $daterange == "today" ) { $timestamp = strtotime("today"); $query .= "DATE_FORMAT(CURDATE(), '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');"; } else if ( $daterange == "yesterday" ) { $query .= "DATE_FORMAT(CURDATE() -1, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');"; } else if ( $daterange == "thisweek" ) { $query .= "DATE_FORMAT(CURDATE() -7, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');"; } else if ( $daterange == "lastweek" ) { $query .= "DATE_FORMAT(CURDATE() -14, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');"; } else if ( $daterange == "thismonth" ) { $query .= "DATE_SUB(CURDATE(),INTERVAL 1 MONTH) >= issue_submitted_date;"; } else if ( $daterange == "lastmonth" ) { $query .= "DATE_SUB(CURDATE(),INTERVAL 2 MONTH) >= issue_submitted_date;"; } else if ( $daterange == "thisyear" ) { $query .= "DATE_SUB(CURDATE(),INTERVAL 1 YEAR) >= issue_submitted_date;"; } else if ( $daterange == "lastyear" ) { $query .= "DATE_SUB(CURDATE(),INTERVAL 2 YEAR) >= issue_submitted_date;"; }
View Replies !
Select As Where Not Working!
SELECT CONCAT(RIGHT(dt,4),'-',MID(dt,4,2),'-',LEFT(dt,2)) AS dateavail FROM atable WHERE dateavail = ��-06-01' (dt is a date in format dd/mm/yyyy - the concat bit works fine) Microsoft OLE DB Provider for ODBC Drivers error ?e14' [MySQL][ODBC 3.51 Driver][mysqld-4.1.19-standard-log]Unknown column 'dateavail' in 'where clause' Also, if I get it to work, how do I convert the 2 strings to date so I can look for < and > ?
View Replies !
DISTINCT() Not Working
Does anyone have any idea why i can't get this to return distinct values? PHP SELECT DISTINCT(agi.userid), agi.imageid, agi.title, agi.dateline, agi.catid, agi.thumbname FROM a_gallerys_images agi WHERE agi.valid = 1 AND agi.open = 1 AND agi.catid = 2 ORDER BY agi.dateline DESC LIMIT 4
View Replies !
Doing A JOIN Not Working
I have 2 tables, one contains columns that holds the ID to the other table so eg Table 1 (images) id | imagepath -------------- 1 dir/hair.png 2 dir/face.png Table 2 (buildImages) userid | hair | face 1 1 2 So i'd just like to retrieve a row like dir/hair.png, dir/face.png where userid = 1 does that make sense? Oh, the title of this thread is kind of wrong, shoudl be how do I do this join :P
View Replies !
Working With SET Type ...
I have this SET(Ƈ',ƈ',Ɖ',Ɗ') on a column. Suppose that the colunm has the data (1,2,4) Now i want to select rows that contain either 1 or 2 or 4. What is the fastest way to do this ?
View Replies !
Queries Not Working
its been ages since I have worked with MySQL so I am very rusty and am just trying to patch together loads of old code at the moment. $dbh=mysql_connect ("localhost", "*********", "************") or die ('I cannot connect to the database because: ' . mysql_error()); mysql_select_db ("*********"); $result = mysql_query("SELECT * FROM Update WHERE number=Ƈ'); $row = mysql_fetch_array($result); echo $row['title']; mysql_close($dbh); I keep getting this message: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dcaggorg/public_html/update/index.php on line 18
View Replies !
|
TRACKER
