RE: Can we make some C, Java based "Mysql App Server"
Woah... scope creep.. hehehe Actually, I've done a little something along these lines with the MySQL C++ API (Unfortunately in VC++). I've found it useful for throwing together apps against the same db quickly. This, however, would be even better... If anyone decides to shoot forward on this, please let me know! My time is somewhat constrained right now (moving, trying to get a home fixed up, a child on the way), but I would be more than willing to donate some time to further this along. Thanks, -Mike Gerst SBC Services One SBC Center St. Louis, MO 63011 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2003 12:54 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can we make some C, Java based "Mysql App Server" hmm a middleware product would be good , extra processing=time ? also stored procedures would be excellent , also maybe a middleware product to load balance between slave and master and delegate slaves if a master goes down is there such a thing ? >>> Nitin Nanivadekar <[EMAIL PROTECTED]> 04/03/03 04:41pm >>> Dear Friends, We all know Mysql is a beautiful database engine but there are some things missing. Even Mysql team acknowkedges the need in their ToDo list. i was thinking of designing a Java, C based application server that acts as a gridge bewween the applications & the database. This server can take care of various aspects & especially as a Pre-Processor for aome nice queries such as "Connect By", Stored procedures, triggers etc. Mysql team will require beyond ver 5.0 to give us all this. pls comment :) -Nitin __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- 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]
RE: How Many
Just noticed this thread.
Here is another method, still using the 'userid' method, but slightly
differently; it claims a job immediately. You then have to run a select by
your own 'userid' to find out what you claimed:
Assumptions:
This assumes that the following table exists, and that unique_job_id is an
int, and a primary key. This algorithm also assumes that the jobs should be
working in ascending order (i.e. job 1234 gets processed before 1235)
Also assumes using MySQL Server 4.1 (won't work with 3.xx.xx)
TABLE WorkTable{
userid varchar ALLOW NULL
unique_job_id int NOT NULL PRIMARY KEY
}
Statements:
UPDATE WorkTable
SET userid = 'my_unique_name'
WHERE userid=NULL and
unique_job_id=( SELECT unique_job_id
FROM WorkTable
WHERE userid=NULL
ORDER BY unique_job_id ASC
LIMIT 1);
SELECT unique_job_id
FROM WorkTable
WHERE userid='my_unique_name';
This help a bit? You may also be able to use an API function like
get_last_update or mysql_affected_rows to gather information on what the
UPDATE affected rather than another select. This was just quick and
generic.
-Mike
-Original Message-
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 02, 2003 11:12 AM
To: gerald_clark
Cc: MySQL
Subject: Re: How Many
last man wins.
- Original Message -
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Cal Evans" <[EMAIL PROTECTED]>
Cc: "MySQL" <[EMAIL PROTECTED]>
Sent: Wednesday, April 02, 2003 11:10 AM
Subject: Re: How Many
> AChecks for empty
> B Checks for empty
> AUpdates
> AReads ( and owns record )
> BUpdates
> BReads ( and owns record )
>
> Now What?
>
> Cal Evans wrote:
>
> >No. You check for empty. update, check again to make sure you now own
it.
> >The userid is either going to be you OR someone else. not both. It it's
you
> >then you own the record. If it's someone else then someone slipped in
and
> >grabbed it from you.
> >
> >=C=
> >- Original Message -
> >From: "gerald_clark" <[EMAIL PROTECTED]>
> >To: "Cal Evans" <[EMAIL PROTECTED]>
> >Cc: "Ed Kiefer" <[EMAIL PROTECTED]>; "MySQL" <[EMAIL PROTECTED]>
> >Sent: Wednesday, April 02, 2003 9:22 AM
> >Subject: Re: How Many
> >
> >
> >
> >
> >>Cal Evans wrote:
> >>
> >>
> >>
> >>>Ed,
> >>>
> >>>1: It depends on your hardware. I don't think MySQL imposes a
> >>>
> >>>
> >restriction.
> >
> >
> >>>2: It doesn't work that way. It's not like VFP or Access where you
> >>>
> >>>
> >'edit' a
> >
> >
> >>>record. You can SELECT the contents of a record, you can manipulate
them
> >>>
> >>>
> >and
> >
> >
> >>>then you can UPDATE that record (assuming you have a primary key) but
the
> >>>last person to issue an update is the one who wins.
> >>>
> >>>One thing I've done in the past is use 'soft locks'. Basically if you
> >>>
> >>>
> >HAVE
> >
> >
> >>>to insure that while someone has requested the right to edit a record,
no
> >>>one else can do it you have to enforce it in your own software.
> >>>
> >>>A soft lock is simply a couple of fields in a record. usually a userID
> >>>
> >>>
> >and a
> >
> >
> >>>timedate field. When someone requests permission to edit a record I
do:
> >>>
> >>>1: Make sure the userid field is empty
> >>>2: UPDATE the record with this users userid and the current timedate.
> >>>3: SELECT the record again and make sure that this user's userid is the
> >>>current one. (Keeps someone from slipping in on ya.)
> >>>
> >>>
> >>>
> >>There is still a window of opportunity for an error here.
> >>You can both check for empty, then both update and read.
> >>Now two users believe they have the record.
> >>Update therecord set userid=concat(userid,'|','me') where id=thisnumber;
> >>select userid from the record where id=thisnumber.
> >>
> >>Now:
> >>If the user is '|me' , I am the owner.
> >>If it is '|me|someoneelse' , I am still the owner.
> >>If it is '|someoneelse|me', I am not the owner.
> >>
> >>
> >>
> >>>4: Allow them to edit the record
> >>>5: Commit the record and clear the userid.
> >>>
> >>>If there IS a userid already on the record, I check the timestamp. If
> >>>
> >>>
> >it's
> >
> >
> >>>more than x time units (10 minutes, 2 hours, 3 days, etc) old, I
> >>>
> >>>
> >invalidate
> >
> >
> >>>the lock by going to step 2 anyhow.
> >>>
> >>>Clear as mud?
> >>>
> >>>=C=
> >>>- Original Message -
> >>>From: "Ed Kiefer" <[EMAIL PROTECTED]>
> >>>To: "MySQL" <[EMAIL PROTECTED]>
> >>>Sent: Tuesday, April 01, 2003 7:40 AM
> >>>Subject: How Many
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> How many people can be accessing the same mysql database at the same
>
>
> >time?
> >
> >
> Can several people work on the same record at the same time?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mys
RE: Confused about network traffic on mysql port
Somebody got control of mysql, or your rooted? -Original Message- From: Gary Huntress [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 1:29 PM To: [EMAIL PROTECTED] Subject: Confused about network traffic on mysql port I have noticed on many occasions some extensive traffic on my internal network that I cannot explain. Below you will see two sets of tcpdump traces. I have a mysql server running on my internal host named "herzegbol" and a windows 98 host named shelbyville This trace is when the MySQL server is running: 14:33:45.886159 eth1 > herzegbol.mysql > shelbyville.2333: S 700834979:700834979(0) ack 2360059956 win 5792 (DF) 14:33:46.156126 eth1 > herzegbol.mysql > shelbyville.2311: S 703613196:703613196(0) ack 1969309172 win 5792 (DF) 14:33:47.010646 eth1 > herzegbol.mysql > shelbyville.2345: S 697677373:697677373(0) ack 2546308254 win 5792 (DF) 14:33:47.246107 eth1 > herzegbol.mysql > shelbyville.2304: S 705352284:705352284(0) ack 1841862906 win 5792 (DF) This trace is after I issue mysqladmin shutdown: 14:32:09.886091 eth1 > herzegbol.mysql > shelbyville.2333: R 0:0(0) ack 2360059956 win 0 (DF) 14:32:15.626067 eth1 > herzegbol.mysql > shelbyville.2334: R 0:0(0) ack 2356113189 win 0 (DF) 14:32:17.586063 eth1 > herzegbol.mysql > shelbyville.2308: R 0:0(0) ack 1867829359 win 0 (DF) 14:32:20.696068 eth1 > herzegbol.mysql > shelbyville.2321: R 0:0(0) ack 2130321013 win 0 (DF) 14:32:25.566094 eth1 > herzegbol.mysql > shelbyville.2324: R 0:0(0) ack 2251852705 win 0 (DF) 14:32:30.066104 eth1 > herzegbol.mysql > shelbyville.2325: R 0:0(0) ack 2264947201 win 0 (DF) The reason this is confusing to me is that the traffic originates on the mysql server "herzegbol" via the mysql port and the destination is the windows box on dozens of ports and there is no program or process on the windows machine that is connected to the database server.As far as I can tell there is absolutely no reason for Herzegbol to talk to shelbyville, yet this traffic will pop up almost every day for a period of time and swamp my network. I would like to identify the source and understand the cause. Regards, Gary "SuperID" Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org -- 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]
Strange Query
Don't know if this has already come through to everyone, if so I apologize. Ok, here's the deal Table1 is a master list holding ID numbers, and related information sent by clients Table2 is a queue that utilizes Table1.ID numbers, and updates other information on the current state of a job associated with these ID numbers within Table2. This table is used only as a queue, so the Table1.ID numbers come and go as jobs complete. There can be multiple instances, or 0 instances of Table1.ID on Table2. Table3 is a log that contains even more information concerning the Table1.ID numbers, and associated processing information from Table2, and information of further processing, shipping status, tracking numbers, etc. There can be multiple instances, or 0 instances of the Table1.ID number on Table3. So, what I am trying to do is this: Table1 holds this info(simplified): MasterID(Unique) int NumberRequested int Table2 holds this info(simplified): MasterID int OrderID(Unique) int Table3 holds this info(simplified): MasterID int OrderID(Unique) int JobState bool What I need to extract, within a single statement, is this: For each MasterID on Table1 What is the NumberRequested Does the MasterID exist on Table2, if so, how many times? (QUEUED) Does the MasterID exist on Table3, and if so, How many times with JobState TRUE (GOOD) How many times with JobState FALSE (BAD) If the NumberRequested is greater than QUEUED+GOOD, then return a 1 as (UHOH) So, looking for a return like: MASTERIDNUMBERREQUESTED QUEUED GOOD BAD UHOH 1 105 4 1 1 2 1010 0 0 0 3 108 2 1 0 4 106 0 4 1 5 100 100 0 6 100 5 5 1 This is what I tried: SELECT Table1.MasterID AS MasterID, Table1.numberofcopies AS numberrequested, SUM(CASE WHEN TABLE3.jobstate=0 AND TABLE3.MasterID=Table1.MasterID THEN 1 END) AS good, SUM(CASE WHEN Table3.jobstate=1 AND Table3.MasterID=Table1.MasterID THEN 1 END) AS bad, SUM(CASE WHEN Table2.MasterID=Table1.MasterID THEN 1 END) AS queued, (CASE WHEN numberrequested > queued+good THEN 1 ELSE 0 END) AS uhoh FROM Table1, Table2, Table3 GROUP BY Table1.MasterID; Help please? Thanks, Mike
