ANSI Isolation Level vs. InnoDB consistent read implementation
Hi, Guys I think I got puzzled about the transaction isolation level and the InnoDB consistent read implementation. My understanding about the ANSI isolation level are: 1. READ-COMMITTED is to protect against Lost Updates, Dirty Reads, and NOT protect against Nonrepeatable Reads and Phantoms. 2. REPEATABLE-READ is to protect against Lost Updates, Dirty Reads, Nonrepeatable Reads, and NOT protect against Phantoms. 3. SERIALIZABLE is to protect against Lost Updates, Dirty Reads, Nonrepeatable Reads, and Phantoms. 4. Here Nonrepeatable Reads is also called Consistent Reads. While the isolation level in InnoDB implemenation are little different from the ANSI standard. 1. READ-COMMITTED and REPEATABLE-READ in InnoDB can protect against Phantom, since innodb has next-key locking support. Right? 2. If so, REPEATABLE-READ can protect against the all four secenaria. Why need SERIALIZABLE again, or what is the purpose of SERIALIZABLE to convert all plain SELECT statements to SELECT ... LOCK IN SHARE MODE? 3. Innodb has consistent non-locking read mode. Here the concept of consistent non-locking read is not the concept Nonrepeatable Read that consistent read. They are different terms. Right? Am I understanding the InnoDB right? Thanks in advance. Thx, Xuekun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use LIKE for detecting numbers with commas?
LIKE '%,8,%' ? Probably not as elegant as you were looking for, but it works :) Colin On Monday 06 July 2009 21:31:51 Highviews wrote: Hi, I have numbers separated with commas saved into a TEXT Field, for example: ROW1: 10,5,2,8, ROW2: 2,7,9,65 ROW3: 99,100,55,10,88, etc... Now i want to make a query like this: SELECT * FROM table where numbers LIKE '%8%'; The above query when executed returned the following: ROW1: 10,5,2,8, ROW3: 99,100,55,10,88, Where it should only return ROW1: ROW1: 10,5,2,8, But it is also detecting '88' from ROW2. Any solution to this? I only want exact numbers to be searched out. Thanks! --- http://www.visualbooks.com.pk/ -- Your love life will be... interesting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Deadlock question
Hi, I have the following table and two concurrent jobs, trying to insert data into it. They deadlock on accessing an index which shouldn't collide (the date is part of the index, and the jobs process data for different dates). This is mysql 5.0.51a-24+lenny1~bpo40+1-log. Any ideas? Is this likely to not happen with 5.1? CREATE TABLE `test` ( `kunde` int(11) NOT NULL default '0', `quelle` int(11) NOT NULL default '0', `datum` int(10) unsigned NOT NULL default '0', `ziel` tinyint(4) default NULL, `pakete` bigint(20) default NULL, `bytes` bigint(20) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, KEY `qkd` (`kunde`,`datum`), KEY `qqd` (`quelle`,`datum`), KEY `timestamp` (`timestamp`), KEY `datum` (`datum`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1 *** 1. row *** Status: = 090612 13:52:17 INNODB MONITOR OUTPUT = Per second averages calculated from the last 14 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 2911205, signal count 2566010 Mutex spin waits 0, rounds 827176046, OS waits 1619184 RW-shared spins 8458591, OS waits 749052; RW-excl spins 15010669, OS waits 188067 LATEST DETECTED DEADLOCK 090612 8:35:15 *** (1) TRANSACTION: TRANSACTION 0 534404698, ACTIVE 44 sec, process no 4871, OS thread id 1229306208 inserting mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 3024, undo log entries 2 MySQL thread id 2773037, query id 633323918 acct1.backup.noris.net 10.1.1.95 kunde update insert low_priority into test set kunde=9, quelle=-715188982, datum=20090602, ziel=119, pakete=192, bytes=10752 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 692344 n bits 456 index `qkd` of table `einzel/test` trx id 0 534404698 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 800c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 0337b2b8; asc7 ;; *** (2) TRANSACTION: TRANSACTION 0 534294520, ACTIVE 4074 sec, process no 4871, OS thread id 1249306976 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 35927 lock struct(s), heap size 6256624, undo log entries 411437 MySQL thread id 2763571, query id 633351909 acct1.backup.noris.net 10.1.1.95 kunde update insert low_priority into test set kunde=9, quelle=-1066572043, datum=20090601, ziel=100, pakete=64, bytes=4096 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 692344 n bits 416 index `qkd` of table `einzel/test` trx id 0 534294520 lock_mode X locks gap before rec Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 800c; asc ;; 1: len 4; hex 013217cc; asc 2 ;; 2: len 6; hex 0337b2b8; asc7 ;; Record lock, heap no 347 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd1af4; asc ;; Record lock, heap no 348 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25bd; asc % ;; Record lock, heap no 349 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25be; asc % ;; Record lock, heap no 350 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25c3; asc % ;; Record lock, heap no 351 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25c4; asc % ;; Record lock, heap no 352 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25c5; asc % ;; Record lock, heap no 353 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd25cb; asc % ;; Record lock, heap no 354 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd2633; asc 3;; Record lock, heap no 355 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd2634; asc 4;; Record lock, heap no 356 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8009; asc ;; 1: len 4; hex 01328ee9; asc 2 ;; 2: len 6; hex 08cd2635; asc 5;;
Re: How to use LIKE for detecting numbers with commas?
Thats Great!
Thanks a Ton!
:)
On Mon, Jul 6, 2009 at 7:55 PM, Dan Nelson dnel...@allantgroup.com wrote:
In the last episode (Jul 06), avrom...@whyisitthat.com said:
From: Highviews highvi...@gmail.com
I have numbers separated with commas saved into a TEXT Field, for
example:
ROW1: 10,5,2,8,
ROW2: 2,7,9,65
ROW3: 99,100,55,10,88,
etc...
Now i want to make a query like this:
SELECT * FROM table where numbers LIKE '%8%';
Any solution to this?
I only want exact numbers to be searched out.
It's ugly, but this should work:
SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
numbers LIKE '%,8'
Even better:
SELECT * FROM table WHERE find_in_set('8',numbers);
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set
--
Dan Nelson
dnel...@allantgroup.com
Triggers For Radius DB
Good Day. I hope someone can assist me. I have a freeradius server running off mysql. Now I would like to use triggers to negate some of the traffic logged within it. I tried to use following as trigger.. Create Trigger ftp BEFORE UPDATE ON radacct FOR EACH ROW BEGIN UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE NASPortId=21; UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE NASPortId=21; END; And Many variants of that but only ends up locking the db in someway with messages such as. Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update table 'radacct' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.' From Freeradius. I have managed to get triggers working to a separate db altogether but not from the same db to the same db. What am I doing wrong? Regards. Marcel Grandemange
Stored Procedures from SQL Server
I am working on moving a database from SQL Server to MySQL 5. Tables, data, etc I already know. I have some procedures that i would like to move as well. Does anyone have examples on creating stored procedures? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Stored Procedures from SQL Server
Does anyone have examples on creating stored procedures? There are quite a few in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB - ML wrote: I am working on moving a database from SQL Server to MySQL 5. Tables, data, etc I already know. I have some procedures that i would like to move as well. Does anyone have examples on creating stored procedures? Best, -Jason No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.375 / Virus Database: 270.13.7/ - Release Date: 07/07/09 05:53:00
Fw: General MySQL Question
- Forwarded Message From: MySQL Web Team webmas...@mysql.com To: christudas_...@yahoo.com Sent: Wednesday, July 8, 2009 3:46:09 Subject: Re:General MySQL Question Thank you for contacting MySQL, You are likely to get a more detailed response to your question if you ask on the forums site at http://forums.mysql.com/ or on an appropriate mailing list at http://lists.mysql.com/ Hope this helps, MySQL Web Team === Your query was: Hi, I have an existing database for one of our elearning portals. This support request is to give us information on how an already existing database can be encrypted? Is there a query that can encrypt the database? we are planning to use the ENCODE() and DECODE()for encoding the data into the database and decrypting the data while retrieving information from database. Pls do let us know. Thanks Regards, Christy -- MySQL http://www.mysql.com/ If you wish to unsubscribe from future email communication from MySQL please visit http://www.mysql.com/unsubscribe and enter your email address. If you do not wish to receive email communications from Sun, please click here http://www.sun.com/cgi-bin/sun/dne/ or forward this message to rem...@sun.com. Get your new Email address! Grab the Email name you#39;ve always wanted before someone else does! http://mail.promotions.yahoo.com/newdomains/aa/
Re: Triggers For Radius DB
In the last episode (Jul 07), Marcel Grandemange said: I hope someone can assist me. I have a freeradius server running off mysql. Now I would like to use triggers to negate some of the traffic logged within it. I tried to use following as trigger.. Create Trigger ftp BEFORE UPDATE ON radacct FOR EACH ROW BEGIN UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE NASPortId=21; UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE NASPortId=21; END; And Many variants of that but only ends up locking the db in someway with messages such as. Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update table 'radacct' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.' You don't want to UPDATE the table, since as you have seen it will cause an error. The only row you can change in the table being modified in a trigger is the active row itself, and you must change its values by SETting NEW.fieldname: IF NEW.NASPortID = 21 THEN SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets); SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets): END IF; -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
