trigger mysql 5
Hi all! server version: 5.0.18 I attempted to create a trigger with statement and update, it does like this: CREATE TRIGGER update_trans AFTER UPDATE ON main_trans FOR EACH ROW BEGIN IF OLD.trans_status='INITIAL' and OLD.trans_state='INITIAL' THEN update trans set trans_status=NEW.trans_status, trans_state=NEW.trans_state where main_trans_id=NEW.main_trans_id; END IF; END; I 'm getting an error after : update trans set ... I tried to insert BEGIN update trans set .. END Note that I'm creating the trigger directly in phpmyadmin, I already created succesfully a trigger but it was really a simple one... I did try the delimiter "$$" or any other delimiter of my choice, but it doesn't accept the DELIMITER declaration before my create trigger Nothing works Any tips!? Is that possible to do this kind of trigger!? Tks for your answers, I appreciate!!
Re: insert html into database
Hi steve, you just have to double quotes your insert! On 5/18/06, Steve <[EMAIL PROTECTED]> wrote: Hi everyone... how do I enter html code into my database???
lock the row selected by a session and lock those rows for other sessions
Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock the row selected by a session and lock those rows for other sessions
Select for update is not working like the way I expected it: FIRST SESSION: mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) So, you notice I didn't COMMIT those rows... Check the second session opened: mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ Same thing, I dont want anyway other session to be able to get that selection until I commit. Any other suggestion? Michael Dykman wrote: SELECT FOR UPDATE On Feb 4, 2008 4:58 PM, Frederic Belleudy <[EMAIL PROTECTED]> wrote: Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- 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]
Re: lock the row selected by a session and lock those rows for other sessions
You said: It is not locking the entire table. It's locking the rows you're selecting. If you don't want the second session to hang and wait, then you need to tell it to lock different rows. Well if you read my message, I dont want another script to select the rows that are selected by another opened session but i want other script to be able to select other rows without hangin on Got me? Yes it's like a pool... Baron Schwartz wrote: (Re-CCing the MySQL list) It is not locking the entire table. It's locking the rows you're selecting. If you don't want the second session to hang and wait, then you need to tell it to lock different rows. It might help if you explain what you're trying to accomplish. It sounds like you're trying to build a message queue or something, which is a problem that has been solved already. On Feb 5, 2008 9:44 AM, Frederic Belleudy <[EMAIL PROTECTED]> wrote: 1) damn, I had to recompile myssql because the innodb option wasnt enabled 2) ok now it seems to work almost perfectly. I'm doing the same query on the first session but the second one is waiting for the other session to commit after the selection. Is there a way to tell mysql not locking the table entirely, just the row from the selection? Tks a lot for your help!! Baron Schwartz wrote: 1) is the table InnoDB? 2) is AUTOCOMMIT on? On Feb 5, 2008 8:44 AM, Frederic Belleudy <[EMAIL PROTECTED]> wrote: Select for update is not working like the way I expected it: FIRST SESSION: mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ 5 rows in set (0.00 sec) So, you notice I didn't COMMIT those rows... Check the second session opened: mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> select video_id from videos_innodb where state='QUEUE' limit 5 FOR UPDATE; +--+ | video_id | +--+ |1 | |2 | |3 | |4 | |5 | +--+ Same thing, I dont want anyway other session to be able to get that selection until I commit. Any other suggestion? Michael Dykman wrote: SELECT FOR UPDATE On Feb 4, 2008 4:58 PM, Frederic Belleudy <[EMAIL PROTECTED]> wrote: Hi there, I'm new with innodb and I'm not sure it's good to go with innodb for my personnal goals. Ok, let's assume I 've a table and want to select the first 10 rows from that table but I want to be sure that no other scripts will select the same rows I've previously got by the first script. How can I do that? my table contains one primary key. Let's say id is the column name. So my first script is running and select the ids: 1, 2, 3 10 Then that script will play with the returned ids. In the same time, I'm running a second script and do the same select. But I don't want him to get the first 10 ids. The only thing I can think about is to lock WRITE my table. I taught innodb was able to automatically lock the selected rows and not allowed any other script to get the same rows until it's commited... Tks -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
locking rows with innodb
Hello, I'm having some problems to figure out how I could handle my
problem with innodb.
my table is innodb engine:
CREATE TABLE `videos_innodb` (
`video_id` int(10) unsigned NOT NULL auto_increment,
`client_id` int(10) unsigned default NULL,
`client_id_upload` int(11) NOT NULL default '0',
`state`
enum('GET','QUEUE','AVAILABLE','UPLOAD','ERROR','QUEUE_TRANSCODING')
default 'GET',
`input_file_type_id` int(10) unsigned default NULL,
`output_file_type_id` int(10) unsigned default NULL,
`input_file_name` varchar(150) NOT NULL,
`output_file_name` varchar(150) NOT NULL,
`date_inserted` date default NULL,
`time_inserted` time default NULL,
`date_available` date default '-00-00',
`time_available` time default '00:00:00',
`time_start` time NOT NULL,
`time_end` time NOT NULL,
PRIMARY KEY (`video_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12916 DEFAULT CHARSET=latin1
Ok, the column state is really important for my example. I'd like to
start multiple times the same script to convert videos. But I dont want
them to select the same rows for each sessions that select where
state='QUEUE'.
so I tried to use select ... for update with 2 different sessions. I've
set in each of them autocommit=0;
session 1
session 2
set @@autocommit=0;
set @@autocommit=0;
begin;
begin;
select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
=> returns me 10 videos with the state='QUEUE'
select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
=> it's waiting for the first to session to commit, so I cannot get
other videos with the same state!!
commit;
=> I get 10 video_id
How can I tell mysql to lock only rows that are selected and allow other
sessions to query the table without be locking on the entire table?
Tks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql slave replication with master-retry-count
hello, I would like to set that option in mysql 5.0.45 on my slave server: |master-retry-count=800 my slave status: Slave_IO_State: Queueing master event to the relay log Master_Host: xx.xx.xx.xx Master_User: fmRepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000132 Read_Master_Log_Pos: 353589900 Relay_Log_File: relay-bin.34 Relay_Log_Pos: 26881417 Relay_Master_Log_File: mysql-bin.000154 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 474572276 Relay_Log_Space: 21673882767 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Yes I know the SQL_THREAD is off but this is because of a script that keeps a reasonable delay. Anyway, so I put those options in my.cnf : master-connect-retry=30 master-retry-count=500 I stoped the slave then deleted the master.info file connect-retry is working fine when I do a : show slave status \G; but master-retry-count doesn't show up. I tried to get the info with: show variables; No success... is there a way to be sure that the value is set properly. Let me know! Tks | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql merge table
hi there, i dont know if i did something wrong with the merge table or didnt understand the purpose of it so here is what i did, i ve created 2 tables with the same definition (keys and colums) then ive create the merge table and replace the primary key by an index key and every other indexes remain the same. everything is fine at this stage. So im doing a simple search on the merge table and it returns nothing! if i do a select * from merge_table, it returns all the fields. So im wondering why i dont get a result when i specify a where definition and im sure that it should return a set of results? Any idea? Tks -- Frederic Belleudy Programmer PWIdeas ICQ #: 253-372-030 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
