Re: How to query SET OPTION variables?
On Fri, May 03, 2002 at 01:22:30PM +0200, Harald Fuchs wrote: > > How about "SHOW OPTIONS [LIKE wild]", nicely accompanying "SHOW VARIABLES"? Yeah, that'd be the way to go, I think. -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 85 days, processed 2,227,635,975 queries (300/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql.sock file does not get created
>Description: I try to run the mysqld script to start the MySQL server. It starts, then immediately I get a message saying it has ended. [root@localhost init.d]# ./mysql.server start [root@localhost init.d]# Starting mysqld daemon with databases from /var/lib/mysql/mysql 020504 11:22:19 mysqld ended This does not create the mysql.sock file. This error logs tell me that -- 020503 14:24:19 mysqld started 020503 14:24:20 Can't start server : Bind on unix socket: Permission denied 020503 14:24:20 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 020503 14:24:20 Aborting I dont have another MySQL server running. What am I supposed to do now ? >How-To-Repeat: I dont know. I think this is something wrong here (or) maybe it isnt. >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: email support >Synopsis: .sock file is not created >Severity: serious >Priority: medium >Category: mysql >Class: support >Release: mysql-3.23.22-beta (Source distribution) >Environment: System: Linux localhost.localdomain 2.2.16-22 #1 Tue Aug 22 16:49:06 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686' CXX='c++' CXXFLAGS='-O2 -march=i386 -mcpu=i686' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 May 1 21:34 /lib/libc.so.6 -> libc-2.1.92.so -rwxr-xr-x1 root root 4776568 Aug 31 2000 /lib/libc-2.1.92.so Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --without-debug --without-readline --enable-shared --with-extra-charsets=complex --without-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --enable-assembler --with-mysqld-user=mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: dupe records
Hi, At 07:56 AM 03/05/2002 -0500, you wrote: >[snip] > Here is probably an easy question to answer, but I can't figure > an EASY >way to do it. Right now, I use a temp table with a unique column to solve >it. I am hoping that there is a way in the SELECT >statement. AnywayWhat I want to do is to select records from a table >but if there is more than one with the same cont_id (that is the name of >the column), that it would only select the first one of that id and skip >the rest of that id, but continue with the other rows. > I sure hope that makes since. Only 2 hours of sleep is not good > before >asking for help. >[/snip] > >This query; > >SELECT cont_id, this, that, theother >FROM tblFOO >GROUP BY cont_id >HAVING count(*) = 1 > >will select all records that appear only once in a table according to the >GROUP BY condition. Sure it will. But the original post requires one row for each cont_id even if there are multiple rows with the cont_id. I feel eliminating the HAVING clause from the query would be the exact solution. SELECT cont_id, this, that, theother FROM tblFOO GROUP BY cont_id Anvar. >This query; > >SELECT cont_id, this, that, theother >FROM tblFOO >GROUP BY cont_id >HAVING count(*) > 1 > >will select all records that are duplicates according to the GROUP BY >condition > >HTH! > >Jay > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Data transition
On Sat, 4 May 2002 07:23:46 +0400 support <[EMAIL PROTECTED]> wrote: > Hello, everybody! > > Please give me a hint at some resources about synchronization of two > MySQL databases, situated in different places. > > The point is, I have my local and providers' versions of the same > database. And need to synchronize them at a daily basis with the least > traffic involved. you can do with mysql replication, or you can also do it manually ... each side create bin-log file and translate it into plain-text and insert it into you the other db server, and vice-versa. -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Installation Problem
Hello, I downloaded all the following files to load MYSQL software. mysql-4.0.0a-alpha-win-src.zip mysql-4.0.0a-alpha-win.zip mysql-4.0.1a-alpha-win-src.zip mysql-4.0.1a-alpha-win.zip I installed mysql after unzipping the above two versions of .zip files. After installation it is not coming to the programs but it is installing all the files. What i have to do to show the shortcut in programs and to use mysql database so plese send me all the required files or URLS which will give me correct information to solve my problem Thanks _ Click below to visit monsterindia.com and review jobs in India or Abroad http://monsterindia.rediff.com/jobs - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Installation Problem
Hello, I downloaded all the following files to load MYSQL software. mysql-4.0.0a-alpha-win-src.zip mysql-4.0.0a-alpha-win.zip mysql-4.0.1a-alpha-win-src.zip mysql-4.0.1a-alpha-win.zip I installed mysql after unzipping the above two versions of .zip files. After installation it is not coming to the programs but it is installing all the files. What i have to do to show the shortcut in programs and to use mysql database so plese send me all the required files or URLS which will give me correct information to solve my problem Thanks _ Click below to visit monsterindia.com and review jobs in India or Abroad http://monsterindia.rediff.com/jobs - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Data transition
Hello, everybody! Please give me a hint at some resources about synchronization of two MySQL databases, situated in different places. The point is, I have my local and providers' versions of the same database. And need to synchronize them at a daily basis with the least traffic involved. Thanks, Vyacheslav. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Group by "counted" records...
Hi, My table have two fields (iduser INT, dtaccess TIMESTAMP) with the following data: iduser | dtaccess --- 10 | '2000-10-02' 3 | '2000-12-04' 4 | '2000-10-09' 3 | '2000-12-04' 5 | '2001-02-10' 10 | '2001-03-01' 10 | '2001-01-10' 10 | '2001-01-11' 3 | '2000-12-10' I need to group the records by the number of time some user (iduser) appears in the table, and i need to know how many users appears once, how many appears twice and so on. Example: for these data, the resultset must bring: num_times | num_users - 1 | 2 3 | 1 4 | 1 - i.e: 2 users appears twice, one user - three times, one user - four times. Does anyone knows how can i do that? Thank u very much, Edilson. mysql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
speed optimization on awkward self join
I'm starting to get pretty comfortable with mysql, but I'm still a beginner, and I've run into a problem I can't seem to get around. I'm having trouble getting a particular query to run within anything approaching a reasonable amount of time -- I'm not sure whether it's a problem with my query structure, a problem with my schema, or an intrinsic limitation in what I'm trying to do. Here's a simplified example of my schema (theme of the data changed so as to be more intuitively understandable, hopefully) TABLE characters ID Name TABLE char_data_type ID Type TABLE char_data ID character_ID char_data_type_ID Value So then I'd have a number of characters: "Bob", "Tierney", "Ogrek the Beheader" a number of char_data_types: "Strength", "Intelligence", "Age", "Class" and a number of char_data values: "18", "10", "Rogue", etc. You can pretty easily return a list of all characters with a Strength of 18: SELECT characters.name FROM characters, char_data_type, char_data WHERE char_data.value = "18" AND char_data.char_data_type_ID = char_data_type.ID AND char_data.character_ID = characters.ID; The tricky part is returning characters restricted to certain values for two or more characteristics, i.e. a Strength of 18 _and_ an Age of 20. After some time (I hadn't heard of self joins), I came up with this: (I also want to return a list of matched values, pointless as that seems in the simplified example) SELECT a.name, b.value, bb.value FROM characters a, char_data b, char_data bb, char_data_type c, char_data_type cc WHERE a.id = b.character_id and a.id = bb.character_id and (b.char_data_type_id = c.id and b.value = '18' and c.type = 'Strength') and (bb.char_data_type_id = cc.id and bb.value = '20' and cc.type = 'Age') This works, but it's much too slow. This needs to be able to handle a number of characters in the millions. Selecting INTO OUTFILE 'foo' cuts the time taken by 5-10%, but that's not really sufficient. Because I need to support a large amount of data, and disk space is a priority second only to speed, I'm hesitant to switch to a single characteristics table, combining char_data and char_data.type -- I don't know how much that would help, anyway. And because the characteristic types need to be dynamically addable and removable, I can't just make extra columns in the characters table. Any suggestions? - Colin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Query Problem
I'm working with an event calendaring system that keeps track of the
time an event starts and the time the event ends. The Duration field
holds a decimal value equivalent to the length of time the event lasts.
Here is the description of the Schedule table which holds this field:
mysql> describe Schedule;
+--+--+--+-+
-+---+
| Field| Type | Null | Key | Default
| Extra |
+--+--+--+-+
-+---+
| EventID | int(11) | | PRI | 0
| |
| Location | smallint(4) unsigned | | MUL | 0
| |
| LocationRoom | varchar(44) | YES | | NULL
| |
| TimeType | enum('AllDay','Exact','TBA') | | | TBA
| |
| Instance | datetime | | PRI | -00-00
00:00:00
| |
| Duration | decimal(4,2) | YES | | NULL
| |
+--+--+--+-+
-+---+
I am trying to compute the end time by using the SEC_TO_TIME function in
my select statement. First I want to multiply the Duration by 3600
(number of seconds in an hour) and then feed that value to the
SEC_TO_TIME function to get the time the event ends.
I'm seeing some discrepancy in the operation of the SEC_TO_TIME
function, however. Let's say my Duration is a value of 9.25. When I
multiply 9.25 by 3600, I get 33300.00. When I use
SEC_TO_TIME(33300.00), it returns 09:15:00, the correct value. But when
I try putting it all together in one query like this: SELECT
SEC_TO_TIME(Duration * 3600) . . . it returns 09:00:00. It appears that
the part of my Duration value behind the decimal point is being igored
when I attempt to retrieve the end time in one query. Why
I would greatly appreciate any help!
--
#
Kory Wheatley
Academic Computing Analyst Sr.
Phone 282-3874
#
Everything must point to him.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Performance issues between two servers
Backgroud: We have a process that runs on a server (APPDEV1) that writes records to a mysql server (SQLDEV0). We attempted to move the databases from SQLDEV0 to SQLDEV1 and have run into some sort of performance bottleneck. The server SQLDEV0 is a Compac server with ~2GB of ram and two processors. The server SQLDEV1 is also a Compac server. It has 4GB of ram and two processors that are a bit faster than the ones in SQLDEV0. One big difference between SQLDEV0 and SQLDEV1 is the version of RedHat. SQLDEV0 is running RedHat 7.2. SQLDEV1 is running an enterprise version of RedHat 7.2 so that it can take advantage of the 4GB of ram. All the table spaces are using Innodb. Problem: The process on APPDEV1 can write records to the box SQLDEV0 about eight time faster than to SQLDEV1. We've looked over the my.sql configurations and they seem to be ok. In fact we adjusted the my.cnf file on SQLDEV1 so that it was identicle to SQLDEV0 but it did not help. The systems are running ~70-95% cpu idle so cpu is not a bottle neck. In testing, raw disk I/O rates are about 50% faster on SQLDEV1 as SQLDEV0. We don't see a bottle neck on I/O. This is the only process using mysql on SQLDEV1. On SQLDEV0 it shares access with several other programs but the box is not very busy. Thoughts? Comments? Criticism? Carl McNamee Systems Administrator/DBA Billing Concepts (210) 949-7282 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Any ideas how to speed this up?
Hi, > -Original Message- > From: Kenneth Hylton [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 03, 2002 2:00 PM > To: 'Alexander Shaw'; MySQL List > Subject: RE: Any ideas how to speed this up? > > > What version of the ODBC Drivers are you using? > > I had a VB app using the 3.5x versions of the ODBC driver > manipulating very > small tables and it was absolutley painful to run. Hmm, I don't *really* agree on this lines. The 3.51 is using the same 2.50 code base with extensions that will not cause the performance issues like this. There is a chance that you enabled OPTION=32 from 3.51 which causes this issue, as this option will enable the driver to fetch the latest data from server. Make sure you turn off this option as well as other debug options, if there is any. If you still feel the driver is talking extra time, then please let me know, so that if there is anything we can incorporate in 3.51.03, which will have lot of code cleanups. Also, the performance will be increased drastically with 4.1 protocol, which is tuned to have best performance from all client APIs with lot of advanced features. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Any ideas how to speed this up?
Cheers Ken, will give that a try for a start.
Alex
-Original Message-
From: Kenneth Hylton [mailto:[EMAIL PROTECTED]]
Sent: 03 May 2002 22:00
To: 'Alexander Shaw'; MySQL List
Subject: RE: Any ideas how to speed this up?
What version of the ODBC Drivers are you using?
I had a VB app using the 3.5x versions of the ODBC driver manipulating very
small tables and it was absolutley painful to run.
I stepped back to the version 2.x drivers (2.9, I think - anyhow, the driver
information is available on the web site) drivers and the thing screamed.
So, before looking for SQL based solutions, try using the 2.x drivers first,
if you can.
Regards,
Ken Hylton
Programmer Analyst IV
LEC Systems & Programming
Billing Concepts, Inc.
7411 John Smith Drive
San Antonio, Texas 78229-4898
(210) 949-7261
-Original Message-
From: Alexander Shaw [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 2:28 PM
To: MySQL List
Subject: Any ideas how to speed this up?
Hi,
I'm currently developing an application with an Access 2000 front end and
MySQL back end. I have coded in updates to a table (based on selections in a
list box) with a work around for the lack of support for sub selects using a
temporary table.
The problem is that already with quite small amounts of data in the tables
(already indexed) things are pretty sluggish and there is huge amounts of
reading and writing to the database.
Does anyone have suggestions of ways I could speed this up please? I have
included the code from the after update event of the list box in question.
Alex
Private Sub lstSimilarFrameNo_AfterUpdate()
Dim strSQL As String
Dim rstCurrentSimilars As DAO.Recordset
Set rstCurrentSimilars = Recordset
strSQL = "SELECT FilmID,FrameID FROM Frames WHERE FilmID = " &
cboSimilarFilmNumber & " "
Set rstCurrentSimilars = CurrentDb.OpenRecordset(strSQL)
Do
If rstCurrentSimilars.BOF = True Then Exit Do
CurrentDb.Execute "INSERT INTO CurrentSimilars (FrameID, FilmID,
SimilarID) VALUES (" & txtFrameID & ", " & rstCurrentSimilars!FilmID & ", "
& rstCurrentSimilars!FrameID & ")", dbFailOnError
rstCurrentSimilars.MoveNext
Loop Until rstCurrentSimilars.EOF = True
rstCurrentSimilars.Close
Set rstCurrentSimilars = Nothing
CurrentDb.Execute "DELETE SimilarsForFrames.* FROM SimilarsForFrames LEFT
JOIN CurrentSimilars ON
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID WHERE
SimilarsForFrames.FrameID=" & txtFrameID & " AND
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID", dbFailOnError
CurrentDb.Execute "DELETE CurrentSimilars.* FROM CurrentSimilars WHERE
(CurrentSimilars.FilmID=" & cboSimilarFilmNumber & ") AND
(CurrentSimilars.FrameID=" & txtFrameID & ")"
Me.Dirty = False
Dim i As Integer
For i = 0 To lstSimilarFrameNo.ListCount - 1
If lstSimilarFrameNo.Selected(i) = True Then
CurrentDb.Execute "INSERT INTO SimilarsForFrames
(SimilarID,FrameID) VALUES (" & lstSimilarFrameNo.ItemData(i) & "," &
Me!FrameID & ")"
End If
Next
End Sub
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.346 / Virus Database: 194 - Release Date: 10/04/2002
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.346 / Virus Database: 194 - Release Date: 10/04/2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.346 / Virus Database: 194 - Release Date: 10/04/2002
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: My Query is TOOOOOOOOOOOO Slow :(
Hi, Thank u very much!! It helped a lot... I also created two indexes: one in wmkt_email_sent for (nmachine e fkpbl) and another in wmkt_client for fkEmail (both sugested by Luciano Barcaro). About the size of the resultset: all these table have about 100.000 records and they can be about 10.000.000 (in the end of this year). So if u have more ideas, i'll be very grateful. Thank u very much, Edilson. -Mensagem original- De: Jay Blanchard [mailto:[EMAIL PROTECTED]] Enviada em: sexta-feira, 3 de maio de 2002 14:18 Para: 'MYSQL' Assunto: RE: My Query is T Slow :( You have written the following: [snip & arrange] SELECT idemailsent, email, idclient FROMwmkt_email_sent a, wmkt_client b, wmkt_email c WHERE nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND fkuser=1 AND fkpbl=23 ORDER BY email LIMIT 1000 [/snip] Shouldn't it be SELECT a.idemailsent, c.email, b.idclient and WHERE a.nMachine = 0 (I am being slightly picky here, but alias' are there for a reason). Also, it looks like your AND's should be something like AND c.idemail=b.fkemail AND b.fkemail=a.fkemail note the order of processing. You didn't say how many total records. Can you do this; EXPLAIN SELECT a.idemailsent, c.email, b.idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE a.nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND b.fkuser=1 AND a.fkpbl=23 ORDER BY c.email LIMIT 1000 and mail thos results back? Plus, I would re-arrange the query, putting conditions with values ahead of conditions with matches and move from left to right in the table order; EXPLAIN SELECT a.idemailsent, c.email, b.idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE a.nMachine = 0 AND a.fkpbl=23 AND b.fkuser=1 AND a.fkemail=b.fkemail AND b.fkemail=c.idemail ORDER BY c.email LIMIT 1000 Looks like you can use a couple of thingsdenormalization and INDEXES. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Any ideas how to speed this up?
What version of the ODBC Drivers are you using?
I had a VB app using the 3.5x versions of the ODBC driver manipulating very
small tables and it was absolutley painful to run.
I stepped back to the version 2.x drivers (2.9, I think - anyhow, the driver
information is available on the web site) drivers and the thing screamed.
So, before looking for SQL based solutions, try using the 2.x drivers first,
if you can.
Regards,
Ken Hylton
Programmer Analyst IV
LEC Systems & Programming
Billing Concepts, Inc.
7411 John Smith Drive
San Antonio, Texas 78229-4898
(210) 949-7261
-Original Message-
From: Alexander Shaw [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 03, 2002 2:28 PM
To: MySQL List
Subject: Any ideas how to speed this up?
Hi,
I'm currently developing an application with an Access 2000 front end and
MySQL back end. I have coded in updates to a table (based on selections in a
list box) with a work around for the lack of support for sub selects using a
temporary table.
The problem is that already with quite small amounts of data in the tables
(already indexed) things are pretty sluggish and there is huge amounts of
reading and writing to the database.
Does anyone have suggestions of ways I could speed this up please? I have
included the code from the after update event of the list box in question.
Alex
Private Sub lstSimilarFrameNo_AfterUpdate()
Dim strSQL As String
Dim rstCurrentSimilars As DAO.Recordset
Set rstCurrentSimilars = Recordset
strSQL = "SELECT FilmID,FrameID FROM Frames WHERE FilmID = " &
cboSimilarFilmNumber & " "
Set rstCurrentSimilars = CurrentDb.OpenRecordset(strSQL)
Do
If rstCurrentSimilars.BOF = True Then Exit Do
CurrentDb.Execute "INSERT INTO CurrentSimilars (FrameID, FilmID,
SimilarID) VALUES (" & txtFrameID & ", " & rstCurrentSimilars!FilmID & ", "
& rstCurrentSimilars!FrameID & ")", dbFailOnError
rstCurrentSimilars.MoveNext
Loop Until rstCurrentSimilars.EOF = True
rstCurrentSimilars.Close
Set rstCurrentSimilars = Nothing
CurrentDb.Execute "DELETE SimilarsForFrames.* FROM SimilarsForFrames LEFT
JOIN CurrentSimilars ON
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID WHERE
SimilarsForFrames.FrameID=" & txtFrameID & " AND
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID", dbFailOnError
CurrentDb.Execute "DELETE CurrentSimilars.* FROM CurrentSimilars WHERE
(CurrentSimilars.FilmID=" & cboSimilarFilmNumber & ") AND
(CurrentSimilars.FrameID=" & txtFrameID & ")"
Me.Dirty = False
Dim i As Integer
For i = 0 To lstSimilarFrameNo.ListCount - 1
If lstSimilarFrameNo.Selected(i) = True Then
CurrentDb.Execute "INSERT INTO SimilarsForFrames
(SimilarID,FrameID) VALUES (" & lstSimilarFrameNo.ItemData(i) & "," &
Me!FrameID & ")"
End If
Next
End Sub
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.346 / Virus Database: 194 - Release Date: 10/04/2002
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql - problem with timeout
hello ... first excuse my bad english ;-( we have a problem . one of our servers is working with the following components and configuration: linux suse 7.1 mysql 3.23.33 phpadmin 2.2.5 webmin 0,90 our problem . we work with a few databases in mysql ... often when we start more than (perhaps) hundred search-queries with a perlscript, whitch will show an output of the mysql-database, there is the problem, that mysql dont close the query-connection. many people said to us, there must be a chance to configure mysql so, that mysql will close a search-connection after ** seconds our question: whe will we configure mysql, that we will set a timeout for queries and connections? must it be in the perl-scripts or can we do this on the server with our webmin-tool? please . help us with a good documentation from youreself . maybe in german language?! thanks a lot . guenter m. schmitt koeln - deutschland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select by total goals
Hey, I'm trying to display this information on a page, but I'm having trouble. How do I output this into php? mysql, query Thanks! -Alex "Big Al" Behrens E-mail: [EMAIL PROTECTED] Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) Phone: 651-482-8779 Cell: 651-329-4187 Fax: 651-482-1391 ICQ: 3969599 Owner of the 3D-Unlimited Network: http://www.3d-unlimited.com Send News: [EMAIL PROTECTED] - Original Message - From: "Jeff Kilbride" <[EMAIL PROTECTED]> To: "Alex Behrens" <[EMAIL PROTECTED]>; "MYSQL" <[EMAIL PROTECTED]> Sent: Tuesday, April 30, 2002 9:30 PM Subject: Re: select by total goals > Try: > > SELECT SUM(goals) AS leaders > FROM stats > GROUP BY [player_field] > ORDER BY leaders DESC > > --jeff > > - Original Message - > From: "Alex Behrens" <[EMAIL PROTECTED]> > To: "MYSQL" <[EMAIL PROTECTED]> > Sent: Tuesday, April 30, 2002 6:55 PM > Subject: select by total goals > > > > hey guys, > > > > I'm working on a script to display the season leading scorers for my > > lacrosse site and I don't know how to do the query. > > > > I am trying to display the leaders scorers by total goals throughout the > > season, however I don't know how to select the sum of goals by each > > individual player then sort by amount of goals. > > > > how do i do this? > > > > I thought: select SUM(goals) AS leaders from stats ORDER BY number DESC; > > > > but that just takes the total amount of goals from the table, how do I add > > up individual goals (for each player) then sort by amount? > > > > Thanks! > > > > -Alex "Big Al" Behrens > > E-mail: [EMAIL PROTECTED] > > Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) > > Phone: 651-482-8779 > > Cell: 651-329-4187 > > Fax: 651-482-1391 > > ICQ: 3969599 > > Owner of the 3D-Unlimited Network: > > http://www.3d-unlimited.com > > Send News: > > [EMAIL PROTECTED] > > > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: display days between two dates?
At 01:36 PM 5/3/2002, you wrote: >Hello, > >Newbie SQL person here. I am hoping to be able to do the >following. Given two dates, I would like to display all of the days >between them. > >So if I had the dates 2002-02-08 and 2002-02-12, I would like to have >MySQL spit back > >2002-02-08 >2002-02-09 >2002-02-10 >2002-02-11 >2002-02-12 > >Any way I can do this directly in MySQL without constructing a table? I've >looked through the date maniplulation routines in the manual, but nothing >jumps out at me. Any pointers would be appreciated. > >I know how I could do this in Perl or PHP, but I was hoping to get it done >directly in SQL. Temp table? > >Thanks > >Tim Tim, Here's a tip. You may want to experiment on a temporary table first to see how the conversions end up looking. Just do a "create newtable select * from oldtable limit 10" and try your conversions on the newtable (indexes aren't created for newtable). If it works ok, let it rip on your oldtable. This way you protect your production table and you can experiment as much as you like with your temporary table. If the temporary table didn't convert properly, just drop newtable and try again. :) You can always use it to create a temporary backup of your entire production table before you convert. That way you have a backup in case something goes wrong. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
java...mysql
Sorry if this got sent twice...
Hello all.
Im trying to right a simple program for MySQL
through java but am running into a problem of sorts. Following is the code
that has theproblem:
conn =
DriverManager.getConnection("jdbc:mysql://localhost/mysql?user=root&password=");stmt
= conn.createStatement();
stmt.execute("CREATE DATABASE
quake3parsed");
conn.close();
shell.exec("C:\\mysql\\bin\\mysqladmin -u root -
p reload").waitFor();
conn =
DriverManager.getConnection("jdbc:mysql://localhost/quake3parsed?user=root&password==");stmt
= conn.createStatement();
The problem is on the second connect try I get the following
SQL Exception:
SQLException: General error: Unknown database
'quake3parsed'
SQLState: S1000
VenderError: 1049
But if I go to access MySQL from
any other source it shows that the database has been created and is there.
Is there any sort ofgrace period that I need to wait for MySQL to take the new db?
Also I tried it by
using:
shell.exec("C:\\mysql\\bin\\mysqladmin -u root -p* CREATE
quake3parsed").waitFor();
as well but that gave the same results.
Thanks for the
help in advance.
-Nick
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Any ideas how to speed this up?
Hi,
I'm currently developing an application with an Access 2000 front end and
MySQL back end. I have coded in updates to a table (based on selections in a
list box) with a work around for the lack of support for sub selects using a
temporary table.
The problem is that already with quite small amounts of data in the tables
(already indexed) things are pretty sluggish and there is huge amounts of
reading and writing to the database.
Does anyone have suggestions of ways I could speed this up please? I have
included the code from the after update event of the list box in question.
Alex
Private Sub lstSimilarFrameNo_AfterUpdate()
Dim strSQL As String
Dim rstCurrentSimilars As DAO.Recordset
Set rstCurrentSimilars = Recordset
strSQL = "SELECT FilmID,FrameID FROM Frames WHERE FilmID = " &
cboSimilarFilmNumber & " "
Set rstCurrentSimilars = CurrentDb.OpenRecordset(strSQL)
Do
If rstCurrentSimilars.BOF = True Then Exit Do
CurrentDb.Execute "INSERT INTO CurrentSimilars (FrameID, FilmID,
SimilarID) VALUES (" & txtFrameID & ", " & rstCurrentSimilars!FilmID & ", "
& rstCurrentSimilars!FrameID & ")", dbFailOnError
rstCurrentSimilars.MoveNext
Loop Until rstCurrentSimilars.EOF = True
rstCurrentSimilars.Close
Set rstCurrentSimilars = Nothing
CurrentDb.Execute "DELETE SimilarsForFrames.* FROM SimilarsForFrames LEFT
JOIN CurrentSimilars ON
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID WHERE
SimilarsForFrames.FrameID=" & txtFrameID & " AND
SimilarsForFrames.SimilarID=CurrentSimilars.SimilarID", dbFailOnError
CurrentDb.Execute "DELETE CurrentSimilars.* FROM CurrentSimilars WHERE
(CurrentSimilars.FilmID=" & cboSimilarFilmNumber & ") AND
(CurrentSimilars.FrameID=" & txtFrameID & ")"
Me.Dirty = False
Dim i As Integer
For i = 0 To lstSimilarFrameNo.ListCount - 1
If lstSimilarFrameNo.Selected(i) = True Then
CurrentDb.Execute "INSERT INTO SimilarsForFrames
(SimilarID,FrameID) VALUES (" & lstSimilarFrameNo.ItemData(i) & "," &
Me!FrameID & ")"
End If
Next
End Sub
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.346 / Virus Database: 194 - Release Date: 10/04/2002
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (Maybe) Silly question
On Friday 03 May 2002 16:19, Cal Evans wrote: > 4 step process > > ALTER TABLE to add a new Date column > > Populate the new field with the date portion of the DATETIME > > Drop the DATETIME column > > Rename the DATE column with the name of the original field. > Thanks. I'll try it. > -Original Message- > From: João Paulo Vasconcellos [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 03, 2002 1:43 PM > To: [EMAIL PROTECTED] > Subject: (Maybe) Silly question > > > Hello everybody, > > i was thinking about my database, and I found that the guy who planned it > before me created a field to hold the date people registered with the site. > The field is a DATETIME, but the time portion is never used! > > So, I began to wonder if I could resize the field to DATE, but I am not > sure if the DATE part will be the same after the change. Anybody here did > this before ? > > > > > > > [ Hello filter, I can speak SQL :) ] > > -- > João Paulo Vasconcellos > Gerente de Tecnologia - NetCard > Tel. 21 3852-9008 Ramal 31 > [EMAIL PROTECTED] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- João Paulo Vasconcellos Gerente de Tecnologia - NetCard Tel. 21 3852-9008 Ramal 31 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: (Maybe) Silly question
4 step process ALTER TABLE to add a new Date column Populate the new field with the date portion of the DATETIME Drop the DATETIME column Rename the DATE column with the name of the original field. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: João Paulo Vasconcellos [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:43 PM To: [EMAIL PROTECTED] Subject: (Maybe) Silly question Hello everybody, i was thinking about my database, and I found that the guy who planned it before me created a field to hold the date people registered with the site. The field is a DATETIME, but the time portion is never used! So, I began to wonder if I could resize the field to DATE, but I am not sure if the DATE part will be the same after the change. Anybody here did this before ? [ Hello filter, I can speak SQL :) ] -- João Paulo Vasconcellos Gerente de Tecnologia - NetCard Tel. 21 3852-9008 Ramal 31 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Simple Question relating to Indexing
I've not checked your syntax but it looks like you've got the idea. One caveat. Unless you have a UNIQUE index, you can have multiple people named John Smith. If you specify UNIQUE in your index clause then you can't. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 1:17 PM To: Svensson, B.A.T. (HKG); [EMAIL PROTECTED] Subject: Re: Simple Question relating to Indexing I'd like to first thank Carl and Anders for your quick and very informative answers. I really appreciate it. To get this staightened out, for a table specified by: CREATE TABLE Player ( PlayerID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, TeamID INT NOT NULL, TrophyFlag TINYINT, RecordFlag TINYINT, LetterID TINYINT NOT NULL, NameFirst char(15) NOT NULL, NameLast char(20) NOT NULL) My primary key is obviously: playerid. letterID, trophyflag, recordflag, and teamid can be foreign keys. They are not configured so right now. An index could be created based upon NameFirst and NameLast. Which would be ok so long as there are not two people with the name John Smith (or whatever) - Original Message - From: "Svensson, B.A.T. (HKG)" <[EMAIL PROTECTED]> To: "Shaun Bramley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:11 PM Subject: RE: Simple Question relating to Indexing > > Hello all being relatively new on the SQL scene I must say some of the > > concepts are a little new and strange to me. My really big question is what > > is the difference between an index and a key? are the synonamous with each > > other? > > A key will identifying a row or several rows. Just like you key to > your house can "identified" by you house lock. But the key wont tell > you were your house is located, this is what the index will do for you. > > An index is just a smart way to skip some rows that is not needed to > search while you look for your data. For example if you have a ordered > list of numbers from 1 to hundred, then you know you dont have to look > in the upper part of the list if your number is less than 50. > > So the purpose of the index is to locate where you can find a match > for your key, in other words exclude parts which is guaranteed not > to include the key. > > Analogue example of index and none indexed search: > > None indexed unique search: > > Assume you live in a skyscraper. You start at the bottom floor > and test your house key in every single lock until you find > a match, if you don't find a match you progress to the next floor > and repeat the procedure. If you find a match you stops seating > > None indexed none unique search: > > You start at the bottom floor and test your master key in every > single lock until you find a match. If you find a match, you mark > that door, and continue search the rest of the doors, until you > reached the top floor. > > Indexed unique search: > > You go to the elevator, selects the floor which match your > key, arriving at this floor you tries out all doors until > you find one match. > > Indexed none unique search: > > You go to the elevator, with a list of floors you need to > visit. You visit each floor specified in the list and on > arrival at each of these floors you test all doors at that > floor and mark the doors that has a match. > > > > Basically an index is specifying a lesser search space for you, > so you don't have to brute force search an entire table. A brute > force search is normally refereed to as a "table scan". While > you key is the value that actually precesly can identify your > data, it is a slow way to find it since brute force search is > needed to locate yuor data if you only uses the key. The index > has the ability to very fast narrow down your data, but to > the cost of resultion - the index doesn't well on close > distance. Hence the index can't identify your data, just > tell you an "about where" location. > > In a way you can put it like this: > > The index has good sight at long distance, but bad at short, > while the key has bad sight on long distance but good sight > on short. > > //Anders > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Solved (Re: 4.0.2 Replication still buggy...)
On Fri, May 03, 2002 at 11:46:42AM -0600, Sasha Pachev wrote: > On Wednesday 01 May 2002 11:34 pm, Jeremy Zawodny wrote: > > After even more tracking stuff down, I managed to isolate it to a > > change made in MySQL 4.0.1. ?The query in question was doing: > > > > ? INSERT INTO ... SELECT ... > > > > rather than > > > > ? INSERT IGNORE INTO ... SELECT ... > > > > MySQL 3.23.xx lets the first sneak by without errors even if there are > > duplicates. ?4.0.2 does not. ?It stops. ?Since our master is 3.23 and > > this particular slave is 4.0.2, the two didn't agree. > > That would be a bug in 4.0.2. Can you provide a test case for it? Sure. If you run this set of queries on a MySQL 3.23.xx master which has a 4.0.2 slave, it'll work on the master but kill replication on the slave: ---snip--- CREATE TABLE dirty ( id int not null primary key ); CREATE TABLE archive ( id int not null primary key ); INSERT INTO archive (id) VALUES (1), (2), (3), (4), (5); INSERT INTO dirty SELECT * FROM archive; INSERT INTO dirty SELECT * FROM archive; ---snip--- The last query is the killer. If it is changed to: INSERT IGNORE INTO dirty SELECT * FROM archive; It'll work fine, as I mentioned. Let me know if you need more data. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 85 days, processed 2,208,494,219 queries (300/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
(Maybe) Silly question
Hello everybody, i was thinking about my database, and I found that the guy who planned it before me created a field to hold the date people registered with the site. The field is a DATETIME, but the time portion is never used! So, I began to wonder if I could resize the field to DATE, but I am not sure if the DATE part will be the same after the change. Anybody here did this before ? [ Hello filter, I can speak SQL :) ] -- João Paulo Vasconcellos Gerente de Tecnologia - NetCard Tel. 21 3852-9008 Ramal 31 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
display days between two dates?
Hello, Newbie SQL person here. I am hoping to be able to do the following. Given two dates, I would like to display all of the days between them. So if I had the dates 2002-02-08 and 2002-02-12, I would like to have MySQL spit back 2002-02-08 2002-02-09 2002-02-10 2002-02-11 2002-02-12 Any way I can do this directly in MySQL without constructing a table? I've looked through the date maniplulation routines in the manual, but nothing jumps out at me. Any pointers would be appreciated. I know how I could do this in Perl or PHP, but I was hoping to get it done directly in SQL. Temp table? Thanks Tim Tim Carlson Voice: (509) 376 3423 Email: [EMAIL PROTECTED] EMSL UNIX System Support - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Changing column from UNIQUE?
I have a column in a table that is currently configured as UNIQUE. I have recently discovered that some duplicates may exist in this column, so I want to remove the UNIQUE flag on this column. How? Thanks. sql > Scott Nipp > Systems Analyst > SBC Long Distance > (214) 858-1289 > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Simple Question relating to Indexing
I'd like to first thank Carl and Anders for your quick and very informative answers. I really appreciate it. To get this staightened out, for a table specified by: CREATE TABLE Player ( PlayerID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, TeamID INT NOT NULL, TrophyFlag TINYINT, RecordFlag TINYINT, LetterID TINYINT NOT NULL, NameFirst char(15) NOT NULL, NameLast char(20) NOT NULL) My primary key is obviously: playerid. letterID, trophyflag, recordflag, and teamid can be foreign keys. They are not configured so right now. An index could be created based upon NameFirst and NameLast. Which would be ok so long as there are not two people with the name John Smith (or whatever) - Original Message - From: "Svensson, B.A.T. (HKG)" <[EMAIL PROTECTED]> To: "Shaun Bramley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:11 PM Subject: RE: Simple Question relating to Indexing > > Hello all being relatively new on the SQL scene I must say some of the > > concepts are a little new and strange to me. My really big question is what > > is the difference between an index and a key? are the synonamous with each > > other? > > A key will identifying a row or several rows. Just like you key to > your house can "identified" by you house lock. But the key wont tell > you were your house is located, this is what the index will do for you. > > An index is just a smart way to skip some rows that is not needed to > search while you look for your data. For example if you have a ordered > list of numbers from 1 to hundred, then you know you dont have to look > in the upper part of the list if your number is less than 50. > > So the purpose of the index is to locate where you can find a match > for your key, in other words exclude parts which is guaranteed not > to include the key. > > Analogue example of index and none indexed search: > > None indexed unique search: > > Assume you live in a skyscraper. You start at the bottom floor > and test your house key in every single lock until you find > a match, if you don't find a match you progress to the next floor > and repeat the procedure. If you find a match you stops seating > > None indexed none unique search: > > You start at the bottom floor and test your master key in every > single lock until you find a match. If you find a match, you mark > that door, and continue search the rest of the doors, until you > reached the top floor. > > Indexed unique search: > > You go to the elevator, selects the floor which match your > key, arriving at this floor you tries out all doors until > you find one match. > > Indexed none unique search: > > You go to the elevator, with a list of floors you need to > visit. You visit each floor specified in the list and on > arrival at each of these floors you test all doors at that > floor and mark the doors that has a match. > > > > Basically an index is specifying a lesser search space for you, > so you don't have to brute force search an entire table. A brute > force search is normally refereed to as a "table scan". While > you key is the value that actually precesly can identify your > data, it is a slow way to find it since brute force search is > needed to locate yuor data if you only uses the key. The index > has the ability to very fast narrow down your data, but to > the cost of resultion - the index doesn't well on close > distance. Hence the index can't identify your data, just > tell you an "about where" location. > > In a way you can put it like this: > > The index has good sight at long distance, but bad at short, > while the key has bad sight on long distance but good sight > on short. > > //Anders > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Solved (Re: 4.0.2 Replication still buggy...)
On Wednesday 01 May 2002 11:34 pm, Jeremy Zawodny wrote: > After even more tracking stuff down, I managed to isolate it to a > change made in MySQL 4.0.1. ?The query in question was doing: > > ? INSERT INTO ... SELECT ... > > rather than > > ? INSERT IGNORE INTO ... SELECT ... > > MySQL 3.23.xx lets the first sneak by without errors even if there are > duplicates. ?4.0.2 does not. ?It stops. ?Since our master is 3.23 and > this particular slave is 4.0.2, the two didn't agree. That would be a bug in 4.0.2. Can you provide a test case for it? -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/?ref=mspa __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Optimization
[snip] I want to optimize one of my php scripts to acess mysql only once. Is there anyway to put these two queries together: SELECT COUNT(*) from TABLE where name="JOHN" SELECT * from TABLE where name="JOHN" limit 10 [/snip] try select *, sum(if(name = 'JOHN', 1, 0)) AS COUNT from table where name = 'JOHN' group by name HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: My Query is TOOOOOOOOOOOO Slow :(
You have written the following: [snip & arrange] SELECT idemailsent, email, idclient FROMwmkt_email_sent a, wmkt_client b, wmkt_email c WHERE nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND fkuser=1 AND fkpbl=23 ORDER BY email LIMIT 1000 [/snip] Shouldn't it be SELECT a.idemailsent, c.email, b.idclient and WHERE a.nMachine = 0 (I am being slightly picky here, but alias' are there for a reason). Also, it looks like your AND's should be something like AND c.idemail=b.fkemail AND b.fkemail=a.fkemail note the order of processing. You didn't say how many total records. Can you do this; EXPLAIN SELECT a.idemailsent, c.email, b.idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE a.nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND b.fkuser=1 AND a.fkpbl=23 ORDER BY c.email LIMIT 1000 and mail thos results back? Plus, I would re-arrange the query, putting conditions with values ahead of conditions with matches and move from left to right in the table order; EXPLAIN SELECT a.idemailsent, c.email, b.idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE a.nMachine = 0 AND a.fkpbl=23 AND b.fkuser=1 AND a.fkemail=b.fkemail AND b.fkemail=c.idemail ORDER BY c.email LIMIT 1000 Looks like you can use a couple of thingsdenormalization and INDEXES. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Simple Question relating to Indexing
> Hello all being relatively new on the SQL scene I must say some of the > concepts are a little new and strange to me. My really big question is what > is the difference between an index and a key? are the synonamous with each > other? A key will identifying a row or several rows. Just like you key to your house can "identified" by you house lock. But the key wont tell you were your house is located, this is what the index will do for you. An index is just a smart way to skip some rows that is not needed to search while you look for your data. For example if you have a ordered list of numbers from 1 to hundred, then you know you dont have to look in the upper part of the list if your number is less than 50. So the purpose of the index is to locate where you can find a match for your key, in other words exclude parts which is guaranteed not to include the key. Analogue example of index and none indexed search: None indexed unique search: Assume you live in a skyscraper. You start at the bottom floor and test your house key in every single lock until you find a match, if you don't find a match you progress to the next floor and repeat the procedure. If you find a match you stops seating None indexed none unique search: You start at the bottom floor and test your master key in every single lock until you find a match. If you find a match, you mark that door, and continue search the rest of the doors, until you reached the top floor. Indexed unique search: You go to the elevator, selects the floor which match your key, arriving at this floor you tries out all doors until you find one match. Indexed none unique search: You go to the elevator, with a list of floors you need to visit. You visit each floor specified in the list and on arrival at each of these floors you test all doors at that floor and mark the doors that has a match. Basically an index is specifying a lesser search space for you, so you don't have to brute force search an entire table. A brute force search is normally refereed to as a "table scan". While you key is the value that actually precesly can identify your data, it is a slow way to find it since brute force search is needed to locate yuor data if you only uses the key. The index has the ability to very fast narrow down your data, but to the cost of resultion - the index doesn't well on close distance. Hence the index can't identify your data, just tell you an "about where" location. In a way you can put it like this: The index has good sight at long distance, but bad at short, while the key has bad sight on long distance but good sight on short. //Anders - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Optimization
Hi, I want to optimize one of my php scripts to acess mysql only once. Is there anyway to put these two queries together: SELECT COUNT(*) from TABLE where name="JOHN" SELECT * from TABLE where name="JOHN" limit 10 Is it possible ? Thanks, Rodrigo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: My Query is TOOOOOOOOOOOO Slow :(
Hi Edilson, Increase your key and join buffers. also select your absolute values first. i.e.: SELECT idemailsent, email, idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE fkuser = 1 AND fkpbl = 23 AND nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail ORDER BY email LIMIT 1000 Also make sure you index any columns which will be used to key off of. This includes any columns you plan to join on. Edilson Vasconcelos de Melo Junior wrote: > > Hi, > > My query is running very very VERY slowly and this is creating a lot of > troubles. Please, see it below and if u find something useful and faster > i'll be very grateful. > > MYSQL >> SELECT idemailsent, email, idclient FROM wmkt_email_sent a, > wmkt_client b, wmkt_email c WHERE nMachine = 0 AND c.idemail=a.fkemail AND > b.fkemail=a.fkemail AND fkuser=1 AND fkpbl=23 ORDER BY email LIMIT 1000 > > the tables structures are: > > mysql> show fields FROM wmkt_email_sent; > +-+---+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +-+---+--+-+-++ > | idemailsent | int(11) | | PRI | NULL| auto_increment | > | fkpbl | int(11) | | | 0 || > | fkemail | int(11) | | | 0 || > | dtSend | timestamp(14) | YES | | NULL|| > | nResult | int(11) | | | 0 || > | dtLastUp| timestamp(14) | YES | | NULL|| > | nMachine| int(11) | | | 0 || > +-+---+--+-+-++ > > mysql> show fields FROM wmkt_client; > +--+---+--+-+-++ > | Field| Type | Null | Key | Default | Extra | > +--+---+--+-+-++ > | idclient | int(11) | | PRI | NULL| auto_increment | > | realname | varchar(80) | | | || > | fkemail | int(11) | | | 0 || > | dtInsert | timestamp(14) | YES | | NULL|| > | dtLastUp | timestamp(14) | YES | | NULL|| > | fkuser | int(11) | | | 1 || > +--+---+--+-+-++ > > mysql> show fields FROM wmkt_email; > ++--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > ++--+--+-+-++ > | idemail| int(11) | | PRI | NULL| auto_increment | > | email | varchar(255) | | UNI | || > | fklastresp | int(11) | | | 0 || > ++--+--+-+-++ > > Thank u very much, > Edilson. > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Colin Faber (303) 736-5160 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
My Query is TOOOOOOOOOOOO Slow :(
Hi, My query is running very very VERY slowly and this is creating a lot of troubles. Please, see it below and if u find something useful and faster i'll be very grateful. MYSQL >> SELECT idemailsent, email, idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND fkuser=1 AND fkpbl=23 ORDER BY email LIMIT 1000 the tables structures are: mysql> show fields FROM wmkt_email_sent; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | idemailsent | int(11) | | PRI | NULL| auto_increment | | fkpbl | int(11) | | | 0 || | fkemail | int(11) | | | 0 || | dtSend | timestamp(14) | YES | | NULL|| | nResult | int(11) | | | 0 || | dtLastUp| timestamp(14) | YES | | NULL|| | nMachine| int(11) | | | 0 || +-+---+--+-+-++ mysql> show fields FROM wmkt_client; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | idclient | int(11) | | PRI | NULL| auto_increment | | realname | varchar(80) | | | || | fkemail | int(11) | | | 0 || | dtInsert | timestamp(14) | YES | | NULL|| | dtLastUp | timestamp(14) | YES | | NULL|| | fkuser | int(11) | | | 1 || +--+---+--+-+-++ mysql> show fields FROM wmkt_email; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | idemail| int(11) | | PRI | NULL| auto_increment | | email | varchar(255) | | UNI | || | fklastresp | int(11) | | | 0 || ++--+--+-+-++ Thank u very much, Edilson. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to Count(*) with LIMIT
At 09:46 AM 5/3/2002, you wrote: > > Nope. :) > > The table could be in excess of 1 million rows. The > > user sees just the > > first 100 rows no matter what. If he wants to see a > > different group of > > records he can use the search fields at the top of > > the form. I want the web > > server to serve as many pages/sec as possible and > > limiting the # of rows > > returned to 100 (or less) is the best way to do it. > > If everyone started to > > do 1 million row queries, then it won't support many > > users. > > > >I think that mysql selects all row regardless of LIMIT > >So although it returns only 100 - I would think >there's a better way to do it. > >olinux olinux, If there is a better way, I'm all ears. :) (Cursors might be an option but they aren't available yet until 4.1/4.2) LIMIT 100 does work extremely fast on large tables provided that if any sorting is done, the sort column is indexed. If they aren't indexed, then MySQL will need to fetch all the rows to sort them. I think this is what you were referring to. Other than that, I think LIMIT is the solution. Unless someone out there can come up with something better for a webserver running PHP. Mike sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strategies for maintaining tables calculated from other tables?
MySQL has a column type for that, so you don't have to use an INT or BIGINT: http://www.mysql.com/doc/S/E/SET.html --jeff - Original Message - From: "Harald Fuchs" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 4:19 AM Subject: Re: Strategies for maintaining tables calculated from other tables? > In article <[EMAIL PROTECTED]>, > "Nick Arnett" <[EMAIL PROTECTED]> writes: > > > I'm finding that it's not quite as simple as I had imagined to maintain a > > table whose values are calculated by analyzing other tables. The source > > tables contain time series data, which can is updated several times a day. > > To calculate totals by day, week, month, etc., I don't want to have to start > > over from scratch, so I want to keep track of which records in the source > > tables have already been analyzed. Further complicating things, there are > > several analyses that take place and I may add others later. I don't always > > want to have to do all of them at the same time. > > > So, at the moment, I've added some Boolean enum columns to the source data > > tables, showing which analyses have been done. Do the analysis, set the > > Booleans to true. I'm not sure I really like that approach, since it can > > mean adding columns to rather large tables at times. > > How about adding a single INT or BIGINT column which you treat as a > bit string in your application (presuming you don't ever have more than 32 > resp. 64 analyses). > > > Just to make it all a bit more complicated, the initial analysis is done in > > a temporary table, for huge performance gain, then inserted into its final > > resting spot. > > This should not matter as long as you set your flags after the final > INSERT has been done. > > > P.S. sql,query (to satisfy that danged filter) > > Me too ... > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: need SQL assistance with a few selects MORE INFO
[snip] 2. The date of max disksize and the date of min disksize...in one query...if that's possible. [/snip] mysql> select RunDate, -> MAX(IF(ClientName = 'ABC INC', DiskSize, '')) AS ABC, -> MAX(IF(ClientName = 'Eat at Joe', DiskSize, '')) AS Joe, -> MAX(IF(ClientName = 'The Hop', DiskSize, '')) AS Hop, -> MAX(IF(ClientName = 'Razoo', DiskSize, '')) AS Razoo -> from tblBAR -> group by RunDate -> order by DiskSize DESC; ++--+--+--+---+ | RunDate| ABC | Joe | Hop | Razoo | ++--+--+--+---+ | 2002-07-12 | 3.0 | | | | | 2002-07-04 | | | | 2.6 | | 2002-07-13 | 1.8 | 2.0 | | | | 2002-07-11 | 1.2 | 1.8 | | | | 2002-07-17 | | | 1.1 | 1.1 | | 2002-07-14 | | 0.8 | | | | 2002-07-08 | | | 0.6 | | ++--+--+--+---+ 7 rows in set (0.00 sec) This requires that you know each client. If you look at column ABC and go down, the first value is the MAX disk size, go left to find the date this occured. Look at column Hop and go down to the first value, that is the MAX, go left to find the date this occured. mysql> select RunDate, -> MAX(IF(ClientName = 'ABC INC', DiskSize, '')) AS ABC, -> MAX(IF(ClientName = 'Eat at Joe', DiskSize, '')) AS Joe, -> MAX(IF(ClientName = 'The Hop', DiskSize, '')) AS Hop, -> MAX(IF(ClientName = 'Razoo', DiskSize, '')) AS Razoo -> from tblBAR -> group by RunDate -> order by DiskSize; ++--+--+--+---+ | RunDate| ABC | Joe | Hop | Razoo | ++--+--+--+---+ | 2002-07-08 | | | 0.6 | | | 2002-07-14 | | 0.8 | | | | 2002-07-17 | | | 1.1 | 1.1 | | 2002-07-11 | 1.2 | 1.8 | | | | 2002-07-13 | 1.8 | 2.0 | | | | 2002-07-04 | | | | 2.6 | | 2002-07-12 | 3.0 | | | | ++--+--+--+---+ 7 rows in set (0.00 sec) This is still calcing MAX, but since there is ORDER BY DiskSize you can take the DESC (descending) attribute out and reading the table is the same. The first value down the column is the MINIMUM, go left to find the date this occured. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Simple Question relating to Indexing
I'll take a stab at this. A key is a value that you can use in a table and relate it to another table. In this usage you will hear people talk of primary keys and foreign keys. A primary key is a value that uniquely identifies a record in a table. With VERY FEW exceptions, all tables should have a primary key. I prefer a primary key that does not mean anything (i.e. a sequential number using MySQL's auto increment instead of SSN or phone number) The primary reason against these 'smart keys' is that no matter what you choose, you will eventually have one that is wrong and has to be changed. Then you orphan all the child records in other tables until you update them. It's a real PITA. (BTW, a primary key can have multiple parts if necessary...but again KISS is the best rule) A foreign key is a primary key form another table, stored in a record to relate that record back to the 'parent'. This allows you to store related data in separate tables but retrieve it easily. My favorite example is people an phones. 1 person may have n phone numbers. your choices for modeling this are either keep adding columns to your person table each time you come up with a new phone type or add a phone table and store the phone number and the personID as a FK. Then you can select * from phone where personID=4 to get all of person #4's phones. (NEVER use Select *!) An Index is a mechanism to allow for faster searching through the database. Index can be unique, as in the case of primary keys. This forces the value to be unique and will throw an error if you insert a duplicate value into a field that has a unique index on it. General rule of thumb, every table should have exactly 1 primary key and 1..n indexes. Both of these are generalizations but they should help you. Or not... =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 10:45 AM To: [EMAIL PROTECTED] Subject: Simple Question relating to Indexing Hello all being relatively new on the SQL scene I must say some of the concepts are a little new and strange to me. My really big question is what is the difference between an index and a key? are the synonamous with each other? Thanks in advance Shaun Bramley query mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Simple Question relating to Indexing
Hello all being relatively new on the SQL scene I must say some of the concepts are a little new and strange to me. My really big question is what is the difference between an index and a key? are the synonamous with each other? Thanks in advance Shaun Bramley query mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: need SQL assistance with a few selects
[snip] 1. An average disksize for each client, date doesn't matter. The result I'd like to see would be something like: "ABC INC","1.8" "Joe's Restaurant","1.2" [/snip] mysql> SELECT CLIENT_NAME, AVG(DISK_SIZE) AS Average, MIN(DISK_SIZE) AS Minimum, MAX(DISK_SIZE) AS Maximum -> from table <--this is a key word use something else -> GROUP BY CLIENT_NAME; ++-+-+-+ |CLIENT_NAME | Average | Minimum | Maximum | ++-+-+-+ | ABC INC| 2.0 | 1.2 | 3.0 | | Eat at Joe | 1.9 | 1.8 | 2.0 | ++-+-+-+ 2 rows in set (0.00 sec) [snip] 2. The date of max disksize and the date of min disksize...in one query...if that's possible. [/snip] I have tried a number of ways and cannot get this one. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question
Shows, Friday, May 03, 2002, 5:02:16 PM, you wrote: SL> Description: SL> error = SL> Column 'nombre' is used with UNIQUE or INDEX but is not defined as NOT NULL SL> My server= SL> mysql Ver 9.38 Distrib 3.22.30, for pc-linux-gnu (i686) SL> Question= SL> Can you help me ? You can't create indexes on columns that can have NULL values. Look at: http://www.mysql.com/doc/P/r/Problems_with_NULL.html SL> Thanks. SL> Juan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: GUI managers for Linux
Rance,
Thursday, May 02, 2002, 8:51:24 PM, you wrote:
RH> Victoria,
RH> You asked me to be sure that the information going into the MyCC dialog box
RH> about the server was correct, and it is, port, user, password, and host, but
RH> still no connect, php can even connect to the database with the
RH> server/user/password triple.
RH> so back to my original question, why cant mycc connect to the database, and
RH> why is the server refusing an apparently valid connection request.
Did you connect to the remote MySQL server or to localhost?
RH> MyCC does support connection via sockets, and Im not using sockets, should I
RH> change just to get it running?
If you connected to the localhost you should specify socket file
manually.
RH> Rance
>>From: Victoria Reznichenko <[EMAIL PROTECTED]>
>>To: [EMAIL PROTECTED]
>>Subject: Re: Re: GUI managers for Linux
>>Date: Wed, 1 May 2002 13:00:01 +0300
>>
>>Rance,
>>Wednesday, May 01, 2002, 12:19:42 AM, you wrote:
>>
>>RH> Victoria, sorry it has taken so long to get back to you, Ive had a
>>partition
>>RH> table problem that has taken a couple of days to fix
>>
>>RH> anyway,
>>
>>RH> Using MyCC, in the database connection dialog, I have the host
>>"localhost"
>>RH> a valid user name, and password
>>
>>RH> Identified the port as the default 3306 but dont know if this is the
>>right
>>RH> answer for my server, or how to find out.
>>
>>You can check it by command:
>> SHOW VARIABLES LIKE 'port';
>>
>>RH> no other boxes checked on the front page of the connection dialog.
>>
>>RH> When I try to connect using this information, I get Error 10061:
>>Connection
>>RH> to Mysql Server Failed.
>>
>>This error occures when server refused connection ...
>>Rance, check if all data (username, password, host, port) is
>>correctly. I know it's a bit of bore request, but I ask you to check
>>it.
>>
>> >>From: Victoria Reznichenko <[EMAIL PROTECTED]>
>> >>To: [EMAIL PROTECTED]
>> >>Subject: Re: GUI managers for Linux
>> >>Date: Mon, 29 Apr 2002 16:48:02 +0300
>> >>
>> >>Rance,
>> >>Sunday, April 28, 2002, 2:44:45 AM, you wrote:
>> >>
>> >>RH> Ive downloaded and sucessfully installed both mysqlgui, and mycc
>>from
>> >>the
>> >>RH> mysql.com site
>> >>
>> >>RH> for some wierd reason I cant connect to the database with either of
>> >>them
>> >>
>> >>RH> I can with the text mysql client, and the text mysqladmin
>> >>
>> >>RH> I have created a database, and granted rights to that database to a
>> >>user.
>> >>
>> >>RH> that user can log in using "mysql -u username -p"
>> >>
>> >>RH> This is a Mandrake Linux 8.2 box running mysql 3.23.47
>> >>
>> >>RH> mysqlgui version 1.7.4
>> >>
>> >>RH> mycc version 0.8.2 alpha
>> >>
>> >>Did you fill up entries for connection? ("Database Connection Dialog" in
>> >>MyCC and "Options" in MySQLGUI)
>> >>
>> >>What errors did you receive?
>> >>
>> >>RH> any hints much appreciated
>> >>RH> Rance
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
indexing?
> -Original Message- > From: Steve Bradwell > Sent: Friday, May 03, 2002 10:31 AM > To: '[EMAIL PROTECTED]' > Subject: indexing? > > I've been doing some reading on indexes but I thought I should get an > experienced persons input. My question is about "Over Indexing". > I have a MySQL MyISAM table that stores inventory transactions, in, out > etc. I store numeric fields that are primary indexes in other tables, 2 > datetime fields and a qty field, Paul DuBois' MySQL book says that > anything used in a where clause or join clause is a candidate for an > index. It turns out that 7 out of eight fields fit this description,1 > primary key, 4 smallints, and 2 are datetime fields. > > Should I use all these indexes or is this overkill? > > Thanks for any input > -Steve. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to Count(*) with LIMIT
> Nope. :) > The table could be in excess of 1 million rows. The > user sees just the > first 100 rows no matter what. If he wants to see a > different group of > records he can use the search fields at the top of > the form. I want the web > server to serve as many pages/sec as possible and > limiting the # of rows > returned to 100 (or less) is the best way to do it. > If everyone started to > do 1 million row queries, then it won't support many > users. > I think that mysql selects all row regardless of LIMIT So although it returns only 100 - I would think there's a better way to do it. olinux __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database Maintenance Philosophy ?
Well, the first obvious suggestion is to use RAID to avoid "hiccups" with disk failure. You probably already have that. At my last job we setup all our servers so that everything was stored on an external RAID. The only thing on the internal (software mirrored) disks was the OS and whatever config files couldn't be stored on the external disks. A cron ran nightly to copy the config files on the internal drive to a backup machine, usually the development machine. If a problem occurred on the production machine, the cables to the external disks would be connected to the backup machine and a script run to reconfigure (change IP address, put config files in place, etc.) the backup machine as the production machine. Reboot and you're back online. Not exactly a hot backup, but the complete switchover could be done within 20 minutes. The longest part being the reboot. The size of the database was not an issue because you weren't restoring or copying anything, except small config files. There were some very large Oracle databases that grew by about 40 million records per month (HBO/Cinemax monthly subscribers). This setup does you no good in the event of database corruption. > Good Friday Morning to you all! > > Some of our MySQL databases are approaching the extrememly large size (10's > of millions of records) and while we do regularly scheduled maintenance via > CRON, and we do nightly differential back-ups couple with full back-ups on > the weekends. Data is added at a fairly brisk pace, we archive data older > than 90 days either in seperate tables (AWK script running SELECT ...INTO > statements via CRON) or by running queries to get specific information (AWK, > CRON) into other tables and deleting records, plus a couple of other ways. > We maintain back-ups for a long, long time. > > Yesterday I was told that we could expect our incoming data to grow by a > factor of 3 in the next 4 months and that incoming data would grow by a > factor of 10 over the next year. We're growing! Yippee for us! (he says, > while trying to maintain a non-facetious look on his face) I always picture > the worst possible scenario, because if something happens to the data my > butt will be the one in the sling. Unless I can put if off on our hardware > guys. > > So I am curious, how are you performing maintenance? I have a written > maintenance plan which is reviewed periodically so that adjustments can be > made if needed, but this plan looks a little thin to me when it comes to > maintaining the size and scope of data that seems to be headed down the road > towards me. Any suggestions you have would be most appreciated. > > Thanks! > > Jay > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
need SQL assistance with a few selects
Greetings folks, I am able to do all this by pulling out data with PHP or PERL...just need some straight MySQL command line advise Specifically, I need some assistance with the following: Here's my table's structure (table name is 'table'): ID, CLIENT_NAME, CLIENT_LOGIN, DISK_SIZE, RUNTIME, RUNDATE Here's example data: "1","ABC INC","abcinc","1.2","00:30:00","2002-01-01" "2","Joe's Restaurant","joesrest","0.2","00:30:00","2002-01-01" "3","ABC INC","abcinc","3.2","00:30:00","2002-01-02" "4","Joe's Restaurant","joesrest","2.2","00:30:00","2002-01-02" "5","ABC INC","abcinc","1.0","00:30:00","2002-01-03" "6","Joe's Restaurant","joesrest","1.2","00:30:00","2002-01-03" This is what I'd like to find out: 1. An average disksize for each client, date doesn't matter. The result I'd like to see would be something like: "ABC INC","1.8" "Joe's Restaurant","1.2" 2. The date of max disksize and the date of min disksize...in one query...if that's possible. I can get the min and max disksize as follows(please critique): SELECT client_name, min(disk_size) from table group by client_name; and likewise with the max() function. Thanks so much! Respectfully yours, Rob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question
Columns that are UNIQUE or indexed *MUST* be defined as NOT NULL (e.g.: 'nombre INT NOT NULL') Michael On Fri, 3 May 2002, Shows Lycos wrote: > > Description: > error = > Column 'nombre' is used with UNIQUE or INDEX but is not defined as NOT NULL > > My server= > mysql Ver 9.38 Distrib 3.22.30, for pc-linux-gnu (i686) > > Question= > Can you help me ? > > Thanks. > Juan > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- /*-- * "It might look like I'm doing nothing, but at the cellular level * I'm really quite busy." * ---anonymous * * * -BEGIN GEEK CODE BLOCK- * Version: 3.1 * GCS/P/H/L/O d- s-:-- a26 C++(+++) UL+++$> P++ L+++ !E W+++ N- o? K- * w--(---) !O M+ !V PS+ PE- Y-- PGP- t+ 5-(--) X(-) R* tv b+ DI-- D-- G e+>++ * h+ r* y-- * --END GEEK CODE BLOCK-- --*/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Good color coded SQL editor for MySQL?
Running emacs 21.2 in Windows NT environment:
(add-to-list 'exec-path "d:/usr/bin")
For the client.
(setf sql-mysql-options (quote ("-n" "-B" "-f" "-i" "-q" "-t")))
Options for the client.
I'm my opinion the best color coded SQL editor ;)
jjs
--
[SIBS] - Sociedade Interbancária de Serviços
http://www.sibs.pt
Rua Soeiro Pereira Gomes, Lote 1 - 1649-031 Lisboa
João Josué Salvado
Email: [EMAIL PROTECTED]
Telef: 00351-218-434-000 (Ext:3421)
Fax: 00351-217-942-440
"There is no reason anyone would want a computer in their home."
Ken Olson, president, chairman and founder of Digital
Equipment Corp., 1977
--
O presente correio electrónico e eventuais ficheiros anexos podem conter
informações confidenciais que devem exclusivamente ser acedidas pelas
instituições ou indivíduos a quem se dirigem. No caso desta mensagem ser
recebida com erro ou por destinatários indevidos, solicita-se a sua
destruição e subsequente aviso para [EMAIL PROTECTED]
This e-mail and related attachments may contain confidential information
which is strictly intended for the use of the authorised recipient. If you
are not the intended addressee or have received this e-mail in error, please
delete it and notify [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question
Description: error = Column 'nombre' is used with UNIQUE or INDEX but is not defined as NOT NULL My server= mysql Ver 9.38 Distrib 3.22.30, for pc-linux-gnu (i686) Question= Can you help me ? Thanks. Juan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1036: Table 'xxx' is read-only
Milan, Friday, May 03, 2002, 2:15:49 PM, you wrote: MR> I have a problem with updating MySQL database on linux with mysql MR> 3.23.24-beta-log. I have prepared a database on Win98 SE platform MR> with 3.23.49a and when i moved to my linux server at school (RedHat MR> 7.0 CZ with MySQL 3.23.24-beta-log) I can't update or insert to table. I MR> have changed rights to database files with chmod to 744 but this MR> doesn't take any effect. Can someone help me? I assume that you have copied files directly on your Linux server. When you change permissions with chmod, you change permissions for user that file belongs. So, if files are not belonging to MySQL user, the server will not open tables. MySQL must be owner of its' data dir and files. mysqldump is recommended for moving database from one server to another. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connecting to MySQL from an apache module using C API
Hello, I have problems with connecting to a mysql database. I use a global MYSQL handle in the module. The connection is created in module_child_init() function and closed in module_child_exit(). It seems, that the connection works only for the first request after apache has been restarted. After that there is no connection and no new connection will be created. What the module do is to read data from HEAP tables from MySQL in a handler function. Maybe is using global MYSQL handle not a good solution? Thanks for help. Robert Vetter Internet Application Developer Kontor23 GmbH Ottenser Hauptstrasse 56-62 22765 Hamburg Tel.: 040/380893-14 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1036: Table 'xxx' is read-only
They must be owned by the user under which the mysqld runs, or change them to mode 766. Milan Reznicek wrote: >I have a problem with updating MySQL database on linux with mysql >3.23.24-beta-log. I have prepared a database on Win98 SE platform >with 3.23.49a and when i moved to my linux server at school (RedHat >7.0 CZ with MySQL 3.23.24-beta-log) I can't update or insert to table. I >have changed rights to database files with chmod to 744 but this >doesn't take any effect. Can someone help me? > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: dupe records
Thank you. This is exactly what I needed. Steve At 07:56 AM 5/3/2002, Jay Blanchard wrote: >[snip] > Here is probably an easy question to answer, but I can't figure > an EASY >way to do it. Right now, I use a temp table with a unique column to solve >it. I am hoping that there is a way in the SELECT >statement. AnywayWhat I want to do is to select records from a table >but if there is more than one with the same cont_id (that is the name of >the column), that it would only select the first one of that id and skip >the rest of that id, but continue with the other rows. > I sure hope that makes since. Only 2 hours of sleep is not good > before >asking for help. >[/snip] > >This query; > >SELECT cont_id, this, that, theother >FROM tblFOO >GROUP BY cont_id >HAVING count(*) = 1 > >will select all records that appear only once in a table according to the >GROUP BY condition. > >This query; > >SELECT cont_id, this, that, theother >FROM tblFOO >GROUP BY cont_id >HAVING count(*) > 1 > >will select all records that are duplicates according to the GROUP BY >condition > >HTH! > >Jay > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem Pl, Solve it
Dear Sir, I am using MySql with Visual Basic I Could not add new record or edit old record through visualbasic in the table of mysql, if there in the table and in that separate field there is no longer value (string long in length) then the value i want to add or edit. thanks please reply me on [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: dupe records
[snip] Here is probably an easy question to answer, but I can't figure an EASY way to do it. Right now, I use a temp table with a unique column to solve it. I am hoping that there is a way in the SELECT statement. AnywayWhat I want to do is to select records from a table but if there is more than one with the same cont_id (that is the name of the column), that it would only select the first one of that id and skip the rest of that id, but continue with the other rows. I sure hope that makes since. Only 2 hours of sleep is not good before asking for help. [/snip] This query; SELECT cont_id, this, that, theother FROM tblFOO GROUP BY cont_id HAVING count(*) = 1 will select all records that appear only once in a table according to the GROUP BY condition. This query; SELECT cont_id, this, that, theother FROM tblFOO GROUP BY cont_id HAVING count(*) > 1 will select all records that are duplicates according to the GROUP BY condition HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database Maintenance Philosophy ?
Good Friday Morning to you all! Some of our MySQL databases are approaching the extrememly large size (10's of millions of records) and while we do regularly scheduled maintenance via CRON, and we do nightly differential back-ups couple with full back-ups on the weekends. Data is added at a fairly brisk pace, we archive data older than 90 days either in seperate tables (AWK script running SELECT ...INTO statements via CRON) or by running queries to get specific information (AWK, CRON) into other tables and deleting records, plus a couple of other ways. We maintain back-ups for a long, long time. Yesterday I was told that we could expect our incoming data to grow by a factor of 3 in the next 4 months and that incoming data would grow by a factor of 10 over the next year. We're growing! Yippee for us! (he says, while trying to maintain a non-facetious look on his face) I always picture the worst possible scenario, because if something happens to the data my butt will be the one in the sling. Unless I can put if off on our hardware guys. So I am curious, how are you performing maintenance? I have a written maintenance plan which is reviewed periodically so that adjustments can be made if needed, but this plan looks a little thin to me when it comes to maintaining the size and scope of data that seems to be headed down the road towards me. Any suggestions you have would be most appreciated. Thanks! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to upgrade MySql?
Thanks! -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 7:04 AM To: [EMAIL PROTECTED] Subject: Re: How to upgrade MySql? Hong, Thursday, May 02, 2002, 10:22:56 PM, you wrote: HT> We have a Web server with Apache 1.3.22+MySql 3.23.44+php 4.0.6 HT> on Solaris 8. Now we want to upgrade MySql to latest version. HT> Should I re-compile both Apache, MySql, and PHP or just re-compile HT> MySql? The question is how to upgrade MySql on Apache server with HT> multi-application? If you want to upgrade MySQL to v3.23.XX you may leave Apache and PHP intact. HT> TIA -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
dupe records
Here is probably an easy question to answer, but I can't figure an EASY way to do it. Right now, I use a temp table with a unique column to solve it. I am hoping that there is a way in the SELECT statement. AnywayWhat I want to do is to select records from a table but if there is more than one with the same cont_id (that is the name of the column), that it would only select the first one of that id and skip the rest of that id, but continue with the other rows. I sure hope that makes since. Only 2 hours of sleep is not good before asking for help. Thanks in Advance Steve mysql,sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connecting to MySQL from an apache module using C API
Hello, I have problems with connecting to a mysql database. I use a global MYSQL handle in the module. The connection is created in module_child_init() function and closed in module_child_exit(). It seems, that the connection works only for the first request after apache has been restarted. After that there is no connection and no new connection will be created. What the module do is to read data from HEAP tables from MySQL in a handler function. Maybe is using global MYSQL handle not a good solution? Thanks for help. Robert Vetter Internet Application Developer Kontor23 GmbH Ottenser Hauptstrasse 56-62 22765 Hamburg Tel.: 040/380893-14 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Migration from windows to linux
I use a shareware tool called dbScripter (www.dkgas.com), which is very cheap to license. It lets you generate scripts that will create table structures, indexes and table-loads. It comes with syntax profiles for SQL Server, Oracle and Access, and you can also create your own profiles. I created one for MySQL and can move data quite easily. I just generate the scripts in Windows against whichever back end, then run them in Linux. It's quite simple. I have licensed it for so long that I forget whether the free download is crippled in any way. It might just have a time limit. If that's all you have to do, I would suggest dbScripter. But is it really all you have to do? Somehow I suspect that this is only the beginning. Do you then have to connect existing Windows apps to the Linux back end? Arthur - Original Message - From: "Sonia" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, April 18, 2002 5:00 AM Subject: Migration from windows to linux > Hello list, > > I`m a newbie to mysql and linux and have at the moment a necessity > concerning to both that I`m not able to handle due to my lack of > knowledge in these fields. The fact is that I need to migrate a mysql > database and its tables from windows to linux and haven`t found anything > in the web that could give me a hint so could someone tell me something > about how to migrate a database from one platform to another? I really > would appreciate the help! > > Looking forward at least one answer ;-) , thanks a lot in advance. > > Sonia. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: data compare and duplicate entries..
Good points, However, while I agree that you need at least one unique key, putting multiple unique keys in a table can also have serious performance ramifications as the system will slow down considerably if constantly rebuilding indexes. It probably depends upon how many fields have to be checked individually, and how busy you expect the system to be overall. Andrew Hazen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 8:17 PM To: [EMAIL PROTECTED] Subject: RE: data compare and duplicate entries.. IMHO it is an expensive to do a query like that. I would consider having a unique key defined and allow the insert form to be posted to the server. If it is a duplicate value then UNIQUE KEY wouldn't allow it be inserted and will return with an error. But if you do not have too many transactions going on then you can have an extra check what Andrew Hazen has suggested. But you cannot get away without having UNIQUE KEY. Because check suggested by Andrew is not fool-proof because if you have too many transactions going on at the same time then it is very likely that SELECT statement may come back with 0 but in the meanwhile somebody else inserted the same row. Vivek > -Original Message- > From: andrewhazen [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 02, 2002 6:44 AM > To: tech; mysql > Cc: andrewhazen > Subject: RE: data compare and duplicate entries.. > > > Not sure of your scripting language, but this logic should work. Just > apply it to each web_form variable posted. > > Select count(*) as number FROM table WHERE field=value_from_web_form > > If number==0 then do your inserts > Else > Echo "Sorry, field=value_from_web_form data already exists."; > Reset form > > > > Andrew Hazen > > > -Original Message- > From: Tech @NorthWeb [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 02, 2002 9:32 AM > To: mysql > Subject: Fw: data compare and duplicate entries.. > > hi, > I was looking for an SQL statement or script that whould allow me to > compare > data when it was entered into a mysql-php field on a web page > so that it > checks whether the data entered has not been used before in the > database.. > In short, when data needs to be entered, the statement/script > must check > in > the database whether the data is not already entered into the database > and > if its there, return with an error.. and not allow the data to be > entered... > if the data is not there, then it may be entered.. > > Thanx > Willem Pretorius > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strategies for maintaining tables calculated from other tables?
I'm also curious what solutions others have arrived at for this. I currently have a similar situation to what you are describing: I've got a large database of timestamped log entries, and need to generate periodic summaries over them, but don't want to actually mine the entire huge database every time. I thus have additional tables that I think of as "caches" of summary info. In the situation where I need to efficiently compute values such as: SELECT COUNT(*) FROM main_log WHERE entry_time >= [begin date] AND entry_time < [end date] I would construct a (permanent) table along the lines of: CREATE TABLE summary_cache (num INT, min_time DATETIME, max_time DATETIME) and then run commands along the lines of LOCK TABLES summary_cache SELECT MAX(max_time) FROM summary_cache INSERT INTO summary_cache SELECT COUNT(*), [the max time just computed], NOW() FROM main_log WHERE entry_time >= [the max time just computed] AND entry_time < NOW() UNLOCK TABLES at various times- maybe on a regular schedule; maybe just before doing computations. It depends on the application and the specifics of the summary I'm computing. (Note that there are also a few issues in here with NOW() value consistency, as well as NOW() value coherency.) Instead of the original query over the entire date range, I can then run SELECT SUM(num), MIN(min_time), MAX(max_time) FROM summary_cache WHERE min_time >= [begin date] AND max_time <= [end date] SELECT COUNT(*) FROM main_log WHERE entry_time >= [begin date] AND entry_time < [min date just computed] SELECT COUNT(*) FROM main_log WHERE entry_time >= [max date just computed] AND entry_time < [end_date] and UNION the results together. Note that if your the MIN and MAX you get out of your summary table are equal to [begin date] and [end date] the main_log WHERE clauses are impossible so you don't even need to run them. One important point about the above is that it intentionally uses intervals which are closed at the bottom and open at the top: the "time < NOW()", specifically, won't work correctly if this is changed because additional entries may come in with timestamps of NOW(). For various reasons, I actually don't make direct use of NOW() as described here; I get a single NOW() value from somewhere and use that explicitly. This also makes it easier to adjust the INSERT INTO to an explicitly specified end time-- the start of a month, for example. You can use additional columns in the summary table to compute additional summaries over the same data set (mins and maxes of various fields in addition to counts, as a trivial example), and you can slap as complex or specific GROUP BY onto your INSERT INTO summary_table as you like, possibly storing some of the grouping info in still more columns in the summary table. You can then use these columns to mimic complex additional where clauses in your original query. Not only is this a useful and flexible technique for performance optimization, it can also be very well encapsulated programmatically, so a nicely coded architecture allows you to add this kind of system to *any* summary queries you run. I'd love any feedback people have on this system. Am I being stupid anywhere here? Any gotchas I maybe haven't foreseen? Further improvements or extensions? -Rob On 2/5/02 at 7:29 pm, Nick Arnett <[EMAIL PROTECTED]> wrote: > I'm finding that it's not quite as simple as I had imagined to > maintain a table whose values are calculated by analyzing other > tables. The source tables contain time series data, which can is > updated several times a day. To calculate totals by day, week, > month, etc., I don't want to have to start over from scratch, so I > want to keep track of which records in the source tables have > already been analyzed. Further complicating things, there are > several analyses that take place and I may add others later. I > don't always want to have to do all of them at the same time. > > So, at the moment, I've added some Boolean enum columns to the > source data tables, showing which analyses have been done. Do the > analysis, set the Booleans to true. I'm not sure I really like that > approach, since it can mean adding columns to rather large tables at > times. I suppose those fields could be in an entirely separate > table, joined by the primary key, but I don't like having to depart > from normal form that way. > > I also considered having a table that keeps track of what analysis > has been done, using a timestamp or auto-increment column, but > realized that would get messed up if I don't process data in the > order it arrived. I could keep track of a range, which would let me > find earlier stuff that hadn't been done. > > Just to make it all a bit more complicated, the initial analysis is > done in a temporary table, for huge performance gain, then inserted > into its final resting spot. > > Any suggestions on how others have solved this sort of problem? -
Re: ERROR 1036: Table 'xxx' is read-only
- Original Message - From: "Milan Reznicek" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:15 PM Subject: ERROR 1036: Table 'xxx' is read-only > I have a problem with updating MySQL database on linux with mysql > 3.23.24-beta-log. I have prepared a database on Win98 SE platform > with 3.23.49a and when i moved to my linux server at school (RedHat > 7.0 CZ with MySQL 3.23.24-beta-log) I can't update or insert to table. I > have changed rights to database files with chmod to 744 but this > doesn't take any effect. Can someone help me? Does the user you're connecting with have the proper privileges set on the Linux MySQL? Check that... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ERROR 1036: Table 'xxx' is read-only
I have a problem with updating MySQL database on linux with mysql 3.23.24-beta-log. I have prepared a database on Win98 SE platform with 3.23.49a and when i moved to my linux server at school (RedHat 7.0 CZ with MySQL 3.23.24-beta-log) I can't update or insert to table. I have changed rights to database files with chmod to 744 but this doesn't take any effect. Can someone help me? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql@lists.mysql.com
Thank you Henning but I'd like to go deeper into detail. What you've said is OK, but then I will have to create a real database-user for each user. I'd like to know how safe this is, cause I am unsure what possibilities this gives to each of the users. On the other hand, if I would create one database user that reads from the database a table (normal table) with all users and passwords, then the check if user-password matches would give the user access to the next website. If a user now wants to select data from the database it would be done with that one user (cause there is no real user for euch user) which is also in my eyes an unsafe solution, cause everyone is the same sql-user. what is the common guideline to realize this? There must be some kind of normal procedure, or isn't? Any hints for an online-manual regarding security aspects? dcp Henning Olsen wrote: > If the users dont have access via ftp to your htm-doc's but only see > the result in browser, you have no problems in having the > MySQL-password in the php-section of the login-doc. > > The users will never see the php-part of the doc, because it's a > server-side part and will only produce the html-code you want it to > produce. > > If your users have ftp-access to docs, you have a problem. > Yours > Henning > > Dennis Peyerl wrote: > >> Hello >> >> I want to realize the following thing: >> A user shall be able to connect through a php-website into mysql. I >> have a form with to input types (standard html). The two input types >> are text and password. >> I don't know how to use that, to log a user in. Do I have to create a >> special table for all the users and then compare the two entries with >> the appropriate inside my table (for that I would need a special user >> taht connects to mysql, just to look if users exist and passwords >> match)? >> Or do I have to create a lot of database-users and the php-script >> (e.g mysql_pconnect) uses the entries from the form? >> >> I don't know what is safe. I would like to get a good document that I >> can read about that. >> Thanks in advance >> >> dcp >> >> >> >> - >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> > > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! with mod_auth from apache integrating with mysql
Info_Best-IT, Thursday, May 02, 2002, 7:07:57 PM, you wrote: II> I have a databse on mysql server that authenticates users... However mod_auth did II> not have access to the database containing the user info. How can I set up my II> database with adequate permissions to allow mod_auth to access that database without II> leaving the database wide open... You should use mod_auth_mysql instead of mod_auth. mod_auth doesn't allow you to authentificate using MySQL database. II> /Thanks II> Tim -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with FullTextSearch and PHP
root,
Thursday, May 02, 2002, 4:42:26 PM, you wrote:
r> If i use full text search with Boolean mode i got the following error
r> mysql> select * from articles where match (title,body) against('+Your -Trail'
r> in boolean mode);
r> ERROR 1064: You have an error in your SQL syntax near 'boolean mode)' at line
r> 1
What version of MySQL do you use?
You can use IN BOOLEAN MODE modifier since 4.0.1.
r> How can i over come this error.
r> mysql> select * from articles where match (title,body) against('+Your
r> -Trail');
r> If i use above query it is retreving data at manually. if i run the quey
r> through PHP it is not retreiving any data.
r> Let me know how can I solve these two problems.
r> regards,
r> sreedhar
r> [EMAIL PROTECTED]
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Should i repair?
Edilson, Friday, May 3, 2002, 3:17:11 AM, you wrote: EVdMJ> I have a table that has now about 10 records (about 100bytes each one). EVdMJ> My queries were too slow and some time a lost connection. But now i can't do EVdMJ> anything with this table :( Show us the structure of the table and provide more information: what is the hardware you are running MySQL on, how many RAM the box has, which MySQL version are you using, how MySQL is compiled, configured and so on. EVdMJ> Even "SELECT * FROM mytable LIMIT 1" doesn't EVdMJ> return a value... Is my table crashed? What should i do now? I can't drop EVdMJ> the table because some data (about 100records) is important. You may try to recreate indexes. See http://www.mysql.com/doc/T/a/Table_maintenance.html for more information on that. EVdMJ> Dirso. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to upgrade MySql?
Hong, Thursday, May 02, 2002, 10:22:56 PM, you wrote: HT> We have a Web server with Apache 1.3.22+MySql 3.23.44+php 4.0.6 HT> on Solaris 8. Now we want to upgrade MySql to latest version. HT> Should I re-compile both Apache, MySql, and PHP or just re-compile HT> MySql? The question is how to upgrade MySql on Apache server with HT> multi-application? If you want to upgrade MySQL to v3.23.XX you may leave Apache and PHP intact. HT> TIA -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Problem
Jagadeesh, Friday, May 03, 2002, 8:06:12 AM, you wrote: JS> We want to use MYSQl as a back end for one of our projects. JS> We download following 4 zip to install MySQL. JS> mysql-4.0.0a-alpha-win-src.zip JS> mysql-4.0.0a-alpha-win.zip JS> mysql-4.0.1a-alpha-win-src.zip JS> mysql-4.0.1a-alpha-win.zip *.win.src.zip is a source distribution. *.win.zip is a binary package. Read installation notes at: http://www.mysql.com/doc/W/i/Windows_installation.html JS> After unzipping the file and MySQL is loaded successfully. JS> But in programs it is not showing any shortcut or program name JS> for JS> mysql. JS> There are any other files to be downloaded to install MySQL JS> properly. JS> When i check in the folder where it is loaded MySQL there are some JS> applications JS> with names MySQLManager,WinMysqladmin in bin directory. JS> When click on these applications it showing some screens but we JS> are unable to JS> do anything on these screen i can only create new database but JS> unable to create JS> any tables. JS> So send me all the details asap You should use client program mysql.exe or any graphical client. You can download two different GUI clients: MyCC and MySQLGUI from MySQL site. http://www.mysql.com/downloads/index.html Any other info about working with MySQL software is also in the manual. Please, check it: http://www.mysql.com/doc/ JS> Thanks -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL under cygwin : mysql_install_db does not create host.frm
sorry, no effect, the deamon could not be started anyway as there is no mysql database ... moreover, mysql_install_db lauch : /usr/libexec/mysqld --bootstrap --skip-grant-tables --basedir=/usr --datadir=/var/mysql/data --skip-innodb --skip-gemini --skip-bdb and the sql statement include only reference to 'root' user in autorisation table. may be that is the problem ? olivier -Message d'origine- De : TP R Murthy [mailto:[EMAIL PROTECTED]] Envoye : ven. 3 mai 2002 12:28 A : Olivier Delrieu Cc : [EMAIL PROTECTED] Objet : Re: mySQL under cygwin : mysql_install_db does not create host.frm hi, where are you running the server from? you might wanna try running from the base dir ./bin/safe_mysqld & i used to have the same problem. on a linux machine though. regards, On Fri, 3 May 2002, Olivier Delrieu wrote: > > Dear all, > > I've installed Cygwin and CAMP and I'm now looking for guidelines to build/install >mysql under cygwin and XPpro. Sorry > for that question but I tried during severals days with no luck. > > I'm using the scripts provided by Gerrit P. Haase in > http://sources.redhat.com/ml/cygwin/2001-12/msg00025.html > with mysql-3.23.49.tar.gz > > At the end of the installation process, I get the following error when starting >mysql deamon : > > /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from >/var/mysql/data > 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: >0) > 020503 07:15:49 mysqld ended > > This error has been already discussed in several mailing lists, the solution was to >chown /var/mysql/data with user > mysql, but it does not work here. > > I used the following procedure : > > - add user mysql and group cyg_mysql in XP (I log with username 'Administrateur') > - add user and group to cygwin files with > mkpasswd -l > /etc/passwd > mkgroup -l > /etc/group > - installation in /scr/mysql > - mysql.build prep => ok > - patch -p0 < ../mysql.patch => ok > - mysql.build conf => ok > - mysql.build build > stops with errors in make info : > make[1]: Leaving directory `/src/mysql/mysql-3.23.49/bdb' > make: *** [info-recursive] Error 1 > - mysql.build install => ok > - mysql.build initdb => ok, but >./scripts/mysql_install_db says : > Preparing db table > Preparing host table > Preparing user table > Preparing func table > Preparing tables_priv table > Preparing columns_priv table > Installing all prepared tables > ERROR: 1033 Incorrect information in file: './mysql/db.frm' > ... > 020503 8:16:00 /usr/libexec/mysqld: Shutdown Complete > > - mysql.build startdb > => stops with 'mysqld ended' : > cat /var/mysql/data/EXBRAYAT.err > /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from >/var/mysql/data > 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' >(errno: 0) > 020503 07:15:49 mysqld ended > > Of course, there is no file in /var/mysql/data/mysql/ ... > > when I cp /dev/null to mysql/host.frm the deamon can access the file and the error >become : > 020503 8:28:58 /usr/libexec/mysqld: Incorrect information in file: >'./mysql/host.frm' > 020503 08:28:58 mysqld ended > > > > So, the installation process did not create the mysql database. but why ? Is there a >patch to apply in > ./scripts/mysql_install_db ? > > Fortunately, I have also installed mysql under XP, and when I copy the XP/mySQL >databases to the CygWin/mySQL ones, > everything (camp & mysqld) is working perfectly, except it is a mess with usernames >... > > Mr Haase, could you help ? :-) > > thanks, > > Olivier. > > > > - > Olivier Delrieu > [EMAIL PROTECTED] > Phone : +33 1 44 89 77 59 >: +33 1 44 89 77 50 (secretariat) > Mobile : +33 6 64 63 00 15 > - > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- _ TP R Murthy (Systems Analyst) Cerulean Information Technology Pvt. Ltd. #24, floor#2, 5th Main, 5th Block, Koramangala, Bangalore-560 095. Phone: 299-1886, 299-1897 web:http://www.ceruleaninfotech.com _ Life is a Dream. Dont Wake up. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EM
RE: ensuring that I'm getting the correct "last insert ID"
Thanks jeff, thats a question thats been bugging me for a while. -Original Message- From: Jeff Kilbride [mailto:[EMAIL PROTECTED]] Sent: 02 May 2002 10:24 To: Sean O'Donnell; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: ensuring that I'm getting the correct "last insert ID" Just make sure you call the last_insert_id() function before returning the connection to the pool. If you're using the mm.mysql driver in Java, you can cast the statement object to an org.gjt.mm.mysql.Statement object and use it's getLastInsertID() method: long lastInsertID = ((org.gjt.mm.mysql.Statement)stmt).getLastInsertID(); A connection pool of this sort can't share connections between different users simultaneously. The MySQL protocol only allows one user per connection at any instant. So, as long as you grab the last insert id before returning the connection to the pool, you will be fine. --jeff - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, May 02, 2002 8:36 AM Subject: RE: ensuring that I'm getting the correct "last insert ID" > if you are using a connection pool the [close database connection] part of > your > example doesnt actually close the connection. so > > 1. Your code might be using several connections > 2. Your code might use a different connection for each statement. > 3. Even if your code only does use the one connection, someone else might be > sharing it. > > asides from that , you dont have to close your database connection to > execute multiple queries in java. > You may have to create multiple statement objects though(depending on the > type of query) > > -Original Message- > From: denonymous [mailto:[EMAIL PROTECTED]] > Sent: 02 May 2002 08:36 > To: Sean O'Donnell; [EMAIL PROTECTED] > Subject: Re: ensuring that I'm getting the correct "last insert ID" > > > But would the same instance of a script drop and pick up different > connections? I haven't done any Java/MySQL work, but anything I've done with > PHP or Perl is based on a model of: > > [begin script] > [open database connection(s)] > [execute queries] > [close database connection(s)] > [end script] > > Is it more common in Java to do something like: > > [begin script] > [open database connection] > [execute query] > [close database connection] > [open database connection] > [execute query] > [close database connection] > [etc...] > [end script] > > ? > > > > - Original Message - > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Thursday, May 02, 2002 11:28 AM > Subject: RE: ensuring that I'm getting the correct "last insert ID" > > > > its used a lot by java. As setting up and closing down connections to the > > database > > is relatively expensive, you use a pool manager, when the connection is > > closed by > > your code, it gets returned to the pool of open connections and is'nt > > actually closed. > > just held open and returned next time you want a connection. So I imagine > if > > you > > have 20 users on the site at once, then they could all be executing > various > > snippets of > > sql over 5 connections. > > > > -Original Message- > > From: denonymous [mailto:[EMAIL PROTECTED]] > > Sent: 02 May 2002 08:27 > > To: Sean O'Donnell; [EMAIL PROTECTED] > > Subject: Re: ensuring that I'm getting the correct "last insert ID" > > > > > > Admittedly, I'm no expert. What *is* connection pooling? > > > > > > - Original Message - > > From: <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Thursday, May 02, 2002 11:21 AM > > Subject: RE: ensuring that I'm getting the correct "last insert ID" > > > > > > > what happens if you are using connection pooling though? > > > > > > > > > -Original Message- > > > From: denonymous [mailto:[EMAIL PROTECTED]] > > > Sent: 02 May 2002 08:09 > > > To: [EMAIL PROTECTED] > > > Subject: Re: ensuring that I'm getting the correct "last insert ID" > > > > > > > > > From: "Jonnycattt" <[EMAIL PROTECTED]> > > > > > > > Hi all, > > > > I know this has been asked a bunch of times, but i need some clarity > > (new > > > > mySQL user). > > > > I have an app that inserts a new user into one table, then inserts > some > > > user > > > > preferences into another table. the procedure is as follows: > > > > 1) insert new user > > > > 2) query for that user's id using select max(userID) as LastUserID > from > > > .. > > > > 3) insert into user preferences table using the previous query's > > > LastUserID. > > > > To be clear, this last insert adds mutliple rows to a table, not one > > row. > > > > > > > > > If I were you, I'd use MySQL's LAST_INSERT_ID() function: > > > http://www.mysql.com/doc/M/i/Miscellaneous_functions.html > > > > > > So long as your ID field is AUTO_INCREMENT, this will return the last > > > auto-generated field in the current handle. > > > > > > Something like this: > > > > > > INSERT INTO UserTable... (your first user insert)
Re: mySQL under cygwin : mysql_install_db does not create host.frm
Hi, I think you must run the db_install script. Good luck, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: TP R Murthy <[EMAIL PROTECTED]> To: Olivier Delrieu <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:27 PM Subject: Re: mySQL under cygwin : mysql_install_db does not create host.frm > hi, > > where are you running the server from? > you might wanna try running from the base dir > > ./bin/safe_mysqld & > > i used to have the same problem. on a linux machine though. > > regards, > > On Fri, 3 May 2002, Olivier Delrieu wrote: > > > > > Dear all, > > > > I've installed Cygwin and CAMP and I'm now looking for guidelines to build/install mysql under cygwin and XPpro. Sorry > > for that question but I tried during severals days with no luck. > > > > I'm using the scripts provided by Gerrit P. Haase in > > http://sources.redhat.com/ml/cygwin/2001-12/msg00025.html > > with mysql-3.23.49.tar.gz > > > > At the end of the installation process, I get the following error when starting mysql deamon : > > > > /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from /var/mysql/data > > 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) > > 020503 07:15:49 mysqld ended > > > > This error has been already discussed in several mailing lists, the solution was to chown /var/mysql/data with user > > mysql, but it does not work here. > > > > I used the following procedure : > > > > - add user mysql and group cyg_mysql in XP (I log with username 'Administrateur') > > - add user and group to cygwin files with > > mkpasswd -l > /etc/passwd > > mkgroup -l > /etc/group > > - installation in /scr/mysql > > - mysql.build prep => ok > > - patch -p0 < ../mysql.patch => ok > > - mysql.build conf => ok > > - mysql.build build > > stops with errors in make info : > > make[1]: Leaving directory `/src/mysql/mysql-3.23.49/bdb' > > make: *** [info-recursive] Error 1 > > - mysql.build install => ok > > - mysql.build initdb => ok, but > >./scripts/mysql_install_db says : > > Preparing db table > > Preparing host table > > Preparing user table > > Preparing func table > > Preparing tables_priv table > > Preparing columns_priv table > > Installing all prepared tables > > ERROR: 1033 Incorrect information in file: './mysql/db.frm' > > ... > > 020503 8:16:00 /usr/libexec/mysqld: Shutdown Complete > > > > - mysql.build startdb > > => stops with 'mysqld ended' : > > cat /var/mysql/data/EXBRAYAT.err > > /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from /var/mysql/data > > 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) > > 020503 07:15:49 mysqld ended > > > > Of course, there is no file in /var/mysql/data/mysql/ ... > > > > when I cp /dev/null to mysql/host.frm the deamon can access the file and the error become : > > 020503 8:28:58 /usr/libexec/mysqld: Incorrect information in file: './mysql/host.frm' > > 020503 08:28:58 mysqld ended > > > > > > > > So, the installation process did not create the mysql database. but why ? Is there a patch to apply in > > ./scripts/mysql_install_db ? > > > > Fortunately, I have also installed mysql under XP, and when I copy the XP/mySQL databases to the CygWin/mySQL ones, > > everything (camp & mysqld) is working perfectly, except it is a mess with usernames ... > > > > Mr Haase, could you help ? :-) > > > > thanks, > > > > Olivier. > > > > > > > > - > > Olivier Delrieu > > [EMAIL PROTECTED] > > Phone : +33 1 44 89 77 59 > >: +33 1 44 89 77 50 (secretariat) > > Mobile : +33 6 64 63 00 15 > > - > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > -- > _ > TP R Murthy (Systems Analyst) > Cerulean Information Technology Pvt. Ltd. > #24, floor#2, 5th Main, 5th Block, Koramangala, Bangalore-560 095. > Phone: 299-1886, 299-1897 > web:http://www.ceruleaninfotech.com > _ > Life is a Dream. Dont Wake up. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PR
RE: LOAD DATA LOCAL error
* Neil Bobstone > I Have compile Mysql 3.23.49 with : --enable-local-infile > > > > But when I want to use LOAD DATA LOCAL its says : The used command is > not allowed with this MySQL version There was some bugs related to --enable-local-infile in 3.23.49: http://www.mysql.com/doc/N/e/News-3.23.49.html > http://www.mysql.com/doc/N/e/News-3.23.50.html > -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mySQL under cygwin : mysql_install_db does not create host.frm
hi, where are you running the server from? you might wanna try running from the base dir ./bin/safe_mysqld & i used to have the same problem. on a linux machine though. regards, On Fri, 3 May 2002, Olivier Delrieu wrote: > > Dear all, > > I've installed Cygwin and CAMP and I'm now looking for guidelines to build/install >mysql under cygwin and XPpro. Sorry > for that question but I tried during severals days with no luck. > > I'm using the scripts provided by Gerrit P. Haase in > http://sources.redhat.com/ml/cygwin/2001-12/msg00025.html > with mysql-3.23.49.tar.gz > > At the end of the installation process, I get the following error when starting >mysql deamon : > > /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from >/var/mysql/data > 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: >0) > 020503 07:15:49 mysqld ended > > This error has been already discussed in several mailing lists, the solution was to >chown /var/mysql/data with user > mysql, but it does not work here. > > I used the following procedure : > > - add user mysql and group cyg_mysql in XP (I log with username 'Administrateur') > - add user and group to cygwin files with > mkpasswd -l > /etc/passwd > mkgroup -l > /etc/group > - installation in /scr/mysql > - mysql.build prep => ok > - patch -p0 < ../mysql.patch => ok > - mysql.build conf => ok > - mysql.build build > stops with errors in make info : > make[1]: Leaving directory `/src/mysql/mysql-3.23.49/bdb' > make: *** [info-recursive] Error 1 > - mysql.build install => ok > - mysql.build initdb => ok, but >./scripts/mysql_install_db says : > Preparing db table > Preparing host table > Preparing user table > Preparing func table > Preparing tables_priv table > Preparing columns_priv table > Installing all prepared tables > ERROR: 1033 Incorrect information in file: './mysql/db.frm' > ... > 020503 8:16:00 /usr/libexec/mysqld: Shutdown Complete > > - mysql.build startdb > => stops with 'mysqld ended' : > cat /var/mysql/data/EXBRAYAT.err > /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from >/var/mysql/data > 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' >(errno: 0) > 020503 07:15:49 mysqld ended > > Of course, there is no file in /var/mysql/data/mysql/ ... > > when I cp /dev/null to mysql/host.frm the deamon can access the file and the error >become : > 020503 8:28:58 /usr/libexec/mysqld: Incorrect information in file: >'./mysql/host.frm' > 020503 08:28:58 mysqld ended > > > > So, the installation process did not create the mysql database. but why ? Is there a >patch to apply in > ./scripts/mysql_install_db ? > > Fortunately, I have also installed mysql under XP, and when I copy the XP/mySQL >databases to the CygWin/mySQL ones, > everything (camp & mysqld) is working perfectly, except it is a mess with usernames >... > > Mr Haase, could you help ? :-) > > thanks, > > Olivier. > > > > - > Olivier Delrieu > [EMAIL PROTECTED] > Phone : +33 1 44 89 77 59 >: +33 1 44 89 77 50 (secretariat) > Mobile : +33 6 64 63 00 15 > - > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- _ TP R Murthy (Systems Analyst) Cerulean Information Technology Pvt. Ltd. #24, floor#2, 5th Main, 5th Block, Koramangala, Bangalore-560 095. Phone: 299-1886, 299-1897 web:http://www.ceruleaninfotech.com _ Life is a Dream. Dont Wake up. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL under cygwin : mysql_install_db does not create host.frm
Dear all, I've installed Cygwin and CAMP and I'm now looking for guidelines to build/install mysql under cygwin and XPpro. Sorry for that question but I tried during severals days with no luck. I'm using the scripts provided by Gerrit P. Haase in http://sources.redhat.com/ml/cygwin/2001-12/msg00025.html with mysql-3.23.49.tar.gz At the end of the installation process, I get the following error when starting mysql deamon : /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from /var/mysql/data 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) 020503 07:15:49 mysqld ended This error has been already discussed in several mailing lists, the solution was to chown /var/mysql/data with user mysql, but it does not work here. I used the following procedure : - add user mysql and group cyg_mysql in XP (I log with username 'Administrateur') - add user and group to cygwin files with mkpasswd -l > /etc/passwd mkgroup -l > /etc/group - installation in /scr/mysql - mysql.build prep => ok - patch -p0 < ../mysql.patch => ok - mysql.build conf => ok - mysql.build build stops with errors in make info : make[1]: Leaving directory `/src/mysql/mysql-3.23.49/bdb' make: *** [info-recursive] Error 1 - mysql.build install => ok - mysql.build initdb => ok, but ./scripts/mysql_install_db says : Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ERROR: 1033 Incorrect information in file: './mysql/db.frm' ... 020503 8:16:00 /usr/libexec/mysqld: Shutdown Complete - mysql.build startdb => stops with 'mysqld ended' : cat /var/mysql/data/EXBRAYAT.err /src/mysql/mysql-3.23.49> Starting mysqld daemon with databases from /var/mysql/data 020503 7:15:49 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 0) 020503 07:15:49 mysqld ended Of course, there is no file in /var/mysql/data/mysql/ ... when I cp /dev/null to mysql/host.frm the deamon can access the file and the error become : 020503 8:28:58 /usr/libexec/mysqld: Incorrect information in file: './mysql/host.frm' 020503 08:28:58 mysqld ended So, the installation process did not create the mysql database. but why ? Is there a patch to apply in ./scripts/mysql_install_db ? Fortunately, I have also installed mysql under XP, and when I copy the XP/mySQL databases to the CygWin/mySQL ones, everything (camp & mysqld) is working perfectly, except it is a mess with usernames ... Mr Haase, could you help ? :-) thanks, Olivier. - Olivier Delrieu [EMAIL PROTECTED] Phone : +33 1 44 89 77 59 : +33 1 44 89 77 50 (secretariat) Mobile : +33 6 64 63 00 15 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: memcpy segfaults from store_record_in_cache under load on p4, 3.23.41 and .49
Jay, please send me the error log, the queries, and the table definitions. What is your my.cnf like? Are your sure memory consumption does not approach 2 GB? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: "Jay Thorne" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, May 03, 2002 9:24 AM Subject: memcpy segfaults from store_record_in_cache under load on p4, 3.23.41 and .49 > error log available on request. Ezmlm rejected it. > > Its always in memcpy+49 just before it gets to the pthread handler and the > segfault handler. > This is pretty consistent. > > I have a query that was 100% reliable, until the server went into production. > Now its serving 15 queries/sec, and crashes pretty often. > > And for extra fun, the query that fails is NOT the one that seems to trigger > it. This query is run constantly, mostly with no errors. > > When I run a report query, other simultaneous queries on the same data seem to > get the errors. > > > 0x806d85a handle_segfault__Fi + 406 > 0x82a81e2 pthread_sighandler + 162 > 0x82daa5f memcpy + 39 > 0x80951fb store_record_in_cache__FP13st_join_cache + 255 > 0x8092ad0 sub_select_cache__FP4JOINP13st_join_tableb + 156 > > Machine is 1.6 ghz p4, 512 megs ram, 2x IDE disks. > OS is rh 7.2, all updates, glibc 2.2.4, though mysql-max does not use that, > Server is mysql-max 3.23.49a, from the rpm from mysql.com. Tried a source > recompile, exact same symptoms and error location. > > Tried also 3.23.41 from redhats's source tree, recompiled with --innodb, since > I need the innodb consistent read handler, the performance under myISAM is > disappointing under load. Errors under .41/using glibc are nearly identical, > modulo different version and shared libc. (glibc 2.2.4): > > 0x80bf15f handle_segfault__Fi + 431 > 0x4002a4f3 _end + 936436875 > 0x4018863f _end + 937870807 > 0x80e8309 store_record_in_cache__FP13st_join_cache + 217 > 0x80e535f sub_select_cache__FP4JOINP13st_join_tableb + 159 > 0x80e54c2 sub_select__FP4JOINP13st_join_tableb + 258 > > -- > Jay "yohimbe" Thorne [EMAIL PROTECTED] > Mgr Sys & Tech, Userfriendly.org > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql@lists.mysql.com
If the users dont have access via ftp to your htm-doc's but only see the result in browser, you have no problems in having the MySQL-password in the php-section of the login-doc. The users will never see the php-part of the doc, because it's a server-side part and will only produce the html-code you want it to produce. If your users have ftp-access to docs, you have a problem. Yours Henning Dennis Peyerl wrote: > Hello > > I want to realize the following thing: > A user shall be able to connect through a php-website into mysql. I > have a form with to input types (standard html). The two input types > are text and password. > I don't know how to use that, to log a user in. Do I have to create a > special table for all the users and then compare the two entries with > the appropriate inside my table (for that I would need a special user > taht connects to mysql, just to look if users exist and passwords match)? > Or do I have to create a lot of database-users and the php-script (e.g > mysql_pconnect) uses the entries from the form? > > I don't know what is safe. I would like to get a good document that I > can read about that. > Thanks in advance > > dcp > > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA LOCAL error
Hi, I Have compile Mysql 3.23.49 with : --enable-local-infile But when I want to use LOAD DATA LOCAL its says : The used command is not allowed with this MySQL version Could anyone help me ? Best regards, Julien Calvet - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-selects
"Alex" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:... > i have to use a statement like: > SELECT CODICE FROM TABSIC WHERE COM=1 AND SEN=1 AND PRI=1 > AND CODICE<>ALL(SELECT CODICE_TABSIC FROM ARCHIVIO_SICU_MIN WHERE > (CODICE_ARCHIVIO=342)) > > but mysql doesn't support inner select. > > What statement should i use instead of this one ? > > Thanks... > Alex. Here's how I'd do that : SELECT TS.CODICE, ASM.CODICE_TABSIC FROM TABSIC TS WHERE TS.COM=1 AND TS.SEN=1 AND TS.PRI=1 AND TS.CODICE<>ALL LEFT JOIN ARCHIVIO_SICU_MIN ASM WHERE ASM.CODICE_ARCHIVIO=342 This is a blind guess - haven't tried it - , maybe it'll work for you... regards, marc - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA error
Hi, I Have compile Mysql 3.23.49 with : --enable-local-infile But when I want to use LOAD DATA LOCAL its says : The used command is not allowed with this MySQL version Could anyone help me ? Best regards, Julien Calvet - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql@lists.mysql.com
Hello I want to realize the following thing: A user shall be able to connect through a php-website into mysql. I have a form with to input types (standard html). The two input types are text and password. I don't know how to use that, to log a user in. Do I have to create a special table for all the users and then compare the two entries with the appropriate inside my table (for that I would need a special user taht connects to mysql, just to look if users exist and passwords match)? Or do I have to create a lot of database-users and the php-script (e.g mysql_pconnect) uses the entries from the form? I don't know what is safe. I would like to get a good document that I can read about that. Thanks in advance dcp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: inserting into the lowest possible id
Hi, Nice tricky, but... SET @x:=0; SELECT @x:=t1.id+1 FROM mytable t1 LEFT JOIN mytable t2 ON t1.id+1 = t2.id WHERE t2.id IS NULL LIMIT 1; INSERT INTO mytable SET id=@x,data0='x',data1=''; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Dan Nelson <[EMAIL PROTECTED]> To: daniel <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:24 AM Subject: Re: inserting into the lowest possible id > In the last episode (May 02), daniel said: > > lets say you have a table like this > > > > id | data0 | data1 | ... > > 0| x | y | ... > > 1| x | y | ... > > 2| x | y | ... > > 3| x | y | ... > > 4| x | y | ... > > 7| x | y | ... > > > > what if when i performed an INSERT query again, i wanted that id to > > NOT be 8, but be 5 since it's the lowest available id. what would i > > do? > > SELECT t1.id+1 > FROM mytable t1 LEFT JOIN mytable t2 ON t1.id+1 = t2.id > WHERE t2.id IS NULL; > > That will return the first "empty" id in each block of unused ids. In > your example, it would return > > +-+ > | t1.id+1 | > +-+ > | 5 | > | 8 | > +-+ > 2 rows in set > > If you only want the first row, add a "LIMIT 1" to the end of the query. > > -- > Dan Nelson > [EMAIL PROTECTED] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with MySQL on FreeBSD4.5
(Sorry if I am reposting this now, guys...ehe) I'm having trouble with a MySQL installation on a FreeBSD4.5 system (MYSQL version 3.23.49). >From time to time top shows that it runs >98% of CPU time, and of course the site's >response gets sluggish... Sometimes queries even times out or MySQL reports that no sockects are free to complete the request. This actually does not happen anymore, but it did a few times earlier...short time after we had a disk crash... ;-) I had a comment from one of the users regarding this: >I seriously suspect that mySQL under FreeBSD is your problem. On another heavily >loaded website I use (www.perlmonks.org), we're experiencing >similiar problems. >Current speculation is that it has to do with the way the FreeBSD flock() function is >broken. >I imagine what you're seeing is lots of blocked mySQL processes, or where a query >takes an exceptionally long time to complete. CPU utilization is >remaining pretty >low during the event. Just want to know if anybody has something to say to this matter, perhaps suggestions on where to start for debugging the problem? I have no clue where to begin looking for reasons, so any tip could very well be a good tip for me right now! Debugging tools or approaches to recommend...? Anybody? Best regards, Eivind, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ROWID
Hello, Is there, in MySQL 4.0 database tables, a pseudo column ROWID (containing a unique id for each row of the tables), like in Oracle or Informix databases ? If not, will this feature be in MySQL 4.1 ? Regards A. ROY - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Problem
On 3 May 2002 05:06:12 - "Jagadeesh Suryadevara" <[EMAIL PROTECTED]> wrote: > When i check in the folder where it is loaded MySQL there are some > applications > with names MySQLManager,WinMysqladmin in bin directory. > When click on these applications it showing some screens but we > are unable to > do anything on these screen i can only create new database but > unable to create > any tables. > So send me all the details asap you should find in bin directory, a file called "mysql", you can run it from ms-dos prompt it's text based ;-) -- Let's call it an accidental feature. -- Larry Wall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
