[PHP-DB] Re: time field query problems.
Hi. On Mon 2002-07-29 at 14:41:30 -0400, [EMAIL PROTECTED] wrote: > Sorry to repost but I typed in the wrong sql statement in my previous post. Ah. Okay. > For some reason the below statement is not working. Can anyone tell me why? > > Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE < now() > AND ORDER_NO = '5' AND EDIT_LOCK > 0; Regardless of the original problem, you should use EDIT_LOCK < NOW() - INTERVAL 10 MINUTE because this variant has no expression on the left side and therefore could use an index, if there is one on EDIT_LOCK (MySQL does not optimize expressions, in the few cases where this would be possibible). > -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is > NULL. Oh. Is it really a TIME field, not TIMESTAMP? In this case you would compare a time (without date) with a whole datetime value from NOW(). These values cannot be compared reasonable. Additionally +- INTERVAL only works on DATE or DATETIME/TIMESTAMP values, otherwise you have to use DATE_SUB/DATE_ADD. In this case, you would need something like SELECT EDIT_LOCK FROM ordmaster WHERE EDIT_LOCK < DATE_FORMAT( NOW() - INTERVAL 10 MINUTE, "%T" ) AND ORDER_NO = '5' AND EDIT_LOCK > 0; This does not handle the special cases on day change, but I presume that this already had been thought of, or else a TIME instead of a DATETIME field makes no sense. Greetings, Benjamin. -- [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Table Locking...
Hi. Please stop cross-posting and use the appropriate forum. On Wed, Nov 21, 2001 at 03:27:23PM -0700, [EMAIL PROTECTED] wrote: > > (PHP-DB folks, ignore this top question - unless you want to answer) > > Which type of DB/Table provides table locking while a process is > altering data? ISAM and MyISAM. BDB has page locking. InnoDB has row locking. > I don't care for locking while using SELECTs, but I do > need locking when something's being INSERTed or UPDATEd, so I won't get > two processes trying to do the same darned thing at the same time. Well, the above was about implicit locking. If you use LOCK TABLE, you will always get the whole table locked, I think. > (MySQL folks, ignore this bottom question - unless you want to > answer) > > I'm trying to go through a table and check for a particular > information. If it doesn't exist, then go ahead and issue an INSERT > with the relevant data. The next time another process comes along, > it'll obviously find the information and issue an UPDATE instead. But, > what happens if I get two processes running at the same time, trying to > find information that does not exist yet? You will get concurrency problems. More serious answer: There are several solutions to this problem. One is the beforementioned LOCK TABLE. > If both issue a SELECT and find the information not there, both will > try to issue an INSERT, where really I want one of those processes to > wait for the first one to finish doing whatever it needs to do, prior to > continuing. (all of this is being done in PHP by the way). Does anyone > have any suggestions for this scenario (or am I just blowing smoke out > my arse and there's something else, internally, that I'm not aware of?) No. You have to program your application to take care of this. How should MySQL know that are want to issue a INSERT after the SELECT? > When (and how) to issue some sort of "wait" state for one process while > the other's finishing what it needs to do, then restart the second > process (which should re-check for existing information, and not blindly > issue another INSERT right after the first one). Another method is assure uniqueness via keys and to simply issue the INSERT and retry with an UPDATE if the INSERT failed (because the row already existed). In this case you don't need LOCK TABLE. Bye, Benjamin. -- [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: Connection closing problem!
Hi. It looks like you are using persistent connections with SQL server, but not with MySQL (time_wait and close_wait are states of a TCP connection after it was closed by the appliction). I don't know much about PHP, but there is somewhere a option to influence whether you want persistent connections or not. Second, what is MySQL telling (mysqladmin processlist with MySQL-root privileges). Are there sleeping connections (persistent connections) or not (no persistent connections). Bye, Benjamin. On Mon, Feb 05, 2001 at 12:26:38PM +1100, [EMAIL PROTECTED] wrote: > Hi, > Does anyone know of or have a problem that means that the > HTML server will not receive the message back from the MySQL > server to say close the connection. > The two servers are on two seperate machines. > The html server is Apache using the PHP scripting language. > The error messages I am getting, not so much error messages but > messages, when I do a netstat on the html machine state that alot > of the sql connections are time_wait or close_wait. This should > cause no problem I here you say but when the majority of the html > server connections are on time_wait or close_wait then the site > starts to slow right down. And eventually cause database server > busy errors for users. On the SQL server the netstat results appear > to be fine with the majority of them being Established. Can any > shed some light on this problem or even tell me of a way to fix it. If > any questions about the problem need to be asked I will try and > answer them. Thanks in advance. > > Drew > > Andrew Toussaint > Richardson-Shaw Pty Ltd > [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
